Inspection Form With Pass/Fail Formula

[Z_Man]

I need help setting up an inspection form with a pass/fail result column. I would like to be able to enter the actual dimension after measuring the part then have the pass/fail column turn green for with word "pass" or turn red for with the word "fail". I've included the tolerance used as well on the spreadsheet. Is there anyway this all can work together in a formula?
.

 

[Miner]

Try using an IF statement along with conditional formatting.

 

[Miner]

Attached is an example of what you can do.

 

[Z_Man]

Thank you so much for your help! Is there a way to show two places after the decimal point without having it error to "FAIL"? Also should I insert a text box to show any geometric symbols such as diameter, etc? Or is there another to trick the formula to allowing the use of words or symbols included with the number without having it error also?

 

[Miner]

Try this.

 

[Z_Man]

Awesome work Miner!


Few more questions.



Is it possible to be able to have two dimensions in the same column and get the formula to work?



Also is there a way to have a plus/minus tolerances together with this formula as well? Or will that require another formula and special formatting.



I've attached an examples within the spreadsheet to show what I'm asking.


Sorry for all the questions. We're updating our inspection process at work on our forms with excel. We've been using a basic excel format.

 

[Miner]

Regarding two dimensions (i.e., 8.05/8.00 or +.127/-.0) in the same column, there is no way to use that in a formula. Excel will interpret that as text instead of numbers. If you create columns for the actual Maximum and Minimum specifications, you can run the formulas from those columns and put whatever you want in the Print Dimension and Tolerance columns

 

[Proud Liberal]

No, you cannot put two values in ACTUAL column.

Taking a slightly different approach: adding F & G column (hidden in outline), the formulas for RESULT can be much simpler. Also, moving the header information to another tab permits you to size the report columns any way you want. I also put the print excerpts into the comments in column B. Columns F, G, and I all contains formulas that would need to be copied down when adding more rows.

 

[Z_Man]

I was wondering if there is a formula that will work with fractions on an inspection form? I have an older drawing from a customer that calls out some dimensions in fractions and others in decimals.

 

[Miner]

Certainly. See the attached file for an example. It's not the formulas, but the formatting and data entry. Format the cells as fractions, then enter the data as fractions. The answer will then display as a fraction.