IF-THEN-ELSE For SpeedCalc
Anthony ChandlerThis tutorial shows you how to get more out of SpeedCalc. By using clever formulas, you can set up a spreadsheet to perform different computations based on the result of logical IF tests. The techniques apply to any version of SpeedCalc, COMPUTE!'s powerful machine language spreadsheet program. (The Commodore 64/128 version of SpeedCalc appeared in the January, 1986 issue of COMPUTE!. The Apple II and Atari versions were published in February 1986 and March 1986, respectively.)
SpeedCalc, the versatile spreadsheet program published in the January-March, 1986 issues of COMPUTE!, offers a great variety of built-in functions. It supports all the math operations of BASIC, as well as two new ones (@ave and @sum), but there is no specific mention of how the program can perform conditional operations and make decisions. Here are techniques to make SpeedCalc calculate based on the outcome of logical tests modeled after the IF-THEN-ELSE construction in BASIC.
More Than A Glorified Calculator
Many people use a spreadsheet as little more than a glorified calculator: Once a sheet has been set up, you punch a button and the program performs a large number of related calculations. While the result of one calculation frequently serves as input for another, the process doesn't involve anything resembling intelligence on the part of the program. Nevertheless, the SpeedCalc spreadsheet program can test conditions and take action based on the results. The process works very much like the familiar IF-THEN-ELSE construction in BASIC.
In plain English, a typical IF-THEN-ELSE construction would be translated as, "IF a certain condition is true, THEN do the first task. ELSE if the condition is false, do the second task." A computer can't work with abstract concepts such as truth or falsity, but it's very good at telling the difference between one numeric value and another. When the computer performs an IF test in BASIC, it uses numeric values (usually -1 and 0) to represent true and false, respectively. You can verify this by entering the following statements in BASIC direct mode:
A=1;PRINT (A=1) A=0:PRINT (A=1)
In Microsoft BASIC and most other versions, the computer prints -1 and 0, indicating that it uses -1 to represent a true condition and 0 to represent a false condition. The BASICs on Apple II and eight-bit Atari computers use 1 instead of -1 to represent true. To implement IF-THEN-ELSE with a formula in SpeedCalc, we can take advantage of the fact that true and false are represented as simple numeric values.
How Many Tests Do You Need?
If you give the matter some thought, you'll discover that only two basic IF tests are needed to cover all possible cases. Here they are:
IF A>B THEN (this cell=) C ELSE (this cell=) D IF A<>B THEN (this cell=) C ELSE (this cell=) D
In these examples the letters A, B, C, and D represent the values contained in particular cells within the spreadsheet. A cell, of course, can contain a simple numeric value such as 2500, a reference to another cell, or a complex expression such as (ab2*(@sqr(2))) or (12*ac24+52*11).
Other IF tests can be achieved by varying one of the preceding constructions. For example, these two statements are logically equivalent:
IF A =< B THEN C ELSE D IF B > A THEN C ELSE D
Likewise, these two statements are equivalent:
IF A = B THEN C ELSE D IF A <> B THEN D ELSE C
IF-THEN-ELSE Formulas
Every IF-THEN-ELSE statement can be broken into two separate parts—the IF test and its consequence. The first portion (for example, IF A=B) tests a logical condition. The second portion (for example, THEN C ELSE D) states the consequence of the test. The THEN portion of the consequence is performed when the IF test is true, and the ELSE portion is performed when the IF test is false. Table 1 shows SpeedCalc formulas for the two IF tests described in the preceding section.
The consequence (THEN-ELSE) portion of the formula will always be the same expression—D+(C-D)*(...)—which represents the logical statement ELSE + (THEN - ELSE)*(...). When the ELSE portion of the consequence is to be 0, the expression reduces to a simple C*(...). When the THEN portion of the consequence is to be 0, all you need is the expression D - D*(...).
To express a complete IF-THEN-ELSE statement in a SpeedCalc formula, you need to multiply the consequence portion of the statement by the IF portion. For example, say that you wish to use this statement:
IF A>B THEN C ELSE D
The SpeedCalc equivalent is expressed by this formula:
D+(C-D)*@int((@sgn(A-B)+1)/2)
Note that we have placed the consequence portion—D+(C-D)— first and the IF portion—@int((@sgn(A-B)+1)/2)— last. The multiplication operator (*) separates the two portions of the statement.
Inside The IF Test
Recall that the computer ordinarily makes a decision based on an IF test by comparing two numbers. More specifically, it subtracts one number from the other, then determines whether the result is positive (true), or zero or negative (false). For example, to perform the statement IF A>B, we want to know whether the result of (A-B) is positive or not. If it is positive, then A is greater than B. If it is zero, then A equals B. If it is negative, A is less than B. In other words, after subtracting the two numbers, we then need to know the sign of the remainder.
SpeedCalc, of course, has no difficulty performing the subtraction. To determine the sign of the result, you need only enclose the expression in a @sgn() function, using the formula @sgn(A-B). When the result of A-B is positive, @sgn(A-B) resolves to 1. When the result of A-B is negative, it resolves to -1, and when the subtraction yields 0, @sgn(A-B) yields 0.
Now let's build on this basic expression to perform specific IF tests. To select only cases where A is greater than B (IF A>B), you need to select only the positive result. To do this, add the value of 1, divide by 2, and make the result an integer with the @int() function:
@int((@sgn(A-B)+1)/2)
This formula yields 1 when A is greater than B, and 0 in all other cases.
To select only cases where A is unequal to B (IF A<>B), you need to include negative as well as positive results (in other words, all non-zero results). The @abs() function easily converts any negative value into a positive value:
@abs(@sgn(A-B))
This formula yields 1 whenever A is unequal to B, and 0 only when A equals B.
Now we have formulas which resolve to the value 1 when the desired condition is true or the value 0 when it is false. Table 2 shows the complete formulas.
For both formulas in Table 2, when the IF test is true (resolves to 1), the cell is made equal to D+(C-D)*1. This performs the THEN part of the IF-THEN-ELSE statement, making the cell equal to C. When the IF test is false (resolves to 0), the cell is made equal to D+(C-D)*0. This performs the ELSE part of the IF-THEN-ELSE statement by making the cell equal to D.
To take a more realistic example, say that you want SpeedCalc to compute the equivalent of the following statement:
IF Q>9 THEN (this cell=) Q*P*.85 ELSE (this cell=) Q*P
Now assume that the value Q is in cell AB1 and the value P is in cell AB2. This formula produces the desired result:
=ab1*ab2+(ab1*ab2*.85-ab1*ab2)*@int((@sgn(ab1-9)+1)/2)
Boolean Operators
In certain cases the Boolean operator OR, NOT, or AND is required to perform a conditional test. The easiest of these to implement is NOT. If the value of A is 1, then the expression NOT A yields 0. If A equals 0, then NOT A equals 1. Both alternatives can be handled with this SpeedCalc expression:
abs(1-A)
The AND and OR operations can be simulated by combining two IF tests. For an AND operation, the results of both IF tests are multiplied:
[consequence]*[IF test 1]*[IF test 2]
For an OR operation, the results of both IF tests are added together:
[consequence]*([IF test 1]+[IF test 2])
A Practical Illustration
For example, say that your business wants to calculate the quantity discounts diagrammed in Table 3. When you sell items in quantities of 9 or fewer, no discount is given. A 10 percent discount is given on purchases of 10 to 99 items, and purchases of 100 or more items qualify for a 15 percent discount.
To calculate the discounts within SpeedCalc, you need to set up a sheet with two conditional calculations; the first one requires an AND function. Run SpeedCalc and enter the sheet as shown in the figure.
To test whether the sheet performs as expected, enter some test results in cell AB2. You should get the results shown in Table 4.
Although the algorithms are simple, it is easy to make mistakes in logic when setting up such involved formulas. It often helps to write the statements on paper before entering the actual formula. Before using the formula for serious purposes, you should test it with some sample values to make sure it works correctly.