Google Sheets: Fix #NUM! Function Parameter Out of Range Error
#NUM! Function parameter out of range.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:
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
Google Sheets: How to Fix IMPORTRANGE Internal Error
Error: IMPORTRANGE internal error
Google Sheets: How to Fix #REF! Circular Dependency Detected
#REF! Circular dependency detected
Google Sheets: Fix #VALUE! Function Parameter Expects Number Error
#VALUE! Function parameter expects number values. But "text" is a text and cannot be coerced to a number.