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 again,
We do not need to design any function in reverse like reverse scan or any other function in reverse. We can apply to any function the principle I called before the double transpose principle.
The principle is very simple, If we have a function fbr(a) that does something to an array by rows, if you call =TRANSPOSE(fbr(TRANSPOSE(a))) will calculate same things but this time by columns.
Or in other words bycol(a)=transpose(byrow(transpose(a)))
For reversing or flipping (have this function by the way AFLIP ) we need more versatility than a transpose because there are 3 ways of doing it: by row, by col and by array. These can produce 9 different outcomes, because we can flip the input in a way and the output in a diffrent way or not at all. It is common to do running totals of a flipped column and not to flip back the results. So a good flip function that covers all scenarios gives you the flexibility to choose whatever satisfies your needs without defining a reverse function for a single operation that does a double flip in one way only. By manipulating the input and the output arrays as you wish is more versatile. =AFLIP(fn(AFLIP(a,k)),k)
Makes sense?
Do not worry, buckle up and we will go together on the consecutive distributions journey.
PS I will do it as fast as I can considering my spare time. Being lucky today to be able to post from work.✌

 

[bines53]

 

[bines53]

Hi Xlambda,

Do you have an idea, or a solution, how to work with two ranges with the byrow function, inside the countifs function?

Thanks,
David

 

[Xlambda]

Hi David,

Can you be more specific please? Examples? Need a structure, to get from this to that example and will be doable and without ...IFS functions. ✌.
In general, I do not use COUNTIFS or SUMIFS function or any other ...IFS function because of their limited versatility to work with ranges only and not with array calculations. This will limit the eventuality of modular lambda "programming", using functions as routines for other functions.
Excel could have changed them extremely easily to allow array arguments, but they chose not to for safety reasons. They are powerful basic tools for financial or any other reports and should be kept in open sight on the real estate of the spreadsheets as ranges, easily checkable by a supervisor. There are enough famous Excel tiny errors that caused disasters in real life.
PS Still need few good hours to finish the consecutive distribution presentation I've promised. I have joined it with a similar related study about manipulating pattern distributions, very useful for other scenarios stuff, not only consecutive.

 

[bines53]

Hi Xlambda,

I will give two examples about the same two ranges of data, in the ranges there are numbers between 0 and 3 .A1:D23 ,and F1:I23.The ranges has similar values.

first function,
=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1)),
I want to sum each line, in one formula.

Second function

=SUM(BYCOL(SEQUENCE(,4,0),LAMBDA(x,MIN(COUNTIF(A1:D1,x),COUNTIF(F1:I1,x)))))
OR
=SUM(BYCOL(UNIQUE(A1:D1,1),LAMBDA(x,MIN(COUNTIF(A1:D1,x),COUNTIF(F1:I1,x)))))

The formula sum, how many common values are there in both ranges, I want to sum each line, in one formula.
I hope my explanation is clear.

Thanks,
David

 

[Xlambda]

It will be a major help to post proper spreadsheet example using the mini sheet interface.
It is not that I cannot figure it out eventually, but it saves time that I do not have especially when I'm at work. Help me help you.
Also, we have an "audience" here that deserves and would be nice for them to understand what we are doing. ✌
Like this, for first function
Your formula is
=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
You do know how to use the mini-sheet interface? Is very well explained and easy at the top of the forum.
Use it for the second function example.

Book1
	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	1	1	2	2		1	3	3	3		0	1	0	0		1		1
2	3	1	2	0		0	0	2	3		0	0	1	0		1		1
3	1	2	3	3		1	1	0	1		0	1	0	0		1		1
4	2	0	1	2		1	0	3	0		1	0	0	0		1		1			checking outcome
5	2	0	0	1		0	2	3	3		0	0	0	0		0		0			manual countifs vs sigle cell formula
6	3	0	3	2		0	2	0	0		0	0	0	0		0		0			=AND(P1:P23=R1#)
7	1	0	0	2		0	0	3	2		1	0	1	0		2		2			TRUE
8	3	3	1	0		1	1	3	2		0	0	0	0		0		0
9	2	1	3	0		0	3	1	2		0	0	0	0		0		0			=> same outcome every time
10	2	0	3	0		1	2	0	0		1	0	0	0		1		1
11	0	0	2	3		1	0	2	2		1	0	1	0		2		2
12	3	0	1	0		1	2	3	3		0	0	0	0		0		0
13	2	1	3	1		3	3	0	2		0	0	0	0		0		0
14	3	0	3	2		1	0	2	2		1	0	1	0		2		2
15	2	0	3	1		0	3	1	3		0	0	0	0		0		0
16	2	1	1	1		3	0	3	2		0	0	0	0		0		0
17	2	2	0	2		2	3	0	2		1	0	2	0		3		3
18	3	1	2	1		0	3	3	2		0	0	0	0		0		0
19	0	2	0	3		3	3	3	3		0	0	0	1		1		1
20	2	2	0	3		3	1	1	3		0	0	0	1		1		1
21	2	3	0	3		3	2	2	2		0	0	0	0		0		0
22	1	2	2	3		0	0	3	0		0	0	0	0		0		0
23	3	0	0	1		0	2	1	0		0	0	0	0		0		0
24	=RANDARRAY(23,4,0,3,1)					=RANDARRAY(23,4,0,3,1)					K1:K23							=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
25											=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1))
26
27																P1:P23
28																=SUM(K1:N1)
29
Sheet1

Cell Formulas
Range	Formula
A1:D23,F1:I23	A1	=RANDARRAY(23,4,0,3,1)
R1:R23	R1	=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
U6	U6	=FORMULATEXT(U7)
U7	U7	=AND(P1:P23=R1#)
K1:N23	K1	=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1))
P1:P23	P1	=SUM(K1:N1)
A24,F24,R24	A24	=FORMULATEXT(A1)
K25	K25	=FORMULATEXT(K1)
P28	P28	=FORMULATEXT(P1)
Dynamic array formulas.

 

[bines53]

Hi Xlambda,

The first function works perfectly!
I'm still in diapers, in complex functions with excel 365.
I will try to understand how to use the mini-sheet interface.

Thanks,
David

 

[bines53]

Hi Xlambda,

Recently, I don't have a computer, I work with my mobile phone and an Excel application, it is not possible to use the mini-sheet interface.
Is it possible to upload an Excel file?

Thanks,
David

 

[bines53]

Hi Xlambda,

In the second function with map,

First line
=SUM(MAP(COUNTIF(A1:D1,SEQUENCE(,4,0)),COUNTIF(F1:I1,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))

Second line
=SUM(MAP(COUNTIF(A2:D2,SEQUENCE(,4,0)),COUNTIF(F2:I2,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))
........
last line
=SUM(MAP(COUNTIF(A23:D23,SEQUENCE(,4,0)),COUNTIF(F23:I23,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))

Thanks,
David

 

[bines53]

Hi Xlambda,

My solution, not desirable, with map, concat, mid,
=MMULT(MAP(MID(BYROW(A1:D23,LAMBDA(x,CONCAT(COUNTIF(x,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,MID(BYROW(F1:I23,LAMBDA(y,CONCAT(COUNTIF(y,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,LAMBDA(x,y,MIN(x,y))),{1;1;1;1})

Thanks,
David