Excel 2016: Multiple conditions with the OR function

Updated 2017.07.30

·
Learn how to use the ** OR function **to
make a decision about the value to place in a cell.

Use this workbook for the examples on this page.

The *OR*
function allows you to look at up to 255 **conditions** (logical tests which
can be either TRUE or FALSE) and combine them into a single TRUE or FALSE
value. The *OR *function will return the value *TRUE* if *any one *of
its arguments is true, otherwise it will return the value *FALSE. *It
allows you to make decisions (using the *IF* function) __based on more
than one condition__. For example, a teacher might want to create a
spreadsheet that puts the letter "P" in a cell if a student passes a
class, and puts the letter "F" in a cell if a student fails the
class. Assume that there are two conditions for passing the class: (1) the
student must have an average of __70 or more on homework__, **AND** (2)
the student must have a grade of __65 or more on the final test__. The
student will only pass if *both* of these conditions are true. __Another
way of looking at this problem__ is that the student will fail the class if *either*
of the following conditions is *TRUE*: (1) the student had an average
below 70 on the homework, **OR** the student had a grade below 65 on the
final test.

An *OR*
function must always be supplied with up to 255 arguments, and will return the
value TRUE if any one of the arguments is *TRUE*. It will return the value
*FALSE* only if __all__ of the arguments are *FALSE*.

From the
workbook, use the * Gradebook OR *worksheet for the following
example. The rule for failing the class is that the student will fail if he
fails

In Excel, we
must use the *OR* function to look at *both* conditions. We can make
our work easier if we divide the problem into smaller steps. We will use
columns I and J for this.

A student fails the homework if his homework score is less than the minimum. For George Washington, this is: B2<$F$2.

Use the fill handle to copy the formula to the next three rows:

Now put a logical test in J2 to determine if the student failed the Final Test:

Use the fill handle to copy the formula to the next three rows:

Now let's add a **Fail Class? **column--Column K. The student fails the class if the value in
column I is true OR the value in column J is true. So we need the OR function
in column K:

Use the fill handle to copy the formula to cells K3:K5:

This tells us who failed (TRUE) and who passed (FALSE), but we want the letter "P" and the letter "F" to appear, not TRUE and FALSE. Now all that needs to be done is to put a formula in column E that will look at the value in column K to see if the student failed the class:

Use the fill handle to copy this formula to cells E3:E5. You should see the following values:

Note how simple all of the formulas are when we break the problem into small parts:

Note also that we could have combined all of the functions into a single formula:

Note that
even though we have only used two conditions in the arguments to the *OR*
function here, there can be as many as 255. If __any__ one of the arguments
is *TRUE* the *OR *function will return the value *TRUE*. It
will only return the value *FALSE* if __all__ of the arguments are *FALSE*.