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

Example 1 – All Numbers

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

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.

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.

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.

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.

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 `1`s. 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).

Summary

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