Tolerances Logic for Excel

[Jeff Smith]

Can anyone help in this re-post?

I have tried and tried to solve the logic of Pass/Fail to applicable
tolerances:

The table of tolerances are

Situation A Situation B

<= 60 = +20 -20 +30 -20
80 = +20 -20 +30 -30
100 = +30 -30 +40 -40

=120 = +40 -40 +40 -40

I need to measure the number of results that are out of tolerance in a list
(up to 1500 records). I envisage dedicating a column to fill with "0"/"1"
or "TRUE"/"FALSE" or "PASS"/FAIL".

I have spent many hours on this problem and am no nearer to solving the
logic than when I started. Has anyone encountered a similar situation? Or
can someone "see" the logic?

I would be even more impressed if someone can help with how to "highlight"
in Conditional formatting so the Pass/Fail can be detected at the time of
data entry. This is less important than counting the "failures".

Situation A is sampling and testing the product at the point of sale where
confidence the sample is representative of the batch is higher than
Situation B where sampling is prior to release of the product from the
production facility.

The range of values are stipulated at 60, 80, 100 and 120 but it is
permissible to supply values below 60 and above 120 but the tolerances
remain at the lower and upper levels regardless.

It is easy for a persons mind to determine pass/fail but not so easy to
define for a program. The real need I have is to determine the number of
non-conformances as there is a permitted nuimber of "failures" per group of
10 results.

Thanking any of you wonderful experts in grateful anticipation.

sincerely

Jeff Smith

 

[Trevor Shuttleworth]

Jeff

I'm not sure if it's just me (probably) but I don't understand the rules of
engagement here. It would help me to understand what you are trying to do
and perhaps provide a formula or code if I had some specific examples of
what is within and without the tolerance. If you have a value of say 85 is
that out of tolerance compared to 60 +/-20 or within tolerance compared to
80 +/-20

"It is easy for a persons mind to determine pass/fail " ... maybe not so !

Maybe you could post some sample data indicating how you determine
pass/fail, etc

Regards

Trevor

 

[Jeff Smith]

I have added some examples below (in the body of the query) for
clarification. Hope it helps. Jeff.

Jeff

I'm not sure if it's just me (probably) but I don't understand the rules of
engagement here. It would help me to understand what you are trying to do
and perhaps provide a formula or code if I had some specific examples of
what is within and without the tolerance. If you have a value of say 85 is
that out of tolerance compared to 60 +/-20 or within tolerance compared to
80 +/-20

"It is easy for a persons mind to determine pass/fail " ... maybe not so !

Maybe you could post some sample data indicating how you determine
pass/fail, etc

Regards

Trevor

Example
Specified =100mm. Test Result Situation A = 140mm = Fail. If situation B =
Pass

Specified = 100mm. Test Result Situation A = 70mm = Pass If situation B =
70 = Pass.

Specified = 80mm. Test Result Situation A = 110 = Fail but Situation B =
Pass.

(These are all termed consistence tests, and results are physical
measurements in mm)

 

[Trevor Shuttleworth]

Jeff

OK, since the solutions aren't rolling in ... let's give it a go. I don't
know if this will cover all the scenarios but it copes with your examples.

First, assume you have a Tolerance Table on a separate sheet, aptly named
"Tolerance Table" (no quotes). The table will look something like:

Tolerance A- A+ B- B+
60 20 20 20 30
80 20 20 30 30
100 30 30 40 40
120 40 40 40 40


The headings aren't particularly important ... they're not actually used,
they just need to mean something to you.

Then, on your "input" sheet, assume that the result is in column A, the
specification is in column B, and the results for Situation A and Situation
B are in columns C and D respectively.

Result Specification Situation A Situation B
140 100 fail pass
70 100 pass pass
110 80 fail pass


If row 1 is the headings as above, in cell C2 put the following formula:

=IF(OR(A4<B4-VLOOKUP(B4,'Tolerance
Table'!$A:$E,2,FALSE),A4>B4+VLOOKUP(B4,'Tolerance Table'!$A:$E,3,FALSE)),
"fail","pass")

In cell D2 put the following formula:

=IF(OR(A2<B2-VLOOKUP(B2,'Tolerance
Table'!$A:$E,4,FALSE),A2>B2+VLOOKUP(B2,'Tolerance Table'!$A:$E,5,FALSE)),
"fail","pass")

Drag the formulae down as far as you need to. May not be perfect but you
can add tolerances to the table so you may be able to refine it if it
doesn't do everything you need.

Be aware that it doesn't test for "invalid" specifications so, for example,
if you put 95 in the Specification (on input) you'd get #N/A in the
Situation columns. You can check for this if you need to ... but you'd need
to decide on the rules if/when this happens.

Some test values ...

Result Specification Situation A Situation B
140 100 fail pass
70 100 pass pass
110 80 fail pass
35 60 fail fail
40 60 pass pass
45 60 pass pass
50 60 pass pass
55 60 pass pass
60 60 pass pass
65 60 pass pass
70 60 pass pass
75 60 pass pass
80 60 pass pass
85 60 fail pass
90 60 fail pass
95 60 fail fail
45 80 fail fail
50 80 fail pass
55 80 fail pass
60 80 pass pass
65 80 pass pass
70 80 pass pass
75 80 pass pass
80 80 pass pass
85 80 pass pass
90 80 pass pass
95 80 pass pass
100 80 pass pass
105 80 fail pass
110 80 fail pass
115 80 fail fail


What I'm still not absolutely sure of is how you want it to work at extremes
.... that is way below 60 and way above 120. But it's a start.

I hope this helps. Post back if you need any more help. Sorry it's taken a
while to follow up but I've been away for a couple of days.

Regards

Trevor

 

[Jeff Smith]

Trevor,

Thank you for this response. Your input is very much appreciated. I shall
work through this.

sincerely

Jeff Smith