Excel tools to demonstrate

RANK, LARGE, SMALL Functions

If we simply want to rank a set of values from 1 to N, where N is the number of values, Excel offers the RANK function. We can either rank them from highest to lowest or vice versa. Of course, an alternative is to sort the numbers. If for any reason we don’t want to sort, then we can use the RANK function.

To use the RANK function:

Enter the formula =RANK(value,datarange, order), where value is a single value in the datarange, and order is an optional argument. If order is missing (or 0), rank 1 corresponds to the largest value; if order is 1 (or any other nonzero value), rank 1 corresponds to the smallest value.

Typically, we start with a data set in a column. Then we use the RANK function to rank the first value and copy it down (with datarange absolute) to rank all of the others. If two or more values are tied, they share a rank.

Try it! Rank the values in A1:A10 from largest to smallest in column B. Then rank them from smallest to largest in column C. How does RANK deal with the tied 55’s? (Scroll to the right for answers.)

The LARGE and SMALL functions are less well-known Excel functions, but they can be useful. Let’s say we have a data set with 250 values and we want the 10 largest values. Then we can find these with the LARGE function, using arguments 1 through 10. Similarly, we can use the SMALL function to find the 10 smallest values.

To use the LARGE function:

Enter the formula =LARGE(datarange,k), where k is any integer from 1 to the number of data values. This returns the kth largest value.

To use the SMALL function:

Enter the formula =SMALL(datarange,k), where k is any integer from 1 to the number of data values. This returns the kth smallest value.

Typically, we would enter a sequence of values of k in some column, enter the LARGE or SMALL formula for the first of them, and then copy down.