ASCAN

[Xlambda]

ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)

LAMBDA 1.1.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
2	sample 1			a		=ASCAN(A3:D6)					=ASCAN(A3:D6,-1)					=ASCAN(A3:D6,1)
3	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
4	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
5	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
6	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
7
8						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
9	sample 2			a		=ASCAN(A10#)					=ASCAN(A10#,-1)					=ASCAN(A10#,1)
10	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
11	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
12	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
13	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
14	17	18	19	20		153	171	190	210		17	35	54	74		45	50	55	60
15	21	22	23	24		231	253	276	300		21	43	66	90		66	72	78	84
16
17	Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18	adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post

Cell Formulas
Range	Formula
F2,P9,K9,F9,P2,K2	F2	=FORMULATEXT(F3)
F3:I6	F3	=ASCAN(A3:D6)
K3:N6	K3	=ASCAN(A3:D6,-1)
P3:S6	P3	=ASCAN(A3:D6,1)
A10:D15	A10	=SEQUENCE(6,4)
F10:I15	F10	=ASCAN(A10#)
K10:N15	K10	=ASCAN(A10#,-1)
P10:S15	P10	=ASCAN(A10#,1)
Dynamic array formulas.

 

[Xlambda]

Hi David,
Did you test it ? Your formula does not work.

ARRAYS.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2					2 columns running totals
3		sample			=ASCAN(B4:C10,1)
4		120	45		120	45
5		230	23		350	68
6		430	83		780	151
7		520	45		1300	196
8		712	89		2012	285
9		439	56		2451	341
10		492	37		2943	378
11
12					David's formula
13					=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))
14					120
15					350
16					780
17					1300
18					2012
19					2451
20					2943
21
Sheet2

Cell Formulas
Range	Formula
E3,E13	E3	=FORMULATEXT(E4)
E4:F10	E4	=ASCAN(B4:C10,1)
E14:E20	E14	=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))
Dynamic array formulas.

 

[bines53]

Hi Xlambda,

This is a partial formula, I mean, is it possible to reach a complete formula with the formula TAKE in your opinion?
And ,If we change to- index(x,,2) ,We will get the solution to the second column.

David

 

[bines53]

Hi Xlambda,

I have a solution,Thanks for the inspiration!

=LET(z,B4:C10,MAKEARRAY(ROWS(z),COLUMNS(z),LAMBDA(x,y,SUM(TAKE(CHOOSECOLS(z,y),x)))))

David

 

[Xlambda]

You're welcome! I've been there though. I have used expandable "ranges" with makearray and lots of other constructions designs long time ago. What I have learned is that these designs should be used only when there is NO other way possible.
Here is why. A simple example easy to check.
Your formula for z=SEQUENCE(5000,20) takes 3 minutes and 20 seconds to calculate, meanwhile =ASCAN(SEQUENCE(5000,20),1) is instant.
So, for SUM , ASCAN is most efficient, and for other scenarios that require versatility (to be able to apply any operations between current value and previous values, through an acumulator) , the other 2 constructions.

 

[Xlambda]

Another example of applying the golden rule in lambda "programming": use new lambda helper functions designs only when there is NO other way. (because of their iterative nature, tend to be "slow")
My take of lrobbo314 's REPEAT function without using MAKEARRAY => more efficient
AREPT(ar,v,[h]), Array REPeaT function (can handle blanks)
ar: array
v: vertical repeat factor
h: horizontal repeat factor
- if v or h omitted, 1 value is assigned

=LAMBDA(ar, v, [h],
    LET(
        a, IF(ar = "", "", ar),
        r, ROWS(a),
        c, COLUMNS(a),
        l, c * MAX(h, 1),
        s, SEQUENCE(r * MAX(v, 1), l) - 1,
        INDEX(a, MOD(QUOTIENT(s, l), r) + 1, MOD(s, c) + 1)
    )
)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1
2							=AREPT("A",2,3)				<=>	=REPT(B3,SEQUENCE(2,3)^0)
3		A					A	A	A			A	A	A
4							A	A	A			A	A	A
5																							v,3,h,omitted				v,omitted,h,3					v,3,h,3
6							=AREPT(B7:D10,3,4)																=AREPT(U7:U9,3)				=AREPT(U7:U9,,3)					=AREPT(U7:U9,3,3)
7		A	a	1			A	a	1	A	a	1	A	a	1	A	a	1			a		a				a	a	a			a	a	a
8		B	b	2			B	b	2	B	b	2	B	b	2	B	b	2			b		b				b	b	b			b	b	b
9		C	c	3			C	c	3	C	c	3	C	c	3	C	c	3			c		c				c	c	c			c	c	c
10		D	d	4			D	d	4	D	d	4	D	d	4	D	d	4					a									a	a	a
11							A	a	1	A	a	1	A	a	1	A	a	1					b									b	b	b
12							B	b	2	B	b	2	B	b	2	B	b	2					c									c	c	c
13							C	c	3	C	c	3	C	c	3	C	c	3					a									a	a	a
14							D	d	4	D	d	4	D	d	4	D	d	4					b									b	b	b
15							A	a	1	A	a	1	A	a	1	A	a	1					c									c	c	c
16							B	b	2	B	b	2	B	b	2	B	b	2
17			blank				C	c	3	C	c	3	C	c	3	C	c	3							v,3,h,omitted					v,omitted,h,3
18			empty string				D	d	4	D	d	4	D	d	4	D	d	4							=AREPT(U19:W19,3)					=AREPT(U19:W19,,3)
19																					a	b	c		a	b	c			a	b	c	a	b	c	a	b	c
20			→				v,omitted,h,4																		a	b	c
21							=AREPT(B22:D25,,4)																		a	b	c			v,3,h,3
22		A	a	1			A	a	1	A	a	1	A	a	1	A	a	1												=AREPT(U19:W19,3,3)
23		B	b	2			B	b	2	B	b	2	B	b	2	B	b	2												a	b	c	a	b	c	a	b	c
24		C		3			C		3	C		3	C		3	C		3												a	b	c	a	b	c	a	b	c
25		D	d	4			D	d	4	D	d	4	D	d	4	D	d	4												a	b	c	a	b	c	a	b	c
26
27							v,3,h,omitted													repeat patterns
28							=AREPT(B22:D25,3)													=AREPT(SEQUENCE(3,4),4,3)
29							A	a	1											1	2	3	4	1	2	3	4	1	2	3	4
30							B	b	2											5	6	7	8	5	6	7	8	5	6	7	8
31							C		3											9	10	11	12	9	10	11	12	9	10	11	12
32							D	d	4											1	2	3	4	1	2	3	4	1	2	3	4
33							A	a	1											5	6	7	8	5	6	7	8	5	6	7	8
34							B	b	2											9	10	11	12	9	10	11	12	9	10	11	12
35							C		3											1	2	3	4	1	2	3	4	1	2	3	4
36							D	d	4											5	6	7	8	5	6	7	8	5	6	7	8
37							A	a	1											9	10	11	12	9	10	11	12	9	10	11	12
38							B	b	2											1	2	3	4	1	2	3	4	1	2	3	4
39							C		3											5	6	7	8	5	6	7	8	5	6	7	8
40							D	d	4											9	10	11	12	9	10	11	12	9	10	11	12
41
Sheet1

Cell Formulas
Range	Formula
G2,T28,G28,AD22,G21,AD18,Y18,AF6,W6,AA6,G6,L2	G2	=FORMULATEXT(G3)
G3:I4	G3	=AREPT("A",2,3)
L3:N4	L3	=REPT(B3,SEQUENCE(2,3)^0)
G7:R18	G7	=AREPT(B7:D10,3,4)
W7:W15	W7	=AREPT(U7:U9,3)
AA7:AC9	AA7	=AREPT(U7:U9,,3)
AF7:AH15	AF7	=AREPT(U7:U9,3,3)
Y19:AA21	Y19	=AREPT(U19:W19,3)
AD19:AL19	AD19	=AREPT(U19:W19,,3)
G22:R25	G22	=AREPT(B22:D25,,4)
AD23:AL25	AD23	=AREPT(U19:W19,3,3)
G29:I40	G29	=AREPT(B22:D25,3)
T29:AE40	T29	=AREPT(SEQUENCE(3,4),4,3)
Dynamic array formulas.

 

[st001]

Hi,

I'm stuck on a problem, and if I can use an lambda function for this or not, which I've spent almost all day thinking about
But, essentially, I have a table that looks like this that shows yearly percentages

31/03/2023	31/03/2024	31/03/2025	31/03/2026
5%	6%	3%	8%

And I am trying to show this (via an lambda of some sort), the percentages, repeated four times, by quarter, for a table that looks like this.

30/06/2022	30/09/2022	31/12/2022	31/03/2023	30/06/2023	30/09/2023	31/12/2023	31/03/2024	etc.
5%	5%	5%	5%	6%	6%	6%	6%

I'm quite certain this can be lambdified (the row with the values at least), possibly both, but wasn't able to figure this one out, over the past few hours.

I wonder if anyone has any ideas on how to do this, perhaps create a generic function that can do the same by month too, from years to quarters (repeated 4 times), but also from years to months (repeated 12 times). Or even, if the information is presented by quarter to begin with, from quarters to months (repeated 3 times)

I'm still a bit clueless.

st001

 

[st001]

I solved it!

 

[Xlambda]

Show us !!

 

[st001]

Will post Code this evening. It is on other laptop!
I believe it will boost, enhance a stronger sense of community, and powerful idea sharing on this forum.
Thank you for your patience.

 

[Xlambda]

My first glimpse idea, an universal concept that works for any size intervals. (not only same size)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3		3	8	11	15	21	27
4		a	b	c	d	e	f
5
6		=SEQUENCE(,MAX(B3:G3))
7		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
8
9		=XLOOKUP(B7#,B3:G3,B4:G4,,1)
10		a	a	a	b	b	b	b	b	c	c	c	d	d	d	d	e	e	e	e	e	e	f	f	f	f	f	f
11
12
Sheet4

Cell Formulas
Range	Formula
B6,B9	B6	=FORMULATEXT(B7)
B7:AB7	B7	=SEQUENCE(,MAX(B3:G3))
B10:AB10	B10	=XLOOKUP(B7#,B3:G3,B4:G4,,1)
Dynamic array formulas.