Home > Back-end >  Why does SCAN/LAMBDA give unexpected results?
Why does SCAN/LAMBDA give unexpected results?

Time:10-02

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:

enter image description here

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