How to calculate rank percentile of a list in Excel?

You could use a data collection that contains values and can be filtered based on certain criteria. For the sake of this illustration, we will make use of a list of the populations of the world's capital cities. Calculating the percentile rating of each continent's respective capital cities is going to be a difficult task for us.

If you make regular use of Conditional Formatting's Icon Set, then you should be aware that the icons in the set are determined by the percentile of each value. Having said that, are you familiar with the formula that determines the rank percentile of each number in an Excel list?

Step 1

You can see that although it is simple to see that Buster and Carter has the highest grades overall, it is tough to understand where other cities rank in terms of the mark on each student. This is something that we can observe. They are all mixed up with one another.

Step 2

You may use a formula to get the rank percentile of the data included in a list.

=RANK.EQ(B2,$B$2:$B$6,1)/COUNT($B$2:$B$6)

All of the students on the list are included in the range that goes from B2 to B6. For the purpose of locking the range in marks, it contains dollar signs ($) placed in front of the column letters and the row numbers. We can just replicate the algorithm down the row in order to calculate the grades for each individual student.

Choose a cell that is empty so that you may enter the rank percentile there.

Step 3

To compute all of the rank percentiles, hit the Enter key and then drag the fill handle down.

Step 4

The formula starts in cell B2, which is the first cell in the data list that you use; the data list that you use is from B2 to B6.

Step 5

If the results of calculations are not presenting themselves as percentages, please pick the results of the calculations, then click the Home button, choose Number, and finally choose the Percent Style option.

The final screen will look like the one shown below −

Conclusion

In this tutorial, we used a simple example to demonstrate how you can calculate the rank percentile of a list in Excel.