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.