Ground-breaking news in Excel. The partner to the LET function was just released. Check out the new LAMBDA function in Excel. This makes the Excel formula language Turing Complete. Store formula logic in a name and pass parameters to that logic.
Link to the Brian Jones article: Announcing LAMBDA: Turn Excel formulas into custom functions
Table of Contents
(0:00) Introduction to Lambdas and Revenge of the Nerds
(0:26) Alonzo Church and Alan Turing
(1:52) First LAMBDA: Case Quantity
(3:10) Hypotenuse of a Triangle using HYP
(3:49) Area of a Pizza
(4:55) Using Data Types - Distance Between Two Cities
(5:52) Recursion example from Brian Jones
(6:24) Wrap-up
Link to the Brian Jones article: Announcing LAMBDA: Turn Excel formulas into custom functions
Table of Contents
(0:00) Introduction to Lambdas and Revenge of the Nerds
(0:26) Alonzo Church and Alan Turing
(1:52) First LAMBDA: Case Quantity
(3:10) Hypotenuse of a Triangle using HYP
(3:49) Area of a Pizza
(4:55) Using Data Types - Distance Between Two Cities
(5:52) Recursion example from Brian Jones
(6:24) Wrap-up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2381. The Excel LAMBDA function is going to change everything.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
These LAMBDA functions debuted yesterday.
My first question is “what is a LAMBDA?” If you are a fan of movies and Revenge of the Nerds. It has NOTHING to do with their fraternity LAMBDA LAMBDA LAMBDA.
Certainly you've heard of Alan Turing.
His professor, Alonzo Church invented something called Lambda calculus and the LAMBDA function is named after Lambda calculus.
To simplify it in English: it takes your VBA User-Defined Functions and it moves them right into the Name Manager.
Which means that if you ever move to Excel Online you don't have to worry about the fact that VBA is not there.
You won't have to learn TypeScript. It's pretty cool. These are super-advanced.
There are all kinds of examples out there already on the Internet just less than 24 hours after it comes out.
Including the ability to use recursion within a formula. Now, let's start with something simple.
I used to do Power Excel seminars all the time.
They would tell me that they had some number of people there and I would have to ship books.
But the place where my books were stored only shipped full cases. I was always trying to calculate how many cases do I need to ship?
It's not exactly MROUND. It would have to be like MROUNDUP. It's not a hard calculation, right?
So so if we need 54 books and they come 24 to a case how many full cases is that?
Well you do 2.25 with A5 divided by B5.
That's how many cases but then use ROUNDUP of that. Alright, so I am going to take that logic and encapsulate it in a LAMBDA function.
When you first build your LAMBDA function you can build it right here in the grid and actually test it. But that's not where it's going to live.
Alright, so over here we have our our LAMBDA function and the first names are variables. The input variables.
Alright so we're going to pass it a Quantity and a Case Size. And then the last argument is the logic.
So I encapsulate the the logic from over here.
We are going to round up the quantity divided by Case Size to zero decimal places. And then let's do a little test here. We need six the case size is 24.
And that should say that we need one case. Alright so once you know that it's working.
And this is pretty cool.
What you're going to do is you're going to come in here and copy from either the Formula Bar or Edit Mode. Here. Ctrl C. And then go into Formulas, Define Name.
Give it a great name. And paste your formula right there.
Now, I've already done that right here as CASES. So the LAMBDA I give it quantity and case size and it returns this formula for me.
Alright so now I have the ability to anywhere in a spreadsheet in this workbook use =CASES of how many I need 54, what's the case size?
And it gives me that answer without me having to maintain that logic throughout the spreadsheet. It is only maintained in one place.
The Pythagorean Theorem. What's the length of the hypotenuse of a right triangle?
It is A squared plus B squared. Take the square root of that whole thing. Alright so we have Side A is 3.
Side B is 4. The hypotenuse is A squared plus B squared and then the square root of that. Alright.
So build that into LAMBDA. We pass it a and b. The square root of A Squared plus B Squared.
Do a little test here with three and four and now we get the correct answer of five.
So I stored that in a name called HYP for hypotenuse. Pass it any set of a and b and it'll calculate the hypotenuse for me.
My other favorite example from when I used to actually work in an office.
You know the big question was how many pizzas should we order for the staff meeting?
And is it better to order a 12-inch pizza or a 16-inch pizza?
A 16 inch pizza doesn't sound that much larger than 12 inch pizza you know but it really is when you calculate the area. So 12 inch pizza.
Divide by two to get the radius. PI times the radius squared. Alright build that whole thing in a LAMBDA.
So I pass it the diameter of the pizza and then here check it out LAMBDA does work with the LET function.
So the Radius is Diameter divided by 2. And then calculate PI times radius squared.
Do a little test with a 10 inch pizza. I get the same answer.
And then I have a function called PizzaArea. Alright. And again this is going to work on any worksheet in this workbook.
If i would copy a sheet to a new workbook of course all of the names are going to come along with it so it's almost like having a reusable library of user-defined functions without writing any VBA. You're just using straight formula logic. Alright and these are brilliant.
You know we have these new data types. You can define something as Geography.
Alright so here I have Cleveland and Orlando and I can get out of that Equal A2.Latitude Equal A2.Longitude.
The Orlando latitude and longitude. And of course this calculation is out on the Internet.
It's it's a complicated calculation. And this is using the radius of the Earth as 3959. I've rounded that.
It's off by a fraction of a mile. So here's here's the formula that I am never going to remember.
But I can encapsulate that in a LAMBDA.
Where I ask for From and To.
And there's all the formula logic that again gets saved in the Name Manager under Distance.
And then I can just use any two city pairs and get the distance in miles from those places.
I am going to put some links down in the YouTube description.
You have to check out this article from Brian Jones on the Excel team that came out yesterday. And his example here with recursion.
Alright so he has a string and he wants to get rid of those characters and he stored that in a LAMBDA called ReplaceCharacters.
And inside that LAMBDA is a call back to ReplaceCharacters. So what it's doing essentially is first it removes the one.
Then the two. Then the three.
Then the four until it gets down to the point where they've gone through all the illegal characters. So it's actually looping right inside the formula.
I've never seen so many Excel MVPs write so many articles on the same day about something.
This has really caught the the attention of a lot of people. Check out the link below.
Read the article. Really amazing. Right now they are in the beta channel of Office Insiders.
It might be time for you to switch to Office Insiders and check out that beta channel just to give these a try.
Well, hey, if you like these tips down below the video please Like, Subscribe, and Ring the Bell. Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
These LAMBDA functions debuted yesterday.
My first question is “what is a LAMBDA?” If you are a fan of movies and Revenge of the Nerds. It has NOTHING to do with their fraternity LAMBDA LAMBDA LAMBDA.
Certainly you've heard of Alan Turing.
His professor, Alonzo Church invented something called Lambda calculus and the LAMBDA function is named after Lambda calculus.
To simplify it in English: it takes your VBA User-Defined Functions and it moves them right into the Name Manager.
Which means that if you ever move to Excel Online you don't have to worry about the fact that VBA is not there.
You won't have to learn TypeScript. It's pretty cool. These are super-advanced.
There are all kinds of examples out there already on the Internet just less than 24 hours after it comes out.
Including the ability to use recursion within a formula. Now, let's start with something simple.
I used to do Power Excel seminars all the time.
They would tell me that they had some number of people there and I would have to ship books.
But the place where my books were stored only shipped full cases. I was always trying to calculate how many cases do I need to ship?
It's not exactly MROUND. It would have to be like MROUNDUP. It's not a hard calculation, right?
So so if we need 54 books and they come 24 to a case how many full cases is that?
Well you do 2.25 with A5 divided by B5.
That's how many cases but then use ROUNDUP of that. Alright, so I am going to take that logic and encapsulate it in a LAMBDA function.
When you first build your LAMBDA function you can build it right here in the grid and actually test it. But that's not where it's going to live.
Alright, so over here we have our our LAMBDA function and the first names are variables. The input variables.
Alright so we're going to pass it a Quantity and a Case Size. And then the last argument is the logic.
So I encapsulate the the logic from over here.
We are going to round up the quantity divided by Case Size to zero decimal places. And then let's do a little test here. We need six the case size is 24.
And that should say that we need one case. Alright so once you know that it's working.
And this is pretty cool.
What you're going to do is you're going to come in here and copy from either the Formula Bar or Edit Mode. Here. Ctrl C. And then go into Formulas, Define Name.
Give it a great name. And paste your formula right there.
Now, I've already done that right here as CASES. So the LAMBDA I give it quantity and case size and it returns this formula for me.
Alright so now I have the ability to anywhere in a spreadsheet in this workbook use =CASES of how many I need 54, what's the case size?
And it gives me that answer without me having to maintain that logic throughout the spreadsheet. It is only maintained in one place.
The Pythagorean Theorem. What's the length of the hypotenuse of a right triangle?
It is A squared plus B squared. Take the square root of that whole thing. Alright so we have Side A is 3.
Side B is 4. The hypotenuse is A squared plus B squared and then the square root of that. Alright.
So build that into LAMBDA. We pass it a and b. The square root of A Squared plus B Squared.
Do a little test here with three and four and now we get the correct answer of five.
So I stored that in a name called HYP for hypotenuse. Pass it any set of a and b and it'll calculate the hypotenuse for me.
My other favorite example from when I used to actually work in an office.
You know the big question was how many pizzas should we order for the staff meeting?
And is it better to order a 12-inch pizza or a 16-inch pizza?
A 16 inch pizza doesn't sound that much larger than 12 inch pizza you know but it really is when you calculate the area. So 12 inch pizza.
Divide by two to get the radius. PI times the radius squared. Alright build that whole thing in a LAMBDA.
So I pass it the diameter of the pizza and then here check it out LAMBDA does work with the LET function.
So the Radius is Diameter divided by 2. And then calculate PI times radius squared.
Do a little test with a 10 inch pizza. I get the same answer.
And then I have a function called PizzaArea. Alright. And again this is going to work on any worksheet in this workbook.
If i would copy a sheet to a new workbook of course all of the names are going to come along with it so it's almost like having a reusable library of user-defined functions without writing any VBA. You're just using straight formula logic. Alright and these are brilliant.
You know we have these new data types. You can define something as Geography.
Alright so here I have Cleveland and Orlando and I can get out of that Equal A2.Latitude Equal A2.Longitude.
The Orlando latitude and longitude. And of course this calculation is out on the Internet.
It's it's a complicated calculation. And this is using the radius of the Earth as 3959. I've rounded that.
It's off by a fraction of a mile. So here's here's the formula that I am never going to remember.
But I can encapsulate that in a LAMBDA.
Where I ask for From and To.
And there's all the formula logic that again gets saved in the Name Manager under Distance.
And then I can just use any two city pairs and get the distance in miles from those places.
I am going to put some links down in the YouTube description.
You have to check out this article from Brian Jones on the Excel team that came out yesterday. And his example here with recursion.
Alright so he has a string and he wants to get rid of those characters and he stored that in a LAMBDA called ReplaceCharacters.
And inside that LAMBDA is a call back to ReplaceCharacters. So what it's doing essentially is first it removes the one.
Then the two. Then the three.
Then the four until it gets down to the point where they've gone through all the illegal characters. So it's actually looping right inside the formula.
I've never seen so many Excel MVPs write so many articles on the same day about something.
This has really caught the the attention of a lot of people. Check out the link below.
Read the article. Really amazing. Right now they are in the beta channel of Office Insiders.
It might be time for you to switch to Office Insiders and check out that beta channel just to give these a try.
Well, hey, if you like these tips down below the video please Like, Subscribe, and Ring the Bell. Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
