Works: How to Simulate an IF function in a Works Database Report (92522)


The information in this article applies to:

  • Microsoft Works 2.0
  • Microsoft Works 2.0a
  • Microsoft Works 3.0
  • Microsoft Works 3.0a
  • Microsoft Works 3.0b
  • Microsoft Works 4.5
  • Microsoft Works 4.5a
  • Microsoft Works for Windows 95, version 4.0
  • Microsoft Works for Windows 95, version 4.0 4.0a
  • Microsoft Money 2000 Business and Personal
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 = 1 and 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#.

REFERENCES

For more information about how to perform this task in Works, see your Works printed documentation or online Help.
Modification Type:MajorLast Reviewed:11/15/2004
Keywords:kbhowto KB92522
©2004 Microsoft Corporation. All rights reserved.