Calculating statistics on a portion of a distribution

[DDH]

I am interested in calculating basic stats (mean, median, std dev) on the
middle 80% of a distribution, to eliminate the extreme lows and highs. I
have been doing this manually by counting the number of samples and removing
the bottom 10% and top 10% of the distribution, and then using the average,
median, and stdeva functions on the middle 80%. Is there an automated way to
do this in Excel?

 

[Jerry W. Lewis]

See Help for the TRIMMEAN worksheet function. For other statistics, you
could probably construct an array formula using the SMALL or LARGE function
to return the middle portion of the sample.

Jerry

 

[Dave Curtis]

Hi,

I do this by creating a helper column to eliminate the reqyired percentage
of high and low values.
If your data is in A1:A20, then in b1, enter
=IF(OR(PERCENTRANK($A$1:$A$20,A1)<0.1,PERCENTRANK($A$1:$A$20,A1)>0.9),"",A1)
and drag down.
This will eliminate the upper and lower 10% of the data.
Change the 0.1 and 0.9 in the formula to suit your own cut off points.
Then you can use =MEAN, =MEDIAN and =STDEV on column B, as th blanl cells
will be ignored.

Dave

 

[DDH]

Dave - this works perfectly. Thank you! DDH

Hi,

I do this by creating a helper column to eliminate the reqyired percentage
of high and low values.
If your data is in A1:A20, then in b1, enter
=IF(OR(PERCENTRANK($A$1:$A$20,A1)<0.1,PERCENTRANK($A$1:$A$20,A1)>0.9),"",A1)
and drag down.
This will eliminate the upper and lower 10% of the data.
Change the 0.1 and 0.9 in the formula to suit your own cut off points.
Then you can use =MEAN, =MEDIAN and =STDEV on column B, as th blanl cells
will be ignored.

Dave