Solver add in not delivering desired function

Hello,

I am using excel 2010 and had a person help me setup this solver function and it is not delivering results that I expect. I am not proficient with solver and need some help. H2 AND J2 are not calculating correctly and I am unsure why. Please see the attached spreadsheet. Let me know if any questions arise.

Thanks in advance for any help!!!

Mark

I've set up a solver model without all bells and whistles your model had in order to get solver to work.

One of the problems your model had was that in "Options" -> "All Methods" the box marked "Ignore Integer Constraints" was marked so even if you set a number of cells to "binary" solver happily ignored this. Removing this was not enough to get solver to produce a "sensible" result so I rebuild your model on sheet "Mod_salaries"

Alf

Alf, that added a nice enhancement to modify the lineup, and fixed my existing problems, but it divulged two new problems.

1. The goal is to deliver the highest scoring (cell E89) while staying within the max salary cap (Cell F86). When I set cell F86 to a high number, say 100,000, the selections that solver makes are merely the most expensive. Although the most expensive players do have commonly have the highest average points, not always. There are several positions that have performers that score higher, yet cost less. Can the priority be to create the max possible score (cell E89) and stay beneath the salary cap (Cell F86)?

2. When Cell F86 is set to 65,000 solver makes a selection that is over the budget. The selected group must stay below the salary cap in cell F86.

Thank you so much for your help!!!!

Mark

Sorry my bad. Change Objective cell from E86 to E89 that will change solver to go for maximum points instead of maximum costs as I had set it.

Doing so and changing F86 to 65000 made solver find a solution of 164,2 points at a cost of 64200.

Alf

Alf, thank you for the wonderful assistance!!! Works like a charm!!!


Mark

Glad to be of help. Thanks for feed back and rep!

Alf