ElyxAI

#NUM!: Number Excel Cannot Calculate

Severity: Moderate

#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 numbers

A 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 A2

Diagnostic Steps

  1. 1Select the cell and read which function returns the error. Formulas > Evaluate Formula steps through each argument.
  2. 2For SQRT, LOG, or LN, check the sign. Negative or zero, as required by the function, is #NUM!.
  3. 3For a power, check a negative base with a fractional exponent, such as 1/3.
  4. 4For FACT or a product that grows quickly, compare the result with the limit near 1E+308. FACT(171) is already past it.
  5. 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.
  6. 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:

Frequently Asked Questions

Don’t want to do it by hand? Ask Elyx

Example request

“Fix the #NUM! errors in column D and tell me what caused them”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related Errors