Percentile function

[Guest]

Hello:
I am trying to find out the lower 25% of my dataset. I use the function like
Percentile(A1:A35, 0.25). It returns a value for me. Does it mean that any
number in the data set under this value belongs the lower 25% of the dataset?
I checked the Excel Help file and it does not help. Can anyone give me some
details?
Thanks in advance!

 

[Michael R Middleton]

Vincdc -

I am trying to find out the lower 25% of my dataset. I use the function
like Percentile(A1:A35, 0.25). It returns a value for me. Does it mean
that any number in the data set under this value belongs the lower 25% of
the dataset? <

Yes. You should get the same result with QUARTILE(A1:A35,1).

I checked the Excel Help file and it does not help. Can anyone give me
some details? <

Min value in the data range is assigned percentile 0. Max value is assigned
1. Other data values, in rank order, get a step increment of 1/(n-1), where
n is the total number of values in the data range. Linear interpolation is
used for intermediate percentiles.

Caution: There are various ways to calculate percentiles. Excel's method is
just one method. So, if you refer to statistics books, you may see some of
the others. For example, the min value could be percentile 1/(2n) and max
value could be (2n-1)/(2n), with steps of 1/n, with extreme percentiles
undefined. The differences among methods are usually small for very large
data sets.

- Mike

www.mikemiddleton.com

 

[Guest]

Thanks!

Vincdc -


Yes. You should get the same result with QUARTILE(A1:A35,1).


Min value in the data range is assigned percentile 0. Max value is assigned
1. Other data values, in rank order, get a step increment of 1/(n-1), where
n is the total number of values in the data range. Linear interpolation is
used for intermediate percentiles.

Caution: There are various ways to calculate percentiles. Excel's method is
just one method. So, if you refer to statistics books, you may see some of
the others. For example, the min value could be percentile 1/(2n) and max
value could be (2n-1)/(2n), with steps of 1/n, with extreme percentiles
undefined. The differences among methods are usually small for very large
data sets.

- Mike

www.mikemiddleton.com