Average after dropping two lowest scores

  • Thread starter Thread starter jsl
  • Start date Start date
J

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
 
Hi
use the SMALL function. e.g.
=SMALL(your_range,2)
for the second lowest number in your range
 
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.
 
So the formula for a range D1 to D6 for instance would be.......
=SUM(SUM(D1:D6)-SMALL(D1:D6,1)-SMALL(D1:D6,2))/(COUNT(D1:D6)-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
 
Back
Top