using "&" to combine IF() formulas

[nilizandr]

Hello,
I have a formula which is returning a #VALUE! error, and I'm not sur
why.

I am trying to combine several IF() formulas so that Excel understand
that: If A is true, the result is X; if B is true, the result is Y; i
C is true, the result is Z. etc. The list of A, B C, etc. is exaustiv
-- all possible outcomes are reflected.


The IF() formulas, which are components of the longer formula, wor
independently, but the error occurs when I try to combine severa
formulas with the "&" symbol. Here is the formula. Any thoughts??

=IF(AND(A8=1,D8=-1,F8=1),0)&IF(AND(A88=1,D88=-1,F88=-1),2)&IF(AND(A88=1,D88=1,F88=1),3)

Thanks!
Nil

 

[Guest]

Your IF statements aren't complete. You need a third
argument in each to cover the cases where the AND is false.
Thus...
=IF(AND(A8=1,D8=-1,F8=1),0,"firstANDfail")&IF(AND
(A88=1,D88=-1,F88=-1),2,"secondANDfail")&IF(AND
(A88=1,D88=1,F88=1),3,"thirdANDfail")

Hope this works for you.

 

[Frank Kabel]

Hi
try:
=IF(AND(A8=1,D8=-1,F8=1),0,IF(AND(A88=1,D88=-1,F88=-1),2,IF(AND(A88=1,D
88=1,F88=1),3,"not defined")))