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

#VALUE!

Error message

#VALUE!

What it means

TextData\Format::convertValue() preprocesses the argument for VALUE() and NUMBERVALUE(). After trimming, if the string is itself an Excel error value ('#REF!', '#DIV/0!', ...), it rethrows CalcExp with that exact string, propagating the upstream error into the function's result. The reported '#VALUE!' message is the special case where the input string literally was '#VALUE!'.

Source

Thrown at src/PhpSpreadsheet/Calculation/TextData/Format.php:160

    }

    /**
     * @param mixed $value Value to check
     */
    private static function convertValue(mixed $value, bool $spacesMeanZero = false): mixed
    {
        $value = $value ?? 0;
        if (is_bool($value)) {
            if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
                $value = (int) $value;
            } else {
                throw new CalcExp(ExcelError::VALUE());
            }
        }
        if (is_string($value)) {
            $value = trim($value);
            if (ErrorValue::isError($value, true)) {
                throw new CalcExp($value);
            }
            if ($spacesMeanZero && $value === '') {
                $value = 0;
            }
        }

        return $value;
    }

    /**
     * VALUE.
     *
     * @param mixed $value Value to check
     *                         Or can be an array of values
     *
     * @return array<mixed>|DateTimeInterface|float|int|string A string if arguments are invalid
     *         If an array of values is passed for the argument, then the returned result
     *            will also be an array with matching dimensions

View on GitHub (pinned to 65b080eef4)

Solutions

1. Clean the source cell/region first: fix the upstream formula producing the error, or wrap it with IFERROR
2. Pre-check in the formula: =IF(ISERROR(A1), "", VALUE(A1))
3. Strip literal error strings in PHP before passing values in: if (ErrorValue::isError($v, true)) handle separately
4. When calling Format::VALUE()/NUMBERVALUE() directly, catch Calculation\Exception and use its message as the error result

Example fix

// before: A1 holds #REF! -> VALUE propagates it
$sheet->getCell('B1')->setValue('=VALUE(A1)');

// after
$sheet->getCell('B1')->setValue('=IF(ISERROR(A1), "", VALUE(A1))');

use PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue;

if (is_string($value) && ErrorValue::isError(trim($value), true)) {
    return ''; // or propagate deliberately: return $value;
}
return Format::VALUE($value);

function isErrorString(mixed $v): bool
{
    return is_string($v) && \PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue::isError(trim($v), true);
}

try {
    $v = Format::VALUE($input);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    $v = $e->getMessage(); // the propagated error string, e.g. '#REF!'
}

When it happens

Trigger: =VALUE(A1) or =NUMBERVALUE(A1) where A1 contains or evaluates to an error value (for example a #VALUE! or #REF! produced by another formula); =VALUE("#N/A") with a literal error string; calling Format::VALUE('#VALUE!') directly, which throws Calculation\Exception instead of returning the string.

Common situations: Chained conversions where an earlier stage left error markers in text columns; imported files in which errors were saved as literal '#...' strings; ETL flows that try to normalize mixed columns with VALUE() and hit residue error text.

Related errors

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

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