Fixes / Excel · Checked

Excel #NUM! error: what it means and how to fix it

Short answer

#NUM! means Excel cannot calculate a valid number from your formula. Usually a function got an impossible input, such as a negative number inside SQRT or a start date later than the end date in DATEDIF. Another common cause is IRR, XIRR or RATE failing to find an answer. Check the inputs first: fix negative values, swap reversed dates, or add a guess value to IRR.

What this message means

#NUM! is Excel's error for a formula that contains or produces a number it cannot work with. The formula itself is written correctly (otherwise you would see #NAME? or a syntax warning), but the math cannot be done with the values it received. Typical examples are the square root of a negative number, a financial function like IRR that gives up after 20 attempts, a result larger than about 1E+308, or an argument outside the range a function accepts, such as a year above 9999 in DATE. Any formula that refers to a #NUM! cell also shows #NUM!, so the error can spread across a sheet from one source cell.

Common causes

CauseHow to tell
Mathematically impossible operation, such as the square root of a negative number or a negative number raised to a fractional powerThe formula uses SQRT, ^ or POWER, and the referenced cell holds a negative value. For example, =SQRT(A1) shows #NUM! when A1 is -25.
Invalid function argument, such as a start date later than the end date in DATEDIF, a year outside 1 to 9999 in DATE, or a k value larger than the list size in SMALL or LARGEThe formula uses DATEDIF, DATE, SMALL, LARGE or a similar function, and swapping or checking one argument shows it is out of order or out of range.
IRR, XIRR or RATE cannot find a resultThe cell uses IRR, XIRR or RATE. Microsoft states IRR returns #NUM! if it cannot find a result after 20 tries. It also fails if the cash flows are all positive or all negative.
Result is too large or too small for ExcelThe formula uses large exponents, factorials or chained multiplication. For example, =5^500 or =FACT(200) returns #NUM! because the result exceeds about 1E+308.
Numbers typed with formatting inside the formulaThe formula contains values written like $1,000 or 1,000. The comma splits the value into separate arguments and the $ is read as a reference marker, so the function receives the wrong inputs.
The error is inherited from another cellThe formula itself looks fine, but one of the cells it refers to already shows #NUM!. Use Trace Error to find the original cell.

How to fix it

1. Find the exact step that fails

Excel for Windows and Mac (desktop). Evaluate Formula is not available in Excel for the web.

  1. Select the cell that shows #NUM!.
  2. Click the yellow warning icon next to the cell and choose Show Calculation Steps, or go to Formulas > Evaluate Formula.
  3. Click Evaluate repeatedly until the underlined part turns into #NUM!. That is the function or value causing the error.
  4. If the error comes from another cell, use Formulas > Error Checking > Trace Error to jump to the source cell and fix it there.

2. Correct impossible inputs (negative numbers, reversed dates, out-of-range arguments)

  1. For square roots of values that may be negative, use =SQRT(ABS(A1)) if you want the root of the absolute value, or fix the source value if it should not be negative.
  2. For DATEDIF, make sure the first date is the earlier one: =DATEDIF(start_date, end_date, "d"). Swap the two cell references if the result is #NUM!.
  3. For DATE, check that the year is between 1900 and 9999 (Windows default date system).
  4. For SMALL or LARGE, check that k is not bigger than the number of values in the range, for example =SMALL(A1:A10, 11) returns #NUM!.
  5. Press Enter and confirm the cell now shows a number.

3. Remove formatting from numbers typed into the formula

  1. Click the cell and look at the formula in the formula bar.
  2. Replace any typed values like $1,000 or 1,000 with plain numbers such as 1000.
  3. Apply currency or thousands formatting to the cell instead, using Home > Number Format.
  4. Press Enter to recalculate.

4. Fix IRR, XIRR or RATE that won't converge

  1. Check that the cash flow range has at least one negative value (money out, such as the initial investment) and at least one positive value (money in).
  2. For XIRR, check that every value has a matching date and the first date is the earliest.
  3. Add a guess argument closer to the expected rate, for example =IRR(B2:B10, -0.1) or =IRR(B2:B10, 0.3). IRR uses 0.1 (10%) when guess is left out.
  4. Try several guess values if the cash flows change sign more than once, since there may be more than one valid rate.
  5. For RATE, confirm payments and present value have opposite signs, for example =RATE(60, -200, 10000).

5. Adjust iterative calculation settings

Windows: File > Options > Formulas. Mac: Excel > Settings (Preferences in older versions) > Calculation. Mainly helps formulas that use circular references; IRR has its own fixed 20-try limit, so use a guess value for IRR first.

  1. On Windows, go to File > Options > Formulas. On Mac, go to Excel > Settings > Calculation.
  2. Under Calculation options, check Enable iterative calculation (on Mac: Use iterative calculation).
  3. Raise Maximum Iterations above the default of 100.
  4. Lower Maximum Change below the default of 0.001 for more precision. Both changes make calculation slower.
  5. Click OK and press F9 (Windows) or Cmd+= (Mac) to recalculate.

6. Keep results in range or hide expected errors

  1. If a result exceeds about 1E+308, reduce the exponent or split the calculation into smaller steps, or work with logarithms (for example LN or LOG) instead of the raw value.
  2. If #NUM! is an expected outcome for some rows, wrap the formula: =IFERROR(SQRT(A1), "") or =IFERROR(IRR(B2:B10), "n/a").
  3. Use IFERROR only after confirming the inputs are correct, because it hides every error type, not just #NUM!.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Rebuild the formula in a blank cell using fixed test numbers you know are valid, then swap in cell references one at a time until #NUM! returns; the last reference you added is the problem. Check the function's page on Microsoft Support for its allowed argument ranges. If the workbook came from someone else, ask them what the inputs are supposed to be. For persistent problems, post the formula and sample values on the Microsoft Tech Community Excel forum, where volunteers regularly diagnose #NUM! cases.

Questions people ask

What does #NUM! mean in Excel?

It means a formula contains or produces a numeric value Excel considers invalid, such as the square root of a negative number, a result over about 1E+308, or a function argument outside its allowed range.

Why does DATEDIF return #NUM!?

DATEDIF returns #NUM! when the start date is later than the end date. Swap the two date arguments so the earlier date comes first.

Why does my IRR formula show #NUM!?

IRR gives up after 20 attempts if it cannot find a rate, and it always fails when the cash flows are all positive or all negative. Make the initial investment negative and add a guess value like =IRR(B2:B10, 0.2).

How do I hide #NUM! errors in Excel?

Wrap the formula in IFERROR, for example =IFERROR(A1/B1^C1, ""), to show a blank or your own text instead. Fix the underlying input first, because IFERROR also hides errors you may need to see.

What is the difference between #NUM! and #VALUE!?

#VALUE! means a formula received the wrong type of data, such as text where a number was expected. #NUM! means the data type is right but the numeric value is impossible or out of range.

Sources

Related errors