HomeОбразованиеRelated VideosMore From: ExcelIsFun

Excel Dynamic Arrays: Cross Tabulated Report, Totals Top & Left MMULT Array Function (EMT 1526)

143 ratings | 3038 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1526-1527.xlsx This video is a follow up to EMT 1520. In this video we see how to create fully Dynamic Cross Tabulated Report driven by formula inputs, and have totals at the Left and Above using the MMULT Array Function. Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) Related Videos: Original video about Cross Tab Report, EMT 1520: https://www.youtube.com/watch?v=ZXn0D4Ch7zg Cross Tab Report with totals at bottom and Right, EMT 1528: https://www.youtube.com/watch?v=wHeXjYQGvX8 MMULT Array Function Video: https://www.youtube.com/watch?v=RyorWHIKPC0
Html code for embedding videos on your blog
Text Comments (40)
Ismail Ismaili (5 months ago)
Astonishing amazing brilliant than you so so much Mr. Mike you are always the best.
ExcelIsFun (5 months ago)
You are welcome, Ismail!
Peter Bartholomew (5 months ago)
Another interesting video Mike. I had rather assumed that MMULT was more of a legacy array technique than part of the brave new world! However your video shows that the function plays remarkably well in the context of dynamic arrays. I am still wary of using matrix multiplication on the grounds that most users will not be able to understand it (I am just being patronising?) but it is robust and has the advantage of just processing the values necessary to the calculation rather than scanning every value in the array for possible inclusion in the current calculation. It is, however, a perfect example of a workaround being used to fill the gap whereby 'aggregate by rows' and 'arrogate by columns' represent missing functionality. I think this will become a significant issue if we convert the world into thinking in terms of arrays as objects in their own right, rather than focussing upon single cells which merely hold component values of an array. Something that MMULT can do as an extra party-piece is to introduce filters, weighting or even multiple versions of either into the 'unit' vector to give a set of filtered or weighted sums. Asides: somewhere, in this video or one of its partners, you mention the legacy = ROW(range)^0 as a method for creating the 'unit' vector when SEQUENCE is unavailable (as it will be for some for many years). I would suggest that raising a value to the zero-th power is a bit technical when functions such as = SIGN( ROW(range) ) or = N( COLUMN(range) = 5 ) can be used (the latter is different in its intent). Finally: Is there an approved pronunciation of MMULT? I have always said it as Em-Mult (and Em-Inverse) but with no real knowledge of what is the 'approved' version.
ExcelIsFun (5 months ago)
No, all the old legacy Array Functions ARE part of the brave new world, because they spill and we don't have to be bothered with counting how many cells we need to highlight up front!! You are 100% correct: Most Excel users do not understand Matrix Algebra. I like the SIGN or N, but have always used ^0 because it was so fast to type... No approved pronunciations - and in fact I have been given a lot of grief over the decades for how badly I pronounce certain functions liker MMULT, LINEST and DATEDIF - But I am just a guy trying to have fun with Excel : )
Vida (5 months ago)
Great video. I always do totals on top.
Hussein Korish (5 months ago)
Well... Mike ...Only The way you explain it that makes it easy.
John Borg (5 months ago)
Thanks Mike :)
Bill Szysz (5 months ago)
Thanks Mike :-)))) I made this formula (MMULT, SEQUENCE) just for this to show that we are able to refer to spilled arrays only (not to the source table) I think, it should be useful sometimes ;-))
ExcelIsFun (5 months ago)
Yes, it is VERY useful to be hanging out on such an awesome Online Excel Team with you : )
Chris Mancinelli (5 months ago)
Mike, you continue to amaze. Thanks for all these awesome videos!
ExcelIsFun (5 months ago)
You are welcome for all the videos - many more to come - at least 4 more Dynamic Array and then back to MSPTDA : )
Narendra rathore (5 months ago)
Thank you very much Mike. You are amazing.
ExcelIsFun (5 months ago)
You are very welcome, Syed! More Amazing videos to come!
Gentle Raj (5 months ago)
ExcelIsFun (5 months ago)
Glad it it great for you, Gentle Raj!!!!
Wayne Edmondson (5 months ago)
Hi Mike.. excellent! Have not played with MMULT yet.. will do now. So many clever ways to display results. Thanks for all the insights and tips. Thumbs up!
ExcelIsFun (5 months ago)
You are welcome! MMULT is great, but mostly good for Linear Algebra and Simultaneous Equations! Thanks for your support, Wayne : )
Richard Hay (5 months ago)
Great video--not only for the formulas but also for the competition between the two approaches. SpreadSheet Road (originating in the late 1960's with LANPAR and torturously winding through Visi-Calc, Lotus123 and ending with Excel) is paved with Linear Algebra. Not only is Linear/matrix/array algebra the underpinning of all electronic spreadsheets, but as Excel has become more powerful, efficient and complex, LA has taken an increasingly visible role on the surface of spreadsheets (as evidenced by this video). Regardless how it is learned (classroom or self-taught) Linear Algebra is an essential pre-requisite/co-requisite for advanced spreadsheet construction. You obviously have a solid grasp of LA. I'm certain that the reason you believe Excel is Fun is that you understand Linear Algebra--and you could not have written CSE without that background. For the formula competition in this video, the two formulas do the same calculations but with a somewhat different organization. I think MMULT is easier to construct and is a more direct approach with MMULT during the heavy lifting and with rows, columns and the great new SEQUENCE working together to right-size the resultant array. But I still contend that MS needs to fix this total problem. I could build the interior of this table as fast or faster than dragging fields to form a Pivot Table. But totals are PT automatic. Yes, it needs to be refreshed, but I am really fast with the refresh keyboard. But at least the Total Problem has good solution with MMULT--and I am fine with totals at the top. This table is small so top or bottom, left or right totals are equally fine, but in very large tables the totals at the top actually can help the busy reader quickly find and absorb summary information. Maybe we should have the top-or-bottom option in Pivot Tables!!
ExcelIsFun (5 months ago)
+Richard Hay Yes, I guess you are right, Matrix Algebra is everywhere!!! : ) Thank you very much for your kind words about my videos! i will keep them coming, but just because it is so much fun making them and also a lot of fun trying to keep up the learning of all the new MS tools - because as you say: they do have big plans and they have so many amazing new features : ) : )
Richard Hay (5 months ago)
Totally correct. Many disciplines actively and productively use Excel. Spreadsheet data come form Wall Street, Main Street, Clinical Trials, Psychological Testing, Consumer Surveying, political polling--and even from libraries chasing missing books! You have offered examples of all of these in various videos. However, all these data land in Excel spreadsheets as Matrices. If two or more adjacent cells contain data in one or many rows, or one or many columns a matrix exists. Proper Data Sets are Matrices. Always. The computations and manipulations of these data must follow the rules and techniques of Matrix Algebra. Always. MS is moving surprisingly fast with the increased power and complexity of Excel. The pace has increased over the past five or so years from a crawl to a run. I believe there is a lot more to come--because MS has made a bet big on big data. Your outstanding videos illustrate how these changes have moved Matrix Algebra from largely behind the scenes to an ever more prominent role center stage. I commented thusly to this video because it demonstrates this effectively. MMULT is a blatant Matrix function and SumIffs is more subtle--but both are firmly grounded in the rules of Matrix Algebra and they do the same calculations and yield the same outcome--but through slightly different paths. Your MSPTDA series showcases Matrix Algebra through M-Code and Dax. And of course the columnar data base is a collection of Matrices. My belief is that users generally should learn at least the rudiments Matrix operations to keep up--Easier to keep up than to catch up. You work very hard to make these world class videos. Excel users need to maintain the skill level necessary to obtain their maximum benefit. Learning Matrix algebra is easy and fun--just needs to be done.
ExcelIsFun (5 months ago)
Hopefully MS will fix the Total Thing. Remember, though: history of why spreadsheet was created was partially Linear Algebra, part Finance, Part Accounting, Part Science, a bunch of other parts too : )
Eric Giroux (5 months ago)
Hi Mike, Wow, you're so gifted when it's time to make something complex accessible to everyone. As usual, thanks to you and of course Mr Bill Szysz. :-)
ExcelIsFun (5 months ago)
You are welcome, Eric!!!! Thanks for your support : )
Awesh Bhornya (5 months ago)
Looks fun to learn but still need more practice with these functions.
ExcelIsFun (5 months ago)
Yes, practice makes perfect : )
Leila Gharani (5 months ago)
Love the MMULT combination with Sequence. Mike - I'm behind about a week on your videos! Will be catching up soon :) Love your dynamic array series.
Leila Gharani (5 months ago)
Yes - it was fun :) We missed you!
ExcelIsFun (5 months ago)
Yes, but you got to have MUCH fun that I have been having in Bulgaria with the awesome Excel Crew : )
AeroClub Dehradun (5 months ago)
Hey...hw r u?? I need some help. I got a one master file which have some name and one file which have the same name but with wrong spell..is there any way to correct them ????
ExcelIsFun (5 months ago)
No time right now with my terrible work schedule. But no worries, try this amazing Excel Question site: mrexcel.com/forum
Anil Dutt Bhargava (5 months ago)
Amazing office 365 insider
ExcelIsFun (5 months ago)
Yes, indeed : ) Thanks, Anil!!!
Kevin Lehrbass (5 months ago)
Mike, do you ever sleep? Thanks for the video!!! MMULT is challenging but you explain it so well ! and Bill Szysz is also an Excel guru!!
Pavan Lalwani (5 months ago)
Yes sure Mike. I do that always. I spread the name wherever possible
ExcelIsFun (5 months ago)
+Pavan Lalwani , I am glad that the things I post help you as a trainer! You are welcome to use the materials that I post for non-profit endeavors, but be sure to give credit : )
Pavan Lalwani (5 months ago)
You are simply amazing. I as an Excel trainer copy your notes, words and style. You enrich my knowledge to another level. Thanks a lot Mike
ExcelIsFun (5 months ago)
I 1000% agree with what you said: Bill Szysz is an Excel guru : ) : )
ExcelIsFun (5 months ago)
BTW, I do have a great video that explains MMULT and Matrix Algebra here (better than the explanation in this video): https://www.youtube.com/watch?v=RyorWHIKPC0

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.