How to correct #NUM errors

The #NUM! error in Excel occurs when there’s a problem with a number in your formula—either it’s not valid, too large/small, or the math can’t be performed properly.

✅ Common Causes & Fixes for #NUM! Errors

🔹 1. Invalid Numbers in Formulas

Example:

=SQRT(-1)

You can’t take the square root of a negative number.

Fix:

• Use IF to handle invalid values:

  =IF(A1<0, "Invalid", SQRT(A1))
  

🔹 2. Numbers Too Large or Too Small

Excel can only handle numbers between approximately -1E+308 and 1E+308.

Example:

=10^1000 → #NUM!

Fix:

• Check formulas generating extremely large or small numbers.
• Use scientific notation or reduce scale where possible.

🔹 3. Iteration Formula Can’t Converge

Functions like IRR, RATE, or GOAL SEEK use iteration. If Excel can’t find a result, it gives a #NUM! error.

Example:

=IRR(A1:A10)

If the cash flows don’t allow a valid IRR to be found.

Fix:

• Provide a guess value (2nd argument) to help Excel converge:

  =IRR(A1:A10, 0.1)
  

• Make sure the data is valid (e.g., at least one negative and one positive cash flow).

🔹 4. Bad Inputs for Financial Functions

Functions like NPER, PMT, and RATE can return #NUM! if:

• Interest rate is negative or zero inappropriately.
• Payments and loan amounts don’t make sense together.

Fix:

• Double-check financial logic and parameters.

🔹 5. Non-Numeric Input Where a Number Is Expected

Sometimes a number may be stored as text, and Excel can’t use it in functions like SQRT, LOG, etc.

Fix:
Use the VALUE() function:

=SQRT(VALUE(A1))

✅ General Tip: Use IFERROR to Handle Cleanly

=IFERROR(SQRT(A1), "Check input")

🛠️ Troubleshooting Tips

• Check if the cell contains hidden characters (use TRIM, CLEAN).
• Use Evaluate Formula in the Formulas tab to step through the formula logic.