IF AND OR functions
I have one cell that has 5 precedents, I can change one precedent using IF
function, however that one precedent can change 20 different ways. I've been using the IF and AND function in the following way IF(A1=AA1,BB1)*AND(A2=CC1)...(four more times), AND is a set of conditions that have to happen for BB1 to formulate. AA1-AA20, BB1-BB20, CC1-CC20 represent lists used for Data Validation in A1, A2 and so on. I'm thinking that I would like to write my formula like this: IF(A1=AA1,BB1)Or if (A1=AA2,BB2)*AND(A2=CC1), but I'm not getting the results that I'm after. How do I write the formula: IF 'all these conditions apply' OR IF 'all these conditions apply' THEN fill in the cell. I hope this isn't hard to understand, sorry if it seem muddy. Thanks for any help. Jig |
IF AND OR functions
Not sure if this is what you mean, but I'll give it a shot:
For the first one, IF(A1=AA1,BB1)*AND(A2=CC1)...(four more times), try: =IF(AND(OR(A1=AA1,A1=BB1),A2=CC1,[Condition 2],[Condition 3],[Condition 4],[Condition 5]),"Yes","No") For IF(A1=AA1,BB1)Or if (A1=AA2,BB2)*AND(A2=CC1), try: =IF(OR(OR(A1=AA1,A1=BB1),AND(OR(A1=AA2,A1=BB2),A2= CC1)),"Yes","No") This assumes by A1=AA1,BB1 you mean that A1 has to equal either AA1 or BB1. (I'm just guessing though) If you're trying to match A1 to some value in AA1:AA20, you might want to consider using other functions, like INDEX, MATCH, VLOOKUP, etc. Scott On Jan 26, 5:17 pm, Jiggy wrote: I have one cell that has 5 precedents, I can change one precedent using IF function, however that one precedent can change 20 different ways. I've been using the IF and AND function in the following way IF(A1=AA1,BB1)*AND(A2=CC1)...(four more times), AND is a set of conditions that have to happen for BB1 to formulate. AA1-AA20, BB1-BB20, CC1-CC20 represent lists used for Data Validation in A1, A2 and so on. I'm thinking that I would like to write my formula like this: IF(A1=AA1,BB1)Or if (A1=AA2,BB2)*AND(A2=CC1), but I'm not getting the results that I'm after. How do I write the formula: IF 'all these conditions apply' OR IF 'all these conditions apply' THEN fill in the cell. I hope this isn't hard to understand, sorry if it seem muddy. Thanks for any help. Jig |
IF AND OR functions
On Jan 26, 5:17 pm, Jiggy wrote:
I have one cell that has 5 precedents, I can change one precedent using IF function, however that one precedent can change 20 different ways. I am not sure what you mean by "can change 20 different ways". I've been using the IF and AND function in the following way IF(A1=AA1,BB1)*AND(A2=CC1)...(four more times), AND is a set of conditions that have to happen for BB1 to formulate. I am not sure what is repeated "four more times". Could the above be simplified as folllows: =if(and(A1=AA1,A2=CC1,...), BB1, 0) Note that AND() can have as many as 30 conditions. I'm thinking that I would like to write my formula like this: IF(A1=AA1,BB1)Or if (A1=AA2,BB2)*AND(A2=CC1) Perhaps: =if(and(A2=CC1,A3=CC2,...), if(A1=AA1,BB1,if(A1=AA2,BB2,0)), 0) but I'm not getting the results that I'm after. How do I write the formula: IF 'all these conditions apply' OR IF 'all these conditions apply' THEN fill in the cell. It is not clear what results you are after. This last question is very different from the example logic before it. Perhaps you are looking for the following structu =if(or(and(cond1,cond2,...), and(condA,condB,...)), BB1, 0) Or perhaps: =if(and(cond1,cond2,...), BB1, if(and(condA,condB,...), BB2, 0), 0) But the latter question does not match the example logic. |
IF AND OR functions
just trying to practice..the basic way maybe
=((A1=AA1)*BB1) +((A1=AA2)*BB2*(A2=CC1)) + + + +..... -- ***** birds of the same feather flock together.. "Jiggy" wrote: I have one cell that has 5 precedents, I can change one precedent using IF function, however that one precedent can change 20 different ways. I've been using the IF and AND function in the following way IF(A1=AA1,BB1)*AND(A2=CC1)...(four more times), AND is a set of conditions that have to happen for BB1 to formulate. AA1-AA20, BB1-BB20, CC1-CC20 represent lists used for Data Validation in A1, A2 and so on. I'm thinking that I would like to write my formula like this: IF(A1=AA1,BB1)Or if (A1=AA2,BB2)*AND(A2=CC1), but I'm not getting the results that I'm after. How do I write the formula: IF 'all these conditions apply' OR IF 'all these conditions apply' THEN fill in the cell. I hope this isn't hard to understand, sorry if it seem muddy. Thanks for any help. Jig |
All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com