1. I have a workbook where the user selects an Option and then allocates a percentage to it.
The results are stored in tree like grid (as you might see in probability).

For example :- Choices Allocated %'s
Option 1 - A or B 60% / 40%
Option 2 - 1,2 or 3 50% / 30% / 20%

This would result in the following grid :-

A1 30% (60% x 50%)
A2 18% etc
A3 12%
B1 20%
B2 12%
B3 8%

My problem is that there are numerous options, each with multiple-choices and they are constantly in a state of flux.
This means that constant revisions are needed to the size of the probability grid, and to the formulae in it.

I wondered if anyone may have faced a similar problem or if anyone had any ideas on how best to approach this problem?

Thanks

Rob

2. The formulas could cover the maximum range of possibilities and be based on the number of each. Dynamic ranges of the subset could be used. If the number of possibilities become too large and you don't want the formula overhead, you would have to create a macro.

I think we would need more information on what you have and what you want/need to provide more specific details of either

Steve

3. Alternatively, you could let the user select the options from dropdown lists (using Data | Validation with dynamic lists), and use a single formula to look up the percentages and multiply them.

See the attached workbook: [attachment=86328:Sample.xls]

4. Steve

At present, the grid is fixed and all the possible options are mapped out.
So, for the above, I would plot the 6 possible outcomes on separate rows and then build formula on each row.
E.g. For Option A1, the formula would be the percentage selection for Option A multiplied by the percentage selection for Option 1.

If the no. of Options change, and the number of choices within each option change, the grid has to be re-worked
I could try to cover all the possibilities, but technically this is unlimited. Certainly, it would make the spreadsheet large and cumbersome.

The ideal solution would be to generate a two column array which shows :-
Column 1 - the options chosen (e.g. A1)
column 2 - the percentage result (e.g. 30%)

5. Thanks Hans, your idea gives me a good starting point
If I drag your formula and then selected the various permutations I have the complete list without the hassle of the grid.
This is a big step forward. Ideally, I would now like to generate the data below, without having to go through making all the selections, but this is a big help.

Option 1 A A A B B B
Option 2 1 2 3 1 2 3
Outcome 30% 18% 12% 20% 12% 8%

Thanks guys

Rob

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•