PHPOffice/PhpSpreadsheet · error · Exception

#NUM!

#NUM!

Error message

#NUM!

What it means

#NUM! from the shared bitwise argument validator used by BITAND, BITOR, BITXOR, BITLSHIFT and BITRSHIFT. Excel limits bitwise operands to non-negative integers up to 2^48-1 (281,474,976,710,655); validateBitwiseArgument() throws Calculation\Exception('#NUM!') for any integer outside 0..2^48-1, and the public wrappers return it as the '#NUM!' result string.

Source

Thrown at src/PhpSpreadsheet/Calculation/Engineering/BitWise.php:204

        if ($result > 2 ** 48 - 1) { // possible because shiftAmount can be negative
            return ExcelError::NAN();
        }

        return $result;
    }

    /**
     * Validate arguments passed to the bitwise functions.
     */
    private static function validateBitwiseArgument(mixed $value): float
    {
        $value = self::nullFalseTrueToNumber($value);

        if (is_numeric($value)) {
            $value = (float) $value;
            if ($value == floor($value)) {
                if (($value > 2 ** 48 - 1) || ($value < 0)) {
                    throw new Exception(ExcelError::NAN());
                }

                return floor($value);
            }

            throw new Exception(ExcelError::NAN());
        }

        throw new Exception(ExcelError::VALUE());
    }

    /**
     * Validate arguments passed to the bitwise functions.
     */
    private static function validateShiftAmount(mixed $value): int
    {
        $value = self::nullFalseTrueToNumber($value);

View on GitHub (pinned to 65b080eef4)

Solutions

1. Clamp or reject inputs to the range 0..281474976710655 before calling the bitwise functions.
2. For negative or >48-bit integers, compute the result in PHP with GMP/BCMath and write the value instead of the formula.
3. Remember BITLSHIFT/BITRSHIFT also return #NUM! when the shifted result exceeds 2^48-1, not just the input.
4. Check returned values for the '#NUM!' string when inputs are dynamic.

Example fix

// before
$result = BitWise::BITAND($userMask, $flags); // $userMask = -1 -> '#NUM!'

// after
$result = ($userMask >= 0 && $userMask <= 2 ** 48 - 1)
    ? BitWise::BITAND($userMask, $flags)
    : gmp_intval(gmp_and(gmp_init($userMask), gmp_init($flags)));

const BITWISE_MAX = 281474976710655; // 2**48 - 1
if (!is_int($n) || $n < 0 || $n > BITWISE_MAX) {
    throw new \InvalidArgumentException('bitwise operand must be an integer in 0..2^48-1');
}
$result = BitWise::BITAND($n, $mask);

/** Excel bitwise operands: non-negative integers up to 2^48-1. */
function isValidBitwiseOperand(mixed $v): bool
{
    return is_numeric($v)
        && (float) $v == floor((float) $v)
        && (float) $v >= 0
        && (float) $v <= 2 ** 48 - 1;
}

When it happens

Trigger: =BITAND(281474976710656, 1), =BITXOR(-1, 2), =BITLSHIFT(300000000000000, 8); PHP calls like BitWise::BITAND(-5, 3); values produced by float arithmetic that exceed the 48-bit ceiling after the (float) cast.

Common situations: Passing negative numbers expecting two's-complement behaviour (Excel bitwise functions reject negatives outright); feeding 64-bit IDs, bitmasks from GMP/BCMath code, or float-computed values straight into bitwise formulas; migrating PHP ~ or & expressions to Excel equivalents.

Related errors

• #VALUE!
• #NUM!
• #NUM!
• #VALUE!
• #NUM!

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