Defined names in aggregate calculations

HotXLS documentation · Text and statistical calculated-array arguments

SUM, AVERAGE, MIN, MAX, COUNT and COUNTA take supported scalar and calculated-array defined names alongside worksheet references

Scalar and array values

A name evaluating to a direct scalar follows scalar coercion: numeric text and Boolean values can contribute to a numeric reducer, whereas a name evaluating to an array follows array coercion and leaves text and Boolean members out of numeric collection

For a defined name Numbers whose expression is SEQUENCE(3), SUM(Numbers) gives 6 and AVERAGE(Numbers) gives 2; a scalar Boolean name can contribute 1, whereas an array holding Boolean values excludes those members from the numeric reducers

Numeric reducers propagate typed errors, COUNT skips error values and COUNTA counts nonempty error values; AVERAGE with nothing collected returns a typed #DIV/0! error

Reference identity

Names resolving to actual worksheet references keep reference traversal and reference coercion, SUBTOTAL hidden-row and nested-subtotal rules included; reference-producing INDEX expressions carry on with their existing behaviour

A failed reference traversal does not restart the name as a value operand, so a cell error occurring after partially collected numeric values can neither mask the error nor count the preceding cells twice

RANK, RANK.EQ and RANK.AVG still demand a true reference for their reference argument; a computed array name gains no reference identity merely because other reducers accept it

Dependencies and limits

Calculated names take part in the existing dependency and circular-reference checks; absolute nested names follow source changes and array resizing, whilst relative names resolve separately at each consumer position

Flattened value collection consumes evaluation steps and checks the array memory limits, copied string payloads included; a resource-limit failure remains a calculation error

The existing external name and external reference semantics stay separate from this local calculated-name extension; unknown names keep the existing public parser error contract