BY DAVID HAGER
Here’s a quick and interesting use of the LAMBDA function as an adieu to 2020.
I have written several blog posts on the creation of a Bingo board; the latest can be found here:
Excel #8 – Making a Bingo Card Using Only Formulas with Dynamic Array Functions | Excel For You (wordpress.com)
I realized that this would provide a good example of LAMBDA without any variables. So, in the Excel Name Manager, create a new name called BINGO.
In the Refers To box, copy/paste this formula and click Close.
=LAMBDA(INDEX(SORTBY(SEQUENCE(15),RANDARRAY(15))+(15*(SEQUENCE(,5)-1)),SEQUENCE(5),SEQUENCE(,5)))
Then, enter =BINGO() in any cell. This one formula will return a 5 X 5 range of unique numbers that correspond to the 5 columns of a Bingo card.
The only actions left are to add the B I N G O column headers and format the central cell with a black fill.
Happy New Years, and continue watching for additional LAMBDA creations.
BY DAVID HAGER
This is my first post of many to come about the Excel LAMBDA function. Shown below are the first and best videos about LAMBDA.
I strongly recommend that you watch these videos so that you will truly appreciate how LAMBDA will supercharge the Excel spreasheet.
In this and subsequent posts, I will be looking at complex Excel formulas that I and others have made and demonstrating how they can be converted to reusable Excel functions with LAMBDA.
So, I might as well start with the most complex Excel formula ever made. At my request, the creator of the EXCELXOR blog made an Excel formula that calculates the molecular weight from a chemical formula string. It works, but the creator would take a LONG time to explain how it works. Feel free to study it at the link shown below if you have a few weeks to kill.
Molecular Weights « EXCELXOR
I have modified the original formula slightly to make it amenable for conversion to a LAMBDA function, and you could study this formula as well, but simply trust me that this is exactly the situation LAMBDA was made for and you can use it if you need to.
=LAMBDA(molecule,SUM(IFERROR(INDEX(AWeight,MATCH(MID(molecule,SEQUENCE(LEN(molecule)),MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(molecule&"ζ",SEQUENCE(LEN(molecule)),2),{1,2},1)))<13),{1;1})),Atom,0)),0)IFERROR(0+MID(molecule,MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(molecule&"ζ",SEQUENCE(LEN(molecule)),2),{1,2},1)))<13),{1;1})+SEQUENCE(LEN(molecule)),MMULT(1-ISERR(0+MID(molecule,SEQUENCE(LEN(molecule))+MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(molecule&"ζ",SEQUENCE(LEN(molecule)),2),{1,2},1)))<13),{1;1}),{1,2})),{1;1})),1)IFERROR(0+MID(molecule,IF(ISNUMBER(MATCH(SEQUENCE(LEN(molecule)),IF(ISODD(MATCH(SEQUENCE(LEN(molecule)),MODE.MULT(IF(MID(molecule,SEQUENCE(LEN(molecule)),1)={"(",")"},SEQUENCE(LEN(molecule))+{1,0}),SEQUENCE(LEN(molecule))))),SEQUENCE(LEN(molecule))),0)),FIND(")",molecule,SEQUENCE(LEN(molecule))))+1,MMULT(1-ISERR(0+MID(molecule,IF(ISNUMBER(MATCH(SEQUENCE(LEN(molecule)),IF(ISODD(MATCH(SEQUENCE(LEN(molecule)),MODE.MULT(IF(MID(molecule,SEQUENCE(LEN(molecule)),1)={"(",")"},SEQUENCE(LEN(molecule))+{1,0}),SEQUENCE(LEN(molecule))))),SEQUENCE(LEN(molecule))),0)),FIND(")",molecule,SEQUENCE(LEN(molecule))))+1,{1,2})),{1;1})),1)))
or, =MolecularWeight(A1), whichever is easier 😁.
There are parts of this formula that could be simplified by using the LET function, so if you would like to do this, feel free, but 99% of users that would not gain any readability.
What is important is understanding where the atomic information comes from. In the 2016 article, the arrays storing the atomic symbols and corresponding were in columns on the worksheet. However, if this (or any) LAMBDA function uses a large amount of information that is not practical to store in a LET function, they can be moved/copied in the form of defined names along with the original worksheet along with the LAMBDA function. This step is currently necessary to port your LAMBDA functions to another workbook until Microsoft provides another method. Then, delete the transferred worksheet and the LAMBDA functions and defined names will remain. The array constants, without any connection to the original workbook, are shown below.
Atom = {“Ac”;”Ag”;”Al”;”Am”;”Ar”;”As”;”At”;”Au”;”B”;”Ba”;”Be”;”Bh”;”Bi”;”Bk”;”Br”;”C”;”Ca”;”Cd”;”Ce”;”Cf”;”Cl”;”Cm”;”Co”;”Cr”;”Cs”;”Cu”;”Db”;”Dy”;”Er”;”Es”;”Eu”;”F”;”Fe”;”Fm”;”Fr”;”Ga”;”Gd”;”Ge”;”H”;”He”;”Hf”;”Hg”;”Ho”;”Hs”;”I”;”In”;”Ir”;”K”;”Kr”;”La”;”Li”;”Lr”;”Lu”;”Md”;”Mg”;”Mn”;”Mo”;”Mt”;”N”;”Na”;”Nb”;”Nd”;”Ne”;”Ni”;”No”;”Np”;”O”;”Os”;”P”;”Pa”;”Pb”;”Pd”;”Pm”;”Po”;”Pr”;”Pt”;”Pu”;”Ra”;”Rb”;”Re”;”Rf”;”Rh”;”Rn”;”Ru”;”S”;”Sb”;”Sc”;”Se”;”Sg”;”Si”;”Sm”;”Sn”;”Sr”;”Ta”;”Tb”;”Tc”;”Te”;”Th”;”Ti”;”Tl”;”Tm”;”U”;”V”;”W”;”Xe”;”Y”;”Yb”;”Zn”;”Zr”}
AWeight = {227;107.8682;26.9815;243;39.948;74.9216;210;196.9665;10.811;137.327;9.0122;264;208.9804;247;79.904;12.0107;40.078;112.411;140.116;251;35.453;247;58.9332;51.9961;132.9055;63.546;262;162.5;167.259;252;151.964;18.9984;55.845;257;223;69.723;157.25;72.64;1.0079;4.0026;178.49;200.59;164.9303;277;126.9045;114.818;192.217;39.0983;83.8;138.9055;6.941;262;174.967;258;24.305;54.938;95.94;268;14.0067;22.9897;92.9064;144.24;20.1797;58.6934;259;237;15.9994;190.23;30.9738;231.0359;207.2;106.42;145;209;140.9077;195.078;244;226;85.4678;186.207;261;102.9055;222;101.07;32.065;121.76;44.9559;78.96;266;28.0855;150.36;118.71;87.62;180.9479;158.9253;98;127.6;232.0381;47.867;204.3833;168.9342;238.0289;50.9415;183.84;131.293;88.9059;173.04;65.39;91.224}
My first post showing the use of the LAMBDA function may not have wide utility, but I hope that it provides a sense of how powerful this function is.
Click the link below for the Excel file associated with this article, but realize that the formulas used here on only a beta version of Excel as of December 2020.
Excel For You 