prctile function and percentile function in excel
조회 수: 22 (최근 30일)
이전 댓글 표시
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
2023년 10월 24일
편집: Cris LaPierre
2023년 10월 24일
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
ri = floor(r)
rf = r-ri
% linearly interpret the value corresponding to index of data(1.9)
P = data(ri) + rf*(data(ri+1)-data(ri))
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).
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1520509/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1520513/image.png)
% computer the percentile of each data point
pp = 100*((1:n)-.5)/n
% linearly interpolate the value based on the data point percentages
interp1(pp,data,p)
% compare to prctile
mp = prctile(data,p)
댓글 수: 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. :-)
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.
댓글 수: 0
참고 항목
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!