prctile function and percentile function in excel

I have some datat to analyze and I used prctile function in matlab. I checked the prctile function results with the excel. but the answers are different a little bit. what can I do to be correct? or Is the result of prctile function and percentile in excel not same always?

[jean]

I have same issue

Answer 1

[Cris LaPierre]

The differences you are seeing is because they are using different algorithms. There are many different ways to calculate percentile. The paper listed in the Reference section of the MATLAB Documentation lists at least 14 methods.

• Excel (method 12 in “Quartiles in Elementary Statistics”)
• MATLAB (method 10 in “Quartiles in Elementary Statistics”, with -0.5 instead of +0.5 since MATLAB uses 1 based indexing instead of 0)

A quick summary of the difference using this example

p = 30; % percentile
data = [2 3 32 80];

Excel: Computes the percentile using linear interpolation where 0% is the lowest data point, 100% is the highest.

n = numel(data);  % number of data points
r = (p/100)*(n-1)+1  % -1 and +1 are to shift to 0-based indexing, then back to 1-based indexing

r = 1.9000

ri = floor(r)

ri = 1

rf = r-ri

rf = 0.9000

% linearly interpret the value corresponding to index of data(1.9)
P = data(ri) + rf*(data(ri+1)-data(ri))

P = 2.9000

MATLAB: Computes the percentile using linear interpolation where the lowest data point corresponds to (12.5% here) and the highest data point corresponds to (87.5% here).

% computer the percentile of each data point
pp = 100*((1:n)-.5)/n

pp = 1×4

12.5000 37.5000 62.5000 87.5000

% linearly interpolate the value based on the data point percentages
interp1(pp,data,p)

ans = 2.7000

% compare to prctile
mp = prctile(data,p)

mp = 2.7000

[the cyclist]

Thank you for writing the answer I was too lazy to write. I scanned Mathworld and a couple other places, looking for a list of the different methods, but didn't find them right away.

I wonder if I could find my (very old, handwritten) notes where I graphically illustrated several of the methods for myself. :-)

[Cris LaPierre]

That would be pretty cool!

Answer 2

[the cyclist]

Quoting the Wikipedia article on Percentile, "There is no standard definition of percentile". Because of this, there can be differences between different software systems, particularly for small datasets.

You can be absolutely certain that the MATLAB calculation is accurate. The exact algorithm used is in the Algorithms section of the prctile function documentation.

Note that this does not mean that Excel is inaccurate. (I have no doubt that it is accurate.) The two software system simply use different conventions.