Substituting a value if higher than another in a series.

[Opheltes]

In a course that I teach, there are four components to the grade average: participation, paper, midterm, and final, each of which is worth 25% of the grade. Easy to average the four.

But if a student gets a higher grade on the final than the midterm, I want to substitute that for the midterm. In other words, if the student got a 70 on the midterm and an 80 on the final, I'd like a formula that computes the grade to have 80 twice instead of 70 and 80. (If the student's final is the same or lower than the midterm, then it's simply averaged).

Any suggestions would be welcome. Thanks.
Opheltes

 

[Fluff]

How about

Book1
	C	D	E	F	G
1	Participation	Paper	MidTerm	Final	Average
2	60	80	70	80	75
3	60	80	70	70	70
4	60	70	90	80	75
Report

Cell Formulas
Range	Formula
G2:G4	G2	=IF(F2<=E2,AVERAGE(C2:F2),AVERAGE(C2:D2,F2,F2))

 

[mikerickson]

=(C1+D1+F1+MAX(F1,E1))/4

 

[Opheltes]

=(C1+D1+F1+MAX(F1,E1))/4