Finding the Largest Value in Google Sheets – MAX vs MAXA

You can find the highest value in Google Sheets with either the MAX or MAXA functions. But which one should you use? Let’s explore the key difference between these two functions.

Difference Between MAX and MAXA

  • MAX: This function only considers numeric values when finding the highest value. Text, TRUE/FALSE, or any other data types are ignored. If you enter text values in the range, MAX ignores them and only evaluates the numbers.
  • MAXA: This function is designed to handle mixed data types. It can find the maximum value among numbers, text, and other types. However, it treats text values that cannot be converted to numbers as 0. It evaluates boolean values as 0 for FALSE and 1 for TRUE.

Examples

Copy this Google Sheet to follow along with the examples.

Example 1 – All Numbers

This first example uses only numeric values. You will see the same result as both functions treat numbers the same.

The MAX and MAXA function returning the same number
Numbers

Each function evaluates the numbers in rows 2 through 5 and returns the largest, $261.96.

Example 2 – Dates.

This second example is similar to the first. MAX and MAXA will return the same result. Dates are stored as numbers in Google Sheets, each day being one more than the last.

MAX and MAXA function evaluating dates
Dates

Therefore, the functions work well with dates. Each function evaluates the dates in rows 2 through 4 and finds May 5, 2024 as the latest and therefore largest date.

Example 3 – Numbers and Text

Now we will introduce text into our data but we still won’t see a difference in the results.

The MAX and MAXA functions evaluating text and numbers and getting different results
Numbers and Text

Both functions look at 21, 16, “None“, and 31 for the maximum values. MAX ignores the “None” string while MAXA evaluates it as 0. Since we’re looking for maximum values, neither result changes the output of 31.

Example 4 – Negative Numbers and Text

Negative numbers are the first type of data where we will see differences between MAX and MAXA. Let’s take a look at an example to see why.

MAX and MAXA with negative numbers and text
Negative Numbers and Text

The negative numbers flip the logic that we’ve seen in the previous examples. Here, a 0 is larger than the other numbers. The MAX function ignores the text and therefore finds -6.34% to be the largest number, but MAXA evaluates the text “Zero” to be an 0 and therefore returns that as the largest number.

Example 5 – Boolean Values

Lastly we will look at boolean values. These values have only two options. In this case, they are checked or unchecked boxes.

MAX and MAXA producing different results when evaluating boolean values
Boolean Values

Both functions will look through the values in rows 2 through 4, but MAX will ignore all of them, while MAXA sees the checked boxes as 1s. Therefore, MAXA returns a 1 while MAX finds nothing and returns a 0.

When to Use Each

  • Use MAX when you’re sure your data contains only numbers and want to find the true numeric maximum.
  • Use MAXA when your data might include text, checkboxes, or other non-numeric values, and you want to consider everything (with non-numbers treated as 0).

Video Tutorial

YouTube player
YouTube Tutorial

Summary

In summary, MAXA offers more flexibility for handling diverse data types, while MAX provides a more precise result for purely numeric data.