It is very possible that I dont understand the lambda logic or do I? I have dataset A2:A5 like:
1
3
6
10
If I do: =SCAN(0, A2:A5, LAMBDA(aa, bb, aa bb)) i get:
1
4
10
20
If I do: =SCAN(0, A2:A5, LAMBDA(aa, bb, ROW(bb)-1)) I get
1
2
3
4
if I run: =SCAN(0, A2:A5, LAMBDA(aa, bb, (aa bb)*(ROW(bb)-1))) the result is
1
8
42
208
Why there is 42 and 208 ? How this results in such values? How can it be 42 and 208 ?
Expected result is
1
8
30
80
And I can get it with:
=ArrayFormula(SCAN(0, A2:A5, LAMBDA(aa, bb, aa bb))*(ROW(A2:A5)-1))
But not with
=SCAN(0, A2:A5, LAMBDA(aa, bb, (aa bb)*(ROW(bb)-1)))
CodePudding user response：
SCAN is a great intermediate results function. To understand how SCAN operates, you need to understand how REDUCE operates. The syntax is:
=REDUCE(initial_value, array, LAMBDA(accumulator, current_value, some_function()))
Going through =SCAN(0, A2:A5, LAMBDA(aa, bb, (aa bb)*(ROW(bb)-1))) step by step,
A2:A5 is 1,3,6,10
Step 1:
aa= 0(initial_value)bb= 1(current_value:A2)- Result(
(aa bb)*(ROW(bb)-1)): (0 1)*(2-1)=1
Step 2:
aa= 1(accumulator(previous return value))bb= 3(current_value:A3)- Result(
(aa bb)*(ROW(bb)-1)): (1 3)*(3-1)=8
Step 3:
aa= 8(accumulator(previous return value))bb= 6(current_value:A4)- Result(
(aa bb)*(ROW(bb)-1)): (8 6)*(4-1)=42
Step 4:
aa= 42(accumulator(previous return value))bb= 10(current_value:A5)- Result(
(aa bb)*(ROW(bb)-1)): (42 10)*(5-1)=52*4=208
CodePudding user response：
aa stores the result of the previous calculation, so you have:
CodePudding user response：
above answers pretty much contain all so I will add only this:
you probably expected that by doing (aa bb)*(ROW(bb)-1) you will get:
| (aa bb) | * | (ROW(bb)-1) | ||
|---|---|---|---|---|
| 1 | * | 1 | = | 1 |
| 4 | * | 2 | = | 8 |
| 10 | * | 3 | = | 30 |
| 20 | * | 4 | = | 80 |
but that's not how it works. to get your expected result and by not using your formula where ROW is outside of SCAN:
=ArrayFormula(SCAN(0, A2:A5, LAMBDA(aa, bb, aa bb))*(ROW(A2:A5)-1))
you would need to do:
=INDEX(MAP(SCAN(0, A2:A5, LAMBDA(aa, bb, (aa bb))), ROW(A2:A5)-1, LAMBDA(cc, dd, cc*dd)))
where cc is the entire SCAN and dd is ROW(A2:A5)-1 eg. first do the running total and then multiplication, which is not so feasible length-wise.
or shorter but with SEQUENCE:
=MAP(SCAN(0, A2:A5, LAMBDA(aa, bb, (aa bb))), SEQUENCE(4), LAMBDA(cc, dd, cc*dd))