Love your videos. Very easy to understand. I have been doing Excel Development since 1993 and this is how I would have done it before the introduction of the new functions. =SUM(OFFSET($C$3:$N$7,MATCH($B13,$B$3:$B$11,0)-1,0,1,12)*(ROUNDUP(MONTH($C$2:$N$2)/3,0)=COLUMNS($C$12:C$12))) by doing COLUMNS($C$12:C$12) you don't have to worry about the one person in a hundred (probably less) that likes to insert columns where ever they like. with COLUMN(A:A) if someone inserts a column at column A everything gets wrecked. This way also gives the flexibility if the order of the items needs to be different in the quarterly summary.
Thank you Mynda, Peter, and Sergei! I am obsessed with these new formulas and also spend hours trying to figure them out and make them work; it makes me question my knowledge and abilities too!! I can't wait to dig into the formulas provided to see what else I can learn and use! Thank you so much for sharing and inspiring! Mynda, my love for and knowledge of Excel grew into what it is today because I watched a video you made on dashboards about 8 years. My "excel" feet have never stopped running since! Thank you!!
That was a lot to digest, but enjoyable to watch and follow along. For a moment I thought did I click on a Diarmuid Early video? Hats off to Peter, Sergei, and yourself. Thanks
I haven't come across Sergei before but I just bow down in awe every time I see anything from Peter Bartholomew - really quite astonishing! And thank you Mynda for the very coherent explanation. I know that I will have to spend a bit of time getting my head round that one.
Thank you Mynda for your always valuable videos which are containing the new ideas to solve the problems we are facing all time when we are dealing with excel 🙏
It is like a magic. I need to work a lot to understand each function individually. But the explanation given at the bottom of video was very helpful to visualize how function works. Thanks a lot for such an informative tutorial.
That's mind blowing, Mynda! Thanks to all of you, for sharing this rich knowledge with us. There are so many tricks in this one video, which will open up wide possibilities and applications, and that is really great! 🙂
HOLY COW!!! That was amazing!!! Thank you so much for the time you, and the others spent figuring this out - and especially for explaining it so thoroughly in your video. Amazing stuff!!
Really interesting Mynda, thanks. When it gets to the single cell formula, I try to focus solely on reusability, and so I would have split this into two "reusable" lambdas. First would be a function called QUARTERS(dates,returnType), which would be a function that either returns quarter (QQ) or quarter and year (QQ-YY). Second is a GROUPBY(array, columnToSum,includeTotals), which would take the array of quarter numbers from the QUARTERS() function and sum the values and include totals if requested. Obviously this would have to be used in BYROW() to get the result for each item. Alternatively a better method might be to create an UNPIVOT() function to mirror PQ, which unravelled the table into Date, Item, Value before applying the above quarter and group functions. This approach would leave you with three fairly reusable, and regularly so, functions that you could port across to other workbooks as required. You could obviously go further with group by, to include options for summing, average etc, and using hstack to group by multiple columns, perhaps with subtotals and such, but that'd keep you going for about ten videos!
Great ideas and in fact if you were to look at Peter's actual file you'll see he stores the lambdas in the name manager which vastly simplifies the formulas, but for the purpose of teaching, I moved it all into one formula so I could step through it together. I like the idea of an UNPIVOT lambda that could be reused 👍
@@MyOnlineTrainingHub I think the following should work as an UNPIVOT function: =LAMBDA(data,includesRowTotals,includesColTotals, LET( d,data, r,ROWS(data)-includesRowTotals, c,COLUMNS(data)-includesColTotals, seqD,SEQUENCE((r-1)*(c-1)), indC,MOD(seqD-1,c-1)+2, indR,ROUNDUP(seqD/(c-1),0)+1, colVector,INDEX(data,1,indC), rowVector,INDEX(data,indR,1), valuesVector,INDEX(data,indR,indC), return,HSTACK(colVector,rowVector,valuesVector), return) )(B4:N9,,) where data is your table, and "includesRow/ColumnTotals" will just ignore the respective column or row if there is one (0 for none, 1 for total col/row). I'm sure there is a better way. Getting into it just now, I should have created a "REPEATVECTOR" function to create the respective vectors! That's the wonder of LAMBDA I guess.
Yes, there was a lot more simple function! UNPIVOT: =LAMBDA(data,includesRowTotals,includesColTotals, LET( d,data, r,ROWS(data)-1-includesRowTotals, c,COLUMNS(data)-1-includesColTotals, return,MAKEARRAY(r*c,3, LAMBDA(i,j,INDEX(d,IF(j=1,1,ROUNDUP(i/c,0)+1),IF(j=2,1,MOD(i-1,c)+2)))), return) )(B4:N9,,) I forgot about MAKEARRAY, which saves on a sequence, and I also forgot that INDEX is very clever, and can take IF statements within its parameters and output an array of vectors.
I found the demonstration very effective. Thank you very much for sharing your know-how. i'm trying to adapt the same thing to calculate subtotals using BYCOL, but after 3 days i haven't managed to do it, so i'm writing this commentary in the hope that you'll be able to make a video along these lines.
Glad it was helpful! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, greetings from Miami, Florida. My hat off to Peter and Sergei. I am, however, inclined to use Power Query and Pivot Tables, as I am a lot more confident in using thanks, to a great extent, to your videos.
Those guys are amazing. I've personally received help from Peter Bartholomew in the recent past and I can attest that his knowledge of excel formulas exceeds mine in orders of magnitude!
I am still so confused and lost on these new formulas. I keep trying to see how I can add them to my reports, but I just can't. :( I have watched all of your power query videos and am in love with it!) Thanks for all the great videos and great, straight-forward explanations!
Thank Mynda for the brilliant explanation. Have you ever tried to combine the scan and Byrow function to get an accumulator for each row like a running total. I’ve tried but without success. I’m trying to get the scan function to work across the array but on a row by row basis, once again without having to drag down….. that’s so 90’s after all 😂
Thanks for watching, Brian! I haven't used these new functions much, yet. The running total sounds like a great case for these functions. I'll keep it in mind for future videos.
Thank you for the great video. I need the learn a lot more here. Could you suggest a simple formula to insert to your above formula select rows (which have dates), columns with values . The sum will sort dates from Jan-March into Q1, April-June into Q2 etc. Dont shoot me. I am a beginner. ps. I already use SUMIF to collate the dates. How to fit SUMIF into yours formula.
Glad you liked it 🙏 Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great example of the newer functions. I’ve always used sumifs with two rows in the header dictating the start and end of the sum. Are the one formula methods faster? They look easier to audit.
Good evening, ma'am First of all, thank you for all of these amazing tutorials - they help me a lot. Actually I had a doubt which I failed to resolve by myself. I was wondering if it's possible to import multiple CSVs from a folder as different queries to power query at once without actually combining them? Thank you, any help will be highly appreciated.
@@MyOnlineTrainingHub Thank you very much for the reply ma'am! I was able to stumble upon a solution for the same. I was trying anything that I could imagine out of anxiety to get my work done and I realised that we can attach a folder to ODBC and use use ODBC to load multiple CSVs from that folder as different queries into the power query. Funny enough, it's such a simple solution to such a big problem but it's no where to be found on the internet.
Thank you so much mam Mam i want make career in Data analyst So is it better to learn ms Excel deeply all functions Not depend on copilot and other ai tools
Mam please ans me Copilot 365 will available in soon Ms Excel Should I learn ms Excel all functions and advance ms Excel or I can do my all work by copilot 365 i am confused Please ans me
Great question. Copilot will certainly help with standard formulas, and possibly more complex ones, but it will only be available with Enterprise licenses, so you'd want to hope that you only ever work for a company that is paying for the top tier license fees. Of course, it will always be quicker to write your own formulas if you know how. I know if I were looking for an employee to hire, I'm going to choose one who doesn't need to solely rely on AI to do their job.
Is there any way to track the rows that have been removed from a table because of a duplicate id (while other columns in the row do not have duplicated data)? Thank you in advance!
Once data is removed in Excel you can use the Track Changes tool if you have M365 to see how cells have been changed: ruclips.net/video/XfgDfUEV0fM/видео.html
I love your videos. I really do. However, this time its a huge overkill. Add a simple calculation on the side for 3 months, copy and paste. No complicated array formulas and easily understood for next quarter’s review
I guess if you're happy to do the work each quarter, then you do whatever suits. 😊 If you prefer a one and done formula, then these are options to consider. Or...just use Power Query and PivotTables, which is my preference: ruclips.net/video/JXAz19poVek/видео.html
This will be interesting to digest. How could I have flexibility to have a fiscal quarter/year. e.g., fiscal year is Oct - Sep and therefore Q1 would be Oct - Dec?
I'm not at my PC just now unfortunately. However, you could use the MOD() function to determine the fiscal month number and then the same ROUNDUP() function as the video from there to get the quarter. Something like: =ROUNDUP((MOD(yourDateRange+3,12)+1)/3,0) I might have the +1 wrong, but if you play about with the two functions on their own, you'll get it.
Had you thought of doing it like this? LET( quantity,$C5:$N9, mths,$C$4:$N$4, qtrs,ROUNDUP(MONTH(mths)/3,0), MMULT(quantity,TRANSPOSE(N(qtrs=SEQUENCE(4)))))
Seems very unintuitive to put this data horizontally. We tend to put the dimension that we are going to Add more data to in the rows - that is new quarters /months.
Not as an accountant. Accountants have their chart of accounts in the rows and time across the columns. But there are equivalent solutions if you have your data transposed in the file you can download from the link in the video description.
Did you watch to the end? I say that at the end, but the limitation with this data is it's not in a tabular format, so we have to unpivot it first with Power Query. Here's the video link: ruclips.net/video/JXAz19poVek/видео.html
Why not just do a sum offset or add some helper rows and use sumifs. Even someone who is an expert at excel would take ages to figure out how to audit this formula and understand its mechanics without watching a 12 min video
If you look at Peter's file, you'll see he has simplified them into custom LAMBDA functions that are easy to use, even for beginners. If it's an auditor issue, then they will eventually have to get their skills up to speed, because these functions aren't going away.
one way can be =LET(a,C5:N9, sa,BYROW(CHOOSECOLS(a,{1,2,3}),LAMBDA(s,SUM(s))), sb,BYROW(CHOOSECOLS(a,{4,5,6}),LAMBDA(s,SUM(s))), sc,BYROW(CHOOSECOLS(a,{7,8,9}),LAMBDA(s,SUM(s))), sd,BYROW(CHOOSECOLS(a,{10,11,12}),LAMBDA(s,SUM(s))), HSTACK(sa,sb,sc,sd))
The good old Sumproduct (wrapped into makearray) worked for me =LET(monthstarts, SEQUENCE(1,4,0,3), monthends, SEQUENCE(1,4,2,3), source,B8:I12, MAKEARRAY(ROWS(source),COUNT(monthstarts),LAMBDA(a,b,SUMPRODUCT(source*(SEQUENCE(ROWS(source))=a)*((SEQUENCE(1,COLUMNS(source),0,1)=INDEX(monthstarts,1,b)))))))
Did you say Peter and Sergei sent you 8 formulas, yet am struggling to WRAPMYHEAD onto only one? But in your hands, we are gratefully covered! Thanks Mynda!
Love your videos. Very easy to understand.
I have been doing Excel Development since 1993 and this is how I would have done it before the introduction of the new functions.
=SUM(OFFSET($C$3:$N$7,MATCH($B13,$B$3:$B$11,0)-1,0,1,12)*(ROUNDUP(MONTH($C$2:$N$2)/3,0)=COLUMNS($C$12:C$12)))
by doing COLUMNS($C$12:C$12) you don't have to worry about the one person in a hundred (probably less) that likes to insert columns where ever they like.
with COLUMN(A:A) if someone inserts a column at column A everything gets wrecked. This way also gives the flexibility if the order of the items needs to be different in the quarterly summary.
Thanks so much for sharing 🙏😊
Thank you Mynda, Peter, and Sergei! I am obsessed with these new formulas and also spend hours trying to figure them out and make them work; it makes me question my knowledge and abilities too!! I can't wait to dig into the formulas provided to see what else I can learn and use! Thank you so much for sharing and inspiring! Mynda, my love for and knowledge of Excel grew into what it is today because I watched a video you made on dashboards about 8 years. My "excel" feet have never stopped running since! Thank you!!
Aw, your kind words are what keeps me going. Thank you so much. I'm grateful that I can share these videos with you 🥰🙏
Dear Mynda, so much information for seemingly quite easy calculation. amazing. clearly explained. Thank you.
Thanks so much!
That was a lot to digest, but enjoyable to watch and follow along. For a moment I thought did I click on a Diarmuid Early video? Hats off to Peter, Sergei, and yourself. Thanks
😁 thanks for watching, Michael! Glad you enjoyed it.
I haven't come across Sergei before but I just bow down in awe every time I see anything from Peter Bartholomew - really quite astonishing! And thank you Mynda for the very coherent explanation. I know that I will have to spend a bit of time getting my head round that one.
Glad you enjoyed the video, Peter 🙏😊 have fun deciphering the files.
Thank you Mynda for your always valuable videos which are containing the new ideas to solve the problems we are facing all time when we are dealing with excel 🙏
Thanks for watching!
Mynda, once again you have demonstrated something which I can use in my real-world reporting scenarios. Thank you. Clear and concise as always.
Awesome to hear 🙏
A ton of new formulas I didn't know about in this one. Kudos to all, very clear explanation!
Great to hear! 🙏😊
It is like a magic. I need to work a lot to understand each function individually. But the explanation given at the bottom of video was very helpful to visualize how function works. Thanks a lot for such an informative tutorial.
So pleased you enjoyed it 🙏😊
Nicely done here Mynda, real easy to follow and informative!
Thanks so much, Josh!
That's mind blowing, Mynda! Thanks to all of you, for sharing this rich knowledge with us. There are so many tricks in this one video, which will open up wide possibilities and applications, and that is really great! 🙂
Thanks for watching, Vijay! Glad you enjoyed it. 🤯😁
Peter is showing his amazing LAMBDA skills almost every day in the Techcommunity.
He's super helpful!
@@MyOnlineTrainingHub😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊
HOLY COW!!! That was amazing!!!
Thank you so much for the time you, and the others spent figuring this out - and especially for explaining it so thoroughly in your video. Amazing stuff!!
Thanks for watching! So glad you liked it 😊
Makes my head spin. Amazing what can be done,
I know, right?! 😁
Very clever and helpful, thanks Mynda! So awesome
Cheers, Chris! 🙏😊
Really interesting Mynda, thanks.
When it gets to the single cell formula, I try to focus solely on reusability, and so I would have split this into two "reusable" lambdas. First would be a function called QUARTERS(dates,returnType), which would be a function that either returns quarter (QQ) or quarter and year (QQ-YY).
Second is a GROUPBY(array, columnToSum,includeTotals), which would take the array of quarter numbers from the QUARTERS() function and sum the values and include totals if requested.
Obviously this would have to be used in BYROW() to get the result for each item. Alternatively a better method might be to create an UNPIVOT() function to mirror PQ, which unravelled the table into Date, Item, Value before applying the above quarter and group functions.
This approach would leave you with three fairly reusable, and regularly so, functions that you could port across to other workbooks as required. You could obviously go further with group by, to include options for summing, average etc, and using hstack to group by multiple columns, perhaps with subtotals and such, but that'd keep you going for about ten videos!
Great ideas and in fact if you were to look at Peter's actual file you'll see he stores the lambdas in the name manager which vastly simplifies the formulas, but for the purpose of teaching, I moved it all into one formula so I could step through it together.
I like the idea of an UNPIVOT lambda that could be reused 👍
@@MyOnlineTrainingHub I think the following should work as an UNPIVOT function:
=LAMBDA(data,includesRowTotals,includesColTotals,
LET(
d,data,
r,ROWS(data)-includesRowTotals,
c,COLUMNS(data)-includesColTotals,
seqD,SEQUENCE((r-1)*(c-1)),
indC,MOD(seqD-1,c-1)+2,
indR,ROUNDUP(seqD/(c-1),0)+1,
colVector,INDEX(data,1,indC),
rowVector,INDEX(data,indR,1),
valuesVector,INDEX(data,indR,indC),
return,HSTACK(colVector,rowVector,valuesVector),
return)
)(B4:N9,,)
where data is your table, and "includesRow/ColumnTotals" will just ignore the respective column or row if there is one (0 for none, 1 for total col/row).
I'm sure there is a better way. Getting into it just now, I should have created a "REPEATVECTOR" function to create the respective vectors! That's the wonder of LAMBDA I guess.
Yes, there was a lot more simple function!
UNPIVOT:
=LAMBDA(data,includesRowTotals,includesColTotals,
LET(
d,data,
r,ROWS(data)-1-includesRowTotals,
c,COLUMNS(data)-1-includesColTotals,
return,MAKEARRAY(r*c,3,
LAMBDA(i,j,INDEX(d,IF(j=1,1,ROUNDUP(i/c,0)+1),IF(j=2,1,MOD(i-1,c)+2)))),
return)
)(B4:N9,,)
I forgot about MAKEARRAY, which saves on a sequence, and I also forgot that INDEX is very clever, and can take IF statements within its parameters and output an array of vectors.
Great content and great explanations! Thank you Mynda and the team!
🙏😊Glad you enjoyed it!
I found the demonstration very effective. Thank you very much for sharing your know-how.
i'm trying to adapt the same thing to calculate subtotals using BYCOL, but after 3 days i haven't managed to do it, so i'm writing this commentary in the hope that you'll be able to make a video along these lines.
Glad it was helpful! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Loved It!
Loved It!!
Loved It!!!
THANK YOU!
Thanks so much! 😁🙏
Hi Mynda, greetings from Miami, Florida. My hat off to Peter and Sergei. I am, however, inclined to use Power Query and Pivot Tables, as I am a lot more confident in using thanks, to a great extent, to your videos.
Thanks so much for watching! I too prefer PQ and PivotTables 😉
Thank you! My head is still hurting, though.
😁 mine too!
❤Thanks formula Kings, and Queen 👍😎✊
Cheers, Steven 🙏😊glad you enjoyed it.
Nicely done, Mynda. Nicely done. 😎
Thank you!! 😁 🙏
Thank you very much for the great content!!!
Glad you enjoy it!
Those guys are amazing. I've personally received help from Peter Bartholomew in the recent past and I can attest that his knowledge of excel formulas exceeds mine in orders of magnitude!
Mine too, Rafael! 😊 Thanks for watching.
well done thank you Mynda. You're having too much fun too. Love it
😁 thank you!
You guys are from different planet, no idea what is going on… 😂😂😂😂😂 it sure looks amazing
Thanks for watching 😁
I am still so confused and lost on these new formulas. I keep trying to see how I can add them to my reports, but I just can't. :( I have watched all of your power query videos and am in love with it!) Thanks for all the great videos and great, straight-forward explanations!
I say go with what's best for you. If it were me, I'd use Power Query and PivotTables to do this too 😉
Delightful! 👏👏👏
Glad you think so! 🙏
AMMMMMAZING!
So glad you liked it, Catherine! 🙏😊 Peter and Sergei are exceptional.
Love em ❤
🥰🙏
Thank Mynda for the brilliant explanation. Have you ever tried to combine the scan and Byrow function to get an accumulator for each row like a running total. I’ve tried but without success. I’m trying to get the scan function to work across the array but on a row by row basis, once again without having to drag down….. that’s so 90’s after all 😂
Thanks for watching, Brian! I haven't used these new functions much, yet. The running total sounds like a great case for these functions. I'll keep it in mind for future videos.
Brilliant video 📹 👏 Thank u for sharing this challenging formula with us.
My pleasure!
Thank you for the great video. I need the learn a lot more here. Could you suggest a simple formula to insert to your above formula select rows (which have dates), columns with values . The sum will sort dates from Jan-March into Q1, April-June into Q2 etc. Dont shoot me. I am a beginner. ps. I already use SUMIF to collate the dates. How to fit SUMIF into yours formula.
Glad you liked it 🙏 Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great example of the newer functions.
I’ve always used sumifs with two rows in the header dictating the start and end of the sum.
Are the one formula methods faster? They look easier to audit.
Thanks, Joe! I haven't speed tested them, but they're not likely to be processed over huge tables, so I wouldn't have thought it would be noticeable.
Thanks to peter and sergei..even when we know we cant grasp the concept any way😂
😁Thanks for watching!
Hello @MyOnlineTrainingHub how about summarizing quarterly data to years? Thank you in advance!
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Good evening, ma'am
First of all, thank you for all of these amazing tutorials - they help me a lot. Actually I had a doubt which I failed to resolve by myself. I was wondering if it's possible to import multiple CSVs from a folder as different queries to power query at once without actually combining them?
Thank you, any help will be highly appreciated.
Glad you find my videos helpful! No, you can't import multiple files from a folder as separate queries.
@@MyOnlineTrainingHub
Thank you very much for the reply ma'am!
I was able to stumble upon a solution for the same. I was trying anything that I could imagine out of anxiety to get my work done and I realised that we can attach a folder to ODBC and use use ODBC to load multiple CSVs from that folder as different queries into the power query.
Funny enough, it's such a simple solution to such a big problem but it's no where to be found on the internet.
Interesting! Thanks for sharing 👍
Thank you so much mam
Mam i want make career in Data analyst
So is it better to learn ms Excel deeply all functions
Not depend on copilot and other ai tools
Yes, absolutely learn the functions relevant to being a data analyst and don't rely on AI.
Awesome
Thanks for watching!
Can this also cope with those of us whose quarters do not fit into a calendar year - e.g. fiscal year Apr - Mar?
Yes, but you'd have to use a different formula to map the months to their respective quarters.
I found that the column number isnt supported by excel fot web
Nice video. Can I ask how you are able to "step into" the part written formula at 2:08 to show the 1,1,1,2,2,2..etc
I believe that since May 2023, Excel365 does this when you highlight the portion of the formula you wish to evaluate.
Thanks, Joe! I use the F9 key to evaluate parts of the formula in the formula bar.
Mam please ans me
Copilot 365 will available in soon Ms Excel
Should I learn ms Excel all functions and advance ms Excel or
I can do my all work by copilot 365 i am confused
Please ans me
Great question. Copilot will certainly help with standard formulas, and possibly more complex ones, but it will only be available with Enterprise licenses, so you'd want to hope that you only ever work for a company that is paying for the top tier license fees. Of course, it will always be quicker to write your own formulas if you know how. I know if I were looking for an employee to hire, I'm going to choose one who doesn't need to solely rely on AI to do their job.
Is there any way to track the rows that have been removed from a table because of a duplicate id (while other columns in the row do not have duplicated data)? Thank you in advance!
Once data is removed in Excel you can use the Track Changes tool if you have M365 to see how cells have been changed: ruclips.net/video/XfgDfUEV0fM/видео.html
Wow😮
😊🙏
=LET(MINDBLOWN) !
😁I know, right! Peter and Sergei's formulas are next level.
I love your videos. I really do. However, this time its a huge overkill. Add a simple calculation on the side for 3 months, copy and paste. No complicated array formulas and easily understood for next quarter’s review
I guess if you're happy to do the work each quarter, then you do whatever suits. 😊 If you prefer a one and done formula, then these are options to consider. Or...just use Power Query and PivotTables, which is my preference: ruclips.net/video/JXAz19poVek/видео.html
This will be interesting to digest. How could I have flexibility to have a fiscal quarter/year. e.g., fiscal year is Oct - Sep and therefore Q1 would be Oct - Dec?
I'm not at my PC just now unfortunately. However, you could use the MOD() function to determine the fiscal month number and then the same ROUNDUP() function as the video from there to get the quarter.
Something like:
=ROUNDUP((MOD(yourDateRange+3,12)+1)/3,0)
I might have the +1 wrong, but if you play about with the two functions on their own, you'll get it.
I would've used this "=SUM(OFFSET($C5:$E5,0,3*COUNTA($C$12:C$12)-3))" in cell C13 and dragged down and across.
Thanks for sharing!
Had you thought of doing it like this?
LET(
quantity,$C5:$N9,
mths,$C$4:$N$4,
qtrs,ROUNDUP(MONTH(mths)/3,0),
MMULT(quantity,TRANSPOSE(N(qtrs=SEQUENCE(4)))))
or even
=LET(
quantity,$C5:$N9,
mths,$C$4:$N$4,
qtrs,ROUNDUP(MONTH(mths)/3,0),
MMULT(quantity,TRANSPOSE(N(qtrs=SEQUENCE(5))+N(SEQUENCE(5)=5))))
Nice, James! You are now the 3rd amazing formula writer I know 😁
Like this solution a lot. Might also switch "MONTH(mths)" to "MATCH(mths,mths,0)" in case the financial year isn't January to December.
Wowza, that was some formula….
Sure was, Graham! 😁 Thanks for watching.
Seems very unintuitive to put this data horizontally. We tend to put the dimension that we are going to Add more data to in the rows - that is new quarters /months.
Not as an accountant. Accountants have their chart of accounts in the rows and time across the columns. But there are equivalent solutions if you have your data transposed in the file you can download from the link in the video description.
7:28😂
Glad I gave you a giggle, Yves 😁
Mynda, don't bang your head - you could brain your damage!
😁
cannnot use pivot table?
Did you watch to the end? I say that at the end, but the limitation with this data is it's not in a tabular format, so we have to unpivot it first with Power Query. Here's the video link: ruclips.net/video/JXAz19poVek/видео.html
@@MyOnlineTrainingHub i take it there is no record of original raw data.
Fancy.
Thanks for watching!
I would be surprised if I manage to follow the formulas,.yes hurting my head
😁 I don't blame you, Patrick!
Why not just do a sum offset or add some helper rows and use sumifs. Even someone who is an expert at excel would take ages to figure out how to audit this formula and understand its mechanics without watching a 12 min video
If you look at Peter's file, you'll see he has simplified them into custom LAMBDA functions that are easy to use, even for beginners. If it's an auditor issue, then they will eventually have to get their skills up to speed, because these functions aren't going away.
one way can be
=LET(a,C5:N9,
sa,BYROW(CHOOSECOLS(a,{1,2,3}),LAMBDA(s,SUM(s))),
sb,BYROW(CHOOSECOLS(a,{4,5,6}),LAMBDA(s,SUM(s))),
sc,BYROW(CHOOSECOLS(a,{7,8,9}),LAMBDA(s,SUM(s))),
sd,BYROW(CHOOSECOLS(a,{10,11,12}),LAMBDA(s,SUM(s))),
HSTACK(sa,sb,sc,sd))
Nice alternative! Thanks for sharing 😊
The hard way...
Fair enough 😉
Please mam ans me my last comment
I did.
The good old Sumproduct (wrapped into makearray) worked for me
=LET(monthstarts, SEQUENCE(1,4,0,3),
monthends, SEQUENCE(1,4,2,3),
source,B8:I12,
MAKEARRAY(ROWS(source),COUNT(monthstarts),LAMBDA(a,b,SUMPRODUCT(source*(SEQUENCE(ROWS(source))=a)*((SEQUENCE(1,COLUMNS(source),0,1)=INDEX(monthstarts,1,b)))))))
Nice. Sergei had a couple of MAKEARRAY solutions too.
Doesnt work man tried like a 100 times
Perhaps you don't have these functions in your version of Excel.
@@MyOnlineTrainingHub perhaps.
Did you say Peter and Sergei sent you 8 formulas, yet am struggling to WRAPMYHEAD onto only one? But in your hands, we are gratefully covered! Thanks Mynda!
In the end it was more than 8 🤯too much fun for one person!😁
that was fab! love your vids! and your defintion of fun!
Thank you so much!! 😁
😁