# How to use the LARGE function

The LARGE function calculates the k-th largest value from an array of numbers.

Use the LARGE function, for example, to extract the highest number, second highest, and third highest from a LARGE range of numbers.

#### Table of Contents

- LARGE function Syntax
- LARGE function Arguments
- LARGE function - example
- LARGE function - based on a condition
- LARGE function - based on criteria
- LARGE function - based on a list
- LARGE function - multiple source ranges
- LARGE function - based on a textstring
- LARGE function - calculate an average based on the three largest numbers
- How to extract the k-th largest number in a 3D range

## 1. LARGE Function Syntax

LARGE(*array*, *k*)

## 2. LARGE Function Arguments

array |
Required. Group of numbers for which you want to calculate the k-th largest value. |

k |
Required. The position in the group of numbers to return, sorted from the largest. |

## 3. LARGE function - example

The example demonstrated in cell E3 extracts the third-largest number in cell range B3:B9.

Formula in cell C3:

LARGE(*array*, *k*)

array - B3:B9

k - 3

LARGE(B3:B9, 3)

becomes

LARGE({100; 10; 2; 4; 50; 65; 47}, 3)

and returns 50. 50 is the third-largest number in B3:B9. Only 100 and 65 are larger.

## 4. LARGE function - based on a condition

The formula in cell F3 extracts the second largest number in cell range C3:C9 if the corresponding value on the same row in cell range B3:B9 is equal to the condition specified in cell E3.

Formula in cell F3:

### 4.1 Explaining formula

#### Step 1 - Logical test

The equal sign compares values in an Excel formula, the result is a boolean value TRUE or FALSE.

E3=B3:B9

becomes

"Apple"={"Apple"; "Orange"; "Apple"; "Orange"; "Apple"; "Orange"; "Apple"}

and returns

{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}

#### Step 2 - Filter values based on logical test

The FILTER function extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(C3:C9,E3=B3:B9)

becomes

FILTER(C3:C9,{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE})

becomes

FILTER({100; 10; 2; 4; 50; 65; 47},{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE})

and returns

{100; 2; 50; 47}

#### Step 3 - Calculate the second largest value

LARGE(FILTER(C3:C9,E3=B3:B9),2)

becomes

LARGE({100; 2; 50; 47},2)

and returns 50.

## 5. LARGE function - based on criteria

The formula in cell E3 extracts the second largest number in cell range C6:C12 if two conditions are met, the first condition is specified in cell B3 and the second in cell C3.

Note, the second condition is if a number is smaller than 60.

Formula in cell E3:

### 5.1 Explaining formula

#### Step 1 - Check criteria

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

COUNTIFS(*criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*]…)

Note the less than character in the last argument.

COUNTIFS(B3, B6:B12, C3, ">"&C6:C12)

returns

{0; 0; 1; 0; 1; 0; 1}.

Rows 8, 10 and 12 meet both conditions, see the image above.

#### Step 2 - Filter list

The FILTER function extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(C6:C12, COUNTIFS(B3, B6:B12, C3, ">"&C6:C12))

becomes

FILTER(C6:C12, {0; 0; 1; 0; 1; 0; 1})

becomes

FILTER({100; 10; 2 ;4; 50; 65; 47}, {0; 0; 1; 0; 1; 0; 1})

and returns {2; 50; 47}.

#### Step 3 - Calculate the second largest value

LARGE(FILTER(C6:C12, COUNTIFS(B3, B6:B12, C3, ">"&C6:C12)), 2)

becomes

LARGE({2; 50; 47}, 2)

and returns 47.

## 6. LARGE function - based on a list

The formula in cell F3, in the image above, extracts the second largest number in cell range C3:C9 if the value on the same row in B3:B9 meets any of the conditions specified in cells E3 and E4.

In other words, OR logic applied to a single column.

Formula in cell F3:

### 6.1 Explaining formula

#### Step 1 - Compare the list to items

The COUNTIF function counts the number of cells that meets a condition.

COUNTIF(*range*, *criteria*)

COUNTIF(E3:E4, B3:B9)

becomes

COUNTIF({"Apple"; "Banana"},{"Apple"; "Orange"; "Apple"; "Orange"; "Apple"; "Banana"; "Apple"})

and returns

{1; 0; 1; 0; 1; 1; 1}

#### Step 2 - Filter values based on logical test

The FILTER function extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(C3:C9,COUNTIF(E3:E4, B3:B9))

becomes

FILTER(C3:C9,{1; 0; 1; 0; 1; 1; 1})

becomes

FILTER({100; 10; 2; 4; 50; 65; 47}, {1; 0; 1; 0; 1; 1; 1})

and returns

{100; 2; 50; 65, 47}.

#### Step 3 - Calculate the second largest value

LARGE(FILTER(C3:C9,E3=B3:B9),2)

becomes

LARGE({100; 2; 50; 65, 47},2)

and returns 65.

## 7. LARGE function - multiple source ranges

The formula in cell B3 extracts the second largest number from three different nonadjacent cell ranges, in this example located on the same worksheet. The formula works fine even if they are on different worksheets.

Formula in cell B3:

### 7.1 Explaining formula

#### Step 1 - Join cell ranges

The parentheses and commas let you join cell ranges in the LARGE function, this doesn't work in every function. However, the LARGE and SMALL function works.

(B7:B13,D7:D13,F7:F13)

becomes

({7; 46; 82; 43; 25; 10; 21},{73; 13; 93; 66; 13; 65; 91},{85; 11; 97; 61; 4; 45; 4})

and returns

{7,73,85; 46,13,11; 82,93,97; 43,66,61; 25,13,4; 10,65,45; 21,91,4}.

#### Step2 - Second largest value in the array

LARGE((B7:B13,D7:D13,F7:F13),2)

becomes

LARGE({7,73,85; 46,13,11; 82,93,97; 43,66,61; 25,13,4; 10,65,45; 21,91,4},2)

and returns 93. Only 97 is larger.

## 8. LARGE function - based on a text string

The formula in cell B6 splits the string specified in cell B3 into an array, then extracts the second largest number in the array.

Formula in cell B6:

### 8.1 Explaining formula

#### Step 1 - Split string

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(*Input_Text*, *col_delimiter*, [*row_delimiter*], [*Ignore_Empty*])

TEXTSPLIT(B3,",")

becomes

TEXTSPLIT("73,1,57,56,41,23,48,77,79,29",",")

and returns

{"73", "1", "57", "56", "41", "23", "48", "77", "79", "29"}.

#### Step 2 - Convert to numbers

The asterisk lets you multiply values in an Excel formula, it also lets you convert "text" numbers to regular numbers. The numbers in the array above have double quotes, these will be removed.

TEXTSPLIT(B3,",")*1

becomes

{"73", "1", "57", "56", "41", "23", "48", "77", "79", "29"}*1

and returns

{73, 1, 57, 56, 41, 23, 48, 77, 79, 29}.

#### Step 3 - Extract the second largest number in the array

LARGE(TEXTSPLIT(B3,",")*1,2)

becomes

LARGE({"73", "1", "57", "56", "41", "23", "48", "77", "79", "29"}, 2)

and returns 77. Only 79 is larger.

## 9. LARGE function - calculate an average of the three largest numbers

The formula in cell E3 extracts the three largest numbers in cell range B3:B9, then calculates an average based on these three numbers.

Formula in cell range E3:

### 9.1 Explaining formula

#### Step 1 - Extract the three largest numbers

The LARGE function allows you to extract multiple values if you use an array of numbers in the second argument.

LARGE(B3:B9,{1;2;3})

becomes

LARGE({100; 10; 2; 4; 50; 66; 47},{1; 2; 3})

and returns

{100; 66; 50}.

#### Step 2 - Calculate an average

The AVERAGE function calculates the average of numbers in a cell range or array.

AVERAGE(*number1*, *[number2]*, ...)

AVERAGE(LARGE(B3:B9,{1; 2; 3}))

becomes

AVERAGE({100; 66; 50})

and returns 72. 100 + 66 + 50 = 216. 216/3 equals 72.

## 10. How to extract the k-th largest number in a 3D range

This example shows how to extract the k-th largest number in multiple worksheets. The data must be in the same cell range throughout all worksheets. For example, the image above demonstrates two cell ranges B3:B9 in worksheets '3D range' and '3D range (2)'.

Here is how to enter the LARGE function using 3D ranges:

- Doublepress with left mouse button on a cell.
- Type =LARGE(
- Press and hold SHIFT key.
- Select the remaining worksheets, in this case '3D range (2)' with the mouse.
- Select cell range B3:B9 with the mouse.
- Type the ending parentheses.
- Press Enter.

The formula looks like this;

Section 7 demonstrates how to get the k-th largest value from multiple cell ranges, this works fine with multiple worksheets as well and they don't need to be located on the same cell range. This can however be tedious to enter if many cell ranges are used.

### 'LARGE' function examples

The following 26 articles contain the LARGE function.

This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

Question: List of data and blank cells in a column which will be added from day to day. There are […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]

## Functions in this article

### Functions in 'Statistical' category

The LARGE function function is one of many functions in the 'Statistical' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form