IF AND question

[Gamq]

Hi there,

I need a function that can provide one of three answers:

1 2 1 1...
3 3 4 4...
? ? ? ?...

If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if
A1=2, regardless of A2, answer 95.

The next function for column B is the same, except the answer is dependent
on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or
A3-5 etc.

Any help would be most appreciated.

 

[Ms-Exl-Learner]

Use the below formula for your first query.
=IF(A1="","",IF(A1=2,95,IF(AND(A1=1,A2=3),106,IF(AND(A1=1,A2=4),104))))

I am not clear about your second query.

Remember to Click Yes, if this post helps!

 

[Steve Dunn]

Is this it?:

A3:

=IF(A1=2,95,IF(A1=1,IF(A2=3,106,IF(A2=4,104,"Unknown")),"Unknown"))

B3:

=IF(B1=2,A3-5,IF(B1=1,IF(B2=3,A3+6,IF(B2=4,A3+4,"Unknown")),"Unknown"))


If you'd prefer an error when when A1 or A2 contain unknown values, you can
use NA() instead of "Unknown". If you are happy to have 0 as an answer for
unknown values, these can be simplified:

A3:

=(A1=2)*95+(A1=1)*((A2=3)*106+(A2=4)*104)

B3:

=(B1=2)*(A3-5)+(B1=1)*((B2=3)*(A3+6)+(B2=4)*(A3+4))


HTH
Steve D.