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...
In theory this is what I'm looking for, and should work:
=AVERAGEIF(MAX(E13:F13,">0"),MAX(G13:H13,">0"),MAX(I13,">0"))
It's picking the Max score from E13:F13, >0, the Max Score from G13:H13, >0, any score in I13 >0, and then averages them. IF any columns are blank/empty, it should ignore them, and Only average the columns with numbers >0 in them. However, the spreadsheet will Not accept this formula, and keeps insisting that I put an Apostrophe in the beginning, because it's not a formula?
=AVERAGEIF(MAX(E13:F13,">0"),MAX(G13:H13,">0"),MAX(I13,">0"))
It's picking the Max score from E13:F13, >0, the Max Score from G13:H13, >0, any score in I13 >0, and then averages them. IF any columns are blank/empty, it should ignore them, and Only average the columns with numbers >0 in them. However, the spreadsheet will Not accept this formula, and keeps insisting that I put an Apostrophe in the beginning, because it's not a formula?
Yes, I deleted it as I started thinking I misunderstood the question.
My thought was you could add a "High Score" field for each test and use the max function. Then, use Averageifs to average the high scores if they are greater than 0.
If there's no retest 3, then just delete that field and link the "high score" to test 3's first attempt.
Or, move the final result fields to the end as Hans did and just averageif those 3 cells are ">0" (it would look cleaner and you wouldn't have the "high scores" commingled with the rest of your data, so you could eliminate that condition).