To find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN, and option 6 tells AGGREGATE to ignore errors; replace A2:A10 with your range.
What MIN does with errors
Excel’s MIN function returns the smallest numeric value in its arguments, but it does not skip error values in a referenced range. If even one cell in the range contains an error such as #N/A, #VALUE!, or #DIV/0!, a regular MIN formula returns an error instead of the smallest remaining number. This can happen even when the range contains plenty of valid numbers.
MIN does already ignore some nonnumeric cells. When you refer to a range, blank cells and text entries are not treated as numbers. Zero, however, is a number and can be the minimum. These behaviors are separate from error handling: an error is not simply another kind of text that MIN quietly skips.
That distinction matters when a range combines manually entered values with formulas. A formula that divides by zero, looks up a missing item, or receives invalid input can put an error into the range and prevent MIN from returning a result. The solution is to choose a method that deliberately excludes errors without accidentally excluding valid numbers such as zero.
Use AGGREGATE to ignore errors
AGGREGATE is usually the most direct choice for this task. It supports several calculation types, including MIN, and lets you choose which values to ignore. In the formula below, 5 means MIN and 6 means ignore error values.
With the values to check in A2:A10, use =AGGREGATE(5,6,A2:A10). For example, if A2:A10 contains 12, #N/A, 4, #DIV/0!, a blank, 9, and three more values larger than 4, the result is 4. The errors are skipped, while valid numbers remain eligible to be the minimum.
The formula’s arguments are positional: the first argument selects the calculation, the second sets the ignore option, and the third supplies the range. To adapt it, change only the range unless you intentionally want a different calculation or ignore behavior. AGGREGATE is available in Excel 2010 and later, including Microsoft 365. The formula syntax is the same in current Excel for Windows, Mac, and the web.
- 5: calculate MIN.
- 6: ignore error values.
- A2:A10: use the cells in this range as the input.
If your data occupies a noncontiguous set of cells, AGGREGATE’s reference form can accept additional references as arguments. For ordinary workbooks, a single rectangular range is easier to read and maintain.
Compare the main formula options
AGGREGATE is concise, but it is not the only approach. In Microsoft 365 or Excel 2021 and later, FILTER can first keep numeric entries and then pass those entries to MIN. This can make the filtering rule explicit, particularly when you also want to exclude text and blanks rather than merely ignore errors.
With the values in A2:A10, use =MIN(FILTER(A2:A10,ISNUMBER(A2:A10))). If the range contains 12, #N/A, 4, and 9, the formula returns 4. ISNUMBER marks numeric cells as eligible; FILTER returns those cells; MIN finds the smallest result. Since errors are not numbers, they do not pass the test.
FILTER has an important edge case: if there are no numeric entries, it returns a #CALC! error because it has no results to return. AGGREGATE is often a simpler choice if you only need to skip errors. If you choose FILTER, decide what the formula should show when no numbers qualify instead of assuming that case cannot occur.
Another option in Microsoft 365 is to replace errors with empty text inside an array calculation. With the values in A2:A10, use =MIN(IFERROR(A2:A10,"")). IFERROR substitutes an empty string for each error, and MIN ignores text in the resulting array while evaluating the numbers. This can be useful when you want the error replacement to be visible in the formula itself, but AGGREGATE is generally easier to interpret at a glance.
Choose the right method for your data
Pick a formula based on what should count as a candidate, not just on which formula looks shortest. If every numeric value should count and only errors should be excluded, AGGREGATE with option 6 is a good default. It leaves valid zeros and negative values in the calculation.
Use FILTER with ISNUMBER when the rule is specifically to retain numeric values. That approach excludes text as well as errors, and it makes the eligibility test easy to extend. For instance, a data-cleaning task may require a minimum from numbers only, even if someone has typed notes into the same column. Keep in mind that FILTER requires Excel 2021 or Microsoft 365 and needs a plan for an empty result.
A helper column can be useful when people need to inspect cleaned data row by row. For example, a separate column can turn an error into a blank while preserving other cell contents; then a normal MIN can reference that helper range. This takes more worksheet space, but can make the cleanup visible and easier to audit. It is also useful when several later calculations need the same cleaned values.
Before choosing, check whether text that looks like a number should count. A cell containing the text "4" is not necessarily the same as a numeric 4 for MIN. MIN generally ignores text in a referenced range, while FILTER with ISNUMBER excludes it deliberately. If imported data contains numbers stored as text, convert or clean those values rather than assuming an error-ignoring formula will interpret them as numbers.
Hidden rows and filtered lists
Ignoring errors and ignoring hidden rows are different requirements. AGGREGATE option 6 ignores errors but does not ask the function to exclude hidden rows. If you also want to ignore hidden rows, option 7 combines those two behaviors.
With the values in A2:A10, use =AGGREGATE(5,7,A2:A10) to calculate the minimum while ignoring errors and hidden rows. This is useful when a worksheet uses hidden rows to remove records from a visible summary. Be deliberate about the choice: option 7 can change the answer when a hidden row contains the lowest number, whereas option 6 still considers that number.
Do not use option 7 just because the range is filtered. First check that the result matches the behavior you need for the way your list is filtered or hidden. A clear test is to make the smallest value temporarily hidden and compare the result with option 6. That reveals whether the formula is treating the rows the way your report requires.
Common problems and checks
- The result is still an error: Check that the formula uses option 6 or 7 and that its reference covers the intended cells. A different error in the formula itself, such as a misspelled function or invalid reference, is not fixed by choosing an error-ignoring option.
- The result is zero: Check whether zero is genuinely present. MIN includes zero; it does not treat zero as blank. Also check whether the range has any numeric values at all, because a minimum cannot be meaningfully inferred from text and errors alone.
- A number stored as text is missed: Convert the source value to a real number if it should participate. Formatting a text value to look like a number does not necessarily change its underlying type.
- The minimum seems too low: Look for valid negative values or zeros, and confirm whether hidden rows should count. An error-ignoring formula does not automatically remove outliers or incorrect numeric entries.
- The FILTER formula shows
#CALC!: The range may have no numeric values that pass ISNUMBER. Add a deliberate no-match strategy or use a method suited to the data.
It is also worth checking the source errors rather than automatically suppressing them. A #N/A value might mean a lookup has not found a match; a #DIV/0! value might indicate an unexpected zero denominator. Ignoring these errors can be appropriate for a summary, but it does not repair the underlying data or explain why a row failed.
Handle a range with no usable numbers
An error-ignoring minimum is useful only if at least one valid number remains. If every cell is an error, blank, or nonnumeric text, there is no meaningful minimum to report. Depending on the function and formula path, Excel may return an error or a zero-like result for an input with no eligible numbers, so test this case with your actual layout rather than treating a displayed zero as proof that the data contains a zero.
If you want a friendly message instead of an error, you can wrap the calculation in IFERROR. With the input range in A2:A10, use =IFERROR(AGGREGATE(5,6,A2:A10),"No valid numbers"). This displays the message if the calculation returns an error. However, IFERROR catches any error from the inner expression, not just the specific case where no numbers remain. If errors might indicate a broken reference or another formula problem, investigate them rather than hiding them behind a general message.
For recurring reports, a separate check for numeric inputs or a visible data-quality warning can be more informative than silently returning a default. That is especially useful when the minimum feeds another calculation or a decision. Keep the rule clear: the summary may ignore error cells, while the source data still deserves review.
Frequently Asked Questions
Does Excel MIN ignore errors automatically?
No. A referenced range containing an error usually makes MIN return an error. Use AGGREGATE with function number 5 and option 6 when you want to ignore errors.
What is the formula for MIN while ignoring errors?
With the values in A2:A10, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN and the 6 skips error values.
Does AGGREGATE ignore zero values?
No. Option 6 ignores errors, not zeros. A zero is a valid number and can be the minimum. If zero should be excluded, define that as a separate rule and use a formula that filters for the values you intend to include.
Can MIN ignore errors and hidden rows?
Yes. With values in A2:A10, use =AGGREGATE(5,7,A2:A10). Option 7 tells AGGREGATE to ignore both errors and hidden rows.
Why does my error-ignoring formula return zero?
Zero may be a genuine value in the range, and MIN includes it. If there are no numeric values, check the behavior of the formula and inspect the source cells; do not assume that a zero proves the range contains a numeric zero.
Related
- Excel Not Blank Conditional Formatting: How to Set It Up
- Excel Not Blank Symbol: What <> Means
- Excel Row Limit: How Many Rows Can a Worksheet Hold?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
- Named Ranges in XLOOKUP: How to Use Them in ExcelTo use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name…
- Calculate CAGR Growth in Excel: Formula and ExamplesTo calculate compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning value, raise…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.