Nested IF Integration

[andybason]

Hello

I have the following formula in a spreadsheet:

=IF(A6=Controls!$B$3,IF(AD6="CLEAR","CLEAR",IF(AND($AB$5>="5",$AB$5<="6",MyB!$J$2=0,$AA$5="0:0"),"BACK",IF(AND($AB$5>="10",$AB$5<="11",MyB!$J$2=1,$AA$5="0:0"),"BACK",IF(AND($AB$5>="15",$AB$5<="16",MyB!$J$2=2,$AA$5="0:0"),"BACK",IF(AND($AB$5>="20",$AB$5<="21",MyB!$J$2=3,$AA$5="0:0"),"BACK",IF(OR($AA$5<>"0:0",$AC$5="HT"),"LAY")))))),"")

I am trying to amend the final IF to the following but cannot integrate it correctly. Can anyone offer any tips?

=IF(AND(AE6="SETTLED",OR($AA$5<>"0:0",$AC$5="HT")),"LAY")

Thank you

 

[Andrew Poulsom]

Like this?

=IF(A6=Controls!$B$3,IF(AD6="CLEAR","CLEAR",IF(AND($AB$5>="5",$AB$5<="6",MyB!$J$2=0,$AA$5="0:0"),"BACK",IF(AND($AB$5>="10",$AB$5<="11",MyB!$J$2=1,$AA$5="0:0"),"BACK",IF(AND($AB$5>="15",$AB$5<="16",MyB!$J$2=2,$AA$5="0:0"),"BACK",IF(AND($AB$5>="20",$AB$5<="21",MyB!$J$2=3,$AA$5="0:0"),"BACK",IF(AND(AE6="SETTLED",OR($AA$5<>"0:0",$AC$5="HT")),"LAY")))))),"")

 

[andybason]

Hello Andrew

Thank you for the reply. I had tried to integrate in the way you suggest but I get an error message which highlights the "OR" part.

Thank you

 

[Andrew Poulsom]

What version of Excel do you have? There are too many nesting levels for Excel 2003 and previous versions.

 

[andybason]

Thank you Andrew. I have tried it on both 2003 and 2010 versions. I am not trying to add an additional IF but amend an existing one. The formula works fine without the amendment

Thank you

 

[Andrew Poulsom]

The formula I posted can be entered in Excel 2010 but not in Excel 2003.

 

[konew1]

You say that the original formula is working. It doesn't address the required result if the final IF function is false.

=IF(A6=Controls!$B$3,IF(AD6="CLEAR","CLEAR",IF(AND($AB$5>="5",$AB$5<="6",MyB!$J$2=0,$AA$5="0:0"),"BACK",IF(AND($AB$5>="10",$AB$5<="11",MyB!$J$2=1,$AA$5="0:0"),"BACK",IF(AND($AB$5>="15",$AB$5<="16",MyB!$J$2=2,$AA$5="0:0"),"BACK",IF(AND($AB$5>="20",$AB$5<="21",MyB!$J$2=3,$AA$5="0:0"),"BACK",IF(OR($AA$5<>"0:0",$AC$5="HT"),"LAY","What if false")))))),"")


I think you need resolve that before you do the mod.