iOfficeAI/OfficeCLI · error · ArgumentException

arrayformula=true requires a formula: pass the text directly

Error message

arrayformula=true requires a formula: pass the text directly (arrayformula="B1:B3*C1:C3") or combine with formula=.

What it means

The arrayformula=true/1/yes tokens are flag intent ('make my formula= an array formula'), not formula text. Writing them verbatim replaced the real formula with the literal string 'true' — silent corruption. AddCell substitutes the companion formula= text (or the cell's existing CellFormula text); if neither exists there is nothing to convert, so it rejects clearly.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs:803

        {
            ApplyPhoneticToCell(cell, cellWorksheet, phoneticText, properties);
        }

        // Array formula support during Add
        if (properties.TryGetValue("arrayformula", out var arrFormula))
        {
            // arrayformula=true|1|yes is flag intent ("make my formula= an
            // array formula"), not formula text. Writing it verbatim replaced
            // the real formula with the literal string "true" — silent
            // corruption. Substitute the companion formula= text; without one
            // there is nothing to convert, so reject clearly.
            if (arrFormula.Equals("true", StringComparison.OrdinalIgnoreCase)
                || arrFormula == "1"
                || arrFormula.Equals("yes", StringComparison.OrdinalIgnoreCase))
            {
                arrFormula = properties.GetValueOrDefault("formula")
                    ?? cell.CellFormula?.Text
                    ?? throw new ArgumentException(
                        "arrayformula=true requires a formula: pass the text directly (arrayformula=\"B1:B3*C1:C3\") or combine with formula=.");
            }
            RejectCrossWorkbookFormula(arrFormula);
            ValidateFormulaCellRefs(arrFormula);
            // BUG-R36-B1: if ref was a range (A1:C3), use the full range as
            // arrRef so the array formula spills correctly; otherwise default
            // to the single cellRef.
            var arrRef = arrayFormulaRefRange ?? properties.GetValueOrDefault("ref", cellRef);
            // CONSISTENCY(value-child-uniqueness): drop any stale <is> placeholder
            // so the cell holds a single value child (invalid otherwise).
            cell.RemoveAllChildren<InlineString>();
            cell.CellFormula = new CellFormula(Core.PivotTableHelper.SanitizeXmlText(Core.ModernFunctionQualifier.Qualify(Core.ModernFunctionQualifier.AutoQuoteSheetRefs(arrFormula.TrimStart('=')))))
            {
                FormulaType = CellFormulaValues.Array,
                Reference = arrRef
            };
            EnsureFullCalcOnLoad(); // CONSISTENCY(cell-formula-calc)
            cell.CellValue = null;

View on GitHub (pinned to 1ced45e900)

Solutions

1. Pass the actual formula text in arrayformula= directly: arrayformula="B1:B3*C1:C3".
2. Or combine the flag with formula=: { arrayformula="true", formula="B1:B3*C1:C3" }.
3. Ensure the target cell already holds a formula if you intend the flag to convert an existing one.

Example fix

// before
handler.Add("/Sheet1/A1", "cell", null, new() { ["arrayformula"] = "true" });
// after
handler.Add("/Sheet1/A1", "cell", null, new() { ["arrayformula"] = "B1:B3*C1:C3" });

if (props.GetValueOrDefault("arrayformula")?.ToLowerInvariant() is "true" or "1" or "yes")
{
    if (!props.ContainsKey("formula"))
        throw new ArgumentException("arrayformula=true requires a companion formula=");
}

try { h.Add(parentPath, "cell", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("arrayformula=true requires a formula"))
{ /* supply formula text in arrayformula= directly and retry */ }

When it happens

Trigger: Add("/Sheet1/A1","cell",pos,{["arrayformula"]="true"}) with no formula= and no existing formula on the cell; arrayformula=1 or yes under the same conditions.

Common situations: Using arrayformula=true as if it were the formula text; forgetting to also pass formula=; expecting the flag to convert a formula that does not yet exist on the cell.

Related errors

• Invalid cell reference: '{cellRef}'
• Literal braces '{...}' around a formula create an Excel-reje
• Unrecognized cell parent path segment '{cellSegments[1]}'. E
• --prop shift={shiftVal} not valid for add cell. Use 'right'
• Cannot store '{properties.GetValueOrDefault("value") ?? prop

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/e16f34924e433ff4. Report an issue: GitHub.