prctile function and percentile function in excel

11 次查看（过去 30 天）
Hnin Thet Hmue Khin
评论： 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?

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

回答（2 个）

Cris LaPierre
Cris LaPierre 2023-10-24
编辑：Cris LaPierre 2023-10-24
Ran in:
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
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. :-)

请先登录，再进行评论。

the cyclist
the cyclist 2023-10-24
编辑：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.

类别

在 Help Center 和 File Exchange 中查找有关 Data Distribution Plots 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by