Fabulous Examples of Excel Functions:
AND and OR
This tutorial covers the Excel functions of AND and OR, and contains many examples and a sample worksheet to show how to use these functions. We also discuss the common practice of using these functions in conjunction with the IF function.
Author: Patricia Lynn
If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons) and found it very well done.
The AND and OR functions are identical in that they ask Excel to test from one to 255 conditions, and to return a value of TRUE or FALSE. To achieve a value of TRUE, all conditions of the AND function must be true, but just one condition of the OR function has to be true.
Click on a link below to go directly to that portion of the tutorial. Don't miss our other Excel tutorials listed at right.
AND
Use the AND function to test if all of a group of conditions are met. If so, Excel returns a value of TRUE. Otherwise, it returns a value of FALSE. The syntax for the AND function is:
=AND(test1,test2...)
… where test1, test2, ... are from 2 to 255 conditions that Excel will test that can be either TRUE or FALSE. If Excel cannot arrive at a TRUE or FALSE conclusion, it will return the #VALUE! error value.
- =AND(3+3=6,4*2=8) ... Excel returns a value of TRUE because both conditions are met
- =AND(1+1=2,2*2=4,12/3=8) ... the first two arguments are true, but 12÷3≠8, so Excel returns a value of FALSE
Now let's look at some worksheet examples using cell references and more complex formulas. The actual functions were entered in column A and we show the functions in column B.
Example #1: the AND function can be used to see if the contents of a cell fall within a number range. In this example, Excel returns a value of TRUE because the value in cell D1, which is 12, is both > 10 and < 30.
Example #2: the first condition asks if cell C1 contains "Paul." In functions, you must enclose text in quotation marks. The second condition test if the contents of D1 are not equal to 10. In Excel, "not equal to" is specified by <>. Both of these conditions are met.
Example #3: the second condition makes a simple > test, but the first condition contains the SUM function. We are asking Excel if the sum of the contents of cells D1 through D3 are greater than 20. (Notice that the SUM function must be enclosed in parentheses.) Both of these conditions are met. For more information about the SUM function, see Microsoft Excel: The SUM Function.
If the second condition also contained a SUM function, the AND function would look like: =AND((SUM(D1:D3)=90),(SUM(E1:E3)=28))
Example 4: this example merely illustrates that the conditions tested can be math formulas as well. Excel returns a value of FALSE because D2+E2 is not > 10.
Example 5: in real life, some of our Excel formulas and functions can get complicated. And figuring out the parenthesis can be challenging. The first condition tests to see if the value in D1, divided by the sum of the contents of cells D1 through D3, is greater than .33. In effect, we are testing to see if Paul got more than one-third of the total points.
Study the parenthesis carefully: =AND((D1/(SUM(D1:D3)))>0.33,D1>10 )
AND with IF
Here is an example of the AND function inside an IF function. The OR function can be used similarly. For more information about the IF function, see our tutorial on Microsoft Excel Functions: IF and Nested IFs.
The IF function has three values: 1) a logical test, 2) action to take if test proves true, and 3) action to take if test proves false.
In this example, we are testing whether or not students pass a course based on two different test scores. This function asks Excel to see if the value in H3 is ≥ 500 AND the value in H4 is ≥ 800. If both conditions are met, we ask Excel to display Pass in the cell. Otherwise, we ask Excel to display Fail in the cell.
- =IF((AND(H3>=500,H4>=800)),"Pass","Fail")
OR
Use the OR function to test if any of a group of conditions is met. If even one condition is met, Excel returns a value of TRUE. If none of the conditions is met, Excel returns a value of FALSE. The syntax for the OR function is:
=OR(test1,test2...)
… where test1, test2, ... are from 2 to 255 conditions that Excel will test that can be either TRUE or FALSE. If Excel cannot arrive at a TRUE or FALSE conclusion, it will return the #VALUE! error value.
- =OR(2+2=4,3+3=6) ... Excel would display TRUE in the cell because at least one condition is met
- =OR(2+2=4,3+3=8) ... Excel would display TRUE in the cell because the first condition, 2+2=4, is true
- =OR(2+2=5,3+3=8) ... Excel would display FALSE in the cell because neither condition is met
Typically, in an Excel worksheet, cell references are specified in functions - as we demonstrate below:
- =OR(G4="John",G4="Bob",G4="Judy") ... if any of these conditions are met, Excel returns a value of TRUE in the cell. Text must be wrapped in quotation marks
- =OR(C3+C4>200,C5="Sales",D3/D4=2) ... the conditions may contain math formulas and functions as well
In Conclusion
Let's end with a few important facts. First, all Excel functions start with an equal = sign unless they are imbedded inside other functions.
Second, consider locking cells containing functions and formulas, and protecting the worksheet, to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.
Lastly, when creating complicated functions and formulas, it can be helpful to first type them into a Notepad file, and then copy and paste the function or formula into the cell. Notepad is a simple editor that comes free with Windows. Do not copy formulas or functions from Microsoft Word or other word processing programs.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions-and-or.shtml">Excel Functions: AND and OR</a>
Related Tutorials
► Microsoft Excel: Power and Square Root Functions
