nested "IF" function in Excel 2007

[dale hollingsworth]

How would I write a function that will count the number of tests taken and if it is 5 subtract the lowest score and then average the remaining 4? If only 4 tests then the lowest is not subtracted.

Thanks

 

[T. Valko]

Try this:

=IF(COUNT(A1:A5)=0,"no
data",AVERAGE(IF(COUNT(A1:A5)=5,LARGE(A1:A5,{1,2,3,4}),A1:A5)))

 

[Ron Rosenfeld]

How would I write a function that will count the number of tests taken and if it is 5 subtract the lowest score and then average the remaining 4? If only 4 tests then the lowest is not subtracted.

Thanks

You did not indicate what you want to happen if there are other than 4 or 5
tests, but to average the highest four scores:

=AVERAGE(LARGE(rng,{1,2,3,4}))

where rng is a reference to your five potential cells.

This formula will return an error if there are not at least four scores, and
will average the highest four no matter how many scores are present.
--ron