Solver add in - Constraint not working

[mkmed]

Hello all,

One of the constraints I have in solver is <= 60000 . However, the returned constraint is 60,500. Every time. There are 1,000's of possibilities and I can't figure out which option is causing the turmoil. Any help is greatly appreciated.

Mark

[Alf]

Without seeing you solver model it’s difficult to give any meaningful advice so I would suggest you upload the file with your solver model.

Alf

[mkmed]

Alf, thanks for the interest in my issue. Please see the attached spreadsheet. Any help is greatly appreciated!!1

[MrShorty]

Assuming the attached file is correct, E142 does not contain a formula nor is it listed in the list of "by changing cells". Exactly as given, the obvious solution is to enter the value 60000 (or 50000, or 54321, or similar) into E142 and be done with it. I expect, though, that you really want some kind of formula in E142 that will compute the salary from the information above it.

[mkmed]

MrShorty, thanks for the suggestion, but the solver has a constraint for cell F142 to be < or = to G142. With 60,000 entered in G142, the solver should find a possible solution that does not exceed 60,000. However the total is in excess of 60,000 each time. I must have an error in the options or something. Thanks for your interest.

Mark

[Alf]

I’ve set up two models for you one the simplex LP the other the Evolutionary model.

There were some problems with your set up. As MrShorty so rightly points out the object function or target cell must contain a formula that changes when solver change the variable cells.

The target cell E145 is now set up as a formula i.e. the sumproduct of the sum values in the j column times the point values in the B column.

The cost calculated by Solver in E142 is again a sumproduct of the j sum values times the cost values in column C.

So both these cells E145 and E142 are now dependent of the changes that Solver does to the variable cells.

The constraint is at the moment set so that the range D136:I136 <= D138:I138 i.e. D136 to I136 should be equal to or less than D138 to I138.

Why equal to or less when you wish D136 to I136 to be equal to D138 to I138?

Looking at you data you wish to have 9 players for a cost of maximum 60000 $ and not more then a total of 1000 points. So the average cost for each player should not exceed $ 6667 and the point should be 111 or less.

Taking the average of the players in the table the average cost is $ 11147 and 201 points solver will not find a solution if you set D136 to I136 = D138 to I138.

To get a solution in this case you must set points constraint to 1570 then the Simplex LP will find a solution (9 players) costing $ 59800 with a total of 1570 points.

For the Evolutionary model if you set maximum points to 1410 then a solution will be found with again 9 players at a cost of $ 59300 with 1409 points.

Alf

Ps Uploaded model has constraint D136:I136 <= D138:I138. You will need to change that if
you wish for a solution with 9 players.