A percentile is a form of calculation under statistics where numbers are categorized based on the number of values that fall within the specified percentage.
Excel has an in-built function for finding percentile of values. Excel operations involving percentile are very straightforward.
Below is the syntax for percentile
"=PERCENTILE (array, k)"
The array is the data values.
'K' is any number that represents the nth percentile.
Steps in finding the 90th percentile on Excel
You can calculate the 90th percentile of a set of data within a few steps.
1. Firstly, identify the column containing the data and the column where you want to display your result.
2. Enter the syntax function in the formula bar. In this case, you should enter '0.9' for 'k.'
3. Press enter, and your result will display.
Let's consider an example so that you can see how we can use the PERCENTILE.INC function to find the 90th percentile.
1. Firstly, identify the cell that will display your data. In this example, it's "C2".
2. Select cell "C2".
3. Navigate to the formula toolbar and click the button to insert function (fx).
4. When the dialog box pops up, type "PERCENTILE.INC" in the box where you are to search for functions.
- The PERCENTILE.INC function will appear. Click on it twice.
- A dialog box will pop up, and you will be asked to enter the values for the arguments.
=PERCENTILE (array, k)
- i.e. =PERCENTILE.INC(B2: B8,0.90) Here, the score data is present in the range (B2 to B8) for which we need to apply PERCENTILE.INC function
- Enter the reference cell in the array argument. In this example, it's B2: B8.
- Enter the percentile value for k. Here, we are calculating the 90th percentile, so. we will enter 0.90.
- Click OK and proceed.
The result for the 90th percentile will be displayed, and in this example, it's 95
When using the percentile function, the values for 'k' must fall between 0 and 1.
For instance, if you are finding 50% or 50th percentile, the value for k in the function should be '.5.' or '50 %.'
The value for k must be between 0 and 1. The percentile function will display #NUM! Error if you enter a value less than 0 or greater than 1 for ' k.'
If you enter a non-numeric value for 'k,' then the percentile function will display #VALUE! Error.
#NUM! An error also occurs when you don't enter any value for k.