Home » Excel’s #NUM! Error – What Does it Mean?

Excel’s #NUM! Error – What Does it Mean?

Have you ever wondered what Excel’s #NUM! error actually means? The #NUM! error can appear for several reasons:  here are three of the most likely.

Reason 1: You are Performing an Impossible Calculation in Excel

If you perform an impossible calculation in Excel you will get the #NUM! error. An example of an impossible calculation is calculating the square root of a negative number using the SQRT function.

The #NUM! error will also appear if you try to find the logarithm of a negative number.

Reason 2: A Number is Too Big or Too Small

The smallest number you can use in Excel is -1*10^308. That’s a -1 with 308 zeros after it! The largest number is 1*10^308.  That’s a 1 with 308 zeros after it!  If you enter a number too small or too large you will get the #NUM! error.

For example, the formula =1000^500 (1000 multiplied by itself 500 times) would calculate a number too large for Excel, resulting in the #NUM! error.

Reason 3: You Are Using an Iterative Formula That Fails to Find a Result

An iterative formula is one that performs the same calculation again and again.  An example of this is the IRR function which calculates the internal rate of return for a series of cash flows. IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.