Question

In Excel, give a business example in which an IF function would be useful. How would using the AND or OR functions in the logical_test argument change the way the IF function works?

Answer 1

Summary

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Purpose

Test for a specific condition

Return value

The values you supply for TRUE or FALSE

Syntax

=IF (logical_test, [value_if_true], [value_if_false])

Arguments

• logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
• value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
• value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes

Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

In the example shown, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:

=IF(C6>=70,"Pass","Fail")

Translation: If the value in C6 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".

The logical flow this formula could be reversed. A formula with the same result could be written like this:

=IF(C6<70,"Fail","Pass")

Translation: If the value in C6 is less than 70, return "Fail". Otherwise, return "Pass".

Either formula above, when copied down the column, will test every score and return the correct result.

IF function should embed an AND or OR function in the logical test, respectively.

• AND function. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.
• OR function. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met; FALSE otherwise.