← Back to Error Directory

How to Fix #NUM! Error in Excel & Google Sheets

A complete diagnostic guide covering every function that throws #NUM!, why it happens, and exactly how to fix it — with live examples you can test right on this page.

Table of Contents

• 1. What #NUM! Actually Means
• 2. #NUM! vs #VALUE! vs #REF! — Telling Them Apart
• 3. The Six Root Causes of #NUM!
• 4. Interactive Demo: See #NUM! Trigger Live
• 5. Fixing #NUM! in Math Functions (SQRT, POWER, LOG)
• 6. Fixing #NUM! in Financial Functions (RATE, IRR, NPER)
• 7. Fixing #NUM! in Date Functions
• 8. #NUM! in Array & Dynamic Array Formulas
• 9. Google Sheets Differences
• 10. #NUM! Errors Triggered by VBA
• 11. Preventing #NUM! Before It Happens
• 12. Best Practices Checklist
• 13. Frequently Asked Questions

1. What #NUM! Actually Means

The #NUM! error is Excel's way of telling you: "I understood your formula, the data types are correct, but the math itself doesn't produce a valid, representable number." This is fundamentally different from a syntax problem — Excel isn't confused about what you're asking it to do, it's confused about the answer.

Excel stores numbers as 64-bit floating-point values (IEEE 754 double precision), which gives it roughly 15 significant digits of precision and a maximum magnitude of about 1.7976931348623158 × 10³⁰⁸. Anything mathematically undefined (like the square root of a negative number in real-number arithmetic) or anything that falls outside that representable range triggers #NUM!.

Three distinct situations produce this error, and distinguishing between them is the first step to fixing it:

• Mathematically undefined results — e.g., SQRT(-16), which has no real-number solution.
• Out-of-range results — e.g., 10^400, which exceeds the maximum float Excel can store.
• Non-convergent iterative calculations — e.g., RATE() or IRR() failing to find a solution within their internal iteration limit (usually 20 tries by default).

2. #NUM! vs #VALUE! vs #REF! — Telling Them Apart

A huge number of people searching for a "#NUM! fix" are actually looking at a different error entirely. Here's the fast way to tell them apart before you spend time on the wrong fix:

If your formula returns #VALUE! instead, see our dedicated #VALUE! error guide. If it's #DIV/0!, see the #DIV/0! guide instead — this page focuses specifically on #NUM!.

3. The Six Root Causes of #NUM!

Cause 1: Negative input to a function that requires non-negative numbers

Functions like SQRT, LOG (with certain bases), and ASIN/ACOS (which only accept values between -1 and 1) will throw #NUM! the instant they receive an input outside their valid domain.

=SQRT(-25)        → #NUM! (no real square root of a negative number)
=LOG(-10)         → #NUM! (logarithm of a negative number is undefined)
=ASIN(2)          → #NUM! (ASIN only accepts values between -1 and 1)

Cause 2: Result exceeds Excel's numeric limits

Excel's maximum representable number is approximately 1.79769313486232 × 10³⁰⁸. Any formula whose result exceeds this — most commonly from exponentiation — returns #NUM! instead of a value.

=10^400           → #NUM! (exceeds Excel's max float value)
=FACT(200)        → #NUM! (200! is astronomically larger than Excel can store)

Cause 3: Iterative financial functions failing to converge

Functions like RATE, IRR, and NPER don't solve algebraically — they use iterative approximation (similar to Newton's method) and give up after a fixed number of attempts (20 by default for RATE). If your cash-flow inputs are inconsistent or your initial guess is too far from the real answer, the function returns #NUM! rather than an incorrect result.

=RATE(360, -800, 200000)   → may return #NUM! if payment/loan ratio is unrealistic
=IRR(B2:B14)               → #NUM! if all cash flows are the same sign (no sign change)

Cause 4: Invalid arguments in statistical or engineering functions

Functions like CONFIDENCE, BINOM.DIST, or QUARTILE validate their inputs strictly — for example, a probability argument outside the 0–1 range, or a sample size of zero, will throw #NUM! rather than attempting a nonsensical calculation.

Cause 5: Circular or self-referencing iterative calculations without iterative calculation enabled

When a workbook needs iterative calculation (Formulas → Calculation Options → Enable iterative calculation) but it's turned off, formulas that depend on their own prior result — common in interest-on-interest or goal-seeking models — can return #NUM! instead of resolving.

Cause 6: Array formulas producing an invalid numeric array

In legacy array formulas (entered with Ctrl+Shift+Enter) and modern dynamic array formulas, if any single element of the resulting array is mathematically invalid, the entire spilled range can show #NUM! rather than isolating the error to one cell.

4. Interactive Demo: See #NUM! Trigger Live

Try the calculator below — it mimics exactly how Excel evaluates SQRT() and POWER() so you can see the boundary between a valid result and a #NUM! error in real time.

Try SQRT with -16, or 10^x with x = 400 — both trigger the same #NUM! condition Excel would show.

5. Fixing #NUM! in Math Functions (SQRT, POWER, LOG)

Fix for SQRT with negative numbers

If negative inputs are expected and you want the magnitude of the square root (ignoring sign), wrap the input in ABS():

=SQRT(ABS(A2))

If negative values should instead be flagged as invalid rather than silently converted, use a validation wrapper:

=IF(A2<0, "Invalid: negative value", SQRT(A2))

Fix for POWER/exponent overflow

There's no way to "fix" a number that's genuinely too large to store — the correct approach is to cap the exponent or redesign the calculation to avoid such extreme magnitudes, often by working in logarithmic space instead:

=IF(B2>300, "Result too large to display", 10^B2)

Fix for LOG of a negative or zero value

=IF(C2<=0, "Undefined", LOG(C2))

6. Fixing #NUM! in Financial Functions (RATE, IRR, NPER)

RATE() convergence failures

RATE fails most often because its default starting guess (10%) is too far from the true periodic rate for loans with unusual payment structures. Supply an explicit guess as the 6th argument:

=RATE(360, -800, 200000, 0, 0, 0.005)

If it still fails, verify your sign convention — outgoing payments (money you pay) must be negative, and the loan principal (money you receive) must be positive, or vice versa consistently. Mixed-up signs are the single most common cause of RATE returning #NUM!.

IRR() sign-change requirement

IRR mathematically requires at least one positive and one negative value in the cash flow range — it's solving for the discount rate where the sum of cash flows equals zero, which is impossible if all values point the same direction (all outflows or all inflows).

=IF(COUNTIF(B2:B14,">0")=0, "No positive cash flow present", IRR(B2:B14))

NPER() with an unrealistic rate/payment combination

Like RATE, NPER can fail to converge when the payment amount is too small to ever pay down the loan at the given interest rate (creating an infinite or negative number of periods). Double-check that your payment exceeds the interest accruing per period.

7. Fixing #NUM! in Date Functions

Excel stores dates as serial numbers starting from January 1, 1900 (serial number 1). Any date-related formula that calculates a serial number below zero — most commonly from subtracting a later date incorrectly, or from DATE() being given a negative year/month combination that resolves before the epoch — returns #NUM!.

=DATE(1899,1,1)      → #NUM! (before Excel's date system begins)
=EDATE(A2,-5000)      → #NUM! if the result would be a negative serial number

Fix: validate the resulting date is within Excel's supported range (1/1/1900 to 12/31/9999) before performing the subtraction or offset.

8. #NUM! in Array & Dynamic Array Formulas

With dynamic array functions like SEQUENCE(), a #NUM! error commonly appears when the requested array size is invalid — for example, a negative count, a step of zero producing an infinite sequence, or a total array size exceeding Excel's row/column limits (1,048,576 rows × 16,384 columns).

=SEQUENCE(-5)              → #NUM! (negative row count is invalid)
=SEQUENCE(2000000)          → #NUM! (exceeds max worksheet rows)

For legacy CSE array formulas, if even one intermediate calculation inside the array produces an invalid number, the entire array result displays #NUM!. Isolate the faulty row using a helper column that runs each calculation individually to find the specific offending value.

9. Google Sheets Differences

Google Sheets handles most of these situations identically to Excel, since both follow IEEE 754 floating-point standards, but there are a few notable differences:

• Google Sheets' SEQUENCE() has a lower practical row/array ceiling before triggering a size error, since Sheets enforces stricter total-cell limits per spreadsheet (10 million cells total, shared across all sheets in the file).
• IRR() and RATE() in Sheets sometimes converge on different iteration counts than Excel due to differing internal solver defaults — a formula that works in one may need an explicit guess value in the other.
• Google Sheets shows #NUM! with the exact same label as Excel, unlike some of its other error labels (like #ERROR! for generic parse issues) that don't exist in Excel at all.

10. #NUM! Errors Triggered by VBA

When VBA code writes a formula into a cell via Range("A1").Formula = "=SQRT(-1)", the resulting #NUM! doesn't throw a VBA runtime error — VBA successfully writes the formula, and Excel's calculation engine independently evaluates it and returns #NUM! as the cell's displayed value. If you need to catch this programmatically, check the cell's error value after calculation:

If Application.WorksheetFunction.IsError(Range("A1").Value) Then
    MsgBox "Formula returned an error: " & CStr(Range("A1").Value)
End If

Note that Application.WorksheetFunction.Sqr (VBA's own square root function, distinct from the worksheet SQRT) throws an actual VBA runtime error 5 ("Invalid procedure call") on negative input rather than returning a cell-level #NUM! — the two behave differently because one runs in VBA's execution context and the other in the worksheet calculation engine.

11. Preventing #NUM! Before It Happens

The most maintainable spreadsheets never let #NUM! reach the user in the first place — they validate inputs before the risky calculation runs. This "pre-flight check" pattern applies across nearly every cause above:

=IF(OR(A2<0, A2=""), "Check input", SQRT(A2))
=IFERROR(RATE(B2,C2,D2), "Rate could not be calculated — check sign convention")
=IF(AND(E2>=-1, E2<=1), ASIN(E2), "Value must be between -1 and 1")

This is preferable to a blanket IFERROR() wrapper because it tells the user why the calculation failed rather than just masking the error with a generic fallback.

12. Best Practices Checklist

• ✅ Identify which specific function is throwing the error before attempting a fix — #NUM! from SQRT needs a different fix than #NUM! from RATE.
• ✅ Use ISNUMBER() or explicit range checks before feeding values into domain-restricted functions.
• ✅ For financial functions, always double-check the sign convention (outflows negative, inflows positive) before assuming the function is broken.
• ✅ Supply an explicit initial guess to RATE/IRR when working with unusual payment schedules.
• ✅ Use descriptive IF-based validation instead of blanket IFERROR when the "why" matters to whoever reads the sheet.
• ✅ For array/dynamic formulas, isolate the calculation to a helper column to find which specific row is producing the invalid number.

13. Frequently Asked Questions

What does the #NUM! error mean in Excel?

The #NUM! error means a formula produced a number that Excel cannot represent or display — usually because the math is invalid (like the square root of a negative number), the result is too large or too small for Excel's numeric range, or an iterative function (like RATE or IRR) could not converge on an answer.

Is #NUM! the same as #VALUE! in Excel?

No. #VALUE! means a formula received the wrong data type (like text where a number was expected). #NUM! means the data types were correct, but the resulting calculation is mathematically invalid or outside Excel's supported numeric range.

How do I stop #NUM! errors from showing in my spreadsheet?

Wrap the formula in IFERROR to display a fallback value, or better, add a validation check like IF(value<0,"N/A",SQRT(value)) before the calculation runs, so invalid inputs never reach the function in the first place.

Why does RATE() keep returning #NUM! even though my loan numbers look correct?

This almost always comes down to sign convention — payments and the loan principal need opposite signs (one negative, one positive) for RATE to converge. Also try supplying an explicit guess as the sixth argument if the default 10% guess is far from the real rate.

Can #NUM! appear in a cell that has no formula at all?

No — #NUM! is always the result of formula evaluation. If you see it in what looks like a static cell, it likely contains a formula returning that error, or it's the spilled result of a dynamic array formula in a different cell.

Related Guides

• How to Fix #VALUE! Error in Excel
• How to Fix #DIV/0! Error in Excel
• How to Fix #REF! Error in Excel
• Browse the Full 266+ Error Directory

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.