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:

Salary	AvgPointsPerGame
11100	98.44
9300	98.06
9700	96.06
9000	95.22
7600	94.94
11400	94.75
10800	93.75
7500	93.18
9500	92.62
8000	92.44
8700	88.56
10200	88.25
7300	86.41
7600	85.61
7900	83.25
8500	83.08
10500	82.38
8400	82.28
7100	82.06

<colgroup><col><col></colgroup><tbody>
</tbody>

 

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