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 2, and 3, IF > 0, and average the 3. Again, Average the 3 highest grades, Only IF filled in/higher than zero.
I've found several solutions on the web, and copied them exactly, and NONE worked! I have Office 365 Subscription.
Thank you, Dale
8 Replies
- 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?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).
- JMB17: Hi, got an email with your suggestion, but could not see the attachment, or the post, nor reply to your email.
I Googled awhile ago, and it also recommended the AVERAGEIFS and values >0, and I VERY carefully checked the syntax, and the Excel spreadsheet would Not allow me to enter that formula, and kept insisting it was in error, and not a correct formula! Drove me crazy, and led me to this forum! 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!
