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)
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
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
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) )
= 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.
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 : )
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!!
+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 : ) : )
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.
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 : )
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.