Need help with a formula

[Rob]

Hi everyone,
I need help setting up a formula.

I have N observations (usually between 60 and 100 observations).
I want to choose the observation that estimates the 95th percentile.
Industry guidelines suggest that the observations be ranked and the 95th
percentile observation be chosen based on the following formula:

95th percentile observation ~= observation in position (N*(95/100)+0.5)

If there are 87 observations ranked from smallest to largest, the 95th
percentile observation would be at position 83.15
In cases like this the value of the 95th percentile would be estimated by
linear interpolation of the results at position 83 and position 84 as
follow:

Value at position 83 + 0.15(Value at position 84 - Value at position 83)

Is there some way to set this up as a single formula?

Thank you,
Rob

 

[Bob Phillips]

Doesn't the PERCENTILE function do just that?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Doesn't the PERCENTILE function do just that?

Yes and no. PERCENTILE() gives the correct answer
(almost), whereas the OP wanted the wrong answer .

If there are 87 observations ranked from smallest to largest,
the 95th percentile observation would be at position 83.15[.]
In cases like this the value of the 95th percentile would be
estimated by linear interpolation of the results at position 83
and position 84 as follow:
Value at position 83 + 0.15(Value at position 84 - Value at position 83)

How do you figure that? 87*95% = 82.65. And indeed,
PERCENTILE(87,95%) returns 82.7 if the observed values
are 1,...,87.

I only quibble with the way that PERCENTILE() rounds
results. For example, if the observed values in A1:A87
are 100,200,...,8700, PERCENTILE(A1:A87,95%) returns
8270 instead of 8265 (or arguably 8266 or ....).