I can't add binary constraint in optimization solver in excel

Question

So I have to do an optimization problem (minimize) using Excel Solver where there are many constraints, one of them is a binary constraint. But every time i try to add this binary constraint, it keeps showing this error: "Binary Constraint Cell Reference must include only Variable Cells."

I already made sure that I added the binary variable in the By Changing Variable Cells box on the top of the solver, but it is still not working.

Also, it worked when I only have simple binary decision variable. Specifically, when I only have 1 set of decision variable and it is binary (no other sets of decision variable to be calculated for) -- you want to find only optimal x's (no other variables) and these x's can only be 0 or 1. It works in this case. But for the case where I have to find (let's say) optimal x's (binary) and optimal y's (continuous number), it doesn't work.

I don't know what I could possibly do it wrong. Please help. Thank you!

Answer 1

Hi BK,

Thanks for contacting us and sorry for the inconveniences,

https://stephenlnelson.com/articles/optimization-modeling-solver-excel/

I understand that you want to use a binary constraint to restrict some of your variables to be either 0 or 1, but you keep getting an error message that says "Binary Constraint Cell Reference must include only Variable Cells." I'll try to explain what this error means and how to fix it.

The error message that you are getting means that you are trying to apply a binary constraint to a cell that is not a variable cell. A variable cell is a cell that contains a value that can be changed by Solver to find the optimal solution. A variable cell must be included in the By Changing Variable Cells box on the top of the Solver Parameters dialog box . A binary constraint can only be applied to a variable cell, not to a cell that contains a formula, a constant, or a reference to another cell .

To fix this error, you need to make sure that the cell reference that you enter in the Cell Reference box for the binary constraint is a variable cell. For example, if you have a variable cell in A1 that represents the binary decision variable x, and you want to add a binary constraint to it, you need to enter A1 in the Cell Reference box, and then select bin from the drop-down list box. You can also select the cell directly from the spreadsheet by clicking the icon next to the Cell Reference box .

If you have multiple binary decision variables, you can enter them as a range or as a list separated by commas in the Cell Reference box. For example, if you have variable cells in A1, A2, and A3 that represent the binary decision variables x, y, and z, and you want to add a binary constraint to them, you can enter A1:A3 or A1,A2,A3 in the Cell Reference box, and then select bin from the drop-down list box . ***Note: This is a non-Microsoft websites. The page appear to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.***

I hope this helps!

Regards, Sola

Answer 2

Hi again,

I'm sorry I wasn't able to help, unfortunately, I do not have any more troubleshooting to offer, there are knowledgeable experts on the platform and I hope someone else will take a look at your issue.

Thanks, Sola

Answer 3

Dear Sola,

Thank you for your advice! However, I have entered the cell reference for binary variable in the By Changing Variable Cells box on the top of the Solver Parameters dialog box already. So basically I have added these binary variable cells in both decision variable box and the constraint box, but it is still not working. I have attached the pictures here in case they might help figuring out what is really going on. Thank you so much

figure 1: binary variable added to the By Changing Variable Cells box

![](https://learn-attachment.microsoft.com/api/attachments/34b46738-389a-464d-bbd5-884813df8946?platform=QnA