30 views (last 30 days)
Show older comments
Nick on 24 Mar 2015
-
-
Link
Direct link to this question
https://www.mathworks.com/matlabcentral/answers/184895-prctile-function-vs-excel-percentile
Answered: Chan Dennis on 23 Dec 2018
Accepted Answer: John D'Errico
Hi,
when I run the below,
A = [ 0.02; 0.01 ; 0.01; 0.01 ; 0.04 ]; B = prctile(A,99.95);
I get B = 0.0400, which seems rounded.
In excel the same percentile function produces a result of 0.03996
Is there a way to show a less rounded result in matlab? Any other function or calculation in matlab e.g. 65/43, will produce a result (1.5116) that doesn't round the decimals like prctile does.
(even if I try "format long" the result will still be 0.040000000...)
Many thanks
0 Comments Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
Sign in to answer this question.
Accepted Answer
John D'Errico on 24 Mar 2015
Edited: John D'Errico on 24 Mar 2015
Open in MATLAB Online
This is the behavior as it is designed to do. READ THE HELP.
1) The sorted values in X are taken as the 100*(0.5/N), 100*(1.5/N),
..., 100*((N-0.5)/N) percentiles.
2) Linear interpolation is used to compute percentiles for percent
values between 100*(0.5/N) and 100*((N-0.5)/N)
3) The minimum or maximum values in X are assigned to percentiles
for percent values outside that range.
Thus, for 5 data points, anything above the 90th percentile is returned as the highest element in your vector.
In Excel, the difference is it is NOT the SAME percentile function. Two different people wrote those codes, to two different sets of specifications. The Excel percentile function treats 0.04 as the 100% point. In MATLAB, it is treated as the 90th percentile.
Think of it as if you put each of those numbers into bins. In MATLAB, when you have 5 numbers, MATLAB assigns the 80-100% bin to 0.04, the highest element in the vector, but above 90%, it chooses not to extrapolate. This was clearly a design choice.
B = prctile(A,79:100)
B =
Columns 1 through 11
0.029 0.03 0.031 0.032 0.033 0.034 0.035 0.036 0.037 0.038 0.039
Columns 12 through 22
0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04
Never make the mistake of assuming that two functions in two different languages are the same, that they should produce the same results, even if they have similar names and goals.
0 Comments Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
More Answers (2)
Chan Dennis on 23 Dec 2018
Open in MATLAB Online
Today, I also meet the issue, and find the article in Wikipedia (Percentile).
Clearly, both percentile alogrithms are the different cases of the linear interpolation between closest ranks method. More details in Percentile - Wikipedia.
- When let C=0.5, the result is equal prctile function in matlab.
- When let C=1, the result is equal excel percentile.
There are two matlab functions below, which are the implementations of the above algorithms.
prctile_one.m (resuls equal excel PERCENTILE and PERCENTILE.INC)
function V_x = prctile_one(V,p)
% The linear interpolation between closest ranks method in the case of `C=1`
% [Percentile - Wikipedia](https://en.wikipedia.org/wiki/Percentile)
if ~isvector(p) || numel(p) == 0 || any(p < 0 | p > 1) || ~isreal(p)
error('Make sure the Second digit within the [0,1] interval');
end
V = sort(V,'ascend');
N = length(V);
x = p*(N-1)+1; % position x
if floor(x) < N
V_x = V(floor(x)) + mod(x,1)*(V(floor(x)+1) - V(floor(x))); % value
else
V_x = V(N); % position N
end
prctile_half.m (resuls equal matlab prctile)
function V_x = prctile_half(V,p)
% The linear interpolation between closest ranks method in the case of `C=0.5`
% [Percentile - Wikipedia](https://en.wikipedia.org/wiki/Percentile)
if ~isvector(p) || numel(p) == 0 || any(p < 0 | p > 1) || ~isreal(p)
error('Make sure the Second digit within the [0,1] interval');
end
V = sort(V,'ascend');
N = length(V);
p_1 = 1/(2*N); % position 1
p_N = 1 - 1/(2*N); % position N
if p_1<=p && p<=p_N
x = N*p + 0.5; % position x
V_x = V(floor(x)) + mod(x,1)*(V(floor(x)+1) - V(floor(x))); % value
else if 0<=p && p<p_1
V_x = V(1); % value 1
else if p_N<p && p<=1
V_x = V(N); % value N
end
end
end
0 Comments Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
Nick on 24 Mar 2015
Edited: Nick on 24 Mar 2015
Thanks for the in depth reply. After doing some research, apparently there are at least 9 different methods to calculate percentiles, although excel's seems to be the most popular amongst software packages.
1 Comment Show -1 older commentsHide -1 older comments
Show -1 older commentsHide -1 older comments
John D'Errico on 24 Mar 2015
Direct link to this comment
https://www.mathworks.com/matlabcentral/answers/184895-prctile-function-vs-excel-percentile#comment_274351
Personally, I might have chosen the Excel scheme. The highest datapoint seems like it should be 100%, the smallest, 0%. But I can see entirely valid arguments for doing it other ways.
Of course, were I truly the author of the code, I would have offered multiple schemes, and let the user pick among them as options. But then I tend to overdo those things as an author.
Sign in to comment.
Sign in to answer this question.
See Also
Categories
AI, Data Science, and StatisticsStatistics and Machine Learning ToolboxDescriptive Statistics and VisualizationDescriptive Statistics
Find more on Descriptive Statistics in Help Center and File Exchange
Tags
- prctile
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- Deutsch
- English
- Français
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)
Contact your local office