{"record":{"id":"b5e85aad213da9ac","repo":"PHPOffice/PhpSpreadsheet","slug":"must-specify-range-of-cells-not-any-kind-of-liter","errorCode":null,"errorMessage":"Must specify range of cells, not any kind of literal","messagePattern":"Must specify range of cells, not any kind of literal","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/Statistical/Conditional.php","lineNumber":44,"sourceCode":"     * Excel Function:\n     *        AVERAGEIF(range,condition[, average_range])\n     *\n     * @param mixed $range Data values, expect array\n     * @param mixed $condition the criteria that defines which cells will be checked, expect null|mixed[]|string\n     * @param mixed $averageRange Data values\n     */\n    public static function AVERAGEIF(mixed $range, mixed $condition, mixed $averageRange = []): null|int|float|string\n    {\n        if ($condition !== null && !is_array($condition)) {\n            $condition = StringHelper::convertToString($condition);\n        }\n        if (!is_array($range) || !is_array($averageRange) || array_key_exists(0, $range) || array_key_exists(0, $averageRange)) {\n            $refError = ExcelError::REF();\n            if (in_array($refError, [$range, $averageRange], true)) {\n                return $refError;\n            }\n\n            throw new CalcException('Must specify range of cells, not any kind of literal');\n        }\n        $database = self::databaseFromRangeAndValue($range, $averageRange);\n        $condition = Functions::flattenSingleValue($condition);\n        $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];\n\n        return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);\n    }\n\n    /**\n     * AVERAGEIFS.\n     *\n     * Counts the number of cells that contain numbers within the list of arguments\n     *\n     * Excel Function:\n     *        AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)\n     *\n     * @param mixed $args Pairs of Ranges and Criteria\n     */","sourceCodeStart":26,"sourceCodeEnd":62,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php#L26-L62","documentation":"Statistical\\Conditional::AVERAGEIF() requires its $range and $averageRange to be real cell-range arrays keyed by coordinate (e.g. ['A1'=>'x', ...]). If either is not an array, or is a flat list with key 0 (how literal arrays arrive from the formula parser), it throws CalcException 'Must specify range of cells, not any kind of literal'. A #REF! error value in either position is returned instead of thrown. This is a hard PHP exception, not an Excel error string, so through the engine it aborts formula evaluation.","triggerScenarios":"=AVERAGEIF({1,2,3},\">1\") using an array literal; =AVERAGEIF(5,\">1\") with a scalar; a range reference that collapses to a single row literal during parsing; calling Conditional::AVERAGEIF([10,20],'>10') directly from PHP with a list array instead of a coordinate-keyed map.","commonSituations":"Converting SUMIF-style formulas to AVERAGEIF while keeping inline literals; generated formulas that substitute a PHP array or comma-joined value where a range reference is required; spreadsheets authored in other tools that export criteria functions with literal arrays; calling the Conditional class directly in tests or libraries without simulating engine-processed ranges.","solutions":["Replace the literal with an actual range reference: =AVERAGEIF(A1:A10,\">1\",B1:B10)","If the data lives in PHP, write it into cells first and reference that range in the formula","When calling the API directly, build the range argument the way the engine does: coordinate-keyed arrays (['A1'=>v,...]) not flat lists","Check for #REF! inputs first if ranges may reference deleted areas (they are returned, not thrown)"],"exampleFix":"// before: literal array -> CalcException\n$sheet->getCell('D1')->setValue('=AVERAGEIF({10,20,30},\">15\")');\n\n// after: values in cells, range reference used\n$sheet->fromArray([[10],[20],[30]], null, 'A1');\n$sheet->getCell('D1')->setValue('=AVERAGEIF(A1:A3,\">15\")');","handlingStrategy":"type-guard","validationCode":"// Ranges coming from the engine are coordinate-keyed maps, never flat lists\nfunction isRangeArray(mixed $range): bool\n{\n    return is_array($range) && !array_key_exists(0, $range) && $range !== [];\n}\nif (!isRangeArray($range) || !isRangeArray($averageRange)) {\n    throw new InvalidArgumentException('AVERAGEIF needs cell ranges, not literals');\n}","typeGuard":"function isCoordinateKeyedRange(mixed $r): bool\n{\n    if (!is_array($r) || array_key_exists(0, $r)) return false;\n    foreach (array_keys($r) as $k) { if (!preg_match('/^[A-Z]{1,3}\\d+$/', (string) $k)) return false; }\n    return true;\n}","tryCatchPattern":"try {\n    $avg = Conditional::AVERAGEIF($range, $condition, $averageRange);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Calculation\\Exception $e) {\n    if (str_contains($e->getMessage(), 'Must specify range of cells')) {\n        throw new InvalidArgumentException('pass a real range: =AVERAGEIF(A1:A10,\">1\",B1:B10)');\n    }\n    throw $e;\n}","preventionTips":["Always author AVERAGEIF with range references, never literals or scalars","Materialize PHP arrays into cells (fromArray) and reference that range","Remember the 3-arg AVERAGEIFS form re-dispatches to AVERAGEIF with the same rule","Check for #REF! inputs first - they are returned, not thrown"],"tags":["phpspreadsheet","excel-formula","averageif","conditional","literal-argument","calc-exception"],"backgroundTag":"literal-instead-of-range","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}