prctile function and percentile function in excel

Question

Hnin Thet Hmue Khin 2020-1-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501045-prctile-function-and-percentile-function-in-excel

评论： Cris LaPierre 2023-10-25

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?

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

jean 2023-10-24

移动：DGM 2023-10-24

I have same issue

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Cris LaPierre 2023-10-24

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501045-prctile-function-and-percentile-function-in-excel#answer_1339793

编辑：Cris LaPierre 2023-10-24

在 MATLAB Online 中打开

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

2 个评论
显示无隐藏无

the cyclist 2023-10-24

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 2023-10-25

That would be pretty cool!

请先登录，再进行评论。

Answer 2

the cyclist 2023-10-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501045-prctile-function-and-percentile-function-in-excel#answer_1339761

编辑：the cyclist 2023-10-24

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.