Excel LARGE function with formula examples to get n-th highest value

3 things to know about LARGE function

Before we get to more practical things and start building our own formulas, please pay attention to 3 simple facts that explain the essentials:

1. The LARGE function processes only numeric values. Blank cells, text, and logical values are ignored.
2. If array contains any errors, an error is returned.
3. In case array contains n values, LARGE(array,1) will return the maximin value, and LARGE(array,n) will return the minimum value.

How to get top N values in Excel

To get the largest 3, 5, 10, etc. values with a single formula, carry out these steps:

1. Type the positions of interest in separate cells. These numbers will be used as k values.
2. Make a LARGE formula applying an absolute range reference for array ($B$2:$B$10 in our case) and relative cell reference for k (D3).
3. Enter the formula in the topmost cell, and then drag it down to the below cells. Done!

As an example, we are going to find the top 3 scores in the table below. For this, we type the numbers 1, 2 and 3 in D3, D4 and D5, respectively, and enter the following formula in E3:

=LARGE($B$2:$B$10, D3)

Drag it through E4, and you will get this result:

Instead of typing the positions on the sheet, you can use the ROWS function with an expanding range reference to generate the k values automatically as explained in Excel formula to find top N values in a list.

=LARGE($B$2:$B$10, ROWS(B$2:B2))

How to sum or average largest N values

To sum top n values in a data set, you can use LARGE together with either SUMPRODUCT or SUM in this way:

Or

To average the highest n values, combine the AVERAGE and LARGE functions:

To see how it works in practice, let's find an average of the top 3 scores in our sample table. For this, we are using this formula:

=AVERAGE(LARGE(B2:B10, {1,2,3}))

To add up the highest 3 scores, the formula is:

=SUM(LARGE(B2:B10, {1,2,3}))

How these formulas work:

Normally, the LARGE function returns a single value based on the k number. In these formulas, we supply an array constant like {1,2,3} for the k argument forcing it to return an array of values. That array goes to the outer function to be summed or averaged.

Get data associated with the n-th largest value

To retrieve information relating to the largest values, just nest the LARGE function in the canonical INDEX MATCH formula:

Where:

• Return_array is a range from which to extract matches.
• Lookup_array is a range of numbers to rank from highest.
• N is the position of the largest value to search for.

For example, to get the name of a student who did best on the exams, nter this formula in F3:

=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, $D3), $B$2:$B$10, 0))

Where A2:A10 is the return array (names), B2:B10 is the lookup array (scores) and D3 is the position from largest.

To find out who has the 2^nd and 3^rd highest scores, copy the forma to F4 and F5:

LARGE formula to sort numbers descending

To quickly sort a list of numbers in Excel 365, you can use the new SORT function. In Excel 2019, 2016 and earlier versions that do not support dynamic arrays, we have to rely on the good old LARGE function to sort descending and SMALL to sort ascending.

For this example, we will sort numbers in A2:A10 from highest to lowest. To have it done, we'll again need the ROWS function with an expanding range reference to increment the k argument by 1 with every row:

=LARGE($A$2:$A$10, ROWS(A$2:A2))

The above formula goes to the topmost cell (C2). And then, you drag it through as many cells as there are numbers in the original list (C2:C10 in our case):

LARGE formula for dates and times

As you know, dates and times in Excel are numeric values: dates are stored as integers and times as decimals. What does that mean for us? The LARGE function calculates date and time values in exactly the same way as it does numbers. In other words, the formula you used for numbers will work for dates and times too!

Assuming you have a list of dates in B2:B10, the following formula will return the most recent N dates, depending on how many cells you copy it to:

=LARGE($B$2:$B$10, ROWS(B$2:B2))

The same formula can also find the longest 3 times:

Get a future date closest to today or specified date

This example shows a more specific usage of the Excel LARGE function with dates.

From a list of dates in B2:B10, suppose you wish to return a future date closest to today. To accomplish the task, we'll use the COUNTIF and TODAY functions together to calculate a value for the k argument of LARGE:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY()))

To find the next but one date, the formula is:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY())-1)

To find a date that comes right after a given date, input the target date in some cell (E3 in this example), and concatenate that cell reference in COUNTIF's criteria:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1))

In situation when a date matching your criteria is not found, you can use the IFERROR function as a "wrapper" to catch an error and replace it with whatever text you see fit:

=IFERROR(LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1)), "Not found")

How this formula works:

In essence, you use the COUNTIF function to count how many dates in the list are greater than today or a specified date. That count is the k value the LARGE function needs.

At the moment of writing, today's date was October 7, 2020. The COUNTIF function with ">"&TODAY() for criteria determined that in B2:B10 there are 4 dates greater than 7-Oct-2020. Meaning, the 4th largest date in the list is the closest future date we are looking for. So, we plug COUNTIF into the 2^nd argument of LARGE and get the desired result:

=LARGE($B$2:$B$10, 4)

The next but one date is the 3^rd largest date in our case. To get it, we subtract 1 from COUNTIF's result.