[Excel] Error: #NUM! — How to Fix It

Summary

The #NUM! error appears when a formula produces an invalid numeric result or uses impossible mathematical input. It occurs with functions like SQRT(), LOG(), or financial calculations (IRR, RATE) that can’t converge. The fix involves validating inputs, keeping numbers within Excel’s limits, and handling invalid results using IF() or IFERROR(). This applies to Excel 2010+, 2016, and Microsoft 365.

Context

Excel’s numeric engine has strict mathematical domains and precision limits. When calculations produce imaginary numbers, infinities, or values beyond numeric boundaries, Excel returns #NUM!. Examples include =SQRT(-1) (invalid real root), =LOG(0) (undefined), or large exponentials exceeding 1E+308. In financial functions like IRR() and RATE(), the error signals non-convergence — Excel’s iteration process failed to find a result within default 20 iterations. Adjusting assumptions, guesses, or iteration limits often resolves it.

Probable Cause

  • Invalid mathematical operation. Negative inputs for SQRT(), LOG(), or POWER() cause domain errors.
  • Arguments outside valid ranges. Some functions reject zero or negative numbers (e.g., LOG(0)).
  • Overflow/underflow. Extremely large or small numbers exceed Excel’s numeric range (~1E-308 to 1E+308).
  • Non-convergent financial formulas. Iterative functions like IRR or RATE cannot find a result within the iteration limit.
  • Improper iterative/circular settings. Disabled iteration prevents certain dependent formulas from resolving.

Quick Fix

  1. Pinpoint the source of the error. Use Formulas → Evaluate Formula to find which operation triggers #NUM!.
Formulas → Evaluate Formula → Step In → Identify invalid numeric step
  1. Verify input ranges. Ensure values are within valid mathematical domains.
=IF(A1>0, SQRT(A1), NA())
  1. Control overflow/underflow. Scale numbers to practical limits or use logarithmic transformations.
=LN(A1)  # instead of very large exponential operations
  1. For financial functions (IRR, RATE): provide an initial guess and adjust iteration options if needed.
=IRR(A1:A10, 0.1)
File → Options → Formulas → Enable iterative calculation
  1. Guard against invalid input with IF() or IFERROR().
=IFERROR(LOG(A1), "Invalid input")

Full Example

Scenario 1: A user tries to compute a square root of a negative number.

=SQRT(-9)
→ #NUM!

Fix: Prevent invalid input by checking the sign.

=IF(A1>=0, SQRT(A1), "Invalid value")

Scenario 2: IRR fails due to inconsistent cash flows.

=IRR(A1:A10)
→ #NUM!

Fix: Supply a better guess or adjust inputs.

=IRR(A1:A10, 0.1)

Scenario 3: Overflow caused by exponential growth.

=EXP(1000)
→ #NUM!

Fix: Rescale the input or use logarithmic transformation.

=EXP(A1/100)  # reduce growth factor

Pitfalls & Debug

  • Symptom → SQRT() or LOG() throws #NUM!. Fix → Validate that arguments are positive and nonzero.
  • Symptom → IRR() or RATE() won’t converge. Fix → Change the guess parameter or limit range; ensure sign change exists in cash flow.
  • Symptom → Overflow on exponentials. Fix → Reduce magnitude; Excel’s limit is approximately 1E+308.
  • Symptom → Circular reference preventing solution. Fix → Enable iterative calculation or break the dependency chain.
  • Symptom → Random numeric spikes in models. Fix → Add input validation and fallback defaults via IFERROR().

Validation & Next Steps

Test numeric inputs for validity and domain compliance:

=ISNUMBER(A1)
=IF(A1>0,LOG(A1),"Invalid")

Use reasonable bounds for calculations and adjust model assumptions. For financial models, validate that at least one cash flow is negative and one is positive to ensure IRR() convergence. Save before enabling iterative calculations to avoid infinite loops.

Sources

https://support.microsoft.com/en-us/office/how-to-correct-a-num-error

https://support.microsoft.com/en-us/office/irr-function

https://support.microsoft.com/en-us/office/log-function

Labels

Tool/Excel, OS/Cross-platform, Topic/Numeric & Financial Calculations