What Is MAX Function In Google Sheets?
The MAX in Google Sheets checks the selected dataset or the cell range for the largest or the highest value and will return that value as the output.
The Google Sheets MAX function helps users project the highest values easily in a large dataset with multiple digit numeric values, such as population data, a company’s profit and loss data, etc.
For example, we have the population details of 10 cities which is a large data and not so easy to find the populated city. Therefore, we will use the MAX function to find the most populated city.
Select cell E2, enter the formula =MAX(C2:C11) and press “Enter”, as shown below.
The output is shown above, i.e. the maximum value being the most populated city.
Key Takeaways
- The MAX in Google Sheets is a Statistical function that helps us find the maximum value from a list of values, such as numbers in any format whether integers (positive or negative), decimals, fractions, or dates in any valid format, etc.
- The function returns 0 when none of the arguments is numeric or a blank range is supplied.
- We can use the filter and the sort method and organize the values in descending order to get the maximum value without the formula.
- To find the Max values we can combine the function with other functions, such as FILTER(), XLOOKUP(), etc.
- We can also use the Conditional formatting method to highlight the maximum values in a given dataset. However, we must ensure to select only the numeric values column to avoid unnecessary errors.
- The MAXA function helps find the maximum value from a list of numbers, logical values (true and false), and text representations of numbers.
- When we want to execute a function as an array, we must press the keys “Ctrl+Shift+Enter” or use the ARRAYFORMULA, that is only available in Google Sheets and not in MS Excel.
Syntax
The syntax of the MAX formula in Google Sheets is,
The arguments of the MAX formula in Google Sheets are,
- value1: It is the selected cell range to find the highest number. It is a mandatory argument.
- [value2, …]: It is the additional cell ranges that must be checked along with value1. It is an optional argument.
How To Use MAX Function In Google Sheets?
We can use the MAX in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access From the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output à select the “Insert” tab à click the “Function” option right arrow → click the “Statistical” option right arrow à select the “MAX” function, as shown below.
Step 2: The “MAX” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =MAX( in the cell, as shown below. [Alternatively, type =M or =MA and double-click the MAX from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some MAX in Google Sheets examples along with other functions, such as FILTER(), XLOOKUP() and Conditional Formatting.
Example #1
Consider the dataset that consists of employee details, such as their names, ID’s, department and their salaries. We will use the MAX() for finding maximum salary of employees.
The procedure to find the highest salary from the employee data is,
Select cell G2, enter the formula =MAX(E2:E11) and press “Enter”, as shown below.
Example #2 – MAX with FILTER Function.
The dataset given below consists of the smartphones quarterly sales data from January to March. Let us use MAX with FILTER Function to get the smartphone data with maximum sales in the selected month.
The procedure to combine MAX and FILTER is,
Select cell F2, enter the formula =FILTER(A2:D7,B2:B7=(Max(B2:B7))) and press “Enter”, as shown below.
The output is shown above. We have taken the January sales into account. Therefore, the formula first finds the maximum sales of the Jan month and then filters the respective smartphone sales details, as shown above.
Example #3 – MAX with XLOOKUP Function.
Consider a company’s important events and their respective dates. We will find the highest date value using MAX with XLOOKUP Function.
The procedure to combine MAX with XLOOKUP is,
Select cell A9, enter the formula =XLOOKUP((MAX(B2:B6)),B2:B6,A2:A6) and press “Enter”, as shown below.
Example #4 – Highlight the MAX Value.
The dataset given below consists of laptop brands and the units ordered. Let us use the Conditional Formatting method to highlight the MAX value.
The steps to highlight the max value using the Conditional Formatting are as follows:
Step 1: First, let us apply “Conditional formatting”, as follows:
Choose the data range C2:C9, select the “Format” tab à click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.
[Note: We have selected the column with the numeric value to avoid any confusion or clutter in the final result.]
Step 2: Now, select the “Single color” tab and under the “Format rules” section,
- First, click the “format if…” drop-down, select the “Custom formula is” option and enter =C:C=MAX(C:C) in the “Value” field that appears, as soon as we select from the drop-down.
- Next, in the “Formatting style” options, select the color “Cyan” from the “Fill color” option.
- Finally, click the “Done” button, as shown below.
[Note: In the custom formula entered, we have entered the entire column as a cell range. This is useful when we add further data to the dataset.]
We will get the following output with the maximum value highlighted.
Important Things To Note
- The MAX in Google Sheets ignores the cell references of empty cells, logical values and text strings.
- It also ignores the logical values, but counts those that are supplied to it directly. It counts the values “TRUE” and “FALSE” as 1 and 0, respectively.
- It counts the text representations of numbers irrespective of whether they are supplied directly (in double quotes) or as cell references. For instance, the formula =MAX(5,9,“4”) returns 9.
- When the text strings are supplied directly within double quotes, it returns the “#VALUE!” error. For instance, the formula =MAX(1,2,“t”,“r”) returns the “#VALUE!” error.
Frequently Asked Questions (FAQs)
A few reasons the MAX Google Sheets Function may not work are,
The argument values or cell reference are not organized properly.
The cell range selected is modified or deleted.
We have inserted the result cells below the dataset and new data is added in the dataset.
The cell value for the arguments given directly are inserted with double-quotes.
There is combination of values, such as numeric values and dates are in the selected dataset.
We often forget in which category a function falls, here, the “MAX” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab à click the “Function” option right arrow → click the “All” option right arrow → select the “MAX” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Also, we can use the same path to insert other MAX functions, such as MAXA, MAXIFS, etc.
Alternatively, we can find the Functions icon to insert the MAX in Google Sheets by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “MAX” function, as shown below.
We can insert the Google Sheets IF function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Logical” option right arrow → select the “IF” function, as shown below.
Download Template
This article must help understand MAX in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is MAX in Google Sheets. We learn its syntax & how to use it to find the maximum value of a dataset with detailed examples. You can learn more from the following articles. –
Leave a Reply