PERCENTILE Problem??

[andyp161]

Is it possible using the PERCENTILE function to select the middle 90% o
an array for analysis therefore discarding the top 5% and bottom 5%.

I have a worksheet containing results from a survey set up as follows:

Column A-N = Question 1, Question 2, Question 3 etc.
Row 1 = Lables
Rows 2-2001 = person 1, person 2, person 3 etc.

Column E contains the earnings of each respondent. I would like t
select the middle 90% of earners for further analysis in respect of al
other columns, therefore discarding the top 5% earners and bottom 5
earners from my analysis.

Kind regard

 

[Jerry W. Lewis]

You can use a formula like
=IF((earning<=PERCENTILE(earnings,90%))*(earning>=PERCENTILE(earnings,10%)),1,0)
to create a helper column that has a 1 for values to include

Alternately (less calculationally efficient), you could put the test in
each analysis function, such as
=AVERAGE(IF((earnings<=PERCENTILE(earnings,90%))*(earnings>=PERCENTILE(earnings,10%)),earnings))
array entered (Ctrl-Shift-Enter)

Jerry

 

[Ron Rosenfeld]

Is it possible using the PERCENTILE function to select the middle 90% of
an array for analysis therefore discarding the top 5% and bottom 5%.

I have a worksheet containing results from a survey set up as follows:

Column A-N = Question 1, Question 2, Question 3 etc.
Row 1 = Lables
Rows 2-2001 = person 1, person 2, person 3 etc.

Column E contains the earnings of each respondent. I would like to
select the middle 90% of earners for further analysis in respect of all
other columns, therefore discarding the top 5% earners and bottom 5%
earners from my analysis.

Kind regards

Take a look at the TRIMMEAN worksheet function:

TRIMMEAN

Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
taken by excluding a percentage of data points from the top and bottom tails of
a data set. You can use this function when you wish to exclude outlying data
from your analysis.

Syntax

TRIMMEAN(array,percent)

Array is the array or range of values to trim and average.

Percent is the fractional number of data points to exclude from the
calculation. For example, if percent = 0.2, 4 points are trimmed from a data
set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set.


--ron