RANK

Summary

If you have a range of values, the RANK function tells you where in that range a specific number sits. It can either tell you it's the nth biggest number in the range. Or it can tell you it's the nth smallest number in the range.

The formula only works on numeric values. If text values appear in the range, then these values are ignored. If the formula attempts to evaluate the rank of a text value, then it will result in a #VALUE! error.

Syntax

=RANK(Cell,Range,Value)

Cell is the number that you want to find the rank of. It doesn't need to be a number physically within the range of values you're ranking it against. But that exact value must appear in the range. Otherwise, the formula will yield #N/A.

Range is the range that you want to rank the number within. It must be a continuous range, but can be square—it need not be a single row or column of data.

Value is a number that determines the order in which the item's ranking is given.

• Value = 0. The formula will tell you how big the cell is. I.e. the cell is the nth biggest in the range
• Value is anything other than 0. The formula will tell you how small the cell is. I.e. the cell is the nth smallest in the range.

Examples

Below are some examples of the RANK formula at work. It assumes the range A1:A5 contains the values 1, 2, 3, 4 and 5.

=RANK(2,A1:A5,0) gives the value 4. The number 2 is the fourth biggest in the range

=RANK(2,A1:A5,1) gives the value 2. The number 2 is the second biggest in the range

=RANK(7,A1:A5,1) gives the value #N/A. The number 7 does not appear in the range, so the formula fails.

• Tweet