#NUM! Error in Excel and Google Sheets: Causes and Fixes

Q: Why is DATEDIF showing #NUM! for two real dates?

The start date is after the end date. DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D") was #NUM!. Put the earlier date first, or use DAYS, which in the cheat-sheet test returned 11 in the forward direction.

Q: Why did SQRT(-1) not return a complex number?

This spreadsheet’s SQRT returned #NUM! for -1. It is working with real numbers. SQRT(9) was 3.

Q: How large a POWER result can Sheets store?

POWER(10,100) returned 1E+100. POWER(10,400) returned #NUM!. I did not hunt for the exact cutoff between those two exponents.

Q: Does FACT always error on a decimal?

Not in this Google Sheets file. FACT(1.5) returned 1. FACT(-1) was #NUM!. I did not re-test FACT(1.5) in Excel today.

Q: Is a backwards RANDBETWEEN a #VALUE! typo?

No. RANDBETWEEN(10,1) was #NUM!. The function name was recognized; the bounds were not usable in that order.

Which versions this applies to

Every result below comes from Google Sheets on November 12, 2026. Microsoft documents #NUM! in Excel when a formula produces a number that is too large or is otherwise invalid for the function. I did not re-test this set in desktop Excel today.

Cause 1: A date or year-fraction argument that is not legal

=DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D")                   → #NUM!
=YEARFRAC(DATE(2026,11,1),DATE(2026,11,12),9)                     → #NUM!

DATEDIF wants the start date first. Swapping 12 November and 1 November did not return a negative 11; it returned #NUM!. The forward direction, in the date and time cheat sheet, returned 11. If you hide this with IFERROR, you will also hide a reversed range.

YEARFRAC accepted the same two dates with the default basis and with basis 1. Basis 9 is not a legal basis value, and that call returned #NUM!.

Cause 2: A math function on a number outside its domain

=SQRT(-1)     → #NUM!
=LN(-1)       → #NUM!
=LOG(0)       → #NUM!
=LOG10(0)     → #NUM!
=SQRT(9)      → 3

Square root and the logarithms are not defined for those inputs on the real line this spreadsheet uses. SQRT(9) returned 3, so the functions themselves were working. Zero in LOG or LOG10 was #NUM!, not #DIV/0!. For a genuine divide-by-zero, see #DIV/0! Error.

Cause 3: A result that is too large, or a range that is backwards

=POWER(10,100)     → 1E+100
=POWER(10,400)     → #NUM!
=RANDBETWEEN(10,1) → #NUM!

10^100 was fine. 10^400 was not; the result is outside the number range the sheet will store, and the error is #NUM!. RANDBETWEEN wants the lower bound first. RANDBETWEEN(10,1) returned #NUM!. Swap the arguments, or use RANDBETWEEN(MIN(a,b),MAX(a,b)) if the two inputs might arrive in either order. I did not test that wrapper; I tested the bare backwards call.

Cause 4: FACT of a negative number, and what 1.5 did

=FACT(-1)    → #NUM!
=FACT(3)     → 6
=FACT(1.5)   → 1

A negative factorial is #NUM!, and FACT(3) is 6. FACT(1.5) did not error in this Google Sheets file; it returned 1, the same value as FACT(1). I would not treat that as the mathematical value of 1.5!. I did not re-run FACT(1.5) in desktop Excel today.

#NUM! is not #REF! or #VALUE!

INDEX(A2:A4,20) in the same file returned #NUM!, while INDEX({10,20,30},20) returned #REF!. That pair is unpacked in #REF! Error in Excel and Google Sheets. A wrong type of argument, such as adding text that will not coerce, is #VALUE!.

Excel and Google Sheets differences

Microsoft describes #NUM! for invalid numeric arguments and for numbers outside Excel’s range. The DATEDIF start-after-end case is also documented as #NUM! for Excel. I did not re-run this set, including FACT(1.5), in desktop Excel today.

Common mistakes

Wrapping a reversed DATEDIF in IFERROR and reading the blank as “zero days.” The error was #NUM!, not 0.
Expecting LOG(0) to be #DIV/0!. It was #NUM!.
Calling RANDBETWEEN with the larger number first. That was #NUM!.
Assuming FACT(1.5) is an error, or that it equals 1.5 factorial. In this file it returned 1, with no error.

FAQ

Why is DATEDIF showing #NUM! for two real dates?

The start date is after the end date. DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D") was #NUM!. Put the earlier date first, or use DAYS, which in the cheat-sheet test returned 11 in the forward direction.

Why did SQRT(-1) not return a complex number?

This spreadsheet’s SQRT returned #NUM! for -1. It is working with real numbers. SQRT(9) was 3.

How large a POWER result can Sheets store?

POWER(10,100) returned 1E+100. POWER(10,400) returned #NUM!. I did not hunt for the exact cutoff between those two exponents.

Does FACT always error on a decimal?

Not in this Google Sheets file. FACT(1.5) returned 1. FACT(-1) was #NUM!. I did not re-test FACT(1.5) in Excel today.

Is a backwards RANDBETWEEN a #VALUE! typo?

No. RANDBETWEEN(10,1) was #NUM!. The function name was recognized; the bounds were not usable in that order.

Live-test findings

Google Sheets, November 12, 2026. #NUM! from DATEDIF with 12 Nov then 1 Nov; SQRT(-1); LN(-1); LOG(0); LOG10(0); POWER(10,400); RANDBETWEEN(10,1); YEARFRAC with basis 9; and FACT(-1). POWER(10,100) returned 1E+100. FACT(3) returned 6. FACT(1.5) returned 1. SQRT(9) returned 3.

Sources

Microsoft Support: How to correct a #NUM! error: https://support.microsoft.com/en-us/office/how-to-correct-a-num-error-f5193bfc-4400-43f4-88c4-8e1dcca0428b
Microsoft Support: DATEDIF function: https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
Microsoft Support: FACT function: https://support.microsoft.com/en-us/office/fact-function-ca8588c2-15f2-41c0-8e8c-c11bd471a4f3
Microsoft Support: RANDBETWEEN function: https://support.microsoft.com/en-us/office/randbetween-function-4cc7f0d1-87dc-4eb7-987f-a469ab381685
Google Docs Editors Help: SQRT: https://support.google.com/docs/answer/3093577