Aggregate 2D arrays by row or by column (especially relevant to dynamic arrays)
Assuming the new dynamic array functionality receives widespread usage, one might expect to see more in the way of 2D arrays. Traditionally aggregation over rows (say) to give a column of row sums (or row minima, Booleans, text joins etc.) would be handled by relative referencing but this is not consistent with the concept of dynamic arrays.
My suggestion is to introduce functions BYROW and BYCOLUMN (and, possibly, BYCELL) that would cause aggregation operations to treat the 2D array as a collection of rows/columns/individual cells. The operation should return an array of results.
Example
= AND( BYROW( CriterionRange = Criterion ) )
should return TRUE/FALSE for every row of a spilt array.
A completely different aggregation might then be applied to the resulting column vector.
Peter Bartholomew
shared this idea
1 comment
-
Peter Bartholomew
commented
Now as I have settled in to using dynamic arrays, I find that this limitation affects about 50% of the models I build. The observable symptoms are blocks of 'fill-down' formulas involving @k#
[k being a defined name applied to the anchor cell of a spilt array].Even worse is
= INDEX( twoDArray#, @k#, 0 )
[defining a row of the array]
The functions then applied to such objects might be simple aggregations or functions such as FILTER and UNIQUE.The formula needs to be applied for every value of 'k#', hence the fill, but there may be no way of telling in advance how many terms there are within the array. This makes how far to fill a matter of guesswork.