Combining IF and AND functions
Posted by Holle on December 20, 2001 11:05 AM
How do I combine IF and AND functions in a formula? For example: =IF(A5=2,I5). I also want to add the condition =AND(A6=I6,10 to 15). How would I write this formula?
Posted by Joe Was on December 20, 2001 11:19 AM
You can only nest seven levels of IF statments, but you can reference a cell that includes additional IF statments to extend this limit.
The IF statement has the syntax: Result=IF(test,Then,Else)
So, with this in mind you can replace the "Else" part with a new "IF" up to seven times. You can also mix functions within your IF statements, like:
=IF(AND(I5>0,I5<5),1, IF(AND(I5>4,I5<9),2, IF(AND(I5>8,I5<13),3, IF(AND(I5>12,I5<17),4, IF(AND(I5>16,I5<21),5, IF(AND(I5>20,I5<25),6,0))))))
Note: AND(I5>0,I5<5) is: test1.
,1, is: Then 1.
IF(And(I5>4,I5<9) is: test2.
,2, is: Then 2.
...
If(AND(I5>20,I5<25) is test6.
,6, is: Then 6. and
,0)))))) is: the last Else.
This groups up to 24 collections into groups of no more than 4. With nested IF statements the trick is the "()" parentheses order. JSW
Posted by Jacob on December 20, 2001 11:19 AM
Hi
Try this
=if(and(this=that,something else=something else),do this if true,do this if false)
Jacob
Posted by Aladin Akyurek on December 20, 2001 11:19 AM
Holle --
It's unclear (to me) what you want. But, here an example for bulding up a condition argument with AND in an IF formula:
=IF(AND(B1>C1,B1 < D1),B1*5%,0)
which says: if the value in B1 is between the values in C1 and D1 exclusive, multiply the value in B1 with 5%, otherwise return 0.
Aladin
Posted by Tom Dickinson on December 20, 2001 11:27 AM
nest the and conditions together in the condition:
IF(and(condition1,condition2,...),then statement, if statement)
=If(and(A5=2,A6=I6),I5)
