Tolerances Problem

[Jeff Smith]

Help.

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

All I need to do is 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".

Thanking any of you wonderful experts in grateful anticipation.

sincerely

Jeff Smith

 

[Guest]

Jeff,
Could you give some examples of the data entered that you
want to match against the table, and the result expected.
Not sure what you are getting at with Situation A and
Situation B.
80 +/- 20 overlaps with 100 +/- 30 ???

 

[Jeff Smith]

Thanks for the question. I should have explained more at the time.
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 regualted at 60, 80, 100 and 120 but it is
permissible to sell by values below 60 and above 120 but the tolerances
remain at the lower and upper levels regardless.

Hope this clarifies. 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 (I can figure this logic :- )

Thanks again for your interest.

sincerely

Jeff Smith

 

[Dianne]

If you only have a few "situations" to consider, you could set up your
spreadsheet like this:

A1:E5
Measurement Alower Aupper Bupper Blower
60 40 80 40 90
80 60 100 50 110
100 70 130 60 140
120 80 160 80 160

Then, to evaluate your data A810:
Specified Measurement SituationA SituationB
60 81 Fail Pass
120 120 Pass Pass

The formula in C9 is:
=IF(AND(B9>=VLOOKUP(A9,MyLookup,2,FALSE),B9<=VLOOKUP(A9,MyLookup,3,FALSE
)),"Pass","Fail")

The formula in D9 is:
=IF(AND(B9>=VLOOKUP(A9,MyLookup,4,FALSE),B9<=VLOOKUP(A9,MyLookup,5,FALSE
)),"Pass","Fail")

Just one possible approach.