SUMCOLS and SUMROWS functions for Dynamic Arrays
Create new functions that SUM each column in a dynamic array (SUMCOLS(Ref#)) to produce a row of totals, and SUM each row to produce a column of totals (SUMROWS(Ref#))
Also COUNTCOLS, COUNTROWS, AVERAGECOLS, AVERAGEROWS, but these are less important than SUMCOLS & SUMROWS.
Charles Williams
shared this idea
11 comments
-
Charles Williams
commented
@Harlan Grove ,
SUMCOLS and SUMROWS etc are not floating totals, they are static so your are correct: it would make no sense to position them below or to the right of a dynamic array.. (I also wrote a TOTALS function which IS dynamic if floating totals are wanted)
With dynamic arrays the syntax B5# returns the spilled dynamic array whose top left corner is B5
The problem with filling a formula right or down is that it does not expand/contract with the dynamic array: thus defeating the purpose of the dynamic arryay.
. It is often possible to construct expanding/contracting formulas using things like MMULT, but the resulting formula is very ugly and unintelligible to the average user.
-
Peter Bartholomew
commented
@Kevin Osborn
"dynamic array function inside a table"I think that confuses the nature of a table and an array.
A table is a list of records, each of which is a similar heterogeneous collection of fields in which data is referenced by keyword.
On the other hand an array is an ordered list which should be homogeneous and is referenced by index (or a combination of indices).That leads me to suggest that a total is not part of the array and may logically be placed anywhere one chooses within the workbook; there is no requirement that it should align with the source data table.
If there happen to be text headers that you wish to use to identify a column then
= XLOOKUP( "Column2", Table1[#Headers], Table1 )
would return the column as a range reference.You may not have much use for arrays, as distinct from tables, but others do and I suggest the proposal is worth a vote; some such functionality is essential.
-
Harlan Grove
commented
With new dynamic arrays, does it really make sense to have total rows and columns below or to the right, respectively, any longer? There's always been good reason to put such totals above or to the left of the range over which they'd evaluate, and in the coming era of dynamic arrays and spilled formulas, above/to the left seem to have become the only sensible way to do this.
That said, do we really need B3:Z3: =SUMCOLS(B5:Z999) rather than B3: =SUM(B5:B999) filled right into C3:Z3?
If spilled formulas could truly be unpredictable, so that the largest extent in terms of rows and columns really isn't known, would it ever make sense to put ANYTHING to the right or below any formula returning a variable-size array result?
I had to learn some PL/I way, way back, and I'd prefer Excel didn't copy the approach of adding anything anyone could think of to it. Minimalism may suggest a 2-step approach, e.g., one new function which returns the full array extent of the results given by the top-left cell in its single reference argument or the largest single area range containing the same R1C1 formula as the top-left cell in its single reference argument. For this sort of thing, GETRANGE(B5) which would return a reference to B5:Z999, and that could be chopped up using INDEX(GETRANGE(B5),0,COLUMNS($B$3:D3)) for the sum of column D within the result range.
-
Charles Williams
commented
@Kevin,
Would be nice to address your DA Table problem, but that would not solve the problem for general DA totals.
-
Kevin Osborn
commented
I understand where you are going with this but I'm not going to vote it up. IMO a better solution is to allow 1 dynamic array function inside a table. Not only will this address your issue with sum/count/avg for columns because tables can have a "total" row but it will also fix the deficiency in dynamic arrays whereby structured/table reference cannot be used to reference a column in a dynamic array so the developer is forced to revert back to column numbers. Similarly instead of having sum, count, average rows I'd rather see a "Row Total" added to tables (similar to column totals).
-
Peter Bartholomew
commented
Hi Charles
I fully support this initiative. I had posted a related idea but I am not too fussed about the implementation provided the functionality is there.
My aim is not simply to use DA here and there but to build dynamic solutions from the ground up using nothing but dynamic arrays. At the moment my solutions are full of matrix multiplication (MMULT) to perform aggregations by row and by column and to accumulate arrays in either direction.
There is no chance of rolling such methods out across the Excel community, so a suite of new functions is essential to the success of DA.
It is such a relief to move away from manual knife-and-fork selection of cells and rows for calculation (more accurately selection by mouse and keyboard shortcuts) and let the calculation logic speak for itself based upon references to entire arrays and tables.
-
Jon Acampora
commented
Great idea!
-
Roger Govier
commented
Excellent idea Charles and I have tested your own functions that you have already written which work perfectly.
However, I think it would be even more useful if there was a way to pass an optional array to the function to excluded certain columns otherwise you end up summing dates and unit prices etc., which is neither use to man nor beast.
e.g. =SUMCOLS(E7#,{1:6,9}) which would exclude summing the first 6 columns and the 9th for example. -
BetterSolutions.com
commented
Great Suggestion
-
Jeff
commented
Definitely needed! MMULT is not intuitive or easy for the average user
-
Wyn Hopkins
commented
Essential to really take full advantage of Dynamic Arrays