rank of number in a list in Excel

Q32. In Excel, how do I get the rank of a number in a list of numbers?

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

excel rank of number in a list

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.

excel excel rank of number in a list

 

You can find similar Excel Questions and Answer hereunder

1) I conducted a test and the test scores are available - how can I get Percentile rank of students?

2) Can I determine the number of times a character appears in a cell using formula?

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

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

5) In a Table , how can I add a Total Row that displays the maximum value in a column of numbers?

6) How can I get row count of filtered data?

7) Generate a list of unique values in Excel

8) How can I find the number of working days between 2 dates given a holidays list?

9) How can I create dependent drop down lists?

10) Concatenate number with text but keeping in number format in Excel

11) How can I set up a drop down list?

12) Millions thousands custom number formatting in Excel

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

14) How do I get the 2nd highest number in a range of numbers?

15) How can I find number of months that has elapsed given 2 dates?