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.

Similar Functions

MAX – Finds the largest value in a dataset. Ignores text and boolean values.

MAXA – Finds the largest value in a dataset of numbers, text, and boolean values.

MIN – Finds the smallest value in a dataset. Ignores text and boolean values.

MINA – Finds the smallest value in a dataset of numbers, text, and boolean values.

MAX Function Examples

Largest Number

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.

Largest Number with 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.

Largest Negative Value

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.

Latest Date

Dates may seem like their own data type, but they are just numbers. Each date is one whole number more than the last. Because of this, we can use a function to find the latest date.

The MAX function finding the latest date
Dates

MAX evaluates the dates in B2 through B5 in this list of season start dates. Since dates are numbers, let’s look at the numbers these dates represent.

Custom number format
Custom Number Format
Dates shown as integers
Dates Shown as Numbers

After changing the formatting to a number with no decimal places, it is easy to see why the later date is also the largest. December 21, 2024, is equal to 45,647, the largest number in the list.

Video Tutorial

YouTube player
YouTube Tutorial

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.

Related Articles

MAX vs MAXA – Finding the largest number in Google Sheets.