Excel #NUM! Error: Causes, Fixes, and Examples

Excel’s #NUM! error means a formula cannot produce a valid numeric result, often because an input or function argument is outside an allowed range. Check the formula’s numbers and limits first; then correct the input or adjust the calculation method rather than hiding the error.

What the #NUM! error means

#NUM! is one of Excel’s built-in error values. It signals a numerical problem: Excel has evaluated a formula but cannot return a usable number under the conditions it was given. The error may come from a direct calculation, such as trying to take the square root of a negative number, or from a function that cannot find a result that meets its rules.

The error usually appears in the cell containing the formula. It can then flow into other formulas that depend on that cell, so a single invalid input may produce a trail of errors in a report. The first #NUM! in the chain is often the most useful one to investigate. Select it and inspect the formula bar to see the expression Excel is evaluating.

There is no one universal repair for this error. The right fix depends on whether the formula is mathematically invalid, its arguments are out of range, a numerical search has failed, or a calculation exceeds Excel’s numeric limits.

Common causes of #NUM!

Look for an invalid mathematical operation, an argument that exceeds a function’s permitted range, or a numerical method that fails to find a result. Common examples include:

An operation with no real-number result. A square root of a negative number, or raising a negative number to a fractional power, returns #NUM! in ordinary real-number calculations.
A function argument outside its valid range. For example, LARGE returns an error when you ask for the sixth-largest value in a list containing only five numbers.
A function that cannot converge. Some functions, including IRR and RATE, use numerical iteration to look for a result. They can return #NUM! when they cannot find one under the supplied inputs and calculation process.
A result that is too large for Excel to represent. Extremely large calculations can exceed Excel’s numeric range.
An input that is technically numeric but unsuitable for the calculation. A negative value, a zero where a positive value is required, or an unrealistic estimate can make a formula invalid even when every cell contains a number.

These cases are different from a number stored as text. Text in a calculation more commonly causes #VALUE!, although a function’s specific rules matter. Check the actual error displayed rather than assuming every failed number calculation has the same cause.

How to find the formula causing it

Start with the cell that displays #NUM!. Select it and read the formula bar. If the formula refers to several cells, inspect those inputs and any formulas they contain. A result may be invalid because of one unexpected value several steps upstream.

Check the inputs. Confirm that values are within the expected range and have the intended sign and units. A percentage entered as 20 instead of 20% can radically change a calculation.
Review function arguments. Check whether an argument is a count, rank, rate, date component, or other value with a specific permitted range. For LARGE, for instance, the requested rank cannot exceed the number of numeric values in the array.
Break a long formula into parts. Copy a section of the expression into a temporary cell or use Excel’s formula evaluation tools to see which operation first produces the error.
Follow references upstream. Inspect cells feeding the formula, especially formulas returning an error or values created by imports, lookups, or user input.
Test a known-valid case. If the formula uses an iterative function, try a plausible starting estimate or a small, verified input set. This helps distinguish a bad model from a bad entry.

In Excel for Microsoft 365 on Windows, the Formulas tab includes formula auditing tools such as Error Checking and Evaluate Formula. Evaluate Formula steps through parts of a formula, which can reveal the point where an intermediate calculation becomes invalid. The available labels or ribbon layout can vary somewhat in other Excel releases.

Fixing invalid mathematical operations

For a direct arithmetic error, decide what the formula is supposed to mean before changing it. For example, suppose A2 contains -9 and the formula is intended to calculate a real square root. With that cell layout, =SQRT(A2) returns #NUM!, because a negative number has no real square root.

If the intended input should always be nonnegative, correct the source value or add a check that makes the assumption explicit. If the goal is instead to take the square root of the magnitude, then with A2 still containing -9, =SQRT(ABS(A2)) returns 3. That is only appropriate when the magnitude is what the calculation calls for; using ABS simply to remove an error can conceal a meaningful sign problem.

Another example is a fractional power of a negative number. With a negative value in A2, =POWER(A2,0.5) returns #NUM! in Excel’s real-number arithmetic. If the calculation is meant to use complex numbers, Excel has separate complex-number functions, but changing to them requires a formula designed for complex values. It is not a general repair for an ordinary worksheet formula.

Fixing function arguments and limits

Read the function’s argument requirements and compare them with the actual data. If B2:B6 contains five numeric values, then =LARGE(B2:B6,6) returns #NUM!: there is no sixth-largest value in that range. Changing the second argument to a rank from 1 through 5 returns a value, assuming the range contains the needed numeric entries.

Check whether blank cells, text, or filtered-out records affect the count you have in mind. A range may look like it contains enough entries while some cells are text rather than numbers. Also confirm that a rank or count is a valid positive number. A formula that references a changing rank cell may work for one row and fail when that rank becomes too large.

Do not assume Excel interprets every unusual date or number as an error. For example, Excel’s DATE function can normalize an out-of-range month by carrying it into another year. That behavior differs from functions with strict argument limits. Consult the function’s rules instead of treating all unusual inputs alike.

When iterative functions return #NUM!

Functions such as IRR and RATE may search repeatedly for a numerical answer. If the function cannot find a result that satisfies its calculation criteria, it can return #NUM!. In that case, verify the model before changing any settings or adding error handling.

Check the signs and order of cash flows. A return calculation generally needs an initial investment and later cash flows represented consistently with the function’s definition.
Check that the cash-flow pattern can plausibly support the requested result. Some patterns can have no solution or more than one possible solution.
For functions with an estimate argument, try a reasonable estimate based on the problem. An estimate can help the numerical search find a solution, but it cannot make an impossible model valid.
Confirm that periods, rates, and payment amounts use consistent units. Mixing monthly periods with an annual rate can make a result misleading even if the function returns a number.

For IRR, the estimate is optional and Excel uses a default if you leave it out. If changing an estimate makes the function return a result, check that result against the cash flows and the assumptions; a number appearing in the cell does not prove the model is correct. For financial decisions, verify the actual terms with the lender or a qualified professional.

Large numbers, precision, and display issues

Excel stores numeric values with finite precision and has limits on the size of values it can calculate. An operation that produces an excessively large number can return #NUM!. With no cell references required, =10^309 is an example of an expression that exceeds the range Excel can represent and returns #NUM!.

Very long identifiers, account numbers, and product codes should generally be stored as text, not treated as quantities. Excel preserves only about 15 significant digits of numeric precision, so entering a longer identifier as a number can change its trailing digits. Formatting a cell to display more digits cannot restore precision that was lost when the value was stored.

Do not confuse #NUM! with #####. A row of number signs often means a column is too narrow to display the formatted value, or that a date/time result is negative under the current date system. Widening the column or changing the format may resolve a display issue; it does not repair a calculation error.

Why hiding the error is not always a fix

IFERROR can replace an error with a chosen value, but it does not correct the calculation that caused the error. With A2 containing -9, =IFERROR(SQRT(A2),0) displays 0 instead of #NUM!. That may be suitable when zero is a clearly defined fallback, but it could also make an invalid input look like a valid zero result.

If you use error handling, choose a replacement that communicates what happened. A blank, a label, or a separate validation message may be safer than zero in a calculation where zero has real meaning. Keep the underlying input checks visible, and avoid wrapping an entire complex formula in error handling until you know which errors it might conceal.

Frequently Asked Questions

What does #NUM! mean in Excel?
It means Excel cannot return a valid numeric result for the formula as written. Common reasons include an invalid mathematical operation, an argument outside a function’s allowed range, or a numerical search that cannot find a solution.

How do I fix #NUM! in Excel?
Inspect the formula and its referenced cells, then check signs, units, ranges, and function arguments. Correct the input or calculation method that is invalid. The right change depends on the formula; simply replacing the error may hide a problem.

Why does SQRT return #NUM!?
SQRT returns #NUM! when its input is negative. Confirm that the value should be negative; if the intended operation is the square root of its magnitude, use an absolute value only when that matches the calculation’s meaning.

Why does IRR return #NUM!?
IRR may return #NUM! if it cannot find a result that meets its numerical search criteria. Check the cash-flow signs, ordering, units, and whether the cash flows support a solution. A reasonable estimate can help the search but cannot fix invalid cash flows.

Is #NUM! the same as #VALUE!?
No. #NUM! indicates a numerical issue, while #VALUE! commonly indicates an inappropriate value type, such as text used where a number is expected. The exact cause still depends on the formula and its inputs.

Can I remove #NUM! with IFERROR?
You can replace it with a fallback value, but IFERROR does not make the calculation valid. Use it only when the fallback is meaningful and readers will not mistake it for a genuine calculated result.

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.