{"record":{"id":"d5232099bbd8f662","repo":"PHPOffice/PhpSpreadsheet","slug":"value-d52320","errorCode":"#VALUE!","errorMessage":"#VALUE!","messagePattern":"#VALUE!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/MathTrig/Helpers.php","lineNumber":37,"sourceCode":"    }\n\n    /**\n     * Many functions accept null/false/true argument treated as 0/0/1.\n     */\n    public static function validateNumericNullBool(mixed $number): int|float\n    {\n        $number = Functions::flattenSingleValue($number);\n        if ($number === null) {\n            return 0;\n        }\n        if (is_bool($number)) {\n            return (int) $number;\n        }\n        if (is_numeric($number)) {\n            return 0 + $number;\n        }\n\n        throw new Exception(ExcelError::throwError($number));\n    }\n\n    /**\n     * Validate numeric, but allow substitute for null.\n     */\n    public static function validateNumericNullSubstitution(mixed $number, null|float|int $substitute): float|int\n    {\n        $number = Functions::flattenSingleValue($number);\n        if ($number === null && $substitute !== null) {\n            return $substitute;\n        }\n        if (is_numeric($number)) {\n            return 0 + $number;\n        }\n\n        throw new Exception(ExcelError::throwError($number));\n    }\n","sourceCodeStart":19,"sourceCodeEnd":55,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/MathTrig/Helpers.php#L19-L55","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Normalize inputs to numbers before evaluation (trim, str_replace thousands/currency, cast)","Trap upstream error cells first: if (is_string($v) && str_starts_with($v, '#')) skip","Use a numeric value binder or cast cells on read so strings never reach math functions","Keep application numbers as PHP int/float instead of formatted strings"],"exampleFix":"// before\n$result = Absolute::funcAbs($cellValue); // '1,234.50' -> '#VALUE!'\n\n// after: wash the value first\n$numeric = is_numeric($cellValue) ? (float) $cellValue\n    : (float) str_replace([',', ' ', '%'], '', $cellValue);\n$result = Absolute::funcAbs($numeric);","handlingStrategy":"type-guard","validationCode":"if (is_string($value) && str_starts_with($value, '#')) {\n    return $value; // upstream Excel error - do not feed to math functions\n}\n$number = is_numeric($value) ? (float) $value : null;\nif ($number === null) {\n    throw new InvalidArgumentException('numeric argument required');\n}","typeGuard":"function isNumericLike(mixed $value): bool\n{\n    return $value === null || is_bool($value) || is_numeric($value);\n}","tryCatchPattern":"$result = Absolute::funcAbs($value);\nif ($result === '#VALUE!') {\n    // argument was a non-numeric string or object\n} elseif (is_string($result) && str_starts_with($result, '#')) {\n    // an upstream Excel error string was propagated\n}","preventionTips":["Wash locale-formatted strings (separators, currency, spaces) before math","Check cells for '#' error values before using them as numbers","Use numeric value binders or casts at read time"],"tags":["phpspreadsheet","php","excel","math","value-error","type-mismatch","error-propagation"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}