GREAT!!! I learned about this today and an hour after it I had already implemented it into my work. I use to write macros for people around the organisation and people like what they can do but they don't like handling macros very much... if it's new, then it's scary. Recently I started to do a few of the simpler ones in dynamic array version too. They could help with the same things but instead many of them turned into very long and confusing formulas where there was challenging for them to replace references and variables to make them work. But THIS is perfect. Thank you very much!
Thank you for good explation of LAMBDA. Maybe, we can use a Lambda function by using it with VBA. If we create a Lambda function in a workbook and use it with VBA, you can take benefits of this function if we save it as Personal Macro Workbook and use it different workbooks without opening it again and again. It maybe be tested accordingly
Hi Jon. A very nice tutorial and explanation of the new LAMBDA function. As always, your examples and step by step approach make it easy to follow and understand. Thanks for sharing :)) Thumbs up!!
Nice video, Jon. Fully agree. This can change the future of Excel development. We can hide all the complexities of the formulas and make it simple and clean for end-users. I can't wait for this to be available for all users. Thanks for the clear explanation in the video. Best wishes.
LOL yes! I have a feeling that LAMBDA would take a lot more floppy discs. I believe there is a lot going on behind the scenes to make this all work. Thanks Don!😃
Thank you Jon for this great explanation. Thank you for sharing this knowledge. I think it is great function for any user but setting it up may require an advance user
Hi Jon. Great posting. It was my understanding that those new functions that MS has released specially BYROW and BYCOL was to make the LAMBDA function easier to use instead of using name ranges as arguments inside the function. I need to play around some with this new function that is now available in my excel version
Great question! Yes, the BYROW and BYCOL will allow you to run the LAMDBA calculation on multiple rows or columns and return an array (spill range) of results. For example, if we had 12 columns of numbers in the data set in the video, we could use BYCOL to run the AVERAGE.TOP function on each column and return a spill range of 12 cells that returned the average top 25% for each column. I believe the BYCOL function only allows for one parameter to be passed, so you might have to hard code the 25% in the LAMBDA instead of making it a parameter. Here is a link to the article that explains more about those new functions and has some examples. insider.office.com/en-us/blog/new-lambda-functions-available-in-excel I need to play around with these as well. I believe there are a TON of possibilities here and a lot to be discovered. Thanks again and have a nice day! 🙂
Looking Forward To Having Fun With The LAMBDA Function.Unfortunately Most Of The Projects I Work On I'm Using Either Power Bi,Tableau Or Python...Great Tutorial Thank You Jon :)
Hey Jon, great tutorial and explanation as always. You always explain everything so well. LET is great friends with LAMBDA. With that in mind since AVERAGE_TOP & AVERAGE_BOTTOM you could probably combine them into AVERAGE_TOPBOT. Just add a third argument. =AVERAGE_TOPBOT(Range, Percent, Top_Bot)" *I had to replace the "." with "_" to avoid auto deletion. Enter 1 or 0 for Top or Bottom percentage respectively or maybe "T" for Top or "B" for Bottom or vice versa. Then using LET and other functions modify the logic for the AVERAGEIF Criteria argument.
Below is the screen tip for a recent function I made called AFLIP. It returns data in reverse horizontal or vertical order or both. I am trying to make a set of standards for writing LAMBDAs and the screen tips. That way everyone can be consistent. Syntax: =AFLIP(Array,[Direction]) Screen Tip/ Comment: =AFLIP(Array☛ range to flip order, [Direction]☛ 0/❎=horizontally✅; 1=vertically; 2=both) ⁂[]=optional; ✅=default; ❎=omit Code: =LAMBDA(Array,[Direction], LET(Arr, Array, Dir, IFERROR(--(Direction), 3), DirScan, AND(Dir{2,1,0}), DirMSG, "Direction=0,1,2", RowCt, ROWS(Arr), RowSeq, SORT(SEQUENCE(RowCt),, IF(Dir=0, 1, -1)), ColCt, COLUMNS(Arr), ColSeq, SORT(SEQUENCE(, ColCt),, IF(Dir=1, 1, -1), 1), Result, INDEX(IF(Arr="", "", Arr), RowSeq, ColSeq), Return, IF(DirScan, DirMSG, Result), Return ) ) I use existing function screen tips and syntax as guidelines to make my function Screen Tips. I use descriptive names for each argument. I put each argument/description or note on a different line. I put , and () where applicable. I use the ☛ symbol to tell the user what follows is the argument description. I use the = to specify what each argument value equates to. I use the ; to specify there are multiple possible values for an argument. I use the ✅ symbol for default values and the ❎ symbol for omitted values. I use to the ⁂ symbol to specify the start of notes. This helps to shorten the length, especially in LAMBDAs with multiple optional arguments with a default value. As needed I use other symbols like the # to represent a number or numbers, stylized Ａ,Ｒ, & Ｃ to represent Array, Column(s), & Row(s) respectively, a ❔ symbol to represent Value(s), a 📌 symbol for Position, Then I use TEXTJOIN with a double space as the delimiter to combine the arguments and notes with separation. I then have a simple length check to ensure I am below 255 characters.
Are there any performance considerations using LAMBDA? As you mentioned, it will likely be used with more complex formulas. I wasn't sure if wrapping this around it would affect the time to process. Great explanation!
From my experience, there can be but it is usually barely noticeable. LET and IF statements can help with this. For all parameters that repeat use LET to create variables. Then use IF statements (and/or other TRUE/FALSE logic or selection functions like CHOOSE and SWITCH). Recursive LAMBDAs tend to see this more. However, with the release of BYROW and BYCOL, many of the recursive LAMBDAs can be rewritten to not be recursive anymore. You should check out the Excel LAMBDA Function forum on Mr. Excel's website. There are some great functions on there created by many different people. Some did the same function but very differently. Also, there is a formula speed calculator you can get.
@@ExcelCampus You bet. Also there's no way around this but very large data sets will be slower regardless of the functions you use (existing, LET, LAMBDA, etc.) However, LET and LAMBDA will definitely help
Hi Jon, You have presented a good quality, real word example for us. I have just taken the opportunity to use LAMBDA functions and I am working on a series of examples. I was creating a CAGR (Compound annual growth rate) and it returned errors because of the nature of my data inputs. So, I wanted to use IFERROR() inside my LAMBDA() function but it seems impossible ... is it?
It is very easy to create new functions to use in the cells using VBA Functions (not subs). Lambda let us do SIMPLE functions, without VBA. Nice. But VBA allows more complex functions since it's a full program, and Lambda is just one formula.
Excellent description. Thank you. You compare using LAMBDA to using a formula. What do you see as the pros and cons of using LAMBDA with user defined functions? I use user defined functions often to build Excel tools for other users, but my user defined function references in the workbook that I intend to reference the user defined function associated with the current workbook, but without my knowing switch to referring to user defined functions of the same name on my local computer. These functions then only work on my computer. A different user on a different computer must then edit the function references to remove the path portion of the reference such that the formula will point to the version of the function in that workbook. Would using LAMBDA's resolve this issue? Is there another way to resolve this issue with user defined functions?
Hey David, Great question! I think LAMDAs will help with this issue, but might still have some challenges of their own. The LAMBDA defined functions (LDFs) are only going to be stored in the workbook as named ranges. At least that's how it works right now. Therefore, I don't believe you'll have the issue of having them stored in your Personal Macro Workbook, like you do with UDFs. You can't call an LDF from another workbook. However, there will still be version issues. Especially with LDFs floating around in different workbooks. Managing those versions could be a challenge. Hopefully, Microsoft will come up with a way to organize and manage LDFs at more of a global or organizational level. I foresee this being similar to software updates. As an LDF author you would want the ability to publish updates to your calculations and have the users of your files import/update their LDF calculations. So it's definitely something to consider when creating LDFs. Maybe we'll do a follow up video on best practices for LAMBDA management. I hope that helps. Thanks again and have a nice day! 🙂
I want to get this, my concern is that I get it to do all this work but then be unable to share what I have done because they don't have it. Am I able to get this, and when I share documents they will see whatever I have done without needing the add-in themselves?
Hi I needed to give an array which is dependent on another Sheet- lets say Sheet1 B10 & C10 Value. Example- Instead using =Sum(A1:A300) formulae I want to use =Sum(A&'Sheet1!'B10:A&'Sheet1!'C10) kind of formulae but it's not working!! any help regarding the same will be appreciated
TOP explanation. I certainly use it more and more in the future.
Ideal for making complex calculations easy, once everything is in place. 👍👍
GREAT!!! I learned about this today and an hour after it I had already implemented it into my work. I use to write macros for people around the organisation and people like what they can do but they don't like handling macros very much... if it's new, then it's scary. Recently I started to do a few of the simpler ones in dynamic array version too. They could help with the same things but instead many of them turned into very long and confusing formulas where there was challenging for them to replace references and variables to make them work. But THIS is perfect. Thank you very much!
Thank you for good explation of LAMBDA.
Maybe, we can use a Lambda function by using it with VBA. If we create a Lambda function in a workbook and use it with VBA, you can take benefits of this function if we save it as Personal Macro Workbook and use it different workbooks without opening it again and again.
It maybe be tested accordingly
Hi Jon. A very nice tutorial and explanation of the new LAMBDA function. As always, your examples and step by step approach make it easy to follow and understand. Thanks for sharing :)) Thumbs up!!
Nice video, Jon. Fully agree. This can change the future of Excel development. We can hide all the complexities of the formulas and make it simple and clean for end-users. I can't wait for this to be available for all users. Thanks for the clear explanation in the video. Best wishes.
Thanks Dinesh! I appreciate your support and looking forward to it rolling out further as well.
Excellent explanation. Very brilliant Jon. Thank you for this great tutorial!
Excel has sure come a long way since ver.5 (1993) when it came on 11 floppy discs. Great video. Thanks
LOL yes! I have a feeling that LAMBDA would take a lot more floppy discs. I believe there is a lot going on behind the scenes to make this all work. Thanks Don!😃
Great function, certainly will allowed to extend the use of some spreadsheet with complex formula to less advanced users.
Thank you Jon for this great explanation. Thank you for sharing this knowledge. I think it is great function for any user but setting it up may require an advance user
Thank you Nader! Yes, I hope the setup process gets easier in the future.
Hi Jon. Great posting. It was my understanding that those new functions that MS has released specially BYROW and BYCOL was to make the LAMBDA function easier to use instead of using name ranges as arguments inside the function. I need to play around some with this new function that is now available in my excel version
Great question! Yes, the BYROW and BYCOL will allow you to run the LAMDBA calculation on multiple rows or columns and return an array (spill range) of results. For example, if we had 12 columns of numbers in the data set in the video, we could use BYCOL to run the AVERAGE.TOP function on each column and return a spill range of 12 cells that returned the average top 25% for each column. I believe the BYCOL function only allows for one parameter to be passed, so you might have to hard code the 25% in the LAMBDA instead of making it a parameter.
Here is a link to the article that explains more about those new functions and has some examples.
insider.office.com/en-us/blog/new-lambda-functions-available-in-excel
I need to play around with these as well. I believe there are a TON of possibilities here and a lot to be discovered.
Thanks again and have a nice day! 🙂
Looking Forward To Having Fun With The LAMBDA Function.Unfortunately Most Of The Projects I Work On I'm Using Either Power Bi,Tableau Or Python...Great Tutorial Thank You Jon :)
Thank you Darryl! 🙂
Interesting. So much more wonders with this new thing coming! Thanks, Jon, as always.
Yes, many wonders indeed. Thanks Drei! 🙂
WOW! This is great! Thank you for the video to explain it all. :)
Thanks Jennifer! 🙂
Hey Jon, great tutorial and explanation as always. You always explain everything so well.
LET is great friends with LAMBDA. With that in mind since AVERAGE_TOP & AVERAGE_BOTTOM you could probably combine them into AVERAGE_TOPBOT. Just add a third argument.
=AVERAGE_TOPBOT(Range, Percent, Top_Bot)" *I had to replace the "." with "_" to avoid auto deletion.
Enter 1 or 0 for Top or Bottom percentage respectively or maybe "T" for Top or "B" for Bottom or vice versa. Then using LET and other functions modify the logic for the AVERAGEIF Criteria argument.
Below is the screen tip for a recent function I made called AFLIP. It returns data in reverse horizontal or vertical order or both. I am trying to make a set of standards for writing LAMBDAs and the screen tips. That way everyone can be consistent.
Syntax: =AFLIP(Array,[Direction])
Screen Tip/ Comment: =AFLIP(Array☛ range to flip order, [Direction]☛ 0/❎=horizontally✅; 1=vertically; 2=both) ⁂[]=optional; ✅=default; ❎=omit
Code:
=LAMBDA(Array,[Direction],
LET(Arr, Array, Dir, IFERROR(--(Direction), 3),
DirScan, AND(Dir{2,1,0}), DirMSG, "Direction=0,1,2",
RowCt, ROWS(Arr), RowSeq, SORT(SEQUENCE(RowCt),, IF(Dir=0, 1, -1)),
ColCt, COLUMNS(Arr), ColSeq, SORT(SEQUENCE(, ColCt),, IF(Dir=1, 1, -1), 1),
Result, INDEX(IF(Arr="", "", Arr), RowSeq, ColSeq), Return, IF(DirScan, DirMSG, Result),
Return
)
)
I use existing function screen tips and syntax as guidelines to make my function Screen Tips. I use descriptive names for each argument. I put each argument/description or note on a different line. I put , and () where applicable. I use the ☛ symbol to tell the user what follows is the argument description. I use the = to specify what each argument value equates to. I use the ; to specify there are multiple possible values for an argument. I use the ✅ symbol for default values and the ❎ symbol for omitted values. I use to the ⁂ symbol to specify the start of notes. This helps to shorten the length, especially in LAMBDAs with multiple optional arguments with a default value. As needed I use other symbols like the # to represent a number or numbers, stylized Ａ,Ｒ, & Ｃ to represent Array, Column(s), & Row(s) respectively, a ❔ symbol to represent Value(s), a 📌 symbol for Position,
Then I use TEXTJOIN with a double space as the delimiter to combine the arguments and notes with separation. I then have a simple length check to ensure I am below 255 characters.
Are there any performance considerations using LAMBDA? As you mentioned, it will likely be used with more complex formulas. I wasn't sure if wrapping this around it would affect the time to process. Great explanation!
From my experience, there can be but it is usually barely noticeable. LET and IF statements can help with this. For all parameters that repeat use LET to create variables. Then use IF statements (and/or other TRUE/FALSE logic or selection functions like CHOOSE and SWITCH).
Recursive LAMBDAs tend to see this more. However, with the release of BYROW and BYCOL, many of the recursive LAMBDAs can be rewritten to not be recursive anymore.
You should check out the Excel LAMBDA Function forum on Mr. Excel's website. There are some great functions on there created by many different people. Some did the same function but very differently. Also, there is a formula speed calculator you can get.
Thanks for the explanation Patrick and for sharing the resources! 👍
@@ExcelCampus You bet. Also there's no way around this but very large data sets will be slower regardless of the functions you use (existing, LET, LAMBDA, etc.) However, LET and LAMBDA will definitely help
Hi Jon, You have presented a good quality, real word example for us. I have just taken the opportunity to use LAMBDA functions and I am working on a series of examples. I was creating a CAGR (Compound annual growth rate) and it returned errors because of the nature of my data inputs. So, I wanted to use IFERROR() inside my LAMBDA() function but it seems impossible ... is it?
Thanks very much! This is cool and great.
Excellent!
It is very easy to create new functions to use in the cells using VBA Functions (not subs).
Lambda let us do SIMPLE functions, without VBA. Nice. But VBA allows more complex functions since it's a full program, and Lambda is just one formula.
Excellent description. Thank you. You compare using LAMBDA to using a formula. What do you see as the pros and cons of using LAMBDA with user defined functions?
I use user defined functions often to build Excel tools for other users, but my user defined function references in the workbook that I intend to reference the user defined function associated with the current workbook, but without my knowing switch to referring to user defined functions of the same name on my local computer. These functions then only work on my computer. A different user on a different computer must then edit the function references to remove the path portion of the reference such that the formula will point to the version of the function in that workbook. Would using LAMBDA's resolve this issue? Is there another way to resolve this issue with user defined functions?
Hey David,
Great question! I think LAMDAs will help with this issue, but might still have some challenges of their own. The LAMBDA defined functions (LDFs) are only going to be stored in the workbook as named ranges. At least that's how it works right now. Therefore, I don't believe you'll have the issue of having them stored in your Personal Macro Workbook, like you do with UDFs. You can't call an LDF from another workbook.
However, there will still be version issues. Especially with LDFs floating around in different workbooks. Managing those versions could be a challenge. Hopefully, Microsoft will come up with a way to organize and manage LDFs at more of a global or organizational level.
I foresee this being similar to software updates. As an LDF author you would want the ability to publish updates to your calculations and have the users of your files import/update their LDF calculations.
So it's definitely something to consider when creating LDFs. Maybe we'll do a follow up video on best practices for LAMBDA management.
I hope that helps. Thanks again and have a nice day! 🙂
Is there a lambda function for spelling out numbers or currency into words?
Thanks sir very helpful video
Thanks Rahul! 🙂
hi i am looking to create a button when i tap it it puts time on every time i tap time button please
I want to get this, my concern is that I get it to do all this work but then be unable to share what I have done because they don't have it. Am I able to get this, and when I share documents they will see whatever I have done without needing the add-in themselves?
Hi I needed to give an array which is dependent on another Sheet- lets say Sheet1 B10 & C10 Value. Example- Instead using =Sum(A1:A300) formulae I want to use =Sum(A&'Sheet1!'B10:A&'Sheet1!'C10) kind of formulae but it's not working!! any help regarding the same will be appreciated
check your cell reference