SerenityNetworks
Board Regular
- Joined
- Aug 13, 2009
- Messages
- 131
- Office Version
- 365
- Platform
- Windows
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:
<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.
<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
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>