Determine best possible combination based on two variable

mcpwayne2005

New Member
Joined
Jul 3, 2008
Messages
6
Hi,

First I will mention I am using Excel 2007 on windows Vista, and I do not know a whole lot about excel and virtually nothing about VBA, but I am willing to learn.

First some back ground on what this is for...

I am in a fantasy drum corps league (for any of you who know what Drum corps is) and scoring works like this.

There are 8 captions (GE,GM,VP,VE,VG,MB,ME,MP) and together the 8 captions form the total score following this formula. GE+GM+((VP+VE+VG)/2)+((ME+MB+MP)/2)=Total score.

The fantasy part works by selecting 8 individual captions from different corps based on a point system that is attached. Now when selecting these 8 captions I have 120 points to do so. What I would like to do is be able to insert current scores for each corps and see what combination of captions will produce the highest score while using no more than 120 points.

below is the point layout for each corps and caption and a sample chart of scores.

I would like to be able to of course keep adding current scores and it provide the best combination for me. Also if possible to have it pick the best combo's using no more than 132 points as well while producing the highest score for another league I am in.

Thank you so much for any help you can give me.
Adam

This chart is the point value for each corps caption.
<table style="border-collapse: collapse; width: 484pt;" border="0" cellpadding="0" cellspacing="0" width="646"><col style="width: 100pt;" width="134"> <col style="width: 48pt;" span="8" width="64"> <tbody><tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt; width: 100pt;" height="19" width="134">
</td> <td style="width: 48pt;" width="64">GEV</td> <td style="width: 48pt;" width="64">GEM</td> <td style="width: 48pt;" width="64">VE</td> <td style="width: 48pt;" width="64">VP</td> <td style="width: 48pt;" width="64">VG</td> <td style="width: 48pt;" width="64">ME</td> <td style="width: 48pt;" width="64">MB</td> <td style="width: 48pt;" width="64">MP</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Blue Devils</td> <td class="xl64">24</td> <td class="xl64">25</td> <td class="xl64">24</td> <td class="xl64">25</td> <td class="xl64">23</td> <td class="xl64">25</td> <td class="xl64">25</td> <td class="xl64">25</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Blue Knights</td> <td class="xl64">18</td> <td class="xl64">18</td> <td class="xl64">17</td> <td class="xl64">18</td> <td class="xl64">18</td> <td class="xl64">17</td> <td class="xl64">18</td> <td class="xl64">17</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Blue Stars</td> <td class="xl64">11</td> <td class="xl64">12</td> <td class="xl64">13</td> <td class="xl64">13</td> <td class="xl64">11</td> <td class="xl64">13</td> <td class="xl64">13</td> <td class="xl64">13</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Bluecoats</td> <td class="xl64">19</td> <td class="xl64">22</td> <td class="xl64">20</td> <td class="xl64">19</td> <td class="xl64">19</td> <td class="xl64">22</td> <td class="xl64">22</td> <td class="xl64">24</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Boston Crusaders</td> <td class="xl64">17</td> <td class="xl64">17</td> <td class="xl64">18</td> <td class="xl64">17</td> <td class="xl64">17</td> <td class="xl64">18</td> <td class="xl64">17</td> <td class="xl64">18</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Carolina Crown</td> <td class="xl64">20</td> <td class="xl64">21</td> <td class="xl64">22</td> <td class="xl64">20</td> <td class="xl64">24</td> <td class="xl64">19</td> <td class="xl64">19</td> <td class="xl64">21</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Colts</td> <td class="xl64">16</td> <td class="xl64">16</td> <td class="xl64">15</td> <td class="xl64">14</td> <td class="xl64">15</td> <td class="xl64">16</td> <td class="xl64">15</td> <td class="xl64">14</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Crossmen</td> <td class="xl64">10</td> <td class="xl64">10</td> <td class="xl64">10</td> <td class="xl64">10</td> <td class="xl64">13</td> <td class="xl64">11</td> <td class="xl64">9</td> <td class="xl64">11</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Glassmen</td> <td class="xl64">15</td> <td class="xl64">15</td> <td class="xl64">14</td> <td class="xl64">15</td> <td class="xl64">16</td> <td class="xl64">15</td> <td class="xl64">16</td> <td class="xl64">16</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Madison Scouts</td> <td class="xl64">12</td> <td class="xl64">11</td> <td class="xl64">11</td> <td class="xl64">11</td> <td class="xl64">10</td> <td class="xl64">10</td> <td class="xl64">10</td> <td class="xl64">11</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Mandarins</td> <td class="xl64">6</td> <td class="xl64">7</td> <td class="xl64">6</td> <td class="xl64">7</td> <td class="xl64">8</td> <td class="xl64">7</td> <td class="xl64">7</td> <td class="xl64">7</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Pacific Crest</td> <td class="xl64">9</td> <td class="xl64">9</td> <td class="xl64">9</td> <td class="xl64">9</td> <td class="xl64">12</td> <td class="xl64">9</td> <td class="xl64">11</td> <td class="xl64">9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Phantom Regiment</td> <td class="xl64">21</td> <td class="xl64">20</td> <td class="xl64">19</td> <td class="xl64">22</td> <td class="xl64">20</td> <td class="xl64">24</td> <td class="xl64">23</td> <td class="xl64">23</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Pioneer</td> <td class="xl64">7</td> <td class="xl64">6</td> <td class="xl64">7</td> <td class="xl64">6</td> <td class="xl64">6</td> <td class="xl64">6</td> <td class="xl64">6</td> <td class="xl64">6</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">SC Vanguard</td> <td class="xl64">22</td> <td class="xl64">19</td> <td class="xl64">21</td> <td class="xl64">21</td> <td class="xl64">21</td> <td class="xl64">20</td> <td class="xl64">21</td> <td class="xl64">19</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Spirit from JSU</td> <td class="xl64">14</td> <td class="xl64">14</td> <td class="xl64">16</td> <td class="xl64">16</td> <td class="xl64">14</td> <td class="xl64">14</td> <td class="xl64">14</td> <td class="xl64">15</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">The Academy</td> <td class="xl64">13</td> <td class="xl64">13</td> <td class="xl64">12</td> <td class="xl64">12</td> <td class="xl64">9</td> <td class="xl64">12</td> <td class="xl64">12</td> <td class="xl64">13</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">The Cadets</td> <td class="xl64">25</td> <td class="xl64">24</td> <td class="xl64">23</td> <td class="xl64">24</td> <td class="xl64">22</td> <td class="xl64">23</td> <td class="xl64">24</td> <td class="xl64">22</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">The Cavaliers</td> <td class="xl64">23</td> <td class="xl64">23</td> <td class="xl64">25</td> <td class="xl64">23</td> <td class="xl64">25</td> <td class="xl64">21</td> <td class="xl64">20</td> <td class="xl64">20</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" height="19">Troopers</td> <td class="xl64">8</td> <td class="xl64">8</td> <td class="xl64">8</td> <td class="xl64">8</td> <td class="xl64">7</td> <td class="xl64">8</td> <td class="xl64">8</td> <td class="xl64">8</td> </tr> </tbody></table>

This is a sample list of scores
<table style="border-collapse: collapse; width: 484pt;" border="0" cellpadding="0" cellspacing="0" width="646"><col style="width: 100pt;" width="134"> <col style="width: 48pt;" span="8" width="64"> <tbody><tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt; width: 100pt;" height="19" width="134">
</td> <td style="width: 48pt;" width="64">GEV</td> <td style="width: 48pt;" width="64">GEM</td> <td style="width: 48pt;" width="64">VE</td> <td style="width: 48pt;" width="64">VP</td> <td style="width: 48pt;" width="64">VG</td> <td style="width: 48pt;" width="64">ME</td> <td style="width: 48pt;" width="64">MB</td> <td style="width: 48pt;" width="64">MP</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Blue Devils</td> <td class="xl65">16.4</td> <td class="xl65">16</td> <td class="xl66">15.5</td> <td class="xl65">16.6</td> <td class="xl66">15.5</td> <td class="xl65">16.2</td> <td class="xl65">15.9</td> <td class="xl66">16.3</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Blue Knights</td> <td class="xl65">13.4</td> <td class="xl65">14.9</td> <td class="xl66">13.4</td> <td class="xl65">14.9</td> <td class="xl65">15</td> <td class="xl65">15.3</td> <td class="xl65">14.7</td> <td class="xl65">15.4</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Blue Stars</td> <td class="xl65">13.6</td> <td class="xl65">13.8</td> <td class="xl66">13.7</td> <td class="xl65">15</td> <td class="xl66">13.2</td> <td class="xl65">14.4</td> <td class="xl65">14.7</td> <td class="xl65">14.9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">BlueCoats</td> <td class="xl65">14.7</td> <td class="xl65">14.5</td> <td class="xl66">15</td> <td class="xl65">15.5</td> <td class="xl66">14.4</td> <td class="xl65">15.3</td> <td class="xl65">14.7</td> <td class="xl65">15.4</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Boston Crusaders</td> <td class="xl65">12.4</td> <td class="xl65">14.8</td> <td class="xl66">14</td> <td class="xl65">14.1</td> <td class="xl66">14.7</td> <td class="xl65">15.5</td> <td class="xl65">14.9</td> <td class="xl65">14.9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Carolina Crown</td> <td class="xl65">14.8</td> <td class="xl65">15</td> <td class="xl65">15</td> <td class="xl65">15.2</td> <td class="xl66">15.2</td> <td class="xl65">15</td> <td class="xl65">15.8</td> <td class="xl65">15.6</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Colts </td> <td class="xl65">15</td> <td class="xl65">14.4</td> <td class="xl66">14.6</td> <td class="xl65">14.9</td> <td class="xl66">14.1</td> <td class="xl65">14.5</td> <td class="xl65">14.7</td> <td class="xl65">14.7</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Crossmen</td> <td class="xl65">12.7</td> <td class="xl65">13.7</td> <td class="xl65">15</td> <td class="xl65">12.5</td> <td class="xl66">13.7</td> <td class="xl65">14.3</td> <td class="xl65">12.9</td> <td class="xl65">13.3</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Glassmen</td> <td class="xl65">13.8</td> <td class="xl65">14.3</td> <td class="xl66">14.4</td> <td class="xl65">14.6</td> <td class="xl66">14.3</td> <td class="xl65">13.7</td> <td class="xl65">12.6</td> <td class="xl65">14.7</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Madison Scouts</td> <td class="xl65">12.3</td> <td class="xl65">14.1</td> <td class="xl66">12.9</td> <td class="xl65">13.9</td> <td class="xl66">14</td> <td class="xl65">14</td> <td class="xl65">14.1</td> <td class="xl65">14</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Mandarins</td> <td class="xl65">13.4</td> <td class="xl65">12.8</td> <td class="xl66">11.7</td> <td class="xl65">11.8</td> <td class="xl66">11.7</td> <td class="xl65">13.3</td> <td class="xl65">11.4</td> <td class="xl65">11.6</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Pacific Crest</td> <td class="xl65">14.2</td> <td class="xl65">13.9</td> <td class="xl66">13.5</td> <td class="xl65">12.8</td> <td class="xl66">12.8</td> <td class="xl65">14.3</td> <td class="xl65">13.9</td> <td class="xl65">13.5</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Phantom Regiment</td> <td class="xl65">15</td> <td class="xl65">15</td> <td class="xl66">15.3</td> <td class="xl65">15.7</td> <td class="xl66">15.1</td> <td class="xl65">15.1</td> <td class="xl65">15.3</td> <td class="xl65">15.9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Pioneer</td> <td class="xl65">11.2</td> <td class="xl65">13.5</td> <td class="xl66">13.2</td> <td class="xl65">13.1</td> <td class="xl66">12.4</td> <td class="xl65">12.6</td> <td class="xl65">12</td> <td class="xl65">13.9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Santa Clara Vanguard</td> <td class="xl65">15.8</td> <td class="xl65">15.5</td> <td class="xl66">15.9</td> <td class="xl65">15.9</td> <td class="xl66">15.3</td> <td class="xl65">15.7</td> <td class="xl65">14.8</td> <td class="xl65">15.9</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Spirit</td> <td class="xl65">12.9</td> <td class="xl65">14</td> <td class="xl66">14</td> <td class="xl65">14.3</td> <td class="xl65">15</td> <td class="xl65">14.3</td> <td class="xl65">13.2</td> <td class="xl65">14.5</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">The Academy</td> <td class="xl65">12.9</td> <td class="xl65">13.9</td> <td class="xl66">13.7</td> <td class="xl65">14.4</td> <td class="xl65">15</td> <td class="xl65">15</td> <td class="xl65">13.5</td> <td class="xl65">14.3</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">The Cadets</td> <td class="xl65">15.7</td> <td class="xl65">16.3</td> <td class="xl66">16.4</td> <td class="xl65">16.6</td> <td class="xl66">16.3</td> <td class="xl65">15.8</td> <td class="xl65">16.1</td> <td class="xl65">16.2</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">The Cavaliers</td> <td class="xl65">16</td> <td class="xl65">14.6</td> <td class="xl66">16</td> <td class="xl65">16.3</td> <td class="xl66">15.9</td> <td class="xl65">15.9</td> <td class="xl65">15.6</td> <td class="xl65">16.2</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl67" style="height: 14.4pt;" height="19">Troopers</td> <td class="xl65">13.1</td> <td class="xl65">15</td> <td class="xl66">13.2</td> <td class="xl65">13.5</td> <td class="xl65">14.3</td> <td class="xl65">13</td> <td class="xl65">13.5</td> <td class="xl65">13.9</td> </tr> </tbody></table>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
There are 2 methods, Pivot Table and Filter. You can add your formula in a new column and paste down and then use one of the two methods to sort what you need.
 
Upvote 0
I don't think either method is doing what I need...

It will produce the total score but I need to be able to mis match.

An example of the result i want it to show is...


corps name caption score Caption Value
Blue Devils GEV 16.4 24
Phantom Regiments GEM 15 20
Blue Stars VE 13.7 13
Pacific Crest VP 12.8 9
Boston Crusaders VG 14.7 17
Mandarins ME 13.3 7
Spirit MB 13.2 14
Blue Stars MP 14.9 13

Total 72.7 117

So you see I need it to be able to analyse and choose a combination of captions from different corps (although two or more captions can be from the same corps) that will produce the highest score and the total caption value be 120 or less
 
Upvote 0
So, the values are summed and the scores are based on the formula. I think you are looking at permutations rather than combinations.
=PERMUT(20,8) = 5,079,110,400. For what it is worth:
=COMBIN(20,8) = 125,970

Since there aren't that many rows to show all permutations in a spreadsheet, it might be best to use a process to compute the sums and weighted scores and apply your criteria which should trim down the pack.

There should be something on the net where this kind of thing has been done before. What you would look for is a method to get the list of each permutation. e.g. 1,4,20,3,2,19,9,8 This would be the teams.

If I find something or code it all myself, I will post back.
 
Upvote 0
Thank you so much I think permutations is exactly what I need I appreciate the help more than you can imagine!!
 
Upvote 0
My example xls is at: http://www.mediafire.com/?hnbfbnfl2l2

Notes about my code:
In module mPermute, modify the Sub ComputePerms on the lines:
Code:
  a() = Int2Array(8)
  Main a(), 8

Use Int2Array(20) to compute 20x8 permutations. A minimum of 8 for the 2nd parameter of Main() is needed for the TCompute() in module mTeam to compute the points and scores.

Modify TCompute() if you want to change limits for output. Note that TCompute() has a 2nd optional parameter that you can set to TRUE to output all of the points and scores. Change the Sub Recurse in module mPermute:
Code:
Print #iHandle, (combo & vbTab & TCompute(combo, True))
to
Code:
dim testTCompute as string
testTCompute=TCompute(combo)
If testTCompute<>"" then
  Print #iHandle, (combo & vbTab & testTCompute)
End If
to get a TXT file with just the ones that match the criterion.

In Module mPermute, change or comment/uncomment the lines for output options. I gave several examples for output. I found that output to a TXT file named "Permute 8x8.txt" to be faster than output to the Immediate window by debug.print.

There may be a faster permutation routine on the web but I did not find one. I found that about 1/2 said they did permutations but actually did combinations. Others used arrays or a worksheet to store permutations but those methods are limited by array length and rows in one Sheet when a 20x8 has over 5 billion permutations. Sorting 5 billion rows is a bit of a problem so TCompute() allows more limited selections that match criterion for points.

For a 20x8 computation, my computer was still working on it after 36 hours. It may take quite some time to compute fully.

You can copy and paste the same 20 rows to get Teams in a permuations that could have a permutation with double or triple teams being the same. So, Team ID=1 would also be Team ID=21 is you copied it to the row after team 20. Be sure to redefine the name range tRange if you do this or change the rows for TCompute() to find.

I recommend that you try the 8x8 first. Open the "Permute 8x8.txt" file when it completes to view the results.

Good luck!
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top