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.

Pingback: Groupings, Shopping Lists, Vectors: part 17 « Learning and Unlearning Math

Pingback: Groupings, Shopping Lists, and Vectors: The Series « Learning and Unlearning Math