Choosing the higher number of two, with caveat.

[Opheltes]

Hello all.

If my students get a higher grade on the final exam than the midterm, I substitute that grade in their average to reward improvement. If the final is lower, I merely average the scores.

In a gradebook, I am trying to set up a formula, averaging a series of grades, but choosing the higher of two grades (midterm is column A, final is B, paper is C, presentation is D,etc) only if the final is higher.

Scooter's grades:
A1 is 81%
B1 is 79%
C1 is 80%
D1 is 80%

Sally's grades:
A2 is 90%
B2 is 98%
C1 is 80%
D1 is 80%

Scooter's grades should be merely be averaged with the other scores; Sally's final (B2) would replace her midterm and be averaged with the other grades.

How do I get it to choose the higher grade but only if the higher is the final?

Thanks for any suggestions.
Opheltes

 

[Scott Huish]

=average(max(a1:b1),b1:d1)

 

[Opheltes]

Thanks, Scott. Quick and slick.

Opheltes