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.
Contents
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.
- 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.
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.
The functions work well with dates. Each function evaluates the dates in rows 1 through 4 and finds May 5, 2024, the latest and, therefore, the largest date.
Example 3 – Numbers and Text
Now, we will introduce text into our data, but the results will remain the same.
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 in which we will see differences between MAX and MAXA. Let’s examine 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 a 0 and returns that as the largest number.
Example 5 – Boolean Values
Lastly, we will look at boolean values. These values have only two options: 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 1s. Therefore, MAXA returns a 1 while MAX finds nothing and returns a 0.
When to Use Each
- Use MAX when you 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
Summary
In summary, MAXA offers more flexibility for handling diverse data types, while MAX provides a more precise result for purely numeric data.