|Everything HP200LX: Knowledge, Products, Service|
Using 1-2-3: Lesson 5 Modifying a Spreadsheet is Easy, but Be Careful How a Change Effects Your Formulas
Fifth in our series on Lotus 1-2-3, this article shows how easy it is to copy, move, insert and delete rows and columns in your spreadsheet. But be careful! Changing a spreadsheet can effect formulas in unexpected ways.
This article discusses the basics of spreadsheet modification commands (Copy, Move, Insert and Delete rows and columns) and the impact of those commands on formulas with absolute and relative cell addresses. We'll define all these terms as we go on.
The cut-and-paste commands
Editing an old-fashioned paper spreadsheet was a tedious and time-consuming process. You needed a pair of scissors, a roll of tape (or glue), an eraser, a pencil -- and a large dose of patience! You used the scissors and tape to delete and move entire rows and columns of the spreadsheet. You used the pencil to enter and copy information. You used the eraser to delete or correct mistakes. You used the patience to survive the process.
Electronic spreadsheets make the whole process simpler and less prone to error by automating the calculation of the data, and by making the modification process easier. 1-2-3 has 10 built-in spreadsheet modification commands that take the place of the four manual spreadsheet editing tools described above. It is much easier and quicker to edit an electronic spreadsheet than an old fashioned paper spreadsheet if these commands are understood and used. The commands are shown in the sidebar on the next page.
The previous articles have already shown how easy it is to use spreadsheet modification commands to copy and move simple numbers and text. However, some care must be applied when using these commands with cells or ranges of cells that contain formulas with "relative" and "absolute" cell addresses.
"Relative" and "Absolute" cell references
In a previous issue we discussed how to create a formula in 1-2-3 and how the formula could be copied (or the spreadsheet modified with inserted and deleted rows) with 1-2-3 automatically adjusting the formula to reflect the proper relative addresses. For example, we have discussed in the past how to use the (MENU) Copy command to copy the @function @SUM(C18..C1) from cell C19 to cell D19. When we did this, 1-2-3 automatically adjusted the cell references relative to the original formula's position and placed @SUM(D18..D1) in cell D19. The cell reference C18..C1 is an example of a relative address. When you move it, it automatically adjusts to the correct address relative to the new column or row it is in.
In 1-2-3 there are three kinds of cell references for formulas: relative references, absolute references and references that mix the first two. As described above, relative cell references will change as you copy or move the formula to another column or row. Absolute cell references in a formula never change. The only visible difference between the two types of cell references is that absolute cell references have a dollar sign "$" placed in front of the column letter and row number of the cell. In the example above, when we copied @SUM(C18..C1) to cell D19, the cell references changed. If the @function had been @SUM($C$18..$C$1), the exact formula would have been copied over with no changes.
I bring the subject up here because the difference between absolute and relative cell references becomes very important when you begin to use the spreadsheet modification commands like Copy, Move, Insert or Delete rows and columns.
Mixed references are a single cell reference that is part relative and part absolute. (We will demonstrate a couple of practical day-to-day examples of mixed references later in this article.) You can specify a mixed references in a formula by typing a dollar sign $ before the column or row coordinates of the cell address. An example will help clarify this.
Using the above @SUM function as an example, you might have the following addresses:
@SUM(C18..C1) -- relative cell addresses.
@SUM($C$18..$C$1) -- absolute cell addresses.
@SUM($C$18..C1) -- absolute and relative addresses.
@SUM($C18..C$1) -- mixed addresses single address part relative and part absolute.
Copying a mixed address
For example, enter a formula in cell B2 that contains an absolute reference to cell A1. A simple example of such a formula might be +$A$1. The plus symbol insures that the reference is not read as a label. Also, it is easier to enter. If your cell pointer is in B2, you can enter this formula by just typing (+) and then use the ArrowKeys to move the cell pointer to cell A1. Then press (F4) and (ENTER). If you copied this formula elsewhere, it would not change.
Change the formula in cell A1 to either +$A1 or +A$1 and you now have a mixed cell reference. Copy this formula elsewhere and it may change, depending on where you copy it. The effect of copying these specific absolute or mixed cell references is reflected in the table below. Notice that if the column reference is absolute, it remains fixed if copied across columns. Notice that if the row reference is absolute, it remains fixed as copied down rows.
+$A$1 copied from B2 to cells B2 through D4
A B C D
2 +$A$1 +$A$1 +$A$1
3 +$A$1 +$A$1 +$A$1
4 +$A$1 +$A$1 +$A$1
+$A1 copied from cell B2 to cells B2 through D4
A B C D
2 +$A1 +$A1 +$A1
3 +$A2 +$A2 +$A2
4 +$A3 +$A3 +$A3
+A$1 copied from cell B2 to cells B2 through D4
A B C D
2 +A$1 +B$1 +C$1
3 +A$1 +B$1 +C$1
4 +A$1 +B$1 +C$1
You can use the ABS key (F4) to define absolute and mixed references.
The percent of total example
Now we'll construct a simple spreadsheet to demonstrate some absolute and relative cell referencing concepts. First create a new spreadsheet by pressing (MENU) Worksheet Erase Yes. Have it display values in the comma format by pressing (MENU) Worksheet Global Format , (comma) 0 (ENTER). In cells B3 through B7 enter North, South, East, West and Central. In cell C2 enter Sales and in cell D2 enter Percent. Fill in the numbers in C3 through C7 as follows:
North = 9,877
South = 4,387
East = 6,307
West = 7,429
Central = 3,981
Now enter @SUM (C2..C8) in C9 to total the amounts in column C. Your worksheet will appear as shown below with 31,981 as the sum. (The spreadsheet example used in this article appears as LOTUS26.WK1, archived in LOTUS26.ZIP (ON DISK icon) on the HP Palmtop Paper ON DISK.)
Now we want to set up cells D3 through D7 so they display the percentage of total sales. We could manually enter a formula in cell D3, but let's learn what I call the "point-and-shoot" method of creating the formula.
Example sales spreadsheet
Completed percent of total spreadsheet.
Copying formatted cells
With the cellpointer resting on cell D3, notice that the first line says D3: (P1) +C3/C$9. The P1 designation means the cell is formatted to display its contents as a Percent, with one decimal place. Use the copy command to copy the formula in cell D3 to D4 through D9. (With cell pointer on D3 press (MENU) Copy (ENTER) (.). Use DownArrow to move the pointer to D9 and press (ENTER). Wrap up the chart by entering \- in cells C8 and D8. You should have a screen that looks like the one at the top of this page.
Notice that numbers displayed in cells D4 through D9 are also formatted as percentages. The rule is that when you use the (MENU) Copy command to copy the contents of a formatted cell to another location, that cell's format will be copied along with the contents of the cell. You can use this characteristic of 1-2-3 to your advantage. Whenever you need to format and copy, you'll frequently save time by formatting first and then copying the formatted entry, instead of copying first and then formatting all of the copies.
There are many ways to accomplish the same thing in 1-2-3
One of the greatest strengths of an electronic spreadsheet is also it's greatest weakness -- significant flexibility. There are many ways to accomplish the same task and no method is wrong if it does the job adequately. However, choosing between a number of possible ways to accomplish a given task can be confusing, especially to an inexperienced spreadsheet user. Some solutions are preferred because of their elegance and their ability to handle normal spreadsheet modifications. Without an understanding of the whole -- what you want to accomplish with a particular spreadsheet -- it's easy to choose a way to do something that paints you into a corner.
For example, let's demonstrate two ways of creating cumulative totals formulas (like you might use in an electronic checkbook to add up a column of numbers). One way approaches the problem the same way people approach the task manually. It works, but is a little problematic when modifying the worksheet. The other way takes a little longer to construct (and provides a lesson in editing the contents of a cell) but is more flexible when making spreadsheet modifications - specifically when deleting or inserting rows - a common expected activity.
Modify the "Percent of Total" example we started above as follows:
There are two major ways of replacing a cell's contents. First, you can type over it, and second, you can edit the entry by moving the cell pointer to the desired cell and pressing (F2). Let's see how to edit the @SUM function in cell F3.
When 1-2-3 is in VALUE or LABEL mode pressing F2 will place 1-2-3 in edit mode. When 1-2-3 is in EDIT mode pressing F2 will place it in VALUE or LABEL mode. F2 then acts as a toggle key. Knowing this can be useful when editing long and complex formulas. Also you should be aware that while 1-2-3 is in edit mode pressing (F4) (Abs) will toggle through absolute cell referencing on the cell reference that the cursor happens to be under at the time.
Deleting rows can cause formulas with relative addresses to stop functioning properly.
Impact of spreadsheet modifications on different formulas
Move your cellpointer to cell E3 and press (MENU) Copy. Copy E3 through F3 to E3 through E7. Both totals columns should have the same results. However now we'll observe the impact of deleting a row on these formulas. To do this move the cellpointer to cell E5 and the press (MENU) Worksheet Delete Row (ENTER). Your spreadsheet should display the screen shown at the bottom of this page.
Notice that the formulas in column E below the deleted row "blew up" (they now display error for the cell contents). This is because each formula in column E references the cell just above it (e.g., the formula in cell E3 is +E2+C3, which references cell E2, and so on). When we deleted row 5, we deleted cell E5, and cell E6 became E5. Lotus attempted to move the formula previously in E6 (i.e., +E5+C6) to cell E5, but the +E5 cell reference in the formula no longer made sense. The same was true for E7 moving to E6, but not for the formulas in column F.
Let's also observe another difference with inserting rows. Let's say we wanted to add a North East region to our sales worksheet. The easiest way to do that is to insert a blank row and copy the formulas from one of the other rows into it, and modify the sales numbers. Move the cellpointer to cell E4 and press (MENU) Worksheet Insert Row (ENTER). Then move the cellpointer to B3 and copy the contents of the "North" row, B3 through F3, to B4. Your spreadsheet should look like the one above.
Notice that there are now two "North" columns. It's easy enough to change the name in B4 to "NorEast," but for the moment lets not change the sales figures. That is, let's assume that the sales figures for the North and NorEast are the same.
Notice that the figures in the E column don't make sense anymore. The column is supposed to give a running total of the regions, but the running total for the two North and the South regions is less than the total for the two North regions. The running totals in the F column are accurate. The mixed formulas in column F proved to be most flexible under editing circumstances.
In this example 1-2-3 did what it was told and behaved predictably with the insert and delete rows commands. The challenge to you as the user is to construct formulas that will produce the results you want when you perform predictable spreadsheet modification commands. This will not always be intuitive and will at times take some thought, and trial and error. Of course, the trick like the one you just learned for creating a running total will come in handy. Using absolute cell references at appropriate places and understanding how to edit and modify formulas will greatly improve your spreadsheet skills.
Adding a row to a spreadsheet can cause formulas with relative references to not function properly.
You may also want to explore and play with the various spreadsheet modification commands to further understand what happens to formulas as you use those commands. You should work with non-critical data to start. If you experiment with critical data, you may want to keep the undo feature on (use the F1 key to get instructions on using Undo) until you feel comfortable with your level of knowledge.
Copyright © 2010 Thaddeus Computing Inc