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
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
{""; 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
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}, {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 contain 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 columns D and E. What's […]
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 sorting 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 […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
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 this article
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 signs
Use 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 Oscar
You can contact me through this contact form