#NUM!Excel error guide No upload

#NUM! in Excel — Invalid numeric result

Q: How do I fix #NUM! in Excel?

Validate the input before the calculation: Use IF to catch impossible inputs. This is clearer than hiding the error with IFERROR. Give IRR a better guess: IRR expects at least one negative and one positive cash flow. Add a guess close to the expected monthly or annual rate.

Q: Why does #NUM! appear only in some rows?

Usually because only some rows contain the trigger: A square root or logarithm of a negative number. A date earlier than 1 January 1900 in the 1900 date system. IRR or RATE cannot converge with the supplied cash flows or guess.

Q: How can I stop #NUM! from appearing again?

Prevents a negative square root from producing #NUM!. Use this pattern as a guard: =IF(A2<0,"Must be positive",SQRT(A2))

#NUM! means a formula produced a number Excel cannot represent, or an argument is outside the valid range. Common causes are a negative square root, an impossible date, or an iterative function that cannot converge.

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

The short answer

What it looks like

A maths, finance or date formula returns #NUM!
IRR or RATE cannot find a result
A date calculation produces a negative or out-of-range date

Why #NUM! happens

A square root or logarithm of a negative number.
A date earlier than 1 January 1900 in the 1900 date system.
IRR or RATE cannot converge with the supplied cash flows or guess.
A function argument is outside its allowed range, such as a negative payment period.
A calculation exceeds the largest number Excel can store.

How to fix #NUM!

1
Validate the input before the calculation
Use IF to catch impossible inputs. This is clearer than hiding the error with IFERROR.
```
=IF(A2<0,"Must be positive",SQRT(A2))
```
2
Give IRR a better guess
IRR expects at least one negative and one positive cash flow. Add a guess close to the expected monthly or annual rate.
```
=IRR(B2:B20,0.1)
```
3
Check date ranges
Excel cannot display a negative date. If you subtract a later date from an earlier one, swap the order or use ABS if direction does not matter.
```
=ABS(B2-A2)
```
4
Inspect the arguments
Use Evaluate Formula to find the step that produces #NUM!, then check the number, sign and allowed range of that argument.

A safer formula pattern

=IF(A2<0,"Must be positive",SQRT(A2))

Prevents a negative square root from producing #NUM!.

Check it in your own workbook

Paste the formula into the free browser tools to see the exact fix without uploading your file. The Error Fixer explains the code, the Formula Checker finds syntax problems, and the Formula Explainer breaks down nested logic.

Open the interactive Error Fixer Check a formula

Related functions

SQRT IRR RATE IF IFERROR

Frequently asked questions

What does #NUM! mean in Excel?

How do I fix #NUM! in Excel?

Validate the input before the calculation: Use IF to catch impossible inputs. This is clearer than hiding the error with IFERROR. Give IRR a better guess: IRR expects at least one negative and one positive cash flow. Add a guess close to the expected monthly or annual rate.

Why does #NUM! appear only in some rows?

Usually because only some rows contain the trigger: A square root or logarithm of a negative number. A date earlier than 1 January 1900 in the 1900 date system. IRR or RATE cannot converge with the supplied cash flows or guess.

How can I stop #NUM! from appearing again?

Prevents a negative square root from producing #NUM!. Use this pattern as a guard: =IF(A2<0,"Must be positive",SQRT(A2))

Related Excel error guides

All error codes →

#VALUE!Wrong value type#VALUE! means Excel expected a number, date or logical value but found text, an empty cell or a value from another data type. The fix is to convert the input, remove hidden characters, or guard the calculation with IFERROR.#REF!Broken cell reference#REF! means a formula points to a cell, row, column or sheet that no longer exists. It usually appears after deleting rows or columns, moving a sheet, or pasting a formula into a workbook that does not contain the referenced sheet.#N/ALookup value not found#N/A means a lookup or match function could not find the value. The value may be missing, spelled differently, stored as text instead of a number, or surrounded by spaces. Use exact-match mode and return a clear fallback.#DIV/0!Division by zero#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

Still stuck?

Send the workbook and get the formula fixed

A human Excel expert can repair the formula, explain the cause and return a tested workbook — usually within 24 hours. Fixed quote before work starts, from $15/hr.

Get a fixed quote Ask on WhatsApp

#NUM! in Excel — Invalid numeric result

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

Why #NUM! happens

A square root or logarithm of a negative number.

A date earlier than 1 January 1900 in the 1900 date system.

IRR or RATE cannot converge with the supplied cash flows or guess.

A function argument is outside its allowed range, such as a negative payment period.

A calculation exceeds the largest number Excel can store.

How to fix #NUM!

Validate the input before the calculation

Use IF to catch impossible inputs. This is clearer than hiding the error with IFERROR.

=IF(A2<0,"Must be positive",SQRT(A2))

Give IRR a better guess

IRR expects at least one negative and one positive cash flow. Add a guess close to the expected monthly or annual rate.

=IRR(B2:B20,0.1)

Check date ranges

Excel cannot display a negative date. If you subtract a later date from an earlier one, swap the order or use ABS if direction does not matter.

=ABS(B2-A2)

Inspect the arguments

Use Evaluate Formula to find the step that produces #NUM!, then check the number, sign and allowed range of that argument.

Frequently asked questions

What does #NUM! mean in Excel?

How do I fix #NUM! in Excel?

Why does #NUM! appear only in some rows?

How can I stop #NUM! from appearing again?

Prevents a negative square root from producing #NUM!. Use this pattern as a guard: =IF(A2<0,"Must be positive",SQRT(A2))