Everyone I work with thinks of me as an Excel expert. I watch these videos and think of my skillset as novice, ...at best. Love learning new Excel skills, but still have a long way to go. At least I'm able to follow along and just shake my head and say "Wow!"
Big efforts to bring that crazy solution to us. Using logic operations?! Oh my God, I really forgot it since I graduated from the Engineering faculty. One of our professors told us once the logic science does exist to make our life easier. Your lesson is a proof of that. Thanks Mike. I really appreciate your time and efforts to make us the best.
Well, Ibrrahim, I am glad that the logic science will be of help here!!! And I think your professor is right: sometimes it does come in handy : ) You are welcome for the efforts!!
Mike: yay! You did it - with a little help from the team, but still. And as you so adamantly -and rightfully!- responded: the crazy array formulas are back!... because they never left. There are a few things we can take home from this: - you use an ‘outer’ product to create a matrix from two vectors (something we already know for years) - and more importantly: we now know how to append arrays, - and we now know how to use ‘masks’ to ‘paint’ specific formulas to specific places in the final array. In conclusion: the formulas will always (end up) be(ing) crazy, but the stuff we can do with them will be more and more advanced. The latter is called: “Evolution”. Although right now with all the new stuff it feels more like “Revolution” ;-). Can’t wait to see you on the next one.
Go Team!!!! Array Formula will always be with us!!!! Love that we can do Outer Matrix We can Append Arrays LOVE your terms here: "use ‘masks’ to ‘paint’ specific formulas to specific places in the final array" Evolution and Revolution!!!! Next videos are videos are 1521, 1523, 1524, 1525, 1526, 1527... I stopped doing them in order, but only because I was so excited about the latest video after planning the earlier videos.
I am sorry... it is not fair. Microsoft is not fair. I wish I could make it less NOT fair. Hopefully all of us who see he amazing power of these Dynamic Arrays, will convince Microsoft to make changes so we all can have this power. I have already e-mails Joe McDaid about the survey we did and asked if MS can soon let everyone have Dynamic Arrays!
Hey Mike and David.. totally insane and way cool! Hopefully, Microsoft will make it easier with a TOTALS function or some kind of TOTALS argument built into the base formula generating the spilled range. Until then it is still the Wild West with these unbelievable and intense solutions that you guys visualize and make into reality. Double Thumbs up on this one!!
Yes, the Wild West!!!! Tomorrows EMT 1526 will show an alternative for totals. And then in EMT 1530 we'll do a more simple version of this one where we don't need the word "Total" Thanks for watching, commenting and supporting, Wayne!!!!
Yes, but you may be THE inventor of a formula that people will use for decades!!!! Let it be Date - Time Marked here that, so far, for are the inventor : )
Jealous because my company is STILL using Excel 2013, but is looking to upgrade to 365 early next year. Still using the curly brackets and Ctrl+Shift+Enter for now :( Will be coming back to all these 365 exclusive videos again once that happens :) Thanks for posting all these dynamic array videos, they truly are amazing!
You are welcome, Micah! Thanks for your support on each video : )The playlist for all the videos are here: th-cam.com/play/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx.html
Microsoft: Hey guys you don't need crazy formulas to do arrays any more. :D Mike G: Hey Microsoft, look at this crazy formula because we don't have totals on arrays. Your move :P
Well, I think you are right. It's crazy that we can create a cross tabular report with a short formula but have to go one step back and create insane long formulas to get the totals.
Well... As you all know (Geert, XLarium, Jamie), before Dynamic Arrays we had a much harder time accomplishing this, so even though this is complicated, it is great that we can do it now. But, as you suggest, it would be great if it was easier : )
This illustrates my two “requests for improvement “ for dynamic arrays. Totals are hard. Formatting results is hard. Both would be solved if I could use dynamic array references in an Excel Table !!
Hello Mike! As always, another great video. With these all new family of array function I have a the impression that the Ctrl+Shift+Enter keystroke will no longer be necessary. Don't you? Thanks again! 👍
Whaaaaat ?!!! .... AMAZING ....But .... i guees microsoft has to make a "Total" function attached for dynamic arrays or a set of functions ...just like tables predefines formulas ...right?
Maybe... But, I don't know... The IF to Append seems like a straight forward solution that has been available for a lone time. But, I don't think that we have had much opportunity to use it until the Spilled Arrays : )
Great as always, but it is a very difficult formula. I appreciate, if we will have an easy formula like, sorting is difficult but with SORT formula, it's become easy. But really a great and gigantic effort to get totals (horizontal and vertical). Microsoft should have provided, something like Appending Totals vertically and horizontally.
Yes, I agree that it should be easier. But what David showed us is something really amazing . Thanks for your support, Hassan!!! Plus, the next 5 videos will be more amazing solutions that the simpler Array Functions can provide : )
Very interesting video but it's rather on the complicated side. I wish Microsoft would simply take Charles Williams' Speed Tools and incorporate his floating totals functions into Excel.
Yes, if you go to the excelisfun channel, just search on any topic. There are at least 10 or so videos. Here is one: th-cam.com/video/PzshpJbc8CE/w-d-xo.html
There is a general issue which arises from the desire to show row and column totals, though. Traditional development assumes the user is hell-bent on selecting data manually rather than expending intellectual effort to describe their selection. As a result, there is only one function in Excel that will select each row (or column) of a two-dimensional array and aggregate to give a column of results. MMULT and TRANSPOSE will do the job provided you are content to limit the aggregation to a 'weighted sum' of the row but they are not the friendliest functions in town for a non-mathematician. But what if you want to find the smallest value in each row (SMALL); or to ensure that a condition is satisfied in every column (OR or PRODUCT), or count the number of matches (COUNT), or concatenate each string within the row (TEXTJOIN)???? It would be possible (I think) to use SEQUENCE in combination with INDEX to split a 2D array into rows but the resulting column is not naturally an array because of the way in which INDEX treats array-indices. Either AGGREGATE needs a new function numbers for 000+n, aggregate over row and columns to give a single value 100+n, aggregate over rows to give a column array 200+n, aggregate over columns to give a row array 300+n, aggregate cell by cell to combine corresponding terms within multiple, similarly-dimensioned references; or, alternatively, one could envisage functions BYROW(), BYCOLUMN() which would cause the 2D array to be treated as a collection of ID arrays up to the point where they are aggregated. Do you think we should push for something like this on user voice?
BYROW() and BYCOLUMN() would be great to have!!!! It is always worth a try at User Voice, but I suspect that since Dynamic Arrays are so new, it might not get much traction at this point.
I am not sure what you mean. I think that being able to append an array with an Array Formula is an Epic element on the Array Formula Pantheon. Not only that, but there have been MUCH more insane Array Formula Constructions that have been used to solve common problems in that past, However, I do agree, that if Microsoft could program more of our needs into functions, it would be better. Also, as new Functiosn are created like UNIQUE and SORT, then the imagination is ignited into new directions, and thus we get great solutions like this...
Mike. Just doing a catch up on your fine videos. I wondered, would the following work for a slightly simpler returning of totals: =SUMIFS(fRevenue[Revenue],fRevenue[Product],IF(MAX(ROW(H10#))=ROW(H10#),"*",H10#),fRevenue[Region],IF(MAX(COLUMN(I9#))=COLUMN(I9#),"*",I9#)) I'm virtually certain it does! However, I could be going crazy. Edit to add back in the match formulas I removed: =SUMIFS(fRevenue[Revenue],INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)),IF(MAX(ROW(H10#))=ROW(H10#),"*",H10#),INDEX(fRevenue,,MATCH(I5,fRevenue[#Headers],0)),IF(MAX(COLUMN(I9#))=COLUMN(I9#),"*",I9#))
Very cool formula!!! I could net get it to work... I kept getting an error. This is why MS needs to deploy the new Array Features to everyone, so that you are not denied the opportunity to try this for your self. Can anyone else get this to work?
I wish i could learn that lesson that way through cross tab instead of pivot tables. It too hard for me to interpret it all. By far this method is better
I am sorry if the formula are too hard. The fact is that Array formulas are hard. But the lucky news is that we can just do it with Pivot Tables AND it is much easier, and then all we have to do is Refresh and everything will update.
BTW Mike, before I forget: of course, you HAVE TO differentiate the format between the regular table and the totals. Come on, we know you can do it, just a little conditional formatting, OK? ;-) Make it look pretty...
Yes, the Conditional Formatting video is coming soon. I have already filmed it, but I am so many topics for this New Calc Engine... P.S. i have not forgot about MSPTDA... In fact I can't wait o get back to that!!!
Mike, and while you’re at it, maybe you can add in a little “Leila Gharani”, that is: differentiate the formatting of the entire table, based on the variable selected (you do that with conditional formatting, too). So, other than sales in your data table, you can add in quantities, too. Then add a third variable to your report (the thing you want to aggregate in your report) and of course conditionally format the entire table accordingly. I mean: come on, if we going to make it dynamic, we might as well make it dynamic all the way! ;-)
That formula may be impressive but it is not fun. It is important that someone should experiment and pioneer new approaches but if you seed the idea that this is what spilled arrays are all about you will repel most users before they start. Even if you look a what is, for most of us, 'current technology' of CSE arrays, most users have never heard of them and, of those that have, they are seen as 'advanced', as 'methods of last resort'. They should be seen as providing simplifying structure, sweeping away the need for thousands of single-cell, mixed references with a single expression (used in the same way as turning mounds of straw into tied bales makes them so much easier to handle). For me, arrays or lists are the first port of call, it is destroying that structure to give a multitude of single cells that is the last resort. In the present context, the core of the process is the 2D spilt array. That is the concept that is there to be exploited. Rather than providing a fixed cross-tab you have demonstrated that it is possible vary both the row and column series. Interesting but, I suspect, not useful in most applications. It is a bit like a book where the reader is allowed to choose which ending they want to read. I sometimes replace pivot tables by formulas simply to restrict the user and ensure they are seeing what I wish them to see. Now we layer on that the ability to create sum rows and columns. Is there an overriding need for such a thing to be done as one array? As an example, 'income per annum' and 'lifetime income' do not naturally form part of the same array though, admittedly, they could be shoe-horned into a partitioned array as has been done here. The simpler approach would be to assign fixed locations above and to the left of the 2D spilt array for the row and column totals. There are even operational advantages of such a layout; one no longer has to scroll over an indeterminate number of rows and columns to find the totals.
Well... there are many different seed ideas at the excelisfun channel over the decade of posting videos. With these new Arrays, it is no different: some of the videos show simple uses, some show advanced uses. Some may want to use this formula, some may not. But as David said, he has already had multiple uses for this type of formula. Not only that, but for those people who will use this type of formula, the formula in this video is significantly less difficult than how this was done in older versions. The formula may not be fun for you, but for others it may be the pinnacle of fun.
@@excelisfun I wouldn't wish my remarks to be taken as over-critical. For me, the pinnacle of success is when something that previously appeared as a mess of overlaid complexity is reduced to the application of a single principle or a short sequence of such. I am not yet in a position to test how named formulas act to build spilt arrays or how the arrays can be used in further formulas but my objective would be to go through a sequence of steps, each of which has a name and generates a meaningful data object that could be displayed. I suspect that the individual steps here could be provide standard, reusable functionality but I find it so difficult to see through the fog of deeply nested formulas. I realise that others do think differently. On Chandoo recently someone recently wrote "could you please not introduce names and express your solution in terms I understand", i.e. encrypted using the A1 notation with a sprinkling of '$' signs. As you suggest there are many horses running over multiple courses.
Everyone I work with thinks of me as an Excel expert. I watch these videos and think of my skillset as novice, ...at best. Love learning new Excel skills, but still have a long way to go. At least I'm able to follow along and just shake my head and say "Wow!"
I am happy to help. And, you are an Excel Expert who is going to keep learning and shining at work : )
Fantastic... Long Live Excel Champ 🙌
Glad you like it, Chidambaram!!! Thanks for your support with your comment, thumbs up and Sub : )
Big efforts to bring that crazy solution to us. Using logic operations?! Oh my God, I really forgot it since I graduated from the Engineering faculty. One of our professors told us once the logic science does exist to make our life easier. Your lesson is a proof of that. Thanks Mike. I really appreciate your time and efforts to make us the best.
Well, Ibrrahim, I am glad that the logic science will be of help here!!! And I think your professor is right: sometimes it does come in handy : ) You are welcome for the efforts!!
I watched the video few times and my head still spinning 😜😜, Amazing work from the master.👍👍
Glad you liked the spinning fun : )
Mike: yay! You did it - with a little help from the team, but still.
And as you so adamantly -and rightfully!- responded: the crazy array formulas are back!... because they never left.
There are a few things we can take home from this:
- you use an ‘outer’ product to create a matrix from two vectors (something we already know for years)
- and more importantly: we now know how to append arrays,
- and we now know how to use ‘masks’ to ‘paint’ specific formulas to specific places in the final array.
In conclusion: the formulas will always (end up) be(ing) crazy, but the stuff we can do with them will be more and more advanced.
The latter is called: “Evolution”. Although right now with all the new stuff it feels more like “Revolution” ;-).
Can’t wait to see you on the next one.
Go Team!!!!
Array Formula will always be with us!!!!
Love that we can do Outer Matrix
We can Append Arrays
LOVE your terms here: "use ‘masks’ to ‘paint’ specific formulas to specific places in the final array"
Evolution and Revolution!!!!
Next videos are videos are 1521, 1523, 1524, 1525, 1526, 1527... I stopped doing them in order, but only because I was so excited about the latest video after planning the earlier videos.
It's not fair!!You guys are having all the fun with the new update...
I am sorry... it is not fair. Microsoft is not fair. I wish I could make it less NOT fair. Hopefully all of us who see he amazing power of these Dynamic Arrays, will convince Microsoft to make changes so we all can have this power. I have already e-mails Joe McDaid about the survey we did and asked if MS can soon let everyone have Dynamic Arrays!
Good work Mike and David....I am hoping the Excel Team will add Totals to their To Do list once they roll out Dynamic Arrays to the masses...
Maybe they will. But it is cool to see a formula construction that allows us to Append : )
It’s simply amazing. I am not sure in how many practice days take me to this level of manipulation.
Hey Mike and David.. totally insane and way cool! Hopefully, Microsoft will make it easier with a TOTALS function or some kind of TOTALS argument built into the base formula generating the spilled range. Until then it is still the Wild West with these unbelievable and intense solutions that you guys visualize and make into reality. Double Thumbs up on this one!!
Yes, the Wild West!!!! Tomorrows EMT 1526 will show an alternative for totals. And then in EMT 1530 we'll do a more simple version of this one where we don't need the word "Total" Thanks for watching, commenting and supporting, Wayne!!!!
Excellent.. looking forward to it!
Hats Off to Mr. Mike and Mr. David :)
Go Team!!!!! Davids formula opens up many amazing possibilities.
Wow, pivot tables with formulas - amazing! I can't even imagine the same trick with traditional formulas... ;)
Nice! That's a much cleaner formula than what I had. You're the master of array formulas!
Yes, but you may be THE inventor of a formula that people will use for decades!!!! Let it be Date - Time Marked here that, so far, for are the inventor : )
Jealous because my company is STILL using Excel 2013, but is looking to upgrade to 365 early next year. Still using the curly brackets and Ctrl+Shift+Enter for now :(
Will be coming back to all these 365 exclusive videos again once that happens :) Thanks for posting all these dynamic array videos, they truly are amazing!
You are welcome, Micah! Thanks for your support on each video : )The playlist for all the videos are here: th-cam.com/play/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx.html
Truly amazing formula!!! Many thanks @David and @Mike :-)))
Yes, it opens a bunch of new possibilities!!!! So exciting!!!!
Another Amazing video ...just mind blowing..thank you so much :)
Thank you so much for posting such a informative videos. Highly appreciate your all efforts. 💐💐💐👍👍👍
Wow, amazing Mike! Great job by David too
Glad you like it, Chris! It is lucky we have such a great Team!!!!
Amazing Mike with another EXCELlent video
Glad you like it, Syed!!!! I am amazed at Davids formula too : )
Great tricks! Thumbs up!
Microsoft: Hey guys you don't need crazy formulas to do arrays any more. :D
Mike G: Hey Microsoft, look at this crazy formula because we don't have totals on arrays. Your move :P
Jamie Rogers LOL!
Well, I think you are right. It's crazy that we can create a cross tabular report with a short formula but have to go one step back and create insane long formulas to get the totals.
Well... As you all know (Geert, XLarium, Jamie), before Dynamic Arrays we had a much harder time accomplishing this, so even though this is complicated, it is great that we can do it now. But, as you suggest, it would be great if it was easier : )
Sir so nice video and useful to us thanks
Glad it is nice and useful, Ravindra!!! Thanks for your support with your comment, thumbs up and of course your Sub : )
This illustrates my two “requests for improvement “ for dynamic arrays. Totals are hard. Formatting results is hard.
Both would be solved if I could use dynamic array references in an Excel Table !!
OMG thank you so much Mr. Mike this is absolutely amazing it's a mind blowing for me really
My mind is blown too. But in a good way! Davids formula is amazing : )
Hello Mike! As always, another great video. With these all new family of array function I have a the impression that the
Ctrl+Shift+Enter keystroke will no longer be necessary. Don't you?
Thanks again! 👍
Yes, it is not needed any more : )
Which version of 365 I should buy?
Whaaaaat ?!!! .... AMAZING ....But .... i guees microsoft has to make a "Total" function attached for dynamic arrays or a set of functions ...just like tables predefines formulas ...right?
Maybe... But, I don't know... The IF to Append seems like a straight forward solution that has been available for a lone time. But, I don't think that we have had much opportunity to use it until the Spilled Arrays : )
Great as always, but it is a very difficult formula. I appreciate, if we will have an easy formula like, sorting is difficult but with SORT formula, it's become easy. But really a great and gigantic effort to get totals (horizontal and vertical). Microsoft should have provided, something like Appending Totals vertically and horizontally.
Yes, I agree that it should be easier. But what David showed us is something really amazing . Thanks for your support, Hassan!!! Plus, the next 5 videos will be more amazing solutions that the simpler Array Functions can provide : )
Is there a version of this formula that can count unique items with criteria ?
Very interesting video but it's rather on the complicated side. I wish Microsoft would simply take Charles Williams' Speed Tools and incorporate his floating totals functions into Excel.
Question: are there any videos on Power Bi for Desktop?
Yes, if you go to the excelisfun channel, just search on any topic. There are at least 10 or so videos. Here is one: th-cam.com/video/PzshpJbc8CE/w-d-xo.html
Here is my serach results: th-cam.com/users/ExcelIsFunsearch?query=power+bi+desktop
There is a general issue which arises from the desire to show row and column totals, though. Traditional development assumes the user is hell-bent on selecting data manually rather than expending intellectual effort to describe their selection. As a result, there is only one function in Excel that will select each row (or column) of a two-dimensional array and aggregate to give a column of results. MMULT and TRANSPOSE will do the job provided you are content to limit the aggregation to a 'weighted sum' of the row but they are not the friendliest functions in town for a non-mathematician. But what if you want to find the smallest value in each row (SMALL); or to ensure that a condition is satisfied in every column (OR or PRODUCT), or count the number of matches (COUNT), or concatenate each string within the row (TEXTJOIN)????
It would be possible (I think) to use SEQUENCE in combination with INDEX to split a 2D array into rows but the resulting column is not naturally an array because of the way in which INDEX treats array-indices. Either AGGREGATE needs a new function numbers for
000+n, aggregate over row and columns to give a single value
100+n, aggregate over rows to give a column array
200+n, aggregate over columns to give a row array
300+n, aggregate cell by cell to combine corresponding terms within multiple, similarly-dimensioned references;
or, alternatively,
one could envisage functions BYROW(), BYCOLUMN() which would cause the 2D array to be treated as a collection of ID arrays up to the point where they are aggregated.
Do you think we should push for something like this on user voice?
BYROW() and BYCOLUMN() would be great to have!!!! It is always worth a try at User Voice, but I suspect that since Dynamic Arrays are so new, it might not get much traction at this point.
Mike. I think excel is not fun this time cause of David and the called array formula.
I'm sure life will be much easier in future versions.
I am not sure what you mean. I think that being able to append an array with an Array Formula is an Epic element on the Array Formula Pantheon. Not only that, but there have been MUCH more insane Array Formula Constructions that have been used to solve common problems in that past, However, I do agree, that if Microsoft could program more of our needs into functions, it would be better. Also, as new Functiosn are created like UNIQUE and SORT, then the imagination is ignited into new directions, and thus we get great solutions like this...
Mike. Just doing a catch up on your fine videos. I wondered, would the following work for a slightly simpler returning of totals:
=SUMIFS(fRevenue[Revenue],fRevenue[Product],IF(MAX(ROW(H10#))=ROW(H10#),"*",H10#),fRevenue[Region],IF(MAX(COLUMN(I9#))=COLUMN(I9#),"*",I9#))
I'm virtually certain it does! However, I could be going crazy.
Edit to add back in the match formulas I removed:
=SUMIFS(fRevenue[Revenue],INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)),IF(MAX(ROW(H10#))=ROW(H10#),"*",H10#),INDEX(fRevenue,,MATCH(I5,fRevenue[#Headers],0)),IF(MAX(COLUMN(I9#))=COLUMN(I9#),"*",I9#))
Very cool formula!!! I could net get it to work... I kept getting an error. This is why MS needs to deploy the new Array Features to everyone, so that you are not denied the opportunity to try this for your self. Can anyone else get this to work?
That really is awesome.
REALLY awesome! Thanks for always watching and supporting, Jonathan : )
OK, who are the idiots who gave this video a thumbs down?!?
Thank you, Geert!!!! I think that they just mixed up their RIGHT to criticize with they OBLIGATION to say thank you.
I have the same question!!!
I have the same question too !!
I tried the sumifs with CHOOCOLS instead of INDEX. That didn't work. Any idea why?
I wish i could learn that lesson that way through cross tab instead of pivot tables. It too hard for me to interpret it all. By far this method is better
I am sorry if the formula are too hard. The fact is that Array formulas are hard. But the lucky news is that we can just do it with Pivot Tables AND it is much easier, and then all we have to do is Refresh and everything will update.
BTW Mike, before I forget: of course, you HAVE TO differentiate the format between the regular table and the totals.
Come on, we know you can do it, just a little conditional formatting, OK? ;-)
Make it look pretty...
Yes, the Conditional Formatting video is coming soon. I have already filmed it, but I am so many topics for this New Calc Engine... P.S. i have not forgot about MSPTDA... In fact I can't wait o get back to that!!!
Mike, and while you’re at it, maybe you can add in a little “Leila Gharani”, that is: differentiate the formatting of the entire table, based on the variable selected (you do that with conditional formatting, too).
So, other than sales in your data table, you can add in quantities, too. Then add a third variable to your report (the thing you want to aggregate in your report) and of course conditionally format the entire table accordingly.
I mean: come on, if we going to make it dynamic, we might as well make it dynamic all the way! ;-)
@@GeertDelmulle Great idea!!!
shees.. thats something... i wonder how long would the formula get without o365 :D
Is there not an easy method for the "Totals"? my mind really spins on to that..... Very complex
Not that i now of.
Thumbs uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuup
That formula may be impressive but it is not fun. It is important that someone should experiment and pioneer new approaches but if you seed the idea that this is what spilled arrays are all about you will repel most users before they start. Even if you look a what is, for most of us, 'current technology' of CSE arrays, most users have never heard of them and, of those that have, they are seen as 'advanced', as 'methods of last resort'. They should be seen as providing simplifying structure, sweeping away the need for thousands of single-cell, mixed references with a single expression (used in the same way as turning mounds of straw into tied bales makes them so much easier to handle). For me, arrays or lists are the first port of call, it is destroying that structure to give a multitude of single cells that is the last resort.
In the present context, the core of the process is the 2D spilt array. That is the concept that is there to be exploited. Rather than providing a fixed cross-tab you have demonstrated that it is possible vary both the row and column series. Interesting but, I suspect, not useful in most applications. It is a bit like a book where the reader is allowed to choose which ending they want to read. I sometimes replace pivot tables by formulas simply to restrict the user and ensure they are seeing what I wish them to see.
Now we layer on that the ability to create sum rows and columns. Is there an overriding need for such a thing to be done as one array? As an example, 'income per annum' and 'lifetime income' do not naturally form part of the same array though, admittedly, they could be shoe-horned into a partitioned array as has been done here. The simpler approach would be to assign fixed locations above and to the left of the 2D spilt array for the row and column totals. There are even operational advantages of such a layout; one no longer has to scroll over an indeterminate number of rows and columns to find the totals.
Well... there are many different seed ideas at the excelisfun channel over the decade of posting videos. With these new Arrays, it is no different: some of the videos show simple uses, some show advanced uses. Some may want to use this formula, some may not. But as David said, he has already had multiple uses for this type of formula. Not only that, but for those people who will use this type of formula, the formula in this video is significantly less difficult than how this was done in older versions. The formula may not be fun for you, but for others it may be the pinnacle of fun.
@@excelisfun I wouldn't wish my remarks to be taken as over-critical. For me, the pinnacle of success is when something that previously appeared as a mess of overlaid complexity is reduced to the application of a single principle or a short sequence of such. I am not yet in a position to test how named formulas act to build spilt arrays or how the arrays can be used in further formulas but my objective would be to go through a sequence of steps, each of which has a name and generates a meaningful data object that could be displayed.
I suspect that the individual steps here could be provide standard, reusable functionality but I find it so difficult to see through the fog of deeply nested formulas. I realise that others do think differently. On Chandoo recently someone recently wrote "could you please not introduce names and express your solution in terms I understand", i.e. encrypted using the A1 notation with a sprinkling of '$' signs. As you suggest there are many horses running over multiple courses.
@@peterbartholomew7409 Well said with this metaphor: "there are many horses running over multiple courses"!!!!