prctile function vs excel percentile

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

Answer 1

[John D'Errico]

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.

Answer 2

[Chan Dennis]

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

Answer 3

[Nick]

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.

[John D'Errico]

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.