nested "IF" function in Excel 2007
 

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

nested "IF" function in Excel 2007
 

Try this:

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

--
Biff
Microsoft Excel MVP


<dale hollingsworth wrote in message
...
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

nested "IF" function in Excel 2007
 

On Sun, 02 Nov 2008 18:22:43 -0800, dale hollingsworth wrote:

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