Minimum sample size for 'n' allowed errors, given required accuracy and confidence (statistics)

[SerenityNetworks]

I use the workbook referenced below to provide me with the minimum sample sizes needed for a range of allowed errors, given a required accuracy and required confidence.

In a round-about way, I fill-down the BINOMDIST function in a column to provide me with the probability/confidence that my accuracy requirement will be met for a given sample size and given number of errors. This allows me to then look up the sample size where the probability/confidence I'm looking has been met (by the BINOMDIST formula).

The solution works perfectly. The results have been validated many times. However, it produces a huge (100+ mb) Excel file that is slow to calculate. In the example file provided I'm only giving 5 columns of formulas that are filled-down 200 rows. To accommodate the ranges I need to work with, I must fill-down 50k or more rows and across 75 or more columns. That makes for huge workbooks that are slow to calculate.

I'm wondering if anyone can show me a more elegant method to accomplish my goal. I've tried, but haven't been able to get away from using a massive number of columns and rows. It seems there should be a single formula that will do what I need, but I'm not mathematically inclined enough to be able to figure it out. Any help will be appreciated.

Thanks in advance,
Andrew

Dropbox link to workbook directory

or link to workbook directly if above doesn't work.

 

[shg]

How about these apples?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Accuracy​

[/td][td]

99.90%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Confidence​

[/td][td]

80.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#F3F3F3"]

Allowed Errors​

[/td][td="bgcolor:#F3F3F3"]

NumTrials4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

1,609​

[/td][td]B5: =NumTrials4($A5, $B$2, 1-$B$1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

2,994​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

4,278​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

7,905​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]

13,649​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

20​

[/td][td="bgcolor:#E5E5E5"]

24,726​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

50​

[/td][td="bgcolor:#E5E5E5"]

56,890​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

100​

[/td][td="bgcolor:#E5E5E5"]

109,342​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

200​

[/td][td="bgcolor:#E5E5E5"]

212,819​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

500​

[/td][td="bgcolor:#E5E5E5"]

519,725​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

1,000​

[/td][td="bgcolor:#E5E5E5"]

1,027,513​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

2,000​

[/td][td="bgcolor:#E5E5E5"]

2,038,529​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

5,000​

[/td][td="bgcolor:#E5E5E5"]

5,060,389​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

10,000​

[/td][td="bgcolor:#E5E5E5"]

10,085,026​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

20,000​

[/td][td="bgcolor:#E5E5E5"]

20,119,868​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

50,000​

[/td][td="bgcolor:#E5E5E5"]

50,189,002​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

100,000​

[/td][td="bgcolor:#E5E5E5"]

100,266,915​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

200,000​

[/td][td="bgcolor:#E5E5E5"]

200,377,100​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

500,000​

[/td][td="bgcolor:#E5E5E5"]

500,595,722​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

1,000,000​

[/td][td="bgcolor:#E5E5E5"]

1,000,842,103​

[/td][td][/td][/tr]
[/table]

That last result (the 1,000,842,103) requires only 36 calculations; it calculates instantly.

Function NumTrials4(k As Long, Conf As Double, p As Double) As Long
  ' shg 2017
  ' Rock solid for values < ~ 1 billion
  Dim cdf           As Double   ' cumulative distribution function
  Dim n             As Long     ' trials
  Dim nLo           As Long
  Dim nHi           As Long
  Dim i             As Long

  If k < 0 Then Exit Function
  If Conf <= 0# Or Conf >= 1# Then Exit Function
  If p <= 0# Then Exit Function
  
  nLo = k
  n = k

  With WorksheetFunction
    Do
      cdf = .Binom_Dist(k, n, p, True)
      i = i + 1
      'Debug.Print Right(Space(10) & Format(i, "  #,##0"), 16) & _
                  Right(Space(16) & Format(nLo, "  #,##0"), 16) & _
                  Right(Space(16) & Format(n, "  #,##0"), 16) & _
                  Right(Space(16) & Format(nHi, "  #,##0"), 16) & _
                  Format(cdf, "  0.0000000000")

      Select Case Sgn(1 - cdf - Conf)
        Case -1
          nLo = n
          If n = 0 Then n = 1 Else n = 2 * n
        Case 0
          NumTrials4 = n
          Exit Function
        Case 1
          nHi = n
          Exit Do
      End Select
    Loop

    Do
      If nHi = nLo + 1 Then
        NumTrials4 = nHi
        Exit Function
      End If

      n = (nLo + nHi) \ 2
      i = i + 1
      cdf = .Binom_Dist(k, n, p, True)
      'Debug.Print Right(Space(10) & Format(i, "  #,##0"), 16) & _
                  Right(Space(16) & Format(nLo, "  #,##0"), 16) & _
                  Right(Space(16) & Format(n, "  #,##0"), 16) & _
                  Right(Space(16) & Format(nHi, "  #,##0"), 16) & _
                  Format(cdf, "  0.0000000000")

      Select Case Sgn(1 - cdf - Conf)
        Case -1
          nLo = n
        Case 0
          NumTrials4 = n
          Exit Function
        Case 1
          nHi = n
      End Select
    Loop
  End With

End Function

 

[SerenityNetworks]

Well, I just saw your post above. I was playing with NumTrials2. I'll take a look at NumTrials4 above momentarily. Anyway, here is what I was going to comment on NumTrials2.

Yes, it is faster. I'm trying, but the math and UDFs are getting beyond my ability to follow. What is the PMF and how do I determine the boundary of when the PMF falls below the level at which it can be subtracted from the CDF? I can see that it's around one-hundred for the required values I typically use, but I not understanding the terms leaves me guessing.

I attempted to replace your 'Stop' with a call to your original NumTrials, in order to continue the calculation once PMF equaled zero. I wasn't successful (not sure why yet). But then I noticed that the calculation actually dies/differs from the original NumTrials before PMF equals zero. Of course, I have no idea why.

Thanks again,
Andrew

​

I'm going to take a look at NumTrials4 shortly. But I may not be able to reply until morning.

 

[SerenityNetworks]

Dang! That's amazing!

I'll have to look more closely, but I think I even can follow what you are doing with the UDF. Thank you! This far exceeds my needs. It even lets me calculate ridiculous (but sometimes encountered requirements of 99.99% accuracy with 90% confidence. Wow. (It dies at 87,382 allowed errors at 99.99% accuracy and 90% confidence, but I can live with that )

Any thoughts on the best ways to pursue my other ultimate goals to (2) calculate the confidence level given the count of errors, required accuracy, and sample size, and (3) calculate the number of errors allowed given the sample size, required accuracy, and required confidence? I don't mean to presume or suppose, but this has been such an incredibly helpful thread that I have to ask.

Anyway, again, thank you very much,
Andrew

 

[shg]

A little cleanup:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Accuracy​

[/td][td]

99.99%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Confidence​

[/td][td]

80.00%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#F3F3F3"]

Allowed Errors​

[/td][td="bgcolor:#F3F3F3"]

NumTrials4​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

16,094​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

29,943​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

42,790​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

79,059​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]

136,506​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

20​

[/td][td="bgcolor:#E5E5E5"]

247,278​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

50​

[/td][td="bgcolor:#E5E5E5"]

568,925​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

100​

[/td][td="bgcolor:#E5E5E5"]

1,093,460​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

200​

[/td][td="bgcolor:#E5E5E5"]

2,128,239​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

500​

[/td][td="bgcolor:#E5E5E5"]

5,197,333​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

1,000​

[/td][td="bgcolor:#E5E5E5"]

10,275,245​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

2,000​

[/td][td="bgcolor:#E5E5E5"]

20,385,455​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

5,000​

[/td][td="bgcolor:#E5E5E5"]

50,604,153​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

10,000​

[/td][td="bgcolor:#E5E5E5"]

100,850,634​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

20,000​

[/td][td="bgcolor:#E5E5E5"]

201,199,220​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

50,000​

[/td][td="bgcolor:#E5E5E5"]

501,890,868​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

100,000​

[/td][td="bgcolor:#E5E5E5"]

1,002,670,343​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

131,071​

[/td][td="bgcolor:#E5E5E5"]

1,313,765,866​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

131,072​

[/td][td="bgcolor:#E5E5E5"]

#VALUE!​

[/td][/tr]
[/table]

Function NumTrials4(k As Long, Conf As Double, p As Double) As Long
  ' shg 2017
  Dim cdf           As Double   ' cumulative distribution function
  Dim n             As Long     ' number of trials
  Dim nLo           As Long     ' low limit for n
  Dim nHi           As Long     ' high limit for n
  Dim nCalc         As Long     ' calculation counter

  If k < 0& Then Exit Function
  If Conf <= 0# Or Conf >= 1# Then Exit Function
  If p <= 0# Or p >= 1# Then Exit Function

  nLo = k
  If k = 0& Then nHi = 1 Else nHi = k

  With WorksheetFunction
    ' find the high limit
    Do
      nHi = nHi * 2&
      cdf = .Binom_Dist(k, nHi, p, True)
      nCalc = nCalc + 1&
      'Debug.Print Right(Format(nCalc, "   0"), 3) & _
                  Right(Format(nLo, "               #,##0"), 15) & _
                  Right(Format(n, "               #,##0"), 15) & _
                  Right(Format(nHi, "               #,##0"), 15) & _
                  Format(cdf, "  0.000 000 000 000 000")
    Loop While 1 - cdf < Conf

    ' binary search for n
    Do
      If nHi = nLo + 1& Then
        NumTrials4 = nHi
        Exit Function
      End If

      n = nLo \ 2& + nHi \ 2& + (nLo And nHi And 1&)
      nCalc = nCalc + 1&
      cdf = .Binom_Dist(k, n, p, True)
      'Debug.Print Right(Format(nCalc, "   0"), 3) & _
                  Right(Format(nLo, "               #,##0"), 15) & _
                  Right(Format(n, "               #,##0"), 15) & _
                  Right(Format(nHi, "               #,##0"), 15) & _
                  Format(cdf, "  0.000 000 000 000 000")

      Select Case Sgn(1# - cdf - Conf)
        Case -1
          nLo = n
        Case 0
          NumTrials4 = n
          Exit Function
        Case 1
          nHi = n
      End Select
    Loop
  End With
End Function

 

[shg]

(2) calculate the confidence level given the count of errors, required accuracy, and sample size, and (3) calculate the number of errors allowed given the sample size, required accuracy, and required confidence?

How do you do those now?

 

[SerenityNetworks]

(2) calculate the confidence level given the count of errors, required accuracy, and sample size, and (3) calculate the number of errors allowed given the sample size, required accuracy, and required confidence?

How do you do those now?

I do it the hard way. I enter the required accuracy and then keep changing the confidence until I hit the number of trials and sample sizes I encountered. It's not terribly hard or time consuming (and it will be even less so with your speedy UDF), but it would be nice to have a single formula that did it.

Thanks,
Andrew

 

[SerenityNetworks]

Oh gee, do I feel stupid! I was so into obtaining the sample sizes that I totally forgot that I was using the derived confidence level to achieve it.

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Accuracy[/TD]
[TD]99.90%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Allowed Errors[/TD]
[TD]Trials[/TD]
[TD]Confidence Level[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]1204[/TD]
[TD]70.02%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]2439[/TD]
[TD]70.01%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]615[/TD]
[TD]70.01%[/TD]
[/TR]
</tbody>[/TABLE]

C4: =1-BINOM.DIST(A4,B4,1-$B$1,TRUE)

Or am I having yet another brain cramp?

Thanks,
Andrew

 

[SerenityNetworks]

Oh, and for accuracy...

[TABLE="class: cms_table_grid, width: 50, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Accuracy[/TD]
[TD]99.90%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Allowed Errors[/TD]
[TD]Trials[/TD]
[TD]Accuracy
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]1204[/TD]
[TD]100.00%
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]2439
[/TD]
[TD]99.96%
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]615[/TD]
[TD]99.94%
[/TD]
[/TR]
</tbody>[/TABLE]


C4: =1-A4/B4

Doh!

This is what happens when I get too focused on one problem (and I'm overly tired). I miss the obvious.

Thanks for all your help on this topic. It has been above and beyond all expectations.

Andrew

PS. I guess now I just need to work on finding out the number of allowed errors when I know the required accuracy, required confidence level, and number of trials.

 

[shg]

You're welcome.

 

[SerenityNetworks]

BTW, I came up with my own solution (here) to calculating for allowed errors. Thank you for the example in coding for a UDF. I don't know that my solution is very elegant, but it works - and it's not terribly slow despite being a brute force method. Later I'll look at it more closely to see if I can optimize it as you did with your UDF.

Andrew