Why does SCAN/LAMBDA give unexpected results?

Question

It is very possible that I dont understand the lambda logic or do I? I have dataset A2:A5 like:

If I do: =SCAN(0, A2:A5, LAMBDA(aa, bb, aa+bb)) i get:

If I do: =SCAN(0, A2:A5, LAMBDA(aa, bb, ROW(bb)-1)) I get

if I run: =SCAN(0, A2:A5, LAMBDA(aa, bb, (aa+bb)*(ROW(bb)-1))) the result is

Why there is 42 and 208 ? How this results in such values? How can it be 42 and 208 ?

Expected result is

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

You need to understand how REDUCE operates to see how SCAN does. — TheMaster
– TheMaster, Commented Oct 1, 2022 at 17:07
For respondants, touch on this quoting the OP And I can get it with: =ArrayFormula( ... But not with =SCAN highlight the diffrance, please. — Osm
– Osm, Commented Oct 1, 2022 at 17:43

TheMaster · Accepted Answer · 2022-10-01 17:23:00Z

2

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

edited Oct 1, 2022 at 17:23

answered Oct 1, 2022 at 17:16

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Sharpe · Accepted Answer · 2022-10-01 19:06:42Z

2

aa stores the result of the previous calculation, so you have:

edited Oct 1, 2022 at 19:06

answered Oct 1, 2022 at 17:16

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Comments

player0 · Accepted Answer · 2022-10-01 18:29:34Z

1

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

edited Oct 1, 2022 at 18:29

answered Oct 1, 2022 at 18:20

player0

131k14 gold badges91 silver badges149 bronze badges

1 Comment

Osm Over a year ago

ROW is outside SCAN this is the key.

Collectives™ on Stack Overflow

Why does SCAN/LAMBDA give unexpected results?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related