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))