Pinterest Pixel

How to fix the #NUM error in Excel?

#NUM error in Excel occurs when values in formulas are invalid. It is mostly shown in cases... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to fix the #NUM error in Excel?

#NUM error in Excel occurs when values in formulas are invalid.

It is mostly shown in cases where there is a problem with numbers i.e. the data type entered is not supported in the argument of the formula.

Once you adjust the number input, the calculation can be performed and the error disappears!

In the article, you will be shown the different reasons for #NUM error in Excel and how to fix it:

 

What does #NUM mean in Excel?

It basically means that the calculation cannot be performed due to limitations or errors.

This can be due to the fact that the number is too big or small. It also includes impossible calculations like calculating the square root of a negative number, or if the input for a function is invalid, or if an iterating formula like IRR cannot find the result.

Follow this detailed tutorial on #NUM Excel and download this Excel workbook to practice along and understand better:

download excel workbook Num-error.xlsx

 

How to fix #NUM error in Excel?

Let’s look into more detail and understand the causes and solutions to the #NUM! Excel.

See also  In-Cell Bar Charts with the REPT Function

Example 1 – Number size

Excel has a limitation to the size of the number that it can display. If you enter a number is too large or too small,you will get Excel #NUM Error.

As you can see, in cell C7 we are trying to multiply 5 by itself 500 times. The result will be a really large number that Excel is unable to display. Hence. it returns NUM Excel.

How to fix the #NUM error in Excel?

In cell C10, we are trying to divide 1 by 10^750. Since this will be a very small number Excel cannot perform calculations and hence returns #NUM error.

How to fix the #NUM error in Excel?

Excel is not able to show these values because of its inherent limitations. As shown, other smaller values are calculated and shown in C8 and C9.

How to fix the #NUM error in Excel?

 

Example 2 – Impossible calculations

Excel returns the #NUM! Error when it is faced with a calculation that is impossible to do.

As you can see, the square root or log of the positive numbers has results but the square root or log of the negative numbers is impossible to calculate.

In this example, we are trying to find the square root of -3.

See also  Addition Formula in Excel

How to fix the #NUM error in Excel?

Since we are trying to calculate the square root of a negative number, we will get #NUM error in Excel. In this particular case, you can rectify this issue by taking the absolute value of the number.

ABS function returns the absolute value of a number. It converts negative numbers to positive numbers and keeps the positive numbers unaffected.

How to fix the #NUM error in Excel?

Including the ABS function into the formula uses the absolute value of the number in column A, so now Excel can calculate the number and return a valid answer.

How to fix the #NUM error in Excel?

 

Example 3 – IRR function not working

Excel might show the #NUM! error when using IRR function because of either one of the two reasons:

  • Cash flow does not contain at least one negative and one positive value.
  • Formula can’t find a result.

 

In this example, Excel results a #NUM error instead of proving a value.

How to fix the #NUM error in Excel?

This is because the cash flows provided in the range (B7:B15) are positive and IRR requires at least one negative value as the initial cost of business.

Initial payments are provided as negative value indicating outflow of cash and income payments are represented by positive values. Once you have incorporated this, the function will provide the correct result!

See also  COUNTIF Greater Than 0 in Excel - A Detailed Guide

How to fix the #NUM error in Excel?

 

IRR function can also display #NUM! error when the function iterates and can’t find a result after several tries.

To overcome this, you can increase the maximum number of iterations that Excel performs to get a result.

To increase the number of iterations:

STEP 1: Go to File > Options > Formulas to arrive at the window below

How to fix the #NUM error in Excel?

STEP 2: Under the Calculation options group, check the Enable iterative calculation box.

How to fix the #NUM error in Excel?

STEP 3: In the Maximum Iterations box, type the number of times you want Excel to recalculate.

The higher the number of iterations, the more time Excel needs to calculate a worksheet. Increasing this can help give a result to your calculation.

How to fix the #NUM error in Excel?

STEP 4: In the Maximum Change box, enter the amount of deviation that you are willing to accept between the calculation results.

The smaller the number, the more accurate the result. Increasing this can help give an approximate answer to your calculations.

How to fix the #NUM error in Excel?

Conclusion

After this exercise, we hope that you now understand why you were experiencing the #NUM error in Excel and that you can now easily rectify and overcome this error and continue with smooth functioning in Excel!

See also  Calculate the Compound Interest with Excel's FV Formula

 

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!