MAX Google Sheets Function [With Quiz]
Updated: October 26, 2021 In Google Sheets the MAX function provides the maximum numerical value found in the values, cells, or ranges provided as arguments.
Other data types present in cell and range references (like text) are ignored.
MAX Syntax
- value1 = a value, cell reference, or range reference from which to return the maximum numerical value.
- [value2, ...] = [optional] additional values, cell references, or range references.
Dates, times, and durations are included in the search for the maximum value by MAX because, in Google Sheets, dates and times are just numbers made to look like dates and times.
When negative and positive numbers are included, the 'maximum' is the largest positive number.
When only negative numbers are included, the 'maximum' is the smallest negative number.
The maximum is not the value furthest from zero:
Returns 100.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
MAX Function Example
| A | B | C | |
| 1 | Data | Formula | Output |
| 2 | 98 | =MAX(A2:A) | 98 |
| 3 | 15 | ||
| 4 | zero | ||
| 5 | 6 | ||
| 6 | |||
| 7 | 34 |
The four values considered are:
- 98
- 15
- 6
- 34
The blank cell and zero text are ignored because they cannot be converted to numbers.
Therefore, the output is 98.
Using cell and range references that contain only blank cells and/or text in the arguments of the MAX function will output 0.
MAX Errors
The arguments to MAX can be provided as numbers, Boolean data, cell references, or range references:
Providing text as an argument directly (e.g. =MAX(1,FALSE,"Text",A1,B1:B2)) will result in a #VALUE! error:
Function MAX parameter 3 expects number values. But 'Text' is a text and cannot be coerced to a number.
When text is included in a cell or range reference it is ignored without error.
Boolean values can be considered numbers in certain contexts (TRUE = 1 and FALSE = 0).
There is an interesting anomaly in the MAX function based on this.
If you include Boolean data in a cell or range reference it is not considered.
If you include Boolean data as a value it is considered.
| A | B | C | |
| 1 | Data | Formula | Output |
| 2 | -98 | =MAX(A2:A) | -6 |
| 3 | -15 | =MAX(A2:A,TRUE,FALSE) | 1 |
| 4 | TRUE | ||
| 5 | -6 | ||
| 6 | FALSE | ||
| 7 | -34 |
It's a bit of a strange feature that is unlikely to catch you out.
However, when things like this come up it's best not to mix value and cell/range references as arguments. Sticking to one type of argument helps to avoid potentially confusing outputs.
If the referenced data includes an error, MAX will output the first error it encounters:
| A | B | C | |
| 1 | Data | Formula | Output |
| 2 | 98 | =MAX(A2:A) | #N/A |
| 3 | 15 | ||
| 4 | #N/A | ||
| 5 | 6 | ||
| 6 | #ERROR! | ||
| 7 | 34 |
Another obvious error is if you don't include any arguments (as the first value1 is required):
Which results in an #N/A error:
Wrong number of arguments to MAX. Expected at least 1 arguments, but got 0 arguments.
MAX Function Quiz
Here's a quick summary of how to use the quiz spreadsheet:
- Select the required cell while the formula bar is empty
- Type your answer in the formula bar
- You can click on the spreadsheet to add cell/range references
Complete all questions to solidify your understanding of the MAX function.
| A | B | C | |
| 1 | Salesperson | Sales | Revenue |
| 2 | Dwight | 10 | $3,240 |
| 3 | Jim | 9 | $2,916 |
| 4 | Phyllis | 7 | $2,268 |
| 5 | Stanley | 7 | $2,268 |
| 6 | Andy | 5 | $1,620 |
| 7 | |||
| 8 | Most sales: | ||
| 9 | Highest rev: | ||
| 10 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
![LOWER Google Sheets Function [With Quiz]](https://kierandixon.com/wp-content/uploads/lower-function-google-sheets.png)
