Excel MAX function - formula examples to find highest value

This tutorial explores the Excel MAX function, providing numerous formula examples to find the highest value and highlight the largest number in your spreadsheet. While simple to use, the MAX function offers advanced techniques. This guide covers conditional MAX formulas, extracting the absolute largest value, and more.

- The Excel MAX Function

• Creating a MAX Formula
• Excel MAX Function Examples
  • Finding the Max Value in a Group
  • Max Formula for Non-Adjacent Cells or Ranges
  • Finding the Max Date
  • Conditional MAX Formulas
  • Finding the Max Value, Ignoring Zeros
  • Finding the Largest Number, Ignoring Errors
  • Finding the Absolute Maximum Value
• Highlighting the Max Value
• Troubleshooting the Excel MAX Function

The Excel MAX Function

The Excel MAX function identifies the highest value within a specified dataset.

Syntax:

MAX(number1, [number2], ...) where number can be a numeric value, array, named range, or cell/range reference containing numbers. Number1 is required; subsequent arguments are optional.

The MAX function is compatible with all Excel versions (Office 365, 2019, 2016, 2013, 2010, 2007, and earlier).

Creating a MAX Formula

The simplest MAX formula directly inputs numbers: =MAX(1, 2, 3). However, you'll typically use cell and range references.

The quickest method to find the largest value in a range (e.g., A1:A6):

1. Enter =MAX( in a cell.
2. Select the number range (A1:A6).
3. Add the closing parenthesis ).
4. Press Enter. The formula will be =MAX(A1:A6).

For contiguous rows/columns, Excel's AutoSum feature provides a shortcut:

1. Select the number cells.
2. On the Home tab, click AutoSum and choose Max. (Alternatively, use Formulas > AutoSum > Max).

This inserts a MAX formula in the cell below the selection. Ensure a blank cell exists beneath the selected numbers.

Key MAX Function Considerations:

1. Current Excel versions support up to 255 arguments in a MAX formula.
2. If no numbers are present, MAX returns zero.
3. Errors within arguments result in an error return.
4. Empty cells are ignored.
5. Direct input of logical values and text numbers are processed (TRUE=1, FALSE=0). References to logical/text values are ignored.

Excel MAX Function Examples

This section demonstrates common MAX function applications. Multiple solutions are often available; experiment to find the best fit for your data.

Finding the Max Value in a Group:

Use a range reference for the group (e.g., C2:E7): =MAX(C2:E7)

Max Formula for Non-Adjacent Cells/Ranges:

For non-contiguous selections, reference each cell/range individually. Use Ctrl-click to select multiple areas: =MAX(C5:E5, C9:E9)

Finding the Max Date:

Dates are treated as serial numbers; use a standard MAX formula: =MAX(C2:C7)

Conditional MAX Formulas:

Several approaches exist for finding the maximum value based on criteria. The following examples use the same dataset: items in B2:B15, sales in C2:C15, and the target item in F1.

• Excel MAX IF Formula (Array Formula): =MAX(IF(B2:B15=F1, C2:C15)) (Ctrl Shift Enter)

• Non-Array MAX IF Formula: =SUMPRODUCT(MAX((B2:B15=F1)*(C2:C15)))

• MAXIFS Function (Excel 2019 and Office 365): =MAXIFS(C2:C15, B2:B15, F1)

Get Max Value, Ignoring Zeros:

This is a conditional MAX variation. Use "not equal to 0" in MAXIFS or MAX IF criteria. This is primarily useful with negative numbers. For example, to find the lowest (most negative) discount in C2:C7:

• MAX IF (Array Formula): =MAX(IF(C2:C70, C2:C7)) (Ctrl Shift Enter)
• MAXIFS: =MAXIFS(C2:C7,C2:C7,"0")

Find Highest Value, Ignoring Errors:

Errors in referenced cells propagate to the MAX function. Solutions include:

• MAX IF with ISERROR (Array Formula): =MAX(IF(ISERROR(A1:B5),"",A1:B5)) (Ctrl Shift Enter)
• MAX IF with IFERROR (Array Formula): =MAX(IFERROR(A1:B5,"")) (Ctrl Shift Enter)
• MAXIFS (Limited Use): =MAXIFS(A1:B5,A1:B5,">=0") (Only works if at least one non-negative number exists)
• AGGREGATE Function: =AGGREGATE(4, 6, A1:B5) (4=MAX, 6=ignore errors)

Finding the Absolute Maximum Value:

For positive and negative numbers, find the largest absolute value:

• Array Formula: =MAX(ABS(A1:B5)) (Ctrl Shift Enter)
• Regular Formula: =MAX(MAX(A1:B5), -MIN(A1:B5)) or =MAX(MAX(A1:B5), ABS(MIN(A1:B5)))

Return Maximum Absolute Value with Sign:

To retain the original sign: =IF(ABS(MAX(A1:B5))>ABS(MIN(A1:B5)), MAX(A1:B5), MIN(A1:B5))

Highlighting the Max Value

Conditional formatting offers a visual way to identify the largest number.

Highlight Highest Number in a Range:

Use Excel's built-in "Top or Bottom Ranked Values" rule.

Highlight Max Value in Each Row/Column:

Create a custom rule using a MAX formula (e.g., =C2=MAX($C2:$E2) for rows, =C2=MAX(C$2:C$7) for columns).

Troubleshooting the Excel MAX Function

• MAX Returns Zero: Check for text-formatted numbers. Use =ISNUMBER(A1) to verify numeric data.
• MAX Returns Errors: Examine referenced cells for errors. Use error-handling techniques (like AGGREGATE) to manage errors.

This comprehensive guide provides a solid foundation for utilizing the Excel MAX function effectively. Remember to adapt these examples to your specific data and requirements.

The above is the detailed content of Excel MAX function - formula examples to find highest value. For more information, please follow other related articles on the PHP Chinese website!