Excel MAX Ignore #N/A: Two Reliable Formulas

Errors & fixes · Updated November 18, 2026

To make Excel’s MAX ignore errors such as #N/A, use AGGREGATE with function number 4 and option 6. For a range in A2:A10, enter =AGGREGATE(4,6,A2:A10). If you want to ignore #N/A specifically while allowing other errors to remain visible, use MAX with IFNA instead.

How MAX treats #N/A

MAX returns the largest numeric value in its arguments, but it does not skip an error in a referenced range. If even one cell in the range contains #N/A, #VALUE!, or another error, a normal MAX formula returns an error rather than the largest available number. This can happen when a source formula uses a lookup that has no match, or when imported data already contains errors.

MAX does ignore some nonnumeric contents in a referenced range. Blank cells, text, and logical values in cells are not treated as numbers by MAX. That behavior is different from error handling: an error is not just another kind of text, so it can stop the calculation. Also, a numeric zero is a genuine number, not a blank. If zero is the largest valid value in your data, MAX can return zero.

The right solution depends on what you mean by “ignore NA.” If you want the calculation to skip every error type, AGGREGATE is concise. If you want it to skip only #N/A and still flag other problems, IFNA is more selective.

Use AGGREGATE to ignore errors

AGGREGATE can perform a range of calculations while applying an option to ignore certain values. Its first argument selects the calculation: 4 means MAX. Its second argument controls what to ignore: 6 means ignore error values. The third argument is the range to evaluate.

With values or formulas in A2:A10, use =AGGREGATE(4,6,A2:A10). This returns the largest numeric value in the range while skipping errors such as #N/A and #VALUE!. For example, if A2:A6 contain 12, #N/A, 19, a blank cell, and #VALUE!, the result is 19.

This is often the easiest answer when a report should keep calculating even if several source cells contain errors. It also avoids entering an array formula: type the formula in the result cell and press Enter. The trade-off is that option 6 ignores all error values, not just #N/A. If an unexpected #REF! or #DIV/0! appears in the range, AGGREGATE hides it from this calculation too. That may be useful for a resilient summary, but it can also conceal a data problem that deserves attention.

AGGREGATE is available in modern Excel, including Microsoft 365. It was introduced in Excel 2010, so a workbook intended for people using Excel 2007 or earlier may need a different approach. In Microsoft 365 for Windows and Mac, the formula syntax is the same.

Use IFNA when only #N/A should be ignored

IFNA replaces #N/A with a value you choose, while leaving other error types alone. If the replacement is an empty text string, MAX can ignore that text in the array and calculate from the remaining numbers. This is a useful distinction when #N/A is an expected “no match” result but other errors should still be investigated.

With the values to check in A2:A5, enter =MAX(IFNA(A2:A5,"")) in Microsoft 365 for Windows. If the cells contain 12, #N/A, 19, and a blank, the result is 19. IFNA changes only the #N/A entry to empty text; MAX then evaluates the remaining numeric values. In Microsoft 365, press Enter to calculate it.

If A2:A5 also contains #VALUE!, the formula returns #VALUE! rather than quietly hiding it. That is intentional: IFNA catches #N/A, not every possible error. IFERROR is broader and replaces any error, so using it instead would make the formula less selective.

In older Excel versions without dynamic arrays, formulas that pass a range through a function may require array-formula entry. Microsoft 365 handles this example directly. If you maintain a workbook for older versions, check the behavior in the target version before distributing it; AGGREGATE may be the simpler compatible option if ignoring all errors is acceptable.

A worked example with both methods

Suppose a sales worksheet has results in A2:A6: 12, #N/A, 19, a blank, and #VALUE!. A regular MAX formula fails because the range contains errors. AGGREGATE returns 19 because it skips both errors. An IFNA-based MAX would still return #VALUE!, because it is designed to expose errors other than #N/A.

To compare the methods on data containing only #N/A errors, use a separate range. With 12, #N/A, 19, a blank, and 7 in A2:A6, =AGGREGATE(4,6,A2:A6) returns 19, and =MAX(IFNA(A2:A6,"")) also returns 19 in Microsoft 365. The two formulas agree on the maximum, but they differ in how they treat any other error that might later appear in the range.

That difference matters as a workbook changes. A formula that is safe today because the only errors are lookup misses may later encounter a broken reference or invalid calculation. Choose whether those new errors should be ignored or made visible, then use the method that matches that decision.

Choose the method that fits your data

  • Use AGGREGATE when the goal is to find the maximum while skipping all error values. It is brief and works directly on a range.
  • Use MAX with IFNA when #N/A means “no result” but other errors should remain visible. This is usually the better choice when errors can signal a real formula problem.
  • Use a helper column when the workbook needs to show which source values were accepted or rejected, rather than only producing a summary number.
  • Fix the source formula when #N/A is unexpected. Hiding an error in a summary does not repair the lookup or the data that produced it.

Think about the meaning of #N/A in your worksheet before choosing. In a lookup table, it may simply mean there is no matching item yet. In a complete monthly report, it could instead mean a key record is missing. A formula that suppresses errors makes a cleaner result, but a clean result is not always a correct result.

Why not use IFERROR for every case?

IFERROR can replace #N/A, #VALUE!, #REF!, #DIV/0!, and other Excel errors. That makes it convenient, but it can hide mistakes that you would rather find. For example, a misspelled reference may produce #NAME? or a deleted cell may produce #REF!. If the goal is specifically to tolerate missing lookup results, IFNA expresses that intent more clearly.

AGGREGATE option 6 also ignores every error in the chosen range. It is not equivalent to “ignore #N/A only.” If your data quality process depends on seeing unexpected errors, use IFNA or correct the source cells rather than suppressing all error types in the MAX calculation.

Neither approach changes the original cells. The source range still displays its #N/A or other errors; only the result of the summary formula is affected. That can be helpful because you can retain the underlying diagnostic information while making a separate summary cell calculate.

Use a helper column for visibility

A helper column can make the treatment of errors easier to audit. For example, you might create a cleaned column that converts #N/A results to blanks, then calculate MAX from that cleaned column. This makes it possible to inspect the original result beside the value used in the summary. The main drawback is that it adds another column and requires the helper formula to be filled down consistently.

This approach is especially useful when each row has a separate lookup result and users need to see which entries were missing. For a single summary cell, AGGREGATE or MAX with IFNA is usually simpler. For a shared workbook, clear column headings such as “Source result” and “Value used” can prevent readers from assuming that missing results were real zeros.

Be careful about replacing missing values with zero. Zero is numeric, so MAX will include it. That does not usually change the maximum when positive values are present, but it can change the answer when every valid value is negative. A missing result is not automatically the same thing as a value of zero.

Empty ranges and no usable numbers

A related edge case is a range with no numeric values left after errors are ignored. If every cell is #N/A, there is no meaningful maximum to report. Depending on the method and range contents, the result may be zero or an error; neither necessarily means the data contained a real zero. Decide how a report should represent “no usable values,” such as with a message or a separate status indicator.

For a reliable report, check whether the range contains any numbers before interpreting its maximum. COUNT counts numeric cells and ignores text and errors in a referenced range. A zero count means there are no numeric inputs for MAX to compare, even if the range contains error messages or text. If zero is a possible legitimate maximum, distinguish it from the no-data case rather than relying on the displayed number alone.

Also check whether cells that look empty actually contain formulas returning an empty string. MAX generally ignores text in a referenced range, but the formula may still need a deliberate no-data rule if all results are blank or errors. Test the edge case with a copy of the real data before using the summary in a report.

Frequently Asked Questions

Does MAX ignore #N/A automatically?
No. MAX ignores blanks and text in referenced cells, but an error such as #N/A normally causes the MAX calculation to return an error. Use AGGREGATE or IFNA to handle it.

How do I ignore only #N/A and not #VALUE!?
In Microsoft 365, wrap the range in IFNA and pass the result to MAX. IFNA replaces #N/A only, so another error such as #VALUE! remains visible instead of being silently skipped.

Does AGGREGATE ignore all errors?
Yes, when you use function number 4 for MAX and option 6. That option ignores error values generally, not only #N/A. Use IFNA when you need other error types to continue surfacing.

Why does my formula return zero when every value is #N/A?
There may be no numeric values for MAX to evaluate after the errors are replaced or ignored. A zero result in that situation should not be assumed to represent a genuine zero in the source data; add a separate no-data check if the distinction matters.

Will these formulas change the cells containing errors?
No. They affect only the calculation in the result cell. The original cells continue to show their errors, so you can still inspect or fix the source data.

Should I use IFERROR instead of IFNA?
Use IFERROR only if you intend to treat all error types alike. If the expected issue is a missing match represented by #N/A, IFNA is more precise and less likely to hide an unrelated formula problem.

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.