A complex problem....

[Reboshua]

I realize that this is outside the scope of what any reasonable person would expect a forum to answer, but I've tried to pay some people on Fiverr to do this for me and been rejected each time, saying the problem was too complex or they couldn't do it. So, I toss it to the excel gods here and we'll see how the universe responds. Thank you in advance.

[TABLE="width: 925"]
<tbody>[TR]
[TD]Budget[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Speed Goal[/TD]
[TD="align: right"]280[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Constraints[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Breeder Limit[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]1. Must select exactly 9 animals from the list[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]2. No animal can be selected twice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Animal[/TD]
[TD]Price[/TD]
[TD]Avg Speed[/TD]
[TD]Color[/TD]
[TD]Breeder[/TD]
[TD]STDEV of Speed[/TD]
[TD][/TD]
[TD="colspan: 6"]3. Must pick 2 brown, 2 pink, 1 orange, 2 grey, and 2 tan animals[/TD]
[/TR]
[TR]
[TD]Butterfly[/TD]
[TD]56.00[/TD]
[TD]19.17[/TD]
[TD]Brown[/TD]
[TD]8.00[/TD]
[TD]10.14[/TD]
[TD][/TD]
[TD="colspan: 3"]4. Cannot exceed budget of 600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cattle[/TD]
[TD]44.00[/TD]
[TD]18.72[/TD]
[TD]Orange[/TD]
[TD]8.00[/TD]
[TD]9.18[/TD]
[TD][/TD]
[TD="colspan: 6"]5. No more than "x" animals from a given breeder where x is the variable in cell B3[/TD]
[/TR]
[TR]
[TD]Cod[/TD]
[TD]35.00[/TD]
[TD]13.07[/TD]
[TD]Grey[/TD]
[TD]8.00[/TD]
[TD]11.55[/TD]
[TD][/TD]
[TD="colspan: 6"]6. At any given time, the list of available animals and their respective prices, colors, etc. will change.[/TD]
[/TR]
[TR]
[TD]Crocodile[/TD]
[TD]36.00[/TD]
[TD]12.97[/TD]
[TD]Tan[/TD]
[TD]8.00[/TD]
[TD]9.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deer[/TD]
[TD]36.00[/TD]
[TD]13.91[/TD]
[TD]Brown[/TD]
[TD]8.00[/TD]
[TD]9.18[/TD]
[TD][/TD]
[TD]Goal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dogfish[/TD]
[TD]35.00[/TD]
[TD]7.58[/TD]
[TD]Orange[/TD]
[TD]8.00[/TD]
[TD]4.90[/TD]
[TD][/TD]
[TD="colspan: 6"]1. To find the optimized list of 9 animals that achieve the speed goal I set out in cell B2[/TD]
[/TR]
[TR]
[TD]Elk (wapiti)[/TD]
[TD]35.00[/TD]
[TD]3.74[/TD]
[TD]Brown[/TD]
[TD]8.00[/TD]
[TD]1.90[/TD]
[TD][/TD]
[TD="colspan: 6"]2. Achieve Goal 1 (above) with the lowest possible standard deviation for the entire "portfolio." (see explanation below)[/TD]
[/TR]
[TR]
[TD]Flamingo[/TD]
[TD]35.00[/TD]
[TD]3.92[/TD]
[TD]Grey[/TD]
[TD]8.00[/TD]
[TD]1.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frog[/TD]
[TD]35.00[/TD]
[TD]3.25[/TD]
[TD]Pink[/TD]
[TD]8.00[/TD]
[TD]1.25[/TD]
[TD][/TD]
[TD="colspan: 4"]Context - (apologies if you already know this)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boar[/TD]
[TD]61.00[/TD]
[TD]21.75[/TD]
[TD]Pink[/TD]
[TD]7.00[/TD]
[TD]9.78[/TD]
[TD][/TD]
[TD="colspan: 6"]The standard deviation for the entire portfolio is equal to the square root of the sum of all of the[/TD]
[/TR]
[TR]
[TD]Buffalo[/TD]
[TD]47.00[/TD]
[TD]16.14[/TD]
[TD]Orange[/TD]
[TD]7.00[/TD]
[TD]11.59[/TD]
[TD][/TD]
[TD="colspan: 6"]individual animal variances. Variance is simply the STDEV squared. So, for each combination of 9 animals that meets[/TD]
[/TR]
[TR]
[TD]Camel[/TD]
[TD]53.00[/TD]
[TD]22.09[/TD]
[TD]Tan[/TD]
[TD]7.00[/TD]
[TD]9.34[/TD]
[TD][/TD]
[TD="colspan: 6"]the constraints above, we'll calculate the standard deviation for that unique 9-animal combination.  Once we have all[/TD]
[/TR]
[TR]
[TD]Cassowary[/TD]
[TD]45.00[/TD]
[TD]15.91[/TD]
[TD]Grey[/TD]
[TD]7.00[/TD]
[TD]10.76[/TD]
[TD][/TD]
[TD="colspan: 6"]possible combinations and their respective "portfolio" standard deviations, we'll deliver the combination with the lowest[/TD]
[/TR]
[TR]
[TD]Chimpanzee[/TD]
[TD]44.00[/TD]
[TD]17.79[/TD]
[TD]Pink[/TD]
[TD]7.00[/TD]
[TD]10.20[/TD]
[TD][/TD]
[TD="colspan: 3"]standard deviation as our output.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dunlin[/TD]
[TD]35.00[/TD]
[TD]2.03[/TD]
[TD]Brown[/TD]
[TD]7.00[/TD]
[TD]0.89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ant[/TD]
[TD]75.00[/TD]
[TD]32.43[/TD]
[TD]Grey[/TD]
[TD]3.00[/TD]
[TD]10.33[/TD]
[TD][/TD]
[TD="colspan: 6"]Desired Output (data is for illustrative purposes - does not match data to the left)[/TD]
[/TR]
[TR]
[TD]Antelope[/TD]
[TD]68.00[/TD]
[TD]22.37[/TD]
[TD]Pink[/TD]
[TD]3.00[/TD]
[TD]10.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bee[/TD]
[TD]74.00[/TD]
[TD]28.65[/TD]
[TD]Brown[/TD]
[TD]3.00[/TD]
[TD]10.07[/TD]
[TD][/TD]
[TD]Color[/TD]
[TD]Animal[/TD]
[TD]Speed[/TD]
[TD]Price[/TD]
[TD]Breeder[/TD]
[TD]Stdev[/TD]
[/TR]
[TR]
[TD]Capybara[/TD]
[TD]45.00[/TD]
[TD]18.37[/TD]
[TD]Pink[/TD]
[TD]3.00[/TD]
[TD]9.18[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Ant[/TD]
[TD]40.56[/TD]
[TD]90.00[/TD]
[TD]1.00[/TD]
[TD]9.39[/TD]
[/TR]
[TR]
[TD]Clam[/TD]
[TD]45.00[/TD]
[TD]18.84[/TD]
[TD]Brown[/TD]
[TD]3.00[/TD]
[TD]8.04[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Antelope[/TD]
[TD]32.43[/TD]
[TD]75.00[/TD]
[TD]2.00[/TD]
[TD]8.03[/TD]
[/TR]
[TR]
[TD]Falcon[/TD]
[TD]35.00[/TD]
[TD]3.77[/TD]
[TD]Tan[/TD]
[TD]3.00[/TD]
[TD]2.60[/TD]
[TD][/TD]
[TD]Pink[/TD]
[TD]Bee[/TD]
[TD]27.28[/TD]
[TD]63.00[/TD]
[TD]4.00[/TD]
[TD]3.98[/TD]
[/TR]
[TR]
[TD]Fly[/TD]
[TD]35.00[/TD]
[TD]1.85[/TD]
[TD]Tan[/TD]
[TD]3.00[/TD]
[TD]0.65[/TD]
[TD][/TD]
[TD]Pink[/TD]
[TD]Capybara[/TD]
[TD]25.99[/TD]
[TD]57.00[/TD]
[TD]3.00[/TD]
[TD]2.50[/TD]
[/TR]
[TR]
[TD]Albatross[/TD]
[TD]84.00[/TD]
[TD]29.55[/TD]
[TD]Brown[/TD]
[TD]1.00[/TD]
[TD]10.03[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]Clam[/TD]
[TD]28.65[/TD]
[TD]74.00[/TD]
[TD]5.00[/TD]
[TD]9.65[/TD]
[/TR]
[TR]
[TD]Alligator[/TD]
[TD]91.00[/TD]
[TD]41.02[/TD]
[TD]Pink[/TD]
[TD]1.00[/TD]
[TD]9.49[/TD]
[TD][/TD]
[TD]Grey[/TD]
[TD]Falcon[/TD]
[TD]24.90[/TD]
[TD]53.00[/TD]
[TD]6.00[/TD]
[TD]8.53[/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]60.00[/TD]
[TD]25.98[/TD]
[TD]Tan[/TD]
[TD]1.00[/TD]
[TD]9.39[/TD]
[TD][/TD]
[TD]Grey[/TD]
[TD]Fly[/TD]
[TD]45.78[/TD]
[TD]91.00[/TD]
[TD]6.00[/TD]
[TD]9.11[/TD]
[/TR]
[TR]
[TD]Crab[/TD]
[TD]50.00[/TD]
[TD]17.46[/TD]
[TD]Grey[/TD]
[TD]1.00[/TD]
[TD]8.03[/TD]
[TD][/TD]
[TD]Tan[/TD]
[TD]Albatross[/TD]
[TD]23.66[/TD]
[TD]35.00[/TD]
[TD]8.00[/TD]
[TD]8.28[/TD]
[/TR]
[TR]
[TD]Dinosaur[/TD]
[TD]35.00[/TD]
[TD]6.01[/TD]
[TD]Brown[/TD]
[TD]1.00[/TD]
[TD]3.98[/TD]
[TD][/TD]
[TD]Tan[/TD]
[TD]Alligator[/TD]
[TD]30.74[/TD]
[TD]62.00[/TD]
[TD]3.00[/TD]
[TD]4.25[/TD]
[/TR]
[TR]
[TD]Dove[/TD]
[TD]35.00[/TD]
[TD]6.89[/TD]
[TD]Pink[/TD]
[TD]1.00[/TD]
[TD]2.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]280.00[/TD]
[TD]600.00[/TD]
[TD][/TD]
[TD]5.97[/TD]
[/TR]
[TR]
[TD]Bear[/TD]
[TD]51.00[/TD]
[TD]23.01[/TD]
[TD]Tan[/TD]
[TD]6.00[/TD]
[TD]9.65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Caterpillar[/TD]
[TD]43.00[/TD]
[TD]16.17[/TD]
[TD]Orange[/TD]
[TD]6.00[/TD]
[TD]8.53[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chough[/TD]
[TD]45.00[/TD]
[TD]14.68[/TD]
[TD]Pink[/TD]
[TD]6.00[/TD]
[TD]9.11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cobra[/TD]
[TD]40.00[/TD]
[TD]15.22[/TD]
[TD]Brown[/TD]
[TD]6.00[/TD]
[TD]8.28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Coyote[/TD]
[TD]35.00[/TD]
[TD]8.10[/TD]
[TD]Grey[/TD]
[TD]6.00[/TD]
[TD]4.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dotterel[/TD]
[TD]50.00[/TD]
[TD]18.31[/TD]
[TD]Pink[/TD]
[TD]6.00[/TD]
[TD]9.24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dugong[/TD]
[TD]35.00[/TD]
[TD]3.89[/TD]
[TD]Tan[/TD]
[TD]6.00[/TD]
[TD]1.40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eagle[/TD]
[TD]39.00[/TD]
[TD]10.90[/TD]
[TD]Pink[/TD]
[TD]6.00[/TD]
[TD]7.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eland[/TD]
[TD]35.00[/TD]
[TD]10.29[/TD]
[TD]Orange[/TD]
[TD]6.00[/TD]
[TD]7.94[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alpaca[/TD]
[TD]90.00[/TD]
[TD]40.56[/TD]
[TD]Grey[/TD]
[TD]2.00[/TD]
[TD]10.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Anteater[/TD]
[TD]78.00[/TD]
[TD]28.47[/TD]
[TD]Pink[/TD]
[TD]2.00[/TD]
[TD]10.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chinchilla[/TD]
[TD]46.00[/TD]
[TD]15.48[/TD]
[TD]Pink[/TD]
[TD]2.00[/TD]
[TD]8.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****roach[/TD]
[TD]42.00[/TD]
[TD]15.62[/TD]
[TD]Grey[/TD]
[TD]2.00[/TD]
[TD]7.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cormorant[/TD]
[TD]55.00[/TD]
[TD]28.86[/TD]
[TD]Grey[/TD]
[TD]2.00[/TD]
[TD]7.26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dragonfly[/TD]
[TD]35.00[/TD]
[TD]4.66[/TD]
[TD]Pink[/TD]
[TD]2.00[/TD]
[TD]1.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Badger[/TD]
[TD]57.00[/TD]
[TD]22.01[/TD]
[TD]Grey[/TD]
[TD]4.00[/TD]
[TD]9.59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barracuda[/TD]
[TD]46.00[/TD]
[TD]20.00[/TD]
[TD]Brown[/TD]
[TD]4.00[/TD]
[TD]10.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]47.00[/TD]
[TD]19.03[/TD]
[TD]Orange[/TD]
[TD]4.00[/TD]
[TD]9.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cheetah[/TD]
[TD]50.00[/TD]
[TD]19.33[/TD]
[TD]Tan[/TD]
[TD]4.00[/TD]
[TD]9.71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]48.00[/TD]
[TD]14.24[/TD]
[TD]Brown[/TD]
[TD]4.00[/TD]
[TD]10.21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]37.00[/TD]
[TD]12.26[/TD]
[TD]Grey[/TD]
[TD]4.00[/TD]
[TD]8.84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD]37.00[/TD]
[TD]14.38[/TD]
[TD]Pink[/TD]
[TD]4.00[/TD]
[TD]5.85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elephant seal[/TD]
[TD]35.00[/TD]
[TD]6.73[/TD]
[TD]Orange[/TD]
[TD]4.00[/TD]
[TD]4.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ferret[/TD]
[TD]35.00[/TD]
[TD]9.16[/TD]
[TD]Grey[/TD]
[TD]4.00[/TD]
[TD]2.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]35.00[/TD]
[TD]13.74[/TD]
[TD]Brown[/TD]
[TD]4.00[/TD]
[TD]9.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]35.00[/TD]
[TD]0.70[/TD]
[TD]Orange[/TD]
[TD]4.00[/TD]
[TD]0.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bat[/TD]
[TD]57.00[/TD]
[TD]25.99[/TD]
[TD]Orange[/TD]
[TD]5.00[/TD]
[TD]10.63[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beaver[/TD]
[TD]66.00[/TD]
[TD]26.98[/TD]
[TD]Tan[/TD]
[TD]5.00[/TD]
[TD]8.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bison[/TD]
[TD]53.00[/TD]
[TD]24.90[/TD]
[TD]Grey[/TD]
[TD]5.00[/TD]
[TD]10.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chamois[/TD]
[TD]60.00[/TD]
[TD]22.68[/TD]
[TD]Tan[/TD]
[TD]5.00[/TD]
[TD]8.77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crane[/TD]
[TD]60.00[/TD]
[TD]19.89[/TD]
[TD]Tan[/TD]
[TD]5.00[/TD]
[TD]9.76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RatExcel]

I do not uderstand:

5. No more than "x" animals from a given breeder where x is the variable in cell B3

Please explain.

 

[Reboshua]

The value under the Breeder column is meant to represent an individual animal breeder. I used the numbers 1-8, but probably should have used actual breeder names to be less confusing.

The breeder constraint is intended to limit risk so that you don't select 5 animals from one breeder.

 

[snusnu]

You will probably be better off using Access for this sort of problem. While there are many ways to solve this problem, this is the general approach I would take:

1. Import your data into a table in an Access database
2. Create SELECT queries to get the various combinations of animals you want. For example, the following query gets all pairings of brown animals (filtering out pairings of the same animal) along with the total price of that pairing:

SELECT Data.Animal AS Animal1, Data_1.Animal AS Animal2, Data.Breeder AS Breeder1, Data_1.Breeder AS Breeder2, (Data.Price+Data_1.Price) AS TotalPrice
FROM Data INNER JOIN Data AS Data_1 ON ((Data.Color = Data_1.Color) AND (Data.Animal <> Data_1.Animal))
WHERE Data.Color = 'Brown'

You will have to figure out how to aggregate your other metrics (average and stdev).

3. After you have all your combinations, create a macro that loops through all combinations of these combinations using a nested loop structure. For each loop, check if any of your constraints have been violated yet. If they have, then go to the next iteration of that loop.

In the end you should be able to get a list of all combinations that meet your required constraints.

 

[shg]

It seems easy enough to do, it's just time consuming (there are 12B possible arrangments), and would require a fair amount of code.

Either generate all of the combinations, test to criteria, and, among those that pass, select the one with the lowest SD about the desired speed. That would be fast in generating the combinations, but relatively slow to evaluate each one

An alternative would be to build each combination element by element, testing against crteria as each element is added. That would be faster, I think, but the code would be messier.

 

[shg]

BTW, the speed goal of 280 seems ambitious; the speed of the 9 fastest totals ~ 283.

 

[snusnu]

Actually there will be far fewer than 12 billion possible arrangements. The trivial estimate of combinations can be computed as 59 choose 9, which is about 12 billion as you have stated. However, there is no need to check all 12 billion combinations. In fact, each constraint will serve to further limit the number of combinations that are required to check. For example, I have calculated that the number of combinations of brown and grey animals that have no more than two of the same breeder and a total cost of less than 600 is 11330. Similarly for pink and tan animal pairings, the number of combinations is 9808. Finally, there are 9 orange animals. So, an updated estimate of the number of combinations would be 11330*9808*9, or about 1 billion combinations. That's without filtering out those combinations to ensure that no more than two of the same breeder exists and that the total cost isn't exceeded, which would only further limit the number of combinations.

 

[Reboshua]

Thank you for the replies so far. I think snusnus is correct in that the constraints make the number of arrangements far fewer than 12 billion, which I was hoping would make this possible in excel using Solver. I am knowledgeable enough to structure the problem properly, but I don't know very much about Access and am trying to self-teach more advanced excel capabilities.

shg, the goal of 280 was just an example. I imagine there are times when that will be possible and times when it won't be, which will require adjusting the value higher or lower.

The other complicating factor for this problem is that the animals and breeders will change, as will their respective attributes. So, each time I do this, it will be on a different set of variables.

Off to learn Access, I suppose

 

[snusnu]

Reboshua, keep in mind that the more constraints and combinations you add, the harder (computationally speaking) this problem becomes. You may need to abandon the idea of finding the single most efficient solution and settle on finding the first X number of solutions that match your constraints.

 

[shg]

Actually there will be far fewer than 12 billion possible arrangements.

Actually, there are 59C9 combinations, of which some meet the criteria. It's possible to do instead brown choose 2, grey choose 2, orange choose 1, ... evaluating fewer combinations (~ 232M) at the expense of code complexity.