PHPOffice/PhpSpreadsheet · error · PhpSpreadsheet\Calculation\Exception

#VALUE!

Error message

#VALUE!

What it means

#VALUE! from the base-conversion validator (ConvertBase::validateValue) shared by BIN2DEC/BIN2HEX/BIN2OCT, DEC2BIN/DEC2HEX/DEC2OCT, HEX2* and OCT2*. A boolean value is only tolerated in OpenOffice compatibility mode; in the default Excel (and Gnumeric) mode is_bool($value) throws Calculation\Exception('#VALUE!'), returned as the result string.

Source

Thrown at src/PhpSpreadsheet/Calculation/Engineering/ConvertBase.php:19

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Engineering;

use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;

abstract class ConvertBase
{
    use ArrayEnabled;

    protected static function validateValue(mixed $value): string
    {
        if (is_bool($value)) {
            if (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE) {
                throw new Exception(ExcelError::VALUE());
            }
            $value = (int) $value;
        }

        if (is_numeric($value)) {
            if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) {
                $value = floor((float) $value);
            }
        }

        return strtoupper(StringHelper::convertToString($value));
    }

    protected static function validatePlaces(mixed $places = null): ?int
    {
        if ($places === null) {
            return $places;
        }

View on GitHub (pinned to 65b080eef4)

Solutions

1. Cast booleans to int before calling the conversion functions: (int) $value.
2. If you deliberately need OpenOffice semantics, set Functions::setCompatibilityMode(Functions::COMPATIBILITY_OPENOFFICE) once at bootstrap - but expect other behavioural differences.
3. Filter boolean cells out of conversion inputs when importing mixed-type sheets.

Example fix

// before
$dec = ConvertBinary::toDecimal($cellValue); // true -> '#VALUE!'

// after
$dec = ConvertBinary::toDecimal(is_bool($cellValue) ? (int) $cellValue : $cellValue);

if (is_bool($value)) {
    $value = (int) $value; // Excel-mode equivalent of OpenOffice coercion
}
$dec = ConvertBinary::toDecimal($value);

/** Base-conversion inputs must not be boolean (except in OpenOffice mode). */
function isAcceptableConversionValue(mixed $v): bool
{
    return !is_bool($v);
}

When it happens

Trigger: =BIN2DEC(TRUE()) with default compatibility; ConvertBinary::toDecimal(true) from PHP; conversion functions applied to cells holding TRUE/FALSE values. Functions::setCompatibilityMode(Functions::COMPATIBILITY_OPENOFFICE) switches the behaviour: TRUE becomes 1, FALSE becomes 0.

Common situations: Reading boolean cells or boolean API/JSON values and feeding them into conversion functions; replicating LibreOffice results in a pipeline that runs with Excel compatibility; spreadsheets authored in OpenOffice behaving differently after import.

Related errors

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

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