Excel - Percentile Interpolation

Asked By strawberry on 03-Aug-10 11:39 AM
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 replied to strawberry on 04-Aug-10 08:08 AM
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 =3D percentile):

i =3D 1 + INT((20-1)*85%) =3D 17
f =3D MOD((20-1)*85%,1) =3D 0.15
p =3D 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 is not that I like that forum.  it is 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 replied to joeu2004 on 04-Aug-10 12:21 PM
om/Forums/en-US/category/officeexcel.

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.