Tips for Excel, Word, PowerPoint and Other Applications

RANK, LARGE, and SMALL - more useful functions you've never heard of

Why It Matters To You

Need to rank spend by business segment, by supplier, by GL code? Does your boss keep asking, what are the top 10 expense lines? Need I say more?. These functions help you to quickly highlight the top n or bottom n values in a data set, and are really useful to evaluate RFPs, RFIs, or compare other benchmarking information.

How To ...

Open up the sample file, rank.xls. This is a simple table of bids from 10 suppliers that I want to rank and sort. I'll use this to illustrate how you might use the 3 functions.

RANK

RANK returns the relative rank of a given value from an array of numbers. Rank can be specified in ascending or descending order. For example, the formula =RANK($C13,$C$13:$C$22,0) will look at cell C13 and tell you how it is ranked, in descending order (default), out of all the values in the range C13:C22.

Add a value of 1 to the Order field, to rank in ascending order.

LARGE and SMALL

LARGE and SMALL look at array and return the Nth largest or smallest value in an array, where N is a number you specify. For example, the formula =LARGE($C$13:$C$22,2) will return the second largest value in the range C13:C22.

As you would expect, the formula =SMALL($C$13:$C$22,2) will return the second smallest value in the same range.

These three functions can be used to rank order or identify supplier response and help you quickly identify a number of low bidders. For example, if I want to identify the lowest three highest bidders or the 5 lowest bidders, I could use a table and sorting criteria like the following:

Filtering Criteria

First, I use variables (blue-shaded cells) to set the number of low or high bids I want to see. I use variables (as always) because you may want to change your sorting criteria dynamically. Play around, change the numbers, and see what happens in the Supplier Responses Table.

Supplier Responses

The data in the blue cells is the raw bid data. Next, I use the RANK or the SMALL and LARGE functions with a simple IF statement to order my bids or pull out the top x or lowest y values. Here's what the formulas look like:

The RANK formulas are pretty straight forward.

=RANK($C13,$C$13:$C$22,0)
=RANK($C13,$C$13:$C$22,1)

The SMALL and LARGE formulas simply compare the decreasing RANK value against the minimum rank (LARGE) or low cut off (SMALL) value and either returns the number or leaves the cell blank.

=IF($D13<=$C$6,LARGE($C$13:$C$22,$D13),"")
=IF($E13<=$C$7,SMALL($C$13:$C$22,$E13),"")

You can find all 4 of these examples in the sample file.

Ranked ordered Values

This last example just allows me to rank order the bids in descending order, without having to sort my underlying data set, which could be large and ugly. The only tricky part is when two responses are ranked identically, then the ranking spreadsheet will miss a value.

Notes

Last updated	3/4/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	Conditional Formatting: Formula Is