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...
Let's say the scores are in columns B to F, starting in row 2. Enter the following formula in G2:
=AVERAGE(MAX(B2:C2),MAX(D2:E2),F2)
- 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?
