Skip to content
Apiary

Formula types and array semantics

A spreadsheet file states, for each formula cell, how the formula was entered, and Excel evaluates it accordingly. This page describes the three stored forms, how Apiary represents and evaluates them, and where Apiary differs from Excel.

The three stored forms

Section titled “The three stored forms”

In an XLSX file a formula cell holds one of:

Stored as Entered as Excel evaluates it
<f>SUM(A1:A3)</f> a plain formula by pre-dynamic-array rules (below); the result never spills
<f t="array" ref="B1:B3">…</f>, no cm Ctrl-Shift-Enter over a fixed range (CSE) by fitting the result to the stored range: broadcast, truncate, or pad with #N/A
<f t="array" ref="B1:B3">…</f> with cm="1" on the <c> normal entry in a version with dynamic arrays as a dynamic array: the result sizes its own spill, and collides with #SPILL!

So far as probes of its saved files show, Excel marks cm="1" on every formula whose dynamic-array reading differs from the pre-dynamic-array one, and on some where the two agree. A plain <f> whose two readings differ comes from Excel before dynamic arrays, from a later Excel where the formula was typed with a top-level @ (=@SEQUENCE(3) is saved as <f>_xlfn.SEQUENCE(3)</f>), or from another writer.

In the JSF that @borgar/xlsx-convert produces, the ref becomes the cell’s F, and a t="array" without cm also sets cse: true.

Excel’s rules for a plain formula

Section titled “Excel’s rules for a plain formula”

Excel evaluates each argument slot in scalar or array context. The top level is scalar context. Each function parameter has its own context, which does not follow from whether the parameter accepts an array: LOOKUP’s lookup_vector evaluates its argument in array context, so LOOKUP(2,1/(B1:B3>1),C1:C3) is “r”, but MATCH’s lookup_array and IFERROR’s value evaluate theirs in scalar context. A slot in scalar context stays so even inside array context. Then:

  • an array given to a slot that accepts no array is lifted element-wise, in either context;
  • a reference given to a slot that accepts no range is intersected with the formula’s row or column in scalar context, and read whole as an array in array context;
  • TYPE’s value follows the context it is in: SUMPRODUCT(TYPE(B1:B3)) is 64, where SUMPRODUCT(TRANSPOSE(B1:B3)) intersects its range and is #VALUE!, but TYPE(B1:B3) on its own is TYPE(@B1:B3), 16 outside the range’s rows;
  • ROW and COLUMN over a range yield their array only in array context; in scalar context they yield the first row or column number, so SUM(ROW(A5:A8)) is 5 at any row and SUMPRODUCT(ROW(A5:A8)) is 26, while the array result of INDEX, SEQUENCE or MUNIT is kept there (SUM(SEQUENCE(3)) is 6);
  • the formula’s own result is collapsed as in scalar context: a reference is intersected, an array yields its first element;
  • a defined name’s body evaluates array-wise (SUM(Dbl) with Dbl = A5:A7*2 is 12), but a name that holds a plain range is intersected like a range written out. The formula Dbl on its own yields the first element, 2.

Measured in Excel 16.114 and 16.115 for Mac.

How Apiary represents them

Section titled “How Apiary represents them”

A cell’s formula type is Cell.ft:

  • 'a': dynamic-array formula. It evaluates in array context, and an array result spills.
  • 'cse': Ctrl-Shift-Enter array formula over a fixed range. It evaluates in array context, and its result is fitted to the range.
  • 'plain': a plain formula loaded from a file. It evaluates by Excel’s rules for a plain formula (above).
  • absent or null: the default type, which a newly entered formula has. It evaluates as 'a' does, except that a one-cell result is a value, not a one-cell spill, so the cell has no F unless export adds one (below).

A workbook of type 'google-sheets' differs: no formula loaded into it is 'plain', since spilling formulas from Google Sheets can lack F, and a formula of the default type is a single-cell formula. In such a formula a range given to a parameter that takes a single value is intersected with the formula’s row or column, but inside SUMPRODUCT, INDEX, XLOOKUP and other functions that take arrays, ranges are read whole and arrays are lifted. An array result still spills.

What sets ft:

  • Loading JSF or CSF: 'a' for a formula cell that has F or a CSF ft of 'a', 'cse' for one with cse: true, and 'plain' for any other. In a 'google-sheets' workbook, or on a sheet with Google Sheets dummy values, such a formula has the default type instead, and one whose form indicates a Google array formula gets 'a'.
  • Workbook.editCell: an edit that sets the formula or value sets the default type, or the ft it passes; a style-only edit leaves ft as it was. In a 'google-sheets' workbook, an edited formula gets 'a' where it needs array evaluation. editCell refuses to create a CSE formula; Workbook.setArrayFormula does that.
  • Inserting, deleting and moving cells, copying a sheet, and Workbook.fill keep the formula’s type, as they do not enter the formula anew. Filling from a CSE range makes CSE copies: a whole range filled together is copied at its size, and any other cell of it fills as a one-cell CSE formula.

Export

Section titled “Export”

toJSF writes F for a cell that spilled and for any 'a' formula cell, and cse: true with F for a CSE formula. Outside a 'google-sheets' workbook, a formula of the default type that does not spill gets F where it needs array evaluation; otherwise it is saved as a plain formula, since the two readings agree. toXLSX writes <f t="array"> for a cell with F, and adds cm="1" unless the cell is marked cse. A plain formula never spills, so it is written back as a plain <f>.

toCSF writes ft: 'a' for an 'a' or CSE formula, and for a formula of the default type that needs array evaluation by the same guess. A spilled cell has F in either format, which loads as 'a'. CSF has no CSE form.

Where Apiary differs

Section titled “Where Apiary differs”

Whether a formula of the default type needs array evaluation is a guess from its form, which can miss. Such a formula evaluates correctly in the session, but if it does not spill it is saved as a plain formula, and so evaluates by the plain rules once reloaded.

A plain formula loaded from a file stays plain. Excel treats a loaded plain formula as newly entered when nothing in it can return more than one cell. So if a defined name that such a formula uses is then redefined to an array, Excel spills the formula, where Apiary returns a single value.