Excel LAMBDA Function

[@andrewpeterst]

We're still using MS Office 2013 at my job. Can't wait to use this in 2037!

[@SeleTraining]

LOL.

[@flyer23ful]

My office still even use 2007 version lol I can't stand on it so i decided to upgrade mine into 365 version 😂

[@Mawds69]

Using 2013 here also, but finally in 2 weeks we are going to 365

[@wjb111]

😂

[@Rice0987]

The same situation...😕

[@OriginalPiMan]

I don't know why KZbin recommended me this, but I'm glad it did.

[@alexandersetuain3919]

Same!

[@prateekketkar4022]

Same here my friend 😃

[@smd7]

I've been googling SQL, python and databases. I'm guessing that's why I got suggested this video

[@OriginalPiMan]

@@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

[@shubhankurkumar]

At this point, excel is a programming language in its own right.

[@joca1378]

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.

[@ultrio325]

Just emulate macOS

[@shubhankurkumar]

@@ultrio325 what? Why would I do that?

[@benchilton9679]

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

[@OurNewestMember]

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

[@gopalakrishnanmanikandan6520]

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.

[@SeleTraining]

Glad it helped!

[@kauegraumann1822]

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.

[@skipfred]

VBA is an atrocity as well, so any alternative is nice by default.

[@sohailahmed1351]

@@skipfred I am from a non programming background myself, but I enjoy VBA. Why do people hate it?

[@skipfred]

@@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]

@@skipfred its also like 100 years old lmfao.... u frustrated with the scalability of an abacus aswell?

[@skipfred]

@@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.

[@tylerwiebe7095]

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.

[@SeleTraining]

Check out my video with more examples. kzbin.info/www/bejne/hZichYyFjLCLqpY

[@Moimus]

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.

[@xl000]

This is because you’re not a programmer.

[@saifuckr]

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!

[@CookieYong]

optimizations, dynamic programming, search algorithms, etc...

[@matekochkoch]

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.

[@anubis9139]

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.

[@sohailahmed1351]

I am from a non programming background, but I enjoy VBA. Why do people hate it? It has made querying back end so easy !!!

[@notnotandrew]

Programmers: "First time?"

[@maxwellhouser]

This severely reduces the amount of vba needed for certain sheets, great update and great video

[@ananasbanana]

What’s VBA

[@fixminer9797]

@@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.

[@lajya01]

@@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]

@@lajya01 Is it separate to Excel? Have to pay extra for it?

[@TanjoGalbi]

@@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.

[@WillianGirhad]

Time to port Doom to excel using lambda functions

[@dougshuntich4343]

underrated comment

[@PapaMike23]

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

[@levarhiggs3174]

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.

[@homejonny9326]

Nice! I thought recursion was sought only by javascript Google sheets programers

[@RyNiuu]

agreed, but it allows you to share the file with the office without macros. But yeah... VBA all the way.

[@micahturpin8042]

Holy guacamole, I've been waiting for something like this for AGES. I love Excel. :)

[@JimmyHandmix]

Will have to rewatch this in 5-6 years when I upgrade to the 2021 Version

[@TausifB]

While the content is concise and helpful, the presentation is incredible with impressive voice:)

[@financialchimes4546]

This went from very easy functions to mind blown very quickly.

[@Arcticroberto9376]

Yeah no kidding, after the first minute I was like "this is nothing" to the next minute "this is the best thing ever"

[@lolly_bread]

...and then on the last formula my brain had a "recursion" and turned inside out - oh the pain (Dr.Smith)

[@simba8073]

God bless my successor when I transition my excel sheets with all these lambda defined functions !

[@Capt_AwesomePants]

Documentation and notation.

[@OurNewestMember]

@@Capt_AwesomePants nah. it's been working in production for two years now... Good luck!

[@faustin289]

Actually, it will make their life easier compared to them trying to make sense of your arcane, uncommented VBA spaghetti

[@ophello]

How wasn’t this a function 20 years ago...this feels super obvious.

[@phinix250]

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.

[@dthe3]

@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.

[@dthe3]

@Winston McGee If we talk about existence, Lisp used them 60 years ago. I was only talking in a modern context.

[@Ronald-jm1te]

Your speaking skill is greater than excel skill...

[@floxbr9350]

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.

[@SeleTraining]

True!

[@nNiceDreamsMadeTrue]

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

[@CLove511]

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.

[@JimzAuto]

@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.

[@jeffreyblack666]

@@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.

[@HenkLeerssen]

all this functionality..I used VBA to do the same. Recursion..mind blown!

[@ellisz5972]

In order to understand recursion, you first need to understand recursion.

[@HenkLeerssen]

@@ellisz5972 haha exactly

[@silberlinie]

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.

[@pradeepbaratam]

Its really a powerful function. Saves alot of work by writing off complex formula for data calculation

[@pradeepbaratam]

Just a query, how did the median value automatically pop up? Is it referring from some location?

[@nofal2031]

hundreds of excel sheet ideas failed because of loops thank you for this illustration

[@micheldevost]

4:52 what? How cool is that… this is officially the new thing that I learned today!

[@SeleTraining]

Excellent!

[@stevenkirby4471]

I must need new glasses. I read the title as Excel LAMBADA Function and my mind went in a totally different direction.

[@zn4rf]

well you could name it that way with naming your own funktion and then callig it :D

[@avi2578]

Now that song is stuck in my head... For eternity!

[@reidflemingworldstoughestm1394]

The Lambada function is cool, but the Macarena function blows it away

[@bojanalukic8515]

🤣

[@mossed1449]

Recursion within formulas is the biggie here. Thanks for sharing.

[@cogwheelLearning]

Lambda really looks powerful. I could see myself using it in some of my reports.

[@SeleTraining]

A very cool new addition to Excel.

[@AdilParray]

I'm trying to figure out in what report I'll use this and how.

[@SeleTraining]

@@AdilParray It does make it easy to shrink complex formulas.

[@cogwheelLearning]

@@SeleTraining Good point!

[@DanKaschel]

@@SeleTraining just the ability to abstract logic into a named function without vba is so powerful

[@paskvale65]

Usage of recursion function is basic for programming, nice Excel finally gets here.

[@Stettafire]

Agreed. It took way too long

[@andmos1001]

Bet when lambda get mainstream, programmers will have a lot of fun with this function

[@DanKaschel]

I got really excited that excel might support arrow syntax

[@FlorencioHugando]

One like is not enough for this video. Wow! Can't wait to use lamda function

[@SeleTraining]

Thanks

[@sal8372]

Naming functions to remember them? Cool!

[@DanKaschel]

Loving udfs without vba

[@myyoutubechannel2858]

You are a fantastic teacher. Bravo.

[@SeleTraining]

Thank you! 😃

[@kirubakaranp7866]

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.. 👍👍👍

[@spencershields9186]

The recursion bit is rad, I've wanted something like that in excel for a while.

[@Cyrribrae]

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.

[@bojanalukic8515]

Thanks a lot from Belgrade, that's what I need

[@smileyspoon1]

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.

[@davesouthword1298]

You would have loved Public Function in VBA

[@danielpenteado]

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.

[@ThisIsGoogle]

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

[@Daniel_Zhu_a6f]

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.

[@cidconde3324]

LOL

[@WikiSnapper]

Exploding dice here I come! I should be able to use this to have exploding dice coded in 1 cell! That is super exciting!

[@sumanshrestha9836]

One formula i had been praying for years...🔥🔥

[@zakylib]

If only it were possible to give multiple thumbs ups... Thanks for your teachable explanation.

[@SeleTraining]

So nice of you

[@MicrowaveHateMachine]

I would like to see more examples of this.

[@BHARGAV_GAJJAR]

Do you hate microwaves ?

[@MicrowaveHateMachine]

@@BHARGAV_GAJJAR what you think?

[@excelchartdesign1815]

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.

[@grimendancehall]

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.

[@DavidA20200]

Finally something that is super useful that’s going to actually save me time!

[@GeblockTT]

This function is so powerful that there even is a song about it written by Kaoma in 1989!

[@MaluluKeleGuiSila]

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…”

[@JCA51698]

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.

[@betaorionis2164]

The (x,y) examples made me feel younger: it reminded me of the art of programming an HP calculator.

[@blackstarmaster]

Most Powerful and Excel are two words which can not be combined! Lambdas in C++ are maybe powerful.

[@alquinn8576]

excel (and, somewhat surprisingly, powerpoint) are turing-complete, so in principle, they can compute anything that is computable

[@daniilbarkalov]

great concise style

[@unknown9274]

this brings up some bad memories from my haskell class in university. i guess i have to get back into lambda calculus...

[@SeleTraining]

I took 3 terms of Calculus. Can't remember anything now...

[@ycombinator765]

haahah lol😅 but Haskell and Lambda Calculus still are so beautiful i think. i love em

[@JG0RDON24]

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

[@SeleTraining]

Take a look at my LAMBDA more examples video. You can see some more complex uses.

[@gfhrtshergheghegewgewgew1730]

i love that it even uses a LISP/scheme-like parenthesis syntax

[@OurNewestMember]

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)

[@Kemarrin]

Fantastic 👍 use this often now

[@miket3265]

I'm gonna show this to my PlusOne

[@VaupellGaming]

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.

[@realliveone]

Google what's lambda in any programming language.

[@danielmazur3203]

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. :)

[@indokenmore]

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?

[@varianaditya3539]

Thebest.. thank you.. this is the function i was looking for recursion that i had hard time to think of

[@RiverMersey]

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.

[@SeleTraining]

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.

[@learningcenterHD]

Excel complete Training basic to advance level

[@StriperScott]

That is soooooo much easier than a VBA function!

[@mptron3447]

I have been waiting lambda calculus in spreadsheet for a long time... now I have to wait libreoffice include this

[@Shuffler703]

I had to startup Excel and check it out. Nice!!

[@aviandragon1390]

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.

[@aracon9721]

Install uBlock origin already

[@ramialobaisi4368]

It's new information for me. Thanks

[@lunchrevisited]

A very clear and informative description of this incredible new Excel super function. Thanks!

[@SeleTraining]

Glad it was helpful!

[@TheArtikae]

Excel finally implementing features from the... *checks notes* 70s.

[@777tmack]

Lambda, Lambda… Sounds like the latest installment of the “Revenge of the Nerds” movie franchise. 🤪

[@piphattsuriya-ek973]

Many thanks, upper level of formulas

[@LeeY91]

Nice introduction of the new function, build up customized embedded formulas become easier and better readable. 👍

[@blankmedia01]

This is God mode of functions ... mind blown

[@jtmh31]

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.

[@SeleTraining]

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.

[@jtmh31]

@@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!

[@kenhaley4]

@@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

[@maxheithmar334]

do you know the timetable for us normies to get access to lambda?

[@SeleTraining]

@@maxheithmar334 Microsoft never says when they will release it. It is in beta but it could be days or months.

[@tuaibmuhammad2225]

Excellent work sir

[@mrmuranga]

awesome... very similar to python lambda

[@peterbartholomew7409]

Intentionally so I believe

[@ChaoticNeutralMatt]

This.. solves so many of my issues

[@maxheithmar334]

this is actually pretty sweet. gonna start using this alot

[@timothyvaher2421]

😎 Cool ! Took till 2021 to get this math power... Go Branden!

[@NickKravitz]

When Excel implements sockets and smart pointers you know they are running out of feature ideas.

[@mjustjeanette7026]

Finally, recursive.

[@wayneedmondson1065]

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!!

[@SeleTraining]

Thanks Wayne

[@ExcelTutorials1]

So looking forward to the lambda function!!

[@economicist2011]

*Maniacally cackles in a 30 year old Lisp dialect*

[@stevebabiak6997]

Only 30? Lisp is older than that …

[@davidascic1117]

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.

[@clementm5417]

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..)

[@TylerStanczak]

Really helpful tutorial :)

[@SeleTraining]

Thanks Tyler.

[@MasterSora311]

This is going viral

[@mateocortes9546]

This is just like Anonymous/Lambda Functions in Common Lisp lol

[@spicystapler58]

Ok but we still can’t still do a double vlookup

[@kbaacca]

Yes you can, through index with multiple match

[@noobmodeNL]

Yes you can with Xlookup.

[@Srulio]

Good function. Thanks for the good explanation

[@tomr942]

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?

[@SeleTraining]

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.

[@DanKaschel]

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.

[@toddkes5890]

@@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)

[@SeleTraining]

@@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.

[@zokm8165]

Nice function, but still find the VBA easier.

[@tonysamosa1717]

Python coders: “noob”

[@rapid13]

Python coders: “noob” Non-coders to Python coders: “STFU, nerd”

[@jawedabro4558]

feeling good to know about this formula

[@budidarmawan6959]

was excited about this and then i checked, my company still deployed 2008version :(

[@SeleTraining]

Bummer

[@shabchique7149]

That's really old 😕

[@pinkipromise]

u mean 2007. 2008 dnt exist

[@Adv4U]

great learning session

[@shamusobi2748]

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

[@hajovonta6300]

lambda is core to some languages that are more than 60 years old now.

[@bentley9249]

Wow, liked and subed, thank you mate, this gold keep it coming