Find best players for cheapest price

[JackBauer]

Hello, Imagine you have a large table containing players with their salaries and average performance index. For example:

Player Price Index
Iverson 100usd 20ppg
James 90usd 19ppg
Ming 80usd 25ppg
Carmelo 20usd -5PPG
.....................................and so on

Imagine having fixed amount of money, like 150USD. You need to buy exactly 2 players to your roster. You must get maximum amount of ppg and not to exceed 150 usd.

This is some sort of maximisation equation.

How to do that in excel?

[davsth]

In the real world how many players will you be choosing from? and will you be limited to 2?

the answer will be either with solver or VBA so try and go through all of the combinations.

Why not attach a sheet as per the yellow banner? Noone will wish to be typing your data in again to solve your problem

[Hydraulics]

If the table is not too large, you can use the standard Solver.

You need a column with binary variables, and then a couple of very simple formulas to keep track of total price and performance index.

Add rows as needed, and update the attached setup.

HTH,

Francesco