# AFLIP  	flips an array horizontally and/or vertically



## Xlambda (Sep 18, 2021)

*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)
    )
)
```
Book1ABCDEFGHIJKLMNOPQRST1samplef,omitted212345=AFLIP(A2:E5)if f <>(0,1,2) array flips both directions36781054321f,341113141510876=AFLIP(A2:E5,3)5161718192015141311201918171662019181716151413117108768f,1543219=AFLIP(A2:E5,1)1016171819201111131415126781013123451415f,216=AFLIP(A2:E5,2)17201918171618151413111910876205432121Sheet1Cell FormulasRangeFormulaG2,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.


----------



## samdthompson (Oct 5, 2021)

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 (Oct 6, 2021)

samdthompson said:


> 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.xlsxABCDEFGHIJKLMNOP1Task 1: Build a waffle chart with conditional formating ( 10x10 cells )2%CF values for each color3Total100=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)4Green2585 =C10<=$D$45Red2860 =C10<=$D$56Blue3232 =C10<=$D$67CF formulas89=AFLIP(SEQUENCE(10,10),1)10919293949596979899100118182838485868788899012717273747576777879801361626364656667686970145152535455565758596015414243444546474849501631323334353637383940172122232425262728293018111213141516171819201912345678910202122other functions23ASCAN24AFLIP post 2Cell FormulasRangeFormulaD3,C9D3=FORMULATEXT(D4)D4:D6D4=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)C10:L19C10=AFLIP(SEQUENCE(10,10),1)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueC10:L19Expression=C10<=$D$6textNOC10:L19Expression=C10<=$D$5textNOC10:L19Expression=C10<=$D$4textNO*


----------



## Xlambda (Oct 6, 2021)

*2. function ALFCR(a,[lf],[cr])* *A*rray *L*ast/*F*irst values, by* C*olumns/*R*ows, 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.xlsxABCDEFGHIJKLMNOPQRSTUV1Task 2: Extract first/last values of an array by clms/rows2ALFCR(a,[lf],[cr]) 3a: array4lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values5cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)67lf,cr,omitted (last clm)lf,1,cr,omitted (first clm)8sample 1a=ALFCR(A9:G14)=ALFCR(A9:G14,1,)925772101011121313101115171820212115122526262513303234343014394242391516lf,1,cr,1 (frst row)17sample 2a=ALFCR(A18:G23,1,1)1825715210115137191011121320151718202121222526lf,omitted,cr,1 (last row)22303234=ALFCR(A18:G23,,1)2339422230173926344224AFLIP post 3Cell FormulasRangeFormulaI8,I22,I17,O8I8=FORMULATEXT(I9)I9:I14I9=ALFCR(A9:G14)O9:O14O9=ALFCR(A9:G14,1,)I18:O18I18=ALFCR(A18:G23,1,1)I23:O23I23=ALFCR(A18:G23,,1)Dynamic array formulas.


----------



## Xlambda (Oct 7, 2021)

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.xlsxABCDEFGHIJKLMNOPQRST1ALFCR without AFLIP2ALFCR(a,[lf],[cr]) 3a: array4lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values5cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)67Note: This function can handle arrays that have full rows/clms with blanks/null strings89lf,cr,omitted (last clm)lf,1,cr,omitted (first clm)10sample 1a=ALFCR(A11:G16)=ALFCR(A11:G16,1,)112572712101112131013131517182021152114  15303234303416394239421718lf,1,cr,1 (frst row)19sample 2a=ALFCR(A20:G25,1,1)202  57152115137211112132215182021232526lf,omitted,cr,1 (last row)24 303234=ALFCR(A20:G25,,1)2539 4215303926344226AFLIP post 4Cell FormulasRangeFormulaI10,I24,I19,O10I10=FORMULATEXT(I11)I11:I16I11=ALFCR(A11:G16)O11:O16O11=ALFCR(A11:G16,1,)A14,E25,A24,C20:D20,G14A14=""I20:O20I20=ALFCR(A20:G25,1,1)I25:O25I25=ALFCR(A20:G25,,1)Dynamic array formulas.


----------



## Xlambda (Oct 18, 2021)

*"snake" sequence using AFLIP*
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRS1"snake" sequence using AFLIP23rows7lowest value each row4clms8highest value each row56=SEQUENCE(7,8)from this:tothis:snake sequence71234567812345678891011121314151616151413121110991718192021222324171819202122232410252627282930313232313029282726251133343536373839403334353637383940124142434445464748484746454443424113495051525354555649505152535455561415=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))16123456781716151413121110918171819202122232419323130292827262520333435363738394021484746454443424122495051525354555623AFLIP post 5Cell FormulasRangeFormulaB6,K15B6=FORMULATEXT(B7)B7:I13B7=SEQUENCE(7,8)K16:R22K16=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))Dynamic array formulas.


----------



## Dermot (Nov 27, 2021)

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 (Nov 27, 2021)

Dermot said:


> 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 (Nov 27, 2021)

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 (Nov 27, 2021)

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


----------



## Xlambda (Sep 18, 2021)

*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)
    )
)
```
Book1ABCDEFGHIJKLMNOPQRST1samplef,omitted212345=AFLIP(A2:E5)if f <>(0,1,2) array flips both directions36781054321f,341113141510876=AFLIP(A2:E5,3)5161718192015141311201918171662019181716151413117108768f,1543219=AFLIP(A2:E5,1)1016171819201111131415126781013123451415f,216=AFLIP(A2:E5,2)17201918171618151413111910876205432121Sheet1Cell FormulasRangeFormulaG2,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.


----------



## Xlambda (Nov 27, 2021)

Dermot said:


> 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.xlsxABCDEFGHIJKLMNOPQ1Sample sales report2Jul-21Aug-21Sep-21Oct-21Nov-21Dec-213prod 12541602164prod 215723011289985prod 376561226prod 4136176167347prod 5775813215643AFLIP function89Flipper functionJ13:=AFLIP(B3:G7)10Task 1: Print the report,dates in reversed order (flip horiz.)J12:=AFLIP(B2:G2)1112????=Flipper(B3:G7)Dec-21Nov-21Oct-21Sep-21Aug-21Jul-211302540prod 121616025414112230157prod 2988911223015715prod 3122567616prod 43416717613617prod 54315613258771819Task 2: Print the report, with products in reversed order20Jul-21Aug-21Sep-21Oct-21Nov-21Dec-2121????(no flipping vert. functionality)prod 5 77581321564322prod 41361761673423prod 3765612224prod 2157230112899825prod 12541602162627I21:=AFLIP(A3:A7,1)28J21:=AFLIP(B3:G7,1)2930Task 3: Print the report with products and dates in reversed order31Dec-21Nov-21Oct-21Sep-21Aug-21Jul-2132????(no flipping both directions functionality)prod 543156132587733prod 43416717613634prod 3122567635prod 2988911223015736prod 12161602543738Conclusions 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 functionality424. no flipping horiz. and vert. functionality435. MAKEARRAY with that INDEX inside kind of superflous44alternative to Flipper, only horiz flipping, no counta problems,deals with blanks,no MAKEARRAY needed4546 =LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))4748✌️✌️=LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))(B3:G7)492161602545098891122301575112256765234167176136534315613258775455Dermot'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 horizontaly58 =LAMBDA(a,LET(c,COLUMNS(a),INDEX(IF(a="","",a),SEQUENCE(ROWS(a)),c-SEQUENCE(,c)+1)))59Sheet1Cell FormulasRangeFormulaK9K9=FORMULATEXT(J13)K10K10=FORMULATEXT(J12)B12,B48B12=FORMULATEXT(B13)J12:O12J12=AFLIP(B2:G2)B13:D14B13=Flipper(B3:G7)I13:I17I13=A3:A7J13:O17J13=AFLIP(B3:G7)J20:O20J20=B2:G2I21: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.


----------



## Dermot (Nov 28, 2021)

Yep, you got me good. 
I surrender!
Nice work.


----------



## Xlambda (Dec 22, 2021)

?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 !?✌️


----------



## Xlambda (Dec 22, 2021)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB1Rotating 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:5123448129511211109656783711106287657910111226101173432181591284910closest built-inif we flip the transposeif we flip the initial array11function to rotation is:horizontally <=> 2. scenariohoriz. and vert. <=> 3. scenario12=TRANSPOSE(A5#)=AFLIP(F13#)=AFLIP(A5#,2)1315995112111091426101062876515371111734321164812128417not good enough!!!18Conclusions:19if we flip the transposeRotating an array 180º <=> flipping on both x,y axis20vertically <=> 1. scenarioEven if an array is not a physical object, this kind of looks like21=AFLIP(F13#,1)intermediate axis theorem. ?Check the link:224812The Bizarre Behavior of Rotating Bodies233711242610The technical most accurate definition of transposing a 2D array is:25159rotating an array 90º counter clock wise and flip it vertically26or27rotating an array 90º clock wise and flip it horizontally2829There are million ways to solve rotation without calling AFLIP or other30functions, there is one using recursion, that I love it, but using AFLIP, 31in my opinion, is the most elegant one. Kudos to Geert !!32Sheet12Cell FormulasRangeFormulaA5: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.


----------



## Xlambda (Dec 22, 2021)

*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])*

```
=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)

```
=LAMBDA(a,r,SWITCH(r,-1,AFLIP(TRANSPOSE(a),1),1,AFLIP(TRANSPOSE(a)),2,AFLIP(a,2)))
```
AFLIP.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1Rotating 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 3AFED512C4YEDX1BYX634B1XDEY4C437XYA3F2218DEFCBA9nested rotations10rotating 2 times 90º CCW ("left") <=> rotating 180º (r=2)check rotate 180º <=> AFLIP(a,2) both directions11=AROTATE(AROTATE(A4:D8,-1),-1)=AFLIP(A4:D8,2)12FEDFED13YXYX14434315212116CBACBA1718rotating 2 times 90º CW ("right") <=> rotating 180º (r=2)rotating 3 times 90º CW <=> rotating 90º CCW once19=AROTATE(AROTATE(A4:D8,1),1)=AROTATE(AROTATE(AROTATE(A4:D8,1),1),1)20FED 2F21YXC4YE2243B1XD2321A324CBA25Notes: rotating 3 times 90º CCW <=> rotating 90º CW26Very 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 3A29AFLIP, that is behind all calculations, does this by design.DX1B30EY4C31SWITCH is best function for handling arguments allowed values, F232if 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 number34"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º CCW37MOD(11,4)=3 <=> 270º CCW <=> 90º CCW 3 times3839=REDUCE(A4:D8,SEQUENCE(11),LAMBDA(a,i,AROTATE(a,-1)))40 3A41DX1B42EY4C43F244AROTATE 2Cell FormulasRangeFormulaF3,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.


----------



## Xlambda (Dec 23, 2021)

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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Chessboard set up1st layer2nd layer2board patternCB=defined name of pieces display38♜♞♝♛♚♝♞♜♜♞♝♛♚♝♞♜47♟♟♟♟♟♟♟♟♟♟♟♟♟♟♟♟5665748392♙♙♙♙♙♙♙♙♙♙♙♙♙♙♙♙101♖♘♗♕♔♗♘♖♖♘♗♕♔♗♘♖11abcdefgh121314=AFLIP(CB,1)flipping not good, but rotate 180º does the trick15Flipping=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 color202122♟♟♟♟♟♟♟♟♟♟♟♟♟♟♟♟23♜♞♝♛♚♝♞♜♜♞♝♚♛♝♞♜242526let's rotate the board layer 90º and still use transpose.27-still not correct, now queens are no longer on their colors28Transposing=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♜♟♙♖♜♟♙♖373839if board layer rotated 90º, rotating "CB" 90º CCW does the trick40Conclusion:=AROTATE(CB,-1)✔41When comes to chess boards only AROTATE should be used ?✌♜♟♙♖42♞♟♙♘43♝♟♙♗44♚♟♙♔45♛♟♙♕46♝♟♙♗47♞♟♙♘48♜♟♙♖49AROTATE 3Cell FormulasRangeFormulaM14M14=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 RangesNameRefers ToCellsCB='AROTATE 3'!$X$3:$AE$10X41, X29, X16, M29, M16


----------



## Xlambda (Dec 23, 2021)

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(...... *

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ1All possible chessboard orientations.23=AROTATE(ACHESS(),2)4=ACHESS()hgfedcbaUnicodes of chess pawns and pieces58♜♞♝♛♚♝♞♜♖♘♗♔♕♗♘♖198129813981498159816981798189819982098219822982367♟♟♟♟♟♟♟♟♙♙♙♙♙♙♙♙2♔♕♖♗♘♙♚♛♜♝♞♟763=UNICHAR(X5:AI5)8549451036112♙♙♙♙♙♙♙♙♟♟♟♟♟♟♟♟7121♖♘♗♕♔♗♘♖♜♞♝♚♛♝♞♜813abcdefgh14=AROTATE(ACHESS(),1)15=AROTATE(ACHESS(),-1) 1234567816♜♟♙♖ha♖♙♟♜17♞♟♙♘gb♘♙♟♞18♝♟♙♗fc♗♙♟♝19♚♟♙♔ed♕♙♟♛20♛♟♙♕de♔♙♟♚21♝♟♙♗cf♗♙♟♝22♞♟♙♘bg♘♙♟♞23♜♟♙♖ah♖♙♟♜2487654321AROTATE 4Cell FormulasRangeFormulaN3,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.


----------



## Xlambda (Jun 2, 2022)

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

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP1same values, different chart dimension2simple pattern/chartm,100,v,(25,42,77),r,27,c,11Concept3=WAF(100,{25,42,77},10,10)=WAF(100,{25,42,77},27,11,"x")filled pattern calculation4 251 25 x1max=100542242 x2total cells=r*c=27*11=2976333333377377 x3733333333331 cell value833333333331 cell=max/r/c=max/(r*c)=100/297=0.33679223333333310222222222233333333nr. cells for each value11111112222233333333333values=AD12:AD14/0.336712111111111133333333333125/ 0.3367=74.250113111111111133333333333242124.741433333333333377228.691533333333333 - 1633333333333=INT(AG12#)1733333333333741833333333333124192223333333322820222222222222122222222222check. count nr. of 1,2,3 cells2222222222222=MAP(SEQUENCE(3),LAMBDA(x,SUM(--(O4:Y30=x))))2322222222222↓↓↓=SCAN(0,AG24#,LAMBDA(v,i,v+i))241111111122274742511111111111501242611111111111104228271111111111128111111111112911111111111301111111111131Waffle 1Cell FormulasRangeFormulaB3,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.3367AG17: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.


----------



## Xlambda (Jun 2, 2022)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH12=WAF(C3,C6:C9,C4,C5,"kg")if m<max(v)3max4537 765.32 kg1=WAF(50,{37,100,67},10,10)4rows271733.8 kg233333333333715clms132947.52 kg333333333336726values2947.524444444443988.9 kg43333333333100371733.8444444444444422222223338765.324444444444444222222222293988.94444444444444222222222210values not in order44444444444441111111222114444444444444111111111112333333344444411111111111333333333333331111111111143333333333333153333333333333163333333333333Numbers can be "hidden" nr. with custom format.17333333333333318333333333333319222233333333320222222222222221222222222222222222222222222223222222222222224222222222222225111111122222226111111111111127111111111111128111111111111129111111111111130Waffle 2Cell FormulasRangeFormulaE2,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 FormattingCellConditionCell FormatStop If TrueV4:AG6,V7:AE13Expression=V4=1textNOV4:AG6,V7:AE13Expression=V4=2textNOV4:AG6,V7:AE13Expression=V4=3textNOV4:AG6,V7:AE13Expression=V4=4textNOE3:Q29,S3:S6Expression=E3=1textNOE3:Q29,S3:S6Expression=E3=2textNOE3:Q29,S3:S6Expression=E3=3textNOE3:Q29,S3:S6Expression=E3=4textNO


----------



## Xlambda (Jun 3, 2022)

*"Impress your boss with Waffle Charts in Excel" with a single cell formula for all charts* 😉
Waffle Chart.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZ12=WAF(400,{270,338,256,209},20,20,"lb")345Chocolate weights per 1000 Bars67 209 lbWhite Choc8256 lbCaramel Stuffed Bars9270 lbFruit & Nut Bars10338 lbRasberry Choco111213141516171819202122232425262728WafCell FormulasRangeFormulaB2B2=FORMULATEXT(B7)B7:W26B7=WAF(400,{270,338,256,209},20,20,"lb")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB7:W26Expression=B7=1textNOB7:W26Expression=B7=2textNOB7:W26Expression=B7=3textNOB7:W26Expression=B7=4textNO


----------



## Xlambda (Sep 18, 2021)

*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)
    )
)
```
Book1ABCDEFGHIJKLMNOPQRST1samplef,omitted212345=AFLIP(A2:E5)if f <>(0,1,2) array flips both directions36781054321f,341113141510876=AFLIP(A2:E5,3)5161718192015141311201918171662019181716151413117108768f,1543219=AFLIP(A2:E5,1)1016171819201111131415126781013123451415f,216=AFLIP(A2:E5,2)17201918171618151413111910876205432121Sheet1Cell FormulasRangeFormulaG2,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.


----------



## GraH (Jun 4, 2022)

Amazing!


----------



## Xlambda (Jun 5, 2022)

GraH said:


> Amazing!


Thanks a lot!! Highly appreciated!! ✌️🙏


----------



## Xlambda (Jun 10, 2022)

If we need the charts stacked horizontally or vertically.
*NWAW(m,v,r,c,[txt],[o])*
*m*: max value
*v*: value or array of values
*r*: rows of each waffle chart
*c*: clms of each waffle chart
*[txt]*: labels text
*[o]*: orientation argument, if omitted or 0 displayed horizontally, if 1 or <>0 vertically

```
=LAMBDA(m,v,r,c,[txt],[o],
    LET(
        n, COUNT(v),
        REDUCE(n & " charts",SEQUENCE(n),
            LAMBDA(k,i,
                LET(
                    x, INDEX(v, i),
                    y, DROP(WAF(m, x, r, c, txt), , -1),
                    IFNA(IF(o,VSTACK(k, HSTACK("Waf." & i, y), ""),HSTACK(k, "Waf." & i, y, "")),"")
                )
            )
        )
    )
)
```
Waffle Chart.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO1o,omitted=>charts displayed horizontally2=NWAF(200,{53,147,105},20,10,"lbs")3↓↓↓43 chartsWaf.153 lbsWaf.2147 lbsWaf.3105 lbs5678911111111011111111111111111111111211111111111311111111111111114111111111111111111111511111111111111111111161111111111111111111117111111111111111111111811111111111111111111111191111111111111111111111111111112011111111111111111111111111111121111111111111111111111111111111221111111111111111111111111111112311111111111111111111111111111124Waffle 3Cell FormulasRangeFormulaB2B2=FORMULATEXT(B4)B4:AO23B4=NWAF(200,{53,147,105},20,10,"lbs")Dynamic array formulas.


----------



## Xlambda (Jun 10, 2022)

Waffle Chart.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB1o,1=> charts displayed vertically2=NWAF(200,{53,147,105},20,10,"lbs",1)all in one33 charts=WAF(200,{53,147,105},20,10,"lbs")4Waf.153 lbs 53 lbs15105 lbs26147 lbs37893333333103333333333113333333333123333333333132222233333142222222222152222222222162222222222172222222222181111112222222191111111111111111111120111111111111111111112111111111111111111111221111111111111111111123111111111111111111112425Waf.2147 lbs262728293011111113111111111113211111111113311111111113411111111113511111111113611111111113711111111113811111111113911111111114011111111114111111111114211111111114311111111114411111111114546Waf.3105 lbs4748495051525354551111156111111111157111111111158111111111159111111111160111111111161111111111162111111111163111111111164111111111165111111111166Waffle 4Cell FormulasRangeFormulaB2,O3B2=FORMULATEXT(B3)B3:M66B3=NWAF(200,{53,147,105},20,10,"lbs",1)O4:Z23O4=WAF(200,{53,147,105},20,10,"lbs")Dynamic array formulas.


----------



## Xlambda (Jun 10, 2022)

Screen capture insert (to many cells for minisheet)
Same display like in Chandoo's example, single cell formula plus some text boxes


----------

