How to Use MAX Function in Google Sheets

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.

1. First, select the cell which will hold the result of our MAX function.
   
   
   
   
2. Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘MAX(‘. 
3. 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.
   
   
4. 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)

1. What is the difference between the MAX and MAXA functions?
   The primary difference is that MAXA evaluates TRUE and FALSE values as 1 and 0, respectively. The MAX function only considers numeric values when finding the result. If your dataset contains logical values, then it is suggested to use the MAXA function instead.
2. 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, the MAX 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!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe