SCANBYROW

[tboulden]

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.

=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.

 

[djclements]

@Xlambda My mistake. I thought I had it working without the need for ISOMITTED, but clearly that does not work for all situations. Thank you for the peer review. I guess there's nothing for it... the additional IF statement is needed:

SCANBYR:
=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(SCAN(init_val,EXPAND(arr,,COLUMNS(arr)+1,rs),LAMBDA(a,v,IF(v=rs,v,IF(a=rs,IF(ISOMITTED(init_val),v,func(init_val,v)),func(a,v))))),,-1)))

SCANBYC:
=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(TRANSPOSE(SCAN(init_val,TRANSPOSE(EXPAND(arr,ROWS(arr)+1,,rs)),LAMBDA(a,v,IF(v=rs,v,IF(a=rs,IF(ISOMITTED(init_val),v,func(init_val,v)),func(a,v)))))),-1)))

My intention was to simply share the concept of using EXPAND-DROP as an alternative. Personally, I wouldn't use a generalized lambda function to scan by row, because most of the simple requests I've received could be solved with EXPAND-DROP and a single IF statement (customized for each situation).

I also shared the bisection method because I thought it was awesome. I don't see the "logic flaw" that you mention, because there are no apparent limits. It seems to handle the SEQUENCE(1000000,10) test with relative ease anyways (15 seconds for SCANBiROW on my system). However, I didn't test it extensively to see under which conditions it might fail (if any). And since I was not the original author of this method, I included the link to the source material (credit lori_m).

No harm in sharing ideas, in my opinion. To each their own.

Kind regards.