TRIMMEAN with different percentiles?

  • Thread starter Thread starter agbiggs
  • Start date Start date
A

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

:

....
 
Back
Top