## Groupings, Shopping Lists, Vectors: part 17

In this post I will show a full-fledged matrix multiplication done in a spreadsheet, Excel.  (This series starts here; two recent posts part 15 and part 16 introduce vector inner products done in Excel.)

Above you see one approach, though not one I recommend.  First, let’s see what we’ve got here.  The blue section represents the menu, containing price and nutritional information for a series of items.  The yellow section represents orders, the green section represents totals.  As before, I ignore taxes in the total price.  (Or if you prefer, the price shown is the price before sales tax).  Each section is what is called a matrix: it has rows and columns, and the meaning of each row as well as the meaning of each column is clear.

The blue matrix has 8 rows of numbers, and three columns of numbers; the yellow matrix has 8 rows of numbers and 2 columns of numbers; and the green matrix has three rows of numbers, and two columns of numbers.

What works about the spreadsheet above is that columns G and H work exactly the same way.  In fact, I can copy the whole column H and then right-click on column I and select “Insert Copied Cells” and I will get another column, with order amounts in yellow, and totals in green, and if I change the label from “Jerry’s order” to “Jane’s order”, and change the amounts from Jerry’s amounts to Jane’s amounts, then the totals for Jane will adjust themselves accordingly.  You can see how this works by looking at the formula in G13.  This formula is shown in the box right above the orange-highlighted G: it shows =SUMPRODUCT($C$4:$C$11, G$4:G$11).   It calculates the inner product of the  price column with the order amount column, giving the total price.  This formula, when copied and pasted into H13, will land there as =SUMPRODUCT($C$4:$C$11, H$4:H$11), which means it will still reference price information, but using Jerry’s order amounts.  So this is what works about the approach shown.

What doesn’t work very well about the approach shown above is that the formulas for G14 and G15 cannot be derived from the one in G13 by copy and paste.  Even though we tried very hard to protect the row numbers by typing $C$4 so that when we copy the formula downward Excel won’t mess up and turn it into C5:C12, we can’t get the column designation right.  Neither $C4 nor$C$4 works to get it to paste as D4. This is not surprising. Excel adjusts formulas by noticing how far over and down the target cell is from the source cell. It has no way of guessing that you wanted the columns to advance as you move the cells down. So in the spreadsheet shown above, what is typed into G14 is =SUMPRODUCT($D$4:$D$11,G$4:G$11) and in G15 we have =SUMPRODUCT($E$4:$E$11, G$4:G$11). Below, I show a set up that fits Excel’s way of doing things better, and just maybe this is easier for human beings as well. If you focus on the totals matrix, in green, you see that it gets its columns from the blue matrix, and its rows from the yellow matrix. The order matrix (yellow) is now 3 rows by 8 columns of numbers, the blue matrix is 8 rows by 3 columns of numbers, and the totals matrix is 3 rows by 3 columns of numbers. Yet you can see clearly that the fact that the totals matrix has the same number of rows as columns is mostly coincidence: all it takes is adding or removing a single order, and the totals matrix changes its number of rows accordingly. Conversely, if we were to add another column of, say, cholesterol data to the blue matrix, it would change the shape of the green matrix accordingly. The green matrix has the same columns (not just the number of columns) as the blue matrix, and the same rows (not just the number of rows) of the yellow matrix. I wish I could tell you that the content to be typed into Joe’s total price cell would be =SUMPRODUCT(B13:I13,K4:K11) – but Excel lets us down here. Though Excel documentation (Office Excel 2007) suggests that SUMPRODUCT works on any two ranges of numbers as long as they are equally long, and though we’ve seen before that Excel will blithely calculate numbers without regard for whether they make any sense or not (we saw in the previous posts that Excel will gladly calculate the inner product of two orders), Excel nevertheless refuses to calculate the inner product of a row with a column using SUMPRODUCT. That is too bad, and it is a restriction that I would consider to be a bug in the program. Fortunately, you can see in the figure above that =MMULT(B13:I13,K4:K11) does the trick. MMULT, which appears to stand for matrix multiplication, not only accepts that one range is a row and the other one is a column, it seems to insist on it. We’re over the hump, though, and the road is downhill from here. In thinking through the details of =MMULT(B13:I13,K4:K11) we can see that we want to protect the columns B through I, as they indicate the range of items on the menu: they should stay the same whether we are looking at Jane’s order or Jerry’s order, or whether we are looking at totals for the price or for the calories. Similarly, we want to protect the rows 4 through 11, as they also indicate the range of items on the menu. Conversely, we don’t want to protect row 13 or column K, as these are precisely the ones that should range freely from totals cell to totals cell. This way, we end up with =MMULT($B13:$I13,K$4:K$11) as our formula for cell K13, and this formula can now indeed be copied and pasted into all the other cells of the totals matrix (green). Perhaps a bit surprising, the traditional treatment of matrix multiplication in textbooks matches this latter arrangement. When you multiply a matrix A times a matrix B, they would say, you get a matrix C with the same number of rows as matrix A and the same number of columns as matrix B. They would say that to be able to multiply matrix A with matrix B at all, they must be conforming, which is a fancy way of saying that the number of columns of A must match the number of rows of B. Some people like the visual image of dropping down the left matrix below the right matrix (but keeping it on the left) so that the shape of the result matrix (and the inner products that determine the value in each of its cells) can easily be seen. Some people have never even seen any kind of visual image, and are stuck with remembering some formula they learned in college, like $c_{ij} = \sum_k a_{ik} \times b_{kj}$ How tough it must be to have any kind of real understanding of what you are doing and why, if all you learned was how to manipulate formulas like that. Advertisements This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink. ### 5 Responses to Groupings, Shopping Lists, Vectors: part 17 1. Bob Phillips says: In the first layout, you could just use =SUMPRODUCT(INDEX($C$4:$E$11,,ROW(A1)),$G$4:$G$11) and with the second form, you can use this array formula =SUM(TRANSPOSE(B13:I13)*K$4:K\$11)

2. Robert says:

“Perhaps a bit surprising, the traditional treatment of matrix multiplication in textbooks matches this latter arrangement.”
Why surprising? Because the explanation makes “too much sense” to be used in a textbook?