Purpose

Return value

Syntax

=MAX(number1,[number2],...)
  • number1 - Number, reference to numeric value, or range that contains numeric values.
  • number2 - [optional] Number, reference to numeric value, or range that contains numeric values.

Using the MAX function

The MAX function returns the largest numeric value in the data provided. The MAX function can be used to return the largest value from any type of numeric data. For example, MAX can return the slowest time in a race, the latest date, the largest percentage, the highest temperature, or the top sales number.

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 the supplied data:

=MAX(12,17,25,11,23) // returns 25

When given a range , MAX returns the largest value in the range:

=MAX(A1:A10) // maximum value in A1:A10

Mixed arguments

The MAX function can accept a mix of arguments:

=MAX(5,10)
=MAX(A1,A2,A3)
=MAX(A1:A10,100)
=MAX(A1:A10,C1:C10)

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:

=MAX(-1,TRUE) // returns 1
=MAX(-1,TRUE,"3") // returns 3

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. See a detailed example here .

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 .

Purpose

Return value

Syntax

=MAXA(value1,[value2],...)
  • value1 - Number, reference to numeric value, or range that contains numeric values.
  • value2 - [optional] Number, reference to numeric value, or range that contains numeric values.

Using the MAXA function

The MAXA function returns the largest numeric value in a range of values. Like the MAX function , MAXA ignores empty cells. However, unlike the MAX function, MAXA evaluates the logical values TRUE and FALSE as 1 and 0, and evaluates text as zero when these values appear in a range or cell reference.

The MAXA 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.

Examples

Like the MAX function , the MAXA function returns the largest number in the supplied data:

=MAX(12,17,25,11,23) // returns 25
=MAXA(12,17,25,11,23) // returns 25

MAXA can be used with constants, cell references, or ranges:

=MAXA(5,10)
=MAXA(A1,A2,A3)
=MAXA(A1:A10)

MAXA vs. MAX

The primary difference between MAX and MAXA is that MAXA evaluates TRUE and FALSE values as 1 and 0, and text values as zero when these values appear in a range or in a cell reference. You can see this behavior in the range I7:I12 of the example shown. While the MAX function ignores the logical and text values completely, the MAXA function includes these values when calculating a maximum value.

Note that MAX and MAXA both evaluate numbers as text when supplied directly as arguments:

=MAXA(5,"10") // returns 10
=MAX(5,"10") // also returns 10

Notes

  • MAXA ignores empty cells, but evaluates logical values and text values.
  • Arguments can be provided as numbers, names, arrays, or references.
  • If arguments contain no numeric values, MAXA returns 0.
  • To ignore logical values and text, see the MAX function .