IF AND OR Function Combinations

Do you need to use a logical condition in your formulas? Do you need functions like between but can’t find it? This post will show you how to deal with such issues using if and or function combinations.

We have already covered usage of If Function and nesting functions inside each other in previous posts. This time I’m going to show you how to overcome a problem you are surely going to come across sooner or later if not already. In order to use conditions like between you need this approach (this is actually a normal nested function usage).

AND Function:

And function is used for checking whether all of multiple given conditions are met. If all given conditions are true, than formula result will be TRUE. Likewise If any of given conditions are false, than formula result will be FALSE.

Here is a quick example: For cells A1:A5 has values from 1 to 5;

=AND(A1<6;A2<6;A3<6;A4<6;A5<6) will result as TRUE because all values are smaller than 6

=AND(A1<5;A2<5;A3<5;A4<5;A5<5) will result as FALSE because A5 is not smaller than 5

OR Function:

Or function is used for checking whether any of multiple given conditions are met. If any of given conditions are true, than formula result will be TRUE. Likewise If all of given conditions are false, than formula result will be FALSE.

Here is a quick example: For cells A1:A5 has values from 1 to 5;

=OR(A1<2;A2<2;A3<2;A2<2;A5<2) will result as TRUE because A1 smaller than 2

=OR(A1<1;A2<1;A3<1;A4<1;A5<1) will result as FALSE because non of the values are smaller than 1

IF AND OR Function Combinations:

Now that we covered AND Function and OR Function, it is time for using IF AND OR Function Combinations.

Garage selection example:

=IF(AND(“Car”;”Motorbike”);”Big Garage”;”Small Garage”) if you have both car and motorbike, you’ll get the big garage, if not, you’ll get the small garage.

=IF(OR(“Car”;”Motorbike”);”Garage”;”No Garage”) if you have either car or motorbike you’ll get a garage, if you don’t have either, you won’t get a garage.

After systematically coming here, it doesn’t feel much special, so I prepared a longer example for this.

Assume that we have month values from 1 to 12 in cells A1:A12. We are going to calculate corresponding quarter values for these months in cells B1:B12.

AND Version:

=IF(A1<=3;1;IF(AND(A1>3;A1<=6);2;IF(AND(A1>6;A1<=9);3;4)))

OR Version:

=IF(OR(A1=1;A1=2;A1=3);1; IF(OR(A1=4;A1=5;A1=6);2; IF(OR(A1=7;A1=8;A1=9);3;4)))

Both formula will give the same result. For months 1 to 3 B column will be 1, for months 4 to 6 B column will be 2 and so on…