John Turner, 8/24/1995
Go to SM230 home page.
Go to SM230 syllabus.
It is presumed that you remember from Chemistry the basics of Quattro Pro for Windows. You should be able to start Quattro, load a spreadsheet, save a spreadsheet, access the menus and use the mouse or cursor to move around the spreadsheet. In SM230, we (you) will make extensive use of Quattro for calculation purposes. To guide you through this process, we (you) will construct a spreadsheet to calculate binomial coefficients.
Binomial coefficients are the coefficients in (a+b)^n. The coefficient of a^k is denoted C(n,k). These can be calculated from Pascal's triangle, or they can be calculated using factorials. In the spreadsheet, we will use neither. Instead, we will make use of a recursive relationship. This relationship is
C(n,k)=C(n,k-1)(n+1-k)/k
This relation combined with the fact that C(n,0) is 1 allows us to calculate C(n,1) from C(n,0). We then use C(n,1) to calculate C(n,2), etc. Also, note that the above formula produces zero for C(n,n+1), as it should.
Both the good news and the bad news about spreadsheets is that there are many different ways of accomplishing the same thing. This means that a spreadsheet can only be judged by whether it gives the correct results. Different people will use different cells for some quantities. Some will use rows where others will use columns. Different people use different typing techniques. What will be presented here is one approach to spreadsheets. It is based on considerable experience, particularly with those "hard to find" errors.
Two of the most important ideas of spreadsheets are "pointing" and "copying". For instance, we will need a column in our spreadsheet that contains the integers from 0 to, say, 100. These will be the values for k. Pick some convenient cell, say A8, and enter 0. Move down one cell and type 1+ but do not press Enter. Instead "point" at the previous cell (A8 in the example). You can point by either using the arrow keys or the mouse cursor. The coordinates of the cell at which you are pointing should appear in the formula you were typing. At this point, you merely continue typing to finish the formula. In our example, all you need to type is the Enter key.
Pointing is the only way to ensure that your formulas refer to the cells you mean them to. Reading off the cell coordinates and typing them into the formula is both slow and error-prone (and hard on the eyes). Pointing keeps your attention where it should be, namely on the cells of your spreadsheet.
Having entered one formula (correctly, we hope), our job is nearly over. This is because we will copy this formula into all the other cells in the column. This can be done in several ways in Quattro Pro for Windows. One way that is very useful is to copy to and paste from the clipboard. On the left of the "speedbar" are 3 buttons. One has scissors, one has two stacks of cells and one has a little clipboard and an arrow. The scissors "cut" something out of your spreadsheet and put it into the clipboard. The "stacks of cells" button makes a copy into the clipboard (leaving the original in your spreadsheet). The clipboard and arrow "pastes" the clipboard into the spreadsheet. (Note that since the clipboard is accessible from other applications, this allows you to copy information from a spreadsheet into a text document or vice versa.)
To specify what is to be copied or pasted, you must select cells. If no cells are selected, then the operations apply to the current cell. You can select cells by dragging the mouse. You can also use the arrow keys while holding down the Shift key. This second method can be handy for selecting quite a few cells. PgDn moves the cursor down 20 rows at a time. So Shift + PgDn selects 20 rows. Later, we will see another application of Shift + that is useful for selecting cells next to an existing column.
Applying this to the current example, put the cursor on the cell with the 1+ formula. Copy it to the Clipboard. Then select more cells immediately underneath it and Paste the formula into these cells. This should produce the first so many integers.
0 1+uparrow 1+uparrow
The notion of copying is extremely important. It means that you only have to get the formula right once. It may take some time to get it right the first time, but be patient. Once it's right, you are almost to the end.
Now move to the cell to the right of the zero we entered earlier, i.e., B8. We will start entering the binomial coefficients. The first coefficient is simply 1. Move down to the next cell (B9). In this cell, we want to multiply the previous cell by our recursion formula. Begin with +. (This is one of the keys that signals the start of a formula.) Next, we want to refer to the previous cell. Press the up-arrow to fill in the address. We then continue typing with * for multiplication. After (, we need to refer to the cell containing N. Suppose we use A6 to hold this value. Point to the cell you choose. Before proceeding, press F4. Note that this inserts $ and some other stuff into the cell address. This will be explained later. Continue typing the formula. Don't forget to point when you need to refer to k. When done, copy this formula alongside your column for k.
[N] 0 1 1+uparrow +uparrow*(N+1-leftarrow)/(leftarrow) " "
Copying a formula alongside an existing column is a common problem in spreadsheets and has a "neat" solution. In the above example, with the cursor on the first "real" formula (B9), copy the formula to the Clipboard. Then use the keyboard to select the destination. Starting at the same cell that you copied, hold down the Shift key and press the left arrow. Then continue holding down the Shift key while you press and release the End key. Next press the down arrow. This should zip you to the bottom of the existing column, highlighting as it and the original goes. Last, press the right arrow and finally release the Shift key. This should leave only the "new" column highlighted. Then click on Paste to fill the formula.
Now for F4. When formulas are normally copied, the addresses in them get changed. When we set up the column for k, we meant "add 1 to the cell above me". So as this formula is copied down, each copy of the formula gets a different address for "cell above me". We don't want this to happen for references to N. All references to N must have the same address in them. The $'s mean not to change the part of the address that follows when this formula is copied. If the $ appears before the letter only, then the column reference will not change, but row references will change, if appropriate. Similarly, if the $ appears before the row number, then this will not change, but columns will change. Putting $ before both row and column fixes this address in both dimensions.
This notion of relative and absolute addressing is subtle, yet powerful. For instance, suppose we have a column of x's and want to set up several columns for different functions of x. The functions will be similar, but take different parameters and we will store the parameters at the top of the column containing the function values. (For instance, these could be sin(kx), where k is the frequency.) When we type the first function, we use $ on the column when we refer to x and $ on the row when we refer to the parameter. Then copying this formula across columns and down rows will produce the correct references.
Returning to the task at hand, let us change the value of N. The binomial coefficients should change. They should be zero beyond N. They should be symmetric, starting and ending with 1. These are all examples of checking to see that our spreadsheet gives the correct results, or at least results with the right characteristics.
One more characteristic to check is that the sum of the coefficients should be 2^N. (Consider the expansion of (1+1)^N which consists of binomial coefficients times powers of 1.) Quattro has a built-in function that finds the sum of a block (row or column). All built-in functions begin with @. The summation function is @SUM(). The addresses to sum go inside the parentheses. Of course, one would point to indicate the region to be summed. For the binomial coefficients, pick a convenient cell to contain the sum and type @SUM( and do not press Enter. Then point at the first coefficient. Then hold down Shift. Press and release End. Then press the down arrow and release the Shift key. This will fill in the addresses for summation. Finish the formula by typing the closing parenthesis, ) and Enter. The same rules for F4 apply to addresses in functions. Other useful functions include @AVG(), @MIN(), and @MAX().
Quattro Pro for Windows has a speed button for summation. To sum a column, put the cursor on the top cell. Hold down Shift and press and release End. Then press the down arrow. This highlights the column you wish to sum. While still holding down Shift, press the down arrow one more time. This highlights one more blank cell. Then click on the "summation symbol" button on the speed bar. This will put the @SUM() function in the blank cell. Incidentally, if the extra cell in on the right of a row, then "summation symbol" will calculate the sum of the row. However, the extra cells can only be at the bottom or at the right (or both). This won't put the sum at the top of a column or in some arbitrary cell. To move a cell or block of cells: highlight the cells, press and hold the right mouse button and drag the cells to where you want them.
As we set it up, we can show a table of binomial coefficients. But to find a particular one, we have to read down the column. It would be nice to have a formula that would extract a specified entry. The @INDEX() function takes three arguments. The first is a block of addresses in which to find the entry. The next argument tells how many columns to skip in the block. The last argument tells how many rows to skip. If we specify only the single column of coefficients, then we do not want to skip any columns so we specify 0 for the second argument. For the third argument, we put a cell that tells what value of k we want to use. The value of the @INDEX() function is the desired binomial coefficient.
To be more specific, suppose B4..B99 contains the coefficients and A2 contains the value of k. Then @INDEX(B4..B99,0,A2) will return the desired coefficient. To get a different coefficient (for the same N), change the value in A2. If you are going to copy the @INDEX() formula around, you should use appropriate $'s.
Goto beginning of this document.