Average after dropping two lowest scores

[jsl]

I am trying to take the average of a series of items after dropping the two
lowest scores. This is trivial if I want to drop only the lowest score (use
the MIN function). However, I do not know of a simple way, with an Excel
function for example, to take the average or sum of a set of numbers if I
want to drop the 2 or three lowest scores. Thanks,
Jim

 

[Frank Kabel]

Hi
use the SMALL function. e.g.
=SMALL(your_range,2)
for the second lowest number in your range

 

[jsl]

Thanks!

Hi
use the SMALL function. e.g.
=SMALL(your_range,2)
for the second lowest number in your range

 

[JE McGimpsey]

One way (Array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(LARGE(OFFSET(A1,,,n,1), ROW(INDIRECT("1:" & n - y))))

where you should replace n with the number of rows in your range and y
with the number of scores to drop.

 

[Bill Kuunders]

So the formula for a range D1 to D6 for instance would be.......
=SUM(SUM(D16)-SMALL(D16,1)-SMALL(D16,2))/(COUNT(D16)-2)
if there are more than one second smallest number
then only one of them has been subtracted

or if there are three smallest numbers the same
then only two of them get subtracted.

regards
Bill K