Excel MIN Ignore NA: Formulas That Work

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring #N/A and other errors. Excel’s ordinary MIN function does not ignore errors in its referenced range, so AGGREGATE is the direct option when errors should not affect the result.

Why MIN returns an error

MIN finds the smallest number among its arguments, but it does not treat an error value such as #N/A as an ordinary value to skip. If even one cell in a referenced range contains an error, a MIN formula using that range will return an error instead of the smallest available number. This can happen when data is imported, a lookup formula cannot find a match, or a formula deliberately returns NA() to mark a missing observation.

There is an important distinction between an error and text that merely looks similar. A cell containing the text NA is not the same as a cell containing the Excel error #N/A. MIN generally ignores text and blank cells in a range, but an actual error value needs to be handled explicitly. The formula choice depends on whether you want to skip only #N/A or skip every error type.

Use AGGREGATE to ignore errors

AGGREGATE is usually the simplest solution because it can apply a calculation while excluding errors. Its first argument selects the calculation, its second sets which values to ignore, and its remaining argument identifies the data. Function number 5 means MIN; option 6 means ignore error values.

With the values to check in A2:A10, enter =AGGREGATE(5,6,A2:A10). If the range contains 12, #N/A, 4, a blank, 9, and another error, the result is 4. The formula skips the errors and blank cell, then returns the lowest remaining number.

Option 6 ignores errors of any type, not just #N/A. That includes errors such as #VALUE!, #REF!, and #DIV/0!. This is convenient when the goal is simply to get a minimum from the valid numeric entries, but it can also hide a different problem in the source data. If other errors should remain visible, use the more selective approach below.

Ignore only #N/A with IFNA

IFNA replaces #N/A with a chosen alternative while leaving other error types unchanged. In Microsoft 365, it can process a range as an array, allowing MIN to calculate across the cleaned values. With the values in A2:A10, use =MIN(IFNA(A2:A10,"")). If the range contains numbers 12, 4, and 9 alongside #N/A errors, the result is 4.

The empty text produced by IFNA acts as a nonnumeric placeholder, so MIN skips it while evaluating the numeric values. Unlike AGGREGATE with option 6, this method does not deliberately suppress errors such as #VALUE! or #REF!. If one of those errors is present in the range, it can still surface in the result, which may be useful when you want to find and correct problems other than missing matches.

This array formula works directly in Excel for Microsoft 365; it does not need the old Ctrl+Shift+Enter confirmation in that version. It is also available in Excel for the web. Excel for Mac has the same functions, though keyboard shortcuts and ribbon layout can differ. If a workbook must work in an older Excel release, check that version’s array-formula behavior before relying on this approach; AGGREGATE has broader compatibility with releases from Excel 2010 onward.

Worked example and choosing a formula

Suppose column A contains measurements from a process, and a lookup formula returns #N/A when no measurement is available. A small example might have 12 in A2, #N/A in A3, 4 in A4, a blank in A5, 9 in A6, and #N/A in A7. Both recommended methods return 4 because it is the smallest numeric measurement.

  • Choose AGGREGATE when you want to ignore all error values and return the minimum among the remaining numbers.
  • Choose MIN with IFNA when only #N/A represents an expected missing value and other errors should still draw attention.
  • Use ordinary MIN when the range is expected to contain no errors and an error result should signal that something needs investigation.

Neither error-handling method changes the source cells. The #N/A values remain in the worksheet; the formula only controls how the minimum calculation treats them. That means other formulas, charts, or checks can still use or display the original errors.

What happens when there are no numbers?

A minimum is meaningful only if at least one numeric value remains. If every cell contains an error, AGGREGATE with the settings above returns #NUM! because it has no eligible number from which to calculate a minimum. If the range contains no numbers because it is blank or contains only text, a MIN-based calculation may return 0. That zero can be mistaken for a real measurement, so it is worth deciding how an empty result should be presented.

To display a message instead when a range has no numeric values, test the numeric count before calculating the minimum. With data in A2:A10, use =IF(COUNT(A2:A10)=0,"No numeric values",AGGREGATE(5,6,A2:A10)). COUNT ignores text and errors, so this returns the message when there are no numbers; if there is at least one number, AGGREGATE returns the minimum while ignoring errors. For example, a range containing only #N/A values displays No numeric values rather than a misleading zero.

This guard checks for numeric cells, not whether the data is valid for your particular task. If zero is a valid measurement, it is counted and can become the minimum. If zeros should be excluded too, that is a separate condition and needs a different calculation.

Common problems and checks

If a formula does not return the expected result, first check whether the cells contain real numbers, errors, or numbers stored as text. MIN and AGGREGATE calculate with numeric values; text entries that resemble numbers may not participate as expected. A cell displaying #N/A may also be the result of a formula rather than an error typed directly, but it is still an error value for this purpose.

  • Confirm the range includes the intended rows and does not accidentally include a heading or an unrelated result.
  • Check whether the result should ignore all errors or only #N/A; the choice determines whether AGGREGATE or IFNA is more appropriate.
  • If AGGREGATE returns #NUM!, look for a range with no usable numeric values after errors are excluded.
  • If a formula shows a separator error when pasted, check your Excel regional settings. Some installations use semicolons instead of commas between function arguments.
  • Verify that missing values are actually #N/A errors, not text labels such as NA or a formula that returns an empty string.

There is no need to replace every #N/A cell manually just to calculate a minimum. Doing so can remove useful information about why data is missing and can make later updates harder to maintain. A calculation that handles errors separately preserves the original worksheet data.

How this differs from MINIFS

MINIFS finds the minimum value that meets one or more criteria, such as the lowest sale for a particular product. It is not an error-ignoring version of MIN, and it does not replace AGGREGATE when the task is to skip #N/A values. These functions solve different problems: criteria determine which records qualify, while error handling determines which cell values a calculation can use.

If you need both a condition and error handling, plan the calculation around both requirements rather than assuming a criteria function will automatically skip errors. For example, decide which rows qualify, then ensure errors in the candidate values are handled in a controlled way. Test the result with a small range that includes a qualifying number, an excluded row, and an error so it is clear which values contribute.

Frequently Asked Questions

Does Excel MIN ignore #N/A?

No. If the referenced range contains a #N/A error, ordinary MIN returns an error. Use AGGREGATE with function number 5 and option 6 to ignore errors, or use MIN with IFNA when you want to replace only #N/A during the calculation.

What is the formula for MIN while ignoring errors?

With the numbers and possible errors in A2:A10, use =AGGREGATE(5,6,A2:A10). It returns the smallest numeric value and ignores all error values in that range.

Can I ignore only #N/A and not other errors?

Yes. In Microsoft 365, with the range in A2:A10, use =MIN(IFNA(A2:A10,"")). IFNA substitutes an empty text value for #N/A, while other error types are not deliberately replaced.

Why does AGGREGATE return #NUM!?

With the MIN calculation and error-skipping option, #NUM! commonly means there are no numeric values left to evaluate. The range may contain only errors, blanks, or text. Check the input cells or add a count check if you want a message instead.

Does AGGREGATE ignore errors other than #N/A?

Yes. Option 6 tells AGGREGATE to ignore error values generally, including errors such as #VALUE! and #REF!. Use IFNA instead if those other errors should remain visible rather than being skipped.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.