SCANBYROW

tboulden · Sep 29, 2021

Performs a SCAN(initial_value,row,fn) on each row of array. Corresponding SCANBYCOL is also shown.

BYROW/BYCOL are limited in returning a single value per row/column respectively, whereas SCAN returns an array of the same dimensions as the array being SCANned. We can overcome this limitation by thunk-ing the array result: this is essentially wrapping with a LAMBDA with no parameters, i.e. LAMBDA(x). Then to get the thunked x out, we do LAMBDA(x)() = x.

Excel Formula:

=LAMBDA(initial_value,array,fn,
    LET(
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        row_thunks,
            BYROW(array,
                LAMBDA(row_,
                    LAMBDA(SCAN(initial_value,row_,fn))
                )
            ),
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(
                    INDEX(row_thunks,i,1)(),
                    1,
                    j
                )
            )
        )
    )
)

LAMBDA_SCANBY.xlsx

A

B

C

D

E

F

G

H

I

J

1

2

1

2

3

4

3

5

6

7

8

4

9

10

11

12

5

13

14

15

16

6

7

=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))

=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))

8

9

1

3

6

10

11

13

16

20

10

5

11

18

26

15

21

28

36

11

9

19

30

42

19

29

40

52

12

13

27

42

58

23

37

52

68

13

14

=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))

=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))

15

16

1

2

3

4

11

12

13

14

17

6

8

10

12

16

18

20

22

18

15

18

21

24

25

28

31

34

19

28

32

36

40

38

42

46

50

Sheet1

Cell Formulas
Range	Formula
B2:E5	B2	=SEQUENCE(4,4)
B7,G14,B14,G7	B7	=FORMULATEXT(B9)
B9:E12	B9	=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))
G9:J12	G9	=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))
B16:E19	B16	=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))
G16:J19	G16	=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))
Dynamic array formulas.

tboulden · Sep 29, 2021

SCANBYCOL

Excel Formula:

=LAMBDA(initial_value,array,fn,
    LET(
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        col_thunks,
            BYCOL(array,
                LAMBDA(col_,
                    LAMBDA(SCAN(initial_value,col_,fn))
                )
            ),
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(
                    INDEX(col_thunks,1,j)(),
                    i,
                    1
                )
            )
        )
    )
)

tboulden · Sep 30, 2021

Additional examples:

LAMBDA_SCANBY.xlsx

A

B

C

D

E

F

G

H

I

J

1

2

A

B

C

D

3

E

F

G

H

4

I

J

K

L

5

M

N

O

P

6

7

=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))

=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))

8

9

A

AB

ABC

ABCD

Test_A

Test_AB

Test_ABC

Test_ABCD

10

E

EF

EFG

EFGH

Test_E

Test_EF

Test_EFG

Test_EFGH

11

I

IJ

IJK

IJKL

Test_I

Test_IJ

Test_IJK

Test_IJKL

12

M

MN

MNO

MNOP

Test_M

Test_MN

Test_MNO

Test_MNOP

13

14

=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))

=SCANBYCOL("_Test",$B$2#, LAMBDA(acc,val,val&acc))

15

16

A

B

C

D

A_Test

B_Test

C_Test

D_Test

17

EA

FB

GC

HD

EA_Test

FB_Test

GC_Test

HD_Test

18

IEA

JFB

KGC

LHD

IEA_Test

JFB_Test

KGC_Test

LHD_Test

19

MIEA

NJFB

OKGC

PLHD

MIEA_Test

NJFB_Test

OKGC_Test

PLHD_Test

Alpha

Cell Formulas
Range	Formula
B2:E5	B2	=CHAR(SEQUENCE(4,4,65,1))
B7,G14,B14,G7	B7	=FORMULATEXT(B9)
B9:E12	B9	=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))
G9:J12	G9	=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))
B16:E19	B16	=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))
G16:J19	G16	=SCANBYCOL("_Test",$B$2#, LAMBDA(acc,val,val&acc))
Dynamic array formulas.

Functions below fairly self-explanatory, IDENTITY = LAMBDA(x,x), ADDONE = LAMBDA(x,x+1), etc.

LAMBDA_SCANBY.xlsx

A

B

C

D

E

F

G

H

I

1

COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))

2

3

CHOOSE functions array

4

5

6

3

6

IDENTITY

ADDONE

MINUSTHREE

TIMESTWO

5

2

4

6

18

MINUSTHREE

TIMESTWO

ADDTWO

TIMESTHREE

6

7

8

5

ADDTWO

IDENTITY

ADDONE

MINUSTHREE

7

15

12

24

TIMESTHREE

MINUSTHREE

IDENTITY

TIMESTWO

8

9

Composed across the row

10

IDENTITY

ADDONE(IDENTITY)

MINUSTHREE(ADDONE(IDENTITY))

TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))

11

MINUSTHREE

TIMESTWO(MINUSTHREE)

ADDTWO(TIMESTWO(MINUSTHREE))

TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))

12

ADDTWO

IDENTITY(ADDTWO)

ADDONE(IDENTITY(ADDTWO))

MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))

13

TIMESTHREE

MINUSTHREE(TIMESTHREE)

IDENTITY(MINUSTHREE(TIMESTHREE))

TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))

14

15

Composed down the column

16

5

6

2

10

IDENTITY

ADDONE

MINUSTHREE

TIMESTWO

17

2

12

4

30

MINUSTHREE(IDENTITY)

TIMESTWO(ADDONE)

ADDTWO(MINUSTHREE)

TIMESTHREE(TIMESTWO)

18

4

12

5

27

ADDTWO(MINUSTHREE(IDENTITY))

IDENTITY(TIMESTWO(ADDONE))

ADDONE(ADDTWO(MINUSTHREE))

MINUSTHREE(TIMESTHREE(TIMESTWO))

19

12

9

5

54

TIMESTHREE(ADDTWO(MINUSTHREE(IDENTITY)))

MINUSTHREE(IDENTITY(TIMESTWO(ADDONE)))

IDENTITY(ADDONE(ADDTWO(MINUSTHREE)))

TIMESTWO(MINUSTHREE(TIMESTHREE(TIMESTWO)))

COMPOSE

Cell Formulas
Range	Formula
A4:D7	A4	=MAP(SCANBYROW(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F4:I7	F4	=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13	F10	=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19	A16	=MAP(SCANBYCOL(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F16:I19	F16	=SCANBYCOL(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
Dynamic array formulas.

vincethesun · Feb 9, 2023

Hi tboulden,

These functions are absolutely amazing and useful and thanks for sharing. However, I've got an error from one of the excamples above when I copied it into a speadsheet. In fact, I first put the SCANBYROW, SCANBYCOL, COMPOSE functions into the name manager. Then I got this below:

SCANBYROW+SCANBYCOL.xlsx

A

B

C

D

E

F

G

H

I

1

COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))

2

3

CHOOSE functions array

4

#VALUE!

IDENTITY

ADDONE

MINUSTHREE

TIMESTWO

5

#VALUE!

MINUSTHREE

TIMESTWO

ADDTWO

TIMESTHREE

6

#VALUE!

ADDTWO

IDENTITY

ADDONE

MINUSTHREE

7

#VALUE!

TIMESTHREE

MINUSTHREE

IDENTITY

TIMESTWO

8

9

Composed across the row

10

IDENTITY

ADDONE(IDENTITY)

MINUSTHREE(ADDONE(IDENTITY))

TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))

11

MINUSTHREE

TIMESTWO(MINUSTHREE)

ADDTWO(TIMESTWO(MINUSTHREE))

TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))

12

ADDTWO

IDENTITY(ADDTWO)

ADDONE(IDENTITY(ADDTWO))

MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))

13

TIMESTHREE

MINUSTHREE(TIMESTHREE)

IDENTITY(MINUSTHREE(TIMESTHREE))

TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))

14

15

Composed down the column

16

#NAME?

IDENTITY

TIMESTWO

IDENTITY

17

#NAME?

MINUSTHREE(IDENTITY)

TIMESTWO(ADDONE)

ADDTWO(MINUSTHREE)

TIMESTHREE(TIMESTWO)

18

#NAME?

ADDTWO(MINUSTHREE(IDENTITY))

IDENTITY(TIMESTWO(ADDONE))

ADDONE(ADDTWO(MINUSTHREE))

MINUSTHREE(TIMESTHREE(TIMESTWO))

19

#NAME?

TIMESTHREE(ADDTWO(MINUSTHREE(IDENTITY)))

MINUSTHREE(IDENTITY(TIMESTWO(ADDONE)))

IDENTITY(ADDONE(ADDTWO(MINUSTHREE)))

TIMESTWO(MINUSTHREE(TIMESTHREE(TIMESTWO)))

Sheet2

Cell Formulas
Range	Formula
A4:D7	A4	=MAP(SCANBYROW(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F4:I7	F4	=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13	F10	=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19	A16	=MAP(SCANBYCOL(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F16:I19	F16	=SCANBYCOL(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
Dynamic array formulas.

Could you please help debug the #VALUE! error? Meanwhile, I didn't quite understand the COMPOSE function "=LAMBDA(f,g,LAMBDA(x,g(f(x))))" and how it works. May I expect you to shed some light on it?

Thank you so much in advance.

tboulden · Feb 10, 2023

vincethesun said:
Could you please help debug the #VALUE! error? Meanwhile, I didn't quite understand the COMPOSE function "=LAMBDA(f,g,LAMBDA(x,g(f(x))))" and how it works. May I expect you to shed some light on it?

Thank you so much in advance.

Hi vincethesun! Did you also put IDENTITY, ADDONE, etc. in the Name Manager?

As for COMPOSE, this is a common way to create a "new" function from two existing functions; you could define a function in the Name Manager that is the combination of the two existing functions but this is 1) redundant to a certain degree and 2) time-consuming if you need to compose many different functions in different order. Function composition such as COMPOSE turns a set of a few simple functions into a tool-kit for more complex functions for a low amount of effort. Google for "function composition" and many math or comp sci results can help explain with more detail.

Hope this help!

vincethesun · Feb 11, 2023

Thanks tboulden. I did omit to put one of them in the Name Manager. Now it works perfectly.

Again, the LAMBDA-based UDFs and examples you shared are mind-blowing and incredibly powerful together with their simplicity and elegance. I'm litterally reshaping my understanding of Excel capacities with the amazing LAMBDA function thanks to you. Cheers and bravo!

SCANBYROW

tboulden

Board Regular

tboulden

Board Regular

tboulden

Board Regular

vincethesun

New Member

tboulden

Board Regular

vincethesun

New Member

Forum statistics

Share this page

SCANBYROW

tboulden

Board Regular

tboulden

Board Regular

tboulden

Board Regular

vincethesun

New Member

tboulden

Board Regular

vincethesun

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock