Forum Discussion
Another spilled array that I can't solve - Aggregating monthly into time periods
An interesting set of data. I though of unpivoting it first before proceeding but found it more straightforward to use MAKEARRAY.
=LET( i, ROWS(data), j, COLUMNS(data), GetTotals, LAMBDA(r, c, LET( current_row, CHOOSEROWS(data, r), each_period, INDEX(periods, r), start, IFNA(XMATCH(TRUE, current_row <> 0, 1), 0) + each_period - 1, k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0), seq₁, SEQUENCE(k, , start, each_period), seq₂, SEQUENCE(each_period, , c, -1), total, SUM(INDEX(data, r, seq₂)), check, XOR(seq₁ = c), result, IF(check, total, 0), IFERROR(result, 0) ) ), MAKEARRAY(i, j, GetTotals) )
An interesting set of data. I though of unpivoting it first before proceeding but found it more straightforward to use MAKEARRAY.
=LET(
i, ROWS(data),
j, COLUMNS(data),
GetTotals, LAMBDA(r, c,
LET(
current_row, CHOOSEROWS(data, r),
each_period, INDEX(periods, r),
start, IFNA(XMATCH(TRUE, current_row <> 0, 1), 0) + each_period - 1,
k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0),
seq₁, SEQUENCE(k, , start, each_period),
seq₂, SEQUENCE(each_period, , c, -1),
total, SUM(INDEX(data, r, seq₂)),
check, XOR(seq₁ = c),
result, IF(check, total, 0),
IFERROR(result, 0)
)
),
MAKEARRAY(i, j, GetTotals)
)I implemented this fully and applied to to all my overhead sections where it worked flawlessly. But when I applied it to the revenue section which is basically identical, it worked on some sections and not on others. I have included a full example of a part with the 'data' section and the implemented MAKEARRY. I can't see why it would not work but it seems to leave out a large qty of values. I can't really see any key differences between the application in the overheads vs the revenues and have triple checked the refs and everything else
I have included 2 versions using my data - the full set in the attached file. One with my tweaked function - basically just broken into rows and with the data and period inputs separated out. And one with your original just with ranges applied. In my original all the inputs are actually DAsYou will see how it misses a bunch of entries from the data section.
But I just can't see any variance in the set up of the sections that work and those that don't.
Also. what I love about the solution is that it takes only 2 inputs, the source data array and the array of applied periods. SO no other inputs are interfering with it. So it must be something to do with the shape of the source data
It looks like it was missing because of the XMATCH which was returning the wrong number with the approximate match.
The match part should simply look for first non-zero value to see where values begin in a row.
XMATCH(TRUE, current_row <> 0)Entire formula:
=LET( i, ROWS(H5:CM14), j, COLUMNS(H5:CM14), GetTotals, LAMBDA(r, c, LET( current_row, CHOOSEROWS(H5:CM14, r), each_period, INDEX(D18:D27, r), start, IFNA(XMATCH(TRUE, current_row <> 0), 0) + each_period - 1, k, ROUNDUP(SUM(N(current_row <> 0)) / each_period, 0), seq₁, SEQUENCE(k, , start, each_period), seq₂, SEQUENCE(each_period, , c, -1), total, SUM(INDEX(H5:CM14, r, seq₂)), check, XOR(seq₁ = c), result, IF(check, total, 0), IFERROR(result, 0) ) ), MAKEARRAY(i, j, GetTotals) )Additionally, you can simply your totals column with some eta:
=BYROW(H18#,SUM)- Thanks, but Enterprise versions don't yet have that yet so I'm sticking with the clumsier version of byrow for now. Groupby and pivotby also haven't rolled out to enterprise 365!
Brilliant. Works like a dream. I will test it more and study it> I have seen to power of Makearray but never really been able to harness it. I know I will learn a lot from this and am very grateful for your amazing answer. Thanks hugely
Been working out of a third party office with no external access for the past 2 days so only now managing to review and respond.Just tested more and there is a slight hitch. See follow on post
