# Category : Lotus and other Spreadsheets

Archive : LTSTIPS1.ZIP

Filename : CALC.123

(PC Magazine Vol 3 No 17, Sept 4, 1984 User-to-User)

The computer can be left unattended for large 123 spreadsheets

with numeric calculations which take several minutes and have it BEEP

when finished. After starting the recalculation, press the Home key,

then PgUp five or six times. The buffered keyboard saves everything

subsequent to recalc until that is done and then executes Home and

PgUp. Since the cursor is moved to the top of the worksheet by Home,

the PgUp causes 123 to beep, thus notifying you that the recalculation

is finished.

-----------------------------------------------------------------

Buffer Advantage

(PC Magazine Vol 4 No 6 Mar 19, 1985 Spreadsheet Clinic)

The Home/PgUp procedure (see above) takes advantage of the

keyboard buffer, but it has the disadvantage of losing the pointer

position. You can take advantage of the keyboard buffer and retain

your cell location by asking 123 to perform an illegal task. After

pressing CALC, issue a "/" to get to the command menu and then press A

several times. This illegal command also produces a beep. If you want

to have a clear command line when you come back to the computer, also

press the ESCape key. This trick is more useful when issued from a

macro that would prompt for input after doing the CALC. You will hear

the beep and not have to do anything to restart the macro or return to

the current cell.

-----------------------------------------------------------------

Using the Calc Key

(PC Magazine Vol 3 No 18 Sept 18, 1984 User-to-User)

The Calc (F9) key in 123 is a handy device and has more functions

than one might expect. Calc allows full recalculation of a worksheet,

but pressing F2 (Edit), then the Calc key will calculate only the

single cell where the cell pointer is located. This can be used to

check the results of a change on one particular cell without

recalculating the entire worksheet. Simply move to the cell you want

to check, press F2 and then F9 to see the result in the control panel.

Be careful not to press Enter or any cursor-movement keys because the

value will replace the formula in the cell. Press the Esc key to

return to Ready mode.

Formulas whose values never change can be converted to numbers by

using the Edit and Calc keys. As shown on page 93 of the Lotus manual,

"to enter the value `ten times pi' (1) Type the formula 10*@PI, (2)

Press F9 to convert this formula to the number 31.41592, (3) Press

Enter to enter the number."

These helpful keys can also be used in macros where iterative

calculations or loops occur. For example, use the following sequence

in a macro to increment a counter value: {Edit}+1{Calc}~

This sequence adds 1 to the value of the cell, CALCs the new value,

then loads the value into the cell. There is no recalculation of the

whole worksheet, and you're left with a value rather than a formula in

the cell.

Another useful application of the Calc key is to check on the

value of a cell not currently displayed. For example, you might want

to check on a value that is way down at the bottom of a very long

worksheet. To check on the contents of cell A350, simply type +A350,

then hit F9. The value of the cell will appear in the control panel.

Remember to hit the Esc key to clean up, or the value will be left at

the position of the cell pointer.

Another trick is to grab a 123 row number and convert it to a

label for use in a macro. Say you want to write a macro that will

print out a column with an unknown number of rows. The keyboard macro

\X in the example below contains a named range called "ROW#?". In a

blank cell (C1), calculate the dept of column A, the @COUNT of the

whole column (no gaps). The four keystrokes Edit, Calc, Home and

apostrophe (') serve to turn the function into a left-aligned label.

It is Copied to the cell two rows lower where it provides the crucial

"bottom-line" number to the print section of the macro.

- - - - -

A B C D E F G

1 1 \X

2 2 {goto}C1~

3 3 +@COUNT(A1..A2048)

4 4 {edit}{calc}{home}'~

5 5 /c~ROW#?~

6 6 /ppcarA1..A

7 7 ======> RANGE NAMED ROW#? HERE

8 8 ~gq

9

-----------------------------------------------------------------

Conversion Macro

(PC Magazine Vol 4 No 7 April 2, 1985 Spreadsheet Clinic)

To take the results of a calculation and move them to a different

place on the worksheet, replace the data and perform the same

calculations again means that you can't simply move the calculated

fields to a different section; their values also will change when the

worksheet recalculates. The obvious solution is to use the {edit}{calc}

function key sequence for each cell after the cells are moved. For many

cells this procedure takes a great deal of time, and writing the macro

for multi-column processing is onerous. A simple solution for the long

edit-calc process is first to name the entire range to be processed and

then execute the macro below. This process can be a timesaver for big

ranges, especially given the execution time for an {edit} {calc} loop.

Editor's Note: To convert a large range of formulas to their

values, EDITRANGE is one of the most efficient ways to do it. This

macro takes advantage of 123's option, using the /File Xtract command,

to save a range to disk as Formulas or Values. The range is extracted

in Value form, then immediately brought back into the worksheet with

the /File Combine Copy Entire File command. Creating the range CURRCELL

as the beginning of the macro serves as a place-marker while the work

is being done. An alternative procedure is to use the /Data Query

Extract command instead of /File Xtract, as this process also evaluates

formulas as they are extracted to the Output range. You have the option

of using the same techniques manually rather than within a macro.

/rncCURRCELL~{bs}~

{goto}EDITRANGE~

/fxvGLOOPXXX~EDITRANGE~r{esc}

/fcceGLOOPXXX~

{goto}CURRCELL~

-----------------------------------------------------------------

"Stacking" Results of Repeated Calculations

(PC Magazine Vol 4 No 15 July 23, 1985 Spreadsheet Clinic)

In Vol 4 No 7 (above), a solution was offered to the problem of

saving repeated calculations in different areas of the spreadsheet.

Another approach uses 123's Columnwise Recalculation mode to store

results in a "stack." You can use this method to save the results of

succeeding "what-if" calculations.

Figure 1 is an example of how it works. In the range B7..B17 are

the results of a calculation. The calculation can be anything but in

this very simple example the variable in B5 was reproduced in B7, and

increased by 1 in each succeeding cell from B8 to B17.

Figure 1 shows the results (columns F, G and H) of three

successive iterations of this calculation using three different

variables. Obviously, the three variables were 10, 20 and 30. You

can save many different sets of results in the "iteration" columns, in

this case, six. Each time the worksheet is recalculated, column H

picks up the results from column B, and every other "iteration" column

picks up the values in the column to its right.

Figure 2 shows the formulas that make the technique work.

Columns C, D and E were omitted; their formulas are identical to those

in columns F and G: they point to the values in the column to the right.

The @IF logical operator is nothing more than a method to set the stack

values back to zero for another round of calculations. Thus, if the

zero-flag value in cell C3 (range name ZERO) is set to anything other

than 0, a recalculation of the worksheet will reset all values in the

iteration columns to 0. Note that this method will work only in manual

Columnwise Recalculation mode. In this mode, calculations are made one

column at a time, beginning with column A and moving left to right. In

the default recalc mode (Natural), all the values in the iteration

columns would immediately take on the values in column H.

Use this technique, for example, to store the results for the next

30 years of different possible refinancings of a home mortgage, or for

a scientific application such as the results of amino acid composition

data from different proteins.

Editor's Note: A flag was added to this model to indicate that the

stack is full. For a large worksheet, you may not notice that it was

full; succeeding calculations would push the results of the first

calculations right out of the stack, and their values would be lost.

In cell B21, the added formula is: @IF(D5>0,6,@FALSE) and the cell

was given the +,- format. So long as the iteration number for column D

remains zero, only a period appears in cell B2. As soon as D5 gets an

iteration value, six plus-signs appear in B2. Since the worksheet is

doing left-to-right, columnwise recalculations, the flag will not fly

until the value in D5 is 2 and column C also contains results. If the

flag were to the right of the iteration columns, it could point to C5,

instead of D5, and give the same results.

- - - - -

Figure 1: The stack in which calculation results are stored.

A B C D E F G H

1

2 Zero-Flag

3 0 I T E R A T I O N

4 Variable

5 30 0 0 0 1 2 3

6 ---------------------------------------------

7 30 0 0 0 10 20 30

8 31 0 0 0 11 21 31

9 R 32 0 0 0 12 22 32

10 E 33 0 0 0 13 23 33

11 S 34 0 0 0 14 24 34

12 U 35 0 0 0 15 25 35

13 L 36 0 0 0 16 26 36

14 T 37 0 0 0 17 27 37

15 S 38 0 0 0 18 28 38

16 39 0 0 0 19 29 39

17 40 0 0 0 20 30 40

- - - - -

Figure 2: The formulas that make the stack work.

A B F G H

1

2

3

4 Variable

5 10 @IF($ZERO,0,+G5) @IF($ZERO,0,+H5) @IF($ZERO,0,+H5+1)

6 --------------------------------------------------------

7 1*B5 @IF($ZERO,0,+G7) @IF($ZERO,0,+H7) @IF($ZERO,0,+B7)

8 +B7+1 @IF($ZERO,0,+G8) @IF($ZERO,0,+H8) @IF($ZERO,0,+B8)

9 R +B8+1 @IF($ZERO,0,+G9) @IF($ZERO,0,+H9) @IF($ZERO,0,+B9)

10 E +B9+1 @IF($ZERO,0,+G10) @IF($ZERO,0,+H10) @IF($ZERO,0,+B10)

11 S +B10+1 @IF($ZERO,0,+G11) @IF($ZERO,0,+H11) @IF($ZERO,0,+B11)

12 U +B11+1 @IF($ZERO,0,+G12) @IF($ZERO,0,+H12) @IF($ZERO,0,+B12)

13 L +B12+1 @IF($ZERO,0,+G13) @IF($ZERO,0,+H13) @IF($ZERO,0,+B13)

14 T +B13+1 @IF($ZERO,0,+G14) @IF($ZERO,0,+H14) @IF($ZERO,0,+B14)

15 S +B14+1 @IF($ZERO,0,+G15) @IF($ZERO,0,+H15) @IF($ZERO,0,+B15)

16 +B15+1 @IF($ZERO,0,+G16) @IF($ZERO,0,+H16) @IF($ZERO,0,+B16)

17 +B16+1 @IF($ZERO,0,+G17) @IF($ZERO,0,+H17) @IF($ZERO,0,+B17)

DIR=A:\

A>

-----------------------------------------------------------------

Custom Rounding

(PC Magazine Vol 4 No 24 Nov 26, 1985 Spreadsheet Clinic)

123's @ROUND function is fine for decimal rounding, but what if

you want to round a value to the nearest eighth, sixteenth, or even

four hundredth? In row 4 of the figure below is a general formula

that will round off a number by the rounding factor or your choice.

The table in rows 6 through 15 demonstrates this formula. Column

D contains the original numbers, and column E contains the results of

the rounding. The rounding factor in this particular case is 0.0625

(one sixteenth) in cell G7, which has the range name FACTOR.

A continuous-loop macro that makes it easy to enter numbers for

rounding is included in rows 17 through 19. Call the macro with the

Alt-R key combination. After you enter a number, the macro will format

the cell to four decimal places, move to the next row, and then start

over again. When you have finished entering numbers, get out of the

loop by hitting Ctrl-Break.

Later, if you want to round the same numbers by a different factor

just change the value in cell G7, and you will get an instant

recalculation. If you want your results to be values instead of

formulas, just add {calc} before the tilde at the end of the first

line of the macro.

C D E F G

3 GENERAL ROUNDING FORMULA

4 @INT(Number/Factor+.5)*Factor

5

6 ORIGINAL ROUNDED TO FACTOR

7 NUMBER ONE SIXTEENTH 0.0625

8 ---------------------------

9 0.8888 0.8750

10 0.8 0.8125

11 0.0777 0.0625

12 234.4567 234.4375

13 0.0313 0.0625

14 0.0312 0.0000

15 14 14.0000

16

17 \R @int({?}/FACTOR+.5)*FACTOR~ Enter number with {?}.

18 /rff4~~ Format cell to 4 decimals.

19 {down}/xg\r~ Move down, loop to start.

-----------------------------------------------------------------

Recalculating Values in Individual Cells

(PC Magazine Vol 4 No 26 Dec 10, 1985 Spreadsheet Clinic)

When you work with large spreadsheets, it sometimes takes 123 a

long time to recalculate. You can set the program to do manual

recalculation, so that it updates values only on command. However,

you may sometimes need to recalculate only a single cell or a small

range of cells, in which case it's a bother to have to wait for an

update of the entire spreadsheet.

The solution is to copy a cell, or range of cells, onto itself.

If you want to update the value of a single cell, move the cursor to

it and hit /C followed by two Returns. For a range of cells, define

both the source and target ranges as you would normally, but just be

sure they are in the same range. You'll get a quick recalc of only

the cells you need.

Editor's Note: This is a good solution to an annoying problem.

123 doesn't recognize it as a solution, however. Even if you copy an

entire spreadsheet onto itself and get a complete recalculation, you

will still have a CALC message at the bottom of your screen, meaning

that 123 thinks you still need to recalculate.

Very nice! Thank you for this wonderful archive. I wonder why I found it only now. Long live the BBS file archives!

This is so awesome! 😀 I’d be cool if you could download an entire archive of this at once, though.

But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: http://www.os2museum.com/wp/mtswslnk/