nth smallest value

In this example, the goal is to extract the 3 best race times for each name from the 5 race times that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the fastest time, the 2nd fastest time, and the 3rd fastest time. This problem can be solved with the SMALL function.

Note: I don't know why the second argument for SMALL is called "k" . In this article, I use "n" instead, since "nth" is easier to understand than "kth".

SMALL function

The SMALL function can be used to return the nth smallest value in a set of data. The generic syntax for SMALL looks like this:

=SMALL(range,n)

where n is a number like 1, 2, 3, etc. For example, you can retrieve the first, second, and third smallest values like this:

=SMALL(range,1) // first smallest
=SMALL(range,2) // second smallest
=SMALL(range,3) // third smallest

The SMALL function is automatic. Simply supply a range and a number that indicates rank. The official names for these arguments are "array" and "k". To illustrate with a simple example, below we use the SMALL function to get the 3 best times in column C. The formula in F5, copied down, is:

=SMALL(data,E5)

For array, we use the name range data (C5:C16) and the value for k (n) is pulled from column E. As the formula is copied down, it returns the 3 fastest times.

Note: Excel times are just numbers underneath the formatting, so smaller numbers mean shorter times. This is why we can use the SMALL function to get the "best" times.

Mixed references

In the worksheet shown at top, we can use SMALL to get the 3 fastest times for Hannah like this:

 =SMALL(C5:G5,1) // 1st fastest time
 =SMALL(C5:G5,2) // 2nd fastest time
 =SMALL(C5:G5,3) // 3rd fastest time

The main challenge in the worksheet is to create the syntax needed to copy the formula across the range I5:K16. In the example shown, this is accomplished with the formula in cell I5:

=SMALL($C5:$G5,I$4)

This is a clever use of mixed references that takes advantage of the fact that the numbers 1, 2, and 3 are already in the range I5:K5, so that they can be plugged into the formula directly as n:

• The value given for array is the mixed reference $C5:$G5. Notice that the columns are locked, but rows are not. This allows the rows to update as the formula is copied down, but prevents columns from changing as the formula is copied across.
• The value given for k (n) is another mixed reference, I$4. Here, the row is locked so that it will not change as the formula is copied down. However, the column is not locked, allowing it to change as the formula is copied across.