Quick Answer & Formula
Excel
Sheets
Intermediate
=SQRT(ABS(A2)) | DATEDIF(Start, End, "Y") -- ensure Start <= End The #NUM! error indicates a numeric calculation problem: performing math that results in an impossible number (e.g., square root of a negative number), numbers exceeding Excel's limits (10^308), or DATEDIF where Start Date > End Date.
How to Fix the #NUM! Error in Excel & Google Sheets
The #NUM! error occurs when a formula contains valid function names and syntax, but the numeric values supplied are mathematically impossible to compute.
🔍 The 3 Common #NUM! Scenarios and Solutions
1. DATEDIF: Start Date is Later Than End Date
DATEDIF strictly requires start_date <= end_date.
❌ =DATEDIF(TODAY(), DATE(2020,1,1), "Y")$\rightarrow$ Throws#NUM!✅ =DATEDIF(DATE(2020,1,1), TODAY(), "Y")$\rightarrow$ Returns6
2. Square Roots of Negative Numbers
❌ =SQRT(-64)$\rightarrow$ Throws#NUM!✅ =SQRT(ABS(-64))$\rightarrow$ Returns8
3. Financial Iteration Failure in IRR / RATE
If cash flows are unusual, supply an initial [guess] argument (e.g. 0.1 for 10%):
=IRR(Cash_Flows, 0.1)
?
Frequently Asked Questions
Why does IRR return #NUM!?
IRR requires at least one positive cash flow and one negative cash flow to calculate. If all values are positive or all are negative, IRR cannot solve and returns #NUM!.