How to get nth values with SMALL and LARGE (video)

Abstract

In this video we'll look at how to calculate the "nth" smallest or largest values in a range using the SMALL and LARGE functions. For example, the 1st, 2nd, and 3rd smallest or largest values.

Transcript

In this video we'll look at how to calculate the "nth" smallest or largest values in a range using the SMALL or LARGE functions. This would be, for example, the 1st, 2nd, and 3rd smallest or largest values.

In this first sheet, we have a list of students with five test scores. I'll use the LARGE function to extract the three best scores for each student.

To get top values, use the LARGE function. The LARGE function needs an array and something called "k" which you can think of as "nth".

So, in this case, I'll just point at the five scores for the array and enter "1" for k. This gives us the top score for the first student.

Now, to copy the formula across, I need to first lock the column references for the range so that they don't change.

Then I can copy the formula across and come back and change "k" as needed.

When I copy all three formulas down, we get the three best scores for each student.

So, let me show you a few tricks for handling "k" in this situation.

Since we have a number already in the column heading, I could just use that in the formula and lock row 5.

That lets me copy the same formula everywhere.

Also, I could use the COLUMN function to dynamically generate k. COLUMN shows us that we're in columns 9,10, and 11.

So, by subtracting "8" we can generate the numbers "1," "2," and "3".

Then I can use this same construction directly in the formula everywhere.

Now let's take a look at the SMALL function.

Here we have a list of race results, and I want to extract the three best times overall. The best times are the shortest times, so this is a job for the SMALL function.

I've already got a rank in column D using the RANK function, which I'll leave for reference.

I also have named ranges for "names" and "times." These will make the formulas a bit easier to read.

The SMALL function works just like LARGE, so I'll use "times" for the array, and then just pick up "k" from the column to the left.

When I copy the formula down, we get the three shortest, or best, times.

So, you might wonder at this point how can we get the name for each of these times? And for that, we can use INDEX and MATCH.

MATCH will give us the position of each item in the list.

So I can just take that MATCH formula, and use it directly inside of INDEX to fetch the name.