Hi,

I am trying to build a formula which allows the following conditions:

the formula is on cell J2:

-if H2<0 and H2+I2<0, return H2
-if H2<0 and I2 is N/A, return H2
-if H2>0 and I2<0, return 0

so far I have bould the following but it is not working:

=IF(ISNA(I2),H2,IF(AND(H2>0,I2<0),0,IF(AND(H2<0),( H2+I2)<0),I2,(H2-I2)))

Thank you very much in advance.



Regards,

IF Condition THEN Result ELSE Alternative

You've given the Condition and the THEN Result but not the Else Alternative

What is the relationship between the equations? Are they OR or AND? Or is the second equation the ELSE Alternative for the first?

What this will do is...

IF I2=NA() THEN H2
ELSEIF H2>0 AND I2<0 THEN 0
ELSEIF H2<0 AND H2+I2<0 THEN I2
ELSE H2-I2

I'm not surprised it doesn't work. :-)

David, I agree with gebobs' diagnosis—that is, your IF formula does this:In your original post you described what you want and it's somewhat different. But maybe that doesn't matter; instead, tell me how it isn't working. What about the results don't you like?

you were missing an open parenthese after your second and, but beyond that they are right, your formula won't return what you want.

Try:

=IF(AND(H2<0,(H2+I2)<0),H2,IF(AND(H2<0,ISNA(I2)),H 2,IF(AND(H2>0,I2<0),0,(H2-I2))))