BleepingComputer.com: Excel Solver

I am trying to solve a problem using the solver in excel. I am close to what I want but I am not sure how to do a certain constraint.

I want for the constraint to be only a couple sizes allowed.

ex. I can't have a 13.5 size wire and a 16.5 wire at the same time. There is to much of a margin with the size. It has to be atleast within 1. So I could use a size 14.5, 15, and 15.5 or a 15, 15.5, 16.

This link has a screen shot, which I thought would be easier than explaining my spreadsheet.
http://docs.google.com/View?id=ddm7njsk_0cbb8tbdv



I appreciate any help you guys can give.

This post has been edited by stevenc1010: 03 July 2009 - 04:01 PM

I don't think you can do this in one go as you have more than one possible solution to the problem, and I don't think the SOLVE function can deal with this.

What you could do is break it down into 5 separate SOLVE functions for the different ranges:

13.5, 14.0, 14.5
14.0, 14.5, 15.0
14.5, 15.0, 15.5
15.0, 15.5, 16.0
15.5, 16.0, 16.5

These could be saved as different macros.

Then, once you have your 5 different solutions you could choose which one is best (presumably by selecting which one has the lowest CM value).