Results 1 to 5 of 5
Thread: Multiple Options

20091103, 08:40 #1
 Join Date
 Oct 2009
 Location
 Essex UK
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 multiplechoices 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

20091103, 08:58 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20091103, 09:06 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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]

20091103, 09:24 #4
 Join Date
 Oct 2009
 Location
 Essex UK
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 reworked
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%)

20091103, 09:39 #5
 Join Date
 Oct 2009
 Location
 Essex UK
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
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