How to use the RANK function in excel
Excel rank function helps in comparing a particular value among all values available in the range. This is very useful when we have performance of employees available in a column and we we wanted to show position of that employee among all employees in that department especially when we don’t want to sort the performance of employees.
There are two rank formula in excel available. First Rank.AVG and second Rank.EQ. Both these rank formula provides ranking, just the difference is when a duplicate entry comes RANK.AVG provides average ranking while RANK.EQ returns the higher rank number of that set of values.
Let us first understand how the syntax of excel rank function from the below image.
In the above example, the inputs required are number for which we are searching rank, ref is the series or list of data, and [order] means if we wanted to rank in descending order or in ascending order. It means when we rank 1-n when the value is descending. We will use the value of 0 or 1 for descending or ascending respectively.
RANK.EQ used in the below example and we used C4 in the number input, $C$4:$C$11 for ref. You may notice that there is $ used in the ref as we have to scroll the formula to the end, it fixed the range. Finally, we are using 0 for descending ranking.
In the above example, we can see in the red highlighted box how the formula has been used. One more point to notice here is how it ranked value 98 as it has two entries. We can see that it give both values a rank of 1 which the higher rank. Also, it is to be noted that rank 2 is missing. So from this example, it is now clear how RANK.EQ works.
RANK.AVG works the same as RANK.EQ but there is a small difference in the returned value which we can see in the below example.
In the above example, we used Rank.AVG to find out rank. What the difference we found here is the ranking of value 98. Here we see rank is given 1.5 to each value, which means the average of rank 1 and rank 2 e.g 1.5. Also rank 1 and 2 is is not given to any value.
You can read out more on rank function in excel from the below articles
https://www.excel-easy.com/examples/rank.html
https://www.contextures.com/excel-functions-rank.html
https://exceljet.net/excel-functions/excel-rank-function
Also, You can check this video for more clarity.