Sum array by group (quaters, years other) requires extra argument.
I want to group (sum) a dynamic array. The excel sheet attached has 2 lambda functions. The lambda in cell I5 correctly groups the dynamic array. The lambda in cell H5 does not. What is wrong with th...
Gary1234 While I can't provide a complete explanation, it seems that BYROW behaves differently when used on an array than it does with a range. Specifically, it appears the issue is caused by passing the entire row parameter i to each subsequent calculation, even though the array only contains a single column. The solution in this case is to define the iMax variable first as INDEX(i, 1), then use iMax instead of i for the remaining calculations:
=LAMBDA(Amount,Date,Count, LET( Seq, SEQUENCE(ROWS(Amount)), BYROW(Seq, LAMBDA(i, LET(iMax, INDEX(i, 1), iMin, IF((iMax - Count + 1) > 0, iMax - Count + 1, 1), SUM(INDEX(Amount, SEQUENCE(iMax - iMin + 1, 1, iMin)))))) ) )(C5:C28, B5#, E2)The function, as presented, could also be rewritten and simplified as follows:
=LAMBDA(Amount,Count, BYROW(SEQUENCE(ROWS(Amount)), LAMBDA(r, LET( n, INDEX(r, 1), m, MAX(n-Count+1, 1), SUM(INDEX(Amount, SEQUENCE(n-m+1,, m)))))))(C5:C28, E2)Having said that, the function does not actually sum the data by group. Rather, it's summing the previous n rows in the range. If this is the expected result, great. If not, then another approach is needed. Cheers!
