To find the largest value in Excel while ignoring error cells, use =AGGREGATE(4,6,A2:A10). In this formula, 4 selects MAX and 6 tells AGGREGATE to ignore errors, so a value such as #DIV/0! will not stop Excel from returning the largest valid number.
Why MAX returns an error
The regular MAX function finds the highest number in its arguments, but it does not skip errors in a referenced range. If even one cell in the range contains an error such as #N/A, #VALUE!, or #DIV/0!, MAX usually returns an error instead of the largest number. That can be frustrating when the error is expected, temporary, or unrelated to the values you are trying to compare.
MAX does already overlook some nonnumeric entries when they are in a cell range: text, blank cells, and logical values in referenced cells are not treated as numbers. The problem addressed here is specifically error values. It is also useful to distinguish errors from numbers stored as text. A cell containing the text 25 is not necessarily treated like the numeric value 25 by MAX; ignoring errors does not convert text into numbers.
AGGREGATE provides a way to apply a calculation such as MAX while choosing whether certain conditions should be ignored. Its error-handling option is useful when a range can contain both valid numbers and formula errors.
The basic AGGREGATE formula
Suppose cells A2:A10 contain measurements, and some formulas in that range might produce errors. With the data in A2:A10, use:
=AGGREGATE(4,6,A2:A10)
The result is the largest numeric value in A2:A10 that AGGREGATE can evaluate without treating error cells as fatal. For example, if the range contains 12, 31, #N/A, 18, and #DIV/0!, the formula returns 31. The error cells are skipped; the remaining valid numbers are still compared.
The arguments have distinct jobs:
- 4 is the function number for MAX.
- 6 is the option to ignore error values.
- A2:A10 is the reference containing the values to evaluate.
AGGREGATE was introduced in Excel 2010 and is available in current Microsoft 365 for Windows. The formula syntax is the same in Excel for Mac and Excel for the web. If a workbook is opened in an older Excel version that does not support AGGREGATE, the formula may not calculate there.
What the option number changes
The second argument controls what AGGREGATE ignores. For the question of ignoring errors, option 6 is generally the straightforward choice: it ignores errors but does not intentionally exclude hidden rows. That distinction matters in filtered lists and reports.
- 6 ignores error values.
- 7 ignores error values and rows hidden manually or by a filter.
- 5 ignores hidden rows, but does not tell AGGREGATE to ignore errors.
- 0 through 3 also account for nested SUBTOTAL or AGGREGATE calculations, with different combinations of hidden-row and error handling.
For a normal complete-range maximum, option 6 is often appropriate. If the intended answer should reflect only the rows currently visible in a filtered list, option 7 may be more suitable. For example, with scores in B2:B50 and errors mixed into the column, use =AGGREGATE(4,7,B2:B50) to ignore both errors and hidden rows. This changes the meaning of the result: filtering the list can change the maximum.
Do not choose an option merely because its number looks familiar. Decide whether hidden rows should count, and whether nested SUBTOTAL or AGGREGATE results should be excluded. The option list is especially relevant in reports where rows are hidden or calculations are layered.
Worked example and result checks
Imagine A2:A7 contains 14, 27, #DIV/0!, 9, #N/A, and 22. A regular MAX formula referencing that range returns an error because the range includes error values. With the data in A2:A7, the error-ignoring formula is:
=AGGREGATE(4,6,A2:A7)
The answer is 27: among the numeric entries 14, 27, 9, and 22, the largest is 27. The errors do not become zero, and they do not reduce the maximum; they are excluded from this calculation.
A useful check is to compare the result with the valid numeric entries in the range. If the answer seems unexpectedly low, check whether a larger-looking entry is stored as text, whether it is outside the referenced range, or whether it is in a row excluded by the selected option. If the maximum should be based only on visible rows, test the formula after applying and removing the relevant filter.
Dates and percentages can also be handled, because Excel stores dates and percentages as numbers. The maximum date is the latest date serial in the range, and the maximum percentage is the highest numeric percentage. Format the result cell appropriately: a correct date serial displayed in General format may look like an unfamiliar number, while a percentage result displayed as a plain number can be misread.
What happens when there are no usable numbers?
Ignoring errors does not create a valid answer when the range contains no numeric values. If all the cells are errors, text, or otherwise nonnumeric, AGGREGATE cannot identify a maximum number and can return #NUM!. Treat that as a separate condition from successfully finding a maximum.
For a worksheet intended for other people, consider showing a clear message when the input range has no valid numbers. One option is to count numeric cells first. With the possible values in A2:A10, use:
=COUNT(A2:A10)
COUNT returns the number of numeric cells and does not count error values or text. If it returns zero, there is no numeric maximum to display. If it is greater than zero, the AGGREGATE formula can be used to find the largest value while skipping errors. A separate check is useful because a blank-looking result or an error from the maximum calculation may otherwise be mistaken for a data problem.
Be mindful of formulas that return an empty string. Although a cell may look blank, a formula returning text is not the same as a truly empty cell. It is still not a numeric value for MAX. The key question is whether the range contains at least one usable number, not whether it appears to contain visible content.
Alternatives and when to use them
AGGREGATE is usually the simplest choice when the goal is simply to take a maximum while ignoring error cells. A helper column can be easier to audit when the workbook needs to show which source values are valid. With the original value in A2 and a helper formula entered in B2 and filled down, use:
=IFERROR(A2,"")
Then, with the cleaned helper results in B2:B10, use:
=MAX(B2:B10)
The first formula leaves valid values unchanged and returns an empty string for an error. MAX over the helper range ignores those text results in referenced cells, leaving the numeric values to compare. This approach makes the intermediate results visible, but it uses extra cells and should be checked if the source data changes. It also does not convert numeric text to numbers.
Another option is to include only numeric values with an array calculation. With the candidate values in A2:A10, use:
=MAX(IF(ISNUMBER(A2:A10),A2:A10))
In current Microsoft 365, enter the formula normally; dynamic array calculation handles the range. In older Excel versions that support this formula but do not use dynamic arrays, it may need to be confirmed as an array formula with Ctrl+Shift+Enter. On Mac, the equivalent array-entry keys vary by Excel version; check the version’s instructions rather than assuming the Windows shortcut applies. This method selects numeric values, so it excludes errors as well as text and logical values. AGGREGATE is generally easier when the specific intention is to ignore errors while keeping the usual MAX behavior.
MAXIFS is not a substitute for error handling. It calculates a maximum subject to criteria, but it does not mean that errors in the candidate range are automatically ignored. Likewise, IFERROR wrapped around a single MAX calculation can replace the final error with a message, but it does not recover the maximum from the remaining numbers. For example, if MAX fails because one input is an error, hiding that final error does not make MAX skip the problematic cell.
Common problems and troubleshooting
- The result is still an error: Confirm that the formula uses function number 4 and option 6, and that the referenced range is correct. Also check whether the range contains a condition not handled by the chosen formula, such as no numeric values at all.
- A hidden row still affects the answer: Option 6 ignores errors, not hidden rows. Use option 7 if hidden rows should also be excluded.
- A number seems to be skipped: Check whether it is stored as text. Select the cell and inspect the formula bar, or test the source data with a numeric count. Convert text values only if they represent numbers and conversion is appropriate.
- The result is zero: Zero may be the actual largest numeric value, especially if the remaining values are zero or negative. It can also indicate that the input values are not recognized as numbers. Check the range contents rather than assuming AGGREGATE changed errors into zeros.
- The formula name is not recognized: The workbook may be opened in an Excel release older than Excel 2010 or in another spreadsheet application. Confirm compatibility before sharing or converting the file.
Errors can also be signs of upstream issues rather than harmless noise. For example, #N/A may mean a lookup did not find a match, while #DIV/0! may indicate a missing or zero denominator. AGGREGATE can keep a summary maximum working, but it does not repair those source formulas. If the errors indicate missing data that should affect the analysis, investigate them instead of simply excluding them.
Frequently Asked Questions
Does MAX ignore errors in Excel?
No. MAX usually returns an error when an error value is included in its referenced range. Use AGGREGATE with function number 4 and option 6 to calculate a maximum while ignoring errors.
What formula finds the maximum while ignoring #N/A?
With the values in A2:A10, use =AGGREGATE(4,6,A2:A10). Option 6 ignores #N/A and other error values, not only that one error type.
Does AGGREGATE ignore hidden rows?
It depends on the option. Option 6 ignores errors but includes hidden rows; option 7 ignores both errors and hidden rows. Choose based on whether filtered or manually hidden data should count.
Can I ignore errors but include negative numbers?
Yes. AGGREGATE still compares valid numeric values normally, including negative numbers and zero. If every valid number is negative, the maximum is the negative number closest to zero.
Why does my formula return #NUM! when errors are ignored?
The range may contain no numeric values for AGGREGATE to compare. Check for at least one valid number; text, empty strings, and error values do not provide a numeric maximum.
Choosing the right approach
Use =AGGREGATE(4,6,range) when you want the largest numeric value and error cells should not interrupt the calculation. Choose option 7 instead when hidden rows should be excluded as well. Use a helper column when you want to inspect or document the cleaned values, and consider an array formula when you specifically need to filter for numeric entries. Whatever method you choose, verify that the range includes the intended cells and that skipping the errors is appropriate for the question your worksheet is meant to answer.
Related
- Excel Value Formatting: What It Changes and How to Use It
- Text to Excel Converter: Import Text Into Columns
- Excel Data Entry: Enter and Manage Cell Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Multiple Columns: Formulas and ExamplesTo use SUMIF with multiple columns, add separate SUMIF results for each value column, then combine them with…
- Excel Not Blank Criteria: COUNTIF, SUMIF, and Practical ExamplesIn Excel, use the not-blank criterion "<>" in functions such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS. For example,…
- Excel Date Plus 1 Month: Formula and Month-End RulesTo add one calendar month to a date in Excel, use the EDATE function: if the starting date…
- Excel CONCATENATE Formula Examples and UsesIn Excel, join text with =CONCAT(A2," ",B2) or the ampersand operator, as in =A2&" "&B2; if A2 contains…
- How to Get the Average Number in Excel with a FormulaTo get the average number in Excel, enter =AVERAGE(B2:B10) in an empty cell, replacing B2:B10 with the range…
- Excel Time Math: Formulas for Adding and Subtracting HoursExcel time math treats times as fractions of a 24-hour day: subtract an earlier time from a later…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.