Go Back

How to Use the IF and OR Functions in Excel

While evaluating a dataset, you might come across to a situation where you need to test multiple conditions or logical tests. Excel’s IF function allows users to logically compare values by testing condition(s) and returning a value if that condition(s) is TRUE or FALSE.

If you have multiple conditions to logically test and want to return a value where any one of the conditions is TRUE or FALSE then IF OR statement comes into action.

Using Excel’s IF and OR functions

The Excel OR function is categorized as a logical function, and it returns TRUE if any of the given conditions are TRUE, otherwise, it returns FALSE.

As a combined IF OR statement, OR function is used as a logical_test argument and if any of the specified conditions are TRUE, OR function returns TRUE, and IF statement returns value_if_true argument’s value, otherwise it returns value_if_false argument’s value.  

The syntax of IF OR Statement;

IF(OR( condition1, [condition2], ... ), value_if_true, value_if_false)

Please note when you have entered all the conditions in the OR function then you must put a closing bracket to complete logical_test argument. After that, you need to enter TRUE or FALSE argument as part of the IF statement.

When any of the conditions are TRUE in an OR function, then you can perform calculations as per your requirement in TRUE or FALSE arguments of the IF OR formula in Excel, such as;

=IF(OR(A2=”A”,B2<=20),C2*1.5,C2*1.2)

In this IF OR formula, it says, if cell A2 value is “A” or cell B2 value is less than equal to 20, then return value after multiplying cell C2 value with 1.50, otherwise, return value after multiplying cell C2 value with 1.20. In this way, you can perform various calculations if any of the conditions are TRUE/FALSE.   

Example

Let’s suppose you have a dataset of men’s garments of a store. Each product has different prices for different sizes and colors available in stock. Management has decided to revise prices of products that come in any of the specified colors, irrespective of their sizes, and keep the rest of products’ prices same.  

Using the IF OR statement, you can specify the colors whose prices are to be revised with a certain percentage say by 10% and keep the rest of the products’ prices the same as previous. Let say any product that comes in any of colors “Red” and “Orange” the prices of those products to be increased by 10% and keep the rest of the products’ prices the same.

You need to supply these two conditions in OR function of IF OR formula. OR function will return TRUE, if the product’s color meets any of these colors (Red and Orange), and IF statement returns revised price of that product after multiplying the old price with 1.10 as specified in value_if_true argument, otherwise IF statement will return old price of that product.

IF OR statement for the above example would be as follows;

=IF(OR(C2="Red",C2="Orange"),D2*1.1,D2)

Here you can see that IF OR formula has performed the required calculations where any of the given conditions are TRUE otherwise it has returned the old values of products’ prices.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need help with this formula-for 1 cell if cell c13 = 1 and (if(e13 =1 or f13=1 or g13=1 or h13=1 or I13 =1) =1))then value = $d$6 *$D$9 or if cell d13 = 1 and ( if(e13 =1 or f13=1 or g13=1 or h13=1 or I13 =1) =1)) then value = $d$5 *$D$9 or If cell (c13 = 1 and if J13 =1) then value = $E$6 *$D$9 or If cell (d13 = 1 and if j13 =1) then value = $F$6 *$D$9 If cell (c13 = 1 and if k13 =1) then value = $E$5 *$D$9 or If cell (d13 = 1 and if k13 =1) then value = $F$5 *$D$9
Solved by I. W. in 18 mins
I'm trying to create the formula for this: If C10= 9 or 10, J10= 100. If C10= 7 or 8, J10= 75. If C10= 4 or 5 or 6, J10= 50. If C10= 2 or 3, J10= 25. If C10= 0 or 1, J10= 0 Also: If C10= 5 J10= 100. If C10= 4 J10= 75. If C10= 3 J10= 50. If C10= 2 J10= 25. If C10= 1 J10= 0
Solved by K. L. in 29 mins
if value less then or 1000 then 2300, if less then or 2000 then 3000 and if less then or 3000 then 3200
Solved by F. C. in 22 mins

Leave a Comment

avatar