#NUM!
#NUM! Error in Excel — Causes & Fixes
Excel returns #NUM! when a formula produces a number that is too large, too small, or mathematically impossible — such as the square root of a negative number.
Why this happens
Common causes of #NUM!
Result is too large or too small
Excel supports numbers between approximately ±1×10⁻³⁰⁷ and ±9.99×10³⁰⁷. A calculation that exceeds this range returns #NUM!.
Square root or logarithm of a negative number
=SQRT(-1) and =LOG(-1) are mathematically undefined for real numbers and return #NUM!.
IRR cannot converge
IRR uses an iterative calculation. If the cash flow series has no valid rate of return (e.g., all negative values), IRR returns #NUM! after failing to converge.
LARGE or SMALL rank argument exceeds range size
=LARGE(A1:A5,10) returns #NUM! because the range only has 5 values — you cannot request the 10th largest.
How to fix it
Step-by-step fixes for #NUM!
Add an IF guard before the calculation
=IF(A1<0,"",SQRT(A1)) prevents SQRT from receiving a negative number by returning blank when A1 is negative.
Use ABS() for absolute values before SQRT
If the sign does not matter for your use case, =SQRT(ABS(A1)) avoids the error by always passing a non-negative value.
Provide an initial guess to IRR
=IRR(A1:A10,0.1) gives IRR a starting estimate of 10% to help it converge. Try values between -0.9 and 0.9.
Check LARGE/SMALL rank against COUNT
=IF(B1>COUNT(A1:A10),"",LARGE(A1:A10,B1)) prevents #NUM! when the requested rank exceeds the number of values.
Frequently asked questions
How is #NUM! different from #VALUE!?
#VALUE! means wrong data type (e.g., text where a number is expected). #NUM! means the data type is correct but the number itself is invalid or out of range for the calculation.
Why does IRR return #NUM! even when my cash flows look correct?
IRR requires at least one sign change in the cash flow series — at least one negative and one positive value. If all values are negative (all outflows), there is no rate of return and IRR returns #NUM!.
Related function references
These functions appear most often in formulas that produce #NUM!.
Other Excel errors