Percentiles in Excel

[Jon J]

According to the example in Excel's help tool, the 30th percentile of
(1,2,3,4) is 1.9. Can anyone provide any details on how Excel gets this
result?

My calculations give a different a result for the 30th percentile as follows:
There's a 25% chance that a number selected from (1,2,3,4) is <= 1.
There's a 50% chance a number selected from (1,2,3,4) is <= 2.
Interpolating to get the number with a 30% chance gives me .8(1)+.2(2) = 1.2
for the 30th percentile of (1,2,3,4).

Thanks.

 

[David Biddulph]

Read the help again and it'll give you the hint. You are dividing the range
into not n intervals, but n-1.

=PERCENTILE(A$2:A$5,1/3) (the 33.33% percentile) is 2
The zeroth percentile is 1.
Interpolate between them gives the 30th percentile as being 1.9

QED

 

[Lance]

include the 0

=PERCENTILE({0,1,2,3,4},0.3)

 

[Lance]

Add the 0 in the array

=PERCENTILE({0,1,2,3,4},0.3)