Excel: Solver Cannot Constrain Value to Multiple of a Value

• Microsoft Excel for Windows versions 3.0, 4.0
• Microsoft Excel for OS/2 version 3.0

SUMMARY

The Solver program that comes with Microsoft Excel cannot force an adjustable cell value to be a multiple of a specified value. The Set cell value also cannot be constrained to a multiple of a certain value in a Max or Min problem.

MORE INFORMATION

Often, it would be desirable to have the adjustable variables or optimal solution of a Solver model result in a multiple of a certain value. One example is an investment transaction to purchase bonds in $1000 lots. It may be impossible to purchase $750 of a bond with a $1000 face value; thus, a result that is a multiple of 1000 would be more appropriate.

To constrain an adjustable cell to a multiple of another value, the MOD function, with the adjustable cell as the number argument and the multiple as the divisor argument, would be constrained to a value of zero. Solver is not able to "back-solve" (that is, calculate the inverse for) this type of model.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, page 155

"Microsoft Excel Solver User's Guide," version 3.0, page 13