AFLIP

AFLIP(a,[f])
a
array
[f]
flip argument ; 0 or omitted, flips horiz. ; 1, flips vert. ; 2, flips horiz. and vert.

flips an array horizontally and/or vertically

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
AFLIP flips an array horizontally and/or vertically. Does not call any other lambda.
VBA Code:
=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
ABCDEFGHIJKLMNOPQRST
1samplef,omitted
212345=AFLIP(A2:E5)if f <>(0,1,2) array flips both directions
36781054321f,3
41113141510876=AFLIP(A2:E5,3)
51617181920151413112019181716
6201918171615141311
710876
8f,154321
9=AFLIP(A2:E5,1)
101617181920
1111131415
1267810
1312345
14
15f,2
16=AFLIP(A2:E5,2)
172019181716
1815141311
1910876
2054321
21
Sheet1
Cell Formulas
RangeFormula
G2,G16,G9,M4G2=FORMULATEXT(G3)
G3:K6G3=AFLIP(A2:E5)
M5:Q8M5=AFLIP(A2:E5,3)
G10:K13G10=AFLIP(A2:E5,1)
G17:K20G17=AFLIP(A2:E5,2)
Dynamic array formulas.
 
Upvote 0
well, it's pretty simple
Ok!! No problem. I will compare both functions, your Flipper(a) function and AFLIP(a,[f]), for a "pretty simple" sample array.?
LAMBDA 1.1.5.xlsx
ABCDEFGHIJKLMNOPQ
1Sample sales report
2Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
3prod 1254160216
4prod 21572301128998
5prod 37656122
6prod 413617616734
7prod 5775813215643AFLIP function
8
9Flipper functionJ13:=AFLIP(B3:G7)
10Task 1: Print the report,dates in reversed order (flip horiz.)J12:=AFLIP(B2:G2)
11
12????=Flipper(B3:G7)Dec-21Nov-21Oct-21Sep-21Aug-21Jul-21
1302540prod 1216160254
14112230157prod 29889112230157
15prod 31225676
16prod 434167176136
17prod 5431561325877
18
19Task 2: Print the report, with products in reversed order
20Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
21????(no flipping vert. functionality)prod 5 775813215643
22prod 413617616734
23prod 37656122
24prod 21572301128998
25prod 1254160216
26
27I21:=AFLIP(A3:A7,1)
28J21:=AFLIP(B3:G7,1)
29
30Task 3: Print the report with products and dates in reversed order
31Dec-21Nov-21Oct-21Sep-21Aug-21Jul-21
32????(no flipping both directions functionality)prod 5431561325877
33prod 434167176136
34prod 31225676
35prod 29889112230157
36prod 1216160254
37
38Conclusions Flipper function:J32=AFLIP(B3:G7,2)
391. counta for counting rows or columns, not reliableI32=AFLIP(A3:A7,1)
402. no dealing with blanks functionality (blanks turned into 0's)J31:=AFLIP(B2:G2)
413. no flipping verticaly functionality
424. no flipping horiz. and vert. functionality
435. MAKEARRAY with that INDEX inside kind of superflous
44alternative to Flipper, only horiz flipping, no counta problems,deals with blanks,no MAKEARRAY needed
45
46 =LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))
47
48✌️✌️=LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))(B3:G7)
49216160254
509889112230157
511225676
5234167176136
53431561325877
54
55Dermot's Flipper function used:
56 =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)))))
57Alternative function, flipping horizontaly
58 =LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))
59
Sheet1
Cell Formulas
RangeFormula
K9K9=FORMULATEXT(J13)
K10K10=FORMULATEXT(J12)
B12,B48B12=FORMULATEXT(B13)
J12:O12J12=AFLIP(B2:G2)
B13:D14B13=Flipper(B3:G7)
I13:I17I13=A3:A7
J13:O17J13=AFLIP(B3:G7)
J20:O20J20=B2:G2
I21:I25I21=AFLIP(A3:A7,1)
J21:O25J21=AFLIP(B3:G7,1)
K27K27=FORMULATEXT(I21)
K28K28=FORMULATEXT(J21)
J31:O31J31=AFLIP(B2:G2)
I32:I36I32=AFLIP(A3:A7,1)
J32:O36J32=AFLIP(B3:G7,2)
K38K38=FORMULATEXT(J32)
K39K39=FORMULATEXT(I32)
K40K40=FORMULATEXT(J31)
B49:G53B49=LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))(B3:G7)
Dynamic array formulas.
 
Yep, you got me good.
I surrender!
Nice work.
 
?We are hosted here by the finest Excel laboratory in the world, and we should never "surrender" our experiments, we only have to try to make them as good and accurate as we can. Team work !?✌️
 
Flipping vs Rotating vs Transposing.
Rotating arrays was first covered on the forum by Geert, check his functions: ROT90CW , ROT90CCW.
If we consider xy plane , spreadsheet's plane, x the horizontal axis (clms A B C...), y the vertical axis (rows 1 2 3...), z the depth axis (perpendicular to the spreadsheet) and the origin the center of an array, then:
-flipping horizontally means rotating around y axis
-flipping vertically means rotating around x axis
-rotating nº means rotating nº around z axis
If I am allowed to post a link to a non-excel YT, but a very interesting one about rotation: The Bizarre Behavior of Rotating Bodies by Veritasium.
AFLIP.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Rotating concept. There are 3 scenarios to consider, 2D array wise, for rotating around z axis.
21.2.3.
3rotating 90º counter clock wiserotating 90º clock wiserotating 180º
4sampleshould look like this:should look like this:should look like this:
5123448129511211109
65678371110628765
79101112261011734321
81591284
9
10closest built-inif we flip the transposeif we flip the initial array
11function to rotation is:horizontally <=> 2. scenariohoriz. and vert. <=> 3. scenario
12=TRANSPOSE(A5#)=AFLIP(F13#)=AFLIP(A5#,2)
131599511211109
14261010628765
15371111734321
1648121284
17not good enough!!!
18Conclusions:
19if we flip the transposeRotating an array 180º <=> flipping on both x,y axis
20vertically <=> 1. scenarioEven if an array is not a physical object, this kind of looks like
21=AFLIP(F13#,1)intermediate axis theorem. ?Check the link:
224812The Bizarre Behavior of Rotating Bodies
233711
242610The technical most accurate definition of transposing a 2D array is:
25159rotating an array 90º counter clock wise and flip it vertically
26or
27rotating an array 90º clock wise and flip it horizontally
28
29There are million ways to solve rotation without calling AFLIP or other
30functions, there is one using recursion, that I love it, but using AFLIP,
31in my opinion, is the most elegant one. Kudos to Geert !!
32
Sheet12
Cell Formulas
RangeFormula
A5:D7A5=SEQUENCE(3,4)
F12,F21,U12,N12F12=FORMULATEXT(F13)
F13:H16F13=TRANSPOSE(A5#)
N13:P16N13=AFLIP(F13#)
U13:X15U13=AFLIP(A5#,2)
F22:H25F22=AFLIP(F13#,1)
Dynamic array formulas.
 
AROTATE. The function.
Before getting to AROTATE, a tiny refinement of AFLIP. Both ABS functions can be removed for a shorter version. ABS(sr-r-1) <=> r-sr+1 , ABS(sc-c-1) <=> c-sc+1
AFLIP(a,[f])
Excel Formula:
=LAMBDA(a,[f],
    LET(r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
       x,IF(f,r-sr+1,sr),y,IF(f=1,sc,c-sc+1),
       INDEX(IF(a="","",a),x,y)
    )
)
AROTATE(a,r)
a: any array
r: rotate argument: -1, 90º to the "left"(CCW) once ; 1, 90º to the "right"(CW) once; 2, 180º (2 times 90º CCW or CW)
Excel Formula:
=LAMBDA(a,r,SWITCH(r,-1,AFLIP(TRANSPOSE(a),1),1,AFLIP(TRANSPOSE(a)),2,AFLIP(a,2)))
AFLIP.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Rotating 90º, 180º, 270º,..,for fun 990º
2r,-1 90º CCWr,1 90º CWr,2 180º
3sample=AROTATE(A4:D8,-1)=AROTATE(A4:D8,1)=AROTATE(A4:D8,2)
4ABC 2F 3AFED
512C4YEDX1BYX
634B1XDEY4C43
7XYA3F221
8DEFCBA
9nested rotations
10rotating 2 times 90º CCW ("left") <=> rotating 180º (r=2)check rotate 180º <=> AFLIP(a,2) both directions
11=AROTATE(AROTATE(A4:D8,-1),-1)=AFLIP(A4:D8,2)
12FEDFED
13YXYX
144343
152121
16CBACBA
17
18rotating 2 times 90º CW ("right") <=> rotating 180º (r=2)rotating 3 times 90º CW <=> rotating 90º CCW once
19=AROTATE(AROTATE(A4:D8,1),1)=AROTATE(AROTATE(AROTATE(A4:D8,1),1),1)
20FED 2F
21YXC4YE
2243B1XD
2321A3
24CBA
25Notes: rotating 3 times 90º CCW <=> rotating 90º CW
26Very important for any function in my opinion,could not help it and used REDUCE ?
27is dealing with blanks/null strings.=REDUCE(A4:D8,SEQUENCE(3),LAMBDA(a,i,AROTATE(a,-1)))
28AROTATE does not need anything in this respect since already 3A
29AFLIP, that is behind all calculations, does this by design.DX1B
30EY4C
31SWITCH is best function for handling arguments allowed values, F2
32if a wrong argument is inputted, exits with a #NA error.
33No need for extra conditions checking.instead of "3" in SEQUENCE(3) we can put any number
34"n" and "spin" the array n*90º CCW ?
35=AROTATE(A4:D8,3)
36#N/Afor fun, rotating 990º CCW <=> 990º/90º=11 times 90º CCW
37MOD(11,4)=3 <=> 270º CCW <=> 90º CCW 3 times
38
39=REDUCE(A4:D8,SEQUENCE(11),LAMBDA(a,i,AROTATE(a,-1)))
40 3A
41DX1B
42EY4C
43F2
44
AROTATE 2
Cell Formulas
RangeFormula
F3,S39,B35,S27,S19,F19,S11,F11,S3,M3F3=FORMULATEXT(F4)
F4:J7F4=AROTATE(A4:D8,-1)
M4:Q7M4=AROTATE(A4:D8,1)
S4:V8S4=AROTATE(A4:D8,2)
F12:I16F12=AROTATE(AROTATE(A4:D8,-1),-1)
S12:V16S12=AFLIP(A4:D8,2)
F20:I24F20=AROTATE(AROTATE(A4:D8,1),1)
S20:W23S20=AROTATE(AROTATE(AROTATE(A4:D8,1),1),1)
S28:W31S28=REDUCE(A4:D8,SEQUENCE(3),LAMBDA(a,i,AROTATE(a,-1)))
B36B36=AROTATE(A4:D8,3)
S40:W43S40=REDUCE(A4:D8,SEQUENCE(11),LAMBDA(a,i,AROTATE(a,-1)))
Dynamic array formulas.
 
Visualizing fun experiment, AROTATE vs AFLIP vs TRANSPOSE functionality with a chessboard.
Most important rules of a chessboard set up:
A white (light-colored) square is always on the right, from the perspective of the players who are sitting at the board.
It's important that each queen goes on her own color. The black queen on a black square, the white queen on a white square.
The light-colored rooks are placed on the a1 and h1 squares.
AFLIP.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1Chessboard set up1st layer2nd layer
2board patternCB=defined name of pieces display
38
47
56
65
74
83
92
101
11abcdefgh
12
13
14=AFLIP(CB,1)flipping not good, but rotate 180º does the trick
15Flipping=AROTATE(CB,2)
16if we flip the "CB" layer vertically
17keeping the board fixed is not
18a correct setup.
19-queens no longer on their color
20
21
22
23
24
25
26let's rotate the board layer 90º and still use transpose.
27-still not correct, now queens are no longer on their colors
28Transposing=TRANSPOSE(CB)=TRANSPOSE(CB)
29if we transpose "CB" layer
30keeping the board fixed is not correct.
31-queens are on their color, but,
32from perspective of players, right
33corner is not white
34
35
36
37
38
39if board layer rotated 90º, rotating "CB" 90º CCW does the trick
40Conclusion:=AROTATE(CB,-1)
41When comes to chess boards only AROTATE should be used ?✌
42
43
44
45
46
47
48
49
AROTATE 3
Cell Formulas
RangeFormula
M14M14=FORMULATEXT(M16)
X15,X40,X28,M28X15=FORMULATEXT(X16)
M16:T23M16=AFLIP(CB,1)
X16:AE23X16=AROTATE(CB,2)
M29:T36,X29:AE36M29=TRANSPOSE(CB)
X41:AE48X41=AROTATE(CB,-1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
CB='AROTATE 3'!$X$3:$AE$10X41, X29, X16, M29, M16
 
Last edited:
I think that even more important than the pattern are the numbers (called ranks) and the letters (called files).
So, I came up with a formula ACHESS that returns chess pieces, the "ranks" and the "files". The only thing left to do is the pattern following the rule that a1 is always black or h1 is always white.
For different orientations we use AROTATE, and we are set.
ACHESS() my very first argument free function, like NA(), LET comes immediately after LAMBDA : LAMBDA(LET(......
Excel Formula:
=LAMBDA(
      LET(c,SEQUENCE(,8),r,SEQUENCE(9),s,SEQUENCE(,9)-1,
          wp,c^0*9817,bp,wp+6,w,{2,4,3,1,0,3,4,2}+9812,b,w+6,l,c+96,
          a,UNICHAR(SWITCH(r,1,b,2,bp,7,wp,8,w,9,l)),
          IFNA(IF(s,INDEX(a,r,s),SEQUENCE(8,,8,-1)),"")
     )
)
AFLIP.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1All possible chessboard orientations.
2
3=AROTATE(ACHESS(),2)
4=ACHESS()hgfedcbaUnicodes of chess pawns and pieces
581981298139814981598169817981898199820982198229823
672
763=UNICHAR(X5:AI5)
854
945
1036
1127
1218
13abcdefgh
14=AROTATE(ACHESS(),1)
15=AROTATE(ACHESS(),-1) 12345678
16ha
17gb
18fc
19ed
20de
21cf
22bg
23ah
2487654321
AROTATE 4
Cell Formulas
RangeFormula
N3,C15,M14,B4N3=FORMULATEXT(N4)
N4:V12N4=AROTATE(ACHESS(),2)
B5:J13B5=ACHESS()
X6:AI6X6=UNICHAR(X5:AI5)
X7X7=FORMULATEXT(X6)
M15:U23M15=AROTATE(ACHESS(),1)
C16:K24C16=AROTATE(ACHESS(),-1)
Dynamic array formulas.
 
Inspired by today's (2Jun22) Chandoo's YT : Impress your boss with Waffle Charts in Excel - Easy Recipe
WAF function, can create one or more overlapped waffle charts for any values. (creates an easy-to-use pattern for Conditional Formatting)
WAF(m,v,r,c,[txt])
m: max value
v: value or array of values
r: rows of waffle chart
c: clms of waffle chart
[txt]: labels text
Notes:
- appends a "legend" array to the right of the waffle chart
- if m<max(v) => m=max(v)
- if v is an array of values , values will be overlapped in ascending order
Excel Formula:
=LAMBDA(m, v, r, c, [txt],
    LET(
        s, SORT(SEQUENCE(r, c), , -1),
        f, SORT(TOCOL(v)),
        n, ROWS(f),
        q, SEQUENCE(n),
        x, MAX(m, MAX(f)) / r / c,
        y, INT(f / x),
        z, REDUCE("", n - q + 1, LAMBDA(v, i, IF(s <= INDEX(y, i), i, v))),
        IFNA(HSTACK(z, f & IF(txt = "", "", " " & txt), q), "")
    )
)
Waffle Chart.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1same values, different chart dimension
2simple pattern/chartm,100,v,(25,42,77),r,27,c,11Concept
3=WAF(100,{25,42,77},10,10)=WAF(100,{25,42,77},27,11,"x")filled pattern calculation
4 251 25 x1max=100
542242 x2total cells=r*c=27*11=297
6333333377377 x3
733333333331 cell value
833333333331 cell=max/r/c=max/(r*c)=100/297=0.3367
92233333333
10222222222233333333nr. cells for each value
11111112222233333333333values=AD12:AD14/0.3367
12111111111133333333333125/ 0.3367=74.2501
13111111111133333333333242124.74
1433333333333377228.69
1533333333333 -
1633333333333=INT(AG12#)
173333333333374
1833333333333124
1922233333333228
2022222222222
2122222222222check. count nr. of 1,2,3 cells
2222222222222=MAP(SEQUENCE(3),LAMBDA(x,SUM(--(O4:Y30=x))))
2322222222222↓↓↓=SCAN(0,AG24#,LAMBDA(v,i,v+i))
24111111112227474
251111111111150124
2611111111111104228
2711111111111
2811111111111
2911111111111
3011111111111
31
Waffle 1
Cell Formulas
RangeFormula
B3,AI23,AG16,AG11,O3B3=FORMULATEXT(B4)
B4:M13B4=WAF(100,{25,42,77},10,10)
O4:AA30O4=WAF(100,{25,42,77},27,11,"x")
AG12:AG14AG12=AD12:AD14/0.3367
AG17:AG19AG17=INT(AG12#)
AG22AG22=FORMULATEXT(AG24)
AG24:AG26AG24=MAP(SEQUENCE(3),LAMBDA(x,SUM(--(O4:Y30=x))))
AI24:AI26AI24=SCAN(0,AG24#,LAMBDA(v,i,v+i))
Dynamic array formulas.
 
Waffle charts with CF, any values example.
Function calculates iteratively, (REDUCE) for each value in the values array, the proportion of cell distribution, creating the pattern.
Waffle Chart.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1
2=WAF(C3,C6:C9,C4,C5,"kg")if m<max(v)
3max4537 765.32 kg1=WAF(50,{37,100,67},10,10)
4rows271733.8 kg23333333333371
5clms132947.52 kg33333333333672
6values2947.524444444443988.9 kg433333333331003
71733.844444444444442222222333
8765.3244444444444442222222222
93988.944444444444442222222222
10values not in order44444444444441111111222
1144444444444441111111111
1233333334444441111111111
1333333333333331111111111
143333333333333
153333333333333
163333333333333Numbers can be "hidden" nr. with custom format.
173333333333333
183333333333333
192222333333333
202222222222222
212222222222222
222222222222222
232222222222222
242222222222222
251111111222222
261111111111111
271111111111111
281111111111111
291111111111111
30
Waffle 2
Cell Formulas
RangeFormula
E2,V3E2=FORMULATEXT(E3)
E3:S29E3=WAF(C3,C6:C9,C4,C5,"kg")
V4:AG13V4=WAF(50,{37,100,67},10,10)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
V4:AG6,V7:AE13Expression=V4=1textNO
V4:AG6,V7:AE13Expression=V4=2textNO
V4:AG6,V7:AE13Expression=V4=3textNO
V4:AG6,V7:AE13Expression=V4=4textNO
E3:Q29,S3:S6Expression=E3=1textNO
E3:Q29,S3:S6Expression=E3=2textNO
E3:Q29,S3:S6Expression=E3=3textNO
E3:Q29,S3:S6Expression=E3=4textNO
 
"Impress your boss with Waffle Charts in Excel" with a single cell formula for all charts 😉
Waffle Chart.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2=WAF(400,{270,338,256,209},20,20,"lb")
3
4
5Chocolate weights per 1000 Bars
6
7 209 lbWhite Choc
8256 lbCaramel Stuffed Bars
9270 lbFruit & Nut Bars
10338 lbRasberry Choco
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Waf
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B7)
B7:W26B7=WAF(400,{270,338,256,209},20,20,"lb")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:W26Expression=B7=1textNO
B7:W26Expression=B7=2textNO
B7:W26Expression=B7=3textNO
B7:W26Expression=B7=4textNO
 

Forum statistics

Threads
1,214,985
Messages
6,122,603
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top