## How to use the MAX function

The MAX function calculates the largest number in a cell range. The formula in cell D3 extracts the highest number from cell range B3:B10, see picture above.

Formula in cell D3:

**Table of contents**

- MAX Function Syntax
- MAX Function Arguments
- MAX function not working
- Get largest number based on a condition
- Get largest number based on multiple conditions
- Max function for text
- MAX function ignore #N/A error
- MAX function second highest
- MAX function second-highest number no duplicates
- MAX function second-highest number based on a condition

## 1. MAX Function Syntax

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

## 2. MAX Function Arguments

Argument |
Text |

number1 |
You need at least one argument. Required. |

[number2] |
Up to 254 arguments are possible. Optional. |

Arguments can be constants, named ranges, cell references, and arrays. The MAX function ignores text and boolean values, however not errors.

## 3. Excel function not working

A #NAME? error is most often the result of a misspelled function. The image above shows the MAX function in cell D3, however, it returns a #NAME? error.

### 3.1 Error in data

The MAX function can't handle error values in your data. The first error parsed is returned. The image above shows a #DIV/0! error in cell D3, data in cell B3:B10 contains a #DIV/0 error in cell B5.

### 3.3 Max function returns 0 (zero)

## 4. Max function based on a condition

Array formula in cell F3:

### 4.1 Explaining formula in cell F3

#### Step 1 - Compare values

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

F2=B3:B10

becomes

"Blue"={"Blue"; "Yellow"; "Yellow"; "Blue"; "Yellow"; "Blue"; "Blue"; "Yellow"}

and returns

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

#### Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(F2=B3:B10, C3:C10, "")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}, C3:C10, "")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}, {10; 50; 40; 10; 20; 40; 10; 20}, "")

and returns

{10; ""; ""; 10; ""; 40; 10; ""}.

#### Step 3 - Return max number

The MAX function calculates the largest number in a cell range.

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

MAX(IF(F2=B3:B10, C3:C10, ""))

becomes

MAX({10; ""; ""; 10; ""; 40; 10; ""})

and returns 40 in cell F3.

## 5. Get the largest number based on multiple conditions

The image above shows a formula in cell G3 that extracts the largest number if the adjacent value is equal to the criteria specified in cell E3:E4.

Array formula in cell G3:

### 5.1 Explaining formula in cell F3

#### Step 1 - Identify cells equal to any of the criteria

The COUNTIF function calculates the number of cells equal to a condition.

COUNTIF(*range*, *criteria*)

COUNTIF(E3:E4,B3:B10)

becomes

COUNTIF({"Blue"; "Yellow"},{"Blue"; "Yellow"; "Green"; "Blue"; "Yellow"; "Green"; "Blue"; "Green"})

and returns {1; 1; 0; 1; 1; 0; 1; 0}. 1 means that the value in that position is equal to one of the conditions, this means that the position in the array is important in order to extract correct numbers.

#### Step 2 - Replace 1 with the corresponding number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(COUNTIF(E3:E4, B3:B10), C3:C10, "")

becomes

IF({1; 1; 0; 1; 1; 0; 1; 0}, C3:C10, "")

becomes

IF({1; 1; 0; 1; 1; 0; 1; 0}, {10; 50; 40; 10; 20; 40; 10; 20}, "")

and returns {10; 50; ""; 10; 20; ""; 10; ""}.

#### Step 3 - Return the largest number in the array

The MAX function calculates the largest number in a cell range.

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

MAX(IF(COUNTIF(E3:E4, B3:B10), C3:C10, ""))

becomes

MAX({10; 50; ""; 10; 20; ""; 10; ""})

and returns 50 in cell G3.

## 6. Max function for text

The following section explains how to filter the largest value based on the number of characters in a cell, if you are looking for the most repeated value go to section 6.2 below.

Array formula in cell F3:

### 6.1 Explaining formula in cell F3

#### Step 1 - Count characters

The LEN function counts the number of characters in a cell.

LEN(B3:B12)

becomes

LEN({"Emphasis"; "Basil"; "Boolean"; "Doors"; "Gift"; "Historian"; "Integration"; "Pension"; "Poverty"; "Yogurt"})

and returns {8; 5; 7; 5; 4; 9; 11; 7; 7; 6}.

#### Step 2 - Get largest character count

The MAX function calculates the largest number in a cell range.

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

MAX(LEN(B3:B12))

becomes

MAX({8; 5; 7; 5; 4; 9; 11; 7; 7; 6})

and returns 11.

#### Step 3 - Find the position of a cell containing the largest number of characters

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(MAX(LEN(B3:B12)), LEN(B3:B12), 0)

becomes

MATCH(11, {8; 5; 7; 5; 4; 9; 11; 7; 7; 6}, 0)

and returns 7.

#### Step 4 - Get value based on position

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(B3:B12, MATCH(MAX(LEN(B3:B12)), LEN(B3:B12), 0))

becomes

INDEX(B3:B12, 7)

becomes

INDEX({"Emphasis"; "Basil"; "Boolean"; "Death"; "Gift"; "Historian"; "Integration"; "Pension"; "Poverty"; "Yogurt"}, 7)

and returns "Integration" in cell D3.

### 6.2 Get most frequent value

The formula below extracts the most repeated text value in cell range B3:B12.

Formula in cell D3:

Use the MODE function if you are working with numbers.

#### 6.2.1 Explaining formula in cell D3

**Step 1 - Count value frequency **

The COUNTIF function calculates the number of cells equal to a condition.

COUNTIF(*range*, *criteria*)

COUNTIF(B3:B12, B3:B12)

becomes

COUNTIF({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"},{"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"})

and returns

{3; 1; 2; 3; 1; 1; 2; 1; 3; 1}.

The array is displayed in cell range A3:A12, each number corresponds on the same row to a value in B3:B12. Cell value in B3 is found three times in B3:B12, the array shows 3 in cell A3.

**Step 2 - Get most frequent value**

The MAX function calculates the largest number in a cell range.

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

MAX(COUNTIF(B3:B12, B3:B12))

becomes

MAX({3; 1; 2; 3; 1; 1; 2; 1; 3; 1})

and returns 3.

**Step 3 - Find position of most frequent value**

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(MAX(COUNTIF(B3:B12, B3:B12)), COUNTIF(B3:B12, B3:B12), 0)

becomes

MATCH(3, COUNTIF(B3:B12, B3:B12), 0)

becomes

MATCH(3, {3; 1; 2; 3; 1; 1; 2; 1; 3; 1}, 0)

and returns 1.

**Step 4 - Get most frequent value**

The INDEX function returns a value from a cell range, you specify which value based on a row and column number (optional).

INDEX(*reference*, *row*, *column*)

INDEX(B3:B12, MATCH(MAX(COUNTIF(B3:B12, B3:B12)), COUNTIF(B3:B12, B3:B12), 0))

becomes

INDEX(B3:B12, 1)

becomes

INDEX({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"}, 1)

and returns "Emphasis".

## 7. MAX function ignore #N/A error

This formula returns the largest number from a given cell range ignoring #N/A errors.

Array formula in cell D3:

### 7.1 Explaining formula

#### Step 1 - Identify #N/A errors

The ISNA function returns TRUE if a value is a #N/A error.

ISNA(*value*)

ISNA(B3:B10)

becomes

ISNA({182; 36; #N/A; 93; TRUE; 78; 72; 194})

and returns

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

#### Step 2 - Replace #N/A errors with a blank

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(ISNA(B3:B10), "", B3:B10)

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}, "", {182; 36; #N/A; 93; TRUE; 78; 72; 194})

and returns

{182; 36; ""; 93; TRUE; 78; 72; 194}.

The N/A error is replaced with a blank in the array above.

#### Step 3 - Extract the largest number ignoring blanks and text values

MAX(IF(ISNA(B3:B10), "", B3:B10))

becomes

MAX({182; 36; ""; 93; TRUE; 78; 72; 194})

and returns 194.

## 8. MAX function second-highest number

The image above demonstrates a formula in cell E2 that extracts the second largest number from cell range B3:B10.

Formula in cell E2:

The LARGE function extracts the k-th largest number in a cell range or array.

LARGE(*array*, *k*)

Argument k becomes 2 if we want to extract the second-largest number.

## 9. MAX function second-highest number no duplicates

The image above demonstrates a formula in cell E2 that extracts the second largest number, duplicate numbers are ignored.

For example, both cells B4 and B8 contain the number 50. The LARGE function returns the second largest number considering duplicate numbers as well. The following function ignores duplicates returnning 40 in cell E4.

Formula in cell E2:

### 9.1 Explaining formula in cell E2

#### Step 1 - Find the largest number

The MAX function calculates the largest number in a cell range.

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

MAX(B3:B10)

becomes

MAX({10; 50; 40; 10; 20; 50; 10; 20})

and returns 50.

#### Step 2 - Find the largest number

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

MAX(B3:B10)=B3:B10

becomes

50={10; 50; 40; 10; 20; 50; 10; 20}

and returns

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

#### Step 3 - Replace boolean value TRUE with corresponding numbers

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(MAX(B3:B10)=B3:B10, "", B3:B10)

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}, "", B3:B10)

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}, "", {10; 50; 40; 10; 20; 50; 10; 20})

and returns

{""; 50; ""; ""; ""; 50; ""; ""}.

#### Step 4 - Extract maximum number from array

MAX(IF(MAX(B3:B10)=B3:B10, "", B3:B10))

becomes

MAX({""; 50; ""; ""; ""; 50; ""; ""})

and returns 50.

## 10. MAX function second-highest number based on a condition

Formula in cell F3:

### 10.1 Explaining formula

#### Step 1 - Identify values equal to condition

The equal sign lets you compare value to value, in this case, value to array. The result is a boolean value TRUE or FALSE.

F2=B3:B10

becomes

"Blue"={"Blue"; "Yellow"; "Yellow"; "Blue"; "Yellow"; "Blue"; "Blue"; "Yellow"}

and returns

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

#### Step 2 - Replace TRUE with corresponding number on the same row

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(F2=B3:B10, C3:C10, "")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}, {10; 50; 40; 10; 20; 50; 10; 20}, "")

and returns

{10; ""; ""; 10; ""; 50; 10; ""}.

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

The LARGE function extracts the k-th largest number in a cell range or array.

LARGE(*array*, *k*)

LARGE(IF(F2=B3:B10, C3:C10, ""), 2)

becomes

LARGE({10; ""; ""; 10; ""; 50; 10; ""}, 2)

and returns 10.

### 'MAX' function examples

The following 32 articles have formulas containing the MAX function.

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]

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

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

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

Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]

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 […]

This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

What's on this page Unique distinct values sorted based on frequency Unique distinct values sorted based on frequency - Excel […]

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

### Functions in 'Statistical' category

The MAX 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