How to correct a #NUM! error

How to correct a #NUM! error

Excel shows this error when a formula or function contains numeric values that aren’t valid.

This often happens when you’ve entered a numeric value using a data type or a number format that’s not supported in the argument section of the formula. For example, you can’t enter a value like $1,000 in currency format, because dollar signs are used as absolute reference indicators and commas as argument separators in formulas. To avoid the #NUM! error, enter values as unformatted numbers, like 1000, instead.

Excel might also show the #NUM! error when:

  • A formula uses a function that iterates, such as IRR or RATE, and it can’t find a result.

    To fix this, change the number of times Excel iterates formulas:

    1. Click File > Options. If you are using Excel 2007, select Microsoft Office Button Office button image > Excel Options.

    2. On the Formulas tab, under Calculation options, check the Enable iterative calculation box.

      Screen shot of the Iterative Calculation settings
    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.

    4. In the Maximum Change box, type the amount of change you’ll accept between calculation results. The smaller the number, the more accurate the result and the more time Excel needs to calculate a worksheet.

  • A formula results in a number that’s too large or too small to be shown in Excel.

    To fix this, change the formula so that its result is between -1*10307 and 1*10307.

    Tip:  If error checking is turned on in Excel, you can click Button Image next to cell that shows the error. Click Show Calculation Steps if it’s available, and pick the resolution that works for your data.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.