Excel Made Easy

Rank of number in a list in Excel

For example, I need to determine the rank of students based on marks in the Table below.

To do it in Excel, here is the answer:

a) Add a column for Rank as shown below.

Enter the formula =RANK.EQ(AE3,$AE$3:$AE$12,0) as shown below where AE3 corresponds to the cell containing the individual student's mark and $AE$3:$AE$12 corresponds to the range containing marks for all students.

There are 2 options to deal with the scenario when 2 students have the same mark (say both can be ranked 3)

- RANK.EQ (Both are assigned the same rank and next rank is skipped. In the example case, it would be 3 for both students and rank 4 is skipped in the list).

- RANK.AVG (Both are assigned the same average rank and both ranks are skipped. In the example case, it would be 3.5 for both students and ranks 3 and 4 are skipped in the list)

The third argument in the formula above "0" determines whether the ranking is in ascending order (1) or descending order (0).

b) Copy the formula for the rest of Rank Column.

You can find similar Excel Questions and Answer hereunder

1) Here an explanation about list box and how to control list boxes in Excel VBA

2) Generate a list of unique values in Excel

3) How to find the column number from the cell address in VBA

4) How can I generate random numbers between 2 limits?

5) How can I list all files in a folder using VBA?

6) How can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

7) Applying a countif formula only to visible cells in a filtered list in Excel

8) How do I change the rating numbers to rating labels?

9) How can I set up ListBox using VBA to allow users to select multiple values?

10) How can I create dependent drop down lists?