+ Reply to Thread
Results 1 to 13 of 13

Maximizing the value of combinations within a data set

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office Pro 2010
    Posts
    6

    Question Maximizing the value of combinations within a data set

    I am trying to calculate a season score for each sailboat for a series of races. Basically, each boat receives a high point score based on their position and the number of competitors in their class. If there are 10 boats, the first place boat receives 10 points, second 9, and so on.

    The calculation of season score is the sum of points scored in each race divided by the sum of possible points had the boat won each race. Summing the numerators and denominators separately weighs races with more competitors more heavily). The challenge is that each boat's season score is calculated from the 7 races that produce the maximum result. It is choosing those races that has proved challenging. (Note that each boat sails a different number of races of their choice).

    In the following example, the season consisted of 14 races. Sea Fiddler Sailed in 10 of those races. The straight forward solution (select the 7 highest individual race % scores) would select races 1, 2, 4, 5, 7, 11 and 14. This produces a score of .5333. In fact, races 1, 2, 3, 5, 7, 11 and 14 produce a better score (.5385) because the reduced number of competitors in race 3 more than offsets the better performance in race 4.

    Season Scoring Example.jpg

    Race 1 2 3 4 5 6 7 8 9 10 11 12 13 14
    Finish 2 3 3 5 4 DNC 2 DNC DNC 6 2 4 DNC 3
    Points 2 1 1 3 4 1 1 2 1 3
    Competitors 3 3 3 7 7 3 2 2 5 6 3 4 2 5
    * DNC = Did Not Compete and is not scored.

    I would greatly appreciate any insight from those with more experience than me.
    Last edited by LarryJFox; 09-12-2017 at 06:27 PM. Reason: Add better image of sample data

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    Choose the 7 races having the highest ratios of points to competitors:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    1
    Race
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    2
    Competitors
    3
    3
    3
    7
    7
    3
    2
    2
    5
    6
    3
    4
    2
    5
    3
    Finish
    2
    3
    3
    5
    4
    DNC
    2
    DNC
    DNC
    6
    2
    4
    DNC
    3
    4
    Points
    2
    1
    1
    3
    4
    1
    1
    2
    1
    3
    B4: =IF(ISNUMBER(B3), B2 + 1 - B3, "")
    5
    Pts/Comp
    0.67
    0.33
    0.33
    0.43
    0.57
    0.50
    0.17
    0.67
    0.25
    0.60
    B5: =IF(ISNUMBER(B4), B4/B2, "")
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office Pro 2010
    Posts
    6

    Re: Maximizing the value of combinations within a data set

    Thank you SHG, but the point is this does not always work. In this example, that solution would include score ranks 1 through 7. (note that you have inadvertently selected 8 races). The correct solution in this case (worked out manually) is to drop race 4 (ranked 6) and add race 3 (ranked 8). I am wondering if the solver could be employed to evaluate (in the instant case) 10C7 combinations and select the maximum value.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    Plan B:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    1
    Race
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Points
    Best
    Ratio
    2
    Competitors
    3
    3
    3
    7
    7
    3
    2
    2
    5
    6
    3
    4
    2
    5
    3
    4
    Sea Fiddler
    5
    Finish
    2
    3
    3
    5
    4
    DNC
    2
    DNC DNC
    6
    2
    4
    DNC
    3
    6
    Points
    2
    1
    1
    3
    4
    0
    1
    0
    0
    1
    2
    1
    0
    3
    14
    P6: =SUMIF(B8:O8, "<=7", B6:P6)
    7
    Pts/Comp
    1.00
    0.50
    0.50
    0.50
    0.67
    0.00
    1.00
    0.00
    0.00
    0.20
    1.00
    0.33
    0.00
    0.75
    26
    P7: =SUMIF(B8:O8, "<=7", $B$2:$O$2)
    8
    Rank
    1
    6
    7
    8
    5
    11
    2
    12
    13
    10
    3
    9
    14
    4
    0.5385
    P8: =P6/P7
    9
    10
    She Crab
    11
    Finish
    3
    DNC
    2
    2
    3
    DNC
    3
    DNC
    3
    5
    3
    DNC
    2
    4
    12
    Points
    1
    0
    2
    6
    5
    0
    0
    0
    3
    2
    1
    0
    1
    2
    19
    13
    Pts/Comp
    0.50
    0.00
    1.00
    1.00
    0.83
    0.00
    0.00
    0.00
    0.75
    0.40
    0.50
    0.00
    1.00
    0.50
    30
    14
    Rank
    6
    10
    1
    2
    4
    11
    12
    13
    5
    9
    7
    14
    3
    8
    0.6333
    Last edited by shg; 09-12-2017 at 09:23 PM.

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office Pro 2010
    Posts
    6

    Re: Maximizing the value of combinations within a data set

    SHG: Took me a little bit to fully understand what you had done here. I recreated it as follows (sitting below the balance of the workbook).

    A couple of things. First, there may have been a missing argument in the season score calculation. Once updated, the resulting 0.5333 produced the same error that I started with. Your logic is the same as mine was: the best 7 percentage ranks should produce the best aggregate rank. In this case, it does not (The error occurs in about 8% of my samples). By including 0.333 in Race 3 (rank 8) instead of .429 in race 4 (rank 6) the result goes up to .5385 rather than down as one might expect. The reason is that aggregating the numerators and denominators separately performs the relative "weighting" of races based on the number of competitors. This is why I am thinking about the need to score each combination and take the maximum result. Perplexing, no?


    SSN Scoring Example 2.jpg

    D127: =IF(N(D126), D125 + 1 - D126, 0)
    D128: =D127/D125
    D129: =RANK(D128, $D128:$Q128) + COUNTIF($C128:C128, D128)
    D130: =SUMIF(D129:Q129, "<=7", D127:Q127) / SUMIF(D129:Q129, "<=7", D125:Q125)
    Last edited by LarryJFox; 09-12-2017 at 10:01 PM. Reason: Attachment truncated

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    It's easy enough to ratchet through the combinations, though I wouldn't use Solver -- it has no particular aptitude for evaluating combinations. I'll look at it later today if I have time.

    In the meanwhile, can you post a larger example -- multiple boats.
    Last edited by shg; 09-13-2017 at 09:17 AM.

  7. #7
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office Pro 2010
    Posts
    6

    Re: Maximizing the value of combinations within a data set

    Thanks SGH; I really appreciate your time and interest.

    I am attaching a ss file that contains class 6.
    I have broken links to supporting tabs and saved those as values (e.g. the finishes are loaded from another tab).
    It has 7 boats (6 qualified for the season with minimum 6 or 7 races).
    The top 144 lines of the SS were my initial cut at the scoring program selecting and summing up to the 7 best races on an individual % rank basis.
    (boats that qualify with only 6 races by another rule use all races so that does not create a concern).
    Lines 146 through 188 represent my manual effort to maximize season scores for 5 boats where another scorer disagreed with my results.
    The first 3 were resolved by correcting data errors.
    The last is an example of the anomaly that is plaguing me.
    Attached Files Attached Files

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    Larry, my day job has intervened, and I probably won't get to this today, but will (try to) later in the week. In the meanwhile, if Alf stops by, he may give you a solid Solver solution. Not as good as mine will be, but hey ...

    The chances of me doing this rise to near certainty if you do a little something for the Red Cross for our friends in Houston and Florida.

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Maximizing the value of combinations within a data set

    Hi shg

    Thanks for you vote of confidence but looking at the problem I think it's above my level of expertise so I better leave it in your capable hands

    Regards

    alf

  10. #10
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office Pro 2010
    Posts
    6

    Re: Maximizing the value of combinations within a data set

    On my way from NY to Florida now to see what I can do to help.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    Not fast, but entertaining to watch. The boat with 13 races takes about 40 seconds.

    Whaddaya think, Alf?
    Attached Files Attached Files
    Last edited by shg; 09-15-2017 at 06:48 PM.

  12. #12
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Maximizing the value of combinations within a data set

    Whaddaya think, Alf?
    I've said it before and I say it again. When it comes to problem solving you are second to none!!!

    Hopefully the OP will thank you for your effort, I thing I see less and less of these days

    As this have taken away a part of the satisfaction I use to feel when solving a problem and posting a solution I may still try to solve an interesting problem but having found a solution I do not always post it.

    Alf

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Maximizing the value of combinations within a data set

    Why Alf, you are too kind

    This is not as entertaining, but a few thousand times faster.
    Attached Files Attached Files
    Last edited by shg; 09-21-2017 at 12:03 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help maximizing my profit
    By Dittoz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2012, 12:31 AM
  2. Maximizing vba CPU usage
    By abousetta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2012, 12:02 AM
  3. Maximizing session of IE
    By Chris424 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-24-2009, 04:12 PM
  4. [SOLVED] maximizing file upon opening
    By krazygmtrk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-24-2008, 03:30 AM
  5. Maximizing (Complicated Problem)
    By bravesox2001 in forum Excel General
    Replies: 0
    Last Post: 03-03-2008, 04:26 PM
  6. Maximizing dues income
    By Tanizaki in forum Excel General
    Replies: 0
    Last Post: 01-14-2007, 05:47 PM
  7. [SOLVED] problems maximizing windows in VBA
    By filo666 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2005, 08:36 PM
  8. Maximizing a workbook
    By Cabaco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1