Robert W. Weeks bob.weeks@cox.net, http://members.cox.net/bob.weeks

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
X Range: B1..E1
A Range: B2..E2
B Range: B8..E8

Options Settings:
A Legend: Sales
B Legend: Profit
First Title: Acme Consolidated Industries
Second Title: Sales & Profit Analysis
X-Axis Title: Quarter
Y-Axis Title: Billions
Y-Axis Format: Currency, zero decimal places
Set to B&W
Data Labels A Range: B2..E2
Data Labels B Range: B8..E8

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-
1 'Item "Q1
2 'Sales [$100.00]
3 'Variable Costs +$B$11*B2
4 'Contribution +B2-B3
5 'Fixed Costs +$B$12
6 'Operations Income +B4-B5
7 'Taxes @IF(B6<=0,0,$B$13*B6)
8 'Net Income +B6-B7
9 \= \=
10
11 'Var. Cost Rate: [50%]
12 'Fixed Costs: [$60.00]
13 'Tax Rate: [45%]

Using Absolute References

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:

Move to cell B3

This is the cell we want to change, so we need to move the cell pointer there first.

Press F2

F2 is the Edit key, which lets us change the contents of a cell. Now we're in EDIT mode, as indicated by the mode indicator at the top right of the screen.

Press Left arrow four times

Moves the cursor to the 3 that we need to delete.

Press Del

The Delete key deletes the character the cursor is at. Note that the characters to the right have moved to the left to close the space.

Type 4

Inserts a 4 at the position of the cursor. That's the normal way that editing in 1-2-3 works--new characters are inserted in front of the character the cursor is on.

Press Enter

Completes the edit. At this time, 1-2-3 actually makes the replacement in cell B3. If we had wanted to exit EDIT mode without making the change, we would have pressed Escape.

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:

Move to cell B3

This is the cell to copy from, and it's most convenient to start the /Copy command at the cell you're copying from.

Type /C

Starts the /Copy command.

Press Enter

Since the one cell we want to copy from (B3) is already proposed as the range to copy from, all we had to do was press Enter to accept.

Press Right arrow

Moves the cell pointer to C3, the cell to start copying at.

Type .

Anchors the cell pointer at C3.

Press Right arrow twice

Extends the cell pointer to cover the range C3..E3, the range to copy to.

Press Enter

Completes the /Copy command.

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:

Move to cell A11

Prepare to enter an identifying label.

Type Var. Cost Rate: and press Right arrow

Enters the label and moves to the right.

Type .3 and press Enter

Enters .3 (30 percent) to the cell.

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:

Type /RFP0 and press Enter

Issues the /Range Format Percent 0 decimal places command.

Press Enter

Applies the format to just the proposed cell, cell B11.

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:

Move to cell B3

This is the cell where the formula should go.

Type +

A plus sign starts the formula.

Press Down arrow eight times

Moves the cell pointer to cell B11 and writes the address in the formula.

Type *

The multiplication operator. Note the cell pointer bounces back to the starting cell, B3.

Press Up arrow once

Moves the cell pointer to cell B2 and writes the address in the formula.

Press Enter

Completes the formula.

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

The starting point for the copy.

Type /C

Starts the /Copy command.

Press Enter

Since the one cell we want to copy from (B3) is already proposed as the range to copy from, all we had to do was press Enter to accept.

Press Right arrow

Moves the cell pointer to C3, the cell to start copying at.

Type .

Anchors the cell pointer at C3.

Press Right arrow twice

Extends the cell pointer to cover the range C3..E3, the range to copy to.

Press Enter

Completes the /Copy command.

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:

Move to cell B3

This is the cell that will contain the formula.

Type +

A plus sign starts the formula.

Press Down arrow eight times

Moves the cell pointer to cell B11 and writes the address in the formula.

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

Press F4

F4 is the ABS (absolute reference) key, and makes the reference to cell B11 absolute.

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:

Type *

The multiplication operator.

Press Up arrow once

Moves the cell pointer to cell B2 and writes the address in the formula. This cell will not be an absolute reference, as we want this reference to adjust to C2, D2, and E2 as we copy it.

Press Enter

Completes the formula.

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

Move to cell B3

The starting point for the copy.

Type /C

Starts the /Copy command.

Press Enter

Since the one cell we want to copy from (B3) is already proposed as the range to copy from, all we had to do was press Enter to accept.

Press Right arrow

Moves the cell pointer to C3, the cell to start copying at.

Type .

Anchors the cell pointer at C3.

Press Right arrow twice

Extends the cell pointer to cover the range C3..E3, the range to copy to.

Press Enter

Completes the /Copy command.

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:

Move to cell A12

Prepare to enter a label.

Type Fixed Costs and press Right arrow

Enters the label and moves to cell B12.

Type 20 and press Enter

Enters 20 to the cell as the starting value for Fixed Costs.

Type /RFC and press Enter twice

Formats the cell using the /Range Format Currency command, accepts the proposed answer of two decimal places, and applies the format to just cell B12.

Move to cell B5

Prepare to enter the formula for Fixed Costs.

Type +

Again, a plus sign begins the formula.

Type $b$12 and press Enter

Enters the formula by typing the cell address along with the dollar signs rather than using cell pointing.

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.

Make sure the cell pointer's at cell B5

The starting point for the copy.

Type /C

Starts the /Copy command.

Press Enter

Since the one cell we want to copy from (B5) is already proposed as the range to copy from, all we had to do was press Enter to accept.

Press Right arrow

Moves the cell pointer to C5, the cell to start copying at.

Type .

Anchors the cell pointer at C5.

Press Right arrow twice

Extends the cell pointer to cover the range C5..E5, the range to copy to.

Press Enter

Completes the /Copy command.

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:

Move to cell B11 and type either .5 or 50% and press Enter

Makes the Variable Cost Rate fifty percent.

Move to cell B12, type 60 and press Enter

Makes the Fixed Costs $60.00

Now, look at rows six through eight of the worksheet. Do you see anything unusual? Row 6, the Operations Income, contains negative numbers (in the Currency and Comma formats, 1-2-3 shows negative numbers in parentheses). That's not good for the company, but it doesn't necessarily indicate that anything is wrong with our worksheet model. The problem lies in the tax calculation. For example, the Operations Income for the first quarter (cell B6) is negative $10.00. We'd expect to pay no taxes on that income, but our worksheet reports a tax payment of negative $4.50. That doesn't make sense--to have a negative tax payment.

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:

Move to cell B7

This is where we need to type the formula for taxes.

Type +@if(

Starts the formula. We'll use cell pointing to enter the cell addresses.

Press Up arrow

The cell pointer moves to cell B6, and 1-2-3 types B6 in the formula.

Type <=0,

Completes the condition, B6<=0.

Type 0,

The true-value of the function. In other words, if cell B6 is less than or equal to zero (meaning that the Operations Income is zero or less than zero), the Tax is zero.

Type .45* and press Up arrow

Enters the formula .45*B6 for the false-value of the function.

Type ) and press Enter

Completes the formula.

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.

Move to cell B7.

This is the cell to copy from, so we'll start here.

Type /C and press Enter

Starts the Copy command and selects the range B7..B7 as the range to copy from.

Press Right arrow and type .

Moves to cell C7, the top left corner of the range to copy to, and anchors the cell pointer there.

Press Right arrow twice and press Enter

Highlights the range C7..E7 as the range to copy to and completes the Copy command.

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.

Move to cell A23

Prepare for the /Data Fill command.

Type /DF

Start the /Data Fill command.

Press .

Anchor one corner of the range to fill data with at cell A23.

Press Down arrow until the range expands to include cell A33, and then press Enter

Specifies the range A23..A33 as the range to fill with data.

Type 0 and press Enter

This is the starting value (zero percent) for the fill.

Type .05 and press Enter

This is the step value (five percent), the increment for each cell.

Type .5 and press Enter

This is the stop value (50 percent) for the fill.

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

Move to cell A23

Prepare to start the /Range Format Command.

Type /RFP0 and press Enter

Issues the /Range Format command, using the Percent format with zero decimal places.

Press End and Down arrow

Extends the highlight to the end of the column and highlights the range A23..A33. Note the use of the End key again to speed cell pointer and highlight movement.

Press Enter

Completes the /Range Format command.

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

Move to cell B22

This is where the formula goes.

Type +F8 and press Enter

The formula. Since cell F8 already contains the value we want to accumulate (the Total Net Income), this formula duplicates the value in F8 in cell B22.

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.

Move to cell B22

Prepare for the /Range Format command.

Type /RFT

Issues the /Range Format Text command.

Press Enter

Completes the /Range Format Text command, applying the text format to just the proposed cell, B22.

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

Move to cell A23.

Prepare for the command.

Type /DT1

Issues the /Data Table 1 command.

Press .

Anchors the cell pointer at cell A23.

Press Down arrow and End and Down arrow

Extends the highlight to cell A33.

Press Right arrow

Extends the highlight to cell B33, highlighting the range A23..B33.

Press Enter

Completes the table range entry.

Type B11 and press Enter, or point to cell B11 with the cursor keys and press Enter

Indicates the input cell for the data table and completes the 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:

Move to cell B23

Get in position to format.

Type /RF,0 and press Enter

Use the Comma format with zero decimal places.

Press End and Down arrow

Extends the range highlight to cell B33, the end of the column.

Press Enter

Completes the range highlighting and the Range Format command.

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 "mechanical" interpretation is that if cell B11 (the input cell) is 0%, then cell F8 is $172. Similarly, if cell B11 (the input cell) is 5%, then cell F8 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.

More Data Tables

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.

To use a data table, your model must have one or two input cells where you type the input to the model. For example, when figuring how much car you can afford, you might construct a model that takes a loan amount, interest rate, and loan term and then calculates the monthly payment. In this case, the input cell is the cell where you enter the loan amount. The interest rate and term are fixed; you vary the loan amount until you come across a monthly payment that suits your budget. This is a one-way data table, as only one input variable changes. If you kept the loan amount constant and varied both the interest rate and loan term, then two cells change and you'd have a two-way data table. 1-2-3 can calculate both one-way and two-way data tables.

The key to using data tables is to first create your model so that the variables you want to vary are isolated in a single cell for a one-way table, or two cells for a two-way table. 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 two example of data tables and how they're used.

One-Way Data Tables

You're ready to buy a new car. You have a budget planned that allows for a monthly payment. How much can you spend on the car?

If you have Lotus 1-2-3, you're aware that the @PMT (payment) function calculates loan payments given a loan amount, interest rate, and term. You might make a worksheet like this:

A B C
1 Loan Amount $10,000
2 Interest Rate 12.00%
3 Term 5
4
5 Monthly Payment $222.44

This worksheet works well, but to find the loan amount that corresponds to your desired monthly payment, you'll have to type values into cell B1 and note the resulting payment, until you come across the loan amount that produces the right payment. Wouldn't it be nice to have 1-2-3 find the answer for you?

The Data Table command can help with this problem. With Data Table, you can create a column of input values (various loan amounts in this case) and have 1-2-3 calculate the worksheet for these values, recording variables of interest in a table (the monthly payment, as well as the total cost of the load). It works like this.

First, create the sample worksheet shown above:

Move to cell A1

Prepare to enter the labels.

Type Loan Amount and press Down arrow

Enters the label in cell A1 and moves to the cell below.

Type Interest Rate and press Down arrow

Enters the label to cell A2 and moves to the cell below.

Type Term and press Down arrow twice

Enters the label in cell A3 and moves to cell A5.

Type Monthly Payment and press Enter

Enters the label in cell A5.

Type /WCS15 and press Enter

Issues the /Worksheet Column Set-Width command and makes the column 15 characters wide.

Now, enter and format the input data:

Move to cell B1

Prepare to enter the loan amount.

Type 10000 and press Enter

Enters the number to cell B1.

Type /RFC2 and press Enter twice

Formats the cell as Currency, two decimal places.

Press Down arrow

Move to cell B2.

Type .12 and press Enter, or type 12% and press Enter

Enters 12 percent to cell B2. When you type a number followed by the percent sign, 1-2-3 divides it by 100.

Type /RFP2 and press Enter twice

Issues the /Range Format Percent two decimal places command and formats just this cell.

Press Down arrow

Moves to cell B3.

Type 5 and press Enter

Enters the number 5 to cell B3.

Press Down arrow twice

Moves to cell B5.

Type @pmt(b1,b2/12,b3*12) and press Enter

Enters the payment function to this cell. Note that we divided the annual interest rate by 12 to get the monthly interest rate, and multiplied the number of years in the term to get the number of months in the term.

Type /RFC2 and press Enter twice

Formats the cell as Currency, two decimal places.

Type /WCS12 and press Enter

Issues the /Worksheet Column Set-Width command and makes the column 12 characters wide.

Now, create a column of loan amounts that range across the amounts you're interested in. Here's an example table:

A
7 Loan Amount
8 $10,000
9 12,000
10 14,000
11 16,000
12 18,000
13 20,000

Here's a step-by-step procedure for creating this column of figures:

Move to cell A7, type Loan Amount and press Enter

This is a title for the top of the column.

Move to cell A8

Prepare for the /Data Fill command.

Type /DF

Start the /Data Fill command.

Press .

Anchor one corner of the range to fill data with at cell A8.

Press Down arrow until the range expands to include cell A13, and then press Enter

Specifies the range A8..A13 as the range to fill with data, and completes the command.

Type 10000 and press Enter

This is the starting value for the fill.

Type 2000 and press Enter

This is the step value, the increment for each value.

Type 20000 and press Enter

This is the stop value for the fill.

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

Move to cell A8

Prepare to start the /Range Format Command.

Type /RFC0 and press Enter

Issues the /Range Format command, using the Currency format with zero decimal places.

Press Enter

Completes the /Range Format command for cell A8.

Move to cell A9

Prepare for another /Range Format command.

Type /RF,0 and press Enter

Issues the /Range Format command, using the Comma format with zero decimal places.

Press End and Down arrow

Extends the highlight to the end of the column and highlights the range A9..A13.

Press Enter

Completes the /Range Format command.

Now, in cells B7 and B8, write formulas that represent the values you're interested in.

Move to cell B7

This is where the formula goes.

Type +B5 and press Enter

The formula. Since cell B5 already contains the value we want to accumulate, this formula duplicates the value in B5 in cell B7.

Move to cell C7

This is where the next formula goes.

Type +B5*B3*12 and press Enter

The formula. Since this value, the total cost of the loan, is not computer anywhere else in the worksheet, we just compute it here.

Since these two formulas are not really "formulas" in the usual sense, but instead represent column titles for data, we'll format them with the Text format. This format displays the text of a formula, rather than the calculated value of the formula.

Move to cell B7

Prepare for the /Range Format command.

Type /RFT

Issues the /Range Format Text command.

Press Right arrow and press Enter

Highlights the range B7..C7 and completes the command.

Now, we'll issue the /Data table 1 command.

Move to cell A7.

Prepare for the command.

Type /DT1

Issues the /Data Table 1 command.

Press .

Anchors the cell pointer at cell A7.

Press End and Down arrow

Extends the highlight to cell A13.

Press Right arrow twice

Extends the highlight to cell C13, highlighting the range A7..C13.

Press Enter

Completes the table range entry.

Type B1 and press Enter, or point to cell B1 with the cursor keys and press Enter

Indicates the entry cell for the data table and completes the command.

When 1-2-3 completes the calculations, the table should look like this (format the range B8..C8 with the Currency format, zero decimal places, and the range B9..C13 with the Comma format, zero decimal places if you wish):

A B C
7 Loan Amount +B5 +B5*B3*12
8 $10,000 $222.44 $13,347
9 12,000 266.93 16,016
10 14,000 311.42 18,685
11 16,000 355.91 21,355
12 18,000 400.40 24,024
13 20,000 444.89 26,693

Here's what happened: 1-2-3 took the first value from the leftmost column of the table (10000) and entered it to the input cell (B1). Then 1-2-3 calculated the worksheet. After calculation, 1-2-3 evaluated the formulas in cells B7 and C7, and placed the values in cells B8 and C8. Then 1-2-3 moved down a row, plugged the next value (12000) into the input cell, and repeated the whole process.

The "business" meaning of this table is that if you get a loan for $10,000, the monthly payment will be $222.44, and the total cost of the loan is $13,347. If the loan is for $16,000, the monthly payment is $355.91, and the total cost of the loan is $21,355.

The "mechanical" interpretation is that if cell B1 (the input cell) is $10,000, then cell B5 is $222.44, and the value of the formula +B5*B3*12 is $13,347. Similarly, f cell B1 (the input cell) is $16,000, then cell B5 is $355.91, and the value of the formula +B5*B3*12 is $21,355.

The result? A table showing payments and total loan costs for loans from $10,000 to $20,000. The next step? Make a graph, using A8..A13 for the X range, and B8..B13 for the A range. Find your budgeted monthly payment along the Y axis of the graph, and look to the X axis for the car you can afford.

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 the interest rate, you'll have to issue the Data Table command again, or press the F8 (Table) key, which recomputes the data table.

Two-Way Data Tables

Now, suppose you've settled in on the purchase price of the car. You'd like to investigate the effects of different interest rates along with the term of the loan.

Lotus 1-2-3 can tackle this type of problem with a data table, but unlike the last table, this problem requires varying two input variables at the same time--the loan term and the interest rate--requiring the use of the /Data Table 2 command.

Just as with any time you want to use a /Data Table command, you need to create a model that starts out with the variables you want to vary, and returns the value you're interested in accumulating. In this case, we're going to vary the interest rate and loan term. These two variables are isolated in cells B2 and B3:

A B
1 Loan Amount $12,000
2 Interest Rate 5.90%
3 Term 5
4
5 Monthly Payment $231.44

The formula in cell B5 is @PMT(B1,B2/12,B3*12).

Now, we need to create a table of input values. For a two-way data table, arrange the input values for the first input cell (the interest rate) in a column at the left edge of the table, and the input values for the second input cell (the term of the loan) in a row at the top of the table. We'll use the /Data Fill command as follows:

Move to cell B26

Prepare for the /Data Fill command.

Type /DF

Start the /Data Fill command.

Press Backspace

Cancels the old data fill range and returns the cell pointer to B26.

Press .

Anchor one corner of the range to fill data with at cell B26.

Press Down arrow until the range expands to include cell B36, and then press Enter

Specifies the range B26..B36 as the range to fill with data, and completes the command.

Type .05 and press Enter

This is the starting value for the fill, one percent. Remember, we're working with percentages.

Type .01 and press Enter

This is the step value, the increment for each value. This is one percentage point.

Type .15 and press Enter

This is the stop value for the fill, fifteen percent.

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

Move to cell B26

Prepare to start the /Range Format Command.

Type /RFP0 and press Enter

Issues the /Range Format command, using the Percent format with zero decimal places.

Press End and Down arrow

Highlights the range to format. Note the use of the End key to quickly highlight to the end of the column.

Press Enter

Completes the /Range Format command.

Now, create the row of terms. It's probably easiest to just type them in: 2 in Cell C25, 3 in D25, 4 in E25, and 5 in F25. If you were filling in a lot of numbers, of course, use the /Data Fill command again.

Now, at the top left of the table, we need to enter the formula for the value we want to collect. Two-way data table can accumulate only one set of values, unlike the one-way data table example above, which accumulated two sets of values:

Move to cell B25

That's the top left corner of the data table, where the accumulating formula goes.

Type +B5 and press Enter

Enters the formula to accumulate the monthly payment, which is already calculated in cell B5.

Again, it's best to format these accumulator formulas with the text format:

Make sure the cell pointer is at B25

B25 is the cell to format.

Type /RFT and press Enter

Issues the /Range Format command to format the cell with the text format and completes the command.

Now you're ready to issue the /Data Table 2 command. Remember, the input values for input cell 1 are at the left of the table, and the input values for input cell 2 are at the top of the table.

Move to cell B25

This is the top left corner of the data table range.

Type /DTR

This command, /Data Table Reset, removes any existing data table definitions you may have entered.

Type /DT2

Starts the /Data Table 2 command.

Type .

Anchors the cell pointer at cell B25.

Press End and Down arrow

Extends the highlight to cell B36.

Press Right arrow four times and press Enter

Completes highlighting the range B25..F36, the data table range.

Point to cell B2 and press Enter, or type B2 and press Enter

Indicates cell B2 (the interest rate cell) as input cell 1.

Point to cell B3 and press Enter, or type B3 and press Enter

Indicates cell B3 (the term cell) as input cell 2. This also completes the /Data Table 2 command, and at this time 1-2-3 calculates the table.

When finished, you may want to format the table to make the numbers more attractive:

Move to cell C26

The top left corner of the range to format.

Type /RF,2 and press Enter

Issues the /Range Format Comma two decimal places command.

Press End and Down arrow, and End and Right arrow

Uses the End key in conjunction with the arrow keys to quickly highlight the range C26..F36.

Press Enter

Completes the /Range Format command.

The completed table should look like this (add the column and row titles if you wish):

A B C D E F
23
24 Term of Loan
25 +B5 2 3 4 5
26 I R 5.0% 526.46 359.65 276.35 226.45
27 n a 6.0% 531.85 365.06 281.82 231.99
28 t t 7.0% 537.27 370.53 287.35 237.61
29 e e 8.0% 542.73 376.04 292.96 243.32
30 r 9.0% 548.22 381.60 298.62 249.10
31 e 10.0% 553.74 387.21 304.35 254.96
32 s 11.0% 559.29 392.86 310.15 260.91
33 t 12.0% 564.88 398.57 316.01 266.93
34 13.0% 570.50 404.33 321.93 273.04
35 14.0% 576.15 410.13 327.92 279.22
36 15.0% 581.84 415.98 333.97 285.48

The "business" meaning of this table is that if you get a loan for two years at five percent interest, the monthly payment will be $526.46. If the loan is for four years at ten percent, the monthly payment is $304.35.

The "mechanical" interpretation is that if cell B2 (input cell 1) is 5% and cell B3 (input cell 2) is 2, then cell B5 (the accumulated cell) is $526.46.

Be careful when creating two-way data tables. If the table is large, with many input values, 1-2-3 has to recalculate the worksheet many times. If the worksheet takes a substantial amount of time to recalculate once, the data table could take hours to calculate.

Printing

Printing, of course, is an important skill to master when using 1-2-3, as nearly all activity you do is geared towards producing the final, printed output. There are a few basic things you have to know to produce any output at all on the printer, and then several advanced techniques that can produce more useful and attractive reports.

The basic steps to produce printed output are to first create your worksheet and format it attractively with the /Range Format and /Range Label commands. Then move to the print menu, specify the range of the worksheet to print, enter any options you wish, and then finally issue the Go command to print the worksheet.

Lotus 1-2-3 allows you to direct printed output to either the printer (the usual case) or a disk file. You can send printed output to a disk file so that you can edit the report with a word processor, for example. When printing to disk, 1-2-3 creates a file with the name you specify and the extension .prn that contains an image of what 1-2-3 would send to the printer--the formatted worksheet. This method of printing to the disk also provides a method of getting data out of 1-2-3 for use in other programs. Remember, 1-2-3 stores its files in its own proprietary, binary format that other programs can't use directly. For example, you can't load a 1-2-3 worksheet file into your word processor and expect something useful, but you can load the printed-to-disk file and use it. But since 1-2-3 is such a popular program, many programs can read 1-2-3 worksheet files (and even graph picture files) through a special command or conversion utility.

When you print, 1-2-3 needs to know what to print. The /Print Range command allows you to specify the range you want to print. You can print the entire worksheet, or just a portion of it. When you select the /Print (Printer or File) Range command, use regular range techniques to specify the print range. You can anchor the cell pointer and highlight the range to print, type the range coordinates, or use a range name. Range names are very useful when printing, as you may have several distinct tables that you want to print separately. Since 1-2-3 remembers only a single print range, you have to change the print range every time you want to print a different table. To make this easier, assign range names such as table1, table2, and table3 to the various tables in the worksheet. Then, when you want to print one of the ranges, either type the range name or use the F3 (Name) key to get a menu of range names. Range names work well with macros to control printing several ranges with one command.

Here's a sample script for a printing session:

Type /P

Starts the Print command.

Type P or F

Issues the Printer command to send the printed output to the printer, or the File command to direct output to a disk file. If you select File, 1-2-3 asks for the name of the print file. 1-2-3 adds .prn to the name you supply.

Type R

Issues the Range command to indicate the range to print.

Specify the range to print.

This command starts in free-floating (unanchored) mode, so you can move to one corner of the range to print, press period to anchor, and highlight the range and press Enter. Or, you can type either range coordinates (A1..G50, for example) or a range name (table1, for example) and press Enter.

Headers and Footers

At this time, we've satisfied the minimum requirements for printing--supplying the range to print. If you wish, you could select the Go command to start the printing, but let's put a footer at the bottom of the page to help identify the printout. Headers and footers--a single line of text that appears at the top or bottom of each page--help identify the printout and make it more attractive. Enter headers and footers through the Options command on the Print menu:

Type O

Displays the Options menu.

Type F

Issues the Footer command.

Type @|Budget Report|Page #

The footer text. @ prints today's date, # prints the page number, and | separates the footer into left aligned, centered, and right aligned zones.

Press Enter

Completes the Footer command.

Type Q

Issues the Quit command from the Options menu, returning to the main Print menu.

To enter a header, type H for Header. You can have both headers and footers at the same time.

1-2-3 provides two special characters you can use in headers and footers. The @ character, when in a header or footer, is replaced with the current date. (That's a good illustration of why it's important to enter the correct date and time when you start the computer, or to make sure your automatic clock is operating and set accurately. 1-2-3 will print whatever date you tell or don't tell the computer.) The # character is replaced with the current page number. Also, the | character divides a header or footer into a left aligned area printed at the left margin, a centered area, and a right aligned area printed flush with the right margin. For example, the footer entered above produces this at the bottom of each page:

10-Aug-85 Budget Report Page 1

The page number, of course, would change from page to page.

If you wanted a footer to look like this:

Page 1

type

||Page #

for the footer. There's nothing to print at the left margin or at the center of the line, so there's nothing between the two || symbols. To make a footer like this:

- 1 -

type

|- # -

Use exactly the same techniques for headers.

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. Printers vary, so check your printer manual for the proper procedure and position. Now, to print:

Type G

Issues the Go command to start printing. While 1-2-3 is printing, it displays the WAIT indicator, and you have to wait until the printing is finished and the MENU indicator displays before you can type anything else.

When the printer stops printing, don't touch anything. Note, though, 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:

Type P

Issues the Page command to advance the paper to the top of the next page. As the paper advances, 1-2-3 will print the footer at the proper place.

You can also advance the paper with the Line command, in which case 1-2-3 advances the paper one line for each press of L. This is useful when you want to print another range of the worksheet immediately after the first range. In this case, press L a few times to advance the paper a few lines, then issue the Range command, specify the new range to print, and issue the Go command.

After the paper is through advancing and 1-2-3 displays the MENU indicator, we can go on.

Type Q

Issues the Quit command from the printer menu to return to READY mode.

That's it for printing. If you save the worksheet now, 1-2-3 will remember the print settings, and you won't have to enter them again unless you want to change them.

The Page and Line commands advance the paper in the printer to the top of the next page or a single line. These commands make use of 1-2-3's knowledge of how many lines were printed on the last page, and they update 1-2-3's internal line and page counters. If you use these commands to advance the paper in the printer, you won't lose coordination between the printer and 1-2-3. Otherwise, if you use the printer's controls or platen knob to advance the paper, 1-2-3 won't sense this, and the next time you print, the page breaks will appear in the wrong places, unless you use the Align command to coordinate 1-2-3 and the printer. Using the Page and Line commands is the best way to advance the paper in the printer.

Other Print Commands

The Align command coordinates 1-2-3 and the printer as to where the top the paper is. 1-2-3 keeps a record of the number of lines it prints and uses this count to make page breaks. Therefore, 1-2-3 needs to know where the top of the paper in the printer is. The procedure to follow is to adjust your printer so that the paper is in the proper position for printing the top line on a page, and then issue the Align command. Then, 1-2-3 and the printer are coordinated.

The Align command also resets the page number counter, so the next page you print after issuing the Align command will be page number one. This is important, as 1-2-3 numbers pages continuously even though multiple Go commands. If you print a worksheet that requires five pages, then print the worksheet again, the first page of the second printout will be page six. Issue the Align command to make the next page printed be page number one. This aspect of the Align command is important, of course, only if you're using headers or footers with the # symbol to produce page numbers.

The Margins command on the Options menu lets you change the margins of the printed page. You can adjust the top, bottom, left, and right margins. One use for these commands is when you have a wide printer that uses 15 inch wide paper. In this case, you can usually set the right margin to 132 to use the full width of the paper, although the maximum setting depends on your particular printer. Also, if you set your printer to compressed print in order to print more characters on each line, you will need to adjust the right margin. Adjusting the left margin to a large value helps center worksheets that aren't very wide. 1-2-3's default value for the top and bottom margins is two lines, so you may want to changes these settings to a large value. Remember, the Options menu is "sticky," so after making margin changes, issue the Quit command to return to the main printer menu.

1-2-3 always leaves two blank lines after the header and two blank lines before the footer. There is no way to change this. The top and bottom margins settings determine the number of blank lines before the header and after the footer.

The Border command lets you specify a range of rows or columns to print on every page. A common use for this command is when you have a long list of columnar data. Without using the Border command, pages after the first page won't have column titles at the top of the page. Borders, however, print at the top of every page (for row borders) or the left side of each page (for column borders).

To use border rows or columns, select Borders from the Print Options menu. Select Row or Column, and then using range highlighting techniques, specify the rows or columns to include as a border.

If you use the Border command, don't include the border rows or columns in the print range. If you do, the border rows or columns will print twice on the first page. That's because they're printed once because they're a border, and then again because they're in the print range.

The Setup command on the Print Options menu lets you send a setup string to your printer, which controls special printer features such as print size, line spacing, and so forth. A setup string is composed of a series of ASCII characters, expressed as a backwards slash (\) followed by a three-digit decimal number between zero and 255. To enter a setup string, issue the Setup command, and type the setup string.

For example, the setup string to make an Epson printer print in compressed mode (17 characters per inch) is ASCII character 15. To enter this as a 1-2-3 setup string, enter \015. The setup strings below work for printers compatible with the IBM Graphics Printer or Proprinter, which means Epson, many Okidata, Star, and other printers.

Printer Codes From the Printer Manual

1-2-3 Setup String

Pica (10 characters per inch) 27 80

\027\080

Elite (12 characters per inch) 27 77

\027\077

Compressed (17 characters-inch) 15

\015

Double Strike 27 71

\027\071

Emphasized 27 69

\027\069

Eight lines-inch 27 48

\027\048

Some printer manuals give setup strings as characters. For example, the printer manual might specify the string to produce 12 pitch Elite printing as Esc M. In this case, type the escape character (the "Esc" in the manual) as \027, and then type M, so that the setup string is "\027M" That shows that you can enter setup strings in two ways: with the ASCII characters (such as the "M"), or with the ASCII codes for the characters, which you enter as a three-digit number preceded with the backwards slash.

Remember, if you change the print pitch, you will want to change the right margin to let more characters print of a line, and if you change to eight lines per inch, you'll want to change the page length setting. For example, at eight lines per inch, the 11 inch paper now becomes 88 lines long. When printing at 12 characters per inch, 8 inch wide paper becomes 96 characters wide, when it used to be 80 characters wide at ten characters per inch.

The Page-Length command lets you specify how long your paper is. You set this adjustment in number of lines per page. Normally, for 11 inch paper, you leave this at 66 (11 inches times six lines per inch for most printers). Set the page length to the length of the paper, not the number of lines that you want printed on each page. Use the Margin command to set the top and bottom margins for extra white space at the top and bottom of the page. If you set your printer to operate at eight lines per inch, then make the page length 88 (11 inches times eight lines per inch).

The Other command lets you make changes to the way 1-2-3 displays information on the printer. When you select Other from the Options menu, 1-2-3 displays a menu with these choices: The As-Displayed command tells 1-2-3 to print the information in the same way as it appears on the screen; in other words, 1-2-3 displays the value of a cell instead of its formula. The Cell-Formulas command, on the other hand, prints a columnar listing with each cell on a separate line. This listing shows each cell's formula, not its value. Additionally, 1-2-3 shows cell attributes such as the label prefix, display format, and protection status. Using the Cell-Formulas command is useful for debugging or correcting errors in your worksheet formulas.

The Formatted and Unformatted commands determine whether 1-2-3 observes page length, header, and footer settings while printing. The Formatted command, which is the initial state, prints the worksheet observing these commands, while the Unformatted command does not. The Unformatted command effectively sets an infinite page length. You may want to use the Unformatted command when you're printing a worksheet to disk and intend to print it using a word processing program. In this manner, you can let the word processing program determine where page breaks should go, as well as use the word processor's header, footer, footnote, or other capabilities. Otherwise, you'll probably have to use the word processor to delete 1-2-3's headings and margins.

When printing to disk, also set the left margin to zero. This will let your word processing program control the margins. Otherwise, there will be a column of spaces at the left of the table that you may have to remove.

The Clear command provides a simple method of clearing or resetting print settings. If you use the Clear command, 1-2-3 displays a menu with these options: The All command resets all print settings to the original default values. The Range command clears the print range. The Borders command clears the print borders settings, while the Format command resets the print format to As-Displayed.

Date and Time Functions in Lotus 1-2-3

Date and time functions in Lotus 1-2-3 allow you to manipulate dates and time efficiently, and to perform date and time arithmetic.

To work with dates in 1-2-3, you could type in labels such as "1/15/87", and 1-2-3 would display the date. The problem is that 1-2-3 does not recognize this cell as a date, but as just digits and slashes. Using date functions lets us enter dates in a manner that lets 1-2-3 recognize the value as a date and perform useful work with it.

1-2-3 represents dates as serial numbers, with January 1, 1900 being day number one. All other dates are numbered sequentially. By representing dates in this manner, you can add a number to a date (if payment is due in 45 days, on what date is payment due?), subtract two dates (how many days are between two dates?), fill a range with dates, or graph or sort information chronologically.

To enter a date to 1-2-3, use the @date function. This function takes the three elements of the calendar we are familiar with (the month, day, and year), and calculates that date's serial number. Position the cell pointer to the cell to contain the date, and if the date is January 15, 1987, type

@date(87,1,15)

The format of the @date function, then, is @DATE(year,month,date). You must type the arguments in this order. For the year, type just the last two digits (87, not 1987). This function takes the year, month, and date and converts the date to an integer serial number. In this case, 1-2-3 displays 31792 in the cell.

Since it's not easy to recognize that 31792 means January 15, 1987, 1-2-3 provides a number of date formats. These date formats take a date in serial number form and display it in a more familiar form, as the table below illustrates.

A
1 31792 Unformatted
2 15-Jan-87 Date Format 1 (DD-MMM-YY)
3 15-Jan Date Format 2 (DD-MMM)
4 Jan-87 Date Format 3 (MMM-YY)
5 01/15/87 Date Format 4 (MM/DD/YY)
6 01/15 Date Format 5 (MM/DD)

To format a cell that contains a date, use the /Range Format Date command, select date format one through five, and specify the range to apply the format to.

The cell still contains 31792, so even though the dates appear like dates, you can still use them like integers for arithmetic, sorting, and so forth. In fact, if you had known that January 15, 1987 was 31792 days after January 1, 1900, you could have typed that number into the cell and formatted it as a date.

1-2-3 also works with time. A time serial number is a fraction of one, which represents the passage of the day. A time value of .25 means 6:00 AM, .5 means noon, .75 means 6:00 PM, and so forth. You can combine dates and times by adding a fractional part of a day to the date's serial number. For example, the value 31792.5 means noon on January 15, 1987. 31792.75 means 6:00 pm on the same day.

Enter times to the worksheet with the @time function, which looks like @time(hour,minute,second). Enter the hour using the 24-hour time format where 1:00 PM is hour 13. For example, if it's 8:45 and 38 seconds in the morning, enter @time(8,45,38). For that same time in the evening, enter @time(20,45,38).

You can format these serial numbers with time formats to display them as hours, minutes, and seconds, as the table below shows.

A
8 0.75645333 Unformatted
9 06:09:18 PM Time Format 1
10 06:09 PM Time Format 2
11 18:09:18 Time Format 3
12 18:09 Time Format 4

To use these formats, issue the /Range Format Date Time command, select the format you want, and specify the range to format.

When you format a cell with a time format, 1-2-3 ignores the day part of the number and formats just the time. If the number in cell A1 in the above example had been 31792.75645333, representing 6:09:18 PM on January 15, 1987, the cells A9 through A12 would still show the same results.

1-2-3 can also enter the date and time automatically through the @now function. This function asks your computer (actually DOS, the operating system) for the date and time. This date is correct only if you enter the correct date and time when you start your computer, or if your computer has a device that automatically enters the date and time, is correct only if that device is operating and set properly.

The @now function enters a fractional number, with the fraction representing the time. This can cause a problem when subtracting dates to determine the number of days between two dates. For example, suppose you entered an invoice on January 10, 1987 using the function @date(87,1,10). This produces the date serial number 31787. In another cell you type the @now function. Suppose it's 6:00 PM on January 15, 1987, so that cell contains 31792.75. If you subtract the cells, the result is 5.75. Since we're usually interested in the number of whole days between two dates, you might round this off to 6, implying that there are six days between January 10 and 15. That's not the case.

The solution is to use the formula @int(@now) to enter today's date. The @int function discards the fractional part of the day, returning the "pure" date serial number alone, and the computations will be correct.

Another problem with the @now function is that every time you recalculate the worksheet, 1-2-3 updates the @now function to the new date and time. Often this is the effect you want, but if you're using the @now function as a shortcut for permanently entering the current date or time, you don't want the function to recalculate. In this case, we want to "numberize" the function, which means we convert the function @now to the number 31792 (for example). Then the date or time will not change, because it's no longer a formula but a numeric entry.

There are two methods for changing a formula to a numeric entry. One way is to edit the cell by pressing F2, then calculate the cell with the F9 Calc key. At this time, the edit line changes from the formula to the number the formula represents. When you press the Enter key to complete the edit, the value of the cell becomes that number.

A second way to convert a formula to a number is to use the /Range Values command. This command works like the /Copy command, asking for the range to copy from and then the range to copy to. But instead of copying formulas as /Copy does, the /Range Value command copies the values of the formulas. If the range to copy to is the same as the range to copy from, 1-2-3 replaces the formulas in those cells with their values. This command, obviously, is best when you have more than one cell to "numberize."

Other date functions include:

@DAY(x)

Returns the day of the month from a date. The result is a number from 1 to 12.

@MONTH(x)

Returns the month from a date. The result is a number from 1 to 31, depending on the month.

@YEAR(x)

Returns the year from a date. 1-2-3 returns values from 0 through 99 for years in the 20th century, and 100 through 199 for dates in the next century. Example: 87 is the year 1987, and 110 is the year 2010.

@HOUR(x)

Returns the hour from a time serial number. The result is a number between 0 and 23. Hour zero is midnight, 23 is 11:00 PM.

@MINUTE(x)

Returns the minute of the hour from a time serial number. The result is a number between 0 and 59.

SECOND(x)

Returns the second of the current minute from a time serial number. The result is a number between 0 and 59.

Example:

This example worksheet contains two separate parts--one which performs date calculations on invoices, and another which performs time calculations on time card data. Since this worksheet uses the @now function, we need to know that this view of the worksheet represents how it looked on September 18, 1987.

Screen View of the worksheet:

A B C D E F G
1 Invoice Invoice Payment Days Until
2 Number Date Terms Due Date Age Payment
3 1001 09/03/87 30 10/03/87 15 15
4 1002 09/08/87 45 10/23/87 10 35
5 1003 09/13/87 35 10/18/87 5 30
6
7
8 Clock Clock Hours
9 Date In At Out At Worked Hours Minutes Seconds
10 09/01/87 08:00:03 AM 04:00:05 PM 8.00 8 0 2
11 09/02/87 08:03:45 AM 03:57:00 PM 7.89 7 53 15
12 09/03/87 07:47:45 AM 04:03:13 PM 8.26 8 15 28

Formulas View:

-A- -B- -C- -D- -E- -F- -G-
3 [1001] @DATE(87,9,3 [30] +B3+C3 @INT( +D3-@INT(
) @NOW) @NOW)
-B3
4 [1002] @DATE(87,9,8 [45] +B4+C4 @INT( +D4-@INT(
) @NOW) @NOW)
-B4
5 [1003] @DATE(87,9,1 [35] +B5+C5 @INT( +D5-@INT(
3) @NOW) @NOW)
-B5

10 @DATE(87, @TIME(8,0,3) @TIME(16,0,5 (C10-B10) @HOUR @MINUTE(C @SECO
9,1) ) *24 (C10- 10-B10) ND(C1
B10) 0-B10
)
11 @DATE(87, @TIME(8,3,45 @TIME(15,57, (C11-B11) @HOUR @MINUTE(C @SECO
9,2) ) 0) *24 (C11- 11-B11) ND(C1
B11) 1-B11
)
12 @DATE(87, @TIME(7,47,4 @TIME(16,3,1 (C12-B12) @HOUR @MINUTE(C @SECO
9,3) 5) 3) *24 (C12- 12-B12) ND(C1
B12) 2-B12
)

Discussion:

The first part of the worksheet (rows one through five) performs date calculations. Column A is an invoice number, and column B is the date of the invoice. The dates, of course, are entered with the @date function, so the entry in cell B3 is @date(87,9,3) for September 3, 1987. The cells in column B are formatted with date format 4.

Column C contains the credit terms for this invoice, so invoice 1001 is due in 30 days from its date, and invoice 1001 is due in 45 days from its date.

Column D calculates the due date for the invoice, calculated by adding the number of days the customer has to pay to the invoice date. In cell D3, for example, the formula is +B3+C3. Don't format these cells as dates--if you did, the value of cell D3 (the number 30) expressed as a date would be 01/20/00 (that is January 30, 1900). This cell represents a number of days, not a date serial number.

Column E calculates the age of the invoice. In cell E3 the formula is @int(@now)-B3, which takes today's date and subtracts the date of the invoice. Since the @now function changes each day, these ages will change each day. Note that we use the @int function so that the @now function returns an integer for the date, rather than a fractional date and time. Also, when subtracting dates, subtract the earlier date from the later date. In this way, you'll get positive numbers. If you subtract the earlier date from the later date, you'll get a negative number.

Column F calculates how many days we have to wait for payment (assuming, of course that the customer pays on time). In F3, the formula is +D3-@int(now). This formula takes the due date of the invoice calculated in D3 and subtracts today's date.

The next part of the worksheet (rows 8 through 12) work with time, calculating the elapsed time between the time an employee clocks in in the morning and leaves in the afternoon. Column A is the date of work.

Column B is the time that the employee started to work, as indicated by the time clock. These times are entered with the @time function, so the formula in cell B10 is @time(8,0,3) for 8:00:03 AM. Column C contains the time the employee left work, also entered with the @time function.

In column D we want to calculate number of hours worked. If we subtract the column C values from the column B values, we'll get the fraction of the day worked. That's because the column B values, being about 8:00 AM, are numbers around .333, since 8:00 AM is one-third through the day. The column C values are around .666, so subtracting .666 from .333 leaves .333, which is the fraction of the day the employee worked. If we multiply this by 24 hours per day, we get the number of hours worked. The formula in cell D10, then, is (C10-B10)*24. This number is in hours plus decimal fractions of an hour, so you could multiply these values by the employee's hourly wage to produce the daily pay.

In columns E, F, and G, we calculate the time worked in hours, minutes, and seconds instead of hours and decimal fractions of hours. We can do this by applying the @hour, @minute, and @second functions to the time difference c10-B10.

Using Lotus 1-2-3 Functions

The If Function

The If function's job is to examine something on the worksheet, and then perform one of two actions. It provides a method for decision-making on the worksheet.

The If function looks like this: @IF(x,truevalue,falsevalue). This functions evaluates x, which is a logical assertion that is either true or false. If it is true, then the value of the cell is truevalue (which can be a formula or reference). If x is false, then the value of the cell is falsevalue.

One useful application of the @IF function is to guard against illegal mathematical operations, such as division by zero. Suppose you have the formula A1/B1 in cell C1. The problem is that sometimes the value of cell B1 may be zero, in which case 1-2-3 displays ERR in cell C1. We want to have 1-2-3 display NA in cell C1 when B1 is zero instead of the ugly ERR. Here's a formula for cell A3 that will do the trick: @IF(B1=0,@NA,A1/A2)

In this case the condition is B1=0. If this is true, meaning the value in cell B1 is zero, the value of the @IF function is @NA, which displays NA in cell C1. Otherwise, the value of the function is the formula A1/B1. Another formula that works is @IF(B1<>0,A1/B1,@NA)

A B C D E F
1 5 0 ERR
2 5 0 NA
3
4
5 Shipping Total
6 Subtotal Charges Amount
7 --------- --------- ---------
8 50.45 2.52 52.97
9 143.43 0.00 143.43
10 100.00 0.00 100.00
11 99.99 5.00 104.99


-A- -B- -C-
1 [5] [0] +A1/B1
2 [5] [0] @IF(B2=0,@NA,A2/B2)
3
4
5 "Shipping "Total
6 "Subtotal "Charges "Amount
7 "--------- "--------- "---------
8 [50.45] @IF(A8>=100,0,0.05*A8) +A8+B8
9 [143.43] @IF(A9>=100,0,0.05*A9) +A9+B9
10 [100.00] @IF(A10>=100,0,0.05*A10) +A10+B10
11 [99.99] @IF(A11>=100,0,0.05*A11) +A11+B11

Other Examples of the If Function

A B C D
1 5 2 2.5
2 5 0 ERR
3 5 0 NA
4 5 0 Division By Zero
5
6 Shipping Total
7 Subtotal Charges Amount Notes
8 --------- --------- --------- ---------
9 50.45 5.05 55.50 Apply Shipping Charge
10 143.43 0.00 143.43 Free Shipping
11 100.00 0.00 100.00 Free Shipping
12 99.99 10.00 109.99 Apply Shipping Charge

-A- -B- -C- -D-
1 [5] [2] +A1/B1
2 [5] [0] +A2/B2
3 [5] [0] @IF(B3=0,@N
A,A2/B2)
4 [5] [0] @IF(B4=0,"D
ivision By
Zero",A3/B3
)
5
6 "Shipping "Total
7 "Subtotal "Charges "Amount "Notes
8 "--------- "--------- "--------- "--------
-
9 [50.45] @IF(A9>=100,0,0.1* +A9+B9 @IF(A9>=1
A9) 00,"Free
Shipping"
,"Apply S
hipping C
harge")
10 [143.43] @IF(A10>=100,0,0.1 +A10+B10 @IF(A10>=
*A10) 100,"Free
Shipping
","Apply
Shipping
Charge")
11 [100.00] @IF(A11>=100,0,0.1 +A11+B11 @IF(A11>=
*A11) 100,"Free
Shipping
","Apply
Shipping
Charge")
12 [99.99] @IF(A12>=100,0,0.1 +A12+B12 @IF(A12>=
*A12) 100,"Free
Shipping
","Apply
Shipping
Charge")

Other Lotus 1-2-3 Logical Functions

With Lotus 1-2-3 logical functions, true has the value of one, and false has the value of zero.

@NA

Gives a cell the value NA, which means "Not Available".

@ERR

Gives a cell the error value ERR.

@FALSE

Gives a cell the value False.

@TRUE

Gives a cell the value True.

@ISNA(x)

Returns TRUE if the value of x is @NA; returns FALSE otherwise.

@ISERR(x)

Returns TRUE if the value of x is @ERR; returns FALSE otherwise.

Round

The Round function, which looks like this: @ROUND(x,digits), rounds x to digits number of decimal places.

Don't confuse the Round function with cell formatting produced through the /Range Format or /Worksheet Global Format commands. Formats control just the appearance of the cell; the value of the cell (the number you originally entered or the result of a formula) is unchanged. That's an important point, as sometimes cell formatting can cause erroneous results, as in this example, which does not work without the Round function:

A B C D E
1 Cost per Total Total
2 Area Sq. Feet Sq. Foot Cost Cost
3 --------- --------- --------- --------- ---------
4 Floor 1,546.513 0.29 448.49 448.49
5 Ceiling 1,546.513 0.37 572.21 572.21
6 Walls 6,240.446 0.26 1,622.52 1,622.52
7 --------- ---------
8 Totals 2,643.21 2,643.22

-A- -B- -C- -D- -E-
1 "Cost per "Total "Total
2 'Area "Sq. Feet "Sq. Foot "Cost "Cost
3 '--------- "--------- "--------- "--------- "---------
4 'Floor [1,546.513] [0.29] +B4*C4 @ROUND(B4*C4,2)
5 'Ceiling [1,546.513] [0.37] +B5*C5 @ROUND(B5*C5,2)
6 'Walls [6,240.446] [0.26] +B6*C6 @ROUND(B6*C6,2)
7 "--------- "---------
8 'Totals @SUM(D4..D6) @SUM(E4..E6)

Lookup Tables in 1-2-3

Lookup tables in 1-2-3, implemented by the functions @vlookup (vertical lookup) and @hlookup (horizontal lookup), are two of the most useful special functions in 1-2-3. Lookup tables let you reference data from a table instead of typing it repeatedly.

Let's take a simple example. You need to keep track of income earned by partners in an accounting firm. Each partner has a unique identifying number and billing rate. You want to create a table like this:

A B C D
1 Partner Hours Rate Total
2 1 2.0 30.00 60.00
3 4 3.5 40.00 140.00
4 3 2.0 25.00 50.00
5 5 4.0 45.00 180.00

(The formula in cell D2 is B2*C2.) Without lookup tables, you'd have to type each partner's hourly rate as you enter their hours.

You could, however, create a table of partner numbers and their hourly rates, which might look like this:

A B
21 Partner Rate
22 1 30.00
23 2 35.00
24 3 25.00
25 4 40.00
26 5 45.00

It's just a list of identifying numbers and hourly rates. With this table and the @vlookup function, 1-2-3 can fill in the values for column C automatically. The @vlookup function takes a value (the partner number) and moves down the leftmost column, called the key column, of a table (the table of ID numbers and rates) until it finds the value it's looking for (the partner number). Then it returns a value from a column that you specify (the hourly rate column).

The actual function we need in cell C2 is @VLOOKUP(A2,$A$22..$B$26,1).

The first argument to the function (the cell A2, where we typed the partner ID number) is the key value, the value we're looking for in the table. The second argument (the range $A$22..$B$26) tells 1-2-3 where the lookup table is. This range is just all the rows and columns in the table, not including any titles at the top. The third argument (the number 1) tells 1-2-3 which column of data to return a value from. The leftmost column is column zero, so the hourly rate is in column one.

You often need to use absolute references to the lookup table (that's the dollar signs before the column letters and row numbers) so that you can copy the formula from cell C2 to cells below it. Without the dollar signs, the reference to the table would be relative, and when copied to cell C3, the table range would become A23..B27, which is not correct. With the absolute reference, the table range remains A22..B26 when copied.

In more detail, the @vlookup function takes the key value and moves down the leftmost column of the table range until it finds a value in the table that's greater than the key value. That's important, as we'll see in the next table. Then 1-2-3 moves up one row, and moves to the right the number of columns specified in the function. That cell is the value of the function. In 1-2-3, this value must be numeric (or a formula that evaluates to a number), but in Symphony, the cell could contain a label, letting you fetch names and other character data from the lookup table.

If the key value is smaller than the first value in the table, the function returns ERR, the error constant. If the key value is larger than the last value in the range, the @lookup function stops there. Thus, entering zero into cell A2 results in an error, and entering 100 to that cell returns the hourly rate for partner five.

For lookup tables to work properly, the values in the key column must be sorted in ascending order.

The @hlookup function is identical to @vlookup, except that @hlookup moves across the top of the table looking for a match, then moves down a number of rows to return a value. Most users create their tables in a vertical fashion and use @vlookup.

A more involved example--the 1985 tax table. The original table, as printed in the tax manuals, looks like this:

Schedule X--Single Taxpayers

If Income But Not Tax Plus Of
is Over Over Is Amount Over
0 2390 0.00 0% 0
2390 3540 0.00 11% 2390
2540 4580 126.50 12% 3540
4580 6760 251.30 14% 4580
6760 8850 556.50 15% 6760
8850 11240 870.00 16% 8850
11240 13430 1,252.40 18% 11240
13430 15610 1,646.60 20% 13430
15610 18940 2,082.60 23% 15610
18940 24460 2,848.50 26% 18940
24460 19970 4,283.70 30% 24460
29970 35490 5,936.70 34% 29970
35490 43190 7,816.50 38% 35490
43190 57550 10,739.50 42% 43190
59550 85160 16,770.70 48% 57550
Over 85160 30,009.10 50% 85130

We want to create a worksheet like this:

A B C D E
1 Income Tax Percent Over This Total
2 0 0.00 0% 0.00 0.00
3
4 Up To Tax Percent Over This
5 0 0.00 0% 0
6 2390 0.00 11% 2390
7 2540 126.50 12% 3540
8 4580 251.30 14% 4580
9 6760 556.50 15% 6760
10 8850 870.00 16% 8850
11 11240 1,252.40 18% 11240
12 13430 1,646.60 20% 13430
13 15610 2,082.60 23% 15610
14 18940 2,848.50 26% 18940
15 24460 4,283.70 30% 24460
16 29970 5,936.70 34% 29970
17 35490 7,816.50 38% 35490
18 43190 10,739.50 42% 43190
19 59550 16,770.70 48% 57550
20 85160 30,009.10 50% 85130

-A- -B- -C- -D- -E-
1 "Income "Tax "Percent "Over This "Total
2 [0] @VLOOKUP(A2, @VLOOKUP(A2 @VLOOKUP(A2 +B2+(A2-D2)*
A5..D20,1) ,A5..D20,2) ,A5..D20,3) C2
3
4 "Up To "Tax "Percent "Over This
5 [0] [0.00] [0%] [0]
6 [2390] [0.00] [11%] [2390]
7 [2540] [126.50] [12%] [3540]
8 [4580] [251.30] [14%] [4580]
9 [6760] [556.50] [15%] [6760]
10 [8850] [870.00] [16%] [8850]
11 [11240] [1,252.40] [18%] [11240]
12 [13430] [1,646.60] [20%] [13430]
13 [15610] [2,082.60] [23%] [15610]
14 [18940] [2,848.50] [26%] [18940]
15 [24460] [4,283.70] [30%] [24460]
16 [29970] [5,936.70] [34%] [29970]
17 [35490] [7,816.50] [38%] [35490]
18 [43190] [10,739.50] [42%] [43190]
19 [59550] [16,770.70] [48%] [57550]
20 [85160] [30,009.10] [50%] [85130]

The lookup table is slightly different from the tax table due to the way the @vlookup function works. Say the income is $10,000. From the tax table, we see that for incomes between $8,850 and $11,240, the tax is $870.00 plus 16% of the income over $8,850.00. We need to structure the lookup table so that 1-2-3 bumps into the $11,240 figure, at which time it moves one row up, and then finds the figures for an income of $10,000. The table above does that.

If you give the range name "table" to cells A5..D20, the formula in cell B2 is @VLOOKUP(A2,TABLE,1), the formula in cell C2 is @VLOOKUP(A2,TABLE,2), and the formula in cell D2 is @VLOOKUP(A2,TABLE,3). The first @vlookup returns the fixed tax, the second returns the marginal tax rate, and the third returns the amount to compute the marginal tax on. The three functions are identical, except that they reference different columns of the lookup table.

Finally, they're combined together by the formula in cell E2, which calculates the total tax: +B2+(A2-D2)*C2

String Functions

A B C D E F
1 Whole First Last Lower Upper Proper
2 Name Name Name Case Case Case
3 -------------- -------- -------- -------- -------- --------
4 Johnny Carson Johnny Carson carson CARSON Carson
5 Michael Jackson Michael Jackson jackson JACKSON Jackson
6 Nancy Reagan Nancy Reagan reagan REAGAN Reagan
7 Ronald Reagan Ronald Reagan reagan REAGAN Reagan
8 Robert Redford Robert Redford redford REDFORD Redford
9 Elizabeth Taylor Elizabeth Taylor taylor TAYLOR Taylor
10 Henry Kissinger Henry Kissinger kissinger KISSINGER Kissinge
11 Margaret Thatcher Margaret Thatcher thatcher THATCHER Thatcher
12 Woody Allen Woody Allen allen ALLEN Allen
13 Zsa Zsa Gabor Zsa Zsa Gabor zsa gabor ZSA GABOR Zsa Gabo
14 Leonard Nimoy Leonard Nimoy nimoy NIMOY Nimoy

21 Johnny Carson Johnny Carson
22 Michael Jackson Michael Jackson
23 Nancy Reagan Nancy Reagan
24 Ronald Reagan Ronald Reagan
25 Robert Redford Robert Redford
26 Elizabeth Taylor Elizabeth Taylor
27 Henry Kissinger Henry Kissinger
28 Margaret Thatcher Margaret Thatcher
29 Woody Allen Woody Allen
30 Zsa Zsa Gabor Zsa Zsa Gabor
31 Leonard Nimoy Leonard Nimoy

-A- -B- -C- -D- -E- -F-
1 'Whole 'First 'Last 'Lower 'Upper 'Proper
2 'Name 'Name 'Name 'Case 'Case 'Case
3 '-------------- '-------- '-------- '-------- '-------- '-------
-
4 'Johnny Carson @LEFT(A4,@FIN @RIGHT(A4,@LE @LOWER(C4) @UPPER(D4) @PROPER(
D(" ",A4,1)) NGTH(A4)-@FIN E4)
D(" ",A4,1)-1
)

-A- -B- -C- -D- -E- -F-
21 'Johnny Carson @MID(A21,0,@F @MID(A21,@FIN
IND(" ",A21,1 D(" ",A21,1)+
)) 1,@LENGTH(A21
)-@FIND(" ",A
21,1))

File Combine

1-2-3 has the ability to read one worksheet file into another. This is helpful when tracking departmental performance, for example. Instead of creating one large worksheet which contains data from all the departments, create smaller worksheets for each department. Then, whenever you want the big picture, combine the smaller worksheets into a larger one.

In our exercise, we'll take the three departmental worksheets depta, deptb, and deptc and combine them into the worksheet alldept. The alldept worksheet contains just the net income figures from each department and adds the three net incomes to get the total, company-wide income.

About /File Combine

/File Combine lets you read all or part of a worksheet into the current worksheet on the screen. The key to working with /File Combine is to position the cursor properly, as the top left corner of the incoming worksheet (either cell A1 if you're combining the entire worksheet or the top left corner of a named range if you're combining part of a worksheet) goes in the cell the cell pointer is in when you issue /File Combine.

When combining data, 1-2-3 lets you either copy the incoming cells directly, or add or subtract them from cells on the current worksheet that are overlaid by incoming cells. The choice you make is important to the success of the operation.

If you select to copy the incoming cells, 1-2-3 copies the incoming cell's formula. Often, this is the choice not to make, as you're really interested in the value of the cell, not the formula. The danger results from the fact that the formula for the incoming cell, say B2-B3, probably does not have the same meaning in the current worksheet as it did in the worksheet it came from.

Selecting to add or subtract the incoming cells brings in the values in the incoming cells and either adds them to or subtracts them from cells already in the worksheet. Adding cells to blank cells in the current worksheet simply makes those cells contain the incoming values.

Also, 1-2-3 lets you select to combine the entire worksheet, or just a range. This feature lets us combine just the data we want, the net income figures from each department.

Procedure

First, retrieve and examine the DEPTA worksheet.

Type /FRdepta and press Enter

1-2-3 retrieves the depta worksheet.

All we want to combine from this worksheet are the Net Income figures, so let's give them a range name.

Move to cell B15

This is one corner of the range we want to name.

Type /RNC

Starts the /Range Name Create command.

Type netincome and press Enter

Supplies the range name netincome.

Press Left arrow until the cell pointer moves to cell M15, and press Enter.

Highlights the range B15..M15 and completes the /Range Name Create command.

Now save the worksheet with the same name:

Type /FS, press Enter, and type R

Saves the file with the same name.

Repeat the same process for the deptb and deptc worksheets. These two worksheets are identical to depta, except for the different figures.

Now, retrieve the alldept worksheet and prepare to issue the /File Combine command:

Type /FRalldept and press Enter

Use the /File Retrieve command to retrieve the alldept worksheet.

Position the cell pointer at B5.

That's where we'd like the top left corner of the netincome range from the depta worksheet to appear.

Type /FC

Starts the /File Combine command.

Type A

Issues the Add command to add the incoming values.

Type N

Since we want to combine just a range from the depta worksheet, select Named/Specified-Range from the menu.

Type netincome and press Enter

Specifies the range netincome as the only portion of the range to bring in from depta.

Type depta and press Enter, or highlight depta from the menu of worksheet names and press Enter

Designates depta as the source worksheet for the incoming data.

At this time, 1-2-3 combines the worksheets. It may take a moment or so, as this operation requires use of the disk.

To combine figures from the deptb and deptc worksheets, repeat the same procedure, but position the cell pointer carefully. For the deptb worksheet, the proper cell to place the cell pointer on when starting the /File Combine command is B6, and for deptc, the correct cell is B7.

Notes

If you repeat the procedure, you'll want to erase the range B5..M17 in the alldept worksheet. That's because we're adding cells from the depta, deptb, and deptc worksheets, and if we added them in again, we'd add those values to values already present in alldept. That may or may not be an error--it just depends on what you want to do.

Also, the /File Combine command doesn't require named ranges in the incoming worksheets. Instead of using the range name netincome, we could have typed B15..M15 for the range to combine. Using range names, though, makes the process easier and more consistent. Each department worksheet, for example, does not need to be exactly the same if you use range names. As long as the row of Net Income figures is named netincome using the /Range Name Create command, it doesn't matter what the actual coordinates of the range are.

Databases in Lotus 1-2-3

This exercise will lead you through some common database techniques using Lotus 1-2-3.

First, start 1-2-3 and retrieve the worksheet sales0. This worksheet contains data that we'll use for the exercise:

Type /FRsales0 and press Enter

Issues the /File Retrieve command and loads the sales0 worksheet.

This is a typical Lotus 1-2-3 database--rows and columns of data. Notice the column headings in row 6: Company, Month, and Amount. The data in column A, the company name, are labels, the month column contains dates entered with the @date function, and the amount column contains numeric entries.

The first step is to make the database more attractive by enlarging the width of column A:

Move the cell pointer anywhere within column A.

The Home key is the quickest way. We need to be in column A before we can change the column width.

Type /WCS

Starts the /Worksheet Column Set-Width command.

Press Right arrow until the column width reaches 19

Now the column is wide enough to show the data it contains. This step is not absolutely necessary, and 1-2-3 remembers the full contents of each cell, no matter how narrow you make the column. But it's a good idea to make the columns wide enough to display most of the data they hold.

Press Enter.

Completes the /Worksheet Column Set-Width command.

Sorting the Database

The first thing you might want to do with a database is to sort it. Right now, the data is in no particular order. We'll first sort the database by company.

The first step in sorting is to identify the range containing the data you want to sort. Then, identify the column (or columns) to sort on and whether the sort is in ascending or descending order. Then, issue the Go command to actually perform the sort. Here's how:

First, position the cell pointer on cell A7

This is the top left corner of the range of data to sort, and it's easiest to start there.

Type /DSD

Starts the /Data Sort Data-Range command.

Type .

The period key anchors the cell pointer here.

Press End and Down arrow

Extends the highlight to the end of the column of data. Since databases don't normally contain blank rows, this is the end of the database.

Press Right arrow twice

Extends the highlighted range to include column C.

Press Enter

Completes the Data-Range command and returns to the Data menu.

Notice that when sorting, the data range doesn't include row 6, the row of column titles. If you included them in the data range, they would be sorted along with the rest of the data, and you'd have a mess.

Now, identify the column to sort on. We want to sort by company name

Type P

Issues the Primary-Key command. This is the column of data that 1-2-3 will sort first on.

Move anywhere within column A and press Enter

When identifying the sort key columns, the cell pointer can be anywhere within the column.

Type a and press Enter

The a indicates ascending sort order, which for labels, means in alphabetical order. Typing d would sort in descending, or reverse alphabetical order.

That's all we need to do to sort--except that 1-2-3 doesn't actually sort until you issue the Go command:

Type G

Issues the Go command. 1-2-3 sorts the data and returns to READY mode.

If you like, move about the worksheet to verify that the data is sorted by company.

Now, let's extend the usefulness of this sort. Note that within each company, the months are in no particular order. We could also sort on the month column as well as the company column, so that within each company, the data appears in chronological order. This means that the primary sort key is the company, because it's most important, but the secondary sort key is the month column. 1-2-3 will sort on the secondary key column when there's a tie within the primary key sort column. Here's how:

Type /DSType S

Issue the Secondary-Key command.

Move the cell pointer anywhere within column B and press Enter

This is the secondary sort key column.

Type A and press Enter

Ascending sort order. For dates, ascending order is chronological order, while descending order is reverse chronological order.

Type G

Issues the Go command. 1-2-3 sorts the data and returns to READY mode.

As you move about the worksheet, notice that for each company, the dates are in chronological order.

That's all there is to sorting. We didn't have to identify the data range or primary key sort column to 1-2-3 for the second sort, because it remembered the previous settings. If you add data to the database, or delete rows of data, you'll have to do the Data-Range command again to identify the proper rows of data to sort. Additionally, if you add columns to the database, or delete columns, make sure to issue the Data-Range command and adjust the columns to sort.

Queries

Now, let's take a look at making queries against the database. The first form of a query is the find command, which moves the cell pointer to just the rows of data you want to look at. The overall principal is that you create a criterion range and type examples of the data you want to find. Then issue the Find command, and 1-2-3 locates the data that matches the criterion.

The first step is to create the criterion range. It looks just like the database, so an easy to create it is to copy the column headings from the database to another part of the worksheet:

Move to cell A6

This is the top left corner of the range to copy from.

Type /C

Starts the /Copy command.

Press Right arrow twice and press Enter

This is the range to copy from.

Press Right arrow four times and press Enter

Starts copying at cell E6 and completes the /Copy command.

Now that we have the column headings for the criterion range, we need to identify where the database and the criterion ranges are:

Type /DQ

Starts the /Data Query commands.

Type I

Issues the Input command. The input range is the database itself.

Move to cell A6 and type .

The top left corner of the input range is cell A6, so we moved to it and press the period key to anchor the cell pointer there. For the input range, you must include the row of column headings.

Press End and Down arrow

Moves the highlight to the end of the database.

Press Right arrow twice and press Enter

Highlights the input range and completes the Input command. This range is the same as the data range when sorting, but includes the row of column titles.

Type C

Issues the Criterion command to identify the criterion range.

Move the cell pointer to cell E6 and type .

Cell E6 is the top left corner of the criterion range.

Press Right arrow twice, Down arrow once, and press Enter

Identifies the criterion range and completes the Criterion command. Notice the the criterion range is just like a database range, but it contains just a single row. Later, we'll create a criterion range with additional rows.

Type Q

Quits the /Data Query command menu and returns to ready mode.

In preparation for using the Find command, let's sort the database again so that the Find will be more interesting. We'll sort the database by the amount:

Type /DS

Starts the /Data Sort command.

Type P

Issues the Primary-Key command.

Move the cell pointer to column C and press Enter

Identifies the amount column as the primary sort key column.

Type A and press Enter

Sort in ascending order. Actually the a was already entered from the last sort, so we could have pressed Enter to accept the a that was already there.

Type G

Issues the Go command. 1-2-3 performs the sort and returns to READY mode.

Now, let's enter a criterion in the criterion range. We'll look for the records for ABC Co. The principal is type type an example record that matches the records we want to find:

Move to cell E7

This is the cell right below the Company column title in the criterion range.

Type ABC Co. and press Enter

Type criteria just like regular cell entries.

Now we're ready to issue the Find command:

Type /DQF

Issues the /Data Query Find command.

At this point, 1-2-3 enters FIND mode and moves to the first matching record. There are several actions you can take now:

* Press Down arrow to move to the next matching record. If you press Down arrow enough times, 1-2-3 will beep to indicate there are no more matching records below.

* Press Up arrow to move to the previous matching record. Again, 1-2-3 will beep if there are no matching records above.

* Press Home to move to the top of the database, or End to move to the end of the database. 1-2-3 moves to the first and last records in the database, regardless of whether they match the criterion.

* Use Left arrow and Right arrow to move to the left and right. Many databases may be wider than what will display on the screen, so use these keys to view other columns of data in the database.

* Use the F2 (Edit) key to enter EDIT mode and make changes to a cell. First, you'll need to use the arrow keys to move to the cell to edit. Then press F2 (Edit) and make editing changes as regular, press Enter to complete the Edit and return to FIND mode.

Note that while looking at records, 1-2-3 is in FIND mode. To exit FIND mode and return to the menu or regular READY mode, press either Enter or Esc:

Press Enter or Esc

1-2-3 exits FIND mode, and returns to the /Data Query menu.

Type Q

Issues the Quit command from the /Data Query menu and returns to READY mode.

Now, look at records for a different company:

Move to cell E7

This is the cell for entering company names to look for.

Type Wilson Bros. and press Enter

A new company to look for.

Instead of issuing the /Data Query Find command again, we can use the shortcut of the F7 (Query) key, which repeats the last query command. In this case, it will repeat the Find command, although later on it might repeat the Extract command, if that was the last query command you issued.

Press F7

Repeats the last query command, or /Data Query Find. 1-2-3 enters FIND mode, and you can perform the same actions as above.

Press Enter or Esc

Exits FIND mode. We're back in READY mode instead of the /Data Query menu, because we use the F7 (Query) command to start the find command instead of a menu command.

If 1-2-3 beeps immediately after you issue the FInd command, it means that no records were found meeting your criterion. Check the spelling.

Expand the query to find records for both Wilson Bros. and ABC Co. In this case, we expand the criterion range to include the row of column titles, and two rows below:

Type /DQC

Issues the /Data Query Criterion command.

Press Down arrow and press Enter

1-2-3 remembers the last criterion range, so we can expand it easily with the Down arrow key.

Type Q

Issues the Quit command from the /Data Query menu and returns to READY mode.

Move to cell E8

This is where the second criterion goes.

Type ABC CO. and press Enter

This is the second company name to include in the criterion range.

Press F7

Repeats the Find command. 1-2-3 enters FIND mode and stops at records where the company is either Wilson Bros. or ABC Co.

Press Enter or Esc

Terminates FIND mode and returns to READY mode.

That illustrates an or query: When the criterion range is larger than one row, 1-2-3 matches records that match any of the criterion rows. We could make the criterion range one row deeper and type another company name, and 1-2-3 would match it too.

Now, let's go back to searching for a single company name:

Move to cell E8

Prepare to erase this cell.

Type /RE and press Enter

Issues the /Range Erase command and erases cell E8.

Now, we need to adjust the criterion range so that it doesn't include row 8. If we didn't do this, the next query would still include this blank row as part of the criterion, and a blank criterion row matches all records. This is an important step; otherwise, your queries will not be accurate.

Type /DQC

Issues the /Data Query Criterion command.

Press Up arrow and press Enter

Shrinks the criterion range to include just the row of column titles and one additional row and completes the Criterion command.

Type Q

Issues the Quit command from the /Data Query menu to return to READY mode.

Now, let's look at queries involving numeric columns. If you want to find a record with a certain amount, make a query just like looking for a label:

Move to cell E7

Prepare to use the /Range Erase command to erase any existing criteria.

Type /RE

Starts the /Range Erase command.

Press Right arrow twice and press Enter

Highlights the three criterion cells and completes the /Range Erase command.

Move to cell G7

The cell where a matching query for the Amount should go.

Type 1093 and press Enter

The value to look for.

Press F7

Repeats the Find command. 1-2-3 enters FIND mode, and you can view the records matching the criterion.

Press Enter or Esc

1-2-3 leaves FIND mode and returns to READY mode.

At this point, 1-2-3 finds all records where the amount is 1,093. While this type of query is often useful, many times you want to perform a range query, such as finding all records where the amount is 3,000 or greater. This type of query involves writing an equation or assertion that is true for the records you're interested in.

For example, to find the records with an amount of 3,000 or greater, we need to write an equation that says something>=3000. The "something" in this formula is the top cell of actual data in the Amount column of the database. That's not the column title itself (cell C6), but the first data cell, cell C7. The equation we need in cell G7 is, then, +c7>=3000, as follows:

Move to cell G7

This is the cell to type the criterion in. Actually formula criterion of this type can go in any of the cells in the criterion range, but it makes most sense to enter it in this cell.

Type +C7>=3000 and press Enter

The formula or assertion for the data we want. You can use cursor pointing to enter these formulas.

1-2-3 displays the value of this formula as either 1 or 0, depending on whether this assertion formula is true or false for whatever value cell C7 holds at this time. It's customary to format these criterion cells with the Text format to display the formula in the cell, rather than the nearly meaningless true and false values.

Type /RFT

Starts the /Range Format Text command.

Press Left arrow twice and press Enter

Formats the range E7..G7 with the Text format and completes the command.

Now we see the text of the formula rather than its value. Proceed with the query:

Press F7

1-2-3 enters FIND mode and stops at each record matching the criterion.

Press Enter or Esc

1-2-3 leaves FIND mode and returns to READY mode.

Commonly, when entering criterion formula, we assign range names to the top cells of the database to make entering criterion formulas easier. For example, we might use the /Range Name Create command to give the range name amount to cell C7. Then, we could have typed +amount>=3000 rather than +C7>=3000.

Instead of using the /Range Name Create command three times to give the range name company to cell A7, month to cell B7, and amount to cell C7, we'll use the /Range Name Labels Down command. This command will take the labels in cells A6, B6, and C6, and give the cell below each of those cells the same range name as the label in the cell:

Move to cell A6

This is the top left corner of the range of labels.

Type /RNLD

Starts the /Range Name Labels Down command.

Press Right arrow twice and press Enter

Highlights the range A6..C6, assigns the three range names company, month, and amount to the three cells below, and completes the command.

Note that the formula in cell E7 now reads +amount>=3000.

Now, let's modify this query to find records where the amount is 2,000 or greater and less than 3,000:

Move to cell G7

This is the cell to enter the criterion assertion into. Again, we could enter it in any of the criterion cells, but it makes most sense to enter it here.

Type +amount>=2000#and#amount<3000 and press Enter

Enters the formula in the cell.

Press F7

1-2-3 enters FIND mode and finds the matching records.

Press Enter or Esc

1-2-3 exits FIND mode and returns to READY mode.

For queries against the month column, we'll need to use 1-2-3's date functions. All of the dates in this column are entered as the first day of the month. To find records for a certain month, we can use a matching query by typing the @date function for the month we want in the month column in the criterion range as follows:

Move to cell E7

Prepare to use the /Range Erase command to erase any existing criteria.

Type /RE

Starts the /Range Erase command.

Press Right arrow twice and press Enter

Highlights the three criterion cells and completes the /Range Erase command.

Move to cell F7

This is the cell where a matching query against a month value must go.

Type @date(86,6,1) and press Enter

Enters the @date function for June 1, 1986.

Press F7

1-2-3 enters FIND mode and finds the matching records.

Press Enter or Esc

1-2-3 exits FIND mode and returns to READY mode.

Now, let's enter a query that will find records for June, July, and August of 1986:

Move to cell F7

This is the cell to enter the criterion in. Since this is actually a formula assertion criterion, it could go in any of the three cells in the criterion range, but it makes most sense to put it here.

Type +month>=@date(86,6,1)#and#month<=@date(86,8,1) and press Enter

The assertion formula.

Press F7

1-2-3 enters FIND mode and finds the matching records.

Press Enter or Esc

1-2-3 exits FIND mode and returns to READY mode.

A second type of query that 1-2-3 can make is the extract. In this case, 1-2-3 copies matching data records to an area of the worksheet called the output range. You can then perform any action on these records--printing, calculations, graphing, whatever. The first step is to create the output range and identify it to 1-2-3 as follows:

We'll first copy the range A6..C6 to cell I6, thus forming the top part of the output range.

Move to cell A6

Prepare to issue the /Copy command.

Type /C

Starts the /Copy command.

Press Right arrow twice and press Enter

Highlights the range A6..C6 as the range to copy from and completes answering that question.

Move to cell I6

The top left cell of the range to copy to.

Press Enter

Completes the /Copy command.

Now, use the /Data Query Output command to identify this range as the output range:

Move to cell I6

Prepare for the command.

Type /DQO

Starts the /Data Query Output command.

Type . and press Right arrow twice

Anchors the cell pointer and highlights the three column titles.

Press Down arrow three times and press Enter

Highlights three additional rows to include in the output range and completes the /Data Query Output command.

Type Q

Issues the Quit command. 1-2-3 leaves the /Data Query menu and returns to READY mode.

Now, let's enter a criterion. First, erase any existing criteria:

Move to cell E7

Prepare to use the /Range Erase command to erase any existing criteria.

Type /RE

Starts the /Range Erase command.

Press Right arrow twice and press Enter

Highlights the three criterion cells and completes the /Range Erase command.

Move to cell E7 and type ABC Co. and press Enter

A criterion that will match the records for ABC Co.

Type /DQE

Issues the /Data Query Extract command. We couldn't press the F7 Query key, as that would repeat the last query command, which was a Find, not an Extract.

At this time, 1-2-3 beeps to indicate an error. The problem is that the output range didn't have enough rows to accommodate all the records that were matched.

Press Enter or Esc

Clear the ERROR indicator and return to READY mode.

One solution to this problem is to make the output range large enough to accommodate all the records we're likely to match. A second solution is to include just the row of column titles in the output range, in which case 1-2-3 assumes that all rows below the row of column titles are available for the output of the extract. This approach is risky, because the extract may overwrite other data that is below the output range. We'll use this technique, however, because our worksheet is empty below the column titles.

Type /DQO

Starts the /Data Query Output command.

Press Up arrow three times and press Enter

Now the output range contains just the row of column titles.

Type E

Issues the Extract command from the /Data Query menu.

Type Q

Issues the Quit command from the /Data Query menu. 1-2-3 returns to READY mode.

At this time, examine the output range to verify that it contains all the data records for ABC Co. If it is empty, you probably misspelled the criterion. Let's repeat the extract for a different company:

Move to cell E6.

The cell to enter the criterion in.

Type Wilson Bros. and press Enter

The company name to extract.

Press F7

Now, the F7 Query key performs the Extract command, because that was the last query command we issued.

As 1-2-3 extracts the new data, it first erases all the existing data from the output range. Thus, any new extracts show only the records matching the current criterion.

If you like, you can repeat the Extract command using any of the criteria we've already used.

Database Statistical Functions

1-2-3 contains a class of functions called the database statistical functions. These functions, @dsum, @davg, @dcount, @dmax, @dmin, @dstd, and @dvar, return the same statistics as their regular counterparts (@sum, @avg, etc.), but only for records in a database that match the criterion. The syntax of all the functions, using @dsum as an example, looks like this:

@dsum(input,offset,criterion)

The input and criterion arguments represent the database input range and the criterion range that the function should observe. They're identical to the ranges used in the query commands. The offset arguments tells 1-2-3 which column of the input range to apply the function to. The leftmost column (column A in our example) is column zero; the next column is one.

To use the database statistical functions, it will be easiest if we first assign range names to the input and criterion ranges of the database as follows:

Move to cell A6

Prepare for the /Range Name Create command.

Type /RNC

Start the /Range Name Create command.

Type input and press Enter

Enters the range name input.

Press End and Down arrow, press Right arrow twice, and press Enter

Highlights the range A6..C134 and completes the /Range Name Create command.

Move to cell E6

Prepare for the /Range Name Create command.

Type /RNC

Start the /Range Name Create command.

Type criterion and press Enter

Enters the range name criterion.

Press Right arrow twice, press Down arrow, and press Enter

Highlights the range E6..G7 and completes the /Range Name Create command.

Now, enter labels for the database statistical formulas:

Move to cell E11

Prepare to make a horizontal line.

Type \- and press Enter

Uses the repeating label prefix (\) to create a horizontal line.

Type /C and press Enter, press Right arrow, type ., press Right arrow, and press Enter

Use the /Copy command to duplicate the line across the next two columns. This is a common technique for making horizontal lines across the worksheet.

Move to cell E15

Prepare to type some labels.

Type Sum and press Down arrow

Enters the label to cell E15 and moves to the next cell below.

Type Count and press Down arrow

Enters the label to cell E16 and moves to the next cell below.

Type Average and press Down arrow

Enters the label to cell E17 and moves to the next cell below.

Type Maximum and press Down Arrow

Enters the label to cell E18 and moves to the next cell below.

Type Minimum and press Down Arrow

Enters the label to cell E19 and moves to the next cell below.

Type Std. Dev and press Down Arrow

Enters the label to cell E20 and moves to the next cell below.

Type Variance and press Down Arrow

Enters the label to cell E21 and moves to the next cell below.

Now, for the database statistical functions, the input range is named input, the criterion range is named criterion, and the column offset to use is two, to gather the statistics for the amount column.

Move to cell F15

Prepare to enter the formulas.

Type @dsum(input,2,criterion) and press Enter

Enters the formula to cell F15.

Press Down arrow

Moves to the next cell below.

Type @dcount(input,2,criterion) and press Enter

Enters the formula to cell F16.

Press Down arrow

Moves to the next cell below.

Type @davg(input,2,criterion) and press Enter

Enters the formula to cell F17.

Press Down arrow

Moves to the next cell below.

Type @dmax(input,2,criterion) and press Enter

Enters the formula to cell F18.

Press Down arrow

Moves to the next cell below.

Type @dmin(input,2,criterion) and press Enter

Enters the formula to cell F19.

Press Down arrow

Moves to the next cell below.

Type @dstd(input,2,criterion) and press Enter

Enters the formula to cell F20.

Press Down arrow

Moves to the next cell below.

Type @dvar(input,2,criterion) and press Enter

Enters the formula to cell F21.

Now, format these cells with the Comma format, zero decimal places for appearance

Move to cell F15

Prepare for the /Range Format command.

Type /RF,0 and press Enter

Issues the /Range Format Comma zero decimal places command.

Press End and Down arrow

Extends the highlight to cell F21, the end of the column of figures.

Press Enter

Completes the /Range Format command.

Let's also calculate the most recent date for records matching the criterion. The key here is to remember that as time goes on, dates become greater, so use the @dmax function to find the record with the greatest date. That date, then, is the most recent.

Move to cell E23

Prepare to enter labels.

Type Most and press Down arrow

Enters the label to cell E23 and moves down.

Type Recent and press Enter

Enters the label to cell E24.

Move to cell F24

Prepare to enter the formula.

Type @dmax(input,1,criterion) and press Enter

Enters the formula. Note that the formula is the same as the others, but uses an offset of one to compute the statistic for the first column over, the month column.

Type /RFD4 and press Enter

Formats the cell with the Date 4 format.

Now, let's enter a criterion in the criterion range. First, we'll erase the cells in the criterion range to get rid of any criteria still there:

Move to cell E7

Prepare to use the /Range Erase command to erase any existing criteria.

Type /RE

Starts the /Range Erase command.

Press Right arrow twice and press Enter

Highlights the three criterion cells and completes the /Range Erase command.

Move to cell E7

Prepare to enter a criterion.

Type ABC Co. and press Enter

Enters the criterion.

That's all you need to do. As soon as 1-2-3 calculates the worksheet, the database statistical functions display the results just for the records that meet the criterion; that is, just the records for ABC Co. Enter other criteria to experiment with these functions. Since an empty criterion row matches all data records, use /Range Erase to erase the criterion row. As soon as 1-2-3 calculates the worksheet, the functions calculate their statistics for the entire database.

Data Tables and Crosstabulations

Data tables, databases, and database statistical functions goes hand in hand, letting you perform powerful analysis of your database.

Remember from the Data Table exercise that the /Data Table 1 command takes values from a column of input values, places them in the input cell, recalculates the worksheet, and records the value of some formula. We'll use this capability to compute the total sales for each company, as well as the average amount of each company's invoices.

As always when using the /Data Table 1 command, we need to create a column of input values. In this case, we need to have a column of all the company names. The /Data Query Unique command will make this list for us. This command works like the /Data Query Extract command, but eliminates duplicate values from the result. Here's how to get this list:

First, erase any existing criteria.

Move to cell E7

Prepare to use the /Range Erase command to erase any existing criteria.

Type /RE

Starts the /Range Erase command.

Press Right arrow twice and press Enter

Highlights the three criterion cells and completes the /Range Erase command.

Move to cell N20

This will be the top of the list of company names.

Type Company and press Enter

This is the output range for the result of the /Data Query Unique command.

This output range will contain just a single column from the database. This way, we get just the column that we need, the company names. Additionally, the /Data Query Unique command determines uniqueness by the number of columns contained in the output range. Now, we'll identify this range to 1-2-3 as the new output range:

Type /DQO

Starts the /Data Query Output command. At this time, 1-2-3 highlights the previous output range we used.

Press Backspace

This cancels the previous output range and places the cell pointer at cell N20. This is preferable to pressing Esc, which would also cancel the output range, but place the cell pointer at the top of the old output range, or cell I6.

Press Enter

Finishes the /Data Query Output command, with cell N20 as the output range. 1-2-3 will use as many rows as necessary to hold the results of the Unique extract.

Type U

Issues the Unique command. 1-2-3 performs the extract and returns to the /Data Query menu.

Type Q

Issues the Quit command, and 1-2-3 returns to READY mode.

For appearance's sake, we'll sort this data so that the companies are in alphabetical order:

Move to cell N21

This is the top of the column of data to sort.

Type /DSD

Issues the /Data Sort Data-Range command.

Press Backspace

Cancels the old data range and leaves the cell pointer at cell N21. Again, preferable to pressing the Esc key.

Type . and press End and Down arrow

Anchors the cell pointer at cell N21, expands the highlight down to the end of the column (cell N28) and completes the Data-Range command.

Type P

Issues the Primary-Key command to identify the sort column.

Press Backspace

Cancels the old Primary-Key sort column and returns the cell pointer to column N.

Press Enter

Since the cell pointer is already in the column to sort on, we can press Enter to accept the proposed value.

Type a and press Enter

Indicates ascending (alphabetical) sort order.

Type G

Issues the Go command. 1-2-3 sorts the data and returns to READY mode.

That's it for creating the column of input values for the data table. Note that since we changed the output and data ranges, we'll have to readjust them if we perform an extract or sort of the original database.

The second task in creating a data table is to write the formulas for the values we want the data table to collect. The two values we want, the sum of the invoices and the average amount, are already calculated in cells F15 and F17. We'll use those cells in the data table as follows:

Move to cell O20

Prepare to enter the first formula.

Type +F15 and press Right arrow

Enters the formula for the sum of the invoices and moves to cell P20.

Type +F17 and press Enter

Enters the formula for the average of the invoices.

As is the tradition in data tables, format these two cells with the Text format:

Type /RFT, press Right arrow, and press Enter

Formats the two cells with the text format.

Now we're ready to issue the /Data Table 1 command. Even though we're collecting two columns of data, there is still only one set of input values, so this is a one-way data table:

Move to cell N20

This is the top left corner of the data table range.

Type /DT1

Start the /Data Table 1 command.

Type .

Anchor the cell pointer at cell N20.

Press End and Down arrow, press Right arrow twice, and press Enter

Expand the table range to include the range N20..P28.

Type E7 and press Enter

E7 is the input cell, where 1-2-3 should place each of the company names, one-by-one.

At this time, 1-2-3 calculates the data table. It may take a few moments. When the calculation is complete, format the resulting numbers:

Move to cell O21

The top left cell of the range to format.

Type /RF,0 and press Enter

Starts the /Range Format Comma zero decimal places command.

Press End and Down arrow, and press Right arrow

Highlights the range O21..P28 as the range to format.

Press Enter

Completes the /Range Format command and returns to READY mode.

Now, we need to interpret the results of this command. The "mechanical" interpretation is that when cell E7, the input cell, is ABC Co., cell F15 is 41,854. That's because 1-2-3 took the first value from the left-most column of the data table, put it in the input cell, let the worksheet recalculate, and copied the value in cell F15 to the cell to the right of the input value of ABC Co. Since E7, the input cell, is a cell in the criterion range, cell F15 after recalculation contained the sum of the amount column of all the records in the database that met the criterion. Then 1-2-3 repeated the process for the other values of the left-most column of the data table. That's why the data table may take a moment to calculate--1-2-3 has to calculate the worksheet many times.

The "business" interpretation of this table is that for the period of time covered by this database, ABC Co. invoices totaled 41,854.

Introduction to Macros

Macros are an advanced topic in Lotus 1-2-3 that let you automate many spreadsheet functions. There are two parts to the 1-2-3 macro language:

* Macros let you store keystrokes in cells. These keystrokes are exactly the same keystrokes you would issue to 1-2-3 when performing some task or command--perhaps a menu command, cursor movements, or data entry. When you run the macro, these keystrokes are performed by 1-2-3, just as though you had typed them yourself.

* 1-2-3 macros can also contain advanced macro commands that let macros make decisions, repeat processes a number of times, and present menus.

The primary skill you need to develop macros is an intimate knowledge of 1-2-3. That's because when you write a macro, you're using 1-2-3 without seeing the results of your commands. That's tricky at first, but something macro writers are used to.

In its most basic form, a macro is a set of stored keystrokes. You type these keystrokes into a series of cells in the worksheet. Then, you give a range name to the top cell of the macro so that 1-2-3 can find the macro when you want to run it.

For example, suppose you find yourself frequently formatting a single cell with the Currency format, two decimal places. This task is a good candidate for a macro--something repeated many times. Now, think about the keystrokes used in the process:

1) Press the slash key to activate the menu.

2) Press R for the Range command.

3) Press F for the Format command.

4) Press C for the Currency format.

5) Type 2 and press Enter for the number of decimal places.

6) Press Enter again in response to the range to format question.

In 1-2-3 macro notation, these commands would look like this:

/rfc2~~

That, in fact, is a macro. You can recognize many of the characters in the macro as representing the keys you'd type to format the cell. The tilde (~) represents a press of the Enter key in a macro.

Macro Structure

The most basic type of 1-2-3 macro is a series of keystrokes. You type the keystrokes as a label in a cell or cells in the worksheet, and upon command, 1-2-3 replays the keystrokes. The first step in using a macro, then, is to type the keystrokes in a cell or cells in the worksheet.

Typing a Macro

When typing macro keystrokes, pick an out-of-the way location for them. For example, you might start your macros in cell A7000--certainly a location that most worksheets won't get close to using. Since macros start with a named range, you can move to them quickly by pressing the Goto key (function key F5) and pressing the Name key (function key F3) to get a menu of range and macro names.

Remember, macros are labels, and you can manipulate them with any of the 1-2-3 commands that work with labels--copy, move, erase, whatever.

You can enter macro keystrokes in any form you like. You could type the keystrokes for the example macro discussed above in a single cell like this:

/rfc2~~

Or, you might place each keystroke in its own cell like this:

/
r
f
c
2
~
~

Both of these methods work equally well, because 1-2-3's macro processor starts with the top cell of the macro, executes the keystrokes in that cell, moves down to the next cell, executes its keystrokes, and repeats until it finds an empty cell or a cell that contains the special macro keyword {quit}.

As you gain more experience with macros, you'll find that it makes sense to group related keystrokes together in a single cell. Most people would write this simple macro in a single cell.

When typing macro keystrokes, you often must type a label prefix to force 1-2-3 to enter your typing to a cell. For example, the first keystroke in this macro is the slash key. If you move to a cell and type the slash key, 1-2-3 produces the menus. Further typing will perform menu commands or produce error beeps. To type this macro, you must start with a label prefix (most commonly the apostrophe for left alignment). Thus, to type this macro, you'd actually type '/rfc2~~ and press Enter. In general, when a macro cell starts with the slash key or a number, is is a formula or a label that the macro should type, you must start the macro cell with a label prefix.

Naming a Macro

Once you've typed the macro keystrokes, you must give a range name to the top cell of the macro. Macro range names are the backward slash followed by a single character from the alphabet. For example, you might give this macro the range name \c, the "c" standing for currency format.

Since macro names can be just a single letter from the alphabet, you can have just 26 macros in any worksheet. Later, though, we'll see that a single macro might present a menu of other macros which don't need to be names with a single character, so by using menus you can have as many macros as you want.

There is a special macro named \0 (that's the digit zero, not the letter O). This macro is called the auto-execute macro, because every time you perform the File Retrieve command, 1-2-3 scans the worksheet for a macro called \0. If 1-2-3 finds such a macro, it runs it automatically. You can't type Alternate-0 from the keyboard to run this auto-execute macro, so if you want to run the \0 macro from the keyboard, you must give it an additional macro name.

To name a macro, you can move to the top cell of a macro and issue the Range Name Create command. Another way to name a macro is to type the name of the macro in the cell to the left of the top cell of the macro. Then, use the Range Name Labels Right command to name the macro. This technique makes it easy to name the macro, and it also documents the name of the macro. We'll see an example of this in a moment.

Some macros are special purpose, and have value only in the worksheet they were created for. Others, like the date macro given as an example later on, are more general and useful in many worksheets. When developing these general macros, you might create them in a test worksheet, and when tested and perfected, saved in their own worksheet files containing just a single macro. Then, when you decide to use one of these macros in a new worksheet, you can use the /File Combine Formulas Entire-File command to bring the macro into the current worksheet. One difficulty is that range names do not come into the current worksheet, so you'll have to create the named ranges the macro uses. That's another good reason for using the macro naming technique mentioned above--it makes establishing the named ranges easier.

Running the Macro

After you've named the macro, you can run it. Since this macro is named \c, you'd run the macro by holding down the Alternate key and pressing C. At this time, the macro processor takes over and performs the macro keystrokes, just as though you were typing them yourself.

Before running a macro, you can press the Step key, which is Alternate-F1. This action places you in STEP mode, as indicated at the bottom of the screen. Now, if you run a macro, 1-2-3 executes it one step at a time. Between each step, 1-2-3 displays a flashing SST indicator at the bottom of the screen as a reminder to press any key to proceed to the next step. To leave the single step mode and return to regular, full-speed macro execution, press Alternate-F1 again.

To stop a macro, press the Break (Control-Break) key. Most of the time, 1-2-3 will display the ERROR indicator at the top right of the screen, so press Escape or Enter to clear the error and return to READY mode.

Here's a detailed script for creating and running this macro. Start this exercise with a blank screen.

Move to cell B1

We'll place the macro in cell B1. Although this is not a realistic place for most macros, it will do for this exercise.

Type '/rfc2~~ and press Enter

This is the actual macro. Remember to type the apostrophe label prefix. Otherwise, you won't be entering a label to the cell, you'll be actually performing the Range Format command.

Move to cell A1

We'll type the name of the macro here.

Type '\c and press Enter

This is the name of the macro. The range name we want to use is \c. Be sure to use the backwards slash, not the slash used to summon 1-2-3's menus. Also, you must type a label prefix first, or the backwards slash becomes the repeating label prefix and will fill the cell with the letter c.

Type /rnlr

Starts the Range Name Labels Right command. This command will take the contents of a range and give each cell to the right a range name. the given range name is the contents of each cell in the range.

Press Enter

Responds to the range to name, naming just cell B1 with the name \c.

This completes the making of the macro. Let's run it now.

Move to cell A15, type 100, and press Enter

A cell to test the macro.

Hold down the Alternate key and type C

Runs the macro.

At this time, 1-2-3 runs the macro. You'll see the 100 change to $100.00 as evidence the macro worked. If you looked closely, you may have seen the menus flash at the top of the screen. If you want, run the macro again and watch for the control panel to operate, but if you have a very fast computer, you may not be able to see anything.

Keyboard Equivalents for Macros

Each special key on the keyboard has a notation for use in a macro. Here's a list of keystroke notations for use in macros:

Macro Key

Keyboard key

~ (the tilde)

The Enter key. It's necessary to use this in a macro, because pressing the real Enter key while typing a macro completes the entry of the macro. Leaving out the Enter key is a common mistake when writing macros.

{up}, {down}, {left}, {right}, {pgup}, {pgdn}, {home}, {end}, {bigleft}, {bigright}

These are the cursor navigation keys. With these keys as with other macro keynames, you can include a repetition factor for multiple movements. For example, instead of writing {up}{up}{up} to move up three rows, you can write {up 3}.

{edit}, {name}, {abs}, {goto}, {window}, {query}, {table}, {calc}, {graph}

The function key names for use in a macro.

{escape} or {esc}

The Escape key.

{backspace} or {bs}

The backspace key.

{delete} or {del}

The Delete key.

{~}, {{}, and {}}

Use these for a ~, {, or } in a macro.

Macro Language Commands

{Panelon}, {Paneloff}

When 1-2-3 runs a macro, it plays back the macro keystrokes just as though you were typing them yourself. The control panel will operate too, showing menus, prompts, and other information. While the operation of the control panel is harmless, it may confuse someone who's using your macros and isn't familiar with 1-2-3. Also, operating the control panel uses computer processor time. If you place the command {paneloff} at the top of the macro, 1-2-3 won't display changes in the control panel. {Panelon} restores operation of the control panel. At the end of a macro, 1-2-3 automatically turns the control panel on if it was off.

This is a cosmetic command, never absolutely necessary to use.

{Windowson}, {Windowsoff}

In a similar manner to {paneloff}, {windowsoff} freezes the screen, so that cursor movements won't cause a change in the display. This is useful because it can greatly speed macro execution. Redrawing the screen takes a lot of 1-2-3's time, so eliminating the drawing speeds the macro. {Windowson} restores screen updating.

{?}

The question mark macro command causes macro execution to stop. You can then type whatever you like or move about the worksheet. When you press the Enter key, 1-2-3 resumes the macro. This command allows interactive macros, where the user has input during the macro.

It's important to note that pressing the enter key after moving about the during a {?} pause signifies that the user is through. If you want the macro to press the Enter key, you must include a ~ after the {?}. For example, look at this macro:

/rfc2~{?}~

In this macro, 1-2-3 issues the Range Format Currency 2 decimal places command, then stops and lets the user move about in response to the range to format question. When running the macro, the user will use cursor keys to expand the range and then press Enter. The last ~ in the macro then issues the Enter key to complete the range highlighting. What if the macro was only like this:

/rfc2~{?}

In this case, after expanding the range highlight and pressing the Enter key, 1-2-3 stops macro execution, and the control panel will still be asking for the range to format. The user will have to press the Enter key again to complete the range highlighting.

{Getlabel}, {Getnumber}

These two commands provide a method for asking the user to type something and then accepting the input and storing it in a cell. {Getlabel} stores the input as a label, {getnumber} stores it as a number, if the user typed a valid numeric entry. The syntax of the commands looks like this:

{getlabel prompt,location}
{getnumber prompt,location}

Prompt is a character string that usually asks the user to type something--their name, the amount of a check, or whatever. Location is a cell or range name. After the user types something and presses the Enter key, 1-2-3 places their typing in that cell. For example:

{getnumber "Enter the amount of the check",a50}
{getnumber "Enter the amount of the check",check}

The first example displays the prompt Enter the amount of the check at the control panel, and then places the number the user types in cell A50. The second command performs the same action, but places the result in the cell named check.

{getlabel "Type your name :",a50}
{getlabel "Type your name :,name}

These two examples use the {getlabel} command, as the requested information is a label, not a numeric entry. Notice also that these two examples use a colon and a space after the word name. That makes the prompt easier to respond to. For example, if the command was

{getlabel "Type your name",a50}

the control panel would display

Type your name

After typing the name, the control panel looks like

Type your nameJohn Doe

Note that the first character of the name John Doe appears immediately after the prompt string. Some users may type a space before the name to make the response look better, but that action introduces a space to the name in cell A50, which probably won't be welcome. But the command

{getlabel "Type your name :",a50}

produces a result like this:

Type your name: John Doe

This looks a lot better and is less confusing to the user.

{Quit}

The {quit} command indicates the end of a macro. It's not necessary to use this command to terminate a macro, because a blank cell ends a macro. But it's a good idea to use the {quit} command, as it formally marks the end of a macro. Also, if for some reason the blank cell that ends a macro gains contents of some sort, those contents are considered part of the macro.

Example Macros

Typing Macros

A common task for a macro is to perform typing tasks, such as your name and address. Here's an example macro that types a name and address:

A B
1 \n Bob Weeks~
2 {down}
3 '4455 N. Rockwell
4 {down}
5 Chicago, IL 60625~
6

Discussion:

A1 This cell contains the range name for cell B1. After typing this (remember to type the label prefix ' before the \n), use the Range Name Labels Right command with the range A1..A1.

B1 The starting cell of the macro. This cell types the name Bob Weeks into the current cell and completes the cell entry because of the ~ character, which represents the macro typing the Enter key. If we didn't use the ~, we'd never complete the label entry, and the rest of the macro would be interpreted as additional typing into the current cell, instead of being recognized as macro commands.

B2 Moves the cell pointer one row down to prepare to type the next row of the address.

B3 Types the street address. This is a little tricky. If you move to cell B3 and type 4455 N. Rockwell and press Enter, you'll get an error. That's because 1-2-3 interprets a cell that starts with a digit as a numeric entry, and you can't have spaces, N's, and so forth in a number. Most 1-2-3 users recognize this and start a cell entry like this with a label prefix, say the apostrophe ('). If you start this macro command cell with a single apostrophe, that label prefix makes this cell a label rather than a number. When the macro executor processes this cell, however, it throws away the initial apostrophe and types 4455 N. Rockwell without a label prefix into the current cell, which produces an error. So type two apostrophes into this cell--one for the label prefix for cell B3, and another for the cell the macro types into.

B4 Types the down arrow key to move to the next row below. Note that cell B3 didn't end with the ~ to represent typing the Enter key. That's fine in this case, as pressing the down arrow key completes the previous label entry as well as moving. You can see that macro typing follows the same rules as regular typing.

B5 Types the city, state, and zip code, and then presses the Enter key through the ~ character to complete the entry.

B6 Since this cell is blank, it signifies the end of the macro. It's a good idea to use the {quit} command to explicitly identify the end of a macro.

Summing a Column

This macro will sum a column of numbers. It makes extensive use of cursor navigation keys, including the End key. For this macro to work properly, the cell pointer must be anywhere in the column of figures to add except for the bottom figure.

Here's a screen view of a macro to accomplish this tasks. This macro follows a common convention for macro writing. In column A (or whatever column is to the left of the macro commands), type labels that supply range names for the cells to their right. In column B, type the macro commands. In a column to the right, write a comment or note for each macro cell. This documentation, (both the range names and the comments) is important. The 1-2-3 macro language can be confusing when you're trying to figure out what the macro you wrote last month does, and any notes or comments you make will help clarify matters for you or someone else.

A B C D E F
1 \c {end}{down} Move to end of column
2 {down} Move down one more cell
3 \-~ Type a cell full of minus signs
4 {down} Move down one more cell
5 @sum( Start typing the sum function
6 {up}{end}{up} Move to top of column
7 . Anchor cell pointer with period
8 {end}{down} Move to end of column
9 {up} We moved one cell too far
10 )~ End sum function and press enter
11 {quit} Quit the macro

View of the macro from the Cambridge Spreadsheet Analyst:

B1 '{end}{down}
B2 '{down}
B3 '\-~
B4 '{down}
B5 '@sum(
B6 '{up}{end}{up}
B7 '.
B8 '{end}{down}
B9 '{up}
B10 ')~
B11 '{quit}

Discussion:

A1 The name of the macro, for use with the Range Name Labels Right command.

B1 The start of the macro. Uses the End and Down arrow keys to move to the bottom number in the column.

B2 Moves down an additional row from where the End key moved to.

B3 Types the backwards slash and a minus sign and completes the entry with the ~ to represent the Enter key. When typing this macro, type a label prefix (most commonly the apostrophe) first. If you don't, 1-2-3 will fill cell B3 with minus signs. Instead, this cell produces a line of minus signs at the bottom of the column of numbers.

B4 Moves down one more row in preparation for typing the @sum function.

B5 Starts the @sum function through the opening parenthesis. When typing this cell in the macro, use a label prefix, or you'll be typing a formula here instead of a label that the 1-2-3 macro processor requires.

B6 Uses the Up arrow key to move one row up, then the End and Up arrow keys to move to the top of the column. We needed to use a single {up} first so that the End key would find the top of the column.

B7 This cell, a single period, anchors the cell pointer at the top of the column.

B8 Uses the End and Down arrow keys to move to the bottom of the column.

B9 The End and Down arrow keys of cell B8 moved us one row too far, because of the cell of minus signs we entered earlier. So, use the {up} command to move up a single row.

B10 Types the right-hand parenthesis to complete the @sum function, and types the Enter key to complete the formula typing.

B11 The end of the macro.

As you can see, 1-2-3 macros can be quite general if you plan them properly. To write this macro, however, you need to be very familiar with the way the End key operates--and when you're typing the macro, you don't get to see the effects of your macro commands. Many people run through the macro and write down the keys they pressed, developing a script to follow when creating the macro.