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!

 

[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?