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!

 

[123rickfear]

I've been having a play with this and can't seem to get the formula right, I expect it will need a vba (which is not my strong area).

However, after playing around it looks like Rose, Furyk, Johnson, Steele, Streb and Bohn is your best 6 man team, 124.3 rank.

Rick

 

[ag007]

=SUMIFS(RANK, SALARY,"<="50000",RANK,"<="&SMALL(RANK,6)) -> adds the ranks of the lowest 6 ranked players that have salaries below 50k

 

[123rickfear]

that doesn't give the correct result, that just adds up the 6 lowest ranked players, but the salaries add up to 63500

 

[hinsdale1]

I've been having a play with this and can't seem to get the formula right, I expect it will need a vba (which is not my strong area).

However, after playing around it looks like Rose, Furyk, Johnson, Steele, Streb and Bohn is your best 6 man team, 124.3 rank.

Rick

Thanks Rick - helpful for this lineup - but this was just example.

 

[hinsdale1]

that doesn't give the correct result, that just adds up the 6 lowest ranked players, but the salaries add up to 63500

Thanks for clarifying 123rickfear.. can now stop trying to make that work Any tweaks to this formula that might do better?


Or does anyone have any other formula suggestions or VBA solutions/concepts I might try?

 

[hinsdale1]

Seems like maybe an initial VBA query for all the combination of six salaries (player place in sequence combination not being important) which are less than equal to 50,000 - and then cross-reference these resulting 6 player teams to then assign them a score (simply adding their combined rank scores together) and finding the smallest?

 

[ag007]

that doesn't give the correct result, that just adds up the 6 lowest ranked players, but the salaries add up to 63500

If the smallest 6 ranks, add up to 63,500 in salaries, how can there be another combination of 6 ranks that adds up to less than 63,500 (i.e. 50,000) ?

 

[hinsdale1]

If the smallest 6 ranks, add up to 63,500 in salaries, how can there be another combination of 6 ranks that adds up to less than 63,500 (i.e. 50,000) ?

Ranks and Salaries are separate unrelated data points.. there are loads of 6 salary combinations below or equal to 50000 - then just need to find which 6 salary combination results in the lowest combined rank.

 

[hinsdale1]

Any VBA experts be willing to point me in right direction? Perhaps initial <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> query for all the combination of six salaries (player place in combinations not being important) which are between 49,500 and 50,000 - and then cross-reference these resulting 6 player teams to then assign them a score (simply adding their combined rank scores together) and finding the smallest?