Alif Bin Hussain
While working in Excel, we often use the AND function. Sometimes we also use the OR function. There are times when we might need to use both the AND and OR functions together for various purposes. In this article, we will show you three suitable examples to use the AND and OR functions in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to AND and OR Functions
AND Function:
→ Syntax
=AND(logical1, [logical2],...)
→ Arguments Explanation
| Arguments | Required/Optional | Explanation |
|---|---|---|
| logical1 | Required | 1st logical condition. |
| logical2 | Optional | 2nd logical condition. |
| … | … | … |
→ Return Value
The AND function returns TRUE if all arguments inside it are TRUE, and returns FALSE if one or more arguments are FALSE.
OR Function:
→ Syntax
=OR(Logical1, [Logical2],...)
→ Arguments Explanation
| Arguments | Required/Optional | Explanation |
|---|---|---|
| logical1 | Required | 1st logical condition. |
| [logical2] | Optional | 2nd logical condition. |
| … | … | … |
→ Return Value
The OR function returns TRUE if any one of the arguments inside it is TRUE. It returns FALSE if all the arguments inside it are FALSE.
- Both the AND and OR functions are available in Excel 2010 up to the latest version. We used Excel 2021 for this article.
3 Suitable Examples to Use AND and OR Functions in Excel
In this article, we will demonstrate three suitable examples to use the AND function and the OR function in Excel. We will use the following dataset for this purpose.
Example 1: Check Revenue Earned from Product
In the first example, we will check if the revenue earned from a product is more than the expected amount or not. The steps are discussed in the following section.
Steps:
- First of all, create a column and name it “Output”.
- Next, select cell E5 and write down the following formula.
=OR(AND(C5="Printer",D5>9000),AND(C5="Headset",D5>3500))- Then press Enter to get the result.
- AND(C5=”Printer”,D5>9000) returns TRUE if cell C5 is “Printer” and Revenue Earned is greater than $9,000, otherwise, it returns FALSE.
- AND(C5=”Headset”,D5>3500) returns TRUE if cell C5 is “Headset” and Revenue Earned is greater than $3,500.
- =OR(AND(C5=”Printer”,D5>9000),AND(C5=”Headset”,D5>3500)) returns TRUE if one or both of the statements are true, otherwise, it returns FALSE.
- After that, AutoFill the formula to the rest of the cells in column E.
- Hence, you will have your desired result.
Read More: How to Return TRUE or FALSE Using Excel AND Function
Example 2: Determine Eligibility and Amount of Bonus
In this example, we will determine whether a sales representative is eligible for a bonus and how much amount of bonus he or she will receive. We will use the AND function and the OR function in Excel for this purpose. Keep on reading to learn the steps to do so.
Steps:
- First, select cell E5 and type the following formula.
=IF(OR(D5>=4000,AND(C5="Printer",D5>=9000))=TRUE,D5*0.1,0)- After that, hit Enter and you will get the result.
- AND(C5=”Printer”,D5>=9000) will return TRUE if cell C5 is “Printer” and Revenue Earned >= $9,000.
- OR(D5>=4000,AND(C5=”Printer”,D5>=9000))=TRUE returns TRUE if the above statement is true or Revenue Earned from products other than the printer is greater than or equal to $4,000.
- =IF(OR(D5>=4000,AND(C5=”Printer”,D5>=9000))=TRUE,D5*0.1,0) will return 10% of the Revenue Earned if the OR function returns TRUE, otherwise, it will return $0.
- Finally, AutoFill the formula to the rest of column E to get the amount of bonus in all cells.
Read More: How to Use IF with AND Function in Excel (Formula + VBA)
Example 3: Calculate Results of Students
In this example, we will calculate the results of students. We will determine if a student passed or failed depending on two test scores. You can follow the procedure discussed below.
Steps:
- Insert the following formula in cell E5.
=IF(OR(AND(C5>=60,D5>=60),C5+D5>=120)=TRUE,"Pass","Fail")- Then press the Enter button on your keyboard.
- AND(C5>=60,D5>=60) returns TRUE if scores in cells C5 and D5 both are greater than or equal to 60.
- OR(AND(C5>=60,D5>=60),C5+D5>=120)=TRUE returns TRUE, if C5 and D5 both are greater than or equal to 60 or summation of C5 and D5, is greater than or equal to 120, otherwise, it returns FALSE.
- =IF(OR(AND(C5>=60,D5>=60),C5+D5>=120)=TRUE,”Pass”,”Fail”) will return “Pass”, if the above statement returns TRUE. However, if it returns FALSE, the IF function will return “Fail.”
- After that, AutoFill the formula to the rest of the cells in column E.
- As a result, you will get your desired results for all scores.
- Don’t forget to give proper cell references or you won’t get the desired result.
Read More: Nested IF and AND Functions in Excel (4 Suitable Examples)
Conclusion
Thanks for making it this far. I hope you find this article useful. Now you know three examples of how to use AND and OR functions in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.
