TRIMMEAN with different percentiles?

[agbiggs]

The TRIMMEAN function gives you the average of the interior of a
distribution, where you cut x% from both the top and the bottom.

Is it possible to easily modify the percentages, so I could find the
average between specific percentiles? Say, finding the average of the
values between the 20th and 40th percentiles, rather than just trimming
out on either end? (In this example, I know I could use PERCENTILE to
just find the 30th percentile to get close, but in the data I'm using
it would be better to get an average of a group.)

Thanks very much,

Andrew

 

[Jerry W. Lewis]

You could try something like
=AVERAGE(IF((data>PERCENTILE(data,20%))*(data<PERCENTILE(data,40%)),data))
array entered (Ctrl-Shift-Enter). Ties could be problematic, but they are
with TRIMMEAN as well, since TRIMMEAN{1,1,1,1,1,1,1,1,2,2},10%) should return
1.125=(7*1+1*2)/8 but instead returns 1.2.

Jerry

 

[agbiggs]

Thanks! You'd think they'd make this easier. The whole issue came about
because Autofilter, while making it so you don't see certain cells on
the screen, doesn't exclude them from calculations that cover the whole
range. So I'm having to find ways to get around this. Thanks again,

Andrew

 

[Jerry W. Lewis]

Warning: Be sure brain is in gear before pressing Send. ;-)

1.125 = TRIMMEAN({1,1,1,1,1,1,1,1,2,2},20%), not
TRIMMEAN({1,1,1,1,1,1,1,1,2,2},10%)
Although the exact calcuation for TRIMMEAN({1,1,1,1,1,1,1,1,2,2},10%) is
unclear, since it is not the obvious
7/6=(7.5*1+1.5*2)/9

Jerry

 

[Jerry W. Lewis]

Per Help, TRIMMEAN({1,1,1,1,1,1,1,1,2,2},10%) =
AVERAGE({1,1,1,1,1,1,1,1,2,2}); since 10% of 10 < 2, nothing is trimmed.

Jerry

:

....