Use the MAX Function

The MAX function finds the highest number. It can include up to 255 argumentsArguments are the values you provide a function with so that it can perform a calculation.. Each argument can be a number, a name, an array, a formula, a logical value, a function, a referenceA reference is a cell address, a range, a cell name, a range name, or a named constant., or a text representation of a number.

Syntax:

MAX(argument1, argument2, ...)

The first argument is required; all other arguments are optional.

When using the MAX function, how Excel treats a data type may depend on whether the data type is located in a cell, is located in an array, or is an argument. TRUE and FALSE are logical values. In a cell, Excel considers a number preceded by an apostrophe text. In a formula and in a function, Excel considers a number enclosed in double quotes text. If a cell contains a logical value, text, or an error value Excel ignores the cell. If an array contains a logical value, text, or an error value, Excel ignores the logical value, text, or error value.

If an argument is a logical value or text, Excel includes it in the formula. TRUE becomes 1, FALSE becomes 0, and text causes an error if Excel cannot convert the text to a number. For example, = MAX(TRUE, FALSE) returns 1, =MAX("Two", "1") returns the error #Value, and =MAX("2","1") returns 2.

In Excel, dates are stored as numbers. So, you can use the MAX function to find the latest date.

In the following worksheet, all of the cells that have orange text contain a calculation. All of the calculations use the MAX function. Feel free to modify them and to create your own functions.

The Advanced Examples Tab Explained

Example 1

Excel evaluates each of the arguments: 6, 7, 9, 3, and 2; then returns the highest number, 9.

Example 2

Excel evaluates each of the numbers in cells A1:A5: 6, 7, 9, 3, and 2; then returns the highest number, 9.

Example 3

Excel evaluates each of the arguments: -2, TRUE, .08, .09, and -4. TRUE is a logical value. Excel converts it to 1. When a logical value is given as an argument, Excel includes it in its evaluation. So, Excel returns 1. It also includes text that can be converted to a number. The number .9 is in quotation marks, so it is considered text, but Excel can convert it to 0.9. If you exclude TRUE from the formula, Excel will return 0.9.

Example 4

Excel evaluates each of the numbers in cells B1:B5: -2, TRUE, .08, '.09, and -4. When a logical value is in a cell, Excel ignores it. It also ignores text that can be converted to a number. So, even though TRUE and '0.9 are higher than 0.8, Excel return 0.8.

Example 5

This example includes an array. Excel multiplies 2 times 5, gets 10, 3 times 5, gets 15, and 4 times 5, gets 20. It then returns the highest value, which is 20.

Tip

You can also use AutoSum to access the MAX function. Click the down-arrow associated with the AutoSum button on the Home tab. Then click Max. When you access the MAX function through AutoSum, Excel provides you with extra features that enable you to calculate the highest number quickly. Excel provides you with extra features that enable you to calculate the highest number quickly. For example, you can quickly calculate the highest number in each column or each row in a set of columns or rows.  AutoSum MAX works the same way AutoSum SUM does. Click here to find out more about AutoSum.