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