IF AND OR functions

[Guest]

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

 

[Scott]

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

 

[joeu2004]

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 structure:

=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.

 

[Guest]

just trying to practice..the basic way maybe

=((A1=AA1)*BB1)
+((A1=AA2)*BB2*(A2=CC1))
+
+
+
+.....