More tips on using Lotus products. (Good Ideas)

MORE TIPS ON USING LOTUS PRODUCTS

1-2-3/SYMPHONY

LEAVE FOOTPRINTS IN

@IF STATEMENTS

I needed to develop formulas that would be computed only if several different conditions were met. The figure below contains sales figures for January and February and nested @IF formulas in range D2..E5.

The formula in cell D2 is as follows:

@IF(C2[is less than]B2,0,@IF((C2-B2)[is less than]5,0,(C2-B2)*0.25))

Copy cell D2 to range D3..D5. the formula compares January and February sales. If February sales are less than January sales, no bonus is given. If sales increase by less than $5, no bonus is given. If the incresae in sales is more than $5, the salesperson receives a bonus equal to 25% of the amount of the increase. However, when the formula evaluates to zero, you cannot distinguish which @IF statement evaluated to zero. Enter the following formula in cell E2 to leave "footprints" during the processing of the @IF statement:

@IF(C2[is less than]B2,"Sales Decrease",@IF((C2-B2)[is less than]5,"Minimum Increase",(C2-B2)*0.25))

Copy cell E2 to range E3..E5. Now the formula evaluates either to a positive, calculated numeric value or to a text string that tells you the specific condition that prevented the salesperson from earning a bonus. This approach is helpful both in debugging and in making the spread-sheet more useful.

Connie H. Koehn Deere & Co. Moline, Ill.

INVALID RANGE NAMES

With 1-2-3 Releases 2/2.01 and 2.2 and with any release of Symphony, you can quickly identify range names that have been invalidated, as well as the location of the valid ranges. 1-2-3 and symphony use the upper-left and the lower-right cells to identify a range name. If you move data into one of these corner cells, or if you delete a row or column that contains one of these cells, you destroy the range name.

To find invalid range names, press slash (in Symphony, MENU) and select Range Name Create (or Delete). Press the NAME …