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:

## Arguments of RANK Function

There are 3 arguments for the RANK function:

- Number or cell address: In our example above the number to be ranked is in cell B2.
- Ref (range): We want to compare the number to the list of numbers in cells from B2 to B11.An absolute reference will be used ($B$2:$B11) instead of a relative reference (B2:B11), so that the referenced range doesn’t change when we copy the formula to the cells below.
- Order: This argument is optional. This argument will tell Excel to rank the list in ascending or descending order as required.

## Ranking in ascending or descending order

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 4^{th}.

The score for Ed is 36. Excel has ranked it 6^{th} instead of 5^{th }because there are 5 students ahead of Ed, two of them having 4^{th} rank.

If you have any queries please post them in comments. I will be more than happy to help.

karthic says

i used the same rank command to rank the students passed in exam but the final rank is increased by one number(i.e, it should be 8 instead it shows 9). i dont know why can you clarify me.

Fahim Lashkaria says

It seems that there might be an issue in the formula. Either copy and paste the formulas here so that I can see or send me file on my email.

karthic says

Dear sir thanks for your reply. i cant see your mail id to send the file.

This is the formula i used to calculate the rank

=IF(AND(D2>34,E2>34,F2>34,G2>34,H2>34,I2>34),RANK(J2,$J$2:$J$37,0),”–“)

it checks six subject marks >34 then it ranks according to the total

Fahim Lashkaria says

info@exceldatapro.com

karthic says

i am waiting for your reply sir