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.

 

70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf from our Excel training page
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf from our Excel training page
Learn how to fix these errors: #DIV/0!, #N/A!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!

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

 

70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf from our Excel training page
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf from our Excel training page
Learn how to fix these errors: #DIV/0!, #N/A!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!

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.

 

 

Posted by Chester Tugwell