Excel OR: How to Use Logical Functions in Excel

Excel is one of the most popular spreadsheet software in the market today. Its ease of use and versatility make it quite popular. If you’ve been using Excel for a while, chances are you’ve had to do data comparisons or logical operations of some kind. In today’s article, we’ll walk you through how to use the logical OR function. Note this is an intermediate Excel topic, and we assume you have already completed an Excel Beginner course. If not, do hop over and first take up this easy Excel course.

Logical functions in MS Excel include IF, OR, AND, TRUE, FALSE AND NOT. Today we will look at the OR() function only. If you’d like to explore the others, feel free to check out this course that deep dives into Excel formulas. Note that our discussion for the OR function will work for all versions of MS Excel i.e. from the oldest version MS Excel 95 to the latest version Excel 2013.

OR Function: What Does It Do?

The OR function is a basic logical function that is used to compare any two statements or values. If you’re familiar with any kind of programming language, you have most probably used it before. It’s construct is basically

(A OR B)

Very simple. If either statement A or statement B are true, the OR condition will return ‘True’. Else it will return false. Here’s what the truth table for OR looks like

How to use Excel OR Function

You must understand that like all logical functions, the value returned for the output is a plain True or False. In Excel, the OR lets you compare up to 255 logical statements. The formula for the OR function is

“ =OR(Logical 1, logical 2,logical 3…logical 255)”

where “Logical” refers to the condition or cell reference that has to be checked. An example for the OR function will make it more clear.  Try this out along with us:

1. Open an Excel worksheet.
2. Enter the following values into the cells B1 to B5:- 10, 25, 75, 125, 150
3. Click on another cell say C1, where you want to results to be displayed.
4. Click on the Formulas on the Menu tab.
5. Click on the Logical Functions from the Ribbon. Open the drop-down menu.
6. Choose OR function from the drop-down list. On clicking this function a dialog box will open.
7. Next you have to click on the Cell B1 to enter the first cell reference in the logical1 box
8. After the cell reference, enter >100 in the logical1 box.
9. Repeat this procedure for the cell reference up to B5
10. Now click OK button.
11. This formula will return the value “True” in the cell C1 because the value in the cell B2 is greater than 100.
12. If you want the output to be “False”, type a number in cell B2 less than 100.

A few pointers while using OR()

• If any array or cell reference argument contain empty cells, those values will be ignored.
• If the specified cell range contains no logical values, OR function returns the #Value! (its the error value)
• To check whether a value occurs in an array, you can use OR array formula. However, keep in mind that you have to press the keys CTRL+SHIFT+ENTER and not simply the return key.

Nesting OR Function inside the IF Function in Excel

The OR function is rather limited when you use it as a standalone function. You can expand the scope of the OR function when you combine it with the IF function. Let’s check this out with help of an example.

1. Enter random values in the column B from B1 through B3.
2. Place your cursor on the cell C1.
3. In the formula bar(fx) enter this formula =IF (OR(B1>100,B2>100, B3>100),”Exceeds Budget”,”Acceptable”)

When you press the enter key, if any of the values in the cells (B1 to B3) contains a value greater than 100, the IF function will return the statement “Exceeds Budget” in the Cell C1. Similarly, if all the values in the cells B1 to B3 are less than 100, the IF function will display the statement “Acceptable.”

Combining IF, OR and AND Functions

Most real world scenarios require a combination of the logical statements. Let’s take an example. Assume a scenario where a customer who spends $200 or more will receive a discount of 20 percent. However, the customer has to be the member of the supermarket. If he is not a member he gets only 10%. Here we have to test for two conditions – Whether the customer has spent $200 or more and is this customer a member.

To perform the logical test, we need to use the IF function, the AND function nested within the IF function will test whether the customer has spent at least $200 and if he is a member. The OR function will determine if the customer has met at least one of the criteria to qualify for a discount.

Enter these values in your worksheet to get the return value for the above example: In B1 enter 20%, B2 10% and in B3 200 for the membership discount, non-membership discount and the minimum purchase value respectively. Now make a list of customer names starting in Cell A6, in B6 enter the amount the customers spent, in C6 enter the membership status- yes or No.

In D6 enter the following formula

=IF((AND(C6=”Yes”,B6>=$B$3)),B6*$B$1,IF((OR(B6>=$B$3)),B6*$B$2,”No Discount”))

This formula tests whether the customer is a member and if she has spent at least $200. If both the criteria are true, multiply the purchase the purchase amount by 20%. This will return the discount. The OR function will test whether the concerned customer has spent at least $200 even if she is not a member. If this criterion is met, multiply by 10% to avail the discount. The answer will be “No Discount”, if no criteria are true.

We hope this gives you a feel of how you can use conditionals to get Excel to work for you. We encourage you to go a step further and take this course towards mastering Excel.