RANK function - Rank function in Excel - Usage and examples

RANK function - Rank function in Excel - Usage and examples Picture 1

RANK function syntax and usage

Function syntax: = RANK (number, ref, [order]) .

Inside:

1. RANK: is the name of the function used to return the rank of a number in a list of numbers.
2. Number: is the number you want to find the rank.
3. Ref: is an array or reference to a list of numbers. Non-numeric values ​​in the array are ignored.
4. Order: optional value.
5. If Order is 0 or the user omits no fill, then the RANK function will sort in descending order (the largest number ranks 1).
6. If Order is a value other than zero, then the RANK function will sort in ascending order.

Note:

1. The RANK function arranges equal numbers of the same rank. However, the duplication will affect the ranking of subsequent numbers. For example, you have the sequence number: 1,2,2,3,4 and are arranged in ascending order, so the order of the number 1 in the series is 5.
2. In some cases, users need to determine rankings that take into account (peer). You can use a combination of COUNT and RANK rows as the example below that the Software Tips will give below.

For example

You have the sales table as the following image:

RANK function - Rank function in Excel - Usage and examples Picture 2

You need to rank the sales of your affiliates and your quarters in ascending rankings.

So in cell F3 you enter the formula: = RANK (E3, $ E $ 3: $ E $ 12,0) . And copy the formula for the remaining cells in column F. The result is as follows:

RANK function - Rank function in Excel - Usage and examples Picture 3

To rank in order from small to large, you change the above formula with:

So in cell F3 you enter the formula: = RANK (E3, $ E $ 3: $ E $ 12,1) .

RANK function - Rank function in Excel - Usage and examples Picture 4

Use a combination of rows COUNT and RANK to rank in descending and continuous order (including duplicate values). In cell F3, enter the formula: = RANK (E3, $ E $ 3: $ E $ 12) + COUNTIF ($ E $ 3: E3, E3) -1. Copy the formula for the cells in column F. The result is:

RANK function - Rank function in Excel - Usage and examples Picture 5

Above Software Tips showed you how to use the RANK function in Excel to look up the rank of a number in a series of numbers. Good luck!.