# Using Excel's RANK function

Excel contains a number of useful functions for ranking values in a range — in other words, figuring out which value is the highest, the second highest, etc. But there are a number of functions used for ranking, and the difference between them can often be confusing. In this article, we'll explain Excel's

## Defining the problem

Before we begin, let's look at an example of when the

Take a look at the spreadsheet below, which shows SnackWorld's number of orders by customers this past March. Let's say that a SnackWorld analyst wanted to add a column to the table showing each customer's rank — in other words, who is the best customer, the second-best customer, etc., by number of orders placed. How would we do this?

## The basic RANK function

We can solve our analyst's problem using the

=RANK (number ,range ,order (optional) )

Let's take a look at the

=RANK (C3 ,$C$3:$C$8 )

Output:5

For the first cell, the

There are a couple of additional points to note here. First of all, notice that we've used relative and absolute cell references in our formula to lock the range

Second, note that the input range to our formula,

What if we wanted to rank our customers not by *number* of orders, but by order *value*? We could easily do that by switching our

=RANK (D3 ,$D$3:$D$8 )

Output:4

The above formula outputs

We could also use the optional

=RANK (D3 ,$D$3:$D$8 ,1 )

Output:3

The above formula outputs

## RANK vs. RANK.EQ and RANK.AVG

So you've got a handle on

Also introduced in Excel 2010 was the

=RANK (C4 ,$C$3:$C$8 )

Output:3

Note that since our last example, the output for cell *average* rather than the *highest* rank in the event of a tie. The cells

## Breaking ties with the RANK function

As we've seen,

We can do this, but it requires expanding our spreadsheet a bit and using some tricky number manipulation. Read on to find out how!

Using our SnackWorld example from above, let's say that our analyst wants to rank customers by number of orders placed — but, in the event of a tie in number of orders placed, use the total order value as a tiebreaker. We'll start with the formula we used above to rank customers by number of orders:

=RANK (C3 ,$C$3:$C$8 )

Output:5

Next, we'll add a column to

=RANK (D3 ,$D$3:$D$8 ) / 100

Output:0.04

Next, we create a column that sums the two

=SUM (E3 ,F3 )

Output:5.04

Now, we have a list of

Finally, we run one last

=RANK (G3 ,$G$3:$G$8 ,1 )

Output:5

And there you have it! As you can see, breaking ties with the

Questions or comments on the

## Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on getting Excel to do your work for you.

- How to create
**beautiful table formatting**instantly... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...