Defined names in aggregate calculations

HotXLS documentation · Text and statistical calculated-array arguments

SUM, AVERAGE, MIN, MAX, COUNT and COUNTA accept supported scalar and calculated-array defined names in addition to worksheet references

Scalar and array values

A name that evaluates to a direct scalar follows scalar coercion: numeric text and Boolean values can contribute to a numeric reducer, while a name that evaluates to an array follows array coercion and excludes text and Boolean members from numeric collection

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

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

Reference identity

Names that resolve to actual worksheet references retain reference traversal and reference coercion, including SUBTOTAL hidden-row and nested-subtotal rules; reference-producing INDEX expressions continue to retain their existing behavior

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

RANK, RANK.EQ and RANK.AVG still require a true reference for their reference argument; a computed array name does not acquire reference identity merely because other reducers accept it

Dependencies and limits

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

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

Existing external name and external reference semantics remain separate from this local calculated-name extension; unknown names retain the existing public parser error contract