This post is part of a series, starting here, about models and ideas that underlie the rather abstract stuff called vectors and matrices in math textbooks. In this series, we’ve looked at shopping lists, pricing out orders, weighted averages, perspective drawings, and other stuff, to introduce – and show the relevance of – the idea of vectors, matrices, vector addition, inner products, matrix multiplication. We played with different representations.
One representation I want to play with in this post is that of a spreadsheet. My examples will be restricted to Excel spreadsheets (on a PC), but they should generalize to other spreadsheet programs. Spreadsheets also play off the usefulness and ubiquity of rectangular arrangements of data, and they allow us to play with lots of numbers and yet not be caught up by having to do lots of arithmetic – the spreadsheet is good at taking that work away from us. In setting up a spreadsheet, we have to show it what computation we want performed, but then the spreadsheet program will do that computation for us, and will redo it as many times as we want, and in particular, it will redo all the computations as needed whenever we change a number.
I’m showing an order form, and the data in the order form is consistent with the example used in prior posts, of a particular fast food place with a particular menu. The part about items and price is intended to be static, the amounts ordered would change from order to order. The total (ignoring taxes) is calculated, and as soon as you change any entry in the Amount ordered column, the total will change accordingly. In this spreadsheet, this is done by entering formulas for the column called Extended Price, and another formula for the cell that contains the total. “Cell” is the name for each rectangle in the grid, and each cell has a name based on which column it is in and which row it is in. The highlighted cell has the name E4, and the content typed into this cell is not 1.50 but the formula shown right above the highlighted E column: =C4*D4.
The formula =C4*D4 means that the number that should be shown in the E4 cell is obtained from multiplying the number in the C4 cell (here 1.50) by the number in the D4 cell (here 1). The nice thing about this formula is that it works (meaning: it displays the right number for us to see) regardless of what the price in cell C4 is and regardless of the quantity ordered. Change the price in C4, and the number shown in E4 changes accordingly, automatically, without you having to do anything. Leave the price in C4 alone but change the quantity ordered in D4, and the number shown in E4 also updates automatically.
The total amount, here$15.10, in cell E12, is obtained from adding up all the extended prices above. What I typed into the cell is =SUM(E4:E11). This is convenient shorthand for =E4+E5+E6+E7+E8+E9+E10+E11, which would have worked just as well.
What I left out of this account is what goes into the cells E5, E6, …, E11. In cell E5 goes =C5*D5, etc, but typing all of these in would be a hassle, and Excel doesn’t make you do that. I can copy and paste the content of cell E4 into cell E5! When pasting “=C4*D4” into cell E5, Excel will automatically adjust the formula so that it becomes =C5*D5. In fact, I can fill all of the cells E5 to E11 in one action by copying cell E4 (control C) and then selecting (left click and hold) the entire range E5:E11 and then paste (control V).
There is one last shortcut I will introduce in this post, and that is the SUMPRODUCT construct. In the above version of the order form, extended prices are shown, and those extended prices may be useful. Yet it is quite possible to get the total price without listing the extended prices. In Excel, you can get the total directly from the price column and the amount ordered column, as follows. In the cell E12, you can type in =SUMPRODUCT(C4:C11,D4:D11), and it will display the correct total amount $15.10 even if you had put nothing at all in E4, E5, E6..E11. The SUMPRODUCT will take two ranges and compute pair-wise products and add them all up. This matches our notion of inner product, assuming all the numbers are lined up appropriately. Excel doesn’t know or care that it is multiplying hamburger prices with hamburger amounts – it assumes you know what you are doing, and would blithely multiply hamburger prices with fries amounts if you specified the ranges wrong.
In a subsequent post, I’ll show matrix multiplication based on the SUMPRODUCT features in Excel.