📊Google Sheets Diagnostic PlaybookUpdated 2026-11-06

Google Sheets: Fix #NUM! Function Parameter Out of Range Error

Exact Error Message:#NUM! Function parameter out of range.
⚡ Direct Answer / Immediate Fix:

Check for negative values passed into functions requiring positive inputs (e.g. SQRT, LOG, FACT). Wrap values in ABS() if magnitude is what you need: =SQRT(ABS(A2)).

Why This Error Occurs (Root Cause Analysis)

A mathematical formula receives an argument that exceeds calculation boundaries, such as taking the square root of a negative number (SQRT(-5)), an iterative calculation that fails to converge, or financial calculations (IRR, NPV) with invalid cashflow sequences. The #NUM! error indicates that a formula argument is mathematically invalid or falls outside allowable numeric bounds. Common causes include taking the square root of a negative number (`SQRT(-4)`), calculating logarithms of zero (`LN(0)`), passing a negative number into `LARGE` or `SMALL`, or generating numbers that exceed Google Sheets 64-bit IEEE floating-point limit (~1.7976931348623157E+308).

Step-by-Step Resolution Guide

Follow these verified steps in sequence to isolate and eliminate the error condition:

1
Check for negative values passed into functions requiring positive inputs (e.g. SQRT, LOG, FACT). Wrap values in ABS() if magnitude is what you need: =SQRT(ABS(A2)).
2
In financial calculations like IRR, provide an initial guess rate (e.g. =IRR(A1:A10, 0.1)) to help the iterative engine converge on a solution.
3
Ensure dates and serial numbers are within Google Sheets valid range (between December 30, 1899 and December 31, 9999).

Technical Edge Cases & Environment Exceptions

⚠️ Critical Edge Case Scenarios

Negative values passed into mathematical operations requiring positive domain inputs, such as `SQRT(-25)`, `LN(0)`, or setting `k` higher than the population count in `LARGE(A1:A10, 15)`. IEEE floating point numbers exceeding 1.7976931348623157E+308 also trigger #NUM!.

When This Doesn't Work (Alternative Diagnostics & Workarounds)

🔧 Advanced Escalation & Fallback Workarounds

When debugging financial functions like IRR or XIRR that fail to converge within Google Sheets' standard 20 iterations, provide an explicit initial guess parameter (e.g. `=XIRR(A1:A20, B1:B20, 0.1)`). Supplying an estimated discount rate helps the Newton-Raphson solver stabilize.

Prevention Checklist & Operational Standards

🛡️ How to Permanently Prevent Recurrence

Add input bounds checking using MIN() and MAX() to enforce positive thresholds on numeric parameters.

Frequently Asked Questions: #NUM! Function parameter out of range.

Why does DATEDIF return #NUM! when calculating days between dates?

`DATEDIF(start_date, end_date, unit)` requires the start date to be earlier than or equal to the end date. If the start date is later than the end date, DATEDIF throws #NUM!. Reverse the date arguments or wrap in `IF(start<=end, ...)`.

What is the largest number Google Sheets can compute before returning #NUM!?

Google Sheets supports numbers up to approximately 1.79E+308. Any mathematical operation (such as `2^1024` or massive factorials via `FACT()`) that exceeds this threshold results in an immediate #NUM! overflow error.

How do I handle LARGE() or SMALL() when the k parameter exceeds available rows?

If a range contains 10 rows and you request `=LARGE(A1:A10, 15)`, Google Sheets returns #NUM! because the 15th largest value does not exist. Use `=LARGE(A1:A10, MIN(15, COUNT(A1:A10)))` to prevent out-of-bounds queries.

Why does the DATEDIF function return #NUM! error?

DATEDIF requires the start_date parameter to be strictly earlier than or equal to the end_date parameter. If the start date is later than the end date, the formula returns #NUM! to signal negative elapsed duration.

What is the maximum number Google Sheets can calculate before throwing #NUM!?

Google Sheets utilizes 64-bit double-precision floating-point numbers. Any mathematical calculation producing a result greater than approximately 1.79E+308 or smaller than 2.22E-308 will result in an arithmetic overflow #NUM! error.

Related Guides & Architecture Walkthroughs

More Google Sheets Diagnostic Playbooks