Auto Calculate and put a 'x' in OK or Not OK box

[Ozman89]

I have a Sheet we use to fill out and determine if a certain dimension is good from our report. I would like this to automate and figure out if the dimension is within tolerance or not for all rows.

I would like the formula to place an X in the "OK" box if the result in the J column is between F+G and F-H, Otherwise put an X in the "Not OK" box.
Example: J9=179.886 F9+G9=179.900 F9-H9 = 179.860 Therefor there should be an X in the OK box (column N) (See attached sheet)

[Aardigspook]

I can't open .xlsm files from here, but these should work based on your example:

In the OK column (N9):

Please Login or Register  to view this content.

In the not-OK column:

Please Login or Register  to view this content.

I've assumed 'greater than or equal to' and 'less than or equal to'. If you don't want the 'equal to' parts, then replace the >= with > and <= with <.

Hope that helps.

[etaf]

try
=IFERROR(IF(AND(J9>=(F9-H9),J9<=(F9+G9)),"X",""),"")
and
=IFERROR(IF(AND(J9>=(F9-H9),J9<=(F9+G9)),"","X"),"")

[Ozman89]

Thank you both for the quick replies!

Etaf this worked perfectly. Is there a way to include as well a Pass/Fail? Some of these will be automatic Pass or Fail where we will input that word. Can we have the X populate based on that as well?

I know i can make this work. =IF(J18="Pass","X","") and the Fail be =IF(J18="Fail","X","") But i don't know how to combine these with the last two formulas you helped with.

[etaf]

=IFERROR(IF(OR(J9="pass",AND(J9>(F9-H9),J9<(F9+G9))),"X",""),"")
But how do you want to handle the tolerances ?

NOT sure of the fail, as the tolerances kick in

Does Pass/Fail override the tolerance

[Ozman89]

I would like the Pass/fail to override the tolerances. In some cases the tolerances is in a degree of an angle and we know based on the tool we use to cut the feature it will always be within tolerance, unless the tool breaks so it is a automatic pass always. I have tried the suggestion and it seems to work fine until i get to line 18 of the example. For reference i am using:

=IFERROR(IF(OR($J9="Pass",AND($J9>($F9-$H9),$J9<($F9+$G9))),"X",""),"") in the OK column and
=IFERROR(IF(OR($J9="Pass",AND($J9>($F9-$H9),$J9<($F9+$G9))),"","X"),"") in the Not OK Column.

[etaf]

OK
=IFERROR(IF(J9="fail","",IF(J9="pass","X",IF(AND(J9>(F9-H9),J9<(F9+G9)),"X",""))),"")

Not OK
=IFERROR(IF(J9="pass","",IF(J9="fail","X",IF(AND(J9>(F9-H9),J9<(F9+G9)),"","X"))),"")


for the NOT OK
could if just simply say
if there is an X in the OK then put a blank in NOT OK
If there is No X in the OK then out an X in NOT OK
or would there be instances where the OK would be blank and so would the NOT OK

[Ozman89]

This worked perfectly! You are awesome!

As for the edit questions there wouldn't be a need for blanks. This report is to be filled out completly. If there are extra rows they will be deleted.

[etaf]

Ok
so in NOT OK

we could put
=IF( N9 = "X", "", "X")

so that does assume that if OK is an X then no matter what NOT OK would be blank
and if OK is a BLANK then NOT OK would always be an X