Category : Lotus and other Spreadsheets
Archive   : 123TUTOR.ZIP
Filename : TUTORIAL.ASC

A Brief Course in Advanced Lotus 1-2-3 Functions

1) Part I

There are plenty of references where one can turn to learn the basics of
spreadsheets, but very few teaches the advanced features of the various
programs, and the user is often left to discover them by him (her) self.

In this series of short courses, the less used, and often very useful
functions of 1-2-3 will be discussed. These are functions that makes the
difference between a ho-hum spreadsheet, and a great and fun tool to use.

We will cover the macro capabilities, the creation of menus, and finally
the word-processing function of 1-2-3, which are barely referred to, and yet
can be quite convenient.

The only knowledge that is assumed for this tutorial is that the reader
is able to move in the worksheet, copy ranges, erase ranges, and other very
basic procedure.

Since it is easier to demonstrate the different function through
examples, we will construct a hypothetical worksheet. For a change, this will
not deal with the best way to invest your money, sell stocks and bond, or
other financial applications. Instead we will use a medically flavored
problem.

As you know there has been a strong tendency in Europe, and in this
country, to monitor the evolution of the pregnancy with ultrasound. This
technique allow to see the baby in its mother's uterus and allows to measure
how much it is growing. In practice the obstetrician refers his (her) patient
to another physician that knows how to perform the examination. This later
physician then sends a report to the obstetrician. This report should contain
the name of the patient, the name of the referring physician, the measurements
that were obtained, and the conclusions based on the findings.

Although in real life, many more information are obtained, these will be
sufficient to demonstrate the function that we want to learn.



Let us start with a clean worksheet. If you are currently in another
worksheet, save it with:

/File Save (followed eventually by Replace)

then type

/Worksheet Erase

If you start afresh, you will be in front of a clean worksheet.

To start by an easy task, lets format the columns. The command to use is:

/Worksheet Column Set



1








and you then answer to the prompt, by replacing the suggested value (9),
by the values indicated below for each column:

A = 1, B = 22, C = 4, D = 4, E = 6, F = 1, G = 5, H = 5, I = 5, J = 1,
K = 5, L = 5, M = 5, N = 1, O = 2

You need to move to each column before setting its width.

Now let us go in column B, row 1, and introduce the label:

D A T A I N P U T A R E A

which will define the data input area. To make it more fancy, we can go
in column B row 2 (from now on I will refer to the cell address by its Lotus
name: B2), and introduce the label:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
which makes a neat underline. You might have wanted to use \~ instead in
B2. You would then have:

/Copy from B2 to B2..D2

Then introduce the following label in the different cells:

C3: ' Data
C4: '# 1
D4: '# 2
E3: ^Mean
G2: 'Predicted age
G3: ' in week
G4: '5th
H4: '50th
I4: '95th
K2: 'Predicted size
K3: ' in mm
K4: '5th
L4: '50th
M4: '95th
O5: '?


















2








Your worksheet should look like this now:

A B C D E F G H I J K L M NO
1 D A T A I N P U T A R E A
2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
3 Data Mean in week in mm
4 # 1 # 2 5th 50th 95th 5th 50th 95th
5 ?

Let us now go in F6 and introduce a vertical bar:
|

and then

/Copy from F6 to F6..F10

next we will do almost the same:

/Copy from F6 to J6..J10

Note that in some cases it may indeed be easier to type cell addresses
than to point. Pointing would have implied here that we would have to deanchor
the range by pressing BackSpace, moved to the correct location (J6), reanchor
the pointer, by pressing . and finally extend the range. Note that you do not
need to press . twice when defining a range. Once is sufficient, but I find it
disturbing to introduce one . and see two appear.

Up to now we have only introduced kid'stuff. Be patient. We still have a
few label to introduce so lets do that now:

B6: " Biparietal Diameter (mm)
B7: "Abdominal Diam #1
B8: "Abdominal Diam #2
B9: 'Abdominal Perimeter:
B10: "Estimated Fetal Weight (g) -->
B11: 'EFW (pounds + ounces)
D11: 'lbs.
F11: 'ounces

A word of explanation about these labels. The "Biparietal Diameter" (BPD)
is more or less the distance between the two ears of the baby. It is a well
established measurement that is used among other thing to find the age of the
baby, and it is used in the formula to derive the baby's weight. The abdominal
diameters are two diameters measured at the level of the baby's liver, which
are obtained (usually) perpendicular to each other. The abdominal diameters
are mainly used in the estimation of the baby's weight. The abdominal
perimeter (AP), as indicated by its name, is the perimeter of the baby's
abdomen, and is obtained from the two abdominal diameters by a simple formula
that we will see in a second. The estimated fetal weight (EFW) is an
approximation of the weight of the baby. It is derived from the BPD and the
AP, through a very complicated formula that we will see in a moment. Finally,
since most american patients are not too familiar with the metric system, the
gram in which the EFW is expressed are converted into pounds and ounces
(rounded to the nearest ounce).



3








Your worksheet should look like this now:

A B C D E F G H I J K L M NO
1 D A T A I N P U T A R E A
2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
3 Data Mean in week in mm
4 # 1 # 2 5th 50th 95th 5th 50th 95th
5 ?
6 Biparietal Diameter (mm) | | |
7 Abdominal Diam #1 | | |
8 Abdominal Diam #2 | | |
9 Abdominal Perimeter: | | |
10 Estimated Fetal Weight (g) --> | | |
11 EFW (pounds + ounces) lbs. ounces
12

Please note that we have prepared to take two measurements for each
parameter: Data 1 and 2. All calculation will in fact be based on the mean of
these to measurements as referred to in column E. 1-2-3 provides a "mean"
function, which is @AVG(range). We could enter:

E6: @AVG(C6..D6)

Note that 1-2-3 is smart enough not to average data with empty cells. If
our user only introduce one of the two data, the mean will still come out
correct.

We can refine this formula by rejecting values that are not
physiologically possible, because they are too big for instance. The BPD
should not be larger than 105 mm. To introduce this lets place a condition
that the cell only displays the value if it below a certain limit. To do that,
press EDIT (F2), then HOME, and introduce the appropriate changes as described
below. When done press return.

E6: @IF(@AVG(C6..D6)<105,@AVG(C6..D6),@ERR)

which means: If the mean is below the maximum that we have set up, let
the cell represent the mean (the second term in the formula), otherwise (the
last term in the formula) let us display an error. This would do the trick
nicely. It would be nice that the cells display something like "Please
remeasure" but @if function in 1-2-3 can only deal with numbers, @NA, and
@ERR. In certain conditions we would like, however, to avoid this unesthetic
ERR message. We could trap it, and replace it by a nicer 0. There is a
function called @ISERR that test if the cell contains an error or not. Its
general formula is

@ISERR(condition, response if condition true, response if condition is false)

We can therefore modify our formula to read:

E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6))

Now you may want to play with the worksheet. First save it under the name
BABY by typing:



4








/File Save BABY ~

The ~ sign refers to the key. You can now enter values in C6 and
D6, and observe the reaction of the worksheet. When ready for more, let us
save a lot of efforts, and do:

/Copy from E6 to E7

Which introduces the same formula for the next line. We have a small
modification to do: the upper limit for normal here in 120 mm and not 105. So
press Edit (F2), then Home, then a couple of TAB and introduce the change. Now
you can copy the correct formula into the next line:

/Copy from E7 to E8

You should have:

E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6))
E7: @IF(@ISERR(@IF(@AVG(C7..D7) <120,@AVG(D7..C7),@ERR)),0,@AVG(C7..D7))
E8: @IF(@ISERR(@IF(@AVG(C8..D8) <120,@AVG(D8..C8),@ERR)),0,@AVG(C8..D8))

To be sure that the data are displayed with only one digit after the
period, go to C6 and type

/Range Format Fixed and enter 1 to replace the suggested 2

The cells E6 to E8 should now read:

E6: (F1) @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6))
E7: (F1) @IF(@ISERR(@IF(@AVG(C7..D7) <120,@AVG(D7..C7),@ERR)),0,@AVG(C7..D7))
E8: (F1) @IF(@ISERR(@IF(@AVG(C8..D8) <120,@AVG(D8..C8),@ERR)),0,@AVG(C8..D8))

If you want to get rid of the values that you have introduced in the data
area C6..D8, just type

/Range Erase C6..D8

We can now introduce a simple formula. The formula for a circumference
(C) is: C = Pi*D where Pi is the greek letter, and D the diameter of the
circle. Let us assume that similarly, the abdominal perimeter (AP) is equal to
the mean of the two abdominal diameters multiplied by PI (the greek letter).
We thus would have:

C9: (E7+E8)*@PI/2

This is fine, except that we could improve it slightly by using a value
for @PI/2 since this is a constant that will never change. To do that, lets go
into C9, and correct the formula to:

C9: @PI/2

and then while still in C9, press then . This will convert
the formula into its value. This will save 1-2-3 one computation, each time
the worksheet is recalculated. The value should appear as:



5








C9: 1.5707963268

We can now complete the formula by pressing again and completing
the formula to read:

C9: 1.5707963268*(E7+E8)

We can now introduce the formula for the estimated fetal weight. This is
unfortunately a long and complicated formula. This formula was developed by
Sheppard et al. from Yale University.

E10:
10^(-1.7492+0.166*(E6/10)+0.046*(C9/10)-2.646*((C9/10)*(E6/10))/1000))*1000

I know it looks terrible, but that's why having a computer is great: you
introduce it once and you can use it forever ! Another thing is definitely
puzzling. How come that the cell E10 displays the value: 17.81 when we have
not introduced any value ? This is due to the fact that the equation starts
with an independent term 1.7492, and that when everything else is equal to
zero, the cell provide the value of: (10^(-1.7492))*1000.

The formula as it is now is (almost) correct. We will see in a moment,
that it still contains a small bug. It is however barely readable. These cell
addresses are difficult to read. To change that, let us go in cell E6 and
type:

/Range Name Create

and give the name

BPD

The second serve to define that only cell E6 in the range.
Similarly lets go in cell C9 and type:

/Range Name Create

and give the name

ABD_PER

You might be tempted to have given a name such as AP. Try to avoid this
practice. First it may be too short to be meaningful, and second, you might by
error give it a number such as AP1, AP2... if you have many range referring to
similar information. You would then be in deep trouble. Those name are in fact
cell addresses, and 1-2-3 will use the value in the cell AP1, not in the range
that you meant to give this name ! This is an important pitfall and be careful
to remember it: it might otherwise be quite difficult to debug your formulas !
Now go in cell E10, and surprise the formula is now a lot more clear. It
reads:

E10:10^(-1.7492+0.166*(BPD/10)+0.046*(ABD_PER/10)-2.646 *
((ABD_PER/10)*(BPD/10))/1000))*1000

What about this pitfall that I mentioned earlier ? Go into cell C6, and


6








introduce a value. Immediately 1-2-3 answers by giving you a value for the
EFW. This is incorrect, since the formula requires that you also provide the
value for the abdominal diameters. You therefore have a wrong answer in your
EFW, although nothing tells you about it in the worksheet. This is a very
important point. The worksheet is totally stupid. It calculates whatever you
want, but it is your responsibility to be sure that the calculations are based
on the correct values, and that all values needed for the calculations are
present. The solution is however quite simple. We have to introduce a formula
that says, if any of the data is absent, do not calculate, and display 0. This
is the formula:

@IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE,otherwise give the result of the formula)

The formula would then become:

E10:@IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE,
@ROUND((10^(-1.7492+0.166*(BPD/10) +
0.046*(ABD_PER/10)-2.646*((ABD_PER/10)*(BPD/10))/1000))*1000,0))

The @ROUND function is introduce to avoid value smaller than a gram: the
formula is not that accurate, and it would give a false sense of precision.
Let us give it a name by typing:

/Range Name Create

and give the name

EFW

Our formula is now complete, and you can enjoy it by entering a few value
in C6..D8. It is also a good time to save your worksheet if you have not taken
the habit to do that frequently. By the way, when you work on a difficult
worksheet, it will not be impossible that a worksheet that was originally
correct, become all messed up at some point. If you only save it, and replace
the old worksheet, chances are that soon or later you will have lost a
previously correct formula. So it is a good habit to save successive version,
such as Stage1, Stage2, Stage3 etc ...

Another pearl: Suppose that you would like to have a copy of a
complicated formula such as the one in E10, somewhere else in the worksheet.
What would you do: /Copy it ? Well yes and no. If you /Copy it, and have not
be careful to use absolute addresses (the $ sign in front of both the letter
and the number portion of the cell address) you will end up will a complicated
formula, that is totally garbelled up. If it is far from its original
location, it might be a real pain to fix it. A much simpler solution is the
following. Go into the cell that you want to copy. Press EDIT, than HOME, '
and then RETURN. What you have done it transform the formula into a very long
label. You can now /Copy this label where ever you want. To make use of it,
just do the reverse maneuver: press EDIT, HOME, then DEL, and then RETURN. Of
course you have to do that in the original cell too.

We have two small formula to include now. They will convert the EFW
expressed in grams into its value in pounds and ounces:

C11: @INT(EFW/453)


7









This find the largest integer of the EFW divided by the conversion factor
between pounds and gram which is 453. Let us give C11 a name by typing:

/Range Name Create

and give the name

LBWGT

We now have to calculate how many ounces there are. To do this we will
take the difference between the EFW, and its larger integer in pounds. The
rest is what should be converted in ounces. The number of ounces will be the
differences divided by the conversion factor from grams to ounces, which is
28. And we also would like to round the value, to avoid false precision. The
formula will then be:

E11: @ROUND((EFW-LBWGT*453)/28,0)


You have noticed that we haven't filled the formula for the columns on
the right of F. This will come later: we first need some other parameters.

Before saving the worksheet again, lets introduce two last labels:

B13: 'C O M M E N T S:
B14: '~~~~~~~~~~~~~~~~

Your worksheet should look like this now:


A B C D E F G H I J K L M NO
1 D A T A I N P U T A R E A
2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
3 Data Mean in week in mm
4 # 1 # 2 5th 50th 95th 5th 50th 95th
5 ?
6 Biparietal Diameter (mm) 0.0 | | |
7 Abdominal Diam #1 0.0 | | |
8 Abdominal Diam #2 0.0 | | |
9 Abdominal Perimeter: 0
10 Estimated Fetal Weight (g) --> 0 | | |
11 EFW (pounds + ounces) 0 lbs. 0 ounces
12
13 C O M M E N T S:
14 ~~~~~~~~~~~~~~~~











8








2) Part II

In the previous part, we have dealt exclusively with the introduction of
numbers. We now have to introduce the "administrative data", such as the name
of the patient, physician, etc... The first part will be exceedingly simple:
we will set the column width. See the first part if you are not too sure how
to do it. Let us set the column width to:

P = 25, Q = 3, R = 1, S = 3, T = 1, U = 4, V = 10, W = 10, X = 9

Then, just introduce the following series of labels:

P1: "Patient's information:
P2: "Last Name :
P3: "First Name :
P4: "Birthday (MM/DD/YY) :
P6: "Referring physician:
P9: "Operator:
P12: "LMP (MM-DD-YY):
P13: "Nbr of days elapsed:
P14: "Nbr of weeks elapsed:
P15: "Due Date (from LMP) :
P17: 'Please move the cursor with the <-- and --> arrow
P18: 'When you have seen the age, type an extra

To make it more fancy go in P1 and type:

/Range Unprotect

which will display the label in high intensity. Then do the same in P6,
and P9. Then introduce a

'/

in R4, T4, R12, T12. Do not forget the apostrophe, or else you initiate a
command. Finally introduce the following labels:

W1: '<-- Today's Date
W4: '<-- Patient's age
W13: 'days
W14: 'weeks
















9








Your worksheet should now look like this:


P Q R S T U V W X
1 Patient's information: <-- Today's Date
2 Last Name :
3 First Name :
4 Birthday (MM/DD/YY) : / / <-- Patient's age
5
6 Referring physician:
7
8
9 Operator:
10
11
12 LMP (MM-DD-YY): / /
13 Nbr of days elapsed: days
14 Nbr of weeks elapsed: weeks
15 Due Date (from LMP) :
16
17 Please move the cursor with the <-- and --> arrow
18 When you have seen the age, type an extra


This is fairly self explanatory, except maybe for the LMP. LMP is a
common abbreviation that refers to the Last Menstrual Period. This is an
important date since this is how the length of the gestation is counted. It is
true that the baby is only conceived around 15 days after the first day of the
LMP, but it is easier to count the gestation from the LMP, since most pregnant
patients know the date of the LMP, and few the date of the conception. As you
suspect, we will introduce a few formula in here too. You might expect that
the first would be something like @TODAY in V1. Well not quite. If we were to
do that, we would always have the current day in the worksheet, and if we were
to reload in memory a worksheet save a few days or weeks earlier, the new date
would erase the old date, and mess up all the calculations saved in the old
worksheet. What we need, is more like a "stamping" of the date. We will see
the trick used for that later. For now lets give the name DATE to the range
V1. See in the earlier portion of this article if you are not too confident
about how to do this.

The next obvious formula that you want to introduce is the one that
calculates the patient's age. But before doing that lets name the 3 ranges Q4,
S4, U4, with the following names:

Q4: MBIRTH
S4: DBIRTH
U4: YBIRTH

The formula for the age of the patient is today's date - the birthday of
the patient, divided by 365. Since the age on someone is usually expressed in
years (for instance 34), without the decimal portion of the year (not as
34.68), we have to trim this with the @INT function. The formula then becomes:

V4: @INT((@TODAY-@DATE(YBIRTH,MBIRTH,DBIRTH))/365)



10








In fact it would be more correct to use 365.25, instead of 365, to take
into account the 29th of February that occurs every 4 years. But, again, this
is irrelevant precision. As for the formula to obtain the EFW earlier, we have
to make sure that all portion of the birthday are filled. If one was missing
the calculation would be meaningless, and we would like the cell to display
that the value is not available (@NA). We can therefore correct the formula to
become:

V4: @IF(YBIRTH=0#OR#MBIRTH=0#OR#DBIRTH=0,@NA,
@INT((@TODAY-@DATE(YBIRTH,MBIRTH,DBIRTH))/365))

It will probably strike some of you that this formula still allows a
potential pitfall. Think about it. What if you introduced as the year portion
of the birthday as being 23 (short for 1923). What would happen ? Nothing ?
Well that is the problem. The medical literature as never recorded a pregnancy
in a 62 years old woman. So this formula does not account for this problem. Of
course we could include a statement such as @IF age > than 45 years display
ERR. That is to crude for our great program, and we will see how we can solve
this later. Make a note (in your SideKick, or whatever else to check for this
later.

The next formula that we can introduce is the one in V12, that will find
the Lotus 1-2-3 serial number of the days of the LMP. This one is a formula
straight out of the box:

V12: @DATE(U12,Q12,S12)

Since the serial number type of display, is difficult to read, for most
of us, lets transform it into something more understandable, by using the
following command:

/Range Format Date 1

And lets give it the name LMPDATE. By now you should be expert at giving
names.

From the value obtained in V12, we can compute how many days have elapsed
since the beginning of the gestation. The formula is very simple, it is
today's date - the date of the LMP. This is really of example where the Lotus
date arithmetic is tremendous. The formula is:

V13: +DATE-V12

However such a simple formula does not account for error in the
introduction of the values. We can trap them in a simple way by checking that
the number of days that have elapsed, is less than the maximal number of days
of a normal gestation. Since a normal gestation is 40 weeks (280 days) and
that the baby may be as much as 14 days late, if we assume 320 days we should
be able to cover even the most rare case, and the patients whose ovulation are
delayed. The number of days that have elapsed, should also be more than 0 of
course. Thus the enhanced formula:

V13: @IF((DATE-V12)>0#AND#(DATE-V12)<320,DATE-V12,@ERR)

Another relation which is easy to obtain, and is used in many


11








calculations in the number of weeks that have elapsed, in other word, the age
of the baby. This formula is straight forward:

V14: +($V$13/7)

Since we do not want all the number behind the period, we will get rid of
them. The way we have gotten rid of unnecessary numbers previously was by
taking the @INTeger of the number. We cannot do this here, since as mentioned
earlier, this value is going to be used in some following calculations. What
we in fact want is to keep the value as it is, but only show the integer of
the number of weeks. This is done by formatting the cell

/Range Format Fixed 0

Since this is the age of the baby, why not call this cell AGE, this will
make further calculations a lot easier.

/Range Name Create AGE~

We can also calculate when the baby should be expected. This is commonly
called the Due Date.

V15: (D1) @IF(@ISERR(LMPDATE),@ERR,@IF(@ISERR(V13),@ERR,LMPDATE+280))

You will have noticed that the cell will only display a result, when two
conditions are filled: the LMPDATE is available, and is valid, as checked by
the number of days elapsed (V13). Again you should fixe the cell attribute to
display the date format #1, as you previously did in V12.

Your screen should look like this now:



P Q R S T U V W X
1 Patient's information: 01-Mar-85 <-- Today's Date
2 Last Name : 1
3 First Name :
4 Birthday (MM/DD/YY) : / / NA <-- Patient's age
5
6 Referring physician:
7
8
9 Operator:
10
11
12 LMP (MM-DD-YY): / / ERR
13 Nbr of days elapsed: ERR days
14 Nbr of weeks elapsed: ERR weeks
15 Due Date (from LMP) : ERR
16
17 Please move the cursor with the <-- and --> arrow
18 When you have seen the age, type and extra





12








This screen is now finished. It contains important information that we
can now use to complete the right portion of the screen that we developed in
the last session. As you remembered, we left the first screen to look like
this:


A B C D E F G H I J K L M NO
1 D A T A I N P U T A R E A
2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
3 Data Mean in week in mm
4 # 1 # 2 5th 50th 95th 5th 50th 95th
5 ?
6 Biparietal Diameter (mm) 0.0 | | |
7 Abdominal Diam #1 0.0 | | |
8 Abdominal Diam #2 0.0 | | |
9 Abdominal Perimeter: 0
10 Estimated Fetal Weight (g) --> 0 | | |
11 EFW (pounds + ounces) 0 lbs. 0 ounces
12
13 C O M M E N T S:
14 ~~~~~~~~~~~~~~~~


As we mentioned earlier, the BPD can be used to predict the age of the
baby, and we should now be able to complete this. Besides, since we now know
what is the age of the baby (from the LMP), we can predict what should be the
size of the BPD, and what should be its weight. This is important, since when
the user of our worksheet will introduce his numbers, it would be nice to tell
him what size to expect, so that a grossly abnormal value can be remeasured,
or verified. The predictions, however, are never absolute. There are always
baby that are destined to be bigger or heavier than other. Therefore we should
take that into account. You suspected that by the fact that we have introduced
label for the 5th, 50th (mean) and 95th percentile confidence limits. What
this means, is that there is 90% chances that the measurements obtained on the
baby that is investigated will fall between the lower and higher confidence
limits. Should it be falling outside, one might suspect that something is
abnormal with the baby.

The equation to predict the normal size of the BPD is:

L6: -19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3

Here we can see the advantage of the use of name ranges. The formula
appears a lot clearer, than if cell addresses had been used. If when you press
return, 1-2-3 beeps at you, and you have checked that the formula is indeed
correct, the problem is that you have forgotten to name the range, as
suggested previously. It would be a pity to have to press ESC to go name the
range: this would erase all the equation that you have entered, a pretty
tedious job. If you know the cell address you could edit the formula by
replacing AGE by the cell address. To show you another technique, O will not
remind you of what the cell address was. The trick previously used to copy a
formula at long distance without having to change all the cell addresses, can
be used here again. While in the EDIT mode, press HOME, and add an apostrophe
at the very beginning of the formula. As the upper right indicator on your
screen mentions, by doing that you have transformed a formula into a long


13








label, that 1-2-3 can now accept. Now that you have saved your work, go and
/Range Name the cell V14 and call it AGE. When you return to L6, press EDIT
again, HOME, and delete the '. Now 1-2-3 will accept the formula. Remember
this trick. It is very useful when you introduce long formula. Some formula
can reach 240 characters. You won't be able to see them entirely in your
screen: they will scroll right or left. Such long formula can be terrible to
debug, and it is not always possible to split them in shorter pieces. In such
cases the above trick allows you to get out of it, you can then use the
/Print Printer Option Other Cell-Formula Quit Range ~ Go Quit
to get a printed copy of the formula, whic is now a label. Often the problem
will be unbalanced parenthesis, or incorrect use of the #AND#, #OR# kind of
parameters.

After this slight disgression, let us return to our formula in L6. As you
have noticed, it appears that there is a bug: although no age has been
introduced, it displays the value -19.634. Again this is the independent term
of the polynomial equation. This demonstrates that polynomial equation should
not be used outside their limit of definition. To trap this error lets edit
the formula to make sure that nothing is displayed in the cell if the AGE is
less then 12 weeks:

L6: @IF(AGE<12,0,-19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3)

We can now introduce the confidence limits. These are equal to the age,
less (or plus) 1.66 standard deviations:

K6: @IF(AGE<12,0,L6-(1.66*2.75373))
M6: @IF(AGE<12,0,L6+(1.66*2.75373))


Following exactly the same principle, we can also introduce the predicted
age from the BPD in cell H6, as well as the confidence limits in cells G6 and
I6:

H6: (G) @IF(E6=0,0,2.810638+0.4665383*E6-0.003687003*E6^2+0.0000282043*E6^3)
G6: (G) @IF(H6=0,0,H6-(1.66*1.08))
I6: (G) @IF(H6=0,0,H6+(1.66*1.08))

We can add one more "bell and whistle" by having the worksheet
automatically flag for us if the value of the BPD is within the predicted
size, or if it outside. The formula is:

O6: @IF(E6=0,0,@IF(E6M6,1,0))

which would display a "1" if the BPD is outside the confidence limits, or
a 0 if it is within.

We can now introduce the equation to determine the predicted estimated
fetal weigh. This is a difficult equation, it contain some exponentiation, and
is almost a mile long:

L10: 10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2-0.0000202932*AGE^3)

We have to make sure that the cell will not display wrong information by
modifying it to look like:


14









L10: @IF(AGE<15,0,10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2
-0.0000202932*AGE^3))

and the standard deviation would be:

K10:@IF(L10=0#OR#AGE<15,0,L10-(10^(-2.72529+0.4042264*AGE-
0.01048152*AGE^2+0.000098495*AGE^3)))

M10:@IF(L10=0#OR#AGE<15,0,L10+(10^(-2.573974+0.4008528*AGE-
0.01031992*AGE^2+0.0000961377*AGE^3)))

Wheew, this was the last equation. By now no equations should ever threat
you anymore ! Lets call the 5th percentile (K10) LOWEFW, and the 95th
percentile (M10) HIGHEFW, using the /Range Name Command. We can now fill the
last formula, that will check whether our baby is within or outside the normal
weight predictions for its age:

O10: @IF(EFW=0,0,@IF(EFW=0#OR#HIGHEFW=0,0,@IF(EFWHIGHEFW,1,0)))

You now have finished all the input screens, and your first screen should look
like:


A B C D E F G H I J K L M NO
1 D A T A I N P U T A R E A
2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
3 Data Mean in week in mm
4 # 1 # 2 5th 50th 95th 5th 50th 95th
5 ?
6 Biparietal Diameter (mm) 0.0 | 0 0 0 | ERR ERR ERR |0
7 Abdominal Diam #1 0.0 | | |
8 Abdominal Diam #2 0.0 | | |
9 Abdominal Perimeter: 0
10 Estimated Fetal Weight (g) --> 0 | | ERR ERR ERR |0
11 EFW (pounds + ounces) 0 lbs. 0 ounces
12
13 C O M M E N T S:
14 ~~~~~~~~~~~~~~~~


Well done. But that is not too terrifically exciting to move all over
these two screens to introduce data. Isn't there any better system ? Of course
there is, and this will be the third part. Do not forget to save your
worksheet if you have not taken the habit to do it every 5 minutes or so.












15








3) Part III: Using Macros

In the first two installments we have prepared a fairly complicated sets
of data entry forms, in which most of Lotus 1-2-3 formula and error trapping
have been used. To complete this quick teaching of the advanced features of 1-
2-3, will will now use an extensive series of macro to make the data entry
easier, and even to use 1-2-3 for word processing !

Lets start by retrieving the worksheet. Press HOME, you should see the
data entry form for the measurements. Press TAB once. You now have the
patient's information entry form. Pressing TAB again bring you in an empty
screen. For a change we will accept the default size of the column. Columns Y
to AF should be visible now. Go in Z1 and type:

Z1: 'MACROS

Let us go in cell Y4 and enter the label:

Y4: '\M

and in

Z4: '{GOTO}message~/XMmenu~

What does this last label means ? Well this will be our first macro. Note
that it starts with an apostrophe. Although it is not always necessary to do
so, I would strongly advise you to do so. A 1-2-3 macro is a series of
instructions that can be invoqued by pressing a single combination of ALT and
one letter. The {GOTO} is the way to place in the macro one of the special
keys of the IBM PC keyboard. You simply type its name between the curly
brackets. Your 1-2-3 manual has the list of all the keys that can be used in
the macro, and the macro spelling of their name. Here we ask the program to go
to a place called "message". This is simply a range that we will define that
will contain a message. This is a handy technique. If you want to tell the
user of your worksheet long stories about what they should do at what moment,
you can include a series of message in the worksheet, and send the program the
these location. Alternatively, you can /Range Erase the message range, and
include its content within the macro. The macro will then type it for you.
This last technique is slightly slower, but more flexible. Please note, and
this is crucial, the ~ sign, just after message. This is the spelling of the
key RETURN, in 1-2-3 language. Since you cannot include a Return within the
macro, (this would terminate the entry) 1-2-3 has given a simple name to the
Return key, which is by far the key that you will need the most. You have to
include a ~ every time that you would have pressed returned if you were to do
the series of command yourself. An easy trick is to perform the series of
commands yourself noting VERY carefully what key you press. Be extremely
careful about ~ and spacebar. These are the two major offenders, and 1-2-3 is
absolutely not forgiving.

Alternatively, if you are a Prokey User, you can store the macro in a
Prokey key, exit from 1-2-3, write the new Prokey macro (prokey
filename.pro/w), and then import it into your worksheet with the /File Import
Text. You will have to change the key names, delete the and ,
and the *. This may look tedious, but it can save a lot of time for
complicated macro. As you can see there is something more in the label we


16








introduce: /XMmenu~. This is a special macro instruction that tell 1-2-3 that
a special command (/X) is about to come. The special command in this case is a
Menu (M) called menu. This may appear terribly confusing ! In fact it is
fairly simple. 1-2-3 allows you to make your own menus just like the one you
see when pressing /. And that is what we are about to do.

Another very important thing to know, is that a macro is just a label,
until you give it a name. This name MUST be \ plus one letter, and only that
(for one single exception that we will see later). The letter can be upper or
lower case, it does not matter. Now it starts to make sense why we introduced
this mysterious \M label in Y4. This is going to be the name of the macro that
we have introduce in Z4.

It is not mandatory to use this convention, but it is quite convenient to
write down the name of the macro in the left adjacent cell. The cell that we
will name, however, is the cell that contains the macro. Lets do it:

/Range Name Create \M~~

There are two ~. The first terminates the name, the second, defines the
range. You could cover more than one cell with the macro range for the name.
This is, however, not recommended. If you move this range around, the range
might be stretched, and complications are bounded to occur.

Now go in Y5 and type:

Y5:'Name

This will be the first entry in our future menu. Since we said in the
previous macro, that processing of the macro should continue (/X) in a menu
(M) called "menu", it is logic that we now give the name "menu" to cell Y5.
Again do a

/Range Name Create Menu~~

We mentioned this "message" range, but we have not defined it yet. So
press GOTO (F5) and answer P41. You will arrive to a virgin section of the
worksheet. Type:

/Range Name Create Message~~

then go into P42, and type:

Please select an option from the above menu.

Then press GOTO again, and answer Y1. If everything went smoothly, press
and you should see a new menu in place of the familiar Worksheet ...
menu. This one just says Name. Although this is great, it is not yet terribly
exciting ! Press CTRL BREAK and GOTO Y1. There move to Z5, and enter:

Z5: 'Data
AA5: 'Comments
AB5: 'Report
AC5: 'Prepare
AD5: 'Exit


17









These will be the future tasks that we want our worksheet to perform.
Since the name are quite descriptive for you, you may not want to write
anything else, but since we want anyone to be able to understand how our
worksheet works, lets introduce some information:

Y6: 'Enter administrative data for the patient
Z6: 'Enter measurements
AA6: 'Enter your comments and suggestions regarding this examination
AB6: 'Print the report
AC6: "Prepare for a entering a new patient WARNING: this erase the current
data
AD6: 'Quit the program, and return to LOTUS Access Menu

Now press to see our progress. Isn't that great ? It looks just
like the real thing doesn't it ? This show an important feature of the menu
creation in 1-2-3. You have to give one name per option, one option in each
adjacent cell (no more than 8), the total length of the option of the menu
cannot exceed 64 characters (otherwise it would bump into the right upper
corner command indicator), and there must be a blank cell to the right of the
last option. Secondly, the second line must either contain a word of
explanation, or be blank. Some of you probably had the curiosity to move in
this menu. Note that as with the real 1-2-3 menu you can either point to, or
press the first letter. If you want to give the first letter, no two option
can start with the same letter. That why this macro contains the option
"Prepare" instead of the more logical "Clear", which would have interfere with
"Comment" or "New" that would have interfered with "Name". If two options
start with the same letter, the first will always be selected if you select by
letter. Pointing could work however. You now realize why when you want to
/File Extract, you have to press /File Xtract, and not /File Extract, which
would have collided with the /File Erase, also on the same menu. This has
probably erase more files than any other peculiarity of 1-2-3. If you can
devise a way to rename this Xtract command, call Mitch, he might want to
included it in the next revision of 1-2-3.

Those audacious among you that have selected an option in the menu, will
have been disappointed: the only thing that happened was that the upper right
status indicator changed from CMDREADY to READY. Not to impressive. By the way
did you noticed this CMDREADY ? When you are executing a 1-2-3 macro, the
letter CMD (for command) are placed in front of most of the usual commands
(but not WAIT for instance).

Why did nothing happened ? Why didn't we automatically start entering
data ? Well we didn't told 1-2-3 what to do. This is the third important point
to know about the menu in macro. We have seen what the two first line must
contain, now we have discovered that in order for 1-2-3 to do something, we
have to store in the next cell in the same column what should be done. This is
very important. A macro, can extend other a very long series of cells,
provided that they are contiguous, and on top of each other. Execution will
stop as soon as 1-2-3 arrives at a blank cell. That makes sense in fact ! Also
note that although we only named one cell (Y5) with the name, all cells to the
right (only in cases of menu), and all cells below those are included in the
same menu.

Enough theory. Lets introduce those famous instructions that we want 1-2-


18








3 to do for us. And lets start by the entering the patient's name and related
information.

Y7: '{GOTO}p1~/XIpatname<>1~/XG\M~

This tells 1-2-3 to go to cell P1, and to execute a special macro command
(/X) which is "Ipatname<>1~/XG\M~". I stands for If, and "patname' is a range.
A command language without IF is not a command language. The way the /XI
command works is the following. If the condition that is tested (in this case
if "patname" is different from (<>) 1) is true, than proceed and execute what
is on the same line (/XG\M~), otherwise, drop to the next line, and execute
the commands on the next line. The /XG\M~ is a macro command (/X) that says to
Go (G) to another cell, or range, or macro (\M) and execute what is inside.
Note that you should not forget the ~ !!! Very important !

Now that we know what this is all about, what does it mean ? When we
introduce the name of the patient, we do not want that by pressing Name (which
will be the first choice of our menu) by error, to have to reenter the name
again. So we need to see if the patient's name as already been entered. If it
has, than we just return to the menu, no harm done. If it has not, than we
should enter it. We would therefore like to be able to test if there is a name
in the cell Q1 (go name it "patname", then come back). However, 1-2-3 does not
let us do this. If we ask the value of a label, it will always answer 0, which
is unfortunately the same answer that we would receive if the cell is empty.
This does not allows us to make the test we need. The only solution is
therefore, to place a value in the cell ahead of time, and we choose 1, in
this case. Then test if the cell has a value different of one. If we introduce
a label in it, the value 1 will be replaced by 0. This will allow us to
perform the test, but I am sure that quite a few of you must think "This is
totally, crazy, it is twice more work !". Well yes, and no. It is indeed
another step, but you will see later that we can have 1-2-3 do the job for
you. That is the second note you should write down for yourself, and see how
we will solve it later.

Note that by using the {GOTO}p1 instruction, we managed to have the label
stored in P1 to show in the left upper corner of the screen, which makes it
easier for the user to understand what he is doing.

Finally, note the convention that upper cases are used to type 1-2-3
instructions or key names, while lower cases are used to type range name, cell
addresses, and all the rest. Although this is not mandatory, it will make the
reading of your macro a lot easier. For instance a macro such as:
/PPOOUOCQRPOOR~GQ makes less sense than /PPOOUOCQRpoor~GQ.

Now we can introduce the next commands:

Y8: '/XLPatient's LAST NAME:~patname~
Y9: '/XLPatient's FIRST NAME:~patfname~

They introduce a new type of special macro commands: the /XL. This prompt
the user to input a Label (L), and it stores it in a cell whose address
follows the ~. The prompt can contain some information about what should be
entered, in this cases: Patient's LAST NAME:, and Patient's FIRST NAME:, and
store the information in the cells "patname" and "patfname". We have already
named Q2 as patname, go and name Q3 as patfname. The /XL command accept


19








anything including numbers and blank responses (a simple return). This is
important to remember. There is no way to make sure that a user will fill a
cell that he MUST fill. The trick described earlier: preload the cell with a
value, ask the question (/XL), then test the cell to see if the value is
different from the value that you preloaded, will not work, since the user
could type Return, to the question, therefore erasing the preloaded value, and
fouling the test. This is probably one reason why innocent people bought
Symphony, later to discover that it was so impossible to use !

The next command is very similar:

Y10: '/XNMONTH of birthday (type 0 if unknown):
~mbirth~/XImbirth>12~/XGmonthbirth~

Since it is more than 78 characters it did not fit on a standard page,
and it was therefore placed on two lines. Here we use the command /XN. This is
the similar command to input a number, however, there is a major difference.
The user cannot simply press Return to go to the next question. He must input
a value. We therefore need to provide an escape mechanism in case our patient
does not want to reveal her age. Inputing 0 will do. Since there is no 0
month, it should not introduce any confusion in the further calculation. If
something else than 0 is introduced it will be stored in "mbirth". Again,
please go and name Q4 with this name.

Note that this line continues with a IF condition (/XI). What this IF
condition does, is check that the value just stored in "mbirth" is larger than
12. This would refer to an impossible month, and should be trapped. If the
value is indeed found to be too large, than processing goes to (/XG) a cell
called "monthbirth" which as you guessed is Y10. This allows to make an
infinite loop, that the user can only escape by given a valid answer, or a 0
to indicate that the answer is not available. Before forgetting it, please
name Y10 "monthbirth".

If the value that the user has introduced is valid, processing will
continue at the next line which is:

Y11: '/XImbirth=0~/XGphysintro~

Again we have an IF condition. Now we test to see if the value in mbirth
is not 0. If it is, than there is no point in asking what is the day or year
or birth of the patient, we should continue processing further down (/XG),
where information regarding the referring physician ("physintro") are needed.
As before if the tested condition is not true processing drops down on the
next line:

Y12: '/XNPatient's DAY of BIRTHDAY:
~dbirth~/XIdbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~

This is basically the same thing, except that to test if the day is
possible we need a more sophisticated check-up. Indeed the day vary with which
month is used. So we should test for the day and the month. This is easily
done using the @VLOOKUP function, that you may have wonder how in the hell I
would be able to introduce in this tutorial, without referring to some sort of
interest rate kind of table. Well here it is:



20








Month table: mthtable
1 31
2 29
3 31
4 30
5 31
6 30
7 31
8 31
9 30
10 31
11 30
12 31

You should introduce this in

Y19: 'Month table: mthtable

Y20: 1 Y26: 7
Z20: 31 Z26: 31
Y21: 2 Y27: 8
Z21: 29 Z27: 31
Y22: 3 Y28: 9
Z22: 31 Z28: 30
Y23: 4 Y29: 10
Z23: 30 Z29: 31
Y24: 5 Y30: 11
Z24: 31 Z30: 30
Y25: 6 Y31: 12
Z25: 30 Z31: 31

The entry in [Y19: 'Month table: mthtable] is not indispensable, but it
allows you to remember to name of the table (range). As you know there is no
direct tricks in 1-2-3 to find the name of a range. When the range covers only
one cell, than it is easy to /Copy the cell into a neighboring cell. Then by
going into the neighboring cell, the control panel will tell you:
+Cell_address, where Cell_address will either be letters and number such as
AE23 for instance, or it will be the cell name. This trick does not work if
the range is more than one cell large, and the only trick is to do: /Range
Name Create, and point to the name that you want to investigate. It is easier,
when using utility type of range as the one we just create to place its name
just above.

Now you should name the whole range Y20..Z31 with the name "mthtable".
The formula that we have introduced in Y12 makes more sense now. The
[/XIdbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~] means if the date of
birth (dbirth) is bigger than the value found by the @VLOOKUP function in the
month table (mthtable) for the month in question (mbirth), in the column which
as an offset of 1, go to (/XG) daybirth to continue processing. Again, as you
guessed daybirth is Y12, so please go name it accordingly. If the condition is
not filled, that is if the day is within the correct limits, than processing
continues on the next line. Otherwise the user is looped back, and asked the
same question again. We should make the test a little more bullet proof by
adding



21








Y12: '/XNPatient's DAY of BIRTHDAY:
~dbirth~/XIdbirth<1#AND#dbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~

which would trap the malicious user that would try to introduce a day 0,
and fool our worksheet !

As you see 1-2-3 is incredibly versatile, and you can test most anything.
Something, however, that you will not be able to test, is that the user
introduce a value such as 23.46 day ! But such a malicious user should be
fired anyway !

On to the next line.

Y13: '/XNPatient's YEAR of BIRTHDAY:
~ybirth~/XI@year(@today)-ybirth>45~/XGyearbirth~

You remember this first note you had to remember about the patient age
that should not be more than 45 years otherwise it would be unlikely that she
gets pregnant ? Well now is the time to attack this problem. What the
[/XI@year(@today)-ybirth>45~/XGyearbirth~] does is test that the year portion
(@YEAR) of the current date (@TODAY), minus the year of the patients birthday
(ybirth) is no greater than 45. If it is, than the program will loop back.
Again please name Y13 with the name "yearbirth".

We now have to complete a few more entries, and this should be kid's
stuff for you by now !

Y14: '{CALC}{GOTO}p6~/XLPhysician's name:~physname~
Y15: '{GOTO}p9~/XLOperator:~operator~

You are already familiar with the {GOTO} trick. But what about this first
{CALC} ? For whatever reason, when 1-2-3 in processing macro, it does not
recalculates the worksheet. Since we would like the age of the patient to
appear as soon as we have introduced her year of birth, we have to
specifically ask 1-2-3 to recalculate the worksheet, thus the {CALC}. Please
do not forget to name Y14 with the name "physname", and Y15 with the name
"operator".

Next comes our last challenge for this session:

Y16: '{GOTO}p12~/RIlmp~{?}
Y17: '/XG\m~

We have a new command (/RI) which means Range Input. We could have used a
similar way to introduce the LMP of the patient, as we did to introduce her
birthday. That would not have been very challenging however. Beside patients
always know their birthday pretty well, but are not usually so sure about
their last menstrual periods "It was September 16, no, I think it was the
first of October." The entry system we used previously would not allow to loop
back to change a value. Of course we could have made a small modification such
as "Do you want to change ? Yes/No" and loop back, but this would have been
outside the range of this tutorial. So lets try a different method. First go
in cell P10 and

/Range Name Create LMP~ and define the range P10..U15


22









Then go in Q12 and issue the command:

/Range Unprotect Q12~

and do the same in cell S12 and U12. You will notice that these cells are
now in high intensity. What we have done by using the /Range input is define a
range in which one can introduce number. But since the cells are naturally
"protected" (a strange concept, that I refer you to your manual to better
understand) we need to /Range Unprotect some of them. If you run it now you
will see the result. To do this go in cell Q2 and introduce the value 1, then
press , and in the menu, select Name. This will allow you to debug any
problem so far. The most likely problem would be and error such as

Illegal cell or range name

that would be displayed in the bottom left corner of the screen, with the
incriminated cell or range on the second line on the top left of the screen.
The likely error, is that you forgot to define one of the ranges, during one
of the /Range Name Create operation. Fix the problem, then go in cell Q2 and
introduce the value 1, then press , and in the menu, select Name to try
again. When you reach the LMP input range you will note that you can only go
in cells Q12, S12, and U12. What ever movements you use with the arrows cursor
you will only be able to go in these cells, and most of the other movement
keys will simply beep at you. To escape just introduce the values that you
want, then press return. You may have to press return twice, if you are making
an entry at the same time. The screen will then scroll and wait for you to
press an extra return, before returning to the menu. This extra return is due
to the {?} that we placed in Y16, and it allows you to contemplate your
masterpiece before moving onto the next subject.

Now that you have master all these concepts the rest of this part will
just be a breeze ! We will now fill the rest of the menu option except for the
Report option that will compose the next lesson. Go back to the menu in cell
Y5, and introduce:

Z7: '/RIdata~/XG\m~

This will introduce the data in the first screen. You will have to define
a "data" range that extends from:

/Range Name Create data and to the range limit question: A1..D8

Again the /XG\m~ is designed to bring the user back in the menu, when he
(she) has finished entering data.

The next option we have to complete is:

AA7: '{GOTO}a13~/RIinpcmts~/XG\m~

Which is preparing the area for "free form" entry of comments that the
user might want to include. Again we have to define a range whose name is
inpcmts which is done by:

/Range Name Create inpcmts and to the range limit question: A13..B20


23









The next option on the menu is the report. Since we will deal with this
later, lets just temporarily introduce the following macro, that just loops
back to the menu:

AB7: '/XG\m~

The next option is also very simple. We could /Range Erase every field
that we had previously used (patname, patfname, physname etc..) but besides
being a lot of typing this would be much more time consuming than erasing big
blocks of data at a time. So the following macro will clean our worksheet
faster:

AC7: '/REjunk1~/REjunk2~/REybirth~/REjunk4~
{GOTO}patname~1~/XG\m~

Again the series of command is longer than 80 characters so I had to cut
it in two. BE VERY CAREFUL not to introduce any blank between the two part of
the line when reconstituting it. You will also have noticed in the second line
the [{GOTO}patname~1~] instruction. This is the answer to the second note that
you had to remind yourself. We simply ask 1-2-3 to erase everything we do not
need, than to go in the cell that will contain the patient's name, and
introduce our test variable. So you see that although this is a few extra
step, we do not have to care about them, 1-2-3 does them for us. The range
that you have to define for

Junk1 is Q2..Q12
Junk2 is S4..S12
Junk4 is C6..D8

Note that these are more easy to define than the previous one. Why no
Junk3 range ? Well logically this would have been the range that would have
erased the year of birth of the patient. Since this is only one cell and it
already had a name, why not use it ?

The final macro, to exit 1-2-3 is very simple:

AD7: '/QYEY

Note that if you have started from the Lotus Access Menu, you will be
returned to it, and that there is no way to return directly to DOS, and
continue in a batch file for instance. Something like /QYEYEY (for Exit Yes)
will not work. If you want to go to DOS, then you have to start with 123.EXE,
and not LOTUS.EXE. This may look a trivial matter, but you might have in your
worksheet a menu that says something like:

Do you want to go to WordStar dBase DOS

or whatever. Under each of those choices you could have a macro that contains
instructions such as:

/PFnext~ROOUML0~QRwordstar~GQ/QY

/PFnext~ROOUML0~QRdbase~GQ/QY



24








Which means: print to a file called NEXT, and replace the existing
version by this one, using the option other unformated, with no left margin, a
range called WordStar that could contain something like:

cd\wp
ws

as you note these are DOS commands. That's why you have to print them
with no left margin, and no header of course. Then the macro exits from 1-2-3,
and you could get dumped into a previously running batch file that could say
something like:

cd\123
123
rename next.prn next.bat
next

and would therefore start the next application, without having to know
anything about how you got there. Isn't that great ?

It might be necessary to give at this point what the expected answers
should be if you input some values. To do that press , select Name, and
complete your screen to look like this:



Patient's information: 01-Mar-85 <-- Today's Date
Last Name : Foucher
First Name : Adele
Birthday (MM/DD/YY) : 5 / 4 / 56 28 <-- Patient's age

Referring physician: Herodote


Operator: Vesalius


LMP (MM-DD-YY): 9 / 6 / 84 06-Sep-84
Nbr of days elapsed: 176 days
Nbr of weeks elapsed: 25 weeks
Due Date (from LMP) : 13-Jun-85

Please move the cursor with the <-- and --> arrow
When you have seen the age, type and extra


Since you won't be able to introduce the same date as I included, add the
difference between the date that you enter and the sample date above, to the
patient's birthday, and LMP. (We hope that Victor Hugo, will not resent the
use of his wife in this tutorial, if you do not like it, you can include Sally
Brown instead, but I did not wanted to get into copyright trouble with Schultz
!). Then select Data and fill the next screen:





25









D A T A I N P U T A R E A
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size
Data Mean in week in mm
# 1 # 2 5th 50th 95th 5th 50th 95th
?
Biparietal Diameter 66 66.0 |23.8 25.6 27.4 | 60 64 69 |0
Abdominal Diam #1 55 55.0 | | |
Abdominal Diam #2 53 53.0 | | |
Abdominal Perimeter: 169
Estimated Fetal Weight (g) --> 677 | | 632 871 1193 |0
EFW (pounds + ounces) 1 lbs. 8 ounces

C O M M E N T S:
~~~~~~~~~~~~~~~~


This should allow you to check that the equations that you have
introduced produce the correct value. Although this may not be critical for a
tutorial, a friend of yours might be pregnant, and you wouln'dt want to give
her incorrect information should you demonstrate her this worksheet !

The last macro that we will introduce for this session is the dating
macro:

Z2: '{GOTO}message~/DFdate~@TODAY~~~/XG\M~

The /DFdate~@TODAY~~~ portion tells 1-2-3 to /Data Fill a range called
"date", with the formula @TODAY, and accept the increment and maximal limit
value. Then the macro initiate the menu system. The \0 is a particuliar macro,
that is executed automatically when the program is started, but cannot be
invoqued after that. It allows to store for you all sorts of chores that you
would otherwise do manually, such as changing the default subdirectory etc...
























26








4) Part IV: Word Processing with 1-2-3

In the previous three sections we have prepared a sophisticated series of
instructions that allows us to enter data concerning our patient, and make a
series of calculations based on these data. We are going to prepare in this
installment all the necessary instructions to have 1-2-3 prepare a report out
of these data. What one means with a report is apparently exceedingly
variable. Most data-base programs have "report" facilities that are in fact
sorted listing of subset of the original data. These typically look like
columns of unattractive numbers. What we mean here by report is something that
should attempt to duplicate what a secretary would send to the referring
physician. We are (fortunately/unfortunately) not ready yet to be able to have
1-2-3 be as good as a real secretary, but as you will see, we can make
noticeable effort in this direction.

The first thing we will want the user to check before printing the report
is that the printer is ON. Since the number of instructions that we will be
using is large, and would otherwise mess up the clean menu we previously did,
we will start a new menu such as:

Is the printer "ON" ? Print the report

A few important things here: As you see, 1-2-3 allows to branch menus to
sub menu. This is extremely convenient, and allows to make fairly user-
friendly applications. Also, note that the menu do not have to be spacially
contiguous. We will see in a instant how to connect them. Lets us introduce
the following instructions:

AH1: 'Is the printer "ON" ?
AH2: 'Please make sure that the printer is "ON", then select "Print the
report"
AI1: 'Print the report
AI2: 'Please be patient, printing the report takes a few minutes !~

Which corresponds to the entry of the menu, and the previously described
line of explanation. Also note that the menu options do not necessarily need
to be a single word. A short sentence is perfectly valid, as long as the total
of all the entries does do exceed the length of the screen, minus the command
indicator. Note that here we place the "Is the printer "ON" " option first so
that if the user press return without too much thought, he does not bypass the
checking: we will simply make this entry loop back with the instruction on the
third line of the menu being:

AH3: '/XMAH1~

The user has therefore to make a conscientious effort ot select the next
choice. This menu is unfortunately missing a crucial element. What if the user
decides that he want to modify a value ? No way ! What we need to build in is
a form of ESCAPE. As you know when you are using a real 1-2-3 menu you can
always press ESC to back-up one step. If you press ESC in a menu that you
create, 1-2-3 will simply dump you out of the menu. For a trained user that
represent no problem, but to make the menu more user friendly we have to come
with a better way than that. The easiest is by far to include an instruction
such as:



27








AJ1: 'Main Menu
AJ2: 'Returns to the main menu
AJ3: '/XG\m~

that does nicely the job. When using such back looping option, try to
forecast where the user might want to return. If you have a very complicated
branching pattern, the user might not want to come all the way back to the
root of the tree of menu, and you may have to include more than one option for
back-looping. This system works perfectly, but it has a major disadvantage:
each time you lose one of the possible options for your menu. A neater trick,
but that does not work all the time is to introduce another instruction on the
line, that does the looping. For instance if we modify

AH3: '/XMAH1~

to become

AH3: '/XMAH1~/XMmenu~

then by pressing ESC, 1-2-3 abandon the first instruction (/XMAH1~) and
continues processing at the second (/XMmenu~). We have indeed achieved what we
wanted: by pressing ESC, the user is backed up one level. The problem, is that
this trick only works consistently within menu, and no as well with other
instruction (/XL for instance). This is however one way to have your menu
mimic very closely those of 1-2-3. To summarize, the general principle is:
after the normal series of instructions, place a last instruction that returns
the user to the previous level. Another problem, is that the user will not be
able to press ESC twice to back up two levels in the menu. That would also
drop him back in the worksheet. Nevertheless this is a good trick, that you
should try to implement as much as you can. Here is another example on how to
use it in the instruction for the printing option:

AI3: '/REy35..ae100~/XGAH5~/XMAH1~

Here we erase the range Y35..AE100 which is the location in which we will
prepare the report, then we go (/XG) to the rest of the printing instruction
that are located in cell AH5. The program should never reach the last
instruction (/XMAH1~), and will only arrive there if the user press ESC during
execution of the beginning of the line.

We are now ready to start the preparation of the report. To demonstrate
that we can assemble the report out of little portion, we will start to
assemble the second paragraph, that we will later integrate in the text.

The first task that we will assign us is to print a paragraph that says
what is the estimated gestational age, and when the baby is due. We know what
is the estimated gestational age from the BPD. We calculated this value in H6.
We also know when the baby is due. We have stored this value in V15 (in a
range called: duedate). However, the estimated age is expressed in weeks and
tenth of week. That is surely not a standard unit ! The tenth of week should
therefore be converted in days. Besides the due date are expressed in the
Lotus Date 1 format, which although is standard in Europe, (and I was told
with the US Army), is not traditionally accepted in correspondence. We will
want to change that too. The result we want to obtain is something like:



28









The estimated gestational age is 15 weeks and 3 days, based on
the BPD. The expected due date based on the LMP is May 12, 1985.

We could have the text included in the macro (we will see example of that
later), but this is generally a less efficient solution. Instead, we will make
a copy of it in a range that we will call "estage" (for estimate age) that
will span B41..B45, so please /Range Name Create estage from B41..B45. We will
complete the entry in each of those cells by including the following:

B41: " The estimated gestational age is
B42: @INT(H6)
B43: 'weeks and
B44: @INT((H6-B42)*7)
B45: "days, based on the BPD.

You recognize that @INT obtains the number of week of gestation, and
transforms the decimal portion (the number minus its integer) in days by
multiplying it by 7. We now would like to use the almost unused command
called:

/Range Justify

and justify these five line into one normal sentence. If you try it,
nothing will happen. Why ? Well 1-2-3 refuse to justify anything that contains
a formula. No problem, we will calculate the values of the formulas, and
transform them into numbers. Here we hit a first snag. If we calculate the
formula, we will have no problems, except that the second time around, when we
will want to use the formula, it will have disappeared, and be replaced by a
number ! That's not too great, if we have to retype the formula each type, we
might as well forget the whole worksheet !. What we can do to get around this,
is copy the whole range "estage" into a temporary range that we could
calculate. This is a nice and simple solution: the original formula are still
there, and when we calculate the copied formula we do not mess the originals.
But here again we hit a snag. Try for instance to

/Copy estage~ to B50

By the way lets call B50 estage2 (so please /Range Name Create estage2
from B50). What happened ? Well you noticed it: both formula now refer to
other cells than the one we wanted. What is the problem ? In the formula that
we defined in B42 and B44, we use "relative reference" and not absolute
reference, when we moved the formula the cell that are now referred to have no
interest whatsoever in our problem. How to solve this last problem ? Easy
transform the entries to absolute references by adding a $ sign in front of
the letter and number coordinated of the cell address:

B42: @INT($H$6)
B44: @INT(($H$6-$B$42)*7)

Another problem solved. So now lets try to

/Range Justify

the copied range. Ok, now we have copied our range, but the justification


29








still does not work on formula. So what we will do is transform these formulas
into values. Fortunately this problem is relatively minor. This is easily done
by going into the cell, pressing EDIT, then CALC. We now have a value. So now
lets try again to

/Range Justify

What do we get ? Nothing ? Ha Ha, another problem: 1-2-3 not only refuses
to justify text that includes formula, it also refuse to justify text that
contains values. Fortunately this problem is relatively minor. What we need it
to transform a value into a label. This is easily done by going into the cell,
pressing EDIT, then HOME and adding an apostrophe before pressing return. This
may sound terribly difficult, and it is indeed terribly tedious to do, but
remember that now we are merely finding out what we will have to instruct 1-2-
3 to do. Once this is written into a macro, than it will be a breeze.

We are now ready to write the first line of our macro. For reason of
space I had to split it on two line. Please be careful not to include ANY
blank space between the two segments of the line

AH5: '{GOTO}estage2~/Cestage~~{DOWN}{EDIT}{CALC}{HOME}'{DOWN}{DOWN}
{EDIT}{CALC}{HOME}'{END}{DOWN}

You will see that virtually all book about 1-2-3 advise you to split long
macros so that you can see the whole thing on one screen. That is a nice
advice, but often quite impractical. The macro that we will develop her would
be so long that it would in turn be impractical to read, besides it would
produce a lengthy downward extension in an otherwise compact worksheet, which
would dramatically increase the memory requirements. Finally when you include
a IF condition (/XI) in a line, 1-2-3 expects the next line to correspond to
the action to be taken when the condition is not fulfilled, and splitting line
would therefore not work.

The next line in our macro is:

AH6: '{DOWN}The expected due date based on the LMP is
{DOWN}@month(duedate)~{EDIT}{CALC}~
/RNDmonthdue~/RNCmonthdue~~/XCmonth?~{DOWN}

which includes a fair number of new concepts. The @month is a formula
that finds the month value out of a date (duedate). You are by now familiar
with the {EDIT}{CALC}~ trick. Since we want to convert the number of the month
into a word (June is this case) we have to invoke a subroutine to do that. Why
not a @VLOOKUP formula ? Because 1-2-3 is not able to deal with alphanumeric
character in such tables, and only numbers are accepted. @CHOOSE, another
poorly documented formula, would not work for the same reasons. So will invoke
a subroutine by using a subroutine call: /XR. The subroutine is essentially
going to compare a value. If the comparison is false it will test the next
value on the next line, otherwise it will enter in the cell the name of the
month. Each line ends by a subroutine return (/XR~).

MONTH?
/XImonthdue=1~January~/XR~
/XImonthdue=2~February~/XR~
/XImonthdue=3~March~/XR~


30








/XImonthdue=4~April~/XR~
/XImonthdue=5~May~/XR~
/XImonthdue=6~June~/XR~
/XImonthdue=7~July~/XR~
/XImonthdue=8~August~/XR~
/XImonthdue=9~September~/XR~
/XImonthdue=10~October~/XR~
/XImonthdue=11~November~/XR~
/XImonthdue=12~December~/XR~

Let us introduce this:

AC18: 'MONTH?
AC19: '/XImonthdue=1~January~/XR~
AC20: '/XImonthdue=2~February~/XR~
AC21: '/XImonthdue=3~March~/XR~
AC22: '/XImonthdue=4~April~/XR~
AC23: '/XImonthdue=5~May~/XR~
AC24: '/XImonthdue=6~June~/XR~
AC25: '/XImonthdue=7~July~/XR~
AC26: '/XImonthdue=8~August~/XR~
AC27: '/XImonthdue=9~September~/XR~
AC28: '/XImonthdue=10~October~/XR~
AC29: '/XImonthdue=11~November~/XR~
AC30: '/XImonthdue=12~December~/XR~

and give it the name: "month?". Note that "?", is a valid character in
range name.

We do not need any error trapping here since the value is derived from a
date computed by 1-2-3. Note however that we will compare a range called
"monthdue", and that we therefore need to name this range. It would be
tempting to /Range Name cell B51 with the name "monthdue". That would not work
however. In a few minutes we will /Range Justify this range. Now, when 1-2-3
Justifies a range, it loses not only its boundaries (which makes sense) but
also it left upper corner and sometimes its name (which does not make sense).
Therefore we have to have the macro create the name on the spot: thus the
/RNCmonthdue~~ (note the TWO ~, one to accept the name, the second to accept
the current cell). That would be quite simple, however, occasionally the range
name might not have been destroyed, but simply stretched, often to include
cell IV2048 ! It is therefore a good precaution to start by deleting a name
that you want to create. This too has a small problem: the first time around,
the range will not be there to be erased, and you therefore have to include a
dummy range somewhere that you will call "monthdue". Please do that in AJ34
for instance.

We have now found the month, and transformed it into a word that
represent the name of the month. We now want to get the day and the year. The
next line in the macro will be:

AH7: '@day(duedate)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN}
@year(duedate)~{EDIT}{CALC}{HOME}'~
{UP}/RJ.{DOWN}~{EDIT}.{LEFT}{LEFT}{LEFT}{BS}~

The @day(duedate)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN} portion is self


31








explanatory: @DAY extracts the value of the day out of monthdue. Note that to
use a new formula we had to go to the next line. The {EDIT}{CALC}{HOME}'
transforms the formula into a value, and then into a label to which {END},
19{DOWN} adds a comma, a blank space and the value 19. The macro them goes in
the next cell to get the year value of duedate @year(duedate)~ and then does
the same kind of editing: {EDIT}{CALC}{HOME}'~.

It is worth noticing that 1-2-3 does not return the century portion of
the year, only the year itself, which is why we had to add it in the previous
line. We now have the value of our date, but it looks terrible.

B50: " The estimated gestational age is
B51: '25
B52: 'weeks and
B53: '4
B54: "days, based on the BPD.
B55: 'The expected due date based on this gestational age is
B56: 'June
B57: '13, 19
B58: '85


A little creative justification will fix this in a second. {UP} moves the
cursor one line up, on the day line, (/RJ) justifies the range extending from
the anchored point (.) down {DOWN}~ one line.

B55: 'The expected due date based on this gestational age is
B56: 'June
B57: '13, 19 85

We can now edit this cell {EDIT}, add a period at the end of the date
(.), then move 3 to the left {LEFT}{LEFT}{LEFT} and remove {BS} the blank
space between 19 and 85.

B55: 'The expected due date based on the LMP is
B56: 'June
B57: '13, 1985.

Our whole text is now there, and it is now a simple matter to rejustify
it. To do so, introduce the next line:


AH8: '{END}{UP}/RJ{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}
{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}~/RNCestage2~.{END}{DOWN}~

again do not forget there should not be any blank between the two segment
of line. We will start the justification at the upper left corner of the
range. The fastest way to get there is to press {END}{UP} which will leave us
at the interface between a filled and an empty cell. We then issue the /RJ
command, and define the depth of the range we want to use by using END DOWN,
then we define the lateral extend of the range with a series of {RIGHT}. The
number of {RIGHT} is to compensate for the number of column that we have in
the first screen. When you type those {RIGHT}, you will soon realize that
Lotus could have abbreviated it into {->} or {R} ! If you have ProKey, this
will be easier. Note that again, the /Range Justify, has delete the range name


32








"estage2", and that we have to recreate it, and define its extend.

We have now prepared the first paragraph, and have covered most of the
concept that we will need for the rest of the text. Let us go to the body of
the letter and start creating it.


AH9: "{GOTO}y41~To Dr{DOWN}/Cphysname~~{UP}/RJ{RIGHT}{RIGHT}{DOWN}~{RIGHT}
{RIGHT}{RIGHT}{UP}{UP}{UP}{UP}{UP}{UP}" Examination performed on {RIGHT}
{RIGHT}{RIGHT}@month(date)~/RNDmonthdue~/RNCmonthdue~~/XCmonth?~{DOWN}

and in

AH10: '@day(date)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN}@year(date)~{EDIT}
{CALC}{HOME}'~{UP}{UP}/RJ.{DOWN}{DOWN}{RIGHT}{RIGHT}~{EDIT}.{LEFT}
{LEFT}{LEFT}{BS}~{DOWN}{DOWN}

Note that for the first time I refer in a macro, to a cell address with
it coordinate (Y41) and not through a range name. This is sometime useful when
you are sure that something in your macro should never be moved around, will
not enter in a formula... and especially when the cell will be part of a range
that will be justified. This avoid the constant renaming of the cell. The rest
of the line is fairly simple: we have 1-2-3 type the addressee: To Dr, then on
the next line we copy the name of the addressee, justify the whole think to
put it back on one line, then we move to the right and up. Again 1-2-3
introduce a label for us [" Examination performed on ] which is right
justified by its prefix ("). We then reproduce (in cell AH10) our little date
arithmetic that we previously used. This explains the reason to use a /XC to
call a subroutine for the month. Otherwise we would have had to introduce this
series of code twice. (Which would not have been a real big deal in fact: a
/Copy would have worked quite nicely, since we were careful to use range name,
and not cell addresses).

We then introduce the next lines of instructions:

AH11: '{LEFT}"Re:{RIGHT}/Cpatinfo~~{GOTO}ae39~/XIq4=0~ ~/XGreport1~
AH12: '+birthday~{EDIT}{CALC}{HOME}'{END} years old~

We again ask 1-2-3 to type something for us: ["Re:] and copy in the next
cell the patient's information "patinfo". If you haven't defined this range
yet do it by /RNC patinfo Q2..Q4. Here again we refer to a specific cell
(AE39) which is easier, then using a range name. Then we test if Q4 is = to 0.
If it is we introduce a blank (the blank between the two ~) which erase the
display of the cell by concealing it behind a blank space. We then go to
"report1" which is the name of the cell where execution should proceed. If Q4
is not equal to 0, then we copy onto this cell the value birthday (which is
the age of the patient), transform it into a number, than a label, then go to
the end of the label and add "years old".

Lets introduce the next line:

AH13: '{GOTO}Y41~{DOWN}{DOWN}{DOWN}{DOWN}Dear Dr{DOWN}/Cphysname~~{EDIT},
{UP}/RJ{RIGHT}{RIGHT}{RIGHT}{RIGHT}{DOWN}{DOWN}~

Nothing new here: we define a cell address, go to it, and do the same old


33








trick of copying a range, and reformating it. This cell should be named
"report1" as you had guessed.

AH14: '{DOWN}{DOWN}{DOWN}' Thank you for referring to us Ms{DOWN}
/Cpatname~~{DOWN}/Cpatfname~~{EDIT} ~{DOWN}

Here we have another little trick. Note the /Cpatfname~~{EDIT} ~{DOWN}.
Since it is likely that sometime the user might be lazy, and not introduce the
first name of the patient, we have to plan for such circumstance. If we were
to leave a empty cell, when we will later use the END UP trick to define the
range to justify, the pointer would stop at this level, since it will find an
empty cell. This would thus be too early, and crash the macro. By introducing
an additional blank space, we fill the cell with something invisible, but that
1-2-3 will recognize as a "filled" cell. The only inconvenient, is that the
first name of the patient will be followed by two blank and not one, but we
had admitted that we would not be as perfect as a real secretary !

AH15: 'who is currently{DOWN}/Cage~~{EDIT}{HOME}@INT~{EDIT}{CALC}{HOME}'
{DOWN}weeks by dates.

Here we have another little trick. When we copy age, we copy the formula:
+($V$13/7). A minor bug of 1-2-3 if that if we /Copy it, we get the format of
the cell together, and therefore not the numbers behind the period.
Unfortunately, when we EDIT CALC it, they reappear. To get rid of them we need
to use {EDIT}{HOME}@INT~ which gives us the integer of the formula. We did not
used the integer previously because we wanted the full precision for our
computations.

The next cell does not poses any challenge anymore. We can now justify
this paragraph by:

AH16: '{UP}{UP}{END}{UP}/RJ.{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}
{RIGHT}{RIGHT}~

AH17: '{END}{DOWN}{DOWN}{DOWN}

then copy the product of our earlier efforts:

AH18: '/Cestage2~~{END}{DOWN}{DOWN}{DOWN}

Note the {END}{DOWN}{DOWN}{DOWN} that is a way to find the end of the
paragraph, then pass one line, and position the pointer to the next line to be
ready to proceed with the next set of instructions.

The next two lines offer few challenges:

AH19: ' The estimated fetal weight (EFW) is{DOWN}/Cefw~~
AH20: '{EDIT}{CALC}{HOME}'{DOWN}grams (which is about{DOWN}/Clbwgt~~{EDIT}
{CALC}{HOME}'{DOWN}pounds and {DOWN}/Cozwgt~~{EDIT}{CALC}{HOME}'{DOWN}
ounces).{DOWN}

we again have the now familiar trick of /Crange~~, remember the two ~,
one to terminate the name of the range, the second to accept the current cell
as the recipient. The copied value is then {EDIT}{CALC}{HOME}'{DOWN}. Note
that it is not necessary to press return (~) after the apostrophe, pressing


34








{DOWN} is sufficient. You will notice later on that if you were to use the
formula as they are, you would not obtain the expected value, although they
are correct in the worksheet. What happened ? As in previous cases, by copying
the formula you in fact change the cells that the formula refer to, and the
formula loses in meaning. As before the solution is to transform the formulas
in the original cell to indicate absolute references. So you should correct
the formula on the next few lines to their new values on the second line. This
merely involve adding plenty of $ signs !

change:
E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6))
into:
E6: @IF(@ISERR(@IF(@AVG($C$6..$D$6) <105,@AVG($C$6..$D$6),@ERR)),0,
@AVG($C$6..$D$6))


change:
E10:@IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE,
@ROUND((10^(-1.7492+0.166*(BPD/10) +
0.046*(ABD_PER/10)-2.646*((ABD_PER/10)*(BPD/10))/1000))*1000,0))
into:
E10:@IF($BPD=0#OR#$E$7=0#OR#$E$8=0,@FALSE,
@ROUND((10^(-1.7492+0.166*($BPD/10) +
0.046*($ABD_PER/10)-2.646*(($ABD_PER/10)*($BPD/10))/1000))*1000,0))

change:
C11: @INT(EFW/453)
into:
C11: @INT($EFW/453)

change:
E11: @ROUND((EFW-LBWGT*453)/28,0)
into:
E11: @ROUND(($EFW-$LBWGT*453)/28,0)

change:
V15: (D1) @IF(@ISERR(LMPDATE),@ERR,@IF(@ISERR(V13),@ERR,LMPDATE+280))
into:
V15: (D1) @IF(@ISERR($LMPDATE),@ERR,@IF(@ISERR($V$13),@ERR,$LMPDATE+280))

change:
L6: @IF(AGE=0,0,-19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3)
into:
L6: @IF($AGE=0,0,-19.634+3.0209*$AGE+0.042134*$AGE^2-0.0011756*$AGE^3)

change:
L10: @IF(AGE<15,0,10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2
-0.0000202932*AGE^3))
into:
L10: @IF($AGE<15,0,10^(0.5328769+0.1122225*$AGE-0.000145731*$AGE^2
-0.0000202932*$AGE^3))

change:
K10:@IF(L10=0#OR#AGE<15,0,L10-(10^(-2.72529+0.4042264*AGE-
0.01048152*AGE^2+0.000098495*AGE^3)))


35








into:
K10:@IF($L$10=0#OR#$AGE<15,0,$L$10-(10^(-2.72529+0.4042264*$AGE-
0.01048152*$AGE^2+0.000098495*$AGE^3)))

change:
M10:@IF(L10=0#OR#AGE<15,0,L10+(10^(-2.573974+0.4008528*AGE-
0.01031992*AGE^2+0.0000961377*AGE^3)))
into:
M10:@IF($L$10=0#OR#$AGE<15,0,$L$10+(10^(-2.573974+0.4008528*$AGE-
0.01031992*$AGE^2+0.0000961377*$AGE^3)))

change:
O10: @IF(EFW=0,0,@IF(EFW=0#OR#HIGHEFW=0,0,@IF(EFWHIGHEFW,1,0)))
into:
O10: @IF($EFW=0,0,@IF($EFW=0#OR#$HIGHEFW=0,0,
@IF($EFW<$LOWEFW#OR#$EFW>$HIGHEFW,1,0)))

The next two lines will test whether the estimated fetal weight is normal
or not:

AH21: '/XIefw fetus is too small.~/XGreport2~

AH22: '/XIefw>highefw~The estimated fetal weight indicated that the
fetus is too large.

Exceptionally, there should be a blank space at the end of the first
segment of the line when you reconstruct it. Note that the first line ends by
[/XGreport2~]. This means that if the condition is true, the line of text is
accepted, entered in the current cell, and that processing resume (/XG) at the
cell called "report2". If the condition is false, than the next line (AH22) is
executed. Here we do not need to send the processing to a special address
after testing. Either the condition is true, and the line is accepted, and
then processing follows on the next line, or the condition is false, the line
is not accepted, and processing follows at the next line anyway. The next line
will in fact justify the current paragraph, and is:

AH23: '{UP}{END}{UP}/RJ{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}
{RIGHT}~

AH24: '{END}{DOWN}{DOWN}{DOWN}

Again, you should

/Range Name Create report2 for cell AH23.

We can now copy the comments that have been introduced:

AH25: '/Ccomment~~/RJ.{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{RIGHT}{RIGHT}
{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}~


The range "comment" is smaller than the range "inpcmts". Indeed, we do
not want to copy the title COMMENT in our final report. Go name (/RNC)
"comment" from B15..B20.


36









This last line (AH25), offers no difficulty, however, note that we did
not use the previously described trick of using END DOWN to define the depth
of the range. Why ? To use END DOWN to define the depth of the range, the
range ought to be at least 2 lines deep. If it is only one line deep, than END
DOWN will bring you to the end of the worksheet, not what you want to do of
course. To avoid that pitfall we define ahead of time what we want to
justify. We now the size of the incoming range, we defined it earlier. It
might be tempting to justify a range that is the same size. But as you know,
1-2-3 allows to introduce long labels that are longer than a screen, and an
advanced user that has notice that, might include in the range labels that are
longer, and could not be justified within the same number of lines. We
therefore have to exceed the area to be covered by the new range, to take this
into account. After this justification, we are ready to move down to copy the
closing remarks. We have up to now used the {END}{DOWN}{DOWN}{DOWN} sequence
to do so. Since we do not know whether there will be a paragraph of comment or
not, the way to do is to go many lines down first, then, find the last [empty
cell - filled cell] interface and move then two lines down:

AH26: '{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}
{DOWN}{END}{UP}{DOWN}{DOWN}

This insures that with or without a paragraph of comment, the closing
remarks will be correctly spaced. We can then copy the closing remarks:

B33: ' Sincerely yours,

This would be conveniently stored in B33, out of sight. Again you should
name this range:

/Range Name Create closing for cell B33

We are now ready to introduce the closing remarks in the letter:

AH27: '/Cclosing~~{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}

and finish by introducing the operator, in the correct position for a
signature:

AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~

Our letter is now finally finished. We only have to define the range that
contains the letter, to be able to later print it. We could of course define a
fixed range of 58 lines, starting just above the very first line that we used
in the letter: Examination performed on "date". Although this would work, let
us define a more sophisticated approach. The fixe range risks to crash if the
user has introduced an unusual amount of comment, and has spilled over the 58
lines limits onto the next page. It would be embarrassing that our program,
ignore such cases ! The way around it to start the range at an address we now
is fix, Y35 for instance, then use something like END DOWN to extend the
range. Unfortunately, as you know, all the text that we have introduced in the
body of the letter, are just "long label" that are anchored in the left
column, and are not "in" the adjacent cells. The cells on the right are indeed
empty. The only cell that is not empty, is the one that contains the name of
the operator. Ha Ha, it was not by chance that we placed it there ! We can in


37








fact start to define the range at Y35, anchor the pointer, move a few cell to
the right, in fact exactly the correct amount to reach the same column in
which the signature is stored, then plunge downward with a END DOWN command:


AH29: '{GOTO}y35~/RNCletter~{BS}.{RIGHT}{RIGHT}{RIGHT}{RIGHT}{END}{DOWN}
{RIGHT}{RIGHT}~

The {BS} is there to refuse the default range that 1-2-3 will suggest us
when we give him the name of the range. This trick is nice, but there is a
potential pitfall ! This trick rely crucially on the presence of something in
the cell that contains the name of the operator ! If the user forgot (or
neglect) to introduce it, that's it, the macro crashes. As you remember, the
operator name was introduce by the instruction

Y15: '{GOTO}p9~/XLOperator:~operator~

We are therefore sure that the user will have seen the message, but how
about if he simply pressed return, no label would be stored in the cell. Now
you remember from our discussion that there is no way to trap an empty message
in a /XL command. Another trick that we previously used, preloading a cell
with something - a blank space would be sufficient here -, would not work
either, since by pressing return, the user would erase the blank space, and
empty the content of the cell. The solution is then to add a blank after the
signature has been copied. For that let us modify the previous line of
instruction to read:

change:
AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~

into:
AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~{EDIT} ~

Which place the blank space after the operator's name. If there is a
name, it will go unnoticed, if there is nothing, the blank space will be
sufficient to stop the END DOWN command.

We have now solved all our problems and are ready to print our letter.
Before that, I can't resist giving a last final trick for all of you out
there, that print long report concerning First Trimester balance, Second
Trimester balance, Third Trimester balance, and so on. You know that you can
include in the header, and footer, all sorts of information, but not the value
of a cell in the worksheet. This must have irritated quite a few people. 1-2-3
allows to include labels stored in the worksheet as title for graph labelling,
but not in the header ! Well here is a simple way to do that.

AH30: '/Cpatname~nametitle~
AH31: '/PPCAAOHPatient:
AH32: '
AJ32: '!This line should remain!
AH33: '~ML1~~R80~OFQ

What this says is essentially: copy the value stored in a range in the
worksheet into another range. This second range here will be "nametitle" which
is in AH32. Nothing secret up to now. Then comes /PPCAAOHPatient: that


38








essentially means: Print to Printer after Clearing All previous printing
conditions previously used, then Align the form, use Other conditions and
include a Header that contains (left justified) the text:Patient. Now here
come the trick. As you know 1-2-3 can read labels that follows each other on
adjacent cells, as long as the next cell is situated in the cell just below.
Even better you could even dream of splitting a command on to line. For
instance

{DO
WN}

would work perfectly as well as:

{DOWN}

Now no one with any sense would ever cut a single instruction on two
levels like that, except that in our case, it fits perfectly what we need: the
first portion remains constant, than we can introduce a variable that will be
included in the header. AJ32: '!This line should remain! Simply reminds you
not to compress the next line onto this one, which would result in erasing the
next line of command, when the name is copied onto it. The rest,
[~ML1~~R80~OFQ] starts by entering the name with a ~, then defines the correct
margins, and quit the submenu to return to the main printing menu. Then:

AH34: 'Rletter~GPQ/XG\m~

prints the letter (G), sends a page advance command (P), and returns to
the menu that we designed earlier.

In fact a very similar trick can be designed to automatically save the
worksheet under a name stored in a cell from the worksheet. The macro should
however check that the name follows the requirements of DOS filenames.

I have included on the next page a sample of the type of printout that we
can obtain. As you can see, it is fairly decent, and it is a pity that 1-2-3
word-processing abilities are so underutilized. It is of course tedious to
prepare the printing macro, but once it is done it can be used multiple time,
and that saves a lot of time. Besides, once the general principles involved

are understood, we basically do the same things over and over.

We have now toured most the advanced features of 1-2-3. You now deserve,
to sit back, and enjoy the model that you have create, and admire the macros
at work. I hope that this has tought you some tricks that will be useful in
your own worksheets.

Sincerely yours,



Philippe Jeanty M.D.,Ph.D.







39










Patient: Foucher


Examination performed on March 1, 1985.

Re: Foucher
Adele
28 years old

To Dr Herodote



Dear Dr Herodote,


Thank you for referring to us Ms Foucher Adele who is currently 25
weeks by dates.

The estimated gestational age is 25 weeks and 4 days, based on
the BPD. The expected due date based on the LMP is June 13, 1985.

The estimated fetal weight (EFW) is 677 grams (which is about 1
pounds and 8 ounces).

This baby looks great, it size and weight are appropriate for its
age and no evidence of congenital malformations are seen. The fetal
sex is that of a female.

Sincerely yours,




Vesalius