I’m only 8 minutes into this video and can’t believe how much you’re able to pack into it. That formula looks daunting, but you break it down so simply.
Another great and useful video. Amazing teacher. (Just a point - 27 mins into video, the mention of that if you click out of the cell then formula writing disappears.....the toggle can be used, so when you click between sheets, the writing will not disappear, came by this by chance😄).....thank you for all your tutorials
Thank you Chandoo. This is great information. I'm an analyst and I've been using excel for 20+ years and the LET formula for about a year now and it's changed my life. I think Excel Labs may just take it to the next level. Just downloaded it!
1 question was it possible to VSTACK The headers on top of the Report array to create a fully 1 cell formula and if done that way what are the disadvantages?? I am an advanced excel user of 11 Years and even i learnt ALOT from you today Chandoo !! WOW How can i buy you a coffee this kind of work needs to be appreciated?
You're getting good! I'm actually starting to worry that you're giving away too many trades secrets now 😂 us professionals need to step up our game now.
Complex formula indeed! I have few of these myself, and I only use them in reports/files where I know that a person wont be asking to constantly change things for them (i.e. new requirements every now and then), because you rightly said at some point in the video, debugging is bit difficult/challenging. I like the arrangement though, especially the 13 weeks trend analysis, very neat!
Hi Master Chandoo , my teacher, thanks to keep me update through email and I can watch in detail here. Fantastic and so useful . Enjoy your great life in NZ
A very interesting example of the potentialities of the LET function in conjunction with HStack. How can comments be made within the LET function to make it easier to understand?
Another amazing video!!! I am very grateful that you share such videos which contains so much useful information freely. I have learned much by watching your videos and every time you teach me something new. Thank you very much for sharing your excellent Excel skills.🙏🙏
Thanks Chandoo 👍👍👍 Maybe it's worth putting the whole thing in a LAMBDA function with three arguments in the form of individual tables? It would be less scary for not very advanced users and more resistant to unwanted actions.
Yes, we can. As this is a very specific, non-repeating thing, I think a LAMBDA wouldn't be needed. But the AFE makes it easy to convert such formulas to LAMBDAs.
Hi -- could you explain the LAMBDA -- how does that work? You define "a", then make the 2nd part of the formula equal to a. I don't fully understand whats going on there. Thanks!
This is great. I’m currently working on a report that has over 200 tables with data that I need to combine with vstack, then filter and use the reduce function. Is there a million row limit to vstack? Or can I put this into the Let function and it will work?
I am not sure if all the formulas are available in Excel 2021. I suggest using Excel on the web as most of these should work with that (including Excel labs).
At this point, you're basically coding. In such a case, it's important to focus on readability, so I'd recommend more formatting, such as organizing your code blocks into separate lines. That's the first step to making it easy to understand and maintain later. Having this all bunched together with no line breaks (and blank spacing between sections of code) is one of the primary reasons that the formula is so difficult to follow.
There are multiple reasons why 1: Sorting the entire report is powerful but only possible when the entire report is an array 2 : Filtering an array is more accurate than filtering individual columns
Because we can. I explain my reasons at the end. It is an interesting experiment. I got to learn many new ways of working with long formulas. Hopefully you got to takeaway something too.
You're here for Excel.
I'm here for Chandoo jokes.
I’m only 8 minutes into this video and can’t believe how much you’re able to pack into it. That formula looks daunting, but you break it down so simply.
Another great and useful video. Amazing teacher. (Just a point - 27 mins into video, the mention of that if you click out of the cell then formula writing disappears.....the toggle can be used, so when you click between sheets, the writing will not disappear, came by this by chance😄).....thank you for all your tutorials
Teacher... that was amazing. This video made me fall in love with Excel again. Thank you so much
Thank you Chandoo. This is great information. I'm an analyst and I've been using excel for 20+ years and the LET formula for about a year now and it's changed my life. I think Excel Labs may just take it to the next level. Just downloaded it!
Glad it was helpful!
1 question was it possible to VSTACK The headers on top of the Report array to create a fully 1 cell formula and if done that way what are the disadvantages??
I am an advanced excel user of 11 Years and even i learnt ALOT from you today Chandoo !! WOW How can i buy you a coffee this kind of work needs to be appreciated?
Thanks a lot. As usual. Best Regards!
Thank you for this video as it helped me to understand some new and functions better.
You're getting good! I'm actually starting to worry that you're giving away too many trades secrets now 😂 us professionals need to step up our game now.
Complex formula indeed! I have few of these myself, and I only use them in reports/files where I know that a person wont be asking to constantly change things for them (i.e. new requirements every now and then), because you rightly said at some point in the video, debugging is bit difficult/challenging.
I like the arrangement though, especially the 13 weeks trend analysis, very neat!
Thank you, Chandoo. You presentation is provided in such a simple way to follow. It is clear and concise.
Epic formula, thanks Chandoo!
Hi Master Chandoo , my teacher, thanks to keep me update through email and I can watch in detail here. Fantastic and so useful . Enjoy your great life in NZ
A very interesting example of the potentialities of the LET function in conjunction with HStack.
How can comments be made within the LET function to make it easier to understand?
Thanks for sharing your knowledge to everyone .
Another amazing video!!!
I am very grateful that you share such videos which contains so much useful information freely. I have learned much by watching your videos and every time you teach me something new.
Thank you very much for sharing your excellent Excel skills.🙏🙏
Thanks Chandoo 👍👍👍
Maybe it's worth putting the whole thing in a LAMBDA function with three arguments in the form of individual tables?
It would be less scary for not very advanced users and more resistant to unwanted actions.
Yes, we can. As this is a very specific, non-repeating thing, I think a LAMBDA wouldn't be needed. But the AFE makes it easy to convert such formulas to LAMBDAs.
It's amazing sir... Thank you very much..
Dude, you're the boss of excel!
Love this!
Thanks 🙏 SIR ❤
Amazing!!!
Awesome as usual Purna ..
Thanks sir
Boss, you are the ultimate
Hey chandoo I really like your videos and I never forgot to give you a 👍
Thank you so much 😀
Hi -- could you explain the LAMBDA -- how does that work? You define "a", then make the 2nd part of the formula equal to a. I don't fully understand whats going on there. Thanks!
Nice....
Guruji❤❤❤❤❤❤❤❤
chandoo bro google data analyst capstone project
gurinchi oka video chei bro
This is great. I’m currently working on a report that has over 200 tables with data that I need to combine with vstack, then filter and use the reduce function. Is there a million row limit to vstack? Or can I put this into the Let function and it will work?
If you have such large volume of data, use power pivot and DAX to do you calculations. Formulas will be slow and hard to debug.
@Chandoo are we able to create a paginated type report with this and lambda recursion?
Can you elaborate a bit more on what you need?
Hi Chandoo Ji, Do you see any changes after wearing braces? I think a year has paste, when are you going to remove them?
Hi chandoo , will this work in excel 2021 version version built 2045. I am not able to do it , the grid option under excel labs is blank
I am not sure if all the formulas are available in Excel 2021. I suggest using Excel on the web as most of these should work with that (including Excel labs).
How to tell if I have Excel 365??
Amazon 🎉
At this point, you're basically coding. In such a case, it's important to focus on readability, so I'd recommend more formatting, such as organizing your code blocks into separate lines. That's the first step to making it easy to understand and maintain later. Having this all bunched together with no line breaks (and blank spacing between sections of code) is one of the primary reasons that the formula is so difficult to follow.
You have to watch the tutorial before making comments like this. The formula is neatly arranged thru advanced editor as shown in the video.
My manager will fire me if he see this thing.
Genius
The question is WHY?
There is no why; there is no spoon.
There are multiple reasons why
1: Sorting the entire report is powerful but only possible when the entire report is an array
2 : Filtering an array is more accurate than filtering individual columns
Because we can. I explain my reasons at the end. It is an interesting experiment. I got to learn many new ways of working with long formulas. Hopefully you got to takeaway something too.
Because it’s there.
@@chandoo_ appreciate these epic walkthroughs 👍👍
UAU
Top quality content! Totally deserved to be part of an excel conference. Respect 🫡