Nested IF

[Tony]

I can't figure out a formula for the following problem:
Student A has the choice of taking 4 out of 5 possible test but if he took
all 5 then the lowest grade would be dropped??

 

[Don Guillett]

Assuming grades are in d18,d19,d20,d21,d22 then put this in e18 and copy
down FOUR
=IF(LEN(TRIM(D18))<1,"",LARGE($D$18:$D$22,ROW(A1)))

 

[Bernie Deitrick]

Tony,

=(SUM(B2:B6)-IF(COUNT(B2:B6)=5,MIN(B2:B6),0))/MIN(COUNT(B2:B6),4)

with test grades in B2:B6

HTH,
Bernie
MS Excel MVP