[FIXED] Why does SCAN/LAMBDA give unexpected results?

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)