The MAX function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MAX ignores empty cells, text values, and the logical values TRUE and FALSE.
Basic Example
The MAX function returns the largest numeric value in supplied data: When given a range, MAX returns the smallest value in the range:
Mixed arguments
The MAX function can accept a mix of arguments:
Logical values
The MAX function will ignore logical values and numbers entered as text that appear on the worksheet. However, if such values are provided directly as arguments, MAX will use them:
Errors
When MAX encounters an error in a range, it will return an error. To calculate a maximum value while ignoring errors, you can use the AGGREGATE function, which can be configured to ignore errors.
Other functions
Excel provides other functions that deal with maximum values and rank:
To calculate the maximum value with criteria, use the MAXIFS function. To retrieve the nth largest value in a data set, use the LARGE function. To determine the rank of a number in a set of data, use the RANK function.
Notes
Arguments can be provided as numbers, names, arrays, or references. MAX accepts up to 255 arguments. If arguments contain no numbers, MAX returns 0. MAX ignores empty cells, text values, and TRUE and FALSE in references. MAX will evaluate numbers as text and logical values supplied directly as arguments. To include logical values in a reference, see the MAXA function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
