Formula Error

[samsilverman]

I am using excel 2007:

I have created the following formula (with some help from this forum), but I am getting an error message saying I am missing a left or right parentheses. Can someone help me? I can't figure it out.

=IF(ISBLANK(B17),"",IF(AND($I$8="AIRBORNE ISOLATION",L17<=-0.01,OR(AND(M17="",L18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,F18="",G18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18),AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,F18="",G18=""),<o></o>AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18))),"",IF(AND($I$8="PROTECTIVE ENVIRONMENT",L17>=0.01,OR(AND(M17="",L18=""),AND(M17="",L18=0,(F18+G18)>0,I18="",J18=""),AND(M17="",L18<0,(I18+J18)>0,F18="",G18=""),AND(M17>=0.01,L18=0,(F18+G18)>0,I18="",J18=""),AND(M17>=0.01,L18<0,(I18+J18)>0,F18="",G18=""))),"",1)))<o></o>

 

[Steeviee]

=IF(ISBLANK(B17),"",IF(AND($I$8="AIRBORNE ISOLATION",L17<=-0.01,OR(AND(M17="",L18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,F18="",G18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18),AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,F18="",G18=""),AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18))))),"",IF(AND($I$8="PROTECTIVE ENVIRONMENT",L17>=0.01,OR(AND(M17="",L18=""),AND(M17="",L18=0,(F18+G18)>0,I18="",J18=""),AND(M17="",L18<0,(I18+J18)>0,F18="",G18=""),AND(M17>=0.01,L18=0,(F18+G18)>0,I18="",J18=""),AND(M17>=0.01,L18<0,(I18+J18)>0,F18="",G18=""))),"",1)))

Haven't bothered figuring out where exactly the error is but this no longer gives you the error message.

Good luck!

 

[Steeviee]

=IF(ISBLANK(B17),"",IF(AND($I$8="AIRBORNE ISOLATION",L17<=-0.01,OR(AND(M17="",L18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,F18="",G18=""),AND(M17="",L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18),AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,F18="",G18=""),AND(M17<=-0.01,L18<=-0.0001,(I18+J18)>0,(F18+G18)>0,(I18+J18)>(F18+G18))))),"",IF(AND($I$8="PROTECTIVE ENVIRONMENT",L17>=0.01,OR(AND(M17="",L18=""),AND(M17="",L18=0,(F18+G18)>0,I18="",J18=""),AND(M17="",L18<0,(I18+J18)>0,F18="",G18=""),AND(M17>=0.01,L18=0,(F18+G18)>0,I18="",J18=""),AND(M17>=0.01,L18<0,(I18+J18)>0,F18="",G18=""))),"",1)))

Haven't bothered figuring out where exactly the error is but this no longer gives you the error message.

Good luck!

That's it I think

 

[Andrew Poulsom]

It's very difficult to tell where your ANDs and ORs should end. As it stands the closing parenthesis for your first AND is right at the end. If you use the cursor to move over a parenthesis the other will flash/change colour. Use that as a guide.

If you are still stuck try to break the formula down into logical chunks using Alt+Enter.

 

[Special-K99]

I think it was fine originally, it copied ok into Excel 2007 with no errors.
Strange??

 

[Steeviee]

VERY strange. I copied into Excel 2003 and it really was not happy.

 

[samsilverman]

Thanks for the help and advice Steeviee and Andrew. I'll try it out.

 

[iliace]

You're missing several parentheses. I made this version work; check to see if the logic is what you want it to be.

=IF(ISBLANK(B17), "",
   IF(
      AND($I$8="AIRBORNE ISOLATION", L17<=-0.01,
         OR(
            AND(M17="", L18=""),
            AND(M17="", L18<=-0.0001, (I18+J18)>0, F18="", G18=""),
            AND(M17="", L18<=-0.0001, (I18+J18)>0, (F18+G18)>0, (I18+J18)>(F18+G18),
               AND(M17<=-0.01,L18<=-0.0001, (I18+J18)>0, F18="", G18=""),
               AND(M17<=-0.01,L18<=-0.0001, (I18+J18)>0, (F18+G18)>0, (I18+J18)>(F18+G18))
            )
         )
      ), 
      "",
      IF(
         AND($I$8="PROTECTIVE ENVIRONMENT", L17>=0.01,
            OR(
               AND(M17="", L18=""),
               AND(M17="", L18=0, (F18+G18)>0, I18="", J18=""),
               AND(M17="", L18<0, (I18+J18)>0, F18="", G18=""),
               AND(M17>=0.01, L18=0, (F18+G18)>0, I18="", J18=""),
               AND(M17>=0.01, L18<0, (I18+J18)>0, F18="", G18="")
            )
         ),
         "",
         1
      )
   )
)

 

[samsilverman]

Now I have a NEW problem.

First, the formula no longer returns and error. Thanks Steeviee and iliace for the solutions.

But, I am getting a value of 1 where the formula is pasted instead of an expected "" when there is a value in either cell F18 or G18...even though I18+J18 > F18+G18.

What I am missing here?

 

[samsilverman]

Thanks for the help guys, but there are even more problems with my scenarios. No errors at this point but it is not returning the results i am expecting. I will do some more work on this side and re-post in this thread. Thanks again.