Hi there!
My Situation:
My file has 16 worksheets.
Each worksheet contains about 1500 more or less normally distributed values.
Every worksheet has the identical quantity of entries, but the value ranges differ greatly. In the example provided (excel file) the number can range between 0% and 6%, but on another worksheet the range can be -50000% to +50000%
On each worksheet there are 15 sections each with a cell holding a “low” value and a “high” value (like on the excel file provided).
Aim:
What the macro must do is to change the values of cell H2 (“low”) and H3(“high”) in an iteration, the aim is to satisfy the following conditions:
1) find a concentration of occurrences so that either scenario 1 or scenario 2 lies at 67% or above.
AND
2) The number of observations is to be at least 2% or higher.
In the example provided the green cell G6 shows a value of 68% for scenario 1 (and 32% for the other scenario), so condition 1 is satisfied.
Condition 2: the number of observations is 53 (see cell F11) or 3,48% of the total (1548), therefore this condition is also met and we have a first solution.
Now the macro should jump to the next section and repeat the iteration with the numbers in O3 and O4. To work systematically through the entire number range O3 should be the minimum of the next range. If no solutions can be found with the number in O3 as a minimum then the value O3 must be increased in small increments and so on (we are thus moving in “inch-worm fashion” up the number range and scanning for concentrations.
To graze through all the numbers it is best to start from the absolute min. (0% in this example) and work upwards (up until the absolute max.). It is possible that less than 15 solutions or even no solutions are found.
In that case, the macro can simply move on to the next worksheet and proceed in the same manner until all worksheets are worked through. Hope I didn’t confuse you totally. Please come back with questions if I did!
Best regards,
Excel_Arate
Bookmarks