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

[hinsdale1]

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

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

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!