Complex IF/AND statement

[jtfreed]

This is the formula:

=IF(AND($M$1=1,F27>119999),"ERROR
",IF(AND($M$1=1,F28>119999),"ERROR.",IF(AND($M$1=1,F29>119999),"ERROR
-",IF(AND($M$1=1,F30>119999),"ERROR",IF(AND($M$1=1,F31>119999),"ERROR",IF(AND($M$1=1,F32>119999),"ERROR
",IF(AND($M$1=1,F33>119999),"ERROR ","")))))))

I would like to simplify it somehow. Surely there must be a way. I
tried this

=IF(AND($M$1=1,F27:F33>119999),"ERROR - You must use a State Fund for
this deposit.","")

but it returns an error of #VALUE.

Can any tell me how to condense this formula in some way. Thanks!

 

[Jerry W. Lewis]

=IF($M$1=1,IF(F27>119999,"ERROR
",IF(F28>119999,"ERROR.",IF(F29>119999,"ERROR
-",IF(MAX(F30:F31)>119999,"ERROR",IF(MAX(F32:F33)>119999,"ERROR
",""))))),"")

IF the order of evaluation is unimportant, you coould combine F27 with
F32:F33 and eliminate one level of nesting.

If you don't need to preserve the differences in trailing characters
after "ERROR" for some of the cases, then the whole thing collapses to

=IF(AND($M$1=1,MAX(F27:F33)>119999),"ERROR","")

Jerry