Introduction

Lotus 1-2-3 is integrated software that provides spreadsheet, graphics, and database capabilities. Integrated software means that the commands for the three different functions are in the same program. In fact, 1-2-3 doesn't have three modes of operation. The commands to create a graph are on the same menus as the commands to sort a database or change the width of a worksheet column. 1-2-3, however, is primarily a spreadsheet program. That's where the real strength of 1-2-3 lies, and to use graphics or database functions, you must be proficient in the spreadsheet commands and concepts.

1-2-3 is also general purpose software, meaning that 1-2-3 is "blank" when you first start it. It knows nothing about an income statement, budgets, or balancing a checkbook. 1-2-3, instead, contains a large number of commands, operators, and functions that can perform the calculations necessary to perform the work you want to do. It's your job to build the worksheet with the facilities that 1-2-3 provides. This means that you must know the precise calculations and procedures you need to perform to do your work, and then you must know how to use 1-2-3 well enough to get it to do just that.

Using 1-2-3 can be easy or difficult, depending on the nature of your project and your level of experience. The important thing is to start small and master the fundamentals of 1-2-3. Once your feet are wet and you've successfully created a number of small projects, try your hand at larger projects.

General Principles

First, using 1-2-3 or any other computer program is not difficult. The computer does, however, demand a high degree of precision in your work. For example, in almost any action you take in 1-2-3, the position of the cell pointer is vitally important to the success of the operation. You need to make sure that you've positioned the cell pointer properly.

Second, each key has a highly specific meaning. For example, at first glance the backspace key and the left arrow key might appear to do the same thing, but they don't. Pressing one when the situation calls for the other does the wrong thing, and you'll have to make corrections or adjustments.

Third, be aware of the current situation. If you press a key by mistake, 1-2-3 will probably do something you didn't intend. For example, if you meant to press "C" to start the Copy command, but you press "R" instead, you've started the Range series of commands. You must get rid of the start of the Range command before you can press the key you originally meant to press, "C" to start copying. If you proceed as though you correctly summoned the Copy command, you'll make additional errors that will require correction. Be aware of what 1-2-3 is asking for, and respond to that situation.

Fourth, be aware of the capabilities and limitations of 1-2-3. To familiarize yourself with what it can do, read the entire documentation book that came with your copy of 1-2-3. You don't have to read it to understand everything right now, but read it to be aware of features that exist, and in what situations you might use them. For example, if you never read far enough to learn that 1-2-3 has built-in functions to compute depreciation, you might find yourself spending hours creating depreciation tables manually. Or you might try to manually put page numbers, headers, or footers in the document, when 1-2-3 can print these automatically.

As for limitations of 1-2-3, the best advice is to start small and then do the big project. For example, 1-2-3 works with databases, but 1-2-3 is limited as to the size of the database it can work with, the type of reports it can easily create, and it can work with just a single database. These limitations mean that 1-2-3 isn't the right product for many types of database situations. Investigating 1-2-3's database capabilities with a small set of data will let you find out whether 1-2-3 can do what you want. If you went ahead and entered a large body of data to 1-2-3 and then found that 1-2-3 couldn't do what you want, you'd have wasted a lot of time.

Remember that you, and not 1-2-3, bear the responsibility for the correctness of your worksheets. 1-2-3 is perfectly happy to perform any type of arithmetic that you develop formulas for. And 1-2-3 performs its calculations without knowing that it's creating an income statement or balance sheet. If you enter the wrong formulas or commands, 1-2-3 will dutifully perform the action you indicate, even though it may not be appropriate. Unfortunately, many of your incorrect formulas or commands will not produce error messages from 1-2-3. It's important to understand the how and why of everything you do, and you should work through this exercise with that in mind, instead of just typing the formulas and commands as instructed.

Finally, use your imagination. It's impossible for anyone to teach you all there is to know about 1-2-3 (or any other software) and how to apply its features to your situation. Your imagination and experimentation will lead you towards getting the most out of your software.

The Nature of a Spreadsheet

The heart of Lotus 1-2-3 is its spreadsheet. An electronic spreadsheet is divided into rows and columns, similar to a sheet of ledger paper or other ruled paper. 1-2-3 doesn't display the horizontal and vertical ruler lines, but they're still there in spirit, dividing the workspace into rows and columns. Columns of the spreadsheet are identified by letters (column A, B, C, and so forth), while rows are numbered (row 1, 2, 3, and so forth).

The intersection of a row and column produces a cell, the key element of the worksheet. Everything you type (with a few minor exceptions) goes into one cell or another. A cell can contain three types of entries: Labels are words, usually titles for rows and columns. Numeric entries are numbers that you type. Formulas produce calculated results--the key to using the spreadsheet. Let's take a simple example and see how these three cell types work.

If we want to make a simple profit and loss statement, we might start by typing labels to identify the rows and columns of data. To enter a label, you move the cell pointer--the highlighted block that moves from cell to cell--to the right cell and type the words. After entering labels, the spreadsheet might look like this:

A B C
1 January February
2 Sales
3 Costs
4 Profit

That's a start. Now, to enter some Sales and Costs figures, move the cell pointer to the proper cell and type them.

A B C
1 January February
2 Sales 100 150
3 Costs 60 80
4 Profit

Now, to calculate the Profit. What we need to do is move to cell B4 (for the January Profit) and write a formula that in effect says "Start with the January Sales and subtract the January Costs." 1-2-3 doesn't let you use notations like "January Sales" in a formula. Instead, each cell has its own address to identify it in a command or formula. You form a cell's address by first taking the column letter and then the row number. For example, the January Sales are in column B and row 2, so the address for January Sales is B2. You need to write B2 and not 2B. Now we know how to write the formulas:

A B C
1 January February
2 Sales 100 150
3 Costs 60 80
4 Profit +B2-B3 +C2-C3

1-2-3 does not show the worksheet formulas on the screen or on the printout, but instead shows the formula values:

A B C
1 January February
2 Sales 100 150
3 Costs 60 80
4 Profit 40 70

1-2-3 does remember the formulas, however, and if you change the Sales or Costs figures, 1-2-3 updates the Profit formulas to show the new results. That's one big advantage of formulas--once you've written the formulas, you don't have to perform the calculations again. Another advantage to using formulas is that by using the Copy command, you can quickly create duplicate copies of a formula to calculate the Profit through December. Once you create a formula, then, you can quickly duplicate it as many times as you need. A third advantage to using formulas is that 1-2-3 contains nearly 100 functions--special devices that help formulas perform complicated arithmetic easily.

In a nutshell, that's what an electronic spreadsheet like Lotus 1-2-3 is all about. As you might imagine, there are a few additional details involved in getting this to work, but the spreadsheet concept is quite simple once you master the fundamentals.

This Script

First, start 1-2-3 or clear the worksheet if you need to.

Starting 1-2-3 depends on many factors, such as whether your computer has a hard disk drive with 1-2-3 installed on it, the manner in which 1-2-3 was installed on the hard drive, the name of the subdirectory of the hard drive that contains the 1-2-3 software, and whether you've created batch files or have a menu system for starting programs. For this reason, we won't worry about starting 1-2-3. If you've followed 1-2-3's installation instructions, those instructions tell you how to start 1-2-3, or if someone setup your computer and installed 1-2-3 for you, they'll know how to start it.

Navigation

Navigating about the worksheet is a necessary skill. You'll want to move about the worksheet to examine different parts of it, to enter labels, numbers, or formulas into cells, and to work with ranges. The more efficient your navigation skills, the easier your work will be.

1-2-3 uses the cursor control keys to the right of the keyboard for navigation. These keys perform two functions: cursor movement and a numeric keypad. Normally, when you start 1-2-3, the keys have the cursor control meanings. If you want to use the keys for numeric entry, press the Num Lock key. This shifts the meaning of the keys to numbers. Pressing Num Lock again shifts the meaning back to cursor control. Most people use these keys as cursor controls, entering numbers with the regular number keys at the top of the keyboard.

Use these keys to move about a 1-2-3 worksheet:

Left arrow, Right arrow - These two keys move the cell pointer one column to the left or right.

Up arrow, Down arrow - These two keys move the cell pointer one row up or down.

Page Up, Page Down - These two keys move the cell pointer one screenful of rows up or down. Normally, this is 20 rows, but if you're using a horizontal window split on the worksheet, these keys will move however many rows are contained in the current window.

Big left, Big right - These two keys, actuated by holding down Ctrl (the Control key) and pressing Left arrow or Right arrow, move the cell pointer one screenful of columns to the left or right. The number of columns moved depends on the width of the columns, and whether the screen is split with a vertical window.

Home - Moves directly to cell A1, or if you have worksheet titles in effect, to the top left corner of the active worksheet area (the part of the worksheet not frozen by titles).

End - Does nothing by itself, but modifies the action of the next cursor key pressed. Pressing End displays the indicator END at the bottom right of the screen; you can press End again to turn the indicator off if you pressed End by mistake.

After pressing the End key, the Up arrow, Down arrow, Left arrow, and Right arrow keys move to the end of the row or column in the direction of the key. When the worksheet is empty, these keys move to the end row or column of the worksheet. When the cell pointer is within a row or column of figures, using End moves to the last cell in the row or column. When the cell pointer is in an empty area, using End moves to the first filled cell.

Using the End key to your advantage will greatly speed your work with 1-2-3, as moving to the end of a row or column of figures is a common movement. Not using End is the mark of a novice spreadsheeter.

F5 (Goto) - After pressing this key, 1-2-3 will ask for the address of a cell. If you type a cell address and press Enter, 1-2-3 moves directly to that cell. This is handy for moving long distances when you know the address of the cell.

Remember, most keys on the keyboard repeat if you hold them down for very long. This may lead to confusion at times due to the buffering action of the keystroke buffer. For example, if you press Page Down, 1-2-3 moves down a page. But that action takes a certain amount of time to complete. If you hold down the Page Down key, the keyboard will send repeated Page Down commands to 1-2-3 faster than 1-2-3 can process them. In other words, the keyboard is getting ahead of 1-2-3. In this case, the extra keystrokes are stored in the keystroke buffer and then fed to 1-2-3 as soon as it is ready to process them.

If, then, you press the Page Down key for some time and then release it, 1-2-3 may keep moving the cell pointer because of extra keystrokes stored in the keystroke buffer. Also, the keystroke buffer can hold only so many keystrokes (normally 16), and when the buffer fills, the computer emits a screeching sound. To prevent these effects, avoid pressing keys like Page Down for very long.

Important (For Now) Function Keys

1-2-3 performs many actions and commands through the use of the function keys. This reference gives the meaning and use of each of the function keys.

F1 Help

The F1 Help key is useful when you can't remember some detail of a Lotus 1-2-3 command--the order of arguments in the @PMT function, for example. At any time, even when in the middle of typing a formula or menu command, you can press the Help key and receive a screen of help information. On the help screens, various words are highlighted. You can move from one highlighted word to another with the cursor movement keys and press the Enter key when a topic you want to see is highlighted. The help facility is context-sensitive, meaning that 1-2-3 may sense what you need help with, and will display a screen of (hopefully) pertinent information.

When you're finished viewing the help screens, press Escape to return to where you were when you pressed the Help key.

F2 Edit

The F2 Edit key lets you edit the contents of cells. If you want to change the contents of a cell, you could simply type a new value or formula in the cell. In this case, the value you type replaces the old value in the cell. This method is efficient when you want to type a short label or number into the cell.

If the cell entry is long, and you need to make only a small change to the contents, it's best to use the Edit key. To edit a cell, move to the cell to edit and press the Edit key. 1-2-3 places a copy of the cell's contents on the edit line.

To delete characters on the edit line, use the Backspace key to delete characters to the left of the cursor, or use Left arrow and Right Arrow to position the cursor under the character you want to delete and then press the Del key to remove the character.

To insert characters on the edit line, remember that 1-2-3 works in insert mode. This means that 1-2-3 inserts characters that you type in front of the cursor, and moves the rest of the characters to the right to make room for the new characters that you type. If you wish, you can press the Insert key to switch to insert mode, in which case the characters you enter type over and replace existing characters.

Finally, to complete the edit and place the edited cell contents in the worksheet, press Enter. If your edit is not coming out right and you'd like to disregard your changes and leave the cell as it was, press Escape.

Also, see the use of the Edit key with the Calc key.

F4 ABS

This key makes a cell address absolute so that it isn't adjusted by the Copy command.

F5 Goto

The F5 Goto key lets you move to a cell quickly. To use the Goto key, simply press F5 and then in response to 1-2-3's question, type in the cell address that you want to go to. Press Enter to complete your answer.

F9 Calc

The F9 Calc key recalculates the worksheet. This key is most useful when you switch recalculation mode to manual. In this case, if you make changes to the worksheet, 1-2-3 senses that the worksheet needs to be recalculated, so 1-2-3 displays the CALC indicator at the lower right portion of the screen. While the CALC indicator is on the screen, the formulas in the worksheet may not be current and correct, so you need to use the Calc key to force a recalculation of the worksheet. To switch between automatic and manual recalculation modes, use the Worksheet Global Recalculation command.

F10 Graph

The F10 Graph key performs the same function as the Graph View command. If you have a graph defined, at any time you can view the graph by pressing the Graph key. To return to the worksheet, press any key.

Alt-F4 Undo

This useful key, available only with 1-2-3 Release 2.2, restores the worksheet to its previous state after you perform an erroneous command. For example, if you use the Range Erase command and accidentally erase too large an area on the worksheet, you can press this key to restore the data.

Entering Labels

Once you have 1-2-3 started, in order to make sure we're starting in the same place, we'll use the Home key to move to cell A1.

Prepare to enter the labels. Good labels--the words and phrases identifying the rows and columns of data--are essential to a good worksheet. Here's some tips for typing labels:

Note the important steps in entering labels to the worksheet. First, move the cell pointer to the cell where the data should appear. Type the data, noting that it appears on the edit line as you type. Then press Enter to complete the cell entry.

Also, note the appearance of the label at the cell indicator at the top of the screen. It looks just like the cell entry itself, but is preceded by an apostrophe. The apostrophe is a label prefix, and it was put there by 1-2-3--you didn't make a mistake. Later on we'll discuss the meaning and use of label prefixes.

If you make a mistake and don't notice it until after you press Enter, you can always retype the entire cell contents. This works because when you type something into a cell, 1-2-3 first erases the current contents of the cell, then enters the new contents. Later we'll see that the F2 (Edit) key makes correcting cells easier.

We'll also use a shortcut. When entering labels and numbers to the worksheet, you can press a direction key to both complete the cell entry and move the cell pointer at the same time.

Now let's move back to the top of the worksheet and complete the column labels.

There's one more point to remember about entering labels. If you enter a telephone number, such as 555-1234, 1-2-3 will think that you're entering a formula. Also, an address, such as 100 N. Main Street, will cause an error, because 1-2-3 thinks that you're trying to enter a numeric value. Start these entries with the ' (apostrophe) label prefix to avoid these problems.

Column Widths and Menu Commands

What's wrong with this worksheet so far? If you look at the labels in column A, you'll see that they hang over in to column B, and some extend all the way to column C. We'll fix this by changing the width of column A, making it wide enough to accommodate the width of the labels we typed. 1-2-3 is flexible enough to let you change the width of a column at any time. We could have adjusted the width before doing the typing in column A, but we wouldn't have known how wide to make the column.

Let's adjust the width of column A to fit our data. The first step is to place the cell pointer anywhere within the column whose width we want to change.

At this time, we need to issue a menu command to change the column width. 1-2-3 is a menu-driven program, in that when you want to issue a command, you select it from a menu. Most actions you perform will be menu selections except for moving the cell pointer, entering formulas, and function key commands.

1-2-3 doesn't display the menus while you're working with the worksheet. Instead, you press the slash key to summon the menus. Once you press the slash key, 1-2-3 changes to MENU mode (at the top right of the screen) and displays the main command menu. The menu consist of two lines at the top of the screen. The first line is the actual menu itself, and it lists the available commands. The second line shows either a second menu that you'd get if you select the highlighted command, or a short description of the highlighted command. For example, when you first press the slash key, you get this menu:

Worksheet Range Copy Move File Print Graph Data System Quit
Global Insert Delete Column Erase Titles Window Status Page

The available commands are Worksheet, Range, Copy, Move, File, Print, Graph, Data, System, and Quit. At any time, one menu command is highlighted. The second line of the menu shows either a second menu that you'd get if you select the highlighted command, or a short description of the highlighted command.

To select a command from the menu, first press the slash key to activate the menus. Then follow either of these two methods:

Note that Escape works only while the menus are still on the screen--that is, before you've finished the command. If you go ahead and complete the command and return to READY mode, the Escape key doesn't undo the action. You'll have to correct the situation by some other means. (Alt-F4, the Undo key, may be helpful here).

When entering menu commands, we'll use the shortcut method of typing the first letter of the command we want.

We knew it was allowable to use the arrow keys to stretch the column width because the mode indicator at the top right of the screen read POINT.

That's it for changing the column widths. Don't worry about getting it exactly right this time--if you decide tomorrow that the column would look better at a different width, you can change it then.

Note that this command changed the width of just one column, the current column where the cell pointer was before you pressed the slash key to start the menu command. If you want to change the width of all the columns at once, use the Worksheet Global Column-Width command.

At this point, the worksheet should look like this:

A B C D E F G
1 Item Q1 Q2 Q3 Q4 Total Average
2 Sales
3 Variable Costs
4 Contribution
5 Fixed Costs
6 Operations Income
7 Taxes
8 Net Income

Entering Numbers

Now we're ready to enter some numbers into the worksheet. We'll enter just four numbers now, the Sales figures for the four quarters. Follow these guidelines for entering numbers:

Note that the numbers don't look much like dollars and cents. For now, don't worry about the appearance of numbers. We'll let 1-2-3's formatting commands take care of that later.

After entering the four Sales figures, the worksheet should look like this:

A B C D E F G
1 Item Q1 Q2 Q3 Q4 Total Average
2 Sales 100 87 100 125.78
3 Variable Costs
4 Contribution
5 Fixed Costs
6 Operations Income
7 Taxes
8 Net Income

Formulas

That's it for entering numbers. We're ready to start entering some formulas now and get some real work done.

When entering formulas, follow these guidelines:

First, we'll calculate the Variable Costs for the first quarter in cell B3. In this company, Variable Costs are 30 percent of the Sales figure.

That's an example of typing a formula by typing the cell references--the "B2" in this formula--yourself. For the next formula, we'll use 1-2-3's cell pointing feature. Cell pointing lets you move the cell pointer to a cell to include it in a formula, leading to easier entry of formulas and less errors.

Let's do it again in cell D3 for practice.

The Copy Command

Now, let's use the Copy command to copy the formula from cell D3 to cell E3. It's important to understand how the Copy command works in 1-2-3, and understanding the command starts with a look at the formulas we've written. One way to interpret the formula in cell D3 is as ".3 times the value in cell D2." A different way is to describe the formula is without using cell addresses, using the spatial arrangement of the formula instead. With this method, we might describe the formula as ".3 times the cell one row above."

That's important, because 1-2-3 copies the spatial meaning of the formula instead of the actual text of the formula. After all, if the Copy command placed the formula ".3 times the value in cell D2" in cell E3, it wouldn't have done anything useful. But placing the meaning ".3 times the cell one row above" in cell E3 produces the formula .3*E2, which is just what we'd type into cell E3 if we weren't going to use the Copy command. This type of cell address interpretation is called relative references. 1-2-3 has another type of address interpretation called absolute references.

Whenever you use the Copy command, you must make sure that the formula you're copying has the same relative meaning in the cell you're copying from and the cell or cells you're copying to. Not all formulas are copyable without taking special steps, and although the Copy command will produce the copies, they won't be correct.

It's easiest, although not necessary, to have the cell pointer on the cell you want to copy from before you start the Copy command, so make sure the cell pointer's at cell D3.

Note that the Copy command asked first for the range to copy from, and then the range to copy to. You must strike the Enter key to complete each question. A common mistake is that since 1-2-3 often guesses and displays the correct cell to copy from, you might not remember to press the Enter key. 1-2-3 needs to know if its suggested cell to copy from is correct, so press Enter before moving to the cell to copy to.

That's an example of copying from a single cell to another single cell. Later, we'll see more advanced examples of the copy command. Now, to continue with the next row of formulas. We'll calculate the Contribution for the first quarter, which is equal to the Sales minus the Variable Costs.

Now let's use the Copy command to copy the formula in C4 to both D4 and E4, using a single Copy command. To use the Copy command to copy to more than one cell, we need to know about ranges.

Ranges

Ranges are an important concept in 1-2-3. A range is simply a continuous rectangular block of the worksheet. A range could be a single cell, part of a row or column, or the entire worksheet. Some things you can do with a range include copying, moving, erasing, printing, saving, formatting, aligning, arithmetic, and so forth.

Here's an example range:

This range, which includes cells B2, C2, D2, and E2, is part of a row, so it's certainly a range. To indicate this range in a command, we write one corner cell, two periods, and the opposite corner cell. This range, then, is B2..E2. Another example:

A
+---+
1 | |
| |
| |
| |
| |
| |
1000 | |
+---+

This example range covers column A from row 1 to row 1000. We write this range as A1..A1000. You can see that part of the power of ranges is the ability to specify a large number of cells in a compact notation. Another example:

B C D E
+-------------------------+
2 | |
| |
| |
| |
200 | |
+-------------------------+

This example range covers rows 2 through 200 from columns B through E. This range, then, covers both columns and rows, while the earlier examples covered just a row or column. To write this range, take one corner, say B2, and write the diagonally opposite corner: B2..E200. What if we took cells B2 and E2 as the corner of the range and wrote B2..E2? Then our range covers just row 2, and it's the same range as the first example. When your range covers both rows and columns, you need to use diagonally opposite corners.

When writing these example ranges, we took the top left corner cell first and marked the range from there. You can start marking a range, however, from any corner. For example, we could have written E2..B2, A1000..A1, and E200..B2 for these ranges. But it's become customary over the years to write a range by starting at the top left corner and proceeding from there.

It's also possible, through the Range Name Create command, to assign a name to a range. For example, you might assign the name Sales to the range B2..E2. From the on, when you need to refer to the range in a formula or command, you can type the name Sales. 1-2-3 even lets you select the range name from a menu of range names in some cases.

Now that we know about ranges, let's use them in the next Copy command:

We'll see even more copying in a moment. Now, on to Fixed Costs. Fixed Costs for this company are a numeric value ($25.00), not a formula.

We can copy this value to cells C5, D5, and E5 now.

To complete the rest of the rows of formulas, we could proceed as we have--type a formula into column B, copy it across the row, and go to the next row. What we're going to do, however, is faster. We'll complete all the formulas in column B, then copy them all at once to the rest of the worksheet. This is possible because the Copy command asks for a range to copy from as well as a range to copy to.

Now, let's do the copying. In this case, we're copying from a range of cells, not just a single cell. It's easiest if you position the cell pointer at the top left corner of the range you want to copy from before you start the Copy command, which is cell B6 in this case.

Note that we didn't have to press the Period key to anchor the cell pointer, although we could have without causing harm. That's because 1-2-3 displays the proposed range to copy from in the form B6..B6, which means that the cell pointer is already anchored. If 1-2-3 had displayed it as simply B6, the cell pointer is in free-floating mode, and you'd have to press the Period key to anchor.

That's it for this section of the worksheet. Now we'll move to the total and average columns of the worksheet and complete those formulas.

Functions

To calculate the sum of the sales figures, we could write the formula +B2+C2+D2+E2. While this is feasible for this small worksheet, suppose we had to calculate the sum of several years' of monthly data. In this case, the formula would be very long, and the chance of mistake in entering the formula is great. Also, remember that a formula can be a maximum of 240 characters long. If the formula needs to add more than about 50 cells, you won't be able to type the formula. To help us perform arithmetic on a large area of the worksheet, we need to understand Lotus 1-2-3 functions.

Lotus 1-2-3 offers functions to allow you to perform more complicated types of arithmetic than addition, subtraction, multiplication, division, and exponentiation. Functions all share a common form: @name(arguments). The @ is a convention that 1-2-3 uses to signify the start of a function. If you see @ in a formula, you know it's the start of a function. The name of the function is how we identify. 1-2-3 has about 100 different functions, so there are 100 different names. The arguments, which are enclosed in parentheses, are the input values to the function--the data the function is to work with.

Let's look at one example using the @log (logarithm) function. We might write @log(10) as a formula. In this case, the argument to the @log function is the number 10, and 1-2-3 returns 1 as the answer or result to the function. We could write @log(B45), in which case 1-2-3 calculates the logarithm of the number in cell B45. Or, we could write @log(A1+35-B45), in which case 1-2-3 calculates the value of the equation and then calculates the logarithm of the result. You can see that so far, arguments can be simple numbers, cell addresses, or formulas.

The function we need to use is the @sum function, which can accept a list of arguments. We might write @sum(1,2,3,4), which would evaluate to 10. Or, we might write @sum(B2,C2,D2,E2), which would produce the sum of the sales figures. But this formula is not much relief over typing B2+C2+D2+E2. What distinguishes the @sum function is that it can accept a range of arguments, as in @sum(B2..E2). This formula sums four cells. The formula @sum(A1..A1000) sums 1,000 cells, but is no more difficult to write than is the four cell formula. It's the power of the range notation to specify a large number of cells coupled with the @sum function's ability to accept the range as the argument that makes this scheme work.

We'll use the @sum function to add the Sales figures.

We could type the range B2..E2 directly to the @sum function, but we can also use the cell pointing technique to highlight the range to sum.

That's entering a formula with a range argument by using cell pointing. In the next formula, using the @avg function, we'll type the range reference directly.

Besides the @sum and @avg statistical functions, 1-2-3 also contains the @count, @max (maximum), @min (minimum), @std (standard deviation), and @var (variance) functions for other statistical calculations.

Now let's copy these two formulas with one stroke of the Copy command. We can do this with a single Copy command because the two formulas in F2..G2 are in a range (the from range), and the empty space F3..G8 where we want to place copies of the formulas is a range also.

Formatting Numbers for Appearance

That's it. Our worksheet is complete, at least as far as having all the formulas and numbers we need. Now we'll enhance the looks of the worksheet by using the formatting commands. We'll also use the End key to speed up moving around the worksheet and highlighting ranges.

First, we'll use the Range Format command to control the appearance of numbers in the worksheet. This command assigns a format to a cell or range of cells. The format, which can be one of several styles, makes numbers appear in a more attractive format, with dollar signs, commas, percent signs, and other symbols. The format also enforces a certain number of decimal places--either adding zeros to the end of the number, or rounding off excess decimals. The formats belong to the cell, not the number in the cell, so once you format a cell, any other number appearing in the cell appears in the cell's format.

Here, since we wanted to move the cell pointer and extend the range highlight to the end of this row, we used the End key to place 1-2-3 in End mode (at the bottom of the screen 1-2-3 displayed END). Then, pressing Right arrow moved to the last non-empty cell in that direction--the end of the row. Use the End key whenever you can, as it will greatly speed your work with 1-2-3.

Note that in the cell indicator (at the top left of the screen) we now see the indication (C2) before the contents of the cell, meaning the cell is formatted with the Currency format, two decimal places. You can't type this indicator in yourself; the only way to get it is to use the Range Format command.

Now, let's format the rest of the numbers. We don't want dollar signs on these numbers, so we'll use the Comma format.

These cells have (,2) in the cell indicator for Comma format, two decimal places.

One caution about formatted numbers: Although these numbers look like they have two decimal places, that's for show only. 1-2-3 always remembers the full value of the number (up to fifteen digits of precision) and uses that full value in further calculations. Thus, you may experience rounding errors from time to time. The @round function lets you control these rounding errors.

Here's an example worksheet showing all the formats that 1-2-3 can display values with:

A B
12345.6789 -12345.6789 Unformatted
12345.68 -12345.68 Fixed
$12,345.68 ($12,345.68) Currency
12,345.68 (12,345.68) Comma
12345.6789 -12345.6789 General
1.23E+04 -1.23E+04 Scientific
+A1+A2 +A1+A2 Text

0.2534465 -0.2534465 Unformatted
25.34% -25.34% Percent

Aligning Labels for Appearance

Now we'll use the Range Label command to control the alignment of the column titles in row 1. 1-2-3 aligns cells through the label prefix, the first character of any cell that contains a label. You can see the label prefix at the cell indicator at the top left of the screen, but not in the worksheet cell itself. The three label prefixes that control alignment are the apostrophe ('), which aligns to the left, the caret (^), which aligns in the center, and the quotation mark ("), which aligns to the right. The default label prefix is the apostrophe, meaning that if you don't type the label prefix yourself, 1-2-3 types the apostrophe for you.

1 Cell Display Cell Contents
2
3 Hello 'Hello
4 Hello ^Hello
5 Hello "Hello
6 Hello |Hello
7 ------------------------ \-
8 ======================== \=

You can enter label prefixes for alignment in one of three ways:

Since our labels are already typed, we'll use the Range Label command to align them.

Always use label prefixes to control the alignment of labels. Many times people will align the labels by typing spaces before the label. The label prefix method is easier, and has the advantage that if you later change the column widths, the labels will automatically move to the proper position within the new width of the column. Labels aligned with spaces won't do this.

Drawing a Line

To further enhance the appearance of the worksheet, draw a double line after the Net Income figures. To draw lines in a cell, use the \ label prefix, which repeats the following characters enough times to completely fill the width of the cell. We'll type \= in the cell for a double line. Type \- to draw a single line.

First, move to cell A9 and enter the cell contents:

Now, we'll use the Copy command to extend this line across the rest of the worksheet columns.

Can we use the End key to extend the cell pointer to column G as we did when we formatted the cells? The answer is no. That's because all the cells to the right of B9, the cell where the cell pointer is, are blank--there's nothing for 1-2-3 to use as the end of this row except cell IV9, the right-hand border of the worksheet. But there is a way to use the End key, sometimes called riding the range, as follows:

When a cell is filled with characters using the repeating label prefix (\), the filling is dynamic. If you decide to make any of the worksheet columns wider or narrower, 1-2-3 will add or delete characters so that the cells will always be filled with just the right number of characters, and the line will remain a solid line just as it is now.

Saving the Worksheet

Now, let's save the worksheet to the disk so we'll have a permanent copy. Saving is necessary because while you're creating or using a 1-2-3 worksheet, you're working with data in the RAM memory of the computer. RAM memory is good because it provides fast access to the data so that 1-2-3 can calculate quickly, but it's bad because it's temporary--if you turn off the computer, the RAM memory goes blank. Disk drives (either hard drives or floppy drives) are bad because they're slow, but good because they provide permanent storage.

1-2-3, like many programs, does not save any data to disk automatically. You must give the command to save. When you do, 1-2-3 transfers a copy of the worksheet in RAM to the disk drive, storing it in a file. Since files on the disk have names, you must also supply a name for the file. Your worksheet file names can be up to eight characters long and may include all the alphabet characters (capitalization doesn't matter), the digits 0 through 9, and the $ & # @ ! % ' ~ ( ) - _ { } punctuation characters. Do not use spaces in a worksheet file name. We'll use the name budget for this worksheet:

While 1-2-3 is saving the worksheet, you'll see the light on your disk drive go on to indicate that the drive is working. Also, 1-2-3's mode indicator at the top right of the screen flashes the word WAIT, although if you have a hard disk drive, this small worksheet saves so quickly that you may not see it.

Note that after 1-2-3 completes the save, the worksheet remains on the screen. You can continue working with the worksheet at this time.

1-2-3 adds the extension .wk1 (for Release 2, 2.01 and 2.2) or .wks (for Release 1 and 1A) to the worksheet file name you supply. If you want to copy worksheet files with DOS commands, you'll have to supply the extension in the copy command.

A related command is the File Directory command, which lets you specify on what disk drive and directory 1-2-3 should use to retrieve and save worksheets. For example, if you have a computer with a hard drive, you may have a directory called c:\data\lotus to store your worksheets in. But at other times, you may want to use your floppy diskette drive for storage. In this case, you could issue the File Directory command and type "a:\" in response to the question 1-2-3 asks. Now, whenever you issue the File Retrieve or File Save commands, 1-2-3 will use the A diskette drive. To go back and use the directory on the hard drive, issue the File Directory command and type in c:\lotus\data.

Changes made with the File Directory command are not remembered when you quit 1-2-3. To make a permanent change, use the Worksheet Global Default Directory command.

Clearing the Screen

Let's say that at this time, you're finished with this worksheet and you'd like to start building another worksheet from scratch. You need to clear the work area by using the Worksheet Erase command:

At this time 1-2-3 presents a two-item menu with the commands Yes and No. This is a safety check point, because if you erase the worksheet before you save it, it wouldn't exist anymore. We just saved the worksheet a moment ago, and we haven't made any changes since we saved it, so it's safe to erase the worksheet from the work area.

At this time, 1-2-3 clears the screen and the work area looks just as it does when you first start 1-2-3. If for some reason you issued this command by mistake and you don't want to clear the work area, chose the No command from the menu. 1-2-3 will return to READY mode just as if you had never issued this command.

With Release 2.2 of 1-2-3, 1-2-3 will beep at this time if you've made changes to your worksheet but haven't saved them. You can elect to go ahead and clear the worksheet, or cancel the command and save your changes.

Remember, the Worksheet Erase command erases only the work area (that is, the computer's RAM memory). This command does not affect worksheet files on the disk in any way. To erase a worksheet file from the disk, use the File Erase Worksheet command. If you execute this command, the worksheet is removed from the disk.

Retrieving the Worksheet

At this time, we'd like to continue working on the budget worksheet. Before we can work with it, however, we need to retrieve it from the disk to the work area in RAM memory with the File Retrieve command:

At this time you have three options:

After using any of these methods 1-2-3 will retrieve a copy of the worksheet from the disk to the work area in RAM. The mode indicator reads WAIT during this time; when it reads READY, the worksheet is ready to use.

Note that the cell pointer is at the cell it was when you saved the worksheet. Also note that retrieving a worksheet reads a copy of the worksheet from the disk into RAM. The original worksheet file on the disk is unchanged.

An additional command that you may want to use is the File Directory command. This command tell 1-2-3 where to save and retrieve files. For example, if you're using 1-2-3 on a hard drive system and you have a floppy disk that contains worksheet files, you could place the diskette in drive A and issue the File Directory command. Then, type a: and press Enter. Then, if you issue the File Retrieve command, 1-2-3 shows the worksheets available on drive A. Also, if you create a new worksheet and save it, 1-2-3 will save it on drive A.

Changes made with the File Directory command are not permanent. You can change the directory that 1-2-3 initially points to with the Worksheet Global Default Directory. Type the name of the directory and press Enter. Then select the Update command from the menu. Now, when you start 1-2-3, it will look at the directory you entered.

Titles

The Worksheet Titles command lets you freeze a number of rows or columns on the screen. When a row or column is frozen, it appears on the screen at all times, and does not move off the screen as you move the cell pointer to another part of the worksheet. For example, if you're working with a database, you might want to freeze the row containing the column titles. In this way, no matter how far down the database you move, the column titles will remain on the screen.

The Worksheet Titles Horizontal command freezes all rows above the row containing the cell pointer, the Worksheet Titles Vertical command freezes all columns to the left of the column containing the cell pointer, while the Worksheet Titles Both freezes the rows above the row containing the cell pointer, and the columns to the left of the column containing the cell pointer. You can clear the effects of titles with the Worksheet Titles Clear command, which clears all title settings.

When you set a horizontal title, you should scroll the screen so that the row or rows you want to freeze are at the very top of the screen. Similarly, for vertical or both titles, scroll the screen so that the rows and columns are at the very top and left of the screen. The issue the appropriate titles command.

When titles are set, you can't move the cell pointer into the title rows or columns by using the cell pointer movement keys, but you can move into the title rows or columns with the F5 Goto key. Additionally, while pointing to a range during a command or formula editing, you can move the cell pointer into the title rows or columns.

Windows

The Worksheet Window command lets you split the screen into two parts known as windows. You can move the cell pointer in each window independently of the cell pointer in the other window. In this way, you can bring two widely separated parts of the worksheet into view at the same time.

To create windows, first place the cell pointer at the place to split the screen. Issue the Worksheet Window command. From the menu that appears, select either Vertical or Horizontal, depending which type of split you want. 1-2-3 splits the screen at the place where the cell pointer was when you issued the Worksheet Window command, so you can control the relative sizes of the two windows by placing the cell pointer carefully. 1-2-3 lets you create only two windows.

While working with windows, there is only one cell pointer, and it is in one window or another. To move the cell pointer from window to window, press the F6 (Window) key.

1-2-3 windows initially start out as synchronized, meaning that when a cursor movement causes one window to move, the other window moves too. You can use the Worksheet Window Unsync command to unsynchronize the windows, while the Worksheet Window Sync command restores the synchronization.

Inserting and Deleting Rows and Columns

The Worksheet Insert command allows you to insert new rows and columns in the worksheet. To use this command, select Worksheet Insert. The next menu allows you to select whether to insert rows or columns, so select the right choice for your situation. Then, 1-2-3 asks for the range to insert for. If you want to insert a single row or column, press the Enter key. Otherwise, you can use the arrow keys to expand the highlight to as many rows or columns as you want to insert, and then press Enter.

1-2-3 inserts rows and columns globally, meaning that the insertions take effect across the entire width or depth of the worksheet. Use care not to disturb data that you may not see on the screen.

The Worksheet Delete command allows you to remove rows and columns from the worksheet. It works much like the Worksheet Insert command. After you select Worksheet Delete, you select whether you want to delete rows or columns, then either press the enter key to delete a single row or column, or highlight an area of rows or columns to delete and press Enter.

1-2-3 deletes rows and columns globally, meaning that the deletions take effect across the entire width or depth of the worksheet. This means that you must use care not to disturb data that may not be on the screen.

Recalculation Settings

The Worksheet Global Recalculation command controls the method that 1-2-3 uses to recalculate the worksheet.

The Natural setting is 1-2-3's default setting. With Natural recalculation, 1-2-3 evaluates the cells that a cell references before it calculates the cell. This is the most common way to recalculate a worksheet.

The Columnwise setting means that 1-2-3 calculates the worksheet column by column, while the Rowwise setting calculates row by row. It is unusual to have a situation where these settings are needed; in fact, they will give incorrect results for most worksheets.

The Automatic calculation setting means that 1-2-3 recalculates the worksheet every time you make a change to a cell, meaning that you can always be sure that all your formulas are current. But since recalculating the worksheet takes time, you may want to set recalculation to Manual. This setting means that 1-2-3 does not automatically recalculate the worksheet whenever something changes, but does so only when you press the F9 Calc key. 1-2-3 displays the CALC indicator at the bottom right of the screen when the worksheet needs recalculation. It's common to set recalculation to Manual when working with large worksheets, or at any time when calculation times are more than a second or so.

Graphing

Now, to make the graph. The important thing to remember is that 1-2-3 graphs ranges of data, up to six ranges at once. In this case, we'd like to graph our Sales and Net Income figures. The Sales figures are in one range (we'll call it the A range of data), and the Net Income figures are in another range (the B range). This leaves four other ranges (the C, D, E, and F ranges) available for other uses. Additionally, 1-2-3 uses an X range of data for labels along the X (horizontal) axis of the graph.

Note that after we performed this command, 1-2-3 did not go back to READY mode as most menu commands do, but instead presented the graph command menu again. 1-2-3 does this because we need to make many menu command selections to create a graph. When finished creating the graph, we'll use the Quit command from this menu to return to READY mode.

The next step is to identify the X range of data. In this case, the X range is a series of labels that will appear along the horizontal axis of the graph--the words Q1, Q2, Q3, and Q4. If this worksheet was monthly, the X range would be the cells that contain that words January, February, March, and so forth.

Now, we'll identify the Sales figures as the first or A data range of the graph.

The second or B data range is the Net Income figures.

If you wish, at this time you can view the graph.

Now we'll add labels and titles to the graph. We'll first add legends to the graph, so that we can tell the set of Sales bars from the Net Income bars. The legends are samples of the bar color (for color displays) or crosshatching pattern (for monochrome displays) along with a word or two you supply to describe the range.

Now, enter titles for the graph. 1-2-3 lets you use four titles, the first title which appears at the very top of the graph, the second title which appears below the first title, and the X axis and Y axis titles, which appear along the horizontal and vertical axes of the graph. You can enter whatever you like for the titles.

Whenever you type legends or titles, take care to capitalize the text properly. If you see that you misspelled a word, issue the command again and you can edit the legend or title.

We can also format the numbers along the Y axis of the graph in a similar manner as formatting numbers in the worksheet.

At any time when you're working with the worksheet, function key F10 displays the current graph.

The advantage to integrated software like 1-2-3 is that if we make a change to the worksheet, the graph will change, too. Let's see this in action by adjusting a Sales figure:

Be sure to save the worksheet again to save the graph settings.

If you want to print the graph, you must first save the graph image in a picture file. This is a separate file from the worksheet file, which will be processed later on with the Printgraph program. Here's how:

You can use the same name for both the worksheet file and the picture file. 1-2-3 saves worksheets with the file extension .WKS (if you're using release 1 or 1A) or .WK1 (release 2), and graph picture files with the extension .PIC.

Remember, at any time you're in READY mode, you can press F10 (the Graph key) and 1-2-3 will display the current graph, reflecting all the changes you've made.

Saving a graph in a picture file is also useful because many desktop publishing and word processing programs can retrieve Lotus .pic files.

Printing

Now, let's prepare to print the worksheet. Even in this age of computers, telecommunications, and networks, the printed page is the most common method of communication, so it's important to master printing. When printing, the important steps are to highlight the range to print, issue various options, check the paper in your printer, and then give the Go command to actually start the printing.

Let's put a footer at the bottom of the page to help identify the printout.

Now we're about ready to give the Go command to start the printing. The important step right now is to turn your printer on and make sure that the paper is set to the top of the page. On most printers, this is so that the perforation separating one page from another is right at the top of the printhead. Check your printer manual for the proper procedure and position. Now, to print:

When the printer stops printing, don't touch anything. Note that 1-2-3 stopped printing right after the last line of the print range. It's important to remove the paper from the printer correctly. Don't manipulate the printer--by rolling the platen or pressing the line feed or form feed buttons. That's because 1-2-3 hasn't printed the footer line yet, and moving the paper will destroy 1-2-3's synchronization with the paper position. Instead, use 1-2-3's commands to advance the paper:

Besides the Page command, you can type L (for the Line command) to advance the paper one line at a time. After the paper is through advancing and 1-2-3 displays the MENU indicator, we can go on.

That's it for printing. If you resave the worksheet now (using the same procedure as after creating the graph), 1-2-3 will remember the print settings, and you won't have to enter them again unless you want to change them.

Saving the Worksheet Again

We've made some changes to the worksheet since we last saved it. What would happen if there was a power failure right now? When the computer shuts off, the RAM memory goes blank, and our worksheet is lost.

That's not exactly the case, as we do have a copy of the worksheet saved on the disk. When the power comes back on, we can start the computer and retrieve the worksheet from the disk. But the worksheet will look just as it did when we saved it the last time, so changes made to the worksheet since then are lost. It's a good idea to save your worksheet periodically--not so much because power failures are common, but for many other reasons. Every 15 minutes or so is common for saving.

At this time, 1-2-3 displays the current name of the worksheet. If we want to use the same name, all we need to do is press the Enter key.

At this time, 1-2-3 displays a menu with the two items Cancel and Replace. The Cancel command cancels the effect of the File Save command as if we had never started the command. The Replace command erases the existing worksheet from the disk and saves the current version in its place.

Normally, you'll select Replace from this menu, because when you save a worksheet for the second and subsequent times, it's usually because you've made changes that make the old copy of the worksheet on the disk obsolete. There's no need to keep it, so you select Replace to erase it and save the most current version.

There may be times, however, when you want to retain the old version of the worksheet. For example, you may make experimental changes to the worksheet and you're not sure whether they're correct. You might want to give this worksheet a new name, thus retaining the old version with the original name and the new experimental version with a new, different name. When you want to do this, just type a new name when 1-2-3 asks for the worksheet file name.

In our example, the old worksheet is obsolete, so we'll use the Replace command.

Looking Back

What can we do with this worksheet? One thing we can do is to type new Sales figures into cells B2, C2, D2, and E2, and watch the worksheet recalculate. At any time, remember, you can press F10 (the Graph key) to view the graph with the new sales figures.

You can also type new figures into the Fixed Costs cells B5, C5, D5, and E5. Can we type a new number into a Variable Costs cell, say B3? The answer is we can, but we probably don't want to. If we did, we'd replace the formula for Variable Costs with a plain numeric entry. It would look just the same as the result of the formula, but what would happen if we then changed the Sales figure? Since the Variable Costs is a numeric entry, it won't change and recalculate as the formula did.

While this is fine for a start, suppose one of the goals of building this worksheet was to investigate what happens to Net Income when the Variable Cost Factor changes. Right now, this worksheet presumes that Variable Costs are 30 percent of Sales, as defined by the formula in cell B3 and the others in row three.

Suppose, though, that we'd like to see what happens to Net Income if the Variable Cost Factor increases to 40 percent of Sales. With the way our worksheet is designed, we'll have to edit the formula in cell B3 and use the Copy command to copy it across the rest of the row. Here's how to do it:

This fixes the formula for cell B3, but we'll have to copy it to make the other cells in the row have the same formula:

That's it. The worksheet now reflects the figures if Variable Costs are 40 percent of sales. But changing this figure took considerable effort--editing the formula and then copying it. Additionally, when you print the worksheet, there's no indication of the factor used to compute the variable costs. These two defects of the worksheet illustrate the fact that you should separate assumptions from formulas and data. The Variable Cost Factor, after all, is an assumption that's subject to change. Isolating it on the worksheet makes it easier to change it, and if you print the section of the worksheet that contains your assumptions, you'll have documentation of them as well. Let's modify the worksheet to incorporate this advice:

Percentages, remember, are really fractions, so you should usually enter them as such. But this number doesn't look much like a percentage until formatted with the percent format:

Note that the Percent format does three things: adjusts the number of decimal places as most formats do, places a percent sign after the number, and multiplies the number by 100 for display only. The true value of the cell, as shown in the cell indicator at the top left of the screen, is still .3. The multiplication is for show only.

Now, let's adjust the formula for Variable Costs in cell B3 to refer to this cell:

This completes the formula for cell B3. Now, use the Copy command to apply the formula to cells C3, D3, and E3:

Move to cell B3

What happened? Do you sense an error? You should, as the values in cells C3, D3, and E3 are zero, definitely not what we expected. What happened is we copied a formula that can't be copied, at least not in its present form. (It's important to realize that the figures are in error not because they're zeros, but because they're wrong. A value of zero is many times the correct or desired answer to a calculation. In this case, it would be nice if the zeros were correct, but they're not.)

Remember that 1-2-3, when copying formulas, copies the formula's relative meaning. The formula in cell B3 really means the cell eight rows below times the cell one row above. If you copy this meaning to cell C3, the meaning is still the cell eight rows below times the cell one row above, which translates to C11*C2! You can verify this by moving the cell pointer to C3 and looking at the cell indicator at the top left of the screen. An empty cell has the numeric value of zero when used in a formula like this, and that's why the three cells show zeros.

Notice, however, that 1-2-3 did not indicate an error--no ERROR indicator, no beeps, just numbers that didn't look right. That's because 1-2-3 didn't make the error, we did. The Copy command worked just as it is supposed to, it's we who misused it in this situation. The zeros tipped us off to the error, but in other situations, non-zero numbers could have showed up, and the error would not have been as easy to spot. The moral is that 1-2-3 is happy to perform any action (as long as it knows how) that you direct it to, but you bear the responsibility for making sure your commands and formulas are correct.

To correct this situation, we could copy the 40 percent number in cell B11 across the row to cells C11, D11, and E11. But this is not much better than before. We'd still have to change four cells to change the Variable Cost Factor.

The solution to this problem is to use an absolute reference to cell B11 in the formula in cell B3. An absolute reference is one that doesn't change when copied. If we type a formula with an absolute reference to B11 in cell B3, it will still refer to B11 no matter how far we copy it. Here's how to do it. We'll start by retyping the formula in B3:

This cell we're pointing at (B11) is the cell that should be referred to with an absolute reference:

Note that on the edit line, the formula reads +$B$11. The dollar signs indicate an absolute reference. Now, continue with the formula as regular:

This completes the formula for cell B3. Now, use the Copy command as regular to apply the formula to cells C3, D3, and E3:

Now, to see the effects of the absolute reference, move to cells C3, D3, and E3. Note that the absolute reference ($B$11) is the same in each of the cells. The reference to cell B2, not being absolute, changes to C2, D2, and E2 as regular. To see the effects of these formulas, move to cell B11 and enter different Variable Cost Factors. Remember to enter them as percentages. Type either .25 or 25% for 25 percent. If you typed 25 and pressed Enter, 1-2-3 would show it as 2500% and would multiply 25 by the sales figure ($100.00) to produce 2,500.00 for the Variable Costs.

Other formulas in this worksheet can benefit from this treatment. Fixed Costs are liable to change, so they should be isolated. And, of course, Taxes are always changing, so these should be isolated as well. We'll isolate the Fixed Costs now:

This type of formula, consisting of the plus sign and a cell address (whether absolute or relative), "echoes" the cell's value at the new cell. That's all we need here--there's no computation involved.

Now, type various levels of Fixed Costs in cell B12 to see the worksheet recalculate. Why did we use the absolute reference? If we hadn't, the Copy command would have produced +C12, +D12, and +E12 as the resulting formulas, and the problem is similar to the Variable Costs problem.

Tax Problems and the @if Function

This worksheet contains an error that we haven't seen before. Let's see if we can spot it by entering this data to the worksheet:

Why did 1-2-3 report such a tax payment? You should realize by now that typing the word Taxes next to a formula doesn't teach 1-2-3 the entire federal tax code. Instead, 1-2-3 performs the calculation that the formulas indicate, and in this case, our Tax formula simply takes the Operations Income and multiplies by .45. We need to write a better Tax formula.

How, then, should we calculate Taxes? Let's say, in our simplified world, that if the company earns a profit in a quarter (meaning that cell B6 for the first quarter is greater than zero) the Tax is .45 times the profit. If the Operations Income is less than zero, the Tax is zero for this quarter.

To implement this type of calculation, we need a mechanism that lets 1-2-3 make a decision based on some condition or value and then perform one of two actions, depending on that value. The @if function accomplishes 1-2-3 that. The @if function looks like this: @if(condition,true-value,false-value). Condition is a statement about something in the worksheet that is true or false. If it is true, the value of the @if function is true-value, which might be a number, cell address, or formula. If condition is false, the value of the @if function is false-value, which again might be a number, cell address, or formula. In our case, we need to test the Operations Income (cell B6). Let's go ahead and type the new formula for Taxes in cell B7:

The completed formula should look like this: @IF(B6<=0,0,.45*B6). Now, we need to copy this formula across to the rest of the tax calculations as follows.

Now, you should see zeros for the Tax whenever the Operations Income is zero or negative. For the Operations Incomes that are greater than zero, the Tax will be the same value as before.

Data Tables and What-If Analysis

Data tables are one of the most overlooked features of 1-2-3. Sometimes called what-if tables, data tables let you vary one or two inputs of a model and collect results in a table. In this example, we'll vary the Variable Cost Factor over a range of likely values and collect the Total Net Income that results from each factor.

The key to using data tables is to first create your model so that the variable you want to vary (the Variable Cost Factor in this example) is isolated in a single cell for a one-way table, or two cells for a two-way table. (This is an additional reason why we isolated the Variable Cost Factor in cell B11.) Then create a list of input values, probably using the Data Fill command. Identify the values you want to accumulate and type their formulas across the top of the data table. Issue the Data Table command, and wait for 1-2-3 to calculate the table. Here's how to do it in this case:

First, we'll use the Data Fill command to create a table of input Variable Cost Factors. Our table with start with zero percent, step by five percent, and stop at fifty percent.

That's it for creating the numbers. You may want to format them for appearance as follows:

Now, in cell B22, write a formula that represents the value you're interested in.

Since this formula is not really a "formula" in the usual sense, but instead represents a column title for data, format it with the Text format. This format displays the text of a formula, rather than the calculated value of the formula.

Now, we'll issue the Data Table 1 command.

Now, wait a moment until 1-2-3 calculates the table. (The mode indicator at the top right of the screen displays WAIT while 1-2-3 is calculating the data table. When the mode indicator reads READY mode, the table's finished.)

Now that 1-2-3's finished calculating the numbers, let's format them for appearance:

Here's what happened: 1-2-3 took the first value from the leftmost column of the table (0%) and entered it to the input cell (B11). Then 1-2-3 calculated the worksheet. After calculation, 1-2-3 evaluated the formula in cell B22 (which is simply an "echo" of cell F8, the Total Net Income) and placed its value in cell B23, right next to the input value. Then 1-2-3 moved down a row, plugged the next value (5%) into the input cell, and repeated the whole process.

The "business" meaning of this table is that if Variable Costs are zero percent of sales, the Total Net Income is $172. If variable costs are 5 percent of sales, the Total Net Income is $161.

The data tables are static, that is, they don't recalculate along with the rest of the worksheet (large tables can take several minutes to calculate). This means that if you change anything in the worksheet such as the Sales or Fixed Costs, you'll have to issue the Data Table 1 command again, or press the F8 (Table) key, which recomputes the data table.

The First Worksheet

Screen View of the worksheet:

A B C D E F G
1 Item Q1 Q2 Q3 Q4 Total Average
2 Sales $100.00 $87.00 $100.00 $125.78 $412.78 $103.20
3 Variable Costs 30.00 26.10 30.00 37.73 123.83 30.96
4 Contribution 70.00 60.90 70.00 88.05 288.95 72.24
5 Fixed Costs 25.00 25.00 25.00 25.00 100.00 25.00
6 Operations Income 45.00 35.90 45.00 63.05 188.95 47.24
7 Taxes 20.25 16.16 20.25 28.37 85.03 21.26
8 Net Income 24.75 19.75 24.75 34.68 103.92 25.98
9 =======================================================================

Description of Formulas Used Column B Formula

Sales is a number that you input. 100
Variable Costs are 30 percent of Sales. +30%*B2
Contribution is Sales less Variable Costs. +B2-B3
Fixed Costs are $25.00. 25
Operations Income is Contribution less Fixed Costs. +B4-B5
Taxes are 45 percent of Operations Income. +45%*B6
Net Income is Operations Income less Taxes. +B6-B7

Formula in Cell F2 (Total Sales): +@SUM(B2..E2)
Formula in Cell G2 (Average Sales): +@AVG(B2..E2)

Formulas View of the Worksheet (produced by the Cambridge Spreadsheet Analyst):

-A- -B- -C- -D- -E- -F- -G-
1 'Item "Q1 "Q2 "Q3 "Q4 "Total "Average
2 'Sales [$100.00] [$87.00] [$100.00] [$125.78] @SUM(B2..E2) @AVG(B2..E2)
3 'Variable Costs 0.3*B2 0.3*C2 0.3*D2 0.3*E2 @SUM(B3..E3) @AVG(B3..E3)
4 'Contribution +B2-B3 +C2-C3 +D2-D3 +E2-E3 @SUM(B4..E4) @AVG(B4..E4)
5 'Fixed Costs [25.00] [25.00] [25.00] [25.00] @SUM(B5..E5) @AVG(B5..E5)
6 'Operations Income +B4-B5 +C4-C5 +D4-D5 +E4-E5 @SUM(B6..E6) @AVG(B6..E6)
7 'Taxes 0.45*B6 0.45*C6 0.45*D6 0.45*E6 @SUM(B7..E7) @AVG(B7..E7)
8 'Net Income +B6-B7 +C6-C7 +D6-D7 +E6-E7 @SUM(B8..E8) @AVG(B8..E8)

The First Graph

Graph Settings:

Type: Bar Titles: First ACME CONSOLIDATED INDUSTRIES
Second Sales & Profit Analysis
X: B1..E1 X axis Quarter
A: B2..E2 Y axis (Millions)
B: B8..E8
C: Y scale: X scale:
D: Scaling Automatic Automatic
E: Lower
F: Upper
Format (C0) (G)
Grid: None Color: No Indicator Yes Yes

Legend: Format: Data labels: Skip: 1
A Sales Both
B Profit Both
C Both
D Both
E Both
F Both

The Enhanced Worksheet

Screen View:

A B C D E F G
1 Item Q1 Q2 Q3 Q4 Total Average
2 Sales $100.00 $87.00 $100.00 $125.78 $412.78 $103.20
3 Variable Costs 50.00 43.50 50.00 62.89 206.39 51.60
4 Contribution 50.00 43.50 50.00 62.89 206.39 51.60
5 Fixed Costs 60.00 60.00 60.00 60.00 240.00 60.00
6 Operations Income (10.00) (16.50) (10.00) 2.89 (33.61) (8.40)
7 Taxes 0.00 0.00 0.00 1.30 1.30 0.33
8 Net Income (10.00) (16.50) (10.00) 1.59 (34.91) (8.73)
9 =======================================================================
10
11 Var. Cost Rate: 50%
12 Fixed Costs: $60.00
13 Tax Rate: 45%

Formulas View:

-A- -B- -C- -D- -E-
1 'Item "Q1 "Q2 "Q3 "Q4
2 'Sales [$100.00] [$87.00] [$100.00] [$125.78]
3 'Variable Costs +$B$11*B2 +$B$11*C2 +$B$11*D2 +$B$11*E2
4 'Contribution +B2-B3 +C2-C3 +D2-D3 +E2-E3
5 'Fixed Costs +$B$12 +$B$12 +$B$12 +$B$12
6 'Operations Income +B4-B5 +C4-C5 +D4-D5 +E4-E5
7 'Taxes @IF(B6<=0,0,$B$13*B6) @IF(C6<=0,0,$B$13*C6) @IF(D6<=0,0,$B$13*D6) @IF(E6<=0,0,$B$13*E6)
8 'Net Income +B6-B7 +C6-C7 +D6-D7 +E6-E7
9 \= \= \= \= \=
10
11 'Var. Cost Rate: [50%]
12 'Fixed Costs: [$60.00]
13 'Tax Rate: [45%]