The Percentile Calculator in Excel returns the “k-th percentile” from a supplied data set.
We can use Percentile exc function to calculate Excel 90th percentile rank, the 70th percentile rank, etc.
Figure 1. Percentile Function in Excel
- Array = A set of data values
- k – A number between 0 and 1 representing the k:th percentile
A percentile is simply a value under which a specified percentage of data set values fall.
The Excel Percentile function is an built-in operation which is categorized under Excel’s Statistical Functions.
How to Calculate Percentiles in Excel
We will now show you how to find percentiles in Excel by using the following simple steps;
- Let’s say we are required to analyze the total number of students who ranked above a specified percentile in an entrance exam. We begin by collecting the data available to us inside our Excel worksheet;
Figure 2. of Percentile Data in Excel
Be sure to allocate a column of empty cell for Percentiles exc to return our resulting number values (column F above.
- The Percentile rank formula, which we will enter into cell F2 of our worksheet example is shown below:
Figure 3. Percentile Function in Excel
The Percentile exc function returned 97.9 as the Excel 90th percentile rank value for the array of scores in column B of our worksheet example.
- Copy the modified version of the Percentile rank formula down into the other empty cells in column F to obtain the expected percentile ranking results;
Figure 4. of Percentiles in Excel
A percentile that is calculated with a k value of 4 means that 40% percent of its values are below or equal to its calculated result, while a percentile that is calculated with a k value of.9 means that 90% percent of its values are below or equal to its calculated result.