Excel

How to Fix the #NUM! Error in Excel

The #NUM! error in Excel means a formula produced an invalid number. Learn how to fix impossible inputs, overflow, and iteration issues.

June 9, 2026/5 min read

#NUM! means Excel cannot return a valid numeric result. The formula may be asking for an impossible value, producing a number too large or too small, or using a financial function that cannot converge on an answer.

Unlike #VALUE!, which is often a wrong data type, #NUM! usually means the formula received numeric inputs but the math no longer makes sense.

Check for impossible math

Some calculations are not valid with ordinary real numbers. This formula returns #NUM! because the square root of a negative number is not a valid real-number result in Excel:

=SQRT(-25)

If the negative value is expected and you only need its magnitude, use ABS:

=SQRT(ABS(A2))

Do this only when the business logic supports it. In a small business budget or expense tracker, a negative number may be a refund, credit, reversal, or data-entry error. The correct fix depends on what the number represents.

Check numbers that are too large or too small

Excel has numeric limits. If a formula produces a value outside what Excel can represent, it can return #NUM!.

This often happens when exponent formulas, growth models, or repeated multipliers get out of control:

=POWER(10,400)

If the number is unrealistic, review the input assumptions. If the calculation is valid but too large for the worksheet, scale the model down by using thousands, millions, or percentages instead of raw units.

Fix financial functions that do not converge

Functions such as IRR and RATE estimate an answer. If Excel cannot converge, it may return #NUM!.

For example:

=IRR(A2:A20)

First, confirm the cash-flow range includes at least one negative value and one positive value. Then provide a guess:

=IRR(A2:A20,0.1)

If the result still fails, inspect the cash-flow pattern. Multiple sign changes can produce multiple possible rates, and a broken source range can make the formula impossible to solve.

Check date and time calculations

Date formulas can return #NUM! when the inputs create an invalid date. For example, some date functions fail when the start date comes after the end date or when serial dates fall outside Excel's supported range.

Before changing the formula, check whether the date inputs are real dates and whether the order makes sense for the calculation.

Common causes and fixes

Cause	Example	Fix
Impossible math	Square root of a negative number	Validate the input or use ABS only when appropriate
Number overflow	Exponent or growth model too large	Scale the model or correct assumptions
Financial function cannot converge	IRR or RATE fails	Check cash flows and add a guess
Invalid date input	Date serial outside supported range	Correct the date inputs
Bad iteration settings	Circular model cannot resolve	Review iterative calculation settings and formulas

TIP

When #NUM! appears in a model, inspect the input that changed most recently. The formula may be mathematically correct and the assumption may be the part that broke.

The Griddy way

#NUM! errors are hard to repair by sight because the formula can look reasonable while one assumption makes the result impossible.

"Find the #NUM! errors in this financial model, explain which inputs make the math invalid, and suggest safe corrections"

Griddy can trace the formulas, flag impossible inputs, and show whether the fix belongs in the formula, the assumptions, or the model structure.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Check the assumptions behind invalid numbers

NUM errors are most useful to diagnose inside budgets, forecasts, and pipeline models where impossible inputs can make the math fail.

Finance

All posts