formulastreak

#NUM! error: what it means and how to fix it

#NUM!

A number in the formula (or its result) isn't valid for that calculation.

#NUM! means a calculation got a number it can't use, couldn't find an answer, or produced a result too large or too small to show.

Common causes, ranked

  1. 1

    A number the function can't accept

    For example the square root of a negative number. Microsoft also lists numbers typed with a format the argument doesn't accept, such as $1,000 instead of 1000.

    Fix: Check the inputs make sense for the function.

    Shows the error

    =SQRT(-16)

    Fixed

    =SQRT(ABS(-16))
  2. 2

    IRR or RATE can't find a result

    These functions search for an answer by trying values, and give up after a number of tries.

    Fix: Give IRR a different guess as its last argument. In Excel you can also let it try more times: File › Options › Formulas › Enable iterative calculation, then raise Maximum Iterations.

    Shows the error

    =IRR(B2:B6)

    Fixed

    =IRR(B2:B6,0.1)
  3. 3

    A result too big or too small

    Excel has a limit on how large or small a number it can show.

    Fix: Change the formula so the result stays in range.

    Shows the error

    =10^400

Should you hide it?

Fix the inputs rather than hiding #NUM!; it's telling you the calculation has no valid answer.

Careful: If you must hide it, use a visible message, not 0: a zero rate of return is a real-looking (and wrong) answer.

Excel vs Google Sheets

#NUM! is an error value in both Excel and Google Sheets.

Sources

Practise this in 5 minutes

Fixing #NUM! is taught in Lesson 48: Savings, NPV and IRR (Unit 14, Money and finance), on a live sheet with instant feedback. Pro

Open “Savings, NPV and IRR”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.