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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

MathTrig\Lcm::Lcm() walks its arguments, skips nulls, and only throws at the end via testNonNulls() with ExcelError::VALUE() when not a single non-null argument was found. This mirrors Excel's LCM(): with no usable numeric values the result is #VALUE!, not 0. Strings that fail numeric validation also surface as #VALUE!, but this specific throw means every argument was null.

Source

Thrown at src/PhpSpreadsheet/Calculation/MathTrig/Lcm.php:116

     * @param mixed[] $myPoweredFactors
     */
    private static function processPoweredFactors(array &$allPoweredFactors, array &$myPoweredFactors): void
    {
        foreach ($myPoweredFactors as $myPoweredValue => $myPoweredFactor) {
            if (isset($allPoweredFactors[$myPoweredValue])) {
                if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) {
                    $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
                }
            } else {
                $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
            }
        }
    }

    private static function testNonNulls(int $anyNonNulls): void
    {
        if (!$anyNonNulls) {
            throw new Exception(ExcelError::VALUE());
        }
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Ensure at least one numeric argument reaches LCM; filter blanks before building the formula
  2. Guard the formula: =IF(COUNT(A1:A3)=0, "", LCM(A1:A3))
  3. Pass explicit numbers instead of relying on empty optional cells
  4. Catch Calculation\Exception if you invoke Lcm::Lcm() directly

Example fix

// before
$sheet->getCell('B1')->setValue('=LCM(A1:A3)'); // all blank -> #VALUE!

// after
$sheet->getCell('B1')->setValue('=IF(COUNT(A1:A3)=0, 0, LCM(A1:A3))');
Defensive patterns

Strategy: validation

Validate before calling

$args = array_values(array_filter($args, fn ($a) => $a !== null && is_numeric($a)));
if ($args === []) {
    return 0; // or '#VALUE!' policy result, instead of calling Lcm::Lcm()
}
return Lcm::Lcm(...$args);

Type guard

function hasAtLeastOneNonNull(array $args): bool
{
    return count(array_filter($args, fn ($a) => $a !== null)) > 0;
}

Try / catch

try {
    $lcm = \PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Lcm::Lcm(...$args);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    $lcm = $e->getMessage(); // '#VALUE!'
}

Prevention

When it happens

Trigger: =LCM() over a range where all cells are empty, e.g. =LCM(A1:A3) with blanks; =LCM("") or references that all resolve to null; calling Lcm::Lcm(null, null) directly from PHP.

Common situations: Ranges whose cells are only populated conditionally (report templates, optional columns) so LCM receives only blanks; formulas copied down a sheet where some rows have no data; wrappers that forward optional user input straight into LCM without defaults.

Related errors

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