IFERROR statement with an If(and... formula

Elizabeth Aird

I need help. I can get the if/and statement to work, BUT if the cell is blank it gives one of the options.

=IFERROR(IF(AND([Bottom OP]1 >= [Spec - Low]1, [Bottom OP]1 <= [Spec - High]1)), "In Spec", "Out of Spec")), 0)

without the iferror statement it will say "Out of Spec" where I want it to say nothing.

Help appreciated!!

Paul Newcome

You just have an extra parenthesis after [Spec - High]2.

Try this one...

=IF(ISNUMBER([Bottom OP]2),IF(AND([Bottom OP]2>=[Spec - Low]2,[Bottom OP]2<=[Spec - High]2),"in spec", "OUT OF SPEC"),"")

Paul Newcome

Just a few issues with the parenthesis...

=IFERROR(IF(AND([Bottom OP]1 >= [Spec - Low]1, [Bottom OP]1 <= [Spec - High]1), "In Spec", "Out of Spec"), 0)

I am not sure though if this is going to work how you intend based on your description of what you are trying to do.

Exactly what cell are you referring to when you say "cell is blank"?

Elizabeth Aird

Operators may enter "top op" AND " bottom op" but not always. I don't want a false "out of spec" statement.

Paul Newcome

Ok. So [Top Op1] would only be populated if there is a value in the [Top OP] column and same for [Bottom Op1] with the [Bottom OP] column?

Andrew Stewart

I would recommend testing specifically for a blank (or better still numeric) first, then you do not really need the IFERROR.

EG:

=IF(ISNUMBER([Bottom OP]1),IF(AND([Bottom OP]1 >= [Spec - Low]1, [Bottom OP]1 <= [Spec - High]1)), "In Spec", "Out of Spec"), "")

Paul Newcome

@Andrew Stewart Yes. That is the route I was going to take based on the feedback from the poster, but I didn't want to get things muddled up if that was not the case.

Elizabeth Aird

=IF(ISNUMBER([Bottom OP]2),IF(AND([Bottom OP]2>=[Spec - Low]2,[Bottom OP]2<=[Spec - High]2)),"in spec", "OUT OF SPEC"),"")

did I miss anything? still getting an error... :(

Paul Newcome

You just have an extra parenthesis after [Spec - High]2.

Try this one...

=IF(ISNUMBER([Bottom OP]2),IF(AND([Bottom OP]2>=[Spec - Low]2,[Bottom OP]2<=[Spec - High]2),"in spec", "OUT OF SPEC"),"")

Elizabeth Aird

woohoo!!!!!! Thanks!!!!

Paul Newcome

Happy to help! 👍️