Groupings, Shopping Lists, Vectors: part 16

This has become a long series, and we’re not done yet.  Here is where we started, looking for natural models that give power and flavor to the mathematical idea of a vector.  Since then, we’ve looked at vector inner products, matrices, matrix multiplication, and saw how they arise from relatively straightforward notions of grouping and keeping track of wholes with many parts.  In the last post, I showed in an Excel spreadsheet how these ideas show up there.  My starting point for this post will be pricing out an order, from the last post, but without showing extended prices: The items are listed in the B column, the unit prices are listed in the C column, and Joe’s  order is in the D column.  From the amounts ordered, and the unit prices, the total for the order (ignoring taxes) is shown in the highlighted cell D12.  It shows \$15.10.  Yet behind this number \$15.10 is the formula that generated the number.  This formula is shown on top, just above the highlighted “D”.   The formula used is a variation of =SUMPRODUCT(C4:C11, D4:D11).  This is Excel’s version of what we’ve seen as an inner product calculation.  If you look closely at the formula, you’ll see that it really says =SUMPRODUCT(\$C4:\$C11,D4:D11).  This dollar symbol has a special meaning to Excel, and it has nothing to do with the fact that the result of 15.10 is a dollar amount.  Rather, Excel interprets the dollar sign as protecting or freezing the column number that follows.  This is important, not for calculating the contents of the cell C12, but when we copy this formula into another cell.

In this spreadsheet, I have copied cell D12 into cell E12.  (This can be done by right-click on D12, selecting Copy, then right-click on E12, selecting Paste.  Alternatively, I can hit Control-C in D12, and Control-V in E12.  Yet another way is to drag the bottom right hand corner of the box around D12 and extend it into E12.)  Excel’s way of copying formulas is clever, in that it assumes you don’t want another identical calculation resulting in \$15.10, but that you want the calculation applied to a different set of numbers.  If I had typed in =SUMPRODUCT(C4:C11,D4:D11) in cell D12 and then copied it into cell E12, it would have landed there as =SUMPRODUCT(D4:D11,E4:E11).  This is not quite what I want.  What I want is for cell E12 to be ready to give the total price for Jane’s order.  Instead, =SUMPRODUCT(D4:D11,E4:E11) would calculate the inner product of Joe’s order, and Jane’s order, a calculation for which I have no use.  What I want is for Excel to automatically modify the D4:D11 in =SUMPRODUCT(C4:C11,D4:D11) to E4:E11, but to leave the C4:C11 alone.  That is what the dollar symbol lets me express: =SUMPRODUCT(\$C4:\$C11,D4:D11) tells Excel to leave the C’s alone, but the D’s should be modified based on where the formula is copied into.

In the same way you protect/freeze columns in formulas, you can also protect rows.  Here is a simple example, showing currency conversion. The cell B18 contains the all-important currency conversion rate, here 1.05812 Canadian dollars for 1 US dollar.  The total amounts for Joe and Jane’s orders are shown in the C column, and the D column calculates the corresponding Canadian dollar amount.  The formula in cell D19 is one I typed in; the formula in cell D20 is copied directly from cell D19.  As you can see in the box above the highlighted D, the formula I typed in is =B\$18*C19.  This formula tells two things: one, the number to show in cell D19 is what you get from multiplying the numbers in B18 and C19; two, when the formula is copied into a cell below, change the C19 accordingly, but don’t change the 18 in B18.  Instead of entering =B\$18*C19, I might have entered =\$B\$18*C19, and thus protected it from being copied into a cell anywhere, not just below.  The main point here is that Excel doesn’t know or care, and will blithely calculate what you tell it to, whether doing so makes sense or not.

The way the Canadian dollar vector (D19:D20) depends on the US dollar vector (C19:C20) is a very common and important pattern, it is a vector operation called “multiplying by a scalar”.  In this name, the word “scalar” refers to the single outside number 1.05812, the currency conversion rate.

In the next post in this series I will show an example of matrix multiplication done in Excel.