Explanation
In this example, the goal is to retrieve the largest 3 (top 3) values in the named range data , which appears in the range B6:B16. The standard solution to get “nth largest values” is the LARGE function. However, one potential problem with LARGE is that it will return duplicate values if they are present in the source data.
Named range
For convenience, all values are in the named range data (B6:B16). Using a named range is entirely optional, but it’s a nice way to quickly try out a number of formulas without entering addresses and locking references.
LARGE function
The LARGE function is used to return the nth largest value in a set of data like this:
=LARGE(range,n)
where n is a number like 1, 2, 3, etc. For example, you can retrieve the first, second, and third largest values like this:
=LARGE(range,1) // first largest
=LARGE(range,2) // second largest
=LARGE(range,3) // third largest
The challenge in this problem is that LARGE will return duplicates. For example, because 100 is the top value in the data and occurs twice, LARGE will return 100 when n = 1 and when n = 2.
LARGE with UNIQUE
One easy way to solve this problem is to use the UNIQUE function inside of LARGE. This is the approach seen in the worksheet shown, where the formula in cell E5 is:
=LARGE(UNIQUE(data),D5)
The UNIQUE function simply returns UNIQUE values, so the formula is solved like this:
=LARGE(UNIQUE(data),D5)
=LARGE(UNIQUE({100;100;98;95;95;92;91;90;89;86;85;81}),D5)
=LARGE({100;98;95;92;91;90;89;86;85;81},1)
=100
In cell E6 when n = 2, we get 98:
=LARGE(UNIQUE(data),D5)
=LARGE(UNIQUE({100;100;98;95;95;92;91;90;89;86;85;81}),D5)
=LARGE({100;98;95;92;91;90;89;86;85;81},2)
=98
Without the UNIQUE function, LARGE would return 100 when n = 2.
Legacy Excel
In older versions of Excel that do not have the UNIQUE function, we need a different approach. One option is to use the MAX function with the IF function . We start off with MAX alone in cell E5:
=MAX(data) // returns 100
MAX returns 100, the largest value in data. Next in cell E6, we enter a different formula that uses the IF function to “filter” out previous values like this:
=MAX(IF(data<E5,data))
Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel.
This formula evaluates like this:
=MAX(IF(data<E5,data))
=MAX({FALSE;FALSE;98;95;95;92;91;90;89;86;85;81})
=98
Notice that the IF function converts the value of 100 (which occurs twice) to FALSE. In other words, values that have appeared previously are destroyed. The MAX function simply returns the maximum value in the remaining numbers. As the formula is copied down the column, the reference to E5 changes at each row and IF creates a new array that excludes the previous result, and MAX returns a new maximum value.
Explanation
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 .
