Percentile Interpolation

[strawberry]

For values above 50%, Excel 2003's PERCENTILE function seems to use
the following method:

(F x Xi) + ((1-F) x Xi+1)

where i is the integer and F is the fractional part of (n+1)k

and n is the total number of values

and k is the target percentile

What method does it use for values below 50%?

(Hope this makes sense to someone!)

 

[joeu2004]

For values above 50%, Excel 2003's PERCENTILE function seems to use
the following method: (F x Xi) + ((1-F) x Xi+1)
where i is the integer and F is the fractional part of (n+1)k
and n is the total number of values and k is the target percentile
What method does it use for values below 50%?

I do not think the method of interpolation differs depending on the
percentage.

The following is what I inferred from one example some months ago. I
hope it helps.

Consider the following 20 values in A1:A20:
{4,4,5,5,5,5,6,6,6,7,7,7,8,8,9,9,9,10,10,10}.

Compute the 85%percentile.

Excel's PERCENTILE(A1:A20,85%) results in 9.15.

It appears that Excel calculates that as follows (p = percentile):

i = 1 + INT((20-1)*85%) = 17
f = MOD((20-1)*85%,1) = 0.15
p = INDEX(A1:A20,i) + f * (INDEX(A1:A20,i+1) - INDEX(A1:A20,i))


PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[strawberry]

I do not think the method of interpolation differs depending on the
percentage.

The following is what I inferred from one example some months ago.  I
hope it helps.

Consider the following 20 values in A1:A20:
{4,4,5,5,5,5,6,6,6,7,7,7,8,8,9,9,9,10,10,10}.

Compute the 85%percentile.

Excel's PERCENTILE(A1:A20,85%) results in 9.15.

It appears that Excel calculates that as follows (p = percentile):

i = 1 + INT((20-1)*85%) = 17
f = MOD((20-1)*85%,1) = 0.15
p = INDEX(A1:A20,i) + f * (INDEX(A1:A20,i+1) - INDEX(A1:A20,i))

PS:  For broader participation, you might want to post future
inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Thanks for the clear and thorough response, and for the forum
suggestion. That does indeed seem to be EXACTLY how Excel 2003
calculates percentiles!

Thanks again.