AFLIP

[Xlambda]

AFLIP flips an array horizontally and/or vertically. Does not call any other lambda.

=LAMBDA(a,[f],
    LET(r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
      x,IF(f,ABS(sr-r-1),sr),y,IF(f=1,sc,ABS(sc-c-1)),
      INDEX(IF(a="","",a),x,y)
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	sample						f,omitted
2	1	2	3	4	5		=AFLIP(A2:E5)						if f <>(0,1,2) array flips both directions
3	6	7	8		10		5	4	3	2	1		f,3
4	11		13	14	15		10		8	7	6		=AFLIP(A2:E5,3)
5	16	17	18	19	20		15	14	13		11		20	19	18	17	16
6							20	19	18	17	16		15	14	13		11
7													10		8	7	6
8							f,1						5	4	3	2	1
9							=AFLIP(A2:E5,1)
10							16	17	18	19	20
11							11		13	14	15
12							6	7	8		10
13							1	2	3	4	5
14
15							f,2
16							=AFLIP(A2:E5,2)
17							20	19	18	17	16
18							15	14	13		11
19							10		8	7	6
20							5	4	3	2	1
21
Sheet1

Cell Formulas
Range	Formula
G2,G16,G9,M4	G2	=FORMULATEXT(G3)
G3:K6	G3	=AFLIP(A2:E5)
M5:Q8	M5	=AFLIP(A2:E5,3)
G10:K13	G10	=AFLIP(A2:E5,1)
G17:K20	G17	=AFLIP(A2:E5,2)
Dynamic array formulas.

 

[samdthompson]

This, is my favorite Lambda not for any reason other than it demonstrates the power of what they can do. I cant think of a use for it in my world but it is excellent. Well done mate.

 

[Xlambda]

This, is my favorite Lambda not for any reason other than it demonstrates the power of what they can do. I cant think of a use for it in my world but it is excellent. Well done mate.

Thanks a lot for your kind words!!?✌
Came out with some simple examples using AFLIP (there are variants aflip free, but, if we have it, we can use it)
1. Waffle charts using CF

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Task 1: Build a waffle chart with conditional formating ( 10x10 cells )
2			%	CF values for each color
3		Total	100	=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)
4		Green	25	85	=C10<=$D$4
5		Red	28	60	=C10<=$D$5
6		Blue	32	32	=C10<=$D$6
7					CF formulas
8
9			=AFLIP(SEQUENCE(10,10),1)
10			91	92	93	94	95	96	97	98	99	100
11			81	82	83	84	85	86	87	88	89	90
12			71	72	73	74	75	76	77	78	79	80
13			61	62	63	64	65	66	67	68	69	70
14			51	52	53	54	55	56	57	58	59	60
15			41	42	43	44	45	46	47	48	49	50
16			31	32	33	34	35	36	37	38	39	40
17			21	22	23	24	25	26	27	28	29	30
18			11	12	13	14	15	16	17	18	19	20
19			1	2	3	4	5	6	7	8	9	10
20
21
22			other functions
23			ASCAN
24
AFLIP post 2

Cell Formulas
Range	Formula
D3,C9	D3	=FORMULATEXT(D4)
D4:D6	D4	=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)
C10:L19	C10	=AFLIP(SEQUENCE(10,10),1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C10:L19	Expression	=C10<=$D$6	text	NO
C10:L19	Expression	=C10<=$D$5	text	NO
C10:L19	Expression	=C10<=$D$4	text	NO

 

[Xlambda]

2. function ALFCR(a,[lf],[cr]) Array Last/First values, by Columns/Rows, blanks excluded. Update of former ALFCR . Same functionality, same arguments.
This one calls AFLIP and uses new!! BYROW , BYCOL
The concept is simple, if we have formulas for extracting first clms/rows with values other than blanks/null strings, flipping the array correspondingly, will get last clms/rows values.
Hope that the example is visually explicit enough.

=LAMBDA(a,[lf],[cr],
    LET(r,SEQUENCE(ROWS(a)),c,SEQUENCE(,COLUMNS(a)),x,IF(lf,a,IF(cr,AFLIP(a,1),AFLIP(a))),y,x<>"",
      IF(cr,INDEX(x,BYCOL(IF(y,r),LAMBDA(a,MIN(a))),c),INDEX(x,r,BYROW(IF(y,c),LAMBDA(a,MIN(a)))))
    )
)

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Task 2: Extract first/last values of an array by clms/rows
2	ALFCR(a,[lf],[cr])
3	a: array
4	lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values
5	cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)
6
7									lf,cr,omitted (last clm)						lf,1,cr,omitted (first clm)
8	sample 1						a		=ALFCR(A9:G14)						=ALFCR(A9:G14,1,)
9		2			5		7		7						2
10			10	11	12	13			13						10
11	15		17	18		20	21		21						15
12				25	26				26						25
13		30		32		34			34						30
14				39			42		42						39
15
16									lf,1,cr,1 (frst row)
17	sample 2						a		=ALFCR(A18:G23,1,1)
18		2			5		7		15	2	10	11	5	13	7
19			10	11	12	13
20	15		17	18		20	21
21	22			25	26				lf,omitted,cr,1 (last row)
22		30		32		34			=ALFCR(A18:G23,,1)
23				39			42		22	30	17	39	26	34	42
24
AFLIP post 3

Cell Formulas
Range	Formula
I8,I22,I17,O8	I8	=FORMULATEXT(I9)
I9:I14	I9	=ALFCR(A9:G14)
O9:O14	O9	=ALFCR(A9:G14,1,)
I18:O18	I18	=ALFCR(A18:G23,1,1)
I23:O23	I23	=ALFCR(A18:G23,,1)
Dynamic array formulas.

 

[Xlambda]

This is how ALFCR looks without AFLIP:

=LAMBDA(a,[lf],[cr],
    LET(y,IF(a="","",a),r,SEQUENCE(ROWS(a)),c,SEQUENCE(,COLUMNS(a)),x,y<>"",
      bc,BYCOL(IF(x,r),LAMBDA(a,IF(lf,MIN(a),MAX(a)))),
      br,BYROW(IF(x,c),LAMBDA(a,IF(lf,MAX(a),MIN(a)))),
      IF(cr,INDEX(y,bc,c),INDEX(y,r,br))
    )
)

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ALFCR without AFLIP
2	ALFCR(a,[lf],[cr])
3	a: array
4	lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values
5	cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)
6
7	Note: This function can handle arrays that have full rows/clms with blanks/null strings
8
9									lf,cr,omitted (last clm)						lf,1,cr,omitted (first clm)
10	sample 1						a		=ALFCR(A11:G16)						=ALFCR(A11:G16,1,)
11		2			5		7		2						7
12			10	11	12	13			10						13
13	15		17	18		20	21		15						21
14
15		30		32		34			30						34
16				39			42		39						42
17
18									lf,1,cr,1 (frst row)
19	sample 2						a		=ALFCR(A20:G25,1,1)
20		2			5		7		15	2		11	5	13	7
21				11	12	13
22	15			18		20	21
23				25	26				lf,omitted,cr,1 (last row)
24		30		32		34			=ALFCR(A20:G25,,1)
25				39			42		15	30		39	26	34	42
26
AFLIP post 4

Cell Formulas
Range	Formula
I10,I24,I19,O10	I10	=FORMULATEXT(I11)
I11:I16	I11	=ALFCR(A11:G16)
O11:O16	O11	=ALFCR(A11:G16,1,)
A14,E25,A24,C20:D20,G14	A14	=""
I20:O20	I20	=ALFCR(A20:G25,1,1)
I25:O25	I25	=ALFCR(A20:G25,,1)
Dynamic array formulas.

 

[Xlambda]

"snake" sequence using AFLIP

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	"snake" sequence using AFLIP
2
3	rows	7			lowest value each row
4	clms	8			highest value each row
5
6		=SEQUENCE(7,8)					from this:			to	this:		snake sequence
7		1	2	3	4	5	6	7	8		1	2	3	4	5	6	7	8
8		9	10	11	12	13	14	15	16		16	15	14	13	12	11	10	9
9		17	18	19	20	21	22	23	24		17	18	19	20	21	22	23	24
10		25	26	27	28	29	30	31	32		32	31	30	29	28	27	26	25
11		33	34	35	36	37	38	39	40		33	34	35	36	37	38	39	40
12		41	42	43	44	45	46	47	48		48	47	46	45	44	43	42	41
13		49	50	51	52	53	54	55	56		49	50	51	52	53	54	55	56
14
15											=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))
16											1	2	3	4	5	6	7	8
17											16	15	14	13	12	11	10	9
18											17	18	19	20	21	22	23	24
19											32	31	30	29	28	27	26	25
20											33	34	35	36	37	38	39	40
21											48	47	46	45	44	43	42	41
22											49	50	51	52	53	54	55	56
23
AFLIP post 5

Cell Formulas
Range	Formula
B6,K15	B6	=FORMULATEXT(B7)
B7:I13	B7	=SEQUENCE(7,8)
K16:R22	K16	=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))
Dynamic array formulas.

 

[Dermot]

They say there always several ways to do things in Excel

I didn't see your formula and wrote my own, which is completely different :D

=LAMBDA(a,LET(c,COUNTA(INDEX(a,1,)),r,COUNTA(INDEX(a,,1)),MAKEARRAY(r,c,LAMBDA(rr,cc,INDEX(a,rr,c+1-cc)))))

 

[Xlambda]

They say there always several ways to do things in Excel

Super cool !! Great work!! ?
Can you post please some examples to see your function at work?

 

[Dermot]

well, it's pretty simple
if you call my lambda Flipper, then it's = Flipper(A5:D10) where the range to flip is A5:D10

 

[Xlambda]

Please!!!! I insist. Is respectful for the viewers to present your function ??