Forum Discussion
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 the lambda in cell H5? I have to provide the sequence as an additional argument instead of defining in the lambda function. What am I doing wrong or is this a bug?
Below is the spreadsheet. I will attempt to attach also.
| Months | First Month | Count | ||||||
| 24 | 1/1/2024 | 3 | ||||||
| Not working | Working | |||||||
| Sequence | Date | Amount | Min | Max | Total | Total | ||
| 1 | 1/1/2024 | $9.73 | 1 | 1 | $9.73 | $9.73 | ||
| 2 | 2/1/2024 | $48.75 | 1 | 2 | $9.73 | $58.48 | ||
| 3 | 3/1/2024 | $22.63 | 1 | 3 | $9.73 | $81.11 | ||
| 4 | 4/1/2024 | $49.99 | 2 | 4 | $48.75 | $121.37 | ||
| 5 | 5/1/2024 | $33.92 | 3 | 5 | $22.63 | $106.54 | ||
| 6 | 6/1/2024 | $2.88 | 4 | 6 | $49.99 | $86.79 | ||
| 7 | 7/1/2024 | $92.52 | 5 | 7 | $33.92 | $129.32 | ||
| 8 | 8/1/2024 | $65.84 | 6 | 8 | $2.88 | $161.24 | ||
| 9 | 9/1/2024 | $98.41 | 7 | 9 | $92.52 | $256.77 | ||
| 10 | 10/1/2024 | $17.60 | 8 | 10 | $65.84 | $181.85 | ||
| 11 | 11/1/2024 | $69.09 | 9 | 11 | $98.41 | $185.10 | ||
| 12 | 12/1/2024 | $32.65 | 10 | 12 | $17.60 | $119.34 | ||
| 13 | 1/1/2025 | $32.14 | 11 | 13 | $69.09 | $133.88 | ||
| 14 | 2/1/2025 | $31.93 | 12 | 14 | $32.65 | $96.72 | ||
| 15 | 3/1/2025 | $80.12 | 13 | 15 | $32.14 | $144.19 | ||
| 16 | 4/1/2025 | $60.28 | 14 | 16 | $31.93 | $172.33 | ||
| 17 | 5/1/2025 | $10.58 | 15 | 17 | $80.12 | $150.98 | ||
| 18 | 6/1/2025 | $30.34 | 16 | 18 | $60.28 | $101.20 | ||
| 19 | 7/1/2025 | $51.31 | 17 | 19 | $10.58 | $92.23 | ||
| 20 | 8/1/2025 | $5.46 | 18 | 20 | $30.34 | $87.11 | ||
| 21 | 9/1/2025 | $43.61 | 19 | 21 | $51.31 | $100.38 | ||
| 22 | 10/1/2025 | $7.27 | 20 | 22 | $5.46 | $56.34 | ||
| 23 | 11/1/2025 | $89.27 | 21 | 23 | $43.61 | $140.15 | ||
| 24 | 12/1/2025 | $78.49 | 22 | 24 | $7.27 | $175.03 |
The working lambda function in cell I5 is:
The non-working lambda function in cell H5 is:
Thanks in advance.
Gary
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!
3 Replies
I am posting the solution to get a 'rolling' sum of an array for others that may search for it later.
The solution uses the excel function scan to define a named function (lambda) named Rolling Sum.
The function is defined as:
LAMBDA(Values,Reset,
SCAN(0, SEQUENCE(ROWS(Values)),
LAMBDA(a,s, IF(INDEX(Reset, s, 1), 0, a) + INDEX(Values, s, 1)))
)The 2 parameters are dynamic arrays (or named ranges) of a single column. The first is the array containing the numbers to compute the rolling sum. The second parameter is an array that specifies when the rolling sum is to be reset. I have attached the updated example.
Thanks for the help
G
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!
- djclements
Thank you for finding the problem (and for the simplified solution. I had tried the MAX function but had a problem. I think that was due to the error you found. Thanks again.
I was looking for the previous n rows. That is what I defined as the group. Thanks for making sure.
Gary
