Dec 29 2023 04:19 PM - edited Dec 29 2023 04:49 PM
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
Dec 29 2023 07:56 PM
Solution@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!
Dec 29 2023 10:03 PM
Jan 06 2024 04:04 PM
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
Dec 29 2023 07:56 PM
Solution@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!