CODEWITHSUNDEEP
excelexcel-formulapercentilevba
Raa
Raa

Reputation: 121

Calculating percentile - Excel vs online

I have a set of data say {4,7,7,10,10,12,12,14,15,67} and i want to know the 95th Percentile. I used Excel and Online calculator.

Both gave different answers.

In Excel, formula i used : =PERCENTILE.INC(A1:A10,0.95) and result = 43.6

But this online percentile calculator yielded a result of 67

Which one is right?

Upvotes: 1

Views: 2578

Answers (1)

John Coleman
John Coleman

Reputation: 51998

First of all, both methods are "right" in the sense that both implement a standard algorithm for computing percentiles. Unlike the mean or median (where all sources use the same approach) there are many different approaches to calculating percentiles. The fundamental issue is that there is no obvious solution to the problem of what to do with percentiles which fall between observations. Do you take the observed value which is closest? Do you interpolate between the two? If so -- with what weighting factors do you do the interpolation? Wikipedia discusses nine (!) with both the Excel approach and the approach from that online percentile calculator making the list. See this paper for a very nice discussion of these algorithms.

You can replicate the functionality of that online percentile function like thus:

=SMALL(A1:A10,CEILING.MATH(COUNT(A1:A10)*0.95))

For example:

enter image description here

The point of using the function SMALL rather than a direct numerical index is that this approach works even if the data isn't sorted.

Upvotes: 2

Related Questions