Quick, clean, and to the point

How to get nth values with SMALL and LARGE

How to get nth values with SMALL and LARGE

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. 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 5 test scores. Lets use the LARGE function to extract the best 3 scores for each student.

To get top values, use LARGE. 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 5 scores for 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 to change k as needed.

When I copy all 3 formulas down, we get the best score for each student in the list.

So, let me show you a couple tricks formula handling k in this situation...

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

That lets me copy the same formula everywhere.

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

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

Then I can use this 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 best 3 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 E using the rank function, which I'll leave for reference

I also have named ranges for "names" and "times", to make formulas easier to read.

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

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

So, you might wonder how we can get a name for each of these times?

For that, assuming no duplicates, we can use INDEX and MATCH.

MATCH will give us the position off each time in the list.

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


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.