Download Introduction to Spreadsheets and more Study notes Algebra in PDF only on Docsity!
Math 132-015 & 022 Fall 2002
Introduction to Spreadsheets
Go through this handout if you are not familiar with spreadsheets and relative addressing. The details below refer to Excel 2000, but any version of Excel will be sufficient for the homework. The command-line features work the same on all versions (and on most other spreadsheet programs), but some of the menus and graphical shortcut features may be different. Click Help for information on the version you are using.
Warning: Excel, like all programs, can crash unpredictably, losing all your unsaved work. Save frequently onto your own floppy disk.
1 Cells
A spreadsheet is simply a rectangular array on the screen, consisting of boxes called cells. A cell is labelled by its column (lettered A, B, C,... ) and its row (numbered 1, 2, 3,... ). Thus, cell C19 is in the third (C) column and the 19th row. You may type small letters in referring to columns: the program will know that e9 means the cell E9. A cell can contain three types of data: a string of text; a number; or a formula. In spreadsheets, as in algebra, formulas compute a number by performing operations (like addition) on other numbers (the contents of other cells). The cell then displays the resulting number, rather than the formula itself.
2 Entering Information
To put information in a cell, first select that cell. That is, click on it with the mouse, or use the arrow keys to move the selected cell to the one you want. The currently selected cell will have a dark frame around it. To enter text or numbers, simply type what you want, and press Enter (line-return). What you type is displayed in the cell and in the formula bar at the top of the screen. You can also type into the formula bar by clicking on it, instead of into the cell itself. To enter a formula, you first type an equals sign =, followed by an algebraic expression referring to other cells. For example, if you want cell B3 to display the sum of the numbers in cells B2 and C3, select B3 and enter =B2+C3. (If you have not yet entered any numbers into B2 and C3, they count as zero.) A formula will react to any changes you make in the other cells. Thus, if you change the numbers in B2 and C3, the cell B3 will change to display the sum of the new values. This makes it easy to do “what if” simulations of different
situations.
Examples
multiply B6 and C7 =B6C you must use an asterisk * to multiply wrong: =B6C7 or =B6 C add all the cells between A1 and A15 =sum(A1:A15) cosine of D2 =cos(D2) parentheses are essential wrong: =cos D one half times the square of A3 =1/2A3^2 or =.5A3^ square of half of A3 =(1/2A3)^
3 Relative and Absolute Addressing
Copying is done as in other windows programs: click on the cell you want to copy from, click on the Edit menu at the top of the screen, move down to Copy, and click. Next click on the cell you want to copy to, click on the Edit menu, move down to Paste, and click. (Shortcut: instead of the menus, click +C to copy, +V to paste.) This will copy text and numbers literally, but formulas in a much more subtle and useful way which is the real magic of spreadsheets. A formula can be copied quickly into large regions of cells, and the cell addresses in the formula change depending on which cell it has been copied to. This is called relative addressing. To keep a formula from changing when copied, use absolute addressing.
Example. Suppose in cell B3 you put the formula =B2+A1. In performing the addition, the program thinks of the other cells relative to the current cell: that is, B2 is ‘the cell one row up from the current cell (B3)’, and A1 is ‘the cell two rows up and one column left from the current cell (B3)’. Now copy-and-paste from B3 to B4. You will find that your formula has changed in B4 to =B3+A2! This is because when you are at B4, cell B3 is ‘the cell one row up from the current cell (B4)’, and A2 is ‘the cell two rows up and one column left from the current cell (B4)’. If you copy B3 to D4, the formula in D4 will be =D3+C2. If you want to keep an address unchanged when copied, use absolute ad- dressing, indicated by $ signs before the row and column of the address. Thus, if B3 contains the formula =$B$2+A1, and this is copied to D4, it becomes =$B$2+C2 : that is, the absolute address $B$2 did not change, but the relative address A1 did change. The $ sign does not affect the value of the formula within its cell: it only affects the way the formula is copied to other cells. You can also make the column-part of an address absolute and the row-part relative, or vice-versa. Thus, if =$B2+A1 is copied from B3 to D4, it becomes =$B3+C2. If =B$2+A1 is copied from B3 to D4, it becomes =D$2+C2.
Save As. Excel can crash or freeze at any time for no reason, losing all work which is not saved to a file. Save onto your own floppy disk (which you should back up periodically), or to a file on the computer you are at, transferring it to your own account at the end.
Exercise. (Not to hand in.) Use Fill Down and Fill Right to efficiently create the array:
- 5 1. 6 1. 7 1. 8 1. 9 2. 0
- 0 2. 1 2. 2 2. 3 2. 4 2. 5
- 5 2. 6 2. 7 2. 8 2. 9 3. 0
- 0 3. 1 3. 2 3. 3 3. 4 3. 5
- 5 3. 6 3. 7 3. 8 3. 9 4. 0
Save your work when finished.
6 Application of Absolute Addressing and Naming
Example. Here is how to make a column of evenly-spaced numbers whose spacing is easy to change. First, put the spacing increment into a cell: say, into B1 put the number 0.5. Then start the column of numbers: into B3, for example, put 1.5. Underneath, into B4, put the formula =B3+$B$1. Here B is a relative address, $B$1 an absolute address. Now select the block B4:B (that is, the range between these two cells, inclusive) and Fill Down. You should get the same column of numbers as in a previous exercise: 0.5, 1.0, 1.5.... So B5 should contain =B4+$B$1, etc. Now if you change the value of B1 to 0.2, the column immediately changes to 1.5, 1.7, 1.9, etc. As an experiment, repeat the above exercise without $ signs in the formula in B4. Click to see what formulas got copied into the column. Now try filling down from =B3+B$1 in B4. Can you understand what happened? Absolute addressing is necessary when referring to a quantity (like the increment) which stays constant throughout a computation, but which you might want to change later. Another convenient way to refer to such a constant is naming a cell. In A1, type an alphabetic name for the value in the adjacent cell B1 (call it ‘dt’, for example), followed by =. That is, in A1 enter dt=. Now select B1, and in the Insert menu choose Name > Define. (In older versions, this is in the Formula menu, under Define > Name.) In the dialog box, Excel will suggest the name dt which you entered. Click OK. (If Excel suggests the wrong name or cell, enter the correct information in the boxes.) Now in your formulas you can write dt instead of $B$1, and get the same list. Formulas are more un- derstandable with named constants instead of fussy absolute addresses. Save your work.
