nested "IF" function in Excel 2007

  • Thread starter Thread starter dale hollingsworth
  • Start date Start date
D

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
 
Try this:

=IF(COUNT(A1:A5)=0,"no
data",AVERAGE(IF(COUNT(A1:A5)=5,LARGE(A1:A5,{1,2,3,4}),A1:A5)))
 
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
Click to expand...

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
 
Back
Top