PERCENTRANK Function in Excel
The PERCENTRANK function gives the rank to each number against overall numbers in percentage numbers. Not many of us use this Excel PERCENTRANK function because of a lack of knowledge, or maybe the situation never demanded this at all. But this will be a great function because showing the rank in percentage values within the data set will be a new trend.
For example, suppose we have a dataset that shows the scores of the students. And now, we need to calculate the percentile rank of each student’s score. Using the Excel PERCENTRANK function, we can calculate the rank of each student’s score in the dataset as a percentage of the whole dataset.
Table of contents
Below is the syntax of the PERCENTRANK function.
- Array: This is the range of value against which we are giving rank.
- x: This is nothing but the individual value from the lot.
- [Significance]: It is an optional parameter. We can use this argument if we want to see decimal places. For example, if the rank percentage is 58.56% and we want to see two-digit decimal values, we can enter the significance number as 2. It will show the percentage as 58.00%. It will take three digits of significance by default if you ignore it.
Let us start the example with a simple series of numbers. Consider the below series of numbers for this example.
Copy and paste the above data to a worksheet.
- Open PERCENTRANK function
We must first open the Excel PERCENTRANK function in cell B2.
- Select Number of Range as Absolute Reference
The first argument of the PERCENTRANK function is “Array,” i.e., our total numbers range from A2 to A11, so select this range as the reference and make it an absolute reference.
- Now, select x reference value.
Next, we must select cell A2 as the x reference. As of now, ignore [significance] argument, and close the bracket.
- Next, press the Enter key to get the result.
Also, drag the formula to the below cells as well.
Now, closely look at the numbers. Let us go from the bottom.
We have number 3 for the first three values, so the first lowest number in the array always ranks as 0.000%.
Let us now go to the next value, 4. This number is ranked as 33.333% because number 4 is the 4th positioned number in the range of values. Below this number 4, there are 3 values. And above this number, there are 6 values, so the percentage is arrived at using the below equation.
i.e. 3 / (3 + 6)
Similarly, the top value in the range is 15, and it is ranked as 100%, which is equivalent to a rank of 1.
Now, look at the below scores of students in an examination.
Using this data, we will find the percentage rank. Below is the formula applied for these numbers.
Step 1: Open the PERCENTRANK function
We must first open the PERCENTRANK function in cell C2.
Step 2: Select Number of Range as Absolute Reference
The first argument of the PERCENTRANK function is “Array,” i.e., our total numbers range from B2 to B11. So, we must select this range as the reference and make it an absolute reference.
Step 3: Now select x reference value.
Next, we must select cell B2 as the “x” reference. As of now, ignore [significance] argument, and close the bracket.
Step 4: Hit the Enter key to get the result.
Also, drag the formula to the below cells.
The lowest value in the range is 326 (B8 cell). It has a rank of 0.00%, and the highest value in the range is 589 (B4 cell). Therefore, it has been given a rank of 100%.
We will give the significance parameter as 2 and see what happens.
The decimal places are rounded to the below number, as we can see above. So, for example, in cell C2, without using a parameter, we have got 88.80%, but this time we have got only 88.00% because of the significant value of 2.
Things to Remember Here
- We must remember that only numerical values work for this function.
- If the significance value is <=0, we will get #NUM! Error.
- The Array and the “x” value should not be empty.
This article has been a guide to Excel PERCENTRANK. Here, we discuss how to calculate the PERCENTRANK for numbers or scores of students using the PERCENTRANK function along with examples and downloadable Excel templates. You can learn more about Excel functions from the following articles: –
- Percentage Change FormulaPercentage Change FormulaPercentage Change can be defined as a % change in value due to changes in the old number and new number and the values can either increase or decrease and so the change can be a positive value (+) or a negative value (-).
- How to Calculate Percentage Increase in Excel?How To Calculate Percentage Increase In Excel?Percentage increase = (New Value - Old Value)/ Old Value. Instead of showing the delta as a Value, percentage increase shows how much the value has changed in terms of percentage increase.
- Percentile Rank FormulaPercentile Rank FormulaPercentile rank formula is used to give rank percentile of a given list, in normal calculations we know the formula is R = p/100(n+1), in excel we use the rank.eq function with the count function to calculate the rank percentile of a given list.
- Calculate the Percent Change in ExcelCalculate The Percent Change In ExcelFor calculating the percentage change in MS excel, at least two values from different periods are required to apply the formula. Percentage Change = (New Value – Old Value) / Old Value OR Percentage Change = New Value / Old Value – 1
- Heat Map in ExcelHeat Map In ExcelHeat map represents data in different colors which shows area of the data that needs to be focused. The intensity of the colors show the impact of the data series over the whole data set.