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.
Contents
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:
=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.
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.
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.
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.
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
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.