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.
Code:
[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]