Excel LAMBDA Function
HTML-код
- Опубликовано: 14 июл 2024
- Brand new in Excel for 2021 - LAMBDA is the most powerful function ever. LAMBDA lets you create your own functions and use them throughout a spreadsheet.
Brian's Blog Post: techcommunity.microsoft.com/t...
Dynamic Array Formulas Video: • Excel Dynamic Array Fo...
Data Types Video: • New Excel Linked Data ...
LAMBDA Helper Functions Video: • Excel LAMBDA Helper Fu...
LAMBDA More Complex Examples Video: • Excel LAMBDA Function ...
Support me with your Amazon purchases, click here: amzn.to/2Kxp533
Contents 0:00
Introducing LAMBDA 0:13
Name a Function 1:28
Two Input Variables 2:38
Inputs Using Ranges 3:37
Data Types 4:15
Recursion 5:57
Availability 7:51
Conclusion 8:04
#seletraining #excel #lambda 
Наука
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 RUclips 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!
Will have to rewatch this in 5-6 years when I upgrade to the 2021 Version
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.
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.
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.
Nice introduction of the new function, build up customized embedded formulas become easier and better readable. 👍
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)
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.
Finally something that is super useful that’s going to actually save me time!
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
One formula i had been praying for years...🔥🔥
The recursion bit is rad, I've wanted something like that in excel for a while.
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.
Naming functions to remember them? Cool!
Loving udfs without vba
Thebest.. thank you.. this is the function i was looking for recursion that i had hard time to think of
While the content is concise and helpful, the presentation is incredible with impressive voice:)
Holy guacamole, I've been waiting for something like this for AGES. I love Excel. :)
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'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. ruclips.net/video/Og9TYOWKXt4/видео.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...
Programmers: "First time?"
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
Time to port Doom to excel using lambda functions
underrated comment
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
woppie.. thanks for the last example you showed in the video. hope to find a use for lambda soon
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.
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.
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
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
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?
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.
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)
Your speaking skill is greater than excel skill...
Thanks a lot from Belgrade, that's what I need
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
🤣
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
Wow... great function in excel... thanks for sharing this knowledge with us and your wonderful presentation...
So looking forward to the lambda function!!
this is actually pretty sweet. gonna start using this alot
I would like to see more examples of this.
Do you hate microwaves ?
@@BHARGAV_GAJJAR what you think?
Recursion within formulas is the biggie here. Thanks for sharing.
I am from a non programming background, but I enjoy VBA. Why do people hate it? It has made querying back end so easy !!!
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..
👍👍👍
hundreds of excel sheet ideas failed because of loops thank you for this illustration
If only it were possible to give multiple thumbs ups... Thanks for your teachable explanation.
So nice of you
Excellent and innovative too. But it will take some time to adopt it in my D t D works. Thanks for your help. Cheers!
A very clear and informative description of this incredible new Excel super function. Thanks!
Glad it was helpful!
Really helpful tutorial :)
Thanks Tyler.
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.
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
I have been waiting lambda calculus in spreadsheet for a long time... now I have to wait libreoffice include this
Great explanation of the LAMBDA function
Thanks!
Thank you sir, well done and much appreciated.
Very welcome
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.
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
One like is not enough for this video. Wow! Can't wait to use lamda function
Thanks
Such a nice way of explanation.
*Maniacally cackles in a 30 year old Lisp dialect*
Only 30? Lisp is older than that …
awesome...
very similar to python lambda
Intentionally so I believe
4:52 what? How cool is that… this is officially the new thing that I learned today!
Excellent!
Oh my god, I love this.
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.
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 job explaining this!
This.. solves so many of my issues
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.
I'm gonna show this to my PlusOne
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.
thanks you tube! this is exactly what i was looking for while searching for a covid variant.
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
Lambda, Lambda… Sounds like the latest installment of the “Revenge of the Nerds” movie franchise. 🤪
Many thanks, upper level of formulas
Exploding dice here I come! I should be able to use this to have exploding dice coded in 1 cell! That is super exciting!
Excel complete Training basic to advance level
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
Very powerful, thanks for sharing
Thank you for such good info👍
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.
This is just like Anonymous/Lambda Functions in Common Lisp lol
Thanks for sharing wonderful function. I wish to use the function on office 365 asap.
Greatest teacher
Ok but we still can’t still do a double vlookup
Yes you can, through index with multiple match
Yes you can with Xlookup.
When Excel implements sockets and smart pointers you know they are running out of feature ideas.
A tear just came to my eye, thinking about how incredibly commonplace and boring this kind of operation is in R or Python. Why is anyone still using excel in 2021?
Great stuff this Lambda function, spreadsheets on the move!!! Cleaning data powerful tool.
Yes!
was excited about this and then i checked, my company still deployed 2008version :(
Bummer
That's really old 😕
u mean 2007. 2008 dnt exist
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.
Thank you, powerful function!!! Auto Like 👍🏻 & Save video
Usage of recursion function is basic for programming, nice Excel finally gets here.
Agreed. It took way too long
Python coders: “noob”
Python coders: “noob”
Non-coders to Python coders: “STFU, nerd”
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.
You got such Booming Voice and sound like GOD ...which makes me follow your Videos😉
I had to startup Excel and check it out. Nice!!
Oh well, another nail in the VBA coffin. It’s becoming a less and less useful skill.
Good riddance!
Don't see a need for this in Excel. All functionality remains the same, everything that is being done can already be achieved. If you need anything advanced then use visual basic in Excel, visual basic although developed decades ago is still years ahead of the lambda function
That’s true if you know VB. Most Excel users don’t and this is a simple method to build complex functions without having to code.
@@SeleTraining nice video well explained, I just don't follow the logic from Microsoft in creating such a function
👏🏼 you’re a definition of not giving up, well done! 😎
Thank you! 😁
Excellent, thank you.
I was comparing myself to god, when the VLOOKUP succeeded! LOL