Results 1 to 5 of 5
  1. #1
    New Lounger
    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 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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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]

    Attached Files Attached Files

  4. #4
    New Lounger
    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 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. #5
    New Lounger
    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

Posting Permissions

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