The Blue Whale is the largest animal in the world, ranging in length from 80 to 100 feet. Imagine a 10-story-tall animal swimming in the sea, surely you will not miss it! Now imagine you’re in a sea of numbers, how can you spot the “blue whale”? Think of a financial analyst, financial modeler, CFO, accountant, etc. crunching numbers and they need to find the largest number of units sold, the largest order delivered to a customer, or the largest amount of expense for the past 5 years. How to find these large values? By using the MAX function in Excel.

The MAX formula in Excel results in the largest value from a set of values. In this article, we will discuss the fundaments of Excel’s MAX function, the parts of what composes the MAX formula, how to use the function, and errors encountered.

The Method Behind Excel’s MAX Function

The MAX function in Excel is under the Statistical Functions and its main purpose is to identify the largest value among numerical values.

The MAX formula in Excel:

Where:

Number1 – Required argument that represents a set of numeric values to search the largest values.

[Number2], … An optional argument that represents a set of numeric values to search the largest values.

Keep in mind below key points when using MAX formula in Excel:

• Aside from numeric values, both arguments can either be an array of cells or cell references that contain numbers.

• If you input logical values and text representations of numbers into the arguments, Excel will consider all these.
• If both arguments do not contain numeric values, the MAX formula in Excel will result in zero.
• If any of the arguments contain error values, the MAX formula in Excel will result in errors too.

Examples On How to Use MAX Function in Excel

To illustrate how to use the MAX function in Excel, we will use the 4-column (Customer Name, Date, Units Sold, and Total Sales) table below to answer these questions:

1. What is the largest number of units sold?
2. What is the largest (highest) amount of total sales?
3. What is the largest number of units sold to a customer?
4. What is the largest (highest) number of units sold to a customer within the 1^st half of the year?

Basic Example of Using Excel’s MAX Function

Answering questions 1 and 2 is simple and easy, this is the basic use of MAX function in Excel.

1. What is the largest number of Units Sold?

Column C contains the number of units sold and the data needed so this is the required argument, Number1, in the MAX formula in Excel. Recall that [Number2], … in the formula is only an optional argument.

Excel’s MAX formula =MAX(C2:C11) results in 21,000 units.

• What is the largest (highest) amount of Total Sales?

Column D contains the amount of Total Sales and the data needed to use this as the required argument, Number1, in the MAX formula in Excel.

Excel’s MAX formula =MAX(D2:D11) results in $650,000.

Excel’s MAX Function with IF Statement (Single Criteria)

The MAX function in Excel like any other function can be combined with another Excel function. Answering Questions 3 and 4 will require the help of another Excel function – IF Function.

• What is the largest number of Units Sold to a customer?

Question 3 takes Question 2 to another step by specifying criteria or condition- the Customer Name. Question 2 did not consider this, it simply asks the largest number of units sold regardless of the customer these were sold to.

To answer Question 3, the criteria, Customer Name, must be met or satisfied first before Excel’s MAX function identifies the largest value.

Let’s start with the first customer on the list, John & Co. The MAX formula in Excel {=MAX(IF(A2:A11=G2,C2:C11))} results in 11,000 units sold.

The Number1 argument is the IF statement itself, IF(A2:A11=G2,C2:C11).

• The Logical Test A2:A11=G2, compares the data in the cell range A2:A11 against cell G2 which contains the Customer Name- John & Co. (take note we are using a cell reference, you can also directly enter the customer name =”John & Co.” in the formula).
• Based on satisfying the logical test, this will result in either True or False.
• If the result is True, then all values from cell range C2:C11 will be used for MAX Function in Excel to work on.
• If the result is False, then essentially the MAX Function in Excel will have zero values to work on.

Finally, the MAX Function in Excel will identify the largest value out of the numerical values matching the logical test or criteria.

It is important to point out that the MAX formula in Excel {=MAX(IF(A2:A11=G2,C2:C11))} is an array formula. To enter or edit an array formula, use CTRL + Shift + Enter.

Excel’s MAX Function with IF Statement (Multiple Criteria)

• What is the largest (highest) number of Units Sold to a customer within the 1^st half of the year?

To answer Question 4, two conditions must be met, and thus, two logical tests must be performed.

Criteria #1- The specified Customer Name. For this example, assume Peter & Sons (use the logical test =”Peter & Sons”).

Criteria#2- The Date must be within the 1^st half of the year (use the logical test less than < 30 June 2018).

To determine the largest values based on more than one criteria, use a Nested IF Statement. Using the two specified criteria, Excel’s MAX formula with Nested IF Statement is:

{ =MAX(IF(A2:A11=G2,IF(B2:B11<G3,C2:C11)))}

Tip: An array formula { } performs multiple calculations, use CTRL+ Shift + Enter to enter or edit array formulas.

Using the MAX Function in Excel, the answer to Question 4 is 5,000 units sold. Simply, the largest number of units sold to the customer “Peter & Sons” is 5,000 units.

The Number1 argument is the Nested IF statement IF(A2:A11=G2,IF(B2:B11<G3,C2:C11).

• The Logical Test 1 A2:A11=G2, compares the data in cell range A2:A11 against cell G2 which is the Customer Name- Peter & Sons.
• The Logical Test 2 B2:B11<G3, compares the data in cell range B2:B11 against cell G3 which must be less than 30 June 2018.
• Based on meeting the logical tests, this will result in either True or False.
• If the result is True, then all values from cell range C2:C11 will be used for MAX Function in Excel to work on.
• If the result is False, then essentially the MAX Function in Excel will have zero values to work on.

Finally, the MAX Function in Excel will identify the largest value out of the numerical values matching the 2 conditions.

The MAX IF formula in Excel is useful in identifying the largest value in a data given a certain set of conditions.

Final Points on the MAX Function in Excel

• The MAX Function in Excel works with numerical values only, it ignores non-numeric values and blank cells. If all arguments in the MAX formula in Excel are non-numeric, the result is zero.
• A text representation of a numerical value will not be ignored if entered directly into the MAX formula in Excel as an argument.
• Excel’s MAX formula can accommodate 1 to 255 arguments which can be cell references, ranges, formulas, or functions.
• Excel‘s MAX function can also be accessed under the AutoSum Formula: