How to Use the RANK Function in Excel

RANK function syntax and usage

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

Inside:

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

Note:

• 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.
• 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:

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:

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) .

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:

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!.