MAX Function – Google Sheets

The MAX function in Google Sheets identifies the maximum value within a range of cells or individual values.

Each value argument must be a cell, a number, or a range containing numbers. This function ignores any text values. Use the MAXA function to count text and boolean values.

Make a copy of this Google Sheet with the examples to follow along.

Syntax

This function follows a simple structure:

=MAX(value1, [value2, ...])

  • value1: This is the first number or range of cells you want to analyze.
  • value2, ...: [Optional] Any additional numbers or ranges.

Examples

MAX Function with Numbers

Imagine you have sales amounts in column B (B2:B4). To find the highest amount, use the following formula:

Finding the largest number with the MAX function
MAX Function with Only Numbers

=MAX(B2:B4)

This function returns $26.96, the highest Sales amount among all the Sales Reps.

MAX Function with Numbers and Text

Next, let’s see what happens with non-numbers mixed into your data.

The MAX function ignoring text values
The MAX Function Ignoring Text

Formula used: =MAX(B2:B4)
This formula will display the maximum Price entered as a number value. Even though the value in B3 represents $26, the function skips it since it is a text value.

MAX Function with Negative Values

You can also compare individual values in your spreadsheet. Suppose you have negative percentage values representing three tax rates (B2:B4). To find the highest rate, you may be tempted to use the MAX function.

The MAX function with negative inputs
Negative Inputs

Formula Used: =MAX(B2:B4)

As you can see in the image, be careful when using this function with negative numbers. Since the numbers are negative, the number closest to zero will be the largest number. Consider using the MIN function instead to get the smallest number.

Notes

  • This function ignores text values and empty cells.
  • For text values, use the MAXA function instead.
  • Need the lowest value? Use MIN instead.

Remember, practice makes perfect, so experiment with different scenarios to discover its full potential.