Averaging highest grades

[Dale_Armstrong]

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

[#2]

@Dale_Armstrong 

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)

[#3]

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.

[#4]

@Dale_Armstrong 

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!

[#5]

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?

[#6]

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!

[#7]

@Dale_Armstrong 

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)

[#8]

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?

[#9]

@Dale_Armstrong 

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).