RANK function is used to compare numbers to other numbers in the same list. RANK function will tell you the rank of a given number from a range of number in ascending or descending order.
Syntax
=RANK(number or cell address, ref, (order))
This function is used at various places like schools for Grading, Salesman Performance reports, Product Reports etc.
Let’s understand it better with the help of an example.
We have a list of 10 students and their test scores from cell B2 to B11 as shown below:
To find the rank of the first student’s score in cell B2, enter this formula in cell C2:
=RANK(B2,$B$2:$B$11)
Just double click the fill handle and it will copy the formula from cell C2 down to cell C11. The scores will be ranked in descending order as shown below:
There are 3 arguments for the RANK function:
Using 0 (zero) or leaving this argument empty will give the ranks in descending order.
In our example, we left the order argument blank in order to find the rank in descending order.
The formula used is =RANK(B2,$B$2:$B$11)
For ranking them in ascending order, we need to type 1, or any other number except zero.
For comparing golf scores, we could type 1, to rank in ascending order.
=RANK(B2,$B$2:$B$11,1)
A question might arise here that what will happen to the ranking if some of the scores are similar in other words scores tie with other scores?
Microsoft Excel will automatically skip subsequent numbers to show the correct rank.
As we can see in our example, the last two scores in the list are the same. Both the students Ivy and Joe are ranked as 4th.
The score for Ed is 36. Excel has ranked it 6th instead of 5th because there are 5 students ahead of Ed, two of them having 4th rank.
If you have any queries please post them in comments. I will be more than happy to help.
To simplify the process, we have created a simple and easy UK VAT Taxable Turnover…
Every business has to register for VAT with HM Revenue and Customs if their VAT…
We have created the UK VAT Dual Currency Invoice excel template with predefined formulas that…
We have created a simple and easy UK VAT Purchase Register Excel Template with predefined…
We have created a simple and easy UK VAT Sales Register Excel Template with predefined…
We have created a ready to use UK VAT Progress Billing Invoice template in excel…