Excel MAX: Ignore #DIV/0! Errors

Errors & fixes · Updated November 18, 2026

Yes. With values in A2:A10, use =AGGREGATE(4,6,A2:A10) to return the largest numeric value while ignoring #DIV/0! and other error values in that range. The 4 selects MAX, and option 6 tells Excel to ignore errors.

Why MAX returns an error

Excel’s MAX function finds the largest number in its arguments, but it does not automatically skip errors in a referenced range. If even one cell being evaluated contains an error such as #DIV/0!, MAX generally returns that error instead of the largest number. That can happen even when other cells contain perfectly usable values.

A #DIV/0! error usually means a formula tried to divide by zero or by an empty cell. For example, a column of rates might calculate each amount divided by a quantity; a row with a zero quantity can produce the error. The error is useful because it flags a problem in the calculation, but it can also prevent a summary formula from reporting the largest valid result.

It is worth separating two issues: the calculation error in the source data and the summary calculation that reads it. AGGREGATE lets the summary continue without changing the original cells. That is often preferable when you still want the underlying errors to remain visible for checking.

The recommended formula: AGGREGATE

Assume the values you want to compare are in A2:A10. Use =AGGREGATE(4,6,A2:A10). This is the simplest option for an ordinary range containing a mixture of numbers and errors.

AGGREGATE has several operations and error-handling options. In this formula, the first argument, 4, means MAX. The second argument, 6, means ignore error values. The final argument is the range to evaluate. As a result, the function compares the usable numeric values and leaves error cells out of the calculation.

The same option ignores error types beyond #DIV/0!, including #N/A, #VALUE!, #REF!, and #NUM!. That is useful if a range can contain different errors, but it also means AGGREGATE will not tell you which error occurred or repair its cause. It only prevents errors in the selected reference from stopping this particular maximum calculation.

AGGREGATE is available in Excel 2010 and later, including Microsoft 365. The formula syntax is the same in Excel for Windows, Excel for Mac, and Excel for the web. If a workbook must be opened in an older Excel version, check compatibility before relying on it.

Worked example

Suppose A2:A6 contains 12, #DIV/0!, 7, 18, and #DIV/0!, respectively. A normal MAX calculation over all five cells returns an error because the range contains errors. With the values in A2:A6, use =AGGREGATE(4,6,A2:A6); the result is 18, the greatest numeric value in the range.

The formula does not alter A3 or A6. Those cells still show #DIV/0!, so you can investigate the source calculations separately. If the valid numbers were all negative, AGGREGATE would still select the greatest of those numbers: for example, -2 is greater than -9. This matters when choosing a substitute formula that changes errors to zero, because zero could incorrectly appear to be the maximum in an all-negative set.

AGGREGATE also ignores errors only in the reference you give it. If an error is in A11 but the formula refers only to A2:A10, it does not affect the result. Conversely, expanding the range to include A11 means AGGREGATE will skip that error too, while a regular MAX may then return an error.

Should hidden rows count?

Option 6 ignores errors, but it does not exclude rows hidden manually or filtered out. If the intended maximum should use only visible rows, choose option 7 instead. With values in A2:A10, use =AGGREGATE(4,7,A2:A10). Option 7 tells AGGREGATE to ignore both error values and hidden rows.

This distinction matters in reports. A filtered list might show only one department or date range, while the underlying range still contains other records. Option 6 calculates the maximum across the full referenced range, including hidden or filtered-out records. Option 7 is designed for calculations that should follow visibility, but test the result with your workbook’s filtering and manually hidden rows so it matches the reporting rule you actually need.

Alternatives and when to use them

If you have a reason to avoid AGGREGATE, Excel 365 can also use IFERROR to replace errors temporarily while MAX calculates. With values in A2:A10, use =MAX(IFERROR(A2:A10,"")). In current Microsoft 365, this evaluates the range as an array; the empty text returned for errors is not treated as a number by MAX. This keeps negative values from being displaced by a zero, unlike a zero fallback.

That alternative is less direct than AGGREGATE and depends on array evaluation. In older Excel versions, the formula may require legacy array entry, and compatibility can be less straightforward. For a simple maximum that skips errors, AGGREGATE is generally easier to read and maintain.

You can also prevent some errors at their source by checking the denominator before dividing, or by using IFERROR in the original calculation. That approach changes what the source cell displays, so decide whether hiding the error is appropriate. For a shared workbook, preserving visible errors while using AGGREGATE for a summary can make data issues easier to spot.

Avoid changing errors to zero without considering the data. With values in A2:A10, =MAX(IFERROR(A2:A10,0)) may seem convenient, but if every valid number is negative, the substituted zero becomes the reported maximum. It can also produce a misleading result if zero has a real meaning in your analysis. Use a zero fallback only when zero is a valid and intended replacement for every error in that range.

Common problems and checks

  • The result is still an error: Confirm the formula uses AGGREGATE with function number 4 and option 6, and check whether the error is coming from a different part of the formula or from a separate cell reference.
  • The result is 0 or unexpectedly small: Check whether the range contains actual numeric values. Numbers stored as text may not be treated as numbers by MAX, and AGGREGATE follows MAX’s treatment of the referenced values.
  • The result includes a filtered-out value: Option 6 does not skip hidden rows. Consider option 7 if hidden rows should not count.
  • The largest expected value is missing: Check that the range includes the correct cells and that the value is numeric rather than text. Also check for leading apostrophes or imports that stored numbers as text.
  • The formula is not accepted: Verify the commas and parentheses, and confirm the workbook is opened in a version that supports AGGREGATE. Some regional Excel settings use semicolons instead of commas as formula separators; use the separator configured for that installation.

Also check whether the maximum should include zeros, dates, or percentages. Excel stores dates and percentages as numbers, so MAX can compare them numerically. The formula does not know the meaning of a value; it simply finds the largest number included in the selected reference. Format the result appropriately if the range represents dates or percentages.

Frequently Asked Questions

Does MAX ignore #DIV/0! errors automatically?
No. A #DIV/0! error inside the referenced range can make MAX return an error. Use AGGREGATE with function number 4 and option 6 to ignore errors while finding the maximum.

Does AGGREGATE ignore only #DIV/0! errors?
No. Option 6 ignores error values generally, not just #DIV/0!. That includes errors such as #N/A and #VALUE!. It does not correct them or explain why they occurred.

Can I ignore errors and hidden rows at the same time?
Yes. Use AGGREGATE with function number 4 and option 7. Option 7 ignores error values and hidden rows, unlike option 6, which ignores errors but still includes hidden rows.

Will AGGREGATE ignore numbers stored as text?
Do not assume it will convert text to numbers. MAX generally ignores text in referenced cells, so convert imported text values to real numbers if they need to participate in the comparison.

Does the formula work in Excel for Mac or the web?
AGGREGATE uses the same formula syntax in current Excel for Mac and Excel for the web. Depending on regional settings, your installation may require semicolons rather than commas between arguments.

Choosing the right approach

For most Microsoft 365 workbooks, AGGREGATE is the clearest answer: use function number 4 to request the maximum and option 6 to skip errors. Choose option 7 when hidden rows must also be excluded. Use IFERROR as an alternative when you specifically want to transform errors for the calculation, and be deliberate about the replacement value. Whichever formula you choose, keep the source errors visible when they signal data-quality or calculation issues that still need attention.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.