#NUM!: Number Excel Cannot Calculate
#NUM! means Excel understood the formula and the number is impossible. A negative square root, a result too large to store, or IRR or RATE with no solution all return #NUM!. Text in a numeric argument is #VALUE!, and dividing by zero is #DIV/0!.
#NUM!Excel returns #NUM! when a formula asks for a number that cannot exist in its arithmetic. The function name is valid. The arguments have the wrong size, sign, or range. SQRT of a negative number returns #NUM!. LOG and LN of zero or of a negative number do the same. A negative number raised to a power that is not a whole number returns #NUM! as well, so =(-8)^(1/3) does not return -2. Excel also returns #NUM! when the result is too large or too small to store, past about 1E+308. FACT(170) still fits. FACT(171) does not, and returns #NUM!. DATE returns #NUM! for a different limit: the year argument is below 0, or it is 10000 or greater. A month of 13 does not do this. DATE rolls that month into the next year. IRR and RATE return #NUM! when they cannot find a rate. IRR needs at least one positive cash flow and one negative cash flow. A column of only positive numbers has no rate to find. DATEDIF returns #NUM! when the start date is after the end date. The function name DATEDIF is not translated. Text where a number is required is a different error. SQRT of a word returns #VALUE!, not #NUM!. A division by zero returns #DIV/0!.
Common Causes
The number is outside the function domain
SQRT requires zero or a positive number. LOG and LN require a number greater than zero. A negative input returns #NUM!.
=SQRT(-4) or =LOG(0)The result is too large to store
Excel stops near 1E+308. FACT(171) and larger cross that line and return #NUM!. FACT(170) is still inside the limit.
=FACT(171)A negative number is raised to a fractional power
Excel does not return a real root for a negative base and a non-integer power. =(-8)^(1/3) returns #NUM!. Take the root of the absolute value and put the sign back.
=SIGN(A1)*ABS(A1)^(1/3)IRR or RATE cannot find a rate
Both functions search for a rate. IRR returns #NUM! when every cash flow has the same sign, because no rate connects them. RATE returns #NUM! when the periods, payment, and present value cannot meet.
=IRR(A1:A5) when A1:A5 contains only positive numbersA date argument is out of range
DATE returns #NUM! when year is below 0 or is 10000 or greater. DATEDIF returns #NUM! when the start date is after the end date. A month outside 1 to 12 does not return #NUM!. DATE rolls it.
=DATE(10000,1,1) or =DATEDIF(B2,A2,"d") when B2 is after A2Diagnostic Steps
- 1Select the cell and read which function returns the error. Formulas > Evaluate Formula steps through each argument.
- 2For SQRT, LOG, or LN, check the sign. Negative or zero, as required by the function, is #NUM!.
- 3For a power, check a negative base with a fractional exponent, such as 1/3.
- 4For FACT or a product that grows quickly, compare the result with the limit near 1E+308. FACT(171) is already past it.
- 5For IRR, confirm the range has at least one positive value and one negative value. For RATE, check that a payment of that size can settle the present value over those periods.
- 6For DATE, read the year. Below 0, or 10000 or more, is #NUM!. For DATEDIF, put the earlier date first.
Solutions
For: Negative square root or logarithm
SQRT has no real result for a negative number. Skipping that input avoids #NUM! without hiding a different error.
=IF(A1<0,"",SQRT(A1))- →Find the input that is negative or zero.
- →Correct the source cell, or guard the call.
- →Use ABS only when the absolute value is the number you actually want.
For: Negative base and a fractional power
Excel refuses (-8)^(1/3). The absolute value has a real cube root, and SIGN restores the direction.
=SIGN(A1)*ABS(A1)^(1/3)- →Take the absolute value.
- →Raise that positive number to the power.
- →Multiply by the original sign when the root should stay negative.
For: IRR with no change of sign
IRR needs both a positive and a negative cash flow. A list of only gains, or only costs, returns #NUM!.
=IRR(A1:A8)- →Mark the initial outflow as a negative number.
- →Keep later inflows positive.
- →Recalculate. If the signs were already mixed and IRR still returns #NUM!, the series has no rate within the search.
For: DATE year out of range
DATE returns #NUM! only for a year below 0 or of 10000 or more. An out of range month rolls forward and is not #NUM!.
=DATE(A2,B2,C2)- →Read the year argument.
- →Use a year from 0 through 9999. Years 0 through 1899 are added to 1900.
- →Do not use 10000 or a negative year.
For: DATEDIF with the dates reversed
DATEDIF returns #NUM! when the start is after the end. The function name stays DATEDIF in every Excel language.
=DATEDIF(A2,B2,"m")- →Put the earlier date in the first argument.
- →Put the later date in the second.
- →Keep the unit in quotes, such as "d", "m", or "y".
Prevention Tips
- Check the sign before SQRT, LOG, or LN when the input can be negative.
- Store cash flows for IRR with the outflow as a negative value.
- Keep DATE years inside 0 to 9999. A year from 0 to 1899 means 1900 plus that year.
- Do not use #NUM! and #VALUE! as the same fix. Text is #VALUE!. An impossible number is #NUM!.
Affected Formulas
Real-world Scenarios
Square root of a variance typed with the wrong sign
A sheet takes the square root of a calculated spread. One row subtracted the numbers in the wrong order and stored a negative.
Problem: =SQRT(C2) returns #NUM! because C2 is -4.
Solution: Correct the subtraction. If the input must stay non-negative, skip the root when it is not.
Cash flows entered as all positive
An investment of 1000 is followed by three returns. The investment was typed as 1000 instead of -1000.
Problem: =IRR(A2:A5) returns #NUM! because every value is positive.
Solution: Enter the initial cost as a negative number.
A year of 10000 passed to DATE
A generated year column contains 10000 from a bad source row.
Problem: =DATE(A2,1,1) returns #NUM!. Month and day are valid. The year is not.
Solution: Correct the year. DATE accepts 0 through 9999, and adds 1900 to a year from 0 through 1899.
Free Tools to Fix Your Formulas
Use these free tools to create correct formulas and avoid errors:
Excel Formula Generator
Describe the calculation in plain English. This free Excel formula generator returns a formula you can copy into the sheet.
VLOOKUP Generator
Generate VLOOKUP formulas instantly by describing what you need in plain English
Excel Formula Explainer
Paste an Excel formula that starts with =. This free tool explains what the formula is doing, in plain language.