How to rank in Excel using the RANK function - Ranking from highest to lowest.

When working with statistical tables in Excel and needing to rank data or growth surveys, we need to use the RANK function. The RANK function helps you rank data to determine which data ranks highest or lowest. Users can rank data from highest to lowest or lowest to highest. Using the RANK function is relatively easy, similar to other basic Excel functions. This article will guide you on how to use the RANK function in Excel.

What is the RANK function?

The RANK function is classified as a statistical function in Excel. It returns the statistical rank of a given value within a given array of values. Therefore, it determines the position of a particular value within an array.

The RANK function formula is as follows:

=RANK(number,ref,[order])

The RANK function uses the following arguments:

1. Number (required argument) - This is the value we need to find the rank for.
2. ref (required argument) - Can be a list or an array or a reference to numbers.
3. order (optional argument) - This number specifies how the ranking will be performed (ascending or descending order).
   
   1. 0 - used for descending order
   2. 1 - used for ascending order
   3. If we omit the argument, it will take the default value of 0 (descending order). The function will take any value other than 0 as 1 (ascending order).

Before proceeding, you should know that the RANK function has been superseded by RANK.EQ and RANK.AVG. To ensure backward compatibility, RANK still works in MS Excel 2016 (the latest version), but it may not be available in the future. If you enter this function in MS Excel 2016, it will display a yellow triangle with an exclamation mark.

As a worksheet function, RANK can be entered as part of a formula in a cell of a Microsoft Excel worksheet. To understand how to use the function, consider a few examples in the following section.

Instructions on using the RANK function in Excel

We will apply this to the score table below, requiring us to rank the scores using the RANK function.

1. Arrange them in descending order (from highest to lowest).

Step 1:

In the first cell of the Ranking column, enter the formula = RANK(B3,$B$3:$B$7,0) .

In there:

1. B3 is the number whose rank we need to find, which is the score of the first student.
2. $B$3:$B$7 is a list of numbers representing the Score column.
3. 0 means the ranking is in ascending order (the highest score will be ranked 1st).

Step 2:

After pressing Enter, the result will be 3, meaning Tran Ha An is ranked 3rd with 20.1 points. In the first result cell in the Ranking column, scroll down to the remaining cells to see the rankings for the rest of the participants.

In the case of duplicate numbers, they will have equal rankings and affect subsequent rankings. For example, if the number 22 appears twice, it will both be ranked 2nd. And the number 20.1 will always be ranked 4th, with no 3rd place.

2. Arrange them in order from lowest to highest.

In this ranking, the order needs to be reversed, meaning whoever has the highest score will be ranked last on the list. Here, we will replace the number 0 with the number 1.

We will also enter the formula in the first ranking cell as =RANK(B3,$B$3:$B$7,1) and then press Enter.

The result will be as shown in the image below.

Now you know how to use the RANK function in Excel to rank data, from highest to lowest or lowest to highest. Using the RANK function saves time compared to manually searching for the highest or lowest ranked data.

Good luck with your project!