Average Without Extremes

[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.

 

[CLR]

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

 

[J.E. McGimpsey]

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)

 

[J.E. McGimpsey]

That first formula should be array-entered, BTW.

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)

 

[J.E. McGimpsey]

Pasted the wrong text:

=TRIMMEAN(rng, $J$1/COUNT(rng))