Excel LAMBDA Function: How & When to Use It (Beginner-Friendly)
VloĆŸit
- Äas pĆidĂĄn 24. 07. 2024
- How to create custom functions in Excel with no programming languages.
đ©âđ« Save hours of work by mastering Excel with my courses: bit.ly/excelcourses0221
âĄïž DOWNLOAD the free example file here: bit.ly/lambdafile
The new Excel LAMBDA function allows you to define your own custom functions using Excel's familiar formula language, and that means we no longer need JavaScript or VBA programming knowledge to create our own functions. This is a huge step forward! For those familiar with the concept of LAMBDAs, it is worth noting that the LAMBDA function makes Excel's formula language touring complete. Let's take a look!
LEARN MORE
===========
đ° EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
đŻ FOLLOW me on LinkedIn: / myndatreacy
đŹ EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
âČ TIMESTAMPS
0:00 You don't need JavaScript or VBA to do this anymore
00:39 How to create an Excel LAMBDA
03:37 Practical examples of using LAMBDA
05:47 How to use LAMBDA with other functions
08:48 How to use LAMBDA and LET together
13:08 What you need to know about the function
View my comprehensive courses: www.myonlinetraininghub.com/
#LAMBDAFunction #ExcelLambda #ExcelTutorial - VÄda a technologie
As a self taught data analyst, your videos are invaluable. Thank you so much for taking the time to put these videos together, I can't even explain how much you've helped me advance in my career.
Wonderful to hear! You're very welcome, Stephanie!
Thank you Mynda for sharing your knowledge so generously. It shows to me so well that, sharing is the only function that also multiplies at the same times we all receive it.
Thanks for your kind words, Ivan!
You are amazing, not only very well skilled into the technical stuff and making great demo videos, but that you also take the time to help and advice personally thatâs extraordinary
I made my first real lambda today, calculating over 7 different columns, wrapped into a sum to get the result in one cell - for me, thatâs a success
Woo hoo! Congratulations. So glad I could help, Ivan đ
GREAT examples - looking forward to finding out more about Lambda functions in EXCEL in the future.
Thanks, Ricky!
Thank you so much for the clear explanation - I just found my new favorite Excel function!
Glad it was helpful!
Thank you so much for sharing and explaining Excel Functions so well making it easy to digest and see their use in what I do in my profession! Without even practicing them on Excel, you make it easy to follow to fully understand how Functions work. Can't thank you enough!
You're very welcome!
Finally! I was never scared of VBA, but if I wanted a more sophisticated programming environment, I'd use another application. What makes Excel useful is the mix of on-the-fly data analysis and clear formatting. This just makes the code clearer and Excel far easier to use. I'm excited to get started with lambda functions. Great video as usual!
Thanks so much! Glad you're looking forward to tinkering with LAMBDAS.
Thanks Mynda. I need to spend some time with this, it looks pretty powerful.
Cheers, Chris. Powerfully mind bending :-)
You always delivered the goods with easy to understand test cases. Big Thanks!
Glad you think so! đ
Thank you from Kazakhstan đ
Thank you too!
Thanks Mynda. I have been experimenting with the LAMBDA. Its like writing VBA code with just a function.
Nice! It's certainly much easier than VBA.
Amazing, great video & clear explanation
Glad it was helpful đđ
Your videos are very helpful ! Love from India !!!
Glad you like them!
Real education.thanks.love from Turkey
Thank you!
As always, useful information!
Glad you think so, John!
Hi Mynda!The LAMBDA Function Looks Really Exciting,Powerful And Pretty Versatile Also...Thank You :)
Glad you like it, Darryl!
Thanks from Norway đ
My pleasure, Rasmus!
Amazing. Don't know how I missed seeing this 2 years ago.
Better late than never đ have fun writing your own LAMBDAs!
it's Amazing Function Mynda â„
Glad you like it, Hazem!
Marvellous!!!!!!
Thank you!
Great efforts.very much appreciated.
My pleasure :-)
Amazing concept... A whole new world opens....
Sure does! Have fun writing your own LAMBDAs đ
Thanks for the great lecture
Glad you liked it, Yulin :-)
That looks really good
Certainly opens new avenues in the worksheet.
WOW, Love It â€â€â€
Thanks đ
I like it so much (like everybody else!) that Iâve started a little library of custom functions of my own.
To bad about some stuff like: no I/O for transferring Defined Names between files, Lambda not very stable, etc.
BTW: thanks for the MEDIAN trick. :-)
Nice :-) Daniel Ferry taught me that MEDIAN trick. It's a keeper :-)
Thanks a lot for such an interesting information. Greatings from Ukraine
Glad it's helpful! Best wishes.
You are so awesome! I'll be indebted to you all my life đdađ€©
Aw, you're too kind đ
Thanks for sharing.
My pleasure!
Amazing video
Glad you think so, Harsh!
Very good. You made it much easier to understand. Not sure how much I will use it though TBH. Beyond me how it's got 4 thumbs down!?
Thanks for your support, Henry!
Thanks a lot.
Most welcome!
Informative đđđ
Great to hear, đ
This is PHENOMENAL! Really loved the tax brackets example. It really solidified the functions for accounting related use cases relevant to my line of work.
Any chance you might now when lambda and let become publicly available ie. for us mere mortals outside of the insider program?
PS: depending on the complexity of an authored lambda function, the approach might result in some painful debugging experiences ;-) . The number of parentheses in the tax bracket example was âbeautifulâ.
So pleased you found it helpful, Benson! LET is generally available. LAMBDA is still on Office Insiders at the moment. I don't have an ETA for it.
loved it
Thanks, Ubaidillah!
Mynda, thank you for this tutorial.
I would like to know if the lambda function is used in VBA ?
Thanks, Emre! I'd imagine you can call a lambda with VBA.
Lambda Loves Let & Mynda Loves Lambda đ
đ
Hi Mynda. Thanks for this intro to LAMBDA. I find going in and out of Name Manger to be pretty annoying. MS needs an editor like the VBE, except modernized. Maybe a sidebar concept like for settings and accessible via a keyboard like CTRL+1. I assume they are working on a better interface. Thanks for sharing your tips and insights :)) Thumbs up!!
My pleasure, Wayne. Yes, they're working on the lambda authoring experience.
awesome ma'am
It's my pleasure đ
Love Excel! Where'd you get your Excel branded moleskine like journal? I can't find any in the usual places?
Hi Dave, the Excel journal was a gift from Microsoft to us MVPs at one of the annual summits I attended a couple of years ago. Not sure you can buy them anywhere, sorry.
SuperTutorial Mynda. Thank you for sharing it! Do you know when it will be implemented in Office 365 (No insider)?
Thanks, Ivan! No idea when you'll see it generally available in 365.
Thanks
Welcome :-)
My Lambda â€ïž
Thank you for another excellent tutorial, though I would comment that for anyone who programs in Excel VBA, custom functions are only small part of VBA capability. I have used Excel VBA for over 40 years and consider my knowledge of it to be around 2%, yet I have written many workbook automatons for myself and colleagues that are in daily use. Perhaps you would consider teaching Excel VBA on your channel and really give you audience control over Excel :)
Thanks for the suggestion đ
brilliant
Cheers, Dave!
Thank for your great video! I have a query for Lamda function could you give me advice. How can we apply a custom fruction all workbook in excel. That means when we create a function in workbook 1, it will be auto update to other new workbook. Many thanks.
Currently LAMBDA functions are only present in the active workbook. You would have to copy it to each workbook you want to use it in.
Thanks Mynda and looking forward to getting this functionality. Quick and somewhat nerdy question-On your tax table calculation, I seem to get a tax of $56166 ($1 and a bit off your calculation) on your $190k income. Probably my mistake but wondering why you add 1 in the col F calculations? Thanks
Not sure why you're getting that, Bernard. You can post your question and Excel file on our forum and I'll take a look. www.myonlinetraininghub.com/excel-forum/
Hi! Love your videos! I read that we could use a sheet as a formula. As in the sheet has an input and output, that you then use on another sheet Could you please give us an example on how we could do that.
Thank you! I'm not sure what you mean by 'use a sheet as a formula'.
@@MyOnlineTrainingHub So for example, I have sheet 1. It has 5 'variables' that then affect a big table with calculations with sums and a final result at the end. On sheet 2 I want to be able to have a table of those variables and a column that gives me the result for the corresponding set of variables if they had been input into sheet 1
A little clearer, but still not 100%. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great video. I like to see how all the Excel channels I watch use LAMBDA differently Iâm working on a few myself including an arithmetic/geometric sequencer, text count ( delimited), text split (delimited), reverse text strings, and much more
I found if you name a variable a function name like CELL, and then reference that function in your LAMBDA or LET, it will use the variable and cause an error
Thanks for watching, Patrick! Yes, avoiding function names in your LET variable names is recommended, as per my VAL, UP and LOW example.
@@MyOnlineTrainingHub this probably true for the original name deranged too and just good practice to avoid confusion.
I have finished some of the functions and I am now refining them (shorter, less variables, faster, etc). For others, I am still working on them. I will share the better ones when I am done
Great. I'm looking forward to seeing what you've created. I struggled to find a good example for recursive LAMBDAs.
@@MyOnlineTrainingHub stripping characters from a string and appending n tables are great for recursive LAMBDAs
@@MyOnlineTrainingHub Here is a TEXTCOUNT function. It takes a single delimited text string or a range of delimited text strings (Example A1, A1:A3, A1:C1, or A1:C3) and returns the number of values in each string. Dividing by the delimiter length enables multi-character delimiters like ", " Checking for non-zero length strings allows for blank cells (otherwise an incorrect count of 1 would be returned).
=TEXTCOUNT(Text, Delimiter)
=LAMBDA(Text,Delimiter,
LET(T, Text, D, Delimiter,
TLen, LEN(T), TLenNoD, LEN(SUBSTITUTE(T, D, "")), DLen, LEN(D),
Result, (TLen-TLenNoD) / DLen + (TLen>0), Result))(Text, Delimiter)
I am simply curious to know that is it possible to build functions like SUMIF or VLOOKUP (formula containing range or table array references) using the new LAMBDA function?
Hi Vivek, you can already to this without LAMBDA, so I'm not sure why you would try to write your own versions of these functions? If you did try to write these formulas as a lambda, they would likely be very inefficient because your lambda wouldn't benefit from the efficiency features built into how these functions evaluate in the Excel calc engine.
@@MyOnlineTrainingHub Hii Mynda,
=LAMBDA(a,LOOKUP(2,1/(a""),a))(A:A)
I used this formula to find the last non blank cell value in the column A & it worked great.
Now I simply need to select the column reference after giving a name to my Function...Thanks a lot. đ
Both the lambda and the let function delivered the same result, I dont really see the benefit of using let instead of lambda in this example, but that might only be me. Nice video
Cheers, Christos đ
Which version support LAMBDA Function? I usally used excel 2021.
LAMBDA is currently only available with Microsoft 365.
Does anyone knows how can add in the LAMBDA Function into Excel 2019?
Hi Mike, LAMBDA is only available with a Microsoft 365 license. There's no way to get it in Excel 2019.
Can somebody plz help, I dont seem to have LAMBDA function in my excel, how do I activate it?
LAMBDA is only available with Microsoft 365, so you would need to purchase a Microsoft 365 license.
Hi, Only Wonder : CAN I DO a LAMBDA on this FORMULA: =HYPERLINK(CONCATENATE("tel:",F7)) /// Is a call up function to call customer on one click in EXCEL Sheet... /// Thanks /// McRoman ...
Yes, I'd have thought so.
Thank you. Can you translate your lesson into Arabic, whether in writing or audio? my regards
I don't know Arabic, sorry.
Great stuff, but still struggling to understand how this is an improvement on a standard VBA function.
A couple of reasons: it's more accessible to users not familiar with VBA, it also doesn't require the file to be saved as a .xlsm and then macros enabled upon opening.
whats wrong with my formula here? i'm using predefined names from my sheet, trying to replace my sumifs
LAMBDA(version, segment,SUMIFS(VALUE.D,VERSION.D,version,SEGMENT.D,segment))
the VALUE.D,VERSION.D are named columns in my model, also SEGMENT.D
Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
#teachandtrained
love Mynda!đŠđŸđâ
Glad you liked it!
đ€Ź How long do non-"Insiders" have to wait??!
Hi Rob, I'd expect a few months at least, but I honestly don't have an ETA for when LAMBDA will be generally available.
I believe that Microsoft are releasing a new non-subscription version of Excel later this year (Excel 2021), I wonder if they'll coincide with that release? It's a long way off being ready in my opinion. The recursive LAMBDA has serious limits, they don't seem to clear in memory after each recursion, and there's a limit to the number of recursions (divisible by the number of parameters, making it significantly less with more parameters). I don't think the use of name manager can be sustained, Lambdas should have been enterable by selecting the fX button in the formula bar and typing in a name. That'd have been far better, and I see no obvious reason for having a formula called lambda in itself.
It is awesome though!
@@ricos1497 At the moment, I think the
(number of recursions) x (the number of parameters) < 1024
How do you know that the memory is not cleared following each recursion? That would seriously affect the effectiveness of 'tail end recursion' strategies which aim to control the stack size. I think some of the decisions regarding the new functionality were determined implementation convenience and consistency with the existing calculation framework.
p.s. I do not consider myself in any way knowledgeable on functional programming, it has all come as a rather rude awakening after years of familiarity with an imperative programming style (the Excel sheet itself having been just about the only exception).
@@peterbartholomew7409 I don't know if it clears memory or not, I misspoke. I should have said, it's like the memory isn't cleared. You're right, the 1024 is divided by the number of parameters, which is a limitation. However, that may just lead to better functions being written. I've dabbled in functional programming, and have tended to ape FP when writing in VBA over the last few years. It's a different thought process, but quite an elegant and reusable one! The hardest part is splitting your new LAMBDA functions into their [LAMBDA] functional parts. With the advent of LET without LAMBDA, I was writing screeds of iterative "coding" and now I'm going back through that and saying "what is this line doing" and then assigning a function to that line. It leads to a much more readable and intuitive function in the end I think, a lot more like a traditionally nested formula in Excel albeit the nesting is of lots of new LAMBDA functions that didn't previously exist. I've just created a textsplit function that I'd originally done in let, and it now nests a handful of other functions instead. It's interesting.
"We no longer need VBA programming knowledge." In what way is this a step forward? First we get into what you call Lambda Hell in the Name Manager, with functions scattered all over the place. Second and more important is that VBA code is easy to maintain and debug. It can be commented. I am always very conscious that I may not be here tomorrow and whoever takes over my spreadsheets will turn an arctic shade of pale upon viewing the average nested Lambda formula, such as the examples in the comments. Lambda is simply an open invitation to encourage bad coding habits.
There is a new LAMBDA editor coming, but perhaps the biggest upside to LAMBDAs is that they work in Excel Online, whereas VBA doesn't and never will.
There's literally no reason to do this when user defined functions exist
Except for UDFs you need to save the file as .xlsm and macro enabled workbooks are not allowed in some organisations. They're also more complicated to write. LAMBDA offers a relatively easier entry point to writing custom functions.
UDFs are slower than native formulas and don't use multiple CPU cores (as well or at all).
As soon as it gets a little challenging, UDF are a PITA to use, esp. compared to data handling on import (PowerQuery), Analyzing (PowerPivot and DAX)...