Nested if (and

[aron.silan]

Hi there,

Any help would be much appreciated.

=IF($F26>0,0,+IF(AND($H26>0,M$1>=$H26),$L26)+IF(AND($H26<0,M$1>=$K26),$L26))

I'm struggling to get the following to work. It seems to pick up the first IF and the second IF (AND) however it totally disregards the last IF statement.

Basically the way it should work is: If F26 is 0, put in a 0, otherwise if H26 is greater than 0 and M1 is greater than H26 the put in L26, otherwise IF H26 is less than 0 and M1 is greater than K26 then put in L26

Any suggestions??

[teylyn]

aaron,

lose the "+" sign. It's not needed in Excel and is only testament of previous Lotus 123 usage.

I'm not sure what you're trying to achieve, but maybe:

=IF($F26>0,0,IF(AND($H26>0,M$1>=$H26),$L26,IF(AND($H26<0,M$1>=$K26),$L26)))

[aron.silan]

Hi,

=IF($F26>0,0,IF(AND($H26>0,M$1>=$H26),$L26,IF(AND($H26<0,M$1>=$K26),$L26,"")))

Lost the +, then I get an error, so I put the "" at the end of the function (so if false) and it still seems to disregard the last statement. Basically (so he says) if F26 is greater than 0 do nothing, if not then do the other 2 IF (AND) statements.

[teylyn]

Lost the +, then I get an error,

what error?

post a workbook and explain what you are trying to achieve.

[DonkeyOte]

Basically the way it should work is: If F26 is 0, put in a 0, otherwise if H26 is greater than 0 and M1 is greater than H26 the put in L26, otherwise IF H26 is less than 0 and M1 is greater than K26 then put in L26

Perhaps:

=IF($F26=0,0,IF(OR(AND($H26>0,M$1>=$H26),AND($H26<0,M$1>=$K26)),$L26,"?"))

not clear what should happen if the conditions do not hold true (have entered ? - modify as desired)