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.
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.
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!
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!
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
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.
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 :)
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’.
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/
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
@@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
@@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)
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!!
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. :-)
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.
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.
@@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
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.
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. 😀
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 ...
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))
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
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.
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)...
"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.
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!
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!
Thank you so much for the clear explanation - I just found my new favorite Excel function!
Glad it was helpful!
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.
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!
You always delivered the goods with easy to understand test cases. Big Thanks!
Glad you think so! 🙏
Amazing. Don't know how I missed seeing this 2 years ago.
Better late than never 😊 have fun writing your own LAMBDAs!
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.
Thank you from Kazakhstan 👍
Thank you too!
Hi Mynda!The LAMBDA Function Looks Really Exciting,Powerful And Pretty Versatile Also...Thank You :)
Glad you like it, Darryl!
Amazing, great video & clear explanation
Glad it was helpful 😊🙏
Amazing concept... A whole new world opens....
Sure does! Have fun writing your own LAMBDAs 😊
Your videos are very helpful ! Love from India !!!
Glad you like them!
I was going through the video to check from where you got the if formula at 12:19, can you please explain ?
I wrote the formula. It's a tax calculation based on a bracket system. i.e. the first 18200 is taxed at 0%, the next 26800 is taxed at 19% and so on.
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.
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 😊
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.
Thanks a lot for such an interesting information. Greatings from Ukraine
Glad it's helpful! Best wishes.
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!
Real education.thanks.love from Turkey
Thank you!
Thanks Mynda. I need to spend some time with this, it looks pretty powerful.
Cheers, Chris. Powerfully mind bending :-)
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/
it's Amazing Function Mynda ♥
Glad you like it, Hazem!
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)
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.
Thanks from Norway 👍
My pleasure, Rasmus!
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 :-)
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.
Marvellous!!!!!!
Thank you!
Lambda Loves Let & Mynda Loves Lambda 😊
😁
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
Informative 👍👍👍
Great to hear, 🙏
Great efforts.very much appreciated.
My pleasure :-)
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.
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.
You are so awesome! I'll be indebted to you all my life 🐑da🤩
Aw, you're too kind 😉
Thank you. Can you translate your lesson into Arabic, whether in writing or audio? my regards
I don't know Arabic, sorry.
As always, useful information!
Glad you think so, John!
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.
awesome ma'am
It's my pleasure 😊
Thanks for the great lecture
Glad you liked it, Yulin :-)
WOW, Love It ❤❤❤
Thanks 😄
That looks really good
Certainly opens new avenues in the worksheet.
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. 😀
Which version support LAMBDA Function? I usally used excel 2021.
LAMBDA is currently only available with Microsoft 365.
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.
Thanks a lot.
Most welcome!
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.
Thanks for sharing.
My pleasure!
Amazing video
Glad you think so, Harsh!
My Lambda ❤️
loved it
Thanks, Ubaidillah!
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
Thanks
Welcome :-)
brilliant
Cheers, Dave!
#teachandtrained
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.
🤬 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.
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)...
"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.
love Mynda!🦘🐾🍀☘
Glad you liked it!