IF Function is one of the most powerful functions in excel. And, the best part is, that you can combine other functions with IF to increase its power.
Combining IF and OR functions is one of the most useful formula combinations in excel. In this post, I’ll show you why we need to combine IF and OR functions. And, why it’s highly useful for you.
Quick Intro
I am sure you have used both of these functions but let me give you a quick intro.
- IF – Use this function to test a condition. It will return a specific value if that condition is true, or else some other specific value if that condition is false.
- OR – Test multiple conditions. It will return true if any of those conditions is true, and false if all of those conditions are false.
The crux of both of the functions is IF function can test only one condition at a time. And, OR function can test multiple conditions but only return true/false. And, if we combine these two functions we can test multiple conditions with OR & return a specific value with IF.
How do IF and OR functions Work?
In the syntax of the IF function, have a logical test argument that we use to specify a condition to test.
IF(logical_test,value_if_true,value_if_false)
And, then it returns a value based on the result of that condition. Now, if we use OR function for that argument and specify multiple conditions for it.
If any of the conditions is true OR will return true and IF will return the specific value. And, if none of the conditions is true OR with return FALSE IF will return another specific value. In this way, we can test more than one value with the IF function. Let’s get into some real-life examples.
Examples
Here I have a table with stock details of two warehouses. Now the thing is I want to update the status in the table.
If there is no stock in both of the warehouses status should be “Out of Stock”. And, if there is stock in any of the warehouse status should be “In-Stocks”. So here I have to check two different conditions “Warehouse-1” & “Warehouse-2”.
And the formula will be.
=IF(OR(B2>0,C2>0),"In-Stock","Out of Stock")
In the above formula, if there is a value greater than zero in any of the cells (B2 & C2) OR function will return true, and IF will return the value “In-Stock”. But, if both cells have zero then OR will return false, and IF will return the value “Out of Stock”.
Last Words
Both of the functions are equally useful but when you combine them, you can use them in a better way. As I told you, by combining IF and Or functions you can test more than one condition. You can solve your two problems with this combination of functions.
Good morning. Well done!
Pls assist by putting the 6 formulae in a row. Each as you can see has diff range and corresponding diff excel formula
FOR C C (#)
≤ #21’000 1027.34 =(C3*100)/7
> #21’000 – #54’000 21009.9 =300000+(C4-21000)*100/11
> #54’000 – #129’000 56000 =600000+(C5-54000)*100/15
> #129’000 – #224’000 132000 =1100000+(C6-129000)*100/19
> #224’000 – #560’000 225000 =1600000+(C7-224000)*100/21
> #560’000 570000 =3200000+(C8-560000)*100/24
Hi – are you still looking at this? I have a question I’d like your help on if possible. Thank you! (all the post below seem to be from 2019)
I have a dropdown box that has 4 items, so one can be selected. (cell A1)
In another cell (A2) I would like to return values for each different selection:
Along the lines of.
If Item “One” selected return “1” or
If Item “Two” selected return “2” or
If Item “Three” selected return “4” or
If Item “Four” is selected return “4” or
If No Item, ” ” is selected return “1”
Can anyone help with a formula, pleas
Kind Regards
Steve
In worksheet 2, I want to use a value from a certain cell in worksheet 1, if the text in a cell in worksheet 2 contains a specific word. How do I do that?
IF (text in Worksheet 2, Cell C5 contains “VVV”, enter the value from Worksheet 1, Cell D16) OR IF (text in Worksheet 2, Cell C5 contains “WWW”, enter the value from Worksheet 1, Cell G16) OR IF (text in Worksheet 2, Cell C5 contains “XXX”, enter the value from Worksheet 1, Cell J16) OR IF (text in Worksheet 2, Cell C5 contains “YYY”, enter the value from Worksheet 1, Cell M16) OR IF (text in Worksheet 2, Cell C5 contains “ZZZ”, enter the value from Worksheet 1, Cell P16)
Very helpful.
Question: I am trying to combine 4 possible mutually exclusive possibilities into one cell but each condition has 3 conditions which must be true; e.g. Possibility #1: (cell 1=down,cell2=down, and cell 3>cell4),”Green”, OR Possibility #2 has three different “ands” then the output is “Red”, and so on until all 4 possibilities are listed. So I think that I am trying to say, IF the first set of 3 are satisfied, output Green, OR if the 2nd set of 3 are satisfied, output red, OR if the 3rd set of 3 are all true, output yellow OR if the 4th set are satisfied output blue, otherwise output black. I seem to be stuck trying to connect the 4 arguments.and would appreciate your help with the transitions using multiple OR options. Thank you
So sorry, but I have tried this formula, but there must be an error in it because it did not work for me. I am trying to learn formulas and am very new to it, so I keep rying each and every suggestion. (just as a reminder, it is an IF, OR for sales.so I am not sure what went wrong.
I wonder if you will be so kind and help me out with this issue.
I have looked everywhere for a simple IF and OR for the calculation of car milage usage. Assuming we start
in January and the car started with 15000 km. B2
In February the counter was at 18500 KM B3
In March it was 21200 KM B4
I will need 2 furmulas.
A. there is no consumpion in January, so I need to do:
: if B1 has no KM value then C2 blank,or Zero.
(find out the consumtion between February and January) if B3 has KM and C3 has KM, then deduct C3 from B3
If possible to combine those 2 formulas.
I thank you and hopefully I did not confuse you.