In Excel: a formula receives a numeric argument that is invalid — for example, a negative number under SQRT(), an impossible date in DATEDIF(), or an IRR/XIRR/RATE calculation that cannot converge; fix by correcting the input value or providing a starting guess.
On this page8
Fix: #NUM!
What causes #NUM!
#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 — for example because the cash flows have no valid 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)).
How to fix #NUM!
- Identify which argument is invalid: for SQRT, check that the input is non-negative; for
DATEDIF, check that start ≤ end; forIRR/XIRR/RATE, inspect the cash flows. - Validate the domain first: SQRT needs
B2>= 0; LOG needsB2> 0. Use=IF(B2<0, "Invalid", SQRT(B2))or=IF(B2<=0, "Invalid", LOG(B2)).ABS()is appropriate only when the model intentionally uses magnitude; it does not validate the original input. - For
DATEDIF: swap the date arguments if they are in the wrong order —=DATEDIF(MIN(A1,B1), MAX(A1,B1), "d"). - For
IRR()not converging: provide an explicit guess (second argument, e.g. 0.1 for 10%):=IRR(C2:C10, 0.1). If it still fails, verify at least one sign change in the cash-flow series. - For
XIRR(): check that the dates are sorted ascending and the cash-flow series has at least one positive and one negative value. - Wrap the formula in
IFERRORas a last resort:=IFERROR(IRR(C2:C10, 0.1), "No solution")— but investigate the root cause rather than hiding it permanently.
Identify which argument is invalid: for SQRT, check that the input is non-negative; for DATEDIF, check that start ≤ end; for IRR/XIRR/RATE, inspect the cash flows.
Validate the domain first: SQRT needs B2 >= 0; LOG needs B2 > 0. Use =IF(B2<0, "Invalid", SQRT(B2)) or =IF(B2<=0, "Invalid", LOG(B2)). ABS() is appropriate only when the model intentionally uses magnitude; it does not validate the original input.
For DATEDIF: swap the date arguments if they are in the wrong order — =DATEDIF(MIN(A1,B1), MAX(A1,B1), "d").
For IRR() not converging: provide an explicit guess (second argument, e.g. 0.1 for 10%): =IRR(C2:C10, 0.1). If it still fails, verify at least one sign change in the cash-flow series.
For XIRR(): check that the dates are sorted ascending and the cash-flow series has at least one positive and one negative value.
Wrap the formula in IFERROR as a last resort: =IFERROR(IRR(C2:C10, 0.1), "No solution") — but investigate the root cause rather than hiding it permanently.
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 — for example because the cash flows have no valid 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
With B2 = -4, =IF(B2<0, "Invalid", SQRT(B2)) returns "Invalid" and preserves the domain check. Only if the intended quantity is the magnitude does =SQRT(ABS(B2)) return the intended 2: that changes the input from -4 to 4. For logarithms, zero is invalid too: =IF(B2<=0, "Invalid", LOG(B2)). IRR is separate: =IRR(C2:C10, 0.1) supplies a 10% starting guess, but a different guess cannot create a solution for cash flows with no sign change. NPV directly discounts cash flows at the supplied rate; it does not search for a rate or use a starting guess. Validate inputs against the actual domain rather than changing their meaning. ABS and MIN/MAX transform values; they are not generic validation. Inspect invalid inputs before masking an error with IFERROR. NPV uses a supplied discount rate directly, while IRR, XIRR and RATE search iteratively for a solution. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “excel number error”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
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 —IRRrequires 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
Why does SQRT() return #NUM! for a negative number?
The square root of a negative real number is not a real number — it is an imaginary number. Standard Excel only works with real numbers, so SQRT(-4) is undefined and returns #NUM!. Use =SQRT(ABS(B2)) to get the magnitude, or use =IMSQRT(-4) (requires the Analysis ToolPak) to get the complex result "2i".
Why does IRR() return #NUM! even though my cash flows seem correct?
IRR() iterates toward a solution. If the iteration does not converge within 20 tries (or if the cash flows have no real solution), it returns #NUM!. Try: (1) adding an explicit guess as the second argument (=IRR(range, 0.1)); (2) verifying at least one sign change in the cash flows; (3) checking that no individual value is so extreme it throws off the iteration.
Can I use IFERROR to handle #NUM!?
Yes — =IFERROR(SQRT(B2), 0) returns 0 instead of #NUM! when B2 is negative. But IFERROR hides the root cause. For iterative financial functions (IRR, XIRR, RATE), a "no solution" result may indicate incorrect assumptions in the model itself — investigate before suppressing.