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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

MathTrig\Helpers::validateNumericNullBool() (MathTrig/Helpers.php:24-38) is the workhorse argument validator for math/trig functions: null becomes 0, bool becomes int, numeric is passed through; everything else throws with ExcelError::throwError($number) - an Excel error string input propagates itself, any other value becomes '#VALUE!'. Used by ABS, SIGN, INT, EXP, SQRT, ROUND and variants, CEILING, FLOOR, BASE, ROMAN, SUBTOTAL, the trig family, and more.

Source

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

    }

    /**
     * Many functions accept null/false/true argument treated as 0/0/1.
     */
    public static function validateNumericNullBool(mixed $number): int|float
    {
        $number = Functions::flattenSingleValue($number);
        if ($number === null) {
            return 0;
        }
        if (is_bool($number)) {
            return (int) $number;
        }
        if (is_numeric($number)) {
            return 0 + $number;
        }

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

    /**
     * Validate numeric, but allow substitute for null.
     */
    public static function validateNumericNullSubstitution(mixed $number, null|float|int $substitute): float|int
    {
        $number = Functions::flattenSingleValue($number);
        if ($number === null && $substitute !== null) {
            return $substitute;
        }
        if (is_numeric($number)) {
            return 0 + $number;
        }

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

View on GitHub (pinned to 65b080eef4)

Solutions

1. Normalize inputs to numbers before evaluation (trim, str_replace thousands/currency, cast)
2. Trap upstream error cells first: if (is_string($v) && str_starts_with($v, '#')) skip
3. Use a numeric value binder or cast cells on read so strings never reach math functions
4. Keep application numbers as PHP int/float instead of formatted strings

Example fix

// before
$result = Absolute::funcAbs($cellValue); // '1,234.50' -> '#VALUE!'

// after: wash the value first
$numeric = is_numeric($cellValue) ? (float) $cellValue
    : (float) str_replace([',', ' ', '%'], '', $cellValue);
$result = Absolute::funcAbs($numeric);

if (is_string($value) && str_starts_with($value, '#')) {
    return $value; // upstream Excel error - do not feed to math functions
}
$number = is_numeric($value) ? (float) $value : null;
if ($number === null) {
    throw new InvalidArgumentException('numeric argument required');
}

function isNumericLike(mixed $value): bool
{
    return $value === null || is_bool($value) || is_numeric($value);
}

$result = Absolute::funcAbs($value);
if ($result === '#VALUE!') {
    // argument was a non-numeric string or object
} elseif (is_string($result) && str_starts_with($result, '#')) {
    // an upstream Excel error string was propagated
}

When it happens

Trigger: ABS('abc') -> '#VALUE!'; any of these functions fed a cell whose value is itself '#N/A' or '#DIV/0!' (error propagation); unwashed imported strings like '1,234' or '12%' where PHP sees a non-numeric string.

Common situations: Locale-formatted numbers as strings (thousands separators, comma decimals, currency symbols); error cells cascading through a sheet; leading/trailing spaces or NBSP from CSV/HTML imports.

Related errors

• #VALUE!
• ExcelError::throwError($index_number)
• #VALUE!
• #REF!
• #VALUE!

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