Formula to select 6 Player Team with combined lowest rank score - /w Team salary cap of 50,000 or less

[hinsdale1]

[TABLE="class: grid, width: 318, align: center"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD] Salary [/TD]
[TD] Rank[/TD]
[TD]Picks[/TD]
[/TR]
[TR]
[TD]Jordan Spieth[/TD]
[TD="align: right"]12600[/TD]
[TD]1.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jason Day[/TD]
[TD="align: right"]11500[/TD]
[TD]4.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Justin Rose[/TD]
[TD="align: right"]10900[/TD]
[TD]5.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim Furyk[/TD]
[TD="align: right"]8800[/TD]
[TD]11.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bubba Watson[/TD]
[TD="align: right"]9900[/TD]
[TD]15.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rickie Fowler[/TD]
[TD="align: right"]9800[/TD]
[TD]19.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dustin Johnson[/TD]
[TD="align: right"]10700[/TD]
[TD]22.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul Casey[/TD]
[TD="align: right"]8700[/TD]
[TD]23.05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henrik Stenson[/TD]
[TD="align: right"]9600[/TD]
[TD]24.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt Kuchar[/TD]
[TD="align: right"]9300[/TD]
[TD]24.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zach Johnson[/TD]
[TD="align: right"]8100[/TD]
[TD]25.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brendan Steele[/TD]
[TD="align: right"]7100[/TD]
[TD]25.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Robert Streb[/TD]
[TD="align: right"]7900[/TD]
[TD]27.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jason Bohn[/TD]
[TD="align: right"]6900[/TD]
[TD]29.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Danny Lee[/TD]
[TD="align: right"]7700[/TD]
[TD]31.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Webb Simpson[/TD]
[TD="align: right"]7900[/TD]
[TD]32.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brooks Koepka[/TD]
[TD="align: right"]9700[/TD]
[TD]35.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patrick Reed[/TD]
[TD="align: right"]8100[/TD]
[TD]36.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rory Sabbatini[/TD]
[TD="align: right"]6400[/TD]
[TD]36.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hideki Matsuyama[/TD]
[TD="align: right"]8500[/TD]
[TD]38.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chad Campbell[/TD]
[TD="align: right"]6600[/TD]
[TD]39.55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Russell Henley[/TD]
[TD="align: right"]7500[/TD]
[TD]40.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brandt Snedeker[/TD]
[TD="align: right"]8600[/TD]
[TD]42.05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David Lingmerth[/TD]
[TD="align: right"]7800[/TD]
[TD]43.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jason Kokrak[/TD]
[TD="align: right"]6100[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]William McGirt[/TD]
[TD="align: right"]6400[/TD]
[TD]51.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Justin Thomas[/TD]
[TD="align: right"]7700[/TD]
[TD]51.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ryan Moore[/TD]
[TD="align: right"]7600[/TD]
[TD]52.05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Watney[/TD]
[TD="align: right"]7000[/TD]
[TD]52.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stewart Cink[/TD]
[TD="align: right"]6100[/TD]
[TD]53.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill Haas[/TD]
[TD="align: right"]8000[/TD]
[TD]56.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Will Wilcox[/TD]
[TD="align: right"]7200[/TD]
[TD]57.05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony Finau[/TD]
[TD="align: right"]7300[/TD]
[TD]57.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lee Westwood[/TD]
[TD="align: right"]7000[/TD]
[TD]58.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pat Perez[/TD]
[TD="align: right"]6500[/TD]
[TD]61.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David Hearn[/TD]
[TD="align: right"]6300[/TD]
[TD]61.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ryo Ishikawa[/TD]
[TD="align: right"]5800[/TD]
[TD]62.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kevin Na[/TD]
[TD="align: right"]6900[/TD]
[TD]64.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marc Leishman[/TD]
[TD="align: right"]7100[/TD]
[TD]64.65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jimmy Walker[/TD]
[TD="align: right"]7600[/TD]
[TD]65.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charl Schwartzel[/TD]
[TD="align: right"]8200[/TD]
[TD]65.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charles Howell III[/TD]
[TD="align: right"]6600[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim Herman[/TD]
[TD="align: right"]6300[/TD]
[TD]68.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt Jones[/TD]
[TD="align: right"]6500[/TD]
[TD]69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harris English[/TD]
[TD="align: right"]6800[/TD]
[TD]70.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scott Brown[/TD]
[TD="align: right"]6500[/TD]
[TD]70.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boo Weekley[/TD]
[TD="align: right"]6300[/TD]
[TD]72.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keegan Bradley[/TD]
[TD="align: right"]7500[/TD]
[TD]72.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scott Piercy[/TD]
[TD="align: right"]6700[/TD]
[TD]72.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Russell Knox[/TD]
[TD="align: right"]6400[/TD]
[TD]72.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daniel Summerhays[/TD]
[TD="align: right"]6200[/TD]
[TD]75.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J.B. Holmes[/TD]
[TD="align: right"]7300[/TD]
[TD]76.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Billy Horschel[/TD]
[TD="align: right"]7500[/TD]
[TD]77.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Luke Donald[/TD]
[TD="align: right"]7400[/TD]
[TD]79.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vijay Singh[/TD]
[TD="align: right"]6200[/TD]
[TD]80.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil Mickelson[/TD]
[TD="align: right"]8300[/TD]
[TD]81.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George McNeill[/TD]
[TD="align: right"]6200[/TD]
[TD]81.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl Pettersson[/TD]
[TD="align: right"]7100[/TD]
[TD]82.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jonas Blixt[/TD]
[TD="align: right"]6900[/TD]
[TD]84.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg Owen[/TD]
[TD="align: right"]6300[/TD]
[TD]86.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis Love III[/TD]
[TD="align: right"]6300[/TD]
[TD]87.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ryan Palmer[/TD]
[TD="align: right"]6800[/TD]
[TD]88.55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shawn Stefani[/TD]
[TD="align: right"]6200[/TD]
[TD]88.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Scott[/TD]
[TD="align: right"]8400[/TD]
[TD]89.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kevin Kisner[/TD]
[TD="align: right"]6900[/TD]
[TD]90.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark Wilson[/TD]
[TD="align: right"]5700[/TD]
[TD]91.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ian Poulter[/TD]
[TD="align: right"]7100[/TD]
[TD]91.65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Colt Knost[/TD]
[TD="align: right"]5900[/TD]
[TD]92.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James Hahn[/TD]
[TD="align: right"]5900[/TD]
[TD]93.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Graham DeLaet[/TD]
[TD="align: right"]6800[/TD]
[TD]93.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steven Bowditch[/TD]
[TD="align: right"]6600[/TD]
[TD]95.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson Wagner[/TD]
[TD="align: right"]6000[/TD]
[TD]95.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kevin Chappell[/TD]
[TD="align: right"]6700[/TD]
[TD]96.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Hadwin[/TD]
[TD="align: right"]5800[/TD]
[TD]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jason Dufner[/TD]
[TD="align: right"]6700[/TD]
[TD]100.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alex Cejka[/TD]
[TD="align: right"]5600[/TD]
[TD]102.9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Can excel Formula or vba script work to analyze table and place an "X" in Picks column next to the 6 Player Team which achieves the lowest possible total combined rank (adding their 6 rank scores together) WITH a Team salary Maximum of 50,000 or less?

Little out of my pay grade but would appreciate any help which points me in the right direction.

Thank you in advance to any MATH GENIUSES who are willing to assist!

 

[StephenCrump]

There is a bit of rewriting required, because of the different way the combinations are being put together.

I have a busy work week lined up after a week away, so my time is limited.

To open the question up for other contributors, I suggest you start a new post, including some sample data, and a link to this thread.

If you PM me the new link, I'll take a look when I can, but sorry, it probably won't be soon.

 

[hinsdale1]

Thanks Stephen, completely understand on the busy schedule. Appreciate your efforts already!! Have been using workarounds with current subroutine (which is completely awesome - THANK YOU) to build football teams - and have come up with some decent results. It would be major cool to come up with the perfect team though !!

I think having 9 players might be burdensome computing.. I can manually select the QB and DST, so actually only need to generate the ideal 7 players: 2 RBs, 3 WRs, 1 TE, 1 FLEX (either RB,TE,WR) - for the remaining salary available ($39500 apprx).

Anyway, appreciate any help.tip.direction you can provide when your schedule eases up!!! (if you have time) Loving the idea of generating the perfect team I will post another thread but havent been having a lot of luck finding other contributors on more involved VBA stuff. Hope to chat soon!