Average Without Extremes

  • Thread starter Thread starter Tiziano Bianchi
  • Start date Start date
T

Tiziano Bianchi

I would like to be able to average some account balances after
excluding an X number of lowest and highest values. I would
like to be able to specify every time how many of the lowest and
how many of the highest values to throw out, thus X could also be
equal to zero. The value assigned to X for the lowest values to
exclude would not necessarily match the one assigned for the
highest values to exclude.

I know how to do it if the value for X is 1, for both the highest and
lowest values:
=(SUM(range)-SMALL(range,1)-LARGE(range,1))/(COUNT(range)-2)

I don't know how to do it if the value for X is different than 1.

Thanks for all your help.
 
Maybe try this, same thing, just longer..... (untested)

=SUM((range)-SMALL(range,1)-SMALL(range,2)-LARGE(range,1)-LARGE(range,2))/(C
OUNT(range)-4)

You could then write all these individual formulas and put them all in a
VLOOKUP table to be looked-up by your key value for X.....

Vaya con Dios,
Chuck, CABGx3
 
One way:

Assume your number of low values to exclude is in J1, and the number
of high values to exclude is in J2:

=(SUM(rng, IF($J$1<1, 0, -(SMALL(rng,ROW(INDIRECT("1:"&$J$1))))),
IF($J$2<1, 0, -(LARGE(rng,ROW(INDIRECT("1:"&$J$2)))))))/
(COUNT(rng)-$J$1-$J$2)

If the numbers you want to exclude are the same for low and high, use


=TRIMMEAN(rng, 1/$J$1)
 
That first formula should be array-entered, BTW.

J.E. McGimpsey said:
One way:

Assume your number of low values to exclude is in J1, and the number
of high values to exclude is in J2:

=(SUM(rng, IF($J$1<1, 0, -(SMALL(rng,ROW(INDIRECT("1:"&$J$1))))),
IF($J$2<1, 0, -(LARGE(rng,ROW(INDIRECT("1:"&$J$2)))))))/
(COUNT(rng)-$J$1-$J$2)

If the numbers you want to exclude are the same for low and high, use


=TRIMMEAN(rng, 1/$J$1)
Click to expand...
 
Back
Top