PERCENTILE and QUARTILE in Excel

This example will teach you how to use the functions PERCENTILE (PERCENTILE) and QUARTILE (QUARTILE) in Excel. In the figure below you see a list of values ​​(green shading is for illustration only).

1. Use the function PERCENTILE (PERCENTILE) shown below to calculate the 30th percentile. Excel returns a value of 12,7. This means that 30% (6 out of 20) of the values ​​in the figure below are less than or equal to 12,7.

   =PERCENTILE(A1:A20,0.3)

   =ПЕРСЕНТИЛЬ(A1:A20;0,3)

Note: Second function argument PERCENTILE (PERCENTILE) must be a decimal number between 0 and 1. The algorithm for calculating percentile and quartile in Excel is not quite the same as in most statistics books.

2. Use the function PERCENTILE (PERCENTILE) shown below to calculate the 90th percentile. Excel returns a value of 61,7. This means that 90% (18 out of 20) of the values ​​in the range A1:A20 are less than or equal to 61,7.

   =PERCENTILE(A1:A20,0.9)

   =ПЕРСЕНТИЛЬ(A1:A20;0,9)

3. Use the function QUARTILE (QUARTILE) to calculate the 1st quartile. Excel returns 11,25. This means that 25% (5 out of 20) of the values ​​are less than or equal to 11,25.

   =QUARTILE(A1:A20,1)

   =КВАРТИЛЬ(A1:A20;1)

Second function argument QUARTILE (QUARTILE) must be a number between 0 and 4.

Formula =PERCENTILE(A1:A20;0,25) gives exactly the same result as =QUARTILE(A1:A20;1).

Decide for yourself which function you use to calculate the desired quartile. Below is a small table showing all possible formulas: