Question using CRITBINOM

[lashio]

I have a Quality Control question using CRITBINOM().
My understanding is shown in cell(A7), but it does not seem right.
Will someone help me, please?

Sub Question_on_CRITBINOM()
Range("A1") = "Data"
Range("B1") = "Description"
Range("A2") = 25
Range("A3") = 0.5
Range("A4") = 0.01
Range("B2") = "Number of Bernoulli trials"
Range("B3") = "Probability of a success on each trial"
Range("B4") = "Criterion value"
Range("A6") = "=CRITBINOM(A2,A3,A4)"
Range("A7") = "=""For a "" & (1-A4)*100 & ""% success rate, a maximum of
rejects allowed is "" &A6 & "" out of "" & A2 & "" trials. If it is over
that, reject the whole lot."""
End Sub

 

[Conrad Carlberg]

You're getting the correct answer from CRITBINOM. Of course, your criterion
of 1% is very stringent. In effect, to use these arguments to CRITBINOM is
to ask:

Of 1000 lots, 10 of them (1%) have the 10 smallest number of defectives. I'm
testing 25 units in each of my 1000 lots, and 50% of the units are defective
in each lot. What is the largest number of defective units in the 10 lots
that have the smallest number of defective units?

With a 50% probability of a hit for each unit, it's very unusual (cum 1%) to
get only 1, 2, 3, 4, 5, 6 or 7 hits out of 25 trials.

To convince yourself that CRITBINOM returned the proper number, enter the
numbers 1 through 25 in A1:A25. In B1, enter this formula:

=BINOMDIST(A1,25,0.5,TRUE)

and copy-and paste that down into B2:B25. You'll note that for 6 successes,
BINOMDIST returns .007, and for 7 successes, BINOMDIST returns .02. This
conforms to the CRITBINOM definition, "Returns the smallest value for which
the cumulative binomial distribution is greater than or equal to a criterion
value." 7 is the number of successes associated with your criterion value,
..01, which is the smallest number greater than .007 in the binomial
distribution as you've defined it with your arguments.

C^2
Conrad Carlberg

 

[lashio]

Hi, Conrad
Thank you very much for your detail explanation. I understand it better now.