Excel Number Error

What this does

#NUM! means "invalid numeric argument." Excel raises it when a mathematical or financial function receives a number it cannot process because the operation is undefined or impossible for that input. Key scenarios: (1) SQRT() of a negative number — square roots of negatives are imaginary, not a real number Excel can display; (2) DATEDIF() where the start date is later than the end date; (3) IRR() or XIRR() that cannot converge to a solution within its iteration limit — usually because the cash flows have no positive real solution or the optional guess is far from the answer; (4) RATE() failing to converge; (5) any log or power function receiving an out-of-domain argument (e.g. LOG(0) or LOG(-1)). Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel number error” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Scenario 1: =SQRT(B2) where B2 = -4 returns #NUM! because the square root of a negative real number is undefined. Fix: use =SQRT(ABS(B2)) if you want the magnitude, or =IFERROR(SQRT(B2), "N/A") to handle the case gracefully. Scenario 2: =IRR(C2:C10) returns #NUM! when the cash-flow sequence has no real solution — try adding a guess: =IRR(C2:C10, 0.1) where 0.1 is a 10% initial estimate. If it still fails, review whether the cash flows have at least one sign change (negative to positive or vice versa), which is required for IRR to have a mathematical solution. Address #NUM! at the data-validation layer: add input guards (IF, ABS, MIN/MAX) that prevent out-of-domain values from reaching the sensitive function. Financial functions like IRR and RATE are especially vulnerable because the quality of the input data directly determines whether a mathematical solution exists — a single zero in the wrong place can make an otherwise correct model fail. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. If you take one thing from this page on “excel number error”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

Common mistakes

• Passing a date as a raw string instead of a real date serial to DATEDIF() — if one of the dates is stored as text, Excel may compute it as a very large or very small number, pushing the result out of the valid date range.
• Using IRR() on a cash-flow series with no sign change — IRR requires at least one period of positive cash flow and one of negative (or vice versa); an all-negative or all-positive series has no real internal rate of return.
• Expecting SQRT() to handle negative inputs silently — if you need complex/imaginary results, use the IMSQRT() function from the Analysis ToolPak instead.

Frequently asked questions