Expand IF tests

Recommended Posts

Expand IF tests

One of the first functions beginning Excel users learn is the IF function, which takes the form =IF(condition,true_result,false_result). When you use this function, you typically test only one condition at a time. For instance, in the formula =IF(A1>10,"Great","Average"), the only condition being evaluated is whether the value in cell A1 is greater than 10.

Sometimes, however, you want to display a result depending on whether any one of multiple conditions is true. For example, suppose you need a formula that says: If A1 contains 10, or if A15 is greater than 20, or if A25 is less than 100, then display "Great." However, if none of those conditions is true, then display "Average."

You create such a formula in no time by combining the IF and the OR functions. The OR function takes the form OR(condition1, condition2, condition3), and evaluates to a logical true value if any of the conditions is true. If all of the conditions are false, then the OR function evaluates to a logical false. In our example, the formula would take the form

=IF(OR(A1=10,A15>20,A25<100),"Great","Average").

The OR function will evaluate to true, and the formula with return "Great," if any of the three conditions is true. If all three of the conditions evaluated by the OR function are false, the OR function returns a value of false and the IF test returns "Average."

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.