Microsoft KB Archive/92522

= Works: How to Simulate an IF function in a Works Database Report =

Article ID: 92522

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 2.0a
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.0a
 * Microsoft Money 2000 Business & Personal Edition

-



This article was previously published under Q92522



SUMMARY
In Microsoft Works 4.0, 4.5, and 4.5a for Windows 95, the IF function cannot be used in the database report. However, the relational operators, such as < (less than) and >= (greater than or equal to), can be used as an alternative to the IF function.



MORE INFORMATION
When a relational operator is used in an expression, either a 1 (true) or a 0 (false) is returned as a result. Hence, 10 20 = 0.

You can use this capability to simulate an IF function. The format of the IF operation is as follows:

=IF(test expression, result if true, result if false)

The format of the same function using relational operators instead of the IF is the following (entered on 1 line):

=(test expression)*(result if true) + (NOT(test expression))*(result if false)

NOTES: The NOT operator in Works 2.0 is the tilde (~), and in Works 3.0 and Works for Windows 95, it is #NOT#. For example, if the test expression is (Amount=10), the (NOT(test expression)) is (~(Amount=10)) in Works 2.0 or (#NOT#(Amount=10)) in Works 3.0 or Works for Windows 95. The parentheses are critical: there must parentheses surrounding the test expression and the NOT(test expression), including one before the tilde.

Example
A database contains a field called Sales. You want a formula in the Database Report that returns 50 if the total of Sales is less than 5000, and returns 1% of the total sales otherwise. The following formula could be used in Works 2.0:

=(SUM(Sales)<5000)*50+(~(SUM(Sales)<5000))*.01*SUM(Sales)

To use the same formula in Works 3.0 or 4.0, replace the ~ with #NOT#.

