Issue
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)))
Solution
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))
Answered By - player0 Answer Checked By - Senaida (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.