What causes "AND Value is only appropriate for range operators" in PHPOffice/PhpSpreadsheet?

Use between(5)->and(10) or notBetween(5)->and(10) for range checks

PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

AND Value is only appropriate for range operators

Error message

AND Value is only appropriate for range operators

What it means

In the CellValue wizard, and() supplies the second bound of a two-sided comparison, so it is only legal after a range operator - between() or notBetween(). The wizard tracks the currently selected operator; calling and() while a single-sided operator (equal, greaterThan, lessThan, ...) is active throws because a second operand would be meaningless. A fresh CellValue wizard defaults to the 'equal' operator, so calling and() before any operator call also throws.

Source

Thrown at src/PhpSpreadsheet/Style/ConditionalFormatting/Wizard/CellValue.php:164

                    $condition = self::reverseAdjustCellRef($condition, $cellRange);
                } else {
                    $condition = self::unwrapString($condition);
                }
            }
            $wizard->operand($index, $condition, $operandValueType);
        }

        return $wizard;
    }

    /**
     * @param mixed[] $arguments
     */
    public function __call(string $methodName, array $arguments): self
    {
        if ($methodName === 'and') {
            if (!isset(self::RANGE_OPERATORS[$this->operator])) {
                throw new Exception('AND Value is only appropriate for range operators');
            }

            $this->operand(1, ...$arguments);

            return $this;
        }

        if (!isset(self::MAGIC_OPERATIONS[$methodName])) {
            throw new Exception('Invalid Operator for Cell Value CF Rule Wizard');
        }

        $this->operator(self::MAGIC_OPERATIONS[$methodName]);
        //$this->operand(0, ...$arguments);
        if (count($arguments) < 2) {
            $this->operand(0, $arguments[0]);
        } else {
            /** @var string */
            $arg1 = $arguments[1];

View on GitHub (pinned to 65b080eef4)

Solutions

Use between(5)->and(10) or notBetween(5)->and(10) for range checks
For 'greater than 5 and less than 10' logic, express it as between with adjusted bounds, or build an Expression rule with an AND(...) formula
Never call and() unless the immediately preceding operator call was between()/notBetween()
Catch the exception when operator chains are assembled dynamically

Example fix

// before
$wizard = (new Wizard('A1:E10'))->newRule(Wizard::CELL_VALUE);
$wizard->greaterThan(5)->and(10);   // throws: greaterThan is not a range operator

// after
$wizard = (new Wizard('A1:E10'))->newRule(Wizard::CELL_VALUE);
$wizard->between(5)->and(10);       // between ... and ...

Defensive patterns

Strategy: try-catch

Try / catch

use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;

try {
    $wizard->between($min)->and($max);
} catch (PhpSpreadsheetException $e) {
    // operator was not between/notBetween when and() ran
    $wizard = (new Wizard($range))->newRule(Wizard::CELL_VALUE);
    $wizard->between($min)->and($max);
}

Prevention

Only call and() immediately after between() or notBetween()
Express 'x > a and x < b' as between(a)->and(b), not greaterThan + and
Remember a fresh CellValue wizard defaults to 'equal', so a leading and() always throws
When conditions are assembled dynamically, encode operator/operand pairs as data and expand them in fixed order

When it happens

Trigger: Chaining ->greaterThan(5)->and(10); calling ->and($x) before any operator (default operator is equal, not a range operator); translating SQL 'BETWEEN ... AND ...' habits onto non-between operators.

Common situations: Developers expressing 'value > 5 and value < 10' who reach for greaterThan + and instead of between; refactoring conditions where the operator changed but the and() call remained; conditions assembled dynamically from fragments.

Related errors

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