This information is based on the information in the help menus in StarOffice Calc and in Microsoft Office Excel. The syntax used in these examples is for StarOffice Calc.

IF Function -- StarOffice Help

The IF function specifies a logical test to be performed. It will do one thing if the test is true, and something else if the test is false. This is similar to an IF statement in Visual Basic.

IF(Test; Then_value; Otherwise_value)

Test is any value or expression that can be TRUE or FALSE.

Then_value (optional) is the value that is returned if the logical test is TRUE.

Otherwise_value (optional) is the value that is returned if the logical test is FALSE.

IF(A1>5;100;"too small")

If the value in A1 is higher than 5, the value 100 is entered in the current cell; otherwise, too small is entered in text format.

IF Function -- MS Excel Help

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

Syntax 1

IF (logical_test; value_if_true; value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE.

• Value_if_true is the value that is returned if logical_test is TRUE. If logical_test is TRUE and value_if_true is omitted, TRUE is returned. Value_if_true can be another formula.
• Value_if_false is the value that is returned if logical_test is FALSE. If logical_test is FALSE and value_if_false is omitted, FALSE is returned.
• Value_if_false can be another formula. (** This is what you'll need for A4.)

Remarks

• Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.
• When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
• If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. If some of the value_if_true and value_if_false arguments are action-taking functions, all of the actions are taken.

Examples

In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100;SUM(B5:B15);"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

IF(B2>C2;"Over Budget";"OK") equals "Over Budget"

IF(B3>C3;"Over Budget";"OK") equals "OK"

Suppose you want to assign letter grades based on the numerical value in cell A1. See the following table.

  If A1 is           Then return

  Greater than 89    A
  From 80 to 89      B
  From 70 to 79      C
  From 60 to 69      D
  Less than 60       F

You can use the following nested IF function:

=IF(A1>89;"A";IF(A1>79;"B";IF(A1>69;"C";IF(A1>59;"D";"F"))))

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (A1>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.