Running average with calculated salary cap

[gamma725]

Hi there,
I have two columns of data.
The first column consists of salary and the second column consists of average points per game.
There are 147 rows of data.

I am trying to choose the top 6 BEST averages while keeping the salary of the 6 chosen to not exceed $50,000.
There may be times when the absolute best six averages are not chosen as the best six if the salary exceeds $50k.

Thanks!

sample data:
[TABLE="width: 185"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Salary[/TD]
[TD]AvgPointsPerGame[/TD]
[/TR]
[TR]
[TD="align: right"]11100[/TD]
[TD="align: right"]98.44[/TD]
[/TR]
[TR]
[TD="align: right"]9300[/TD]
[TD="align: right"]98.06[/TD]
[/TR]
[TR]
[TD="align: right"]9700[/TD]
[TD="align: right"]96.06[/TD]
[/TR]
[TR]
[TD="align: right"]9000[/TD]
[TD="align: right"]95.22[/TD]
[/TR]
[TR]
[TD="align: right"]7600[/TD]
[TD="align: right"]94.94[/TD]
[/TR]
[TR]
[TD="align: right"]11400[/TD]
[TD="align: right"]94.75[/TD]
[/TR]
[TR]
[TD="align: right"]10800[/TD]
[TD="align: right"]93.75[/TD]
[/TR]
[TR]
[TD="align: right"]7500[/TD]
[TD="align: right"]93.18[/TD]
[/TR]
[TR]
[TD="align: right"]9500[/TD]
[TD="align: right"]92.62[/TD]
[/TR]
[TR]
[TD="align: right"]8000[/TD]
[TD="align: right"]92.44[/TD]
[/TR]
[TR]
[TD="align: right"]8700[/TD]
[TD="align: right"]88.56[/TD]
[/TR]
[TR]
[TD="align: right"]10200[/TD]
[TD="align: right"]88.25[/TD]
[/TR]
[TR]
[TD="align: right"]7300[/TD]
[TD="align: right"]86.41[/TD]
[/TR]
[TR]
[TD="align: right"]7600[/TD]
[TD="align: right"]85.61[/TD]
[/TR]
[TR]
[TD="align: right"]7900[/TD]
[TD="align: right"]83.25[/TD]
[/TR]
[TR]
[TD="align: right"]8500[/TD]
[TD="align: right"]83.08[/TD]
[/TR]
[TR]
[TD="align: right"]10500[/TD]
[TD="align: right"]82.38[/TD]
[/TR]
[TR]
[TD="align: right"]8400[/TD]
[TD="align: right"]82.28[/TD]
[/TR]
[TR]
[TD="align: right"]7100[/TD]
[TD="align: right"]82.06[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

One option, in C2 is =IF(SUM(A2:A7)<=50000,"50k or under","") copied down.

It shows "50k or under" in C11 as well as C15:C20.

So A11 and the 5 below are the 6 chosen. (the best group under 50k)



Another way of looking at it is put =A2/B2 in C2 and =RANK(D2,$C$2:$C$20) in D2, then filter by the bottom 6 results. This gives 6,9,11,14,15 and 20 for 45100. The best performers per Salary.


You could spend more on better players and have worse ones there, or go for a better average, or a mix. There's no real correct answer without more detail

 

[gamma725]

Thanks. I've sent it along to my Son-in-law and hopefully he'll have what he needs!