Maximizing the value of combinations within a data set

1. 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.

2. 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, "")

3. 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. 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

5. 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)

6. 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.

7. 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.

8. 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. 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. 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. 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.

12. Re: Maximizing the value of combinations within a data set

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. 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.

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