Larger of two values

In this example, the goal is to return the greater of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MAX function.

MAX function

The MAX function returns the largest numeric value in the data provided. In Excel, it's common to use the MAX function with a range like this:

=MAX(range) // max value in range

However, because MAX can accept values in separate arguments, you can easily use the MAX function to select the larger of values in two cells like this:

=MAX(A1,B1) // larger of A1 or B1
=MAX(A1,C1) // larger of A1 or C1

This is the approach used in the worksheet shown, where  the formula in E5 is:

=MAX(B5,C5)

As the formula is copied down, it returns the value in column B or the value in column C, whichever is larger. The MAX function can be used to return the largest value from any type of numeric data. This means you can use MAX to solve a variety of "largest of" problems:

• Later of two dates
• Later of two times
• Slower of two times
• Higher of two temperatures
• Larger of two fractions

Alternative to IF function

As this example shows, the MAX function can be used as a compact and elegant replacement for the IF function. For example, in the example shown, the IF function could be used to return the larger of the two values like this:

=IF(B5>C5,B5,C5)

The basic translation of this formula is "If B5 is greater than C5 return B5. Otherwise return C5". This is a perfectly valid Excel formula, and you will often encounter IF formulas that follow this structure. However, the MAX version of the formula is simpler and contains no redundant references, so less prone to errors:

=MAX(B5,C5)

Note that the references to B5 and C5 appear just once in the MAX formula.