# Thread: few items for class score sheet (2003)

1. ## few items for class score sheet (2003)

Hi All,

I've inherited an excel spreadsheet used as a master (ie, use this to start and then tailor by filling in class info) for keeping track of student grades. I've made a few changes but am having trouble with a few other items. I'm not attaching the spreadsheet now since it isn't mine yet but will if I have to. Here are the questions:

1. We have lab assignments as part of every class. Right now, the following formula calculates the lab grade (similar for quiz):
=IF(COUNT(B9:Y9)>0, ROUND((SUM(B9:Y9) - (COUNT(B9:Y9)>2)*(SMALL(B9:Y9,1)) - (COUNT(B9:Y9)>3)*(SMALL(B9:Y9,2))) / (COUNT(B9:Y9) - (COUNT(B9:Y9)>2) - (COUNT(B9:Y9)>3)),2), "too few labs")
where B9:Y9 is the range for the first student with the average going in col Z and subsequent rows for other students.
Two questions on this:
a. I've set the formula to drop a student's lowest 2 grades. But the dropping does not occur if you've entered only a few grades. In particular, if no grades are entered, you just get the value "too few labs". If you enter the first lab grade, you get a #NUM error because of the SMALL (B9:Y9,2) factor. When you enter a second grade, all grades are counted. When you enter a 3rd grade, the lowest is dropped. When you enter a 4th or subsequent grade, only 2 grades are dropped. Is there a way to avoid the #NUM error when only 1 grade is entered?

b. Is there a way to place an entry somewhere in the spreadsheet (like 2, 3, or 4) that allows a teacher to pick how many labs to drop and have one formula like the above use that entry to calculate the lab average?

2. There is validation of the lab/test scores. Starting with a blank, one might enter "nl" for "no lab" if, for example, a class was cancelled due to snow. Otherwise, no non-numeric entries are allowed. On the other hand, a lab grade could be 0-10 (0 if absent or really not with it) all the way up to 10. However, there could also be extra credit on the lab so another point or two might be allowed - right now 10 is set up as a warning. What I'm looking for is a validation rule that is a 2-step rule: if non-numeric, only allow "nl"; if numeric allow 0-10 with a warning if higher but an error if greater than 10+max extra credit allowed (or negative number). I'd prefer no VBA but am skeptical that such is possible. Thoughts?

TIA

Fred

2. ## Re: few items for class score sheet (2003)

If you make the number of low scores to be dropped variable, how exactly do you want to handle the case where only a small number of scores is available? You'll have to specify this precisely.

You can set up mixed numeric/non-numeric validation using the appropriate formula, but as far as I know, you cannot mix a warning and an error, I think that would require code (in the Worksheet_Change event procedure)

3. ## Re: few items for class score sheet (2003)

Thanks for the answers Hans.

In response to your response:
1. variable number of lowest scores dropped: As the formula included in my original posting showed, I dropped the lowest 2 scores according to the following rule: do not drop any scores until you entered the 3rd score, then drop the lowest. When you enter the 4th score or any subsequent score, drop the 2 lowest. So, if you're dropping n scores ( where n would probably be 2, 3, or 4 depending on the teacher), the rule is drop the lowest score if you have n+1 scores, drop lowest two scores when you have n+2 scores.

One reason I did it this way (as opposed to always dropping the lowest score(s) from the beginning) was because of the Excel errors. That is, you get #NUM errors if you use SMALL(range,k) if you haven't entered k scores yet. By the end of the semester, this is no longer a concern, even with dropping as many as 4 scores (which would probably be the most you'd drop) since you would have had at least 10 quizzes by then. However, I'm getting the #NUM error with my formula too when entering the first score.

Any thoughts on this are appreciated.

2. On the data validation: I just noticed, at least for Excel 2003, that one can create a custom formula to check the entry against. I don't think I ever noticed it before and probably never used it. Is this what you're thinking of? If so, I'm trying to think of the formula to flag a non-numeric entry that's not equal to "nl" or a numeric entry greater than 10 or 12 (12 is a quiz grade max including extra credit). Raising a warning or an error for both is probably ok. Maybe something like the following data validation rule?
AND(<>"nl",>12)

Anyway, I'm interested in what you're thinking about.

Fred

4. ## Re: few items for class score sheet (2003)

2) Yes, I was thinking of Data | Validation with a custom formula. Say that you select a range of cells starting at B9. A possible formula would be:

=IF(ISNUMBER(B9),AND(B9>=0,B9<=10,B9=TRUNC(B9)),B9 ="nl")

The formula will automatically be adjusted for the other cells in the selection. The formula must return TRUE if the value entered by the user is OK, FALSE otherwise. It first checks if the entry is numeric. If so, it checks whether the entry is greater than or equal to 0 AND less than or equal to 10 AND a whole number. If the entry isn't numeric, it is compared to "nl".

Change 10 to 12 or whatever you want to be the maximum score that can be entered. To add a warning if the entry is higher than 10, use the Worksheet_Change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("B9:Y28")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("B9:Y28")).Cells
If oCell > 10 Then
MsgBox "You entered a value higher than 10 in " & _
oCell.Address(False, False), vbInformation
End If
Next oCell
Application.EnableEvents = True
End If
Set oCell = Nothing
End Sub

Adjust the range as needed.

5. ## Re: few items for class score sheet (2003)

Say that you enter the number of scores to be dropped in cell AA1. This can be 0, 1, 2, ...
I have assumed that if the number of available scores is less than or equal to AA1, none should be dropped. If the number is AA1+1, 1 should be dropped, etc. and if the number is twice AA1 or more, AA1 scores should be dropped.
The formula for the average becomes

=IF(COUNT(B9:Y9)>0,SUM(LARGE(B9:Y9,ROW(INDIRECT("1 :"&MIN(COUNT(B9:Y9),MAX(\$AA\$1,COUNT(B9:Y9)-\$AA\$1))))))/MIN(COUNT(B9:Y9),MAX(\$AA\$1,COUNT(B9:Y9)-\$AA\$1)),"no data")

entered as an array formula, i.e. confirmed with Ctrl+Shift+Enter instead of just Enter.

6. ## Re: few items for class score sheet (2003)

Thanks Hans. The formula is great. I'll probably adjust the TRUNC part or leave it out.

Any thoughts on the first part with dropping grades?

Fred

7. ## Re: few items for class score sheet (2003)

That is in the next reply.

8. ## Re: few items for class score sheet (2003)

Sorry Hans - didn't see your later post on the score dropping.

I haven't tested your formula yet but it looks like it should do the trick. I thought the solution was going to based on an array formula but I kept looking to do something with SMALL.

Fred

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•