How to fix a #NUM! error?

addition black and white black and white chalk

  • Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel for iPad, Excel Web App, Excel for iPhone, Excel for Android tablets, Excel for Android phones, Excel for Windows Phone 10, Excel Mobile Excel Starter 2010.

This post will examine when a #NUM! error typically occurs and how to rectify these. Excel displays this error any time a formula or function includes invalid numeric data. This commonly arises once you’ve typed a numeric value applying a data type or an incompatible number format within the argument element within the formula. For instance, you are unable to input a value such as $2,000 in a currency format. This is because dollar signs are employed as absolute reference indicators and commas represent argument dividers in formulas. To bypass the #NUM! error, type values in an unformatted manner, like 2,000, as standard practice.

#NUM! error appearing in formulas using an iterative function

Another time Excel could present this error is when:

  • A formula makes use of an iterative function, like IRR or RATE, and consequently, is unable to capture a result.

Solution to a #NUM! error in iterative functions

To correct this, edit the frequency in which Excel routinely iterates formulas:

a. Select File > Options. For any users of Excel 2007, press the Microsoft Office Button Office button image > Excel Options.

b. Check the Enable iterative calculation box. Do this within the Formulas tab, below Calculation options.

Screen shot of the Iterative Calculation settings to fix a #NUM! error.

c. In the Maximum Iterations box, specify the total amount of times you prefer Excel to recalculate. With a higher value of iterations, Excel will require further time to calculate a worksheet.

d. In the Maximum Change box, enter the proportion of change you’ll allow among calculation results. A smaller value gives an indication of a higher degree of accuracy for the result. This is alongside the total time Excel demands to calculate a worksheet.

  • A formula creates an extremely long or short number that is unable to be presented in Excel.

To resolve this, edit the formula to ensure that its result lies within the range of -1*10307 and 1*10307.

Tip: If error checking is already enabled in Excel, you can select Button Image beside to the cell that indicates the error. Press Show Calculation Steps if it’s available. Then, choose the resolution that is compatible with your data.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have ideas about how we can improve the upcoming version of Excel? If so, please have a gander at the topics at Excel User Voice.

See Also

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: