The LARGE function will return the largest specified number from a given range or data set. For example, LARGE can return the 2nd largest value from a column or the 4th largest value from an entire table.
Contents:
Syntax
= LARGE(array, k)array = a range, dynamic array, table, row, or column to be evaluated
k = the kth largest number to be return from the array
Explanation
The LARGE function is part of the "Statistical" group of functions within Excel.
This function is different from MAX as LARGE allows you to specify which largest (1st, 2nd, 3rd... largest) number you wish to return. MAX will always return the very largest value.
To specify which largest number to return, the k argument must be changed. This argument works in descending order from the largest number in the array. For example, k = 1 would return the largest number in the array while k = 2 would return the second largest number, 3 being the third largest number, and so-on.
The array argument can be a variety of inputs. Any cell range, column, row, or table, can be fed into the function.
This function is fully compatible with dynamic ranges which is especially useful as it gives much more flexibility and adds to the capability of the function as shown in the below examples
Notes:
- If the array contains any blank cells or cells containing text, those cells will be ignored when evaluating the largest number
- If there are no numerical values, the function will return a #NUM! error
- The k argument must be positive and not be greater than the number of items in the array, if they are, a #NUM! error will be returned
Examples
1. How to Return the Nth Largest Value in a Column
To return the nth largest value from an entire column, we can use the LARGE function, and use the whole column as our array.
By referencing the entire column, or C:C in this example, the LARGE function will look at every value in the column and return whichever Nth largest value we need.
In this example, we are returning the 2nd largest sales quantity value from the C column.
= LARGE(column, nth largest)= LARGE(C:C,F2)
2. How to Return the Nth Largest Value in a Row
The same principles from the above example can be applied here. To return the Nth largest value from a row, we can use the LARGE function, and use the whole row as our array.
By referencing the entire row, or 3:3 in this example, the LARGE function will look at every value in the given row and return whichever Nth largest value we need.
In this example, we are returning the 2nd largest sales quantity value in row 3.
= LARGE(row, nth_largest)= LARGE(3:3,F2)
3. How to Return the Nth Largest Value in a Table
The LARGE function can even use entire tables as its array.
In this example, we have a table named "WeekendScoreTable", which contains a list of players, each with a Saturday and a Sunday score. We can plug this table name into the function for our array argument.
When using the entire table as our array, the function will look at every single value held in the table and return the Nth largest numerical value. Any non-numerical values will be ignored.
= LARGE(table_reference, nth_largest)= LARGE(WeekendScoreTable, H3)4. How to Calculate the 1st, 2nd, and 3rd Place Scores
The LARGE function can also be a great way to calculate the top few scores from any given list of scores.
= LARGE(score_range, place)= LARGE($C$3:$C$11, E3)We will want to use our scoring data as our array, in this example C3:C11. Here we want to make these absolute references so we can quickly copy down the first formula in F3 down to the other cells. If we don't and leave it as C3:C11, the cell range will move down as the formula is copied.
From there, we can reference the places we want to return the score from as or k argument.
This process can be repeated with as many scores and places as needed.
5. How to Use the LARGE Function with Criteria
We can get a little bit more complicated and add on a criteria to the LARGE function. This way the function will only look at and return values that match our set criteria.
To do this, we first need to filter out our data for any value matching our criteria. Let's use the below "ScoreTable" and say we want to pull out the 2nd largest value from everyone in California.
First we can must use the FILTER function to filter the existing table:
= FILTER(ScoreTable, ScoreTable[State] = "CA")
Now that we have the reduced the table down to only rows where the state is equal to "CA", the next step is to then to reduce this down further to just the scores. We can accomplish this using the INDEX function.
= INDEX(FILTER(ScoreTable, ScoreTable[State] = "CA"),, 3)
In this step we fed our previous filtered data into the INDEX function, and using that function, are able to pull out the 3rd column, as seen by the "3" in place of the [column_num] argument. Everything else can be blank as we only want to pull out the column containing the scores.
The last step is to tack on the LARGE function, using the filtered and indexed data set as our array.
The final product will look something like this:
= LARGE(INDEX(FILTER(array, criteria_array = criteria),, return_column), nth largest)= LARGE(INDEX(FILTER(ScoreTable, ScoreTable[State] = "CA"),, 3), 2)
6. How to Use the LARGE Function to Return Adjacent Cell
Sometimes you might not want to return the largest value, but instead a cell in another column related to that value.
To return adjacent cells using the LARGE function, we need to combine LARGE with both the INDEX and MATCH functions.
Essentially, we are going to use the MATCH function to read which row the nth largest value is held in our data range, and then feed that row position into the INDEX function to return a value in a different column.
Lets go through the steps. First we start with the MATCH and LARGE function.
= MATCH(LARGE(C3:C11,E3),C3:C11,0) Here we are using the value returned by the LARGE function as our lookup value, and are returning the relative row position of it in our data range (C3:C11). In this case the largest number is in the 9th row position.
Next we need to use INDEX. Because we want to return the Name and not the Score, and we know that the relative row position of one is directly tied to the position of the other, the 9th score position will always match with the 9th name in the list.
Combine everything into one formula and you get:
= INDEX(adjacent_cells, MATCH(LARGE(value_range,nth_largest), value_range, 0))= INDEX(B3:B11, MATCH(LARGE(C3:C11,E3), C3:C11, 0))7. How to Use the LARGE Function with Multiple Ranges
We can tack on multiple non-contiguous ranges to the LARGE function, allowing for more flexibility.
In place of a standard range of cells for your array, Excel can read multiple disconnected ranges if they are put inside parentheses.
For example, instead of being limited to one selected range "C3:C5", we can use two separate ranges as our array "(range_1, range_2)" like so:
= LARGE((range_1, range_2, range_3,...),nth_largest)= LARGE((C3:C5,F3:F5),I2)
8. How to Use the LARGE Function While Ignoring Duplicates
If there are any duplicates in your values, you may also want to ignore or strip out any duplicate values as duplicate values could skew the results you're looking for.
This can be accomplished by using the UNIQUE function. By using the UNIQUE function on our data range before using the LARGE function, we will only pass on unique values to the LARGE function.
Lets look at this example. We want to return the 4th largest number from the list in column B, but don't want to consider duplicate values. If we did, 5 would be returned, which is incorrect.
We first must use the UNIQUE function on our data range. This will give us an array of distinct values.
We can then feed this array into the LARGE function, using the UNIQUE array as our array argument.
Combine them together and you get:
= LARGE(UNIQUE(array), nth_largest)= LARGE(UNIQUE(B3:B11),E2)