Can't set integer constraint on certain cell

Question

I have a production programming exercise that I'm supposed to solve by using the Excel solver. However, I am not able to set an integer constraint on a certain cell that I need to be integer only.

I attached a minimal example to this post, the structure is as follows:
There are 6 products, each with quantities to make-to-order and to make-to-stock, the sum of those two and their respective contribution margin. The total contribution margin is calculated as the sumproduct of the total quantity of each product multiplied with its contribution margin.
Now for the solver, I want to maximize the total contribution margin, by changing the make-to-stock values for each product and the make-to-order values for some products (there are some more constraints which are not relevant to this problem).

Now I already set the solver to where the problem happens, as soon as I try to add an integer constraint to a make-to-stock cell, where the make-to-order cell above it is set to change, I get the error "Integer Constraint Cell Reference must include only Variable Cells.". I don't understand why, because the cell (F3 in this case) is marked as a variable cell. Is this a bug in Solver, or am I completely missing something?

Minimal example: Link

Solution

RikuXan,

The only way that error pops up is when the cells you are adding integer constraints to are not in the "By Changing Variable Cells" box. If you have different variable cells or nothing in that box, that error will pop up when you apply integer restraints.

For your example, you must have both the make-to-stock cell and the make-to-order cell in the "by changing variable..." box.

This is an extremely rare error message that I believe only students like us have trouble with, as I have found nothing else about this on the internet. I solved it for an exercise I was attempting shortly after I came across this post.

Hope that helps (I tried to post a picture but this is my first post). David

Comments

[RikuXan]

Hello David, I am aware of the requirement that the constrained cell must be included in the variable cells list. However my problem was that even with this condition fulfilled, the Solver would still give the error message (Just try to add a constraint on F3 in my example, gives the message even though it is included in B3:G3;F2). In the end I managed to circumvent the problem by restructuring my solution, but I'm still kinda convinved that this is a Solver bug and not me being too stupid to use it. I guess it is related to using a unioned cell set in the By Changing box, but I'm not sure.

[qxg]

It takes me a few minutes to find 'By Changing Variable Cells' box. It would be better to include a snapshot.