The MAX function in Google Sheets is useful when you need to get the maximum value in a numeric dataset.
The maximum value is the quantitative result of comparing numbers in a dataset and finding the value that all other values in the dataset are less than or equal to.
The rules for using the MAX
function in Google Sheets are as follows:
- The function requires one or more arguments which may be a value, cell, or cell range.
- The function then outputs the maximum value out of the arguments provided.
Let’s begin with describing a simple use case!
Let’s say we have a dataset of motorsports statistics. We are tasked to list out a few interesting facts about the current drivers. First, we would need to know which driver has the most points so far. Next, we would need to find out the driver with the most number of “podium” finishes. A podium finish is when the driver finishes either in the first, second, or third place. Can we figure these statistics out using Google Sheets?
The MAX
function makes it quite easy to solve for these statistics. Later, we’ll go through how we can use the data provided and the MAX
function to return the statistics we need. For now, let’s dive into how we can write out the MAX
function.
The Anatomy of the MAX Function
The syntax of the MAX
function is as follows:
=MAX(value1, [value2, ...])
Let’s dissect this formula and understand what each of these terms means:
- = the equal sign is how we start any function in Google Sheets.
- MAX() is our
MAX
function. It computes the maximum value of a dataset. - value1 refers to the first value or range to consider when computing the maximum value.
- value2 and so on refer to additional values and ranges to consider when finding the maximum value.
- Entering text into this function will cause it to return the
#VALUE!
error.
A Real Example of Using MAX Function
Let’s look at a real example of the MAX
function being used in a Google Sheets spreadsheet.
In the example below, we have a dataset of how many points each Formula 1 driver received in the first 11 races. Column M totals all the points so far in the season. Using the MAX
formula, we were able to return the highest number of points in the championship so far.
To get the maximum number of points, we just need to use the following formula:
=MAX(M2:M22)
How did we find out the driver with the most number of points? To get the result, we need to use the output of our MAX
function and use it in an INDEX
and MATCH
lookup.
Typing in the following formula tells us that the current leader in the championship is Lewis Hamilton.
=INDEX(A1:A22,MATCH(P1,M:M,0),0)
In the second example, we’ll see which driver has the most number of podiums.
We used a similar formula to get the result in cell P1. Interestingly, both Verstappen and Hamilton have the same number of podiums at this point of the championship.
You can create a copy of the worksheet above using the link attached below.
If you’re ready to try out the MAX
function in Google Sheets, let’s begin writing it ourselves!
How to Use MAX Function in Google Sheets
In this section, we will go through each step needed to start using the MAX
function in Google Sheets. This specific guide will show you how to find the maximum value in a given range, using the motorsports dataset shown in prior examples.
- First, select the cell which will hold the result of our
MAX
function.
- Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘MAX(‘.
- You may encounter a tooltip box with hints on how to use the current function. We can click on the arrow found in the top-right-hand corner of the box if you would like to hide it from view.
- Next, select the range of values to find the maximum value. In this case, we’ll be finding the maximum value in the range M2:M22. Enter that range as the argument of the
MAX
function.
Afterward, we can simply hit Enter to let the function evaluate.
Frequently Asked Questions (FAQ)
- What is the difference between the MAX and MAXA functions?
The primary difference is thatMAXA
evaluates TRUE and FALSE values as 1 and 0, respectively. TheMAX
function only considers numeric values when finding the result. If your dataset contains logical values, then it is suggested to use theMAXA
function instead. - What happens if there are non-numeric values in our dataset?
The MAX function will simply ignore all non-numeric values in the provided dataset. If no numeric values are found, theMAX
function will return 0.
That’s all you need to remember to start using the MAX
function in Google Sheets. This guide shows how easy it is to use the MAX
function to find the largest value in a dataset.
You can now be confident in using the MAX
function in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that help with your data analysis.
If you want to stay notified of new Google Sheets guides like this, don’t miss out on subscribing to our newsletter!