How to use the rank formula in excel?

The rank formula in excel is a very useful and handy tool to get a rank either in descending or ascending order.

What is the Rank Formula in Excel?

The RANK function is categorized as an Excel Statistical Function. The function returns the statistical rank of a given value within a supplied array of values. It is used to determine the position of a specific value in an array.

The Syntax

• Rank formula in excel syntax

          =RANK( number, ref, [order])

• Parameters

1. Number – A number for which the rank is to be found
2. Ref – This can be a list, or an array, or a reference
3. Order – The order in which the numbers should be ranked. This is an optional parameter

• Output of rank formula

          The RANK function returns a numeric value.

• Note

1. If an order is 0, the numbers are ranked in descending order.
2. If an order is not 0, the numbers are ranked in ascending order.
3. If the order parameter is omitted, it assumes the order is 0

• Applies to:

          Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007.

Example 1 :for using the rank formula in excel

Assume we are given a list of employees with their respective medical reimbursement expenses. We wish to rank them according to total expenditure.

The result we get is shown below:

Types of Rank formulas in excel

There are various kinds of rank formulas in excel, so let me quickly take you through them.

Rank.EQ Formula in Excel

Now, there are so many people who get confused with the explanation written on this function. Also, especially the explanation given by microsft itself.

However, let me clarify that, the Rank.EQ is nothing but the rank function itself. Also, the plain and simple rank function in excel is shown with a yellow exclamation mark. Which means, that it’s the older version and rank. eq formula is the latest formula in excel.

Rank.Avg

Now, the rank average function builds on the rank. eq function, for its shortcomings. Which, is nothing apart from duplicate rankings.

For eg; using the previous example you can see, that video 1 & 2, have the same rank. However, all that the rank.avg will do is to to give your duplicate values an average rank.

So let’s apply the rank. avg formula in excel

Disadvantages of Rank formula

The rank function, as we saw above suffers from giving duplicate values, the same rank. However, ideally, that shouldn’t really be a disadvantage, because we are only considering one set of data, which is views.

Now, what if we had one more parameter? It is still possible to get duplicate ranks, however, the probability of getting duplicate rankings reduces as we keep on increasing parameters.

Solution for Duplicate Ranks

So let me illustrate this point.

So, now you can see that after adding the watch time as a second parameter the ranks have changed.

So, once you get the two average ranks then you can average it and get the final rank based on two parameters.

Other Methods for Ranking in Excel

Now, the only advantage that the rank formula provides, is the integer value of the rank. Else the easiest method to rank is using the sort function in excel. However, as you can try it for yourself and see that

Conclusion

The rank formula in excel is a useful tool for dealing with data, which needs a formula instead of just table sorting. Mostly, this can be useful, when you are creating custom dashboards instead of pivot dashboards. So, a good tool in your excel kitty to know, in situations where you need them.

Further Readings

• Free Excel Course
• Mod Function in excel
• Powerpoint shortcuts
• Advance Excel Course