Excel #NUM! Error: Impossible Number Fixes
By Szabó Gergő · Updated
#NUM! means the calculation produced a number Excel cannot use, or an iterative function never settled on a result.
Syntax and arguments
=IF(OR(year<1900,year>9999),"",DATE(year,month,day))- year
- A calendar year Excel can store, typically 1900 to 9999 on Windows.
- month
- Month number. DATE can overflow months; invalid serials still fail elsewhere.
#NUM! examples
Year in A2 from a messy import.
=IF(OR(A2<1900,A2>9999),"",DATE(A2,B2,C2))DATE returns #NUM! when the year is outside Excel’s range.
Cash flows in B2:B8 that fail IRR.
=IRR(B2:B8,-0.1)A guess near the expected rate helps IRR converge and avoid #NUM!.
A2 is 10, B2 is 400.
=IF(A2^B2>1E100,"Too large",A2^B2)Excel cannot store every power. Test the magnitude before you rely on the cell.
Common mistakes
Two-digit years that become 1900s
Use four-digit years. DATE(26,1,1) is not 2026 in every setting.
IRR without a guess on unusual cash flows
Pass a guess, or switch to XIRR with real dates.
Hiding #NUM! on a date column
Fix the year. A blank fallback is fine only after you know the input is junk.
#NUM! FAQ
Is #NUM! the same as #VALUE!?
No. #VALUE! is a type problem. #NUM! is a number Excel cannot represent or an iteration that failed.
Why does SQRT return #NUM!?
The argument is negative. Use ABS if you meant magnitude, or reject the row.
Does NETWORKDAYS return #NUM!?
Rarely. Invalid date serials more often show #VALUE! or a wrong year that later becomes #NUM! in DATE.