Matlab Percentile function VS excel percentile function

Is the matlab percentile function is equal to excel percentile function. I'm getting two different answers for the same data set.

Answer 1

[Matt Fig]

I don't see a PERCENTILE function in MATLAB. Is it a custom function you wrote?

[vijay sabawat]

there is function matlab

Y = prctile(X,p)

Y = prctile(X,p,dim)

[Matt Fig]

Oh, the PRCTILE function. How different are the values you get?

[vijay sabawat]

It depends on the sample size.. for a size of 10 the difference is about 1.5 and so. But if the sample size is large then results pretty close..

[vijay sabawat]

A = [11 15 74 42 63 21]';

B = prctile(A,30:20:70)

xlswrite('H:\test.xlsx',A)

B =

16.8000 31.5000 56.7000

using excel

=PERCENTILE($A$1:$A$6,0.3) = 18

=PERCENTILE($A$1:$A$6,0.7) = 52.5

Answer 2

[Oleg Komarov]

I get the same results:

A = rand(1000,1);
B = prctile(A,30:20:70)
xlswrite('C:\Users\Oleg\Desktop\test.xlsx',A)

and then using PERCENTILE($A$1:$A$1000,0.3)

EDIT

Excel interpolates for values of the percentile that are not multiples of 1/(n-1). So the 25th percentile on 1:5 = 2! (online reference: percentile)

=PERCENTILE(A1:A5,0.25) where A1:A5 = 1:5 --> 2

Matlab interpolates for percentile values that are not (i -.5)/n, for i = 2, 1.5/5 = .3 which is not the 25th percentile.

prctile(1:5, .25) -->  1.7500 % Which makes more sense IMO

Hope this clarifies.

[vijay sabawat]

instead of 1000 sample size reduce the size to 10 then you can see the difference..

[vijay sabawat]

A = [11 15 74 42 63 21]';

B = prctile(A,30:20:70)

xlswrite('H:\test.xlsx',A)

B =

16.8000 31.5000 56.7000

using excel

=PERCENTILE($A$1:$A$6,0.3) = 18

=PERCENTILE($A$1:$A$6,0.7) = 52.5

[Oleg Komarov]

See my edit.