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:

1	A	B
2	Required	99.50%
3	Samples	1002
4	Fail Point	A formula that returns 6

<colgroup><col style="width:48pt" width="64" span="2"> <col style="width:95pt" width="126"> </colgroup><tbody>
</tbody>

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.

13	S	T	U	V
14
15	Required	99.50%
16	Fail Point	6
17	Samples	Accuracy	Errors	=VLOOKUP(T15,$T$18:$U$28,2,TRUE) [Returns 6]
18	1002	99.002%	10
19	1002	99.102%	9
20	1002	99.202%	8
21	1002	99.301%	7
22	1002	99.401%	6
23	1002	99.501%	5
24	1002	99.601%	4
25	1002	99.701%	3
26	1002	99.800%	2
27	1002	99.900%	1
28	1002	100.000%	0

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[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!