TRIMMEAN with different percentiles?

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

"agbiggs@hotmail.com" wrote:

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

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

"Jerry W. Lewis" wrote:

> 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@hotmail.com" wrote:
>
> > 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
> >
> >

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

"Jerry W. Lewis" wrote:

....
> Although the exact calcuation for TRIMMEAN({1,1,1,1,1,1,1,1,2,2},10%) is
> unclear ...