Excel is a few programming languages, referred to as "micro languages". The one in the video is called Excel Formula Language. If you are into databases and you have PowerPivot add-in, you can also use DAX which is a mix of SQL and Excel Formula Language. Excel also has M Language, Excel Macro Language (for backward compatibility) and obviously VBA.
Excel, and many other Microsoft office applications, work with Visual Basic for Applications (VBA) which for all intents and purposes is a programming language that can be used in excel to make a large variety of programs
Also MS Office SDKs allow programming Excel add-ins and XLL libraries. Tbh this new feature is cool but not as groundbreaking as the many existing formula and programming features in Excel
I'm waking up in the morning, and seeing this video... I can't really believe myself. Many times I have got stuck when I needed to automate some of my works in excel, due to not finding a formula. This is now going to help me, i hope so. Thank you so much for bringing this Lambda function.
@@sohailahmed1351 My original comment was a bit of hyperbole, it's not *that* bad. But personally, I find it clunky, unintuitive, and frustrating to work with.
@@grimendancehall I don't still have to use an abacus. If I was required to use an abacus to get Excel to be reasonably useful then yeah, I would complain about that too.
Finally something new that is not annoying. Now there is a valid argument for migrating to the latest office version. This will render a lot of macros obsolete - good for security.
I'm a heavy user of Excel and have been working in finance for years, and while this function sounds cool, I can't for the life of me think of a situation where this would've come in handy. If you have any real life examples, I'd really be interested in hearing how you use it.
You could as a simple example define a function that calculates the floating average of a range of values and reuse it over and over again. instead of typing the whole formula you could just say something like "=FLOATAVRG(A1:A100)". That is what programmers do all time: define function to do something and reuse it but instead of having to learn VBA you can do it in excel syntax. Or you could do date formatting, validating values, convert values. The possiblities are nearly endless.
hi! im a chemical engineering student and we frequently use excel in all sorts of calculations using complex formulas, and especially in cases where we use equations derived from laws this function will come in very handy!
@@ananasbanana Visual Basic, a programming language supported by excel. You can use it to solve problems which would be difficult or impossible to solve with normal functions.
@@ananasbanana I think it stands for Visual Basic Access. I remember using it a long time ago for L4G programming on a Access database but other Office products started using it also for macro and function programming.
@@ananasbanana It comes built in to most Office applications though in the newest versions (I think from 2016 onwards, someone correct me on this) it has to be enabled as it can be a security risk. It was designed to be used under license with other applications but as far as I am aware no other applications use it outside of the Office range.
I am shocked that it took them that long to introduce the possibility of creating your own functions without having to use that massive PITA that is VBA
I guess I am so used to using VBA for recursion for so long that the last feature there seemed more cumbersome than just using a VBA user defined function.
Oh my gosh. This is so amazing. I have been waiting for something like this forever. Thank you for making my Saturday. I will be even more efficient LOL.
Omg.. it's much more complicated. Just lose the fear of creating a function in VBA, where you have more space to write and you can make comments to organize the code. I do this for litteraly 20 years. And if the input parameters depends on another cell you can use "Application.Volatile" to force update.
well it was done by VBA (you could write your own functions including data type operations), however, VBA has its issues (many being security) so by default, it is blocked unless the user activates it.
@Winston McGee Eeh no... Haskell came out in 1990. Python integrated lambda-functions 25 years ago. C++, C#, Javascript and many more have been using them for years.
I get that you have to show easy examples, but I think it distracts from why lamdas might be useful as you can do everything you showed directly (except for the recursion, which is a bit silliy for this particular application), e.g. just enter =A1:A3*B1:B3. The advantage only comes when you have functions that are much more complex but you re-use frequently.
I did realise this while learning the concept, but it's nice to get confirmation as it's a good feeling to know I derived it's implications without a use case Learning this stuff is fun
I guess this whole thing feels like a trailer hitch on a Prius, asking Excel to do a job meant for VBA. If the formula is simple, hiding it behind a custom function name only makes it more difficult to read without necessarily being more useful. If the formula is complex, I'm not sure why VBA wouldn't still be the better platform, being easier to work with and granting much more control to the developer, especially when we factor in VBA add-ins that can carry custom functions between all of your workbooks. And especially considering the atrocity that is editing formulas in the "Refers to" boxes. I'm not debugging a lambda with 12 nested functions in that thing, accidentally hitting the arrow key and having it insert gibberish in the middle.
@flo xbr perhaps you could create an ‘advanced’ series of presentations covering more in-depth examples. Please reply when you’ve posted your first session so we can check it out.
@@CLove511 For basically that reason. You can do a lot more with VBA, including things which you wouldn't necessarily trust coming from some random. I am assuming the lambda functions are meant to be "safe", so wont need saving as an excel macro enabled workbook.
6:18; I find that a double nested if or while loop with the length of TestString and IlligalChars adds significantly more clarity and understanding for this example.
Actually it's more like the double parentheses are: first to build the Lambda expression, then the next set is to execute the expression. Lisp/Scheme might look like this: (funcall (lambda (x y) (* x y)) 6 8 ) Compared to the XL expression: =Lambda(x, y)(6, 8)
That's absolutely amazing. One year ago I created a vba code in order to export a series of files in different directories and I had to set manually all the possible conditions to avoid the usage of an illegal character.
thats called not knowing how to program. not trying to overly insult but thats the key. a million different ways to do something. obviously some are much better than others. thats why this field is so lucrative... not cuz its hard to write a for loop and add subdirectory to name and make a new file.
Wow Knowing this was such a great relief, I used to write some complicated formulas, multiple times in a sheet at many different cells & sheets.. now this gives me rest.. For Example: I have to write Date in Any format to Mmm-YY using IF formula 12 times.. every were in a sheet.. but Lambda I can jus write it once in Excel Names and call it every were I want.. 👍👍👍
I haven’t finished watching but I don’t like when super simple examples are used. Give me a real world application where it will save a significant amount of time
I was almost able to follow along with what you were showing in this video. You should probably add four or five more advertisements to remedy that. Thank you.
i remember, excel had impressive vector graphics edditing capabilities in 2003 version. in 2020 ms has added custom function definitions inside cells. a very logical way of spreadsheet editor development.
Interesting naming, because when i did calculus back in the days "Lambda" was the wavelength. Also used when i wanted to transpose a wavesheet and "see" it's values from the different angle. Funny how things change because ,, well i dont know why.
Thanks Jason. Looks awesome! I've been waiting for it to hit my Insider laptop so I can tinker with it.. still waiting. Thanks for the preview :)) Thumbs up!!
It is a powerful function; however, it is still only available to insiders. I'll have to come back and watch this video in six months when it finally becomes available to us. I had to do the same thing with XLOOKUP and LET.
Yes. I generally try to get these new function training videos out early so people know what is coming in the future. My tips and tricks videos reference the released functions.
@@SeleTraining Please do not take this as criticism of you. I'm more irritated at Microsoft. They made LET sound like it was available, and I was all excited. Only to find it was only available for insiders, and I had to wait 3 months or so. I've the same irritation about LAMBDA. Have a great day!
There were times, 20+ years back, when one could do this with a VBA macro. I know well the reasons, why macros are in disrepute, but seeing custom defined functions as a new feature today is kind of depressing. And not only as a reminder, how old I am already. :)
Apart from the end example using the IF statement, I dont see the benefits of this function? For example your multiplyxy example, surely it would just be easier and quicker to do =A2*B2? Am I missing something?
If you have a complex formula used throughout your spreadsheet it could save a bunch of time. Also, consider if you had to update the function you could then do it in one place rather than in every cell it is used.
It allows you to perform a transformation and then use that value as a variable without needing to recalculate that value for each time you want to use it.
@@SeleTraining This looks like the key detail. You put "=Property_Tax_Formula(A2,B2,C2)" in the D2 cell, and in the Name Manager screen you assign "Property_Tax_Formula" to a significant equation that you don't want to type out in each cell. This avoids the potential problem where the equation is changed and you might copy the new equation to almost all the locations where it is used but miss a few. By using the Name Manager to change the equation, you ensure that everywhere using 'Property_Tax_Formula' will be using the new equation. For example, you might have a property tax calculation that takes in total land area, waterfront property, and zoning code to determine the base tax value for a piece of land. One year the variables in the formula to determine the tax are changed. If you just copy and pasted the formula, you might make a mistake. But by changing the formula in the Name Manager screen you ensure that all cells using that formula will use the updated formula. (If I am understanding it correctly)
MS Excel has been able to provide results such as this for decades - using either the macro cells or the later VBA macro scripts methods. Both these methods are still available but should be used with caution if sourced from a 3rd party due to the possibility of the script being malicious. As the new LAMBDA() function seems to be called and run from a cell, the complexity of the calculation will be somewhat limited. Most recent versions of MS Excel have a 32,767 characters limitation per cell, previous versions were limited to 255 characters per cell. Programmatically, having multiple clones or slight variants of LAMBDA() functions will be less elegant than writing and calling 1 VBA script. Debugging will be awkward as each cell will only be able to be viewed and edited 1 at a time. Like many MS improvements, a workbook that contains this new LAMBDA() function will not be backwards-compatible with organisations that still use earlier versions of MS Excel. Perhaps other MS Excel users and programmers will have different experiences and opinions on this, which of course I welcome. My own opinion is that LAMBDA() might be useful for some very small-scale and quick fixes but that for consistent and structured sheets, other methods should be employed.
LAMBDA is a good answer for non programmers that are not comfortable with VBA. You also can define the LAMBDA formula in the name manager so it doesn’t have to be confined to a cell.
Here I am, randomly found this video, thinking thermal conductivity was implemented as a function, I really was excited to see how....turns out name lambda can be used for different purposes, who would've thought. It's way easier to just write a vba script that'll do the same thing, on the other hand good function for people without programming background.
WOW! I've been using VBA functions to do the same thing but you need to save the workbook as macro enabled, inviting possible security issues. With the lambda function you can bypass VBA functions completely avoiding any security issues... Correct me if I am wrong!? ;-)
lamda is such an old concept that I think microsoft intentionally delays adding new features so they have something to sell next year: buy Excel 2021...it now has a feature available a decade ago lol
We're still using MS Office 2013 at my job. Can't wait to use this in 2037!
LOL.
My office still even use 2007 version lol
I can't stand on it so i decided to upgrade mine into 365 version
😂
Using 2013 here also, but finally in 2 weeks we are going to 365
😂
The same situation...😕
I don't know why UA-cam recommended me this, but I'm glad it did.
Same!
Same here my friend 😃
I've been googling SQL, python and databases. I'm guessing that's why I got suggested this video
@@smd7
Yeah, I can see the connection there.
I wasn't looking at anything related to computing before this recommendation, other than games.
Because the author paid for it
At this point, excel is a programming language in its own right.
Excel is a few programming languages, referred to as "micro languages". The one in the video is called Excel Formula Language. If you are into databases and you have PowerPivot add-in, you can also use DAX which is a mix of SQL and Excel Formula Language. Excel also has M Language, Excel Macro Language (for backward compatibility) and obviously VBA.
Just emulate macOS
@@ultrio325 what? Why would I do that?
Excel, and many other Microsoft office applications, work with Visual Basic for Applications (VBA) which for all intents and purposes is a programming language that can be used in excel to make a large variety of programs
Also MS Office SDKs allow programming Excel add-ins and XLL libraries.
Tbh this new feature is cool but not as groundbreaking as the many existing formula and programming features in Excel
I'm waking up in the morning, and seeing this video... I can't really believe myself. Many times I have got stuck when I needed to automate some of my works in excel, due to not finding a formula. This is now going to help me, i hope so. Thank you so much for bringing this Lambda function.
Glad it helped!
This went from very easy functions to mind blown very quickly.
Yeah no kidding, after the first minute I was like "this is nothing" to the next minute "this is the best thing ever"
...and then on the last formula my brain had a "recursion" and turned inside out - oh the pain (Dr.Smith)
That's a nice alternative to using VBA, when your client complains that he/she can't activate sheet content, or don't know how to.
VBA is an atrocity as well, so any alternative is nice by default.
@@skipfred I am from a non programming background myself, but I enjoy VBA. Why do people hate it?
@@sohailahmed1351 My original comment was a bit of hyperbole, it's not *that* bad. But personally, I find it clunky, unintuitive, and frustrating to work with.
@@skipfred its also like 100 years old lmfao.... u frustrated with the scalability of an abacus aswell?
@@grimendancehall I don't still have to use an abacus. If I was required to use an abacus to get Excel to be reasonably useful then yeah, I would complain about that too.
Will have to rewatch this in 5-6 years when I upgrade to the 2021 Version
Finally something new that is not annoying. Now there is a valid argument for migrating to the latest office version. This will render a lot of macros obsolete - good for security.
I have been begging for this for years. I'm so happy they put it in. I hate having to create custom functions in VBA just for simple actions.
I'm a heavy user of Excel and have been working in finance for years, and while this function sounds cool, I can't for the life of me think of a situation where this would've come in handy. If you have any real life examples, I'd really be interested in hearing how you use it.
Check out my video with more examples. ua-cam.com/video/Og9TYOWKXt4/v-deo.html
You could as a simple example define a function that calculates the floating average of a range of values and reuse it over and over again. instead of typing the whole formula you could just say something like "=FLOATAVRG(A1:A100)". That is what programmers do all time: define function to do something and reuse it but instead of having to learn VBA you can do it in excel syntax. Or you could do date formatting, validating values, convert values. The possiblities are nearly endless.
This is because you’re not a programmer.
hi! im a chemical engineering student and we frequently use excel in all sorts of calculations using complex formulas, and especially in cases where we use equations derived from laws this function will come in very handy!
optimizations, dynamic programming, search algorithms, etc...
While the content is concise and helpful, the presentation is incredible with impressive voice:)
This severely reduces the amount of vba needed for certain sheets, great update and great video
What’s VBA
@@ananasbanana Visual Basic, a programming language supported by excel. You can use it to solve problems which would be difficult or impossible to solve with normal functions.
@@ananasbanana I think it stands for Visual Basic Access. I remember using it a long time ago for L4G programming on a Access database but other Office products started using it also for macro and function programming.
@@lajya01 Is it separate to Excel? Have to pay extra for it?
@@ananasbanana It comes built in to most Office applications though in the newest versions (I think from 2016 onwards, someone correct me on this) it has to be enabled as it can be a security risk. It was designed to be used under license with other applications but as far as I am aware no other applications use it outside of the Office range.
The recursion bit is rad, I've wanted something like that in excel for a while.
I am shocked that it took them that long to introduce the possibility of creating your own functions without having to use that massive PITA that is VBA
Holy guacamole, I've been waiting for something like this for AGES. I love Excel. :)
hundreds of excel sheet ideas failed because of loops thank you for this illustration
Recursion within formulas is the biggie here. Thanks for sharing.
I guess I am so used to using VBA for recursion for so long that the last feature there seemed more cumbersome than just using a VBA
user defined function.
Nice! I thought recursion was sought only by javascript Google sheets programers
agreed, but it allows you to share the file with the office without macros. But yeah... VBA all the way.
If only it were possible to give multiple thumbs ups... Thanks for your teachable explanation.
So nice of you
One formula i had been praying for years...🔥🔥
Programmers: "First time?"
Naming functions to remember them? Cool!
Loving udfs without vba
Lambda really looks powerful. I could see myself using it in some of my reports.
A very cool new addition to Excel.
I'm trying to figure out in what report I'll use this and how.
@@AdilParray It does make it easy to shrink complex formulas.
@@SeleTraining Good point!
@@SeleTraining just the ability to abstract logic into a named function without vba is so powerful
God bless my successor when I transition my excel sheets with all these lambda defined functions !
Documentation and notation.
@@Capt_AwesomePants nah. it's been working in production for two years now... Good luck!
Actually, it will make their life easier compared to them trying to make sense of your arcane, uncommented VBA spaghetti
4:52 what? How cool is that… this is officially the new thing that I learned today!
Excellent!
Time to port Doom to excel using lambda functions
underrated comment
I am from a non programming background, but I enjoy VBA. Why do people hate it? It has made querying back end so easy !!!
Oh my gosh. This is so amazing. I have been waiting for something like this forever. Thank you for making my Saturday. I will be even more efficient LOL.
You would have loved Public Function in VBA
Omg.. it's much more complicated. Just lose the fear of creating a function in VBA, where you have more space to write and you can make comments to organize the code. I do this for litteraly 20 years.
And if the input parameters depends on another cell you can use "Application.Volatile" to force update.
all this functionality..I used VBA to do the same. Recursion..mind blown!
In order to understand recursion, you first need to understand recursion.
@@ellisz5972 haha exactly
Finally something that is super useful that’s going to actually save me time!
Its really a powerful function. Saves alot of work by writing off complex formula for data calculation
Just a query, how did the median value automatically pop up? Is it referring from some location?
How wasn’t this a function 20 years ago...this feels super obvious.
well it was done by VBA (you could write your own functions including data type operations), however, VBA has its issues (many being security) so by default, it is blocked unless the user activates it.
@Winston McGee Eeh no... Haskell came out in 1990. Python integrated lambda-functions 25 years ago. C++, C#, Javascript and many more have been using them for years.
@Winston McGee If we talk about existence, Lisp used them 60 years ago. I was only talking in a modern context.
The (x,y) examples made me feel younger: it reminded me of the art of programming an HP calculator.
I was like.. Yea ok.. Until you got to recursion. Have to try this out. Dang. I like that they're still pushing. Let() has been great haha.
I must need new glasses. I read the title as Excel LAMBADA Function and my mind went in a totally different direction.
well you could name it that way with naming your own funktion and then callig it :D
Now that song is stuck in my head... For eternity!
The Lambada function is cool, but the Macarena function blows it away
🤣
I get that you have to show easy examples, but I think it distracts from why lamdas might be useful as you can do everything you showed directly (except for the recursion, which is a bit silliy for this particular application), e.g. just enter =A1:A3*B1:B3. The advantage only comes when you have functions that are much more complex but you re-use frequently.
True!
I did realise this while learning the concept, but it's nice to get confirmation as it's a good feeling to know I derived it's implications without a use case
Learning this stuff is fun
I guess this whole thing feels like a trailer hitch on a Prius, asking Excel to do a job meant for VBA.
If the formula is simple, hiding it behind a custom function name only makes it more difficult to read without necessarily being more useful.
If the formula is complex, I'm not sure why VBA wouldn't still be the better platform, being easier to work with and granting much more control to the developer, especially when we factor in VBA add-ins that can carry custom functions between all of your workbooks.
And especially considering the atrocity that is editing formulas in the "Refers to" boxes. I'm not debugging a lambda with 12 nested functions in that thing, accidentally hitting the arrow key and having it insert gibberish in the middle.
@flo xbr perhaps you could create an ‘advanced’ series of presentations covering more in-depth examples. Please reply when you’ve posted your first session so we can check it out.
@@CLove511 For basically that reason. You can do a lot more with VBA, including things which you wouldn't necessarily trust coming from some random.
I am assuming the lambda functions are meant to be "safe", so wont need saving as an excel macro enabled workbook.
Your speaking skill is greater than excel skill...
Bet when lambda get mainstream, programmers will have a lot of fun with this function
I got really excited that excel might support arrow syntax
One like is not enough for this video. Wow! Can't wait to use lamda function
Thanks
I would like to see more examples of this.
Do you hate microwaves ?
@@BHARGAV_GAJJAR what you think?
Awesome he said as he sat at work. This time he was really gonna do it.
The bridge was right there on his home commute
6:18;
I find that a double nested if or while loop with the length
of TestString and IlligalChars adds significantly more
clarity and understanding for this example.
Thanks a lot from Belgrade, that's what I need
i love that it even uses a LISP/scheme-like parenthesis syntax
Actually it's more like the double parentheses are: first to build the Lambda expression, then the next set is to execute the expression.
Lisp/Scheme might look like this:
(funcall
(lambda (x y) (* x y))
6 8
)
Compared to the XL expression:
=Lambda(x, y)(6, 8)
That's absolutely amazing.
One year ago I created a vba code in order to export a series of files in different directories and I had to set manually all the possible conditions to avoid the usage of an illegal character.
thats called not knowing how to program. not trying to overly insult but thats the key. a million different ways to do something. obviously some are much better than others. thats why this field is so lucrative... not cuz its hard to write a for loop and add subdirectory to name and make a new file.
Wow Knowing this was such a great relief, I used to write some complicated formulas, multiple times in a sheet at many different cells & sheets.. now this gives me rest..
For Example:
I have to write Date in Any format to Mmm-YY using IF formula 12 times.. every were in a sheet.. but Lambda I can jus write it once in Excel Names and call it every were I want..
👍👍👍
Usage of recursion function is basic for programming, nice Excel finally gets here.
Agreed. It took way too long
Nice introduction of the new function, build up customized embedded formulas become easier and better readable. 👍
Exploding dice here I come! I should be able to use this to have exploding dice coded in 1 cell! That is super exciting!
Thebest.. thank you.. this is the function i was looking for recursion that i had hard time to think of
You are a fantastic teacher. Bravo.
Thank you! 😃
this brings up some bad memories from my haskell class in university. i guess i have to get back into lambda calculus...
I took 3 terms of Calculus. Can't remember anything now...
haahah lol😅
but Haskell and Lambda Calculus still are so beautiful i think. i love em
I haven’t finished watching but I don’t like when super simple examples are used. Give me a real world application where it will save a significant amount of time
Take a look at my LAMBDA more examples video. You can see some more complex uses.
A very clear and informative description of this incredible new Excel super function. Thanks!
Glad it was helpful!
So looking forward to the lambda function!!
I was almost able to follow along with what you were showing in this video. You should probably add four or five more advertisements to remedy that. Thank you.
Install uBlock origin already
Wow, liked and subed, thank you mate, this gold keep it coming
Excel complete Training basic to advance level
That is soooooo much easier than a VBA function!
i remember, excel had impressive vector graphics edditing capabilities in 2003 version. in 2020 ms has added custom function definitions inside cells. a very logical way of spreadsheet editor development.
LOL
Wow... great function in excel... thanks for sharing this knowledge with us and your wonderful presentation...
I'm gonna show this to my PlusOne
Interesting naming, because when i did calculus back in the days "Lambda" was the wavelength. Also used when i wanted to transpose a wavesheet and "see" it's values from the different angle.
Funny how things change because ,, well i dont know why.
Google what's lambda in any programming language.
And I thought I was a pro in excel. There’s a saying “The more you know the more you’ll realise you don’t know…”
Same here. I’ve been using Excel since ‘97 and MS Works and Lotus 1-2-3 before that. Videos like this put me to shame.
This function is so powerful that there even is a song about it written by Kaoma in 1989!
I have been waiting lambda calculus in spreadsheet for a long time... now I have to wait libreoffice include this
The best tool for quickly playing around with small amounts of data just got a whole lot better!
Lambda, Lambda… Sounds like the latest installment of the “Revenge of the Nerds” movie franchise. 🤪
Many thanks, upper level of formulas
Thanks Jason. Looks awesome! I've been waiting for it to hit my Insider laptop so I can tinker with it.. still waiting. Thanks for the preview :)) Thumbs up!!
Thanks Wayne
Good function. Thanks for the good explanation
It is a powerful function; however, it is still only available to insiders. I'll have to come back and watch this video in six months when it finally becomes available to us. I had to do the same thing with XLOOKUP and LET.
Yes. I generally try to get these new function training videos out early so people know what is coming in the future. My tips and tricks videos reference the released functions.
@@SeleTraining Please do not take this as criticism of you. I'm more irritated at Microsoft. They made LET sound like it was available, and I was all excited. Only to find it was only available for insiders, and I had to wait 3 months or so. I've the same irritation about LAMBDA. Have a great day!
@@jtmh31 If you have a Microsoft 365 Family or Personal subscription, it's easy to become an insider. See insider.office.com/en-us/join/windows
do you know the timetable for us normies to get access to lambda?
@@maxheithmar334 Microsoft never says when they will release it. It is in beta but it could be days or months.
😎 Cool ! Took till 2021 to get this math power... Go Branden!
There were times, 20+ years back, when one could do this with a VBA macro. I know well the reasons, why macros are in disrepute, but seeing custom defined functions as a new feature today is kind of depressing. And not only as a reminder, how old I am already. :)
Great explanation of the LAMBDA function
Thanks!
Most Powerful and Excel are two words which can not be combined! Lambdas in C++ are maybe powerful.
excel (and, somewhat surprisingly, powerpoint) are turing-complete, so in principle, they can compute anything that is computable
I had to startup Excel and check it out. Nice!!
this is actually pretty sweet. gonna start using this alot
Thank you, powerful function!!! Auto Like 👍🏻 & Save video
Apart from the end example using the IF statement, I dont see the benefits of this function? For example your multiplyxy example, surely it would just be easier and quicker to do =A2*B2? Am I missing something?
If you have a complex formula used throughout your spreadsheet it could save a bunch of time. Also, consider if you had to update the function you could then do it in one place rather than in every cell it is used.
It allows you to perform a transformation and then use that value as a variable without needing to recalculate that value for each time you want to use it.
@@SeleTraining This looks like the key detail. You put "=Property_Tax_Formula(A2,B2,C2)" in the D2 cell, and in the Name Manager screen you assign "Property_Tax_Formula" to a significant equation that you don't want to type out in each cell. This avoids the potential problem where the equation is changed and you might copy the new equation to almost all the locations where it is used but miss a few. By using the Name Manager to change the equation, you ensure that everywhere using 'Property_Tax_Formula' will be using the new equation.
For example, you might have a property tax calculation that takes in total land area, waterfront property, and zoning code to determine the base tax value for a piece of land. One year the variables in the formula to determine the tax are changed. If you just copy and pasted the formula, you might make a mistake. But by changing the formula in the Name Manager screen you ensure that all cells using that formula will use the updated formula.
(If I am understanding it correctly)
@@toddkes5890 Yes. You can also save a set of LAMBDA functions with a spreadsheet and then copy the spreadsheet whenever you need to use them.
feeling good to know about this formula
Really helpful tutorial :)
Thanks Tyler.
Fantastic 👍 use this often now
awesome...
very similar to python lambda
Intentionally so I believe
thanks you tube! this is exactly what i was looking for while searching for a covid variant.
When Excel implements sockets and smart pointers you know they are running out of feature ideas.
MS Excel has been able to provide results such as this for decades - using either the macro cells or the later VBA macro scripts methods.
Both these methods are still available but should be used with caution if sourced from a 3rd party due to the possibility of the script being malicious.
As the new LAMBDA() function seems to be called and run from a cell, the complexity of the calculation will be somewhat limited.
Most recent versions of MS Excel have a 32,767 characters limitation per cell, previous versions were limited to 255 characters per cell.
Programmatically, having multiple clones or slight variants of LAMBDA() functions will be less elegant than writing and calling 1 VBA script.
Debugging will be awkward as each cell will only be able to be viewed and edited 1 at a time.
Like many MS improvements, a workbook that contains this new LAMBDA() function will not be backwards-compatible with organisations that still use earlier versions of MS Excel.
Perhaps other MS Excel users and programmers will have different experiences and opinions on this, which of course I welcome.
My own opinion is that LAMBDA() might be useful for some very small-scale and quick fixes but that for consistent and structured sheets, other methods should be employed.
LAMBDA is a good answer for non programmers that are not comfortable with VBA. You also can define the LAMBDA formula in the name manager so it doesn’t have to be confined to a cell.
Ok but we still can’t still do a double vlookup
Yes you can, through index with multiple match
Yes you can with Xlookup.
Here I am, randomly found this video, thinking thermal conductivity was implemented as a function, I really was excited to see how....turns out name lambda can be used for different purposes, who would've thought.
It's way easier to just write a vba script that'll do the same thing, on the other hand good function for people without programming background.
having VBA in your file can be a pain if you're sharing the file (people don't activate VBA, mail apps send warning or even block the file..)
*Maniacally cackles in a 30 year old Lisp dialect*
Only 30? Lisp is older than that …
WOW! I've been using VBA functions to do the same thing but you need to save the workbook as macro enabled, inviting possible security issues.
With the lambda function you can bypass VBA functions completely avoiding any security issues... Correct me if I am wrong!? ;-)
This is just like Anonymous/Lambda Functions in Common Lisp lol
This.. solves so many of my issues
was excited about this and then i checked, my company still deployed 2008version :(
Bummer
That's really old 😕
u mean 2007. 2008 dnt exist
Excellent and innovative too. But it will take some time to adopt it in my D t D works. Thanks for your help. Cheers!
Python coders: “noob”
Python coders: “noob”
Non-coders to Python coders: “STFU, nerd”
Excel finally implementing features from the... *checks notes* 70s.
lamda is such an old concept that I think microsoft intentionally delays adding new features so they have something to sell next year: buy Excel 2021...it now has a feature available a decade ago lol
lambda is core to some languages that are more than 60 years old now.
Great stuff this Lambda function, spreadsheets on the move!!! Cleaning data powerful tool.
Yes!
Oh well, another nail in the VBA coffin. It’s becoming a less and less useful skill.
Good riddance!