Results 1 to 3 of 3
Thread: Calculate Percentage Series

20090604, 09:32 #1
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
I’m trying to find a short hand way of expressing a series of (up to 5) percentages as a single compound percentage. I’m working on something that is required to estimate the overall number of tests required given a certain profile of test iterations with each having a percentage “success/pass” rate.
I have this working currently via a long hand formula that produces the required answer. However, I now want to “reverse” the formula such that if I know the outcome for a particular test iteration scenario I can work backwards to determine the number of first iteration tests. EG:
Estimated Test Success Rate Per Iteration
Planned Tests First Second Third Fourth Fifth Total Estimated Tests
185 60% 80% 100% 273
185 70% 100% 240
185 70% 100% 240
185 70% 100% 240
The first cell in Total Estimated Tests, containing 273, is at address AJ6 and it contains the following formula:
=INT((AD6*(1MIN(1,AE6)))+(AD6*(1MIN(1,AE6))*(1MIN(1,AF6)))+(AD6*(1MIN(1,AE6))*(1MIN(1,AF6))*(1MIN(1,AG6)))+(AD6*(1MIN(1,AE6))*(1MIN(1,AF6))*(1MIN(1,AG6))*(1MIN(1,AH6)))+(AD6*(1MIN(1,AE6))*(1MIN(1,AF6))*(1MIN(1,AG6))*(1MIN(1,AH6))*(1MIN(1,AI6)))+AD6)
i.e. It is calculating the failed tests for each of the up to five possible iterations. The key here is it is NOT using the Planned Test number in anything but the First iteration. For each successive iteration it is calculating the number of tests that will fail based upon the number of tests that failed in the previous iteration.
Is there a means of producing a single overall percentage, based upon the up to five percentages entered, which would result in the same outcome?
The number of tests that can be performed are constrained by time and resources. I know how long tests take and how much testing time I have, therefore I can calculate how many tests can be executed in a given time window. What I want to do then is to be able to calculate from this what the maximum number of tests that can be planned for (the number on the far left) which will always be a smaller number than the maximum tests I can run in a given time window. i.e. Your planning needs to take account of the number of retests your test iteration scenario to ensure that your initial planned tests plus any retests doesn’t exceed the time available.
Thoughts please.
Cheers
Peter

20090604, 10:08 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Your description switches from "I" to "you". Is this some kind of homework assignment?
The overall multiplication factor is
=1+(1AE6)+(1AE6)*(1AF6)+(1AE6)*(1AF6)*(1AG6)+(1AE6)*(1AF6)*(1AG6)*(1AH6)+(1AE6)*(1AF6)*(1AG6)*(1AH6)*(1AI6)
So if you know the end result, you can divide it by
(1+(1AE6)+(1AE6)*(1AF6)+(1AE6)*(1AF6)*(1AG6)+(1AE6)*(1AF6)*(1AG6)*(1AH6)+(1AE6)*(1AF6)*(1AG6)*(1AH6)*(1AI6))
to retrieve the initial number.
(I've assumed that AE6:AI6 will never be higher than 100%, otherwise you can put back in the MIN(1,...) expressions)

20090604, 10:23 #3
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='778356' date='04Jun2009 15:08']Your description switches from "I" to "you". Is this some kind of homework assignment?[/quote]
Hi Hans
Nope. just my bad grammar! Thanks for the feedback, most helpful and works as required.
Cheers
Peter