Excel 2016: Multiple conditions with the OR function

Updated 2017.07.30

Objectives:

·        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.

OR function overview

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.

OR function arguments

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.

Example

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 either the homework or the test.

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image001.jpg

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.

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image002.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image003.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image004.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image005.jpg http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image006.jpg

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:

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image007.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image008.jpg

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:

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image009.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image010.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image011.jpg

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

http://old.briarcliff.edu/departments/cis/Excel/2013/016-Or-Function_files/image012.jpg

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.