PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#NUM!

#NUM!

Error message

#NUM!

What it means

MathTrig\Helpers::validateNotNegative() (MathTrig/Helpers.php:59-66) is the domain guard for functions requiring a non-negative number: it throws #NUM! by default, or a caller-supplied $except error string. Callers include SQRT (Sqrt::funcSqrt), FACT/FACTDOUBLE (Factorial), GCD/LCM values, COMBIN/COMBINX counts, and RAND's max-min span (RANDBETWEEN with min > max).

Source

Thrown at src/PhpSpreadsheet/Calculation/MathTrig/Helpers.php:65

            return $substitute;
        }
        if (is_numeric($number)) {
            return 0 + $number;
        }

        throw new Exception(ExcelError::throwError($number));
    }

    /**
     * Confirm number >= 0.
     */
    public static function validateNotNegative(float|int $number, ?string $except = null): void
    {
        if ($number >= 0) {
            return;
        }

        throw new Exception($except ?? ExcelError::NAN());
    }

    /**
     * Confirm number > 0.
     */
    public static function validatePositive(float|int $number, ?string $except = null): void
    {
        if ($number > 0) {
            return;
        }

        throw new Exception($except ?? ExcelError::NAN());
    }

    /**
     * Confirm number != 0.
     */
    public static function validateNotZero(float|int $number): void

View on GitHub (pinned to 65b080eef4)

Solutions

1. Check $value >= 0 before calling these functions
2. Use abs() only when the domain genuinely means magnitude
3. Supply min/max to RANDBETWEEN in the correct (min, max) order
4. Validate numeric bounds at the input boundary, not inside spreadsheet formulas

Example fix

// before
$result = Sqrt::funcSqrt($a - $b); // negative when $b > $a -> '#NUM!'

// after: guard the domain, then choose a policy
$delta = $a - $b;
if ($delta < 0) {
    throw new InvalidArgumentException('cannot take SQRT of a negative difference');
}
$result = Sqrt::funcSqrt($delta);

if ((float) $number < 0.0) {
    throw new InvalidArgumentException('value must be non-negative for this function');
}
$result = Sqrt::funcSqrt($number);

function isNonNegativeNumber(mixed $value): bool
{
    return is_numeric($value) && (float) $value >= 0.0;
}

$result = Factorial::funcFact($value);
if ($result === '#NUM!' && (float) $value < 0.0) {
    // negative input reached a non-negative-only domain (SQRT/FACT/GCD/LCM/COMBIN)
}

When it happens

Trigger: SQRT(-1); FACT(-5); GCD(-12, 8); RANDBETWEEN(100, 50) where the negative span (max-min) reaches the guard.

Common situations: User-supplied quantities or durations that can go negative; square roots of differences (a-b with b > a); random ranges supplied in the wrong order; date/time deltas computed backwards.

Related errors

• #NUM!
• #VALUE!
• Invalid parameter passed: formula
• Token with id $id does not exist.
• #N/A

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/e43e306b52b63d89. Report an issue: GitHub.