Knowledge Base

Works: IF function Returns Incorrect Results with Blank, Zero

Article ID: 123868

Article Last Modified on 10/6/2003

APPLIES TO

This article was previously published under Q123868

SYMPTOMS

You may get incorrect results when using the IF function to check the contents of a cell for a specific text string. If the cell being referenced contains a blank or a zero (0), the IF function returns a TRUE value even though it did not find the specified text string.

RESOLUTION

To correct the problem, use the S function to force Works to treat the contents of the cell as a text string and exclude blank or zero cell contents.

Example

The following function 
   =if(a1="abc","true result","false result")
results in a "true result" answer if cell a1 is blank, zero, or contains "abc".

To correct the problem and ensure that the function returns "true result" only if the cell a1 contains "abc", use the S function. For example: 
   =if(s(a1)="abc","true result","false result")

MORE INFORMATION

The basic syntax of the IF function is: 
   =IF(Test Condition,ValueIfTrue,ValueIfFalse)
Substitute whatever value, text, or formula is desired in place of the "true result"/"false result" text above. To use the above formula in the Works database, substitute a field name for the cell reference (a1) in the above formula.

The reason the formula 
   =if(a1="abc","true result","false result")
does not work with blank/zero cells is that when Works does the comparison it converts the text (e.g., "abc") to a number (0) to match the data type (numeric) of the zero-value cell. Because all text has a numerical value of zero and a blank cell is equivalent to zero, the result of the conditional test is true.

NOTE: To check if a cell contains text in general, regardless of the individual characters, use the following formula: 
   =if(S(a1)<>"","Is Text","Is a Number, Zero or Blank")

REFERENCES

Works for Windows 3.0

  • IF function: See pages 540-541 of the "Microsoft Works User's Guide."
  • S function: See page 555 of the "Microsoft Works User's Guide."

Works for MS-DOS 3.0

  • IF function: See pages 482-483 of the "Microsoft Works User's Guide."
  • S function: See pages 507 and 513 of the "Microsoft Works User's Guide."

Additional query words: w_works S N text string concatenation characters worksheet equation conditional wrong

Keywords: KB123868


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.