Solution to return a Fail Point value (given required accuracy and sample size)

[SerenityNetworks]

I would like to have a single cell solution that combines functions to return a failure point value when provided a required accuracy and sample size. For example, looking at the bottom table, we have a required accuracy of 99.50% (a variable) and a sample size of 1002 (another variable). With 5 errors we have an accuracy of 99.501%, which is (1002-5)/1002. This is still above 99.50%. But with 6 errors, (1002-6)/1002, we have an accuracy of 99.401%. This is the first whole number where the accuracy falls below the required 99.50%. I need a formula that returns this "failure point" value. I would very much like it to be a single cell formula that doesn't require supporting tables. You can see in the bottom table, I can return the number I need using the VLOOKUP function, but for my uses this would be very cumbersome; thus the desire for a single cell solution. If this is possible, I don't know how to construct it.

Any help will be greatly appreciated.

Thank you,
Andrew

---

What I would like:


[TABLE="class: grid, width: 254"]
<colgroup><col style="width:48pt" width="64" span="2"> <col style="width:95pt" width="126"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 126, align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Required
[/TD]
[TD="align: right"]99.50%
[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Samples
[/TD]
[TD="align: right"]1002[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Fail Point
[/TD]
[TD]A formula that returns 6
[/TD]
[/TR]
</tbody>[/TABLE]

What I can do, but don't like:
What I need can be accomplished using the VLOOKUP function. But this requires a table be constructed with at least the maximum number or errors allowed and then ordered by errors descending. As I need to simultaneously make this calculation for 20 or more different sample sizes, constructing all these tables that are needed for a VLOOKUP to be conducted in an automated fashion would be a bit problematic.

[TABLE="class: grid, width: 564"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]13
[/TD]
[TD="align: center"]S
[/TD]
[TD="align: center"]T
[/TD]
[TD="align: center"]U
[/TD]
[TD="align: center"]V
[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Required[/TD]
[TD="align: right"]99.50%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Fail Point[/TD]
[TD="align: right"] 6 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Samples
[/TD]
[TD]Accuracy
[/TD]
[TD]Errors
[/TD]
[TD]=VLOOKUP(T15,$T$18:$U$28,2,TRUE) [Returns 6]
[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.002%[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.102%[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.202%
[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.301%
[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.401%[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.501%[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.601%[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.701%[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.800%[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]99.900%[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]100.000%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe:

B4: =ROUNDUP(B3*(1-B2)+0.0001,0)

 

[SerenityNetworks]

That is perfect! Although it's simple, it took me a few minutes to wrap my head around why/how it works. Thank you so very much.

Andrew

 

[Eric W]

I just solved your equation algebraically for the Fail Point value, then rounded up (adding .0001 to make sure that exact matches round up too).

Happy to help!