Loading
Everything HP200LX: Knowledge, Products, Service

 Archives
9 years, 100's of pages of HP Palmtop Paper, jammed with tips, reviews and how-to's

Links
Most useful, up-to-date Web sites devoted to the HP 200LX

Downloads
Direct link into 1000's of pieces of Palmtop software

Tips from Our Techs

2008 Catalog
(PDF, 2.6 MB)

Contact Us/About Us

 
Weekly Palmtop Paper Newsletter
(Palmtop tips, latest happenings, and HP Palmtop Paper Store information)


 

 

Privacy Policy

Using 1-2-3: Lesson 5 Modifying a Spreadsheet is Easy, but Be Careful

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.

By Carl Merkle

We started out this series of articles discussing the basics of 1-2-3 Release 2.x for new and first time users. The first two articles (Vol.4, No.4, Pg.32 and Vol.4, No.5, Pg.34) discussed how to read the 1-2-3 screen, what a cell is, how to enter data (both numbers and labels) into a cell, how to move around in the worksheet using the arrow keys, and how to save and retrieve worksheets. The third article (Vol.4, No.6, Pg. 54) showed how to use Lotus 1-2-3 as a sophisticated database that automatically calculates totals. The fourth article (Vol.5, No.1, Pg.34) discussed formulas and @functions, with an introduction to range names.

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

1

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

1

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

1

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

  1. 1. In cell D3 press (+) and then LeftArrow once. Notice that the second line of the control panel (second line, top left of screen) now reads +C3. (I use + to start many formulas because it's easy, but other valid value characters, like the minus sign, can also be used.)
  2. 2. Next press (/) (forward slash or divide sign). The cell pointer (a.k.a., highlight bar or cursor) will return to cell D3 and the control panel now reads +C3/.
  3. 3. Then press the ArrowKeys to move the cell pointer to cell C9, but DO NOT HIT ENTER YET! You'll know you are there when the second line of the control panel says: +C3/C9.

  4.  

     

Before we go any further, we should think ahead and consider how our sales spreadsheet might change in the future. Right now, our sales figures are listed in column C. Let's say they represent sales figures for the 1st quarter of the year. When the next quarter rolls around, we might want add a column between C and D and copy the @SUM formula in C9 to D9. The percent of total sales formula we've been building so far reads +C3/C9 (the sales figure for the North divided by the total sales figure). As we add sales quarters and columns, notice that the total for that quarter will always appear in row 9, but will change to subsequent columns. The total for the 1st quarter is in C9, the 2nd quarter will be in D9, the 3rd quarter in E9, and so on. The number part of the cell reference remains the same (absolute) but the letter part changes (relative). We want the formula we are building to read +C3/C$9.
  1. 4. Now press (F4) (Abs). Notice that the second line of the control panel is modified to read +C3/ $C$9. The C9 cell reference has been changed to an absolute reference. Subsequent presses of (F4) will change the C9 reference to the mixed references +C3/C$9, +C3 /$C9 and finally back to +C3/C9. Cycle through the references until you get to the mixed reference +C3/C$9. Then press (ENTER) twice.

  2.  

     

Notice that the number in cell D3 is 0 rather than some percent. To change that, press (MENU) Range Format Percent 1 (ENTER). Just format cell D3 for now. The percent 30.9% should appear in cell D3.

 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:

  1. 1. In cell E2 enter the label "Total1".
  2. 2. In cell E3 enter the formula +E2+C3.
  3. 3. In cell F2 enter the label "Total2" and then move the cellpointer to F3.
  4. 4. In cell F3 type @sum( and then move the cell pointer to cell C2 and press (.) (the period key). The second line of the control panel should now read @sum(C2..C2. Press the down arrow key once so that the second line of the control panel displays @sum(C2..C3 and then press (F4) to create absolute cell referencing. The second line of the control panel should now display @sum($C$2..$C$3. Then press the close parenthesis key. DO NOT PRESS (ENTER) YET!

  5.  

     

The next thing we need to do is change the @SUM function in cell F3 so that the first cell reference is absolute and the second is relative (i.e., we want it to read $C$2..C3). We are still in the VALUE mode ("VALUE" displayed in upper right of screen), which means we can't just press the left arrow key to move over to $C$3 and delete the dollar signs. In the VALUE mode, 1-2-3 would interpret that keystroke as a cellpointer movement and cause the formula to be entered in cell F3. Go ahead and try it! Press the left arrow key (BUT DON'T PRESS (ENTER)). Notice that 1-2-3 entered the formula in cell F3 and moved the cellpointer to cell E3. That's not what we wanted to do, so move the cell pointer back to cell F3. We'll edit the formula to change $C$3 to C3.

Editing formulas

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.

  1. 5. With your cellpointer in cell F3, press (F2) (Edit). Notice that 1-2-3 places the formula @SUM($C$2.. $C$3) back on the second line of the control panel and that 1-2-3 is now in EDIT mode ("EDIT" in top right of display).
  2. 6. Press the left arrow key three times to move the cursor to just below the $ between the C and 3. Press (DEL) to remove the dollar sign. Now move the cursor one space to the left (below the C) and press the backspace (not left arrow) key to back up over and delete the dollar sign in front of the C. Press (ENTER) to leave the EDIT mode and complete the modification. The first line of the control panel should reflect the formula as @SUM($C$2..C3).

  3.  

     

Edit mode is valuable for correcting long or complex entries. As mentioned, the cursor can be moved one space at a time to the left or right by pressing the left or right arrow keys. (It can be moved five spaces at a time to the left or right by holding down (CTRL) and then pressing the left or right arrow. Press (Fn)- (LeftArrow) (HOME) to move the cursor to the first character in the cell and (Fn)- (RightArrow) (END) to move to the last character.

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.

Spreadsheet modification commands

iPhone Life magazine


Notice about Palmtop.net
The Palmtop Network with its S.U.P.E.R. (Simply Unbeatable Palmtop Essentials Repository) software is now available under the domain name of hp200lx.net.  

 
We Buy
We buy used palmtops, working or broken: HP 200LX, HP 100LX and 1000CX.
 

Copyright 2010 Thaddeus Computing Inc