Excel: Solver Error Value in a Target or Constraint

ID: Q90185

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY

In Microsoft Excel, when setting constraints for a Solver problem, if you type the word "integer" (without the quotation marks) in the Constraint box in the Add Constraint dialog box, Solver fails.

MORE INFORMATION

Excel Solver finds solutions to problems such as maximizing profits or adjusting expenditures to fit within a given budget. When using Solver, you specify various parameters, including constraints on the values that can be used. One possible constraint is "integer," which tells Solver that the number must be a positive or negative whole number or zero.

For example, if you're selling cars, you don't want Solver to tell you that the way to trim your budget is to sell only half a car or -3 cars. In this case, you want to constrain the number of cars sold to an integer value that is greater than, or equal to, 0 (zero).

When adding constraints to your Solver model in the Add Constraint dialog box, if you type the word "integer" into the Constraint box and choose the Add button, Solver adds your constraint; however, if you then choose the OK button in the Add Constraint dialog box, Solver displays the message:

Constraint must be a number, simple reference, or a formula with a numeric value.

When you choose the OK button in this message box, Solver returns you to the Add Constraint dialog box. If you then choose the Cancel button (or if you originally chose the Cancel button after choosing Add) in the Add Constraint dialog box, Solver returns you to the Solver Parameters dialog box.

If you now choose the Solve button in the Solver Parameters dialog box, Solver attempts to find a solution but fails.

Solver displays the Solver dialog box containing the error message:

Solver encountered an error value in a target or constraint cell

To properly constrain a value to an integer, select "int" from the list that contains the comparison operators (<=, = >=) in the Add Constraint dialog box. Notice that when you select int from the list, Solver enters the word "integer" in the Constraint box.

REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, pages 79-120

Additional query words: 7.00

Keywords : kbualink97
Version :
Platform :
Issue type :