Does anyone know how Excel calculates the value it returned using the
function Precentile()?

It is not merely the interpolation of the bounding values.

Hi,
go to excel help and enter percentile

"Greg" wrote:

Does anyone know how Excel calculates the value it returned using the
function Precentile()?

It is not merely the interpolation of the bounding values.

"Eduardo" wrote:
go to excel help and enter percentile

I suspect Greg already read that, since he said "it is not merely the
interpolation", which is all the PERCENTILE help page says (for Excel 2003
offline help).

Presumably, Greg has some reason to believe it is not simple interpolation.
Greg, please show us why you don't think so. A concrete example. Your
expectations. Ideally, your own calculations to justify your expectation.

Alternatively, we can try to demonstrate how we think Excel might be
calculating the interpolation. (I have not yet found a dispositive
description online.)

Consider the example in Table 2 at
http://onlinestatbook.com/chapter1/percentiles.html.

Note: I am not suggesting that the methods described on that web page are
correct or applicable to Excel. As the web page explains, there is no single
method for calculating percentiles. For those who don't want to look, the
table contains the array of the following 20 results:
{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:

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))

"Mere interpolation". But the devil is in the details ;-).


----- original message -----

"Eduardo" wrote:
Hi,
go to excel help and enter percentile

"Greg" wrote:

Does anyone know how Excel calculates the value it returned using the
function Precentile()?

It is not merely the interpolation of the bounding values.