How to Use MAX on ArrayFormula in Google Sheets

This guide will explain how to use the MAX function on an ArrayFormula in Google Sheets.

The Array Formula allows you to perform calculations on multiple rows. With the MAX function, we can use the ARRAYFORMULA function to compare values in multiple rows using a single formula.

Let’s take a look at a quick example of a situation where we might need to find the maximum value multiple times.

Suppose you have a list of products that you would like to purchase. For each of these products, you have kept track of their price in four different supermarkets.

You want to find out which stores have the highest markup price. Since you will need to find the maximum value for each row, we can use the MAX function multiple times.

Alternatively, we may also create an Array Formula so that only one formula is used in the spreadsheet. This method is useful for performing this operation on a large dataset. Google Sheets will run much smoother if it only has to run one main formula to compute the column.

With the DMAX, TRANSPOSE, and ARRAYFORMULA functions, we can create a single Array Formula that can return the maximum value per row. The DMAX formula is often used for column-wise comparisons, but our data can be transposed to fit the input.

Let’s learn how to find the maximum values of several rows in Google Sheets and later test out the function with actual values.

A Real Example of Using An ArrayFormula with MAX Function

Let’s look at a real example of an Array Formula that finds the maximum value in a Google Sheets spreadsheet.

In this example, we are comparing product prices for a future project. We have ten unique products with prices we would like to compare. Using the Array Formula for finding the maximum value, we found out that the maximum price the first product was sold for was $21.00.

To get the values in Column C, we just need to use the following formula:

=ARRAYFORMULA(IFERROR( 1 / ( 1 / DMAX( TRANSPOSE(A2:E), SEQUENCE(ROWS(A2:E)), TRANSPOSE( IFERROR( COLUMN(A2:E) / 0)) ) ) ) )

The DMAX function returns the largest number in a column of records in a list or database. Since we’re performing a row-wise search, we will have to use the TRANSPOSE function on our dataset.

If any of these rows returns an error, we will have to catch the error with the IFERROR function. The IFERROR function will default to a null row. This is the reason why we divide the COLUMN output by 0.

Finally, the ARRAYFORMULA function indicates to Google Sheets that the formula passed should be done for each row in the range.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try this ArrayFunction yourself in Google Sheets, follow our guide in the next section!

How to Use MAX on ArrayFormula in Google Sheets

This section will guide you through each step needed to start using an Array Formula to find the maximum value for each row in a range. The formula will allow the user to compute the maximum value for each row in a range without using the Fill Handle tool to copy a MAX formula.

Follow these steps to start using the Array Formula method:

1. First, add a new column to your table. This column will hold the contents of our Array Formula. In this example, we’ve added a new header under column F.
   
2. Next, we must type the Array Formula method shown in the previous section. Make sure that the ranges referenced correspond to your sheet’s actual range of values.
   
3. Hit the Enter key to return the array output of the formula. In this example, we got the highest price for each product.
   
4. If we need to add new columns, we simply need to adjust the range being referenced. If new rows are added, the Array Formula will automatically compute the maximum value for each added row.
   

Frequently Asked Questions (FAQ)

1. What is the advantage of using the Array Formula?
   The Array Formula method is generally quicker to compute when dealing with a large number of entries. These functions are also future-proof. If the user decides to add more rows to a sheet, the ARRAYFORMULA function will adjust dynamically to the new data. The typical method of dragging down the formula may be more convenient but also prone to errors.

That’s all you need to remember to start using the Array Formula in Google Sheets to find the maximum value. This step-by-step guide shows how we can use a single formula to find the highest price for every product in a table. 

The ARRAYFORMULA function is a powerful function in Google Sheets that works best when combined with other functions. With so many other Google Sheets functions out there, you can surely find one that suits your use case. 

Are you interested in learning more about what Google Sheets can do? 

Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us. 

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