Author: Oscar Cronquist Article last updated on November 22, 2021

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.

Excel Function Syntax

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

Arguments

number1 - You need at least one argument. Required.

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

Comments

Arguments can be constants, named ranges, cell references and arrays.

The MAX function ignores text and boolean values, however not errors.

1. Excel function not working

 

1.1 Error in data

 

1.2 #NAME error

1.3 Max function returns 0 (zero)

2. Max function based on a condition

MAX function based on a condition

Array formula in cell F3:

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

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.

3. Get the largest number based on multiple conditions

MAX function based on criteria

Array formula in cell G3:

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

3.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(rangecriteria)

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 psoition 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.

4. Max function for text

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

MAX function for text

Array formula in cell F3:

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

4.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.

4.2 Get most frequent value

MAX function for text1

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

Formula in cell D3:

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

Use the MODE function if you are working with numbers.

4.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(rangecriteria)

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}.

MAX function get most frequent value 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".

5. MAX function ignore #N/A error

5.1 What is N/A error?

6. MAX function second-highest number

MAX function second highest

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

Formula in cell E2:

=LARGE(B3:B10,2)

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.

The formula returns the largest number if it has a duplicate. Consider these numbers, 3, 2, and 3. The largest number is three, the second-largest number is also three. This is because number three has a duplicate.

7. MAX function second-highest number no duplicates

MAX function second highest no duplicates

Formula in cell E2:

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

7.1 Explaining formula in cell E2

Step 1 - Find 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

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(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

 

Step 4 -

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

8. MAX function second-highest number based on a condition

MAX function second highest based on a condition

Formula in cell F3:

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

9. MAX function Conditional formatting