Forum Discussion
Averaging highest grades
Hi, I teach a class, and have an excel spreadsheet. Format: Test 1 Retest 1 Test 2 Retest 2 Test 3 I'd like it to find the Highest grade of the first two, the Highest grade of the second...
Hans: Hi, thank you for that. Doesn't quite work though, because sometimes I do not have a value in some of the columns, for example, if there is no retest 1, or retest 2, or sometimes no test 3 at all. That seems to mess it up, its still averaging by 3, regardless of null in some columns, which are just blank if there wasn't a test score to put in there. Any idea? Thank you so much.
The formula that I suggested should work if you leave the cells empty for tests/retests that have not been done. Both MAX and AVERAGE ignore blank cells.
But if you enter a 0 in a cell for a test not done, that will mess up the result.
Try it!
- Hans: Hi, this is the formula that matches my columns:
=AVERAGE(MAX(E13:F13),MAX(G13:H13),I13)
If I enter only 1 test score in Test 1, or Test 2, it is averaging it/dividing by 2, even leaving the rest of the cells empty?Ah, OK, if Test 1 and Retest 2 are both blank, the formula will fail, and also if Test 2 and Retest 2 are both blank. Here is a solution with two helper columns.
In J13: =IF(COUNT(E13:F13),MAX(E13:F13),"")
In K13: =IF(COUNT(G13:H13),MAX(G13:H13),"")
In L13 the average: =AVERAGE(I13:K13)