The new function, LAMBDA, is available for any Microsoft 365 subscribers who opt into the Office Insiders program’s Beta Channel. It’s named in honor of Princeton University mathematician Alonzo Church, who invented lambda calculus in 1936. Church was the doctoral advisor of Alan Turing, the famous mathematician who proposed the Turing machine to perform calculations long before modern computers were invented.
USING LAMBDA
Say that you have any complex calculation where you might need to pass variables to the calculation. You can store the variable names and the logic inside the LAMBDA function. After testing in the worksheet, you create a Name and store the logic in the name.
Let’s start with a simple example. In a right triangle, the length of the hypotenuse is the square root of A squared plus B squared. To calculate the hypotenuse, you would use a formula such as =LAMBDA(A,B,SQRT(A^2+B^2)).
In this formula, the first two arguments are variables to hold values that will be passed to the function. The final argument is the logic to perform the calculation. You aren’t limited to just two input variables. Everything up to the last argument is considered a variable. The final argument always holds the calculation logic.
While you’re making sure the logic works, you can test the formula in the grid by adding the values for A and B in parentheses at the end of the formula. The formula =LAMBDA(A,B,SQRT(A^2+B^2))(3,4) would return the answer of 5.
But the real power of the LAMBDA function is when you store the LAMBDA in a Name. On the Formulas tab, choose Define Name. In the New Name dialog, create an easy-to-remember name for your function. You might call it HYPOTENUSE or, to make it even easier, shorten the name to HYP. Then paste the formula =LAMBDA(A,B,SQRT(A^2+B^2) into the “Refers to” box (see Figure 1). Click OK to create the name.
Now that the name is defined, you can enter a formula of =HYP(A2,B2) in the workbook, and Excel will calculate the result. The logic is stored once and can be reused in thousands of cells. If you need to change the logic in the future, edit the formula in the Name Manager.
In the past, people might have used VBA to write their own user-defined functions. These were fine when everyone was using a PC or a Mac. But slowly, the small percentage of people using Excel on an iPad or Android or Excel Online is growing, and those versions of Excel can’t support VBA. LAMBDA functions, however, can be used with Excel online or a mobile device.
COMPLEX USES
The team that developed LAMBDA is the same one that created data types and the LET function. That means LAMBDA was designed to work perfectly with Data Types. This enables more complex uses of LAMBDA.
Say that you have city pairs in column A and B. You convert these to Geography data types using the Data Type gallery on the Data tab of the ribbon. The Map icons in Figure 2 mean that each cell contains many fields about the city. Two of those fields are Latitude and Longitude.
There’s a complicated formula for calculating the distance between two cities using their respective latitude and longitude. You start out assuming the radius of the Earth is 3,959 miles. The formula uses functions for arcsine and cosine and is generally one that most people would never remember: =(3959*2)*ASIN(SQRT(0.5-COS((B2.Latitude-A2.Latitude)*PI()/180)/2+COS(A2.Latitude*PI()/180)*COS(B2.Latitude*PI()/180)*(1-COS((B2.Longitude-A2.Longitude)*PI()/180))/2)).
If you wrap that formula in the LAMBDA function as =LAMBDA(From,To,(3959*2)*ASIN(SQRT(0.5-COS((To.Latitude-From.Latitude)*PI()/180)/2+COS(From.Latitude*PI()/180)*COS(To.Latitude*PI()/180)*(1-COS((To.Longitude-From.Longitude)*PI()/180))/2))) and then store it in the Name Manager with an easy name like MILES, then you can calculate the distance between two cities using a formula as simple as =MILES(A2,B2).
To move that formula from one workbook to another, you simply copy any worksheet from the original workbook to a new workbook, and the names will travel along. With this portability, it’s possible to envision a company’s accounting department that has a library of custom LAMBDA functions that move from workbook to workbook.
Finally, a LAMBDA function can also call other LAMBDA functions, and it can recursively call itself. This lets you create a LAMBDA function that keeps calling itself until the task is complete.
SF SAYS
Mathematicians are celebrating that the LAMBDA function makes Excel “Turing Complete” and able to solve any mathematical problem.
March 2021
