SuperROM Tip #14 by Paul Globman
Copyright (c) 1986, 1990
--------------------------------
Successful spreadsheet analysis usually requires a certain amount of decision
making. Unfortunately, SuperROM does not support...
IF... THEN... ELSE (IF)
They offer an example on page 74 of the Lucid manual as follows:
Suppose you need to calculate the price of a service that was priced at $10 per
hour, but had a 4 hour minimum.
The Lucid manual goes on to offer a solution that requires the use of a lookup
table. This may be the only way to accomplish this task with the Lucid ROM,
but with SuperROM the technique is quite different.
I have spent a fair amount of time learning the SuperROM and would like to
report that I am able to perform...
IF...THEN...ELSE
...decisions within a single cell of LUCID, without having to refer to any
outside table (TBL).
This comes from the use of RELATIONAL OPERATORS, which the LUCID manual does
not discuss. The LUCID DATABASE manual discusses (very) briefly the use of
relational operators for criteria selection in the 'report definition block'
for output.
Relational operators can be used in any cell to obtain a TRUE(-1) or a FALSE(0)
value and when multiplied by -1 yeilds some fantastic options. Please note
examples:
A1 = number of hours worked on a job.
Minimum charge is for 4 hours.
B1 = billable hours
The formula for cell B1 is:
-(A1<=4)*4-(A1>4)*A1
The first '-' is equal to 'IF'
the '*' is equal to 'THEN'
Additional '-'s are read as 'ELSE IF'
So the above example is:
IF A1<=4 THEN 4 ELSE IF A1>4 THEN A1
If the first IF is false the cell value will be zero unless an ELSE IF is
included.
Now enter some data in cell A1 and watch the results. Cell B1 will display
the number of hours billed. Multiply B1 by the hourly rate ($10) and you get
the amount billed.
Pretty POWERFUL indeed!! This formula is the essence of the decision making
processes in some financial spreadsheets.
This can be used for tax calculations like:
IF deductions > 3000 THEN display deductions - 3000 ELSE display 0. (A6=total deductions)
NEW CELL FORMULA: -(A6>3000)*(A6-3000)
Or how about applying 5% discount on total sales (A5) exceeding $20...
NEW CELL FORMULA:+A5+(A5>20)*(.05*A5)
(NEW CELL is another cell, ex:cell B1)
I developed this aspect of SuperROMs use of relational operators in June 1986.
I informed PCSG back then, hoping to find a way of enlightening other SuperROM
users. I hope some of you M100SIG users can make adequate use of this info.
Paul Globman [72227,1661]