What a great video! On the evolution of Excel: Back in the days of Old School: you’d write crazy long formula’s in order to calculate a beautiful albeit scalar result. Then you took it to the next level and started writing crazy array formulas to produce beautiful array results. Then Dynamic Arrays came out and I thought: no more crazy Array formulas. But instantly you corrected me, and oh boy, was I wrong. Now, you write crazy array formulas and out come entire reports - fully dynamic, of course. And you know what: those formulas don’t look all that crazy anymore. And the things we can do now used to be unimaginable. What great Excel times these are, and what a great teacher you are. Thanks for your guidance. Respect! :-)
The history of Excel formulas that you just wrote in your comment is fabulous! You are right, we are in good Excel times : ) Thanks for your kind words, Geert : ) : ) : ) : )
WOW, in your last reporting video, I thought to myself it would be cool if you could run a function on a series of data created in memory. I didn’t have the courage to ask. You just answered that question BIG TIME. It’s truly amazing what we can accomplish with an AWESOME coach. Thank you Professor!!! 👍
Amazing combination of new functions. Just I prefer to name each part of formula inside let function even if it's not repeatable since it more readable but I loved this way as well. Thanks.
That is accurate: brutal. But I like your phrase: Satisfyingly brutal!!!! "Satisfyingly Brutal" should be a synonym for single cell formula reporting lol
Great video. Creating both LET and LAMBDA in one video is a good idea since I have helped some users who have a hard time understanding the distinction. In reality though if I were going to create a single Lamda to generate a single cell report I would skip the LET. Another commenter thought that you were going to use Advanced Formula Environment to create and save Lambda. So did I. I have used it twice ( I dont create many LAMBDAS) and It is great. Much superior to using Defined Name dialog box. Maybe a video on AFE!
I 100% can not see the benefit in using the Advanced Formula Environment to build the formula. The advantage of using the cell to build the formula is that you can test each part by spilling into cells. But maybe I don't know how to test the formula elements in the Advanced Formula Environment window yet. Maybe build the formula in cells, then use the Advanced Formula Environment window to load it as a Defined Name? However, I tried that and could not get any formula to load as a Defined Name. How are you using the Advanced Formula Environment window, Richard Hay?
@@excelisfun I think it would be inefficient to build a LAMBDA in the AFE. I have only ued it twice (once for test and once for real). So not an authority. But I think either the complete LAMBDA should be built in the cell and pasted in th AFE or at least, if long, testable sections should be pasted in and combined in the AFE. Then it is a matter of completing the form in the dialog box. Without going through it all it provides great editing and documentation options not previously available. To get the new function into defined name and in the function drop down you MUST clic once on the SYNC button on the top left. AFE is better thought of as a specialized LAMDA editor rather than a LAMBDA creator. That is how it probably should have been named.
@@richardhay645 Great summary! LAMBDA Editor, Note Taker, Formatter and Defined Name Loader window, but not the place to create the monster formulas. Got it : )
Awesome. Wow. You brained my damage. Hey, here's a tip from a simpleton. At 12:00 or so, when you click on 'array2' in the tool tip and it highlights array2, you don't need to then precisely and carefully click the parenthesis - all you need do is hit the right arrow on the keyboard and it will move to the right spot for you to add the next comma.
It almost feels like cheating that something that used to be extremely complex is now very easy to do Wonderful tutorial Sometimes is useful to creat variables for items only used once. It can help if the variable needs to change and for clarity
Wow Mike! Your single cell report formulas are so awesome! Clever, elegant, logical. Simultaeously humbling and inspiring to watch you work. Thanks for the detailed step by step walkthough which helps us mortals learn the tricks and techniques. Respect and admiration for all that you do :)) Thumbs up!!
Dear Mike, I finally managed to make a LAMBDA that behaved like a function, unlike the previous LAMBDA which was simply a defined name. The previous one, due to the use of the OFFSET volatile function, to include the header, did not turn into a function. With the current LAMBDA, you only need to include the entire table (ST[#All]) to get the desired result. Here's the updated version of LAMBDA: =LAMBDA(Table, LET(Date,INDEX(Table,,1),Person,INDEX(Table,,2),Sales,INDEX(Table,,3), rh,SORT(UNIQUE(HSTACK(DROP(Person,1),EOMONTH(--DROP(Date,1),0))),{1,2}), U,CHOOSECOLS(rh,2), VSTACK( HSTACK(TAKE(Person,1),TAKE(Date,1),TAKE(Sales,1)), HSTACK(rh,SUMIFS(Sales,Date,">"&EOMONTH(--U,-1),Date,"
O No, please do not do that. We create reports mostly with standard and Data Model PivotTables. Just for the instances when we need a report to update instantly, then we do these fun formulas : ) Glad you liked the video, Michal!!!
It is easier to build the formula in the cells so that I can see each part as I build it. But the Advanced Formula Environment seems to be ok for formatting and uploading to the Name Manager. However, I have had little success with it and I not sure how to get it to upload the formulas : (
I have not even download the add-in yet. You are 100% right, I must do that, try it, and start using it. I guess I have been going old school too much lol
I just tried to download and it is not working. Not sure why... I will have to figure it out soon... However, when you use it, does it let you test each part of the formula, like I show in the video?
I did download it and try it. I am not sure how I would ever use it because it doesn't allow me to test each part. At least I could not figure out how to test each part. The advantage of building in cells, is as I show in the video, test each part and see if that particular formula element is spilling correctly. Maybe building the formula in the cell first, then use the window to load the formula as a Defined Name. That might be good. But I couldn't figure out how to do that either... lol
Great video as always. Excel is moving so fast that I am struggling (well failing would be more honest) to keep up. But the next time I need a pivot table, I am going to try formulas. One small point, in LET statements it might be helpful if names were more self-explanatory eg when reviewing the formulas/worksheets later. There is of course a balance to maintain.
Hi Mike, thank you for another great video. Can't wait for LAMDA to be released on my work's 365. We got LET so far. Hopefully in less than 8 months time we will have LAMDA.
Much, much quicker and easier. For reporting we almost always use standard or Data Model PivotTables. But when there is the need for instant update - Bam! We got it : ) The other reason we might make LAMBDA functions is when we have the same report often, then make the function and re-use : )
Dear Mike, I had a good job developing a new LAMBDA but found the old one didn't need to be fixed as Microsoft fixed the issue with the volatile functions. 🤗
@@excelisfun I didn't hear that, I tested the LAMBDA that I sent five months ago, which wasn't turning into a function, and I found that it was working as expected: it turned into a function🤗
Dear Mike, I'm posting again. I hope it doesn't get deleted again. I would like your comment about the LAMBDA that I developed with your idea. 😁 =LAMBDA(Table, LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3), rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}), u,CHOOSECOLS(rh,2), VSTACK( {"SalesRep","EOM","Sales"}, HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
Awesome formula. A few commenters sent in formulas in the last few videos with the same "single table input". Thanks for the formula and I am glad that the comment finally came through. I have added your formula to the download file so the Team can check it out : )
@@excelisfun Dear Mike, Thank you for including my name and my first LAMBDA in the download file. However, the second LAMBDA was much better, due to the header being dynamic, while the header of the first formula is constant. See the difference below: =LAMBDA(Table, LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3), rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}), u,CHOOSECOLS(rh,2), VSTACK( HSTACK(OFFSET(p,-1,,1,1),OFFSET(d,-1,,1,1),OFFSET(s,-1,,1,1)), HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
@@excelisfun I think I will finally purchase Camtasia. I can't find another program out there that does a great job of adding effects, annotations, etc like Camtasia. If I want to take video editing seriously then I need Camtasia, regardless of the cost. I point out many things in my videos now so having an editor that can amplify this with those effects will make all the difference. Thanks for the recommendation.
Hi mike, quick question, back in the day(like 10 years ago) you mentioned that you didn't want actually precisely that you couldn't program in excel yet, I see you are now manipulating variables with let, and building function with lambda so you are effectively programming right ? So how did you take the leap to become a non programmer to an advanced excel programmer ?
I am just one human. I create everything: videos, files, web sites and pdf notes. With 12-15 hours of work each day, all I can muster is pdf notes for my classes, such as Excel Basics, Advanced Excel, Excel Data Analysis, Microsoft Power Tools Data Analysis classes, Statistics and others.
Expected this and as usual good one. In the excel version 2204 Build 16.0.15121.20000 64-bit , for the spilled array conditional formatting has one issue that the double underline border around the cell option is not available. Is any one has the similar issue. Thanks
Yes, 100% there are problems with dynamic spilled arrays and conditional formatting. There are numerous situations where conditional formatting does not work. I have report it but have heard no reply.
I need some help: I've just started a new job and the company is using Office 2013. I'm coming from a Microsoft 365 environment. Question: If I use 365 at home for my awesome spreadsheets, how will they behave in Office 2013 when I send my colleagues the reports?
@@excelisfun please advise if we need to concate customer/region as row header then how to put this in stack formula with one single formula. Kindly guide sir.
Google / RUclips has been causing errors for over a year in the comment area. All of us, including me have had our comments deleted. I have reported this, but have had no reply. At this point, all we can do is to keep trying to post when post is deleted. A few viewers have sent important comments to me at my gmail (excelisfun at gmail) and I can try to post them. However, it is really egregious that the monopoly Google / RUclips has changed how comments work and have let this error/bug persist. It makes the beautiful dialog that we have here in the comments at the excelisfun channel much more difficult. But we don't give up, we just keep posting through this temporary error bug that the monopolist refuses to fix...
@@excelisfun Dear Mike. I made another comment, replacing the one that was deleted, and now I am including the new improved LAMBDA, following your idea, and I would like your comment on it. =LAMBDA(Table, LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3), rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}), u,CHOOSECOLS(rh,2), VSTACK( HSTACK(OFFSET(p,-1,,1,1),OFFSET(d,-1,,1,1),OFFSET(s,-1,,1,1)), HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
@@excelisfun Dear Mike, I stated that the second LAMBDA was better than the first, and it is not 😕, because the OFFSET function returns a dynamic header, which is outside the table's range. LAMBDA works if you manually insert the opening of the parentheses and the table data, but a strange person, who doesn't know what to insert, wouldn't know what to inform. 🤪 I apologize for the inconvenience and I also ask that if you know how to solve the problem so that LAMBDA works as a function, I would appreciate the help. 🤗
Of course. PT 100% faster. But when you need instate update or you want to make a re-usable function, that is when you use a formula like this. PT almost all the time, but once in a while when requirements are different... : )
This is one of those videos that requires multiple viewings to fully appreciate just how powerful Excel has become.
Nicely said: how powerful Excel has become : ) : ) : ) : )
What a great video!
On the evolution of Excel:
Back in the days of Old School: you’d write crazy long formula’s in order to calculate a beautiful albeit scalar result.
Then you took it to the next level and started writing crazy array formulas to produce beautiful array results.
Then Dynamic Arrays came out and I thought: no more crazy Array formulas. But instantly you corrected me, and oh boy, was I wrong.
Now, you write crazy array formulas and out come entire reports - fully dynamic, of course.
And you know what: those formulas don’t look all that crazy anymore. And the things we can do now used to be unimaginable.
What great Excel times these are, and what a great teacher you are.
Thanks for your guidance. Respect! :-)
The history of Excel formulas that you just wrote in your comment is fabulous! You are right, we are in good Excel times : ) Thanks for your kind words, Geert : ) : ) : ) : )
What a perfect example to demonstrate how we have entered a whole new Excel world with amazing possibilities... thank you!
You are welcome, Prathamesh!!!!
Umbelievable Mike The most progiciel in the world could not do that :-) I love the bonus function with using lamda absolutately amazing
The new Excel is THE best ever : ) : ) : ) : )
WOW, in your last reporting video, I thought to myself it would be cool if you could run a function on a series of data created in memory. I didn’t have the courage to ask. You just answered that question BIG TIME. It’s truly amazing what we can accomplish with an AWESOME coach. Thank you Professor!!! 👍
Always glad to help, Kevin!!!!
Speechless........just speechless !!!! That's a lot to practice on!!! Thanks Mike. :) :)
You are welcome, Speechless Formula Guy John : ) : )
Amazing combination of new functions.
Just I prefer to name each part of formula inside let function even if it's not repeatable since it more readable but I loved this way as well.
Thanks.
Naming is a better idea for readability. I often use short names in the videos to keep the complicated formulas less cluttered.
Thanks Mike for this EXCELlent video and this one cell formula which is amazing.
You are welcome, Fellow Teacher : ) : ) : )
Wow, that formula is BRUTAL. Satisfyingly brutal. Yet another awesome video, thank you for sharing!
That is accurate: brutal. But I like your phrase: Satisfyingly brutal!!!! "Satisfyingly Brutal" should be a synonym for single cell formula reporting lol
Mike, I need to take an Excedrin and go lie down for a while after seeing that LET formula lol. Great video as always!
You are making me laugh : ) : ) "Excedrin and go lie down for a while"... Nice description of advanced Excel formulas lol Go Team!
Great video. Creating both LET and LAMBDA in one video is a good idea since I have helped some users who have a hard time understanding the distinction. In reality though if I were going to create a single Lamda to generate a single cell report I would skip the LET. Another commenter thought that you were going to use Advanced Formula Environment to create and save Lambda. So did I. I have used it twice ( I dont create many LAMBDAS) and It is great. Much superior to using Defined Name dialog box. Maybe a video on AFE!
I 100% can not see the benefit in using the Advanced Formula Environment to build the formula. The advantage of using the cell to build the formula is that you can test each part by spilling into cells. But maybe I don't know how to test the formula elements in the Advanced Formula Environment window yet. Maybe build the formula in cells, then use the Advanced Formula Environment window to load it as a Defined Name? However, I tried that and could not get any formula to load as a Defined Name. How are you using the Advanced Formula Environment window, Richard Hay?
@@excelisfun I think it would be inefficient to build a LAMBDA in the AFE. I have only ued it twice (once for test and once for real). So not an authority. But I think either the complete LAMBDA should be built in the cell and pasted in th AFE or at least, if long, testable sections should be pasted in and combined in the AFE. Then it is a matter of completing the form in the dialog box. Without going through it all it provides great editing and documentation options not previously available. To get the new function into defined name and in the function drop down you MUST clic once on the SYNC button on the top left. AFE is better thought of as a specialized LAMDA editor rather than a LAMBDA creator. That is how it probably should have been named.
@@richardhay645 Great summary! LAMBDA Editor, Note Taker, Formatter and Defined Name Loader window, but not the place to create the monster formulas. Got it : )
Awesome. Wow. You brained my damage. Hey, here's a tip from a simpleton. At 12:00 or so, when you click on 'array2' in the tool tip and it highlights array2, you don't need to then precisely and carefully click the parenthesis - all you need do is hit the right arrow on the keyboard and it will move to the right spot for you to add the next comma.
That is a wicked cool (as they say in Boston) tip, DRSteele!!!! I can wait to use it soon : ) : ) : )
It almost feels like cheating that something that used to be extremely complex is now very easy to do
Wonderful tutorial
Sometimes is useful to creat variables for items only used once. It can help if the variable needs to change and for clarity
That is a good idea: create variables for items only used once to add clarity : )
Wow Mike! Your single cell report formulas are so awesome! Clever, elegant, logical. Simultaeously humbling and inspiring to watch you work. Thanks for the detailed step by step walkthough which helps us mortals learn the tricks and techniques. Respect and admiration for all that you do :)) Thumbs up!!
Thanks for the respect and awesome support, Wayne!!!!! : ) : ) : )
Boom!That Was One Of The Coolest Most Beautiful Formulas I've Ever Seen...Thank You Mike :)
Cooler than the other side of the pillow, probably : ) : ) : ) You are welcome, Fellow Biker!
Awesome👍 and excellent as always. Thanks Mike
Great step by step lesson! I like LET ability to materialise each variable like F9 in old school formulas
Glad you like it, Vida!!
Dear Mike,
I finally managed to make a LAMBDA that behaved like a function, unlike the previous LAMBDA which was simply a defined name.
The previous one, due to the use of the OFFSET volatile function, to include the header, did not turn into a function. With the current LAMBDA, you only need to include the entire table (ST[#All]) to get the desired result.
Here's the updated version of LAMBDA:
=LAMBDA(Table,
LET(Date,INDEX(Table,,1),Person,INDEX(Table,,2),Sales,INDEX(Table,,3),
rh,SORT(UNIQUE(HSTACK(DROP(Person,1),EOMONTH(--DROP(Date,1),0))),{1,2}),
U,CHOOSECOLS(rh,2),
VSTACK(
HSTACK(TAKE(Person,1),TAKE(Date,1),TAKE(Sales,1)),
HSTACK(rh,SUMIFS(Sales,Date,">"&EOMONTH(--U,-1),Date,"
Amazing video. Soon I will forget how to use Pivot table with this combination of new formulas 🙂
O No, please do not do that. We create reports mostly with standard and Data Model PivotTables. Just for the instances when we need a report to update instantly, then we do these fun formulas : ) Glad you liked the video, Michal!!!
humbled...again... and happy for it!
Thank you
Great video! Wow! Instead of the Name Manager, you could use the Advanced Formula Environment....(another NEW, NEW SCHOOL Excel Tool!)
It is easier to build the formula in the cells so that I can see each part as I build it. But the Advanced Formula Environment seems to be ok for formatting and uploading to the Name Manager. However, I have had little success with it and I not sure how to get it to upload the formulas : (
I like single cell report series. Amazing video. Thank you Mike 👍👍🙂
You are welcome, kiwikiow!!!!! : )
I had expected You use the Advanced Formula Environment. Great video
I have not even download the add-in yet. You are 100% right, I must do that, try it, and start using it. I guess I have been going old school too much lol
I just tried to download and it is not working. Not sure why... I will have to figure it out soon... However, when you use it, does it let you test each part of the formula, like I show in the video?
I did download it and try it. I am not sure how I would ever use it because it doesn't allow me to test each part. At least I could not figure out how to test each part. The advantage of building in cells, is as I show in the video, test each part and see if that particular formula element is spilling correctly. Maybe building the formula in the cell first, then use the window to load the formula as a Defined Name. That might be good. But I couldn't figure out how to do that either... lol
Great video as always. Excel is moving so fast that I am struggling (well failing would be more honest) to keep up. But the next time I need a pivot table, I am going to try formulas.
One small point, in LET statements it might be helpful if names were more self-explanatory eg when reviewing the formulas/worksheets later. There is of course a balance to maintain.
I 100% agree: variable names should be more descriptive. I often use short variables to make the visuals in the video less cluttered.
Blimey, that's rather good, Mr G.
I love this: blimey : ) Glad you like the vid, Ian!!!
Hi Mike, thank you for another great video. Can't wait for LAMDA to be released on my work's 365. We got LET so far. Hopefully in less than 8 months time we will have LAMDA.
LAMBDA should be in all of Excel 365 now. Have you updated lately?
@@excelisfun Our company has IT administrators that does the roll out of upgrades. Have absolutely no control over it, sadly.
@@excel_along_the_way Understood. I hope you will get all the functions soon : )
@@excelisfun maybe I don't get because I can't spell LAMBDA😁Just realized I typed LAMDA.
@@excel_along_the_way Is it there when you type it correctly?
Absolutely,, it is sum of the excel
Glad you like it, Emre!!!
Great effort
Glad you like it, Farid!!!
Beautiful
Glad you like the beauty, C19 Curfew!!!!
Thank you for showing what is possible. At some point in this video you crossed over into ‘mad scientist’ territory though.
Knowing what is possible is always good, even if it approaches ‘mad scientist’ territory lol Thanks for the new terminology, Jonathan : )
That's a neat looking formula, but wouldn't pulling a quick pivot table be so much quicker and simpler?
Much, much quicker and easier. For reporting we almost always use standard or Data Model PivotTables. But when there is the need for instant update - Bam! We got it : ) The other reason we might make LAMBDA functions is when we have the same report often, then make the function and re-use : )
Dear Mike,
I had a good job developing a new LAMBDA but found the old one didn't need to be fixed as Microsoft fixed the issue with the volatile functions. 🤗
Wait... What? You say that Microsoft fixed volatile functions? I had not heard this. Where did you hear it?
@@excelisfun I didn't hear that, I tested the LAMBDA that I sent five months ago, which wasn't turning into a function, and I found that it was working as expected: it turned into a function🤗
@@JoseAntonioMorato O, I see : )
Dear Mike,
I'm posting again.
I hope it doesn't get deleted again. I would like your comment about the LAMBDA that I developed with your idea. 😁
=LAMBDA(Table,
LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3),
rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}),
u,CHOOSECOLS(rh,2),
VSTACK(
{"SalesRep","EOM","Sales"},
HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
Awesome formula. A few commenters sent in formulas in the last few videos with the same "single table input". Thanks for the formula and I am glad that the comment finally came through. I have added your formula to the download file so the Team can check it out : )
@@excelisfun Dear Mike,
Thank you for including my name and my first LAMBDA in the download file.
However, the second LAMBDA was much better, due to the header being dynamic, while the header of the first formula is constant.
See the difference below:
=LAMBDA(Table,
LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3),
rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}),
u,CHOOSECOLS(rh,2),
VSTACK(
HSTACK(OFFSET(p,-1,,1,1),OFFSET(d,-1,,1,1),OFFSET(s,-1,,1,1)),
HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
When creating a course/series, have you had better luck releasing all of the videos for it at one time or periodically?
I just release as I make them. So I don't really know...
@@excelisfun Ok, I wasn't sure what your process was when creating full series. Thanks
@@PlaybookGamer The process is: a looooooooong time to create classes. Maybe I am just slow, though ; )
@@excelisfun I think I will finally purchase Camtasia. I can't find another program out there that does a great job of adding effects, annotations, etc like Camtasia. If I want to take video editing seriously then I need Camtasia, regardless of the cost. I point out many things in my videos now so having an editor that can amplify this with those effects will make all the difference. Thanks for the recommendation.
@@PlaybookGamer It is the program that I use and I do a lot of editing : ) Great to hear that you are going to do a lot of editing too : )
Maybe I need to practise LAMBDA and LET to get more fun!
Practice can always add more fun : )
Hi mike, quick question, back in the day(like 10 years ago) you mentioned that you didn't want actually precisely that you couldn't program in excel yet, I see you are now manipulating variables with let, and building function with lambda so you are effectively programming right ? So how did you take the leap to become a non programmer to an advanced excel programmer ?
Hi Mike do you have the pdf notes. It will be very helpful
I am just one human. I create everything: videos, files, web sites and pdf notes. With 12-15 hours of work each day, all I can muster is pdf notes for my classes, such as Excel Basics, Advanced Excel, Excel Data Analysis, Microsoft Power Tools Data Analysis classes, Statistics and others.
Expected this and as usual good one. In the excel version 2204 Build 16.0.15121.20000 64-bit , for the spilled array conditional formatting has one issue that the double underline border around the cell option is not available. Is any one has the similar issue. Thanks
Yes, 100% there are problems with dynamic spilled arrays and conditional formatting. There are numerous situations where conditional formatting does not work. I have report it but have heard no reply.
Can you send me your workbook with this issue to excelisfun at gmail. I would like to see your situation.
@@excelisfun , yes and sent the file
@@KgasS I did not get file yet, but I look forward to file. I will present to Microsoft with some of my examples.
@@KgasS Can you send the file again to excelisfun at gmail.
Dear Mike,
I developed a new LAMBDA, but I can't share it as my comment is deleted from the page.☹
I need some help: I've just started a new job and the company is using Office 2013. I'm coming from a Microsoft 365 environment. Question: If I use 365 at home for my awesome spreadsheets, how will they behave in Office 2013 when I send my colleagues the reports?
A lot of what is in M 365 will not work in Excel 2013. All this dynamic array stuff will not work at all. PivotTables should work.
@@excelisfun Thank you for your response and all of your outstanding work and dedication to learning.
@@philclemmons You are welcome : )
Looks like the comment, only with LAMBDA, hasn't been deleted. 🤗
1st comment
You get the first place trophy, Syed Hassan!!!!
@@excelisfun please advise if we need to concate customer/region as row header then how to put this in stack formula with one single formula. Kindly guide sir.
Dear Mike,
I made a comment and it disappeared. I believe it was turned off. Do you have any idea why. 🤔
Google / RUclips has been causing errors for over a year in the comment area. All of us, including me have had our comments deleted. I have reported this, but have had no reply. At this point, all we can do is to keep trying to post when post is deleted. A few viewers have sent important comments to me at my gmail (excelisfun at gmail) and I can try to post them. However, it is really egregious that the monopoly Google / RUclips has changed how comments work and have let this error/bug persist. It makes the beautiful dialog that we have here in the comments at the excelisfun channel much more difficult. But we don't give up, we just keep posting through this temporary error bug that the monopolist refuses to fix...
@@excelisfun Dear Mike.
I made another comment, replacing the one that was deleted, and now I am including the new improved LAMBDA, following your idea, and I would like your comment on it.
=LAMBDA(Table,
LET(d,INDEX(Table,,1),p,INDEX(Table,,2),s,INDEX(Table,,3),
rh,SORT(UNIQUE(HSTACK(p,EOMONTH(--d,0))),{1,2}),
u,CHOOSECOLS(rh,2),
VSTACK(
HSTACK(OFFSET(p,-1,,1,1),OFFSET(d,-1,,1,1),OFFSET(s,-1,,1,1)),
HSTACK(rh,SUMIFS(s,d,">"&EOMONTH(--u,-1),d,"
@@JoseAntonioMorato OK : ) I have updated the download file!! Go Team!!!
@@excelisfun Dear Mike,
I stated that the second LAMBDA was better than the first, and it is not 😕, because the OFFSET function returns a dynamic header, which is outside the table's range.
LAMBDA works if you manually insert the opening of the parentheses and the table data, but a strange person, who doesn't know what to insert, wouldn't know what to inform. 🤪
I apologize for the inconvenience and I also ask that if you know how to solve the problem so that LAMBDA works as a function, I would appreciate the help. 🤗
That's a neat looking formula, but wouldn't pulling a quick pivot table be so much quicker and simpler?
Of course. PT 100% faster. But when you need instate update or you want to make a re-usable function, that is when you use a formula like this. PT almost all the time, but once in a while when requirements are different... : )