Brand new in Excel for Office 365 (currently in Insiders Fast channel only)... a LET function. Any time that you have a formula that repeats the same calculation over and over again, the LET function will let you assign that intermediate result to variables!
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2325.
A first look at the LET function. Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen. Well this is super exciting.
Released to Insiders Fast yesterday, March 16th, 2020.
A brand new function in Excel called the LET function. The LET function.
Now LET allows us to define a variable in Excel and I'm going to give you 2 quick examples here and I hope down in the YouTube comments you'll give me examples of where you think this will work.
Alright, so I have an X value and a Y value and I'm gonna say that let let X equals C6 let Y equals C7 and I can keep adding pairs like a variable name and value, name and value, name and value and then after the final comma I have all of the calculation.
So here I'm taking the square root of X squared plus Y squared to get the hypotenuse in essence.
Now this particular one isn't that interesting, but here's the one that I think.
I can see that this would happen alright, so I have a VLOOKUP here.
The VLOOKUP is returning a lot of NA's because my table is really really small.
And what we used to do before we had =IFERROR( or =IFNA( is do this crazy thing where we say.
The ISNA( of the VLOOKUP.
If that's true, then we do "Not found" otherwise we do that.
We look up again.
So think about any formula where we're doing the exact same calculation twice right? Or multiple times.
While here's how LET is going to solve that.
So we say with LET: let price (that's a variable name) equal the VLOOKUP.
And then here's the calculation. If ISNA( or Price then 0.
Otherwise, Price Alright.
So we define it once and we don't have to put that formula in again and again and again.
This is really exciting brand new stuff from the Excel team and I've heard of this.
I've heard that they were thinking about this and I'm really excited that it showed up yesterday.
It should be at 100% of Insiders Fast, although with that being said I have another computer that doesn't have it.
So go update and see if you have it.
If you like these tips, please Subscribe and ring the Bell.
Feel free to post comments or your ideas of how to use this in the comments below.
For more tips like this, MrExcel 2020 Seeing Excel Clearly Click that "I" in the top right hand corner.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
A first look at the LET function. Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen. Well this is super exciting.
Released to Insiders Fast yesterday, March 16th, 2020.
A brand new function in Excel called the LET function. The LET function.
Now LET allows us to define a variable in Excel and I'm going to give you 2 quick examples here and I hope down in the YouTube comments you'll give me examples of where you think this will work.
Alright, so I have an X value and a Y value and I'm gonna say that let let X equals C6 let Y equals C7 and I can keep adding pairs like a variable name and value, name and value, name and value and then after the final comma I have all of the calculation.
So here I'm taking the square root of X squared plus Y squared to get the hypotenuse in essence.
Now this particular one isn't that interesting, but here's the one that I think.
I can see that this would happen alright, so I have a VLOOKUP here.
The VLOOKUP is returning a lot of NA's because my table is really really small.
And what we used to do before we had =IFERROR( or =IFNA( is do this crazy thing where we say.
The ISNA( of the VLOOKUP.
If that's true, then we do "Not found" otherwise we do that.
We look up again.
So think about any formula where we're doing the exact same calculation twice right? Or multiple times.
While here's how LET is going to solve that.
So we say with LET: let price (that's a variable name) equal the VLOOKUP.
And then here's the calculation. If ISNA( or Price then 0.
Otherwise, Price Alright.
So we define it once and we don't have to put that formula in again and again and again.
This is really exciting brand new stuff from the Excel team and I've heard of this.
I've heard that they were thinking about this and I'm really excited that it showed up yesterday.
It should be at 100% of Insiders Fast, although with that being said I have another computer that doesn't have it.
So go update and see if you have it.
If you like these tips, please Subscribe and ring the Bell.
Feel free to post comments or your ideas of how to use this in the comments below.
For more tips like this, MrExcel 2020 Seeing Excel Clearly Click that "I" in the top right hand corner.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
