Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advantage of Database functions | Excel Worksheet Functions | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions |