Can you post a small sample workbook of what you are trying to accomplish?
I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?
Thanks Jim, I looked at the worksheet you provided. Unfortunately I don't think it'll do the trick. You suggested as constraints:
A5 <= max of desired range
A5 = integer
A5 >= min of desired range
In the particular instance of the data you provided, it works - but only because the solution happens to be the max values of all three data columns. Presumably it would also work fine when the solution used all the minimums.
My problem is that the solution (I'm going to be using many more columns and perhaps 200 rows when I do this for real) is almost certainly going to involve many intermediate values, and I require that they be drawn exclusively from pre-existing values in my data column. My constraint needs to limit A5 to the specific values contained in a specific range.
I'm starting to feel like maybe Solver can't do this. Do I need VBA? I've been holding off on learning it for as long as possible (saying "nah, I don't need it, I'll just use solver and reeeeeally long IF statements") but this might be the last straw.
Any ideas? Thanks again,
I the following array formula in cell A7 for example and filled across.
=MIN(ABS(A5-A1:A4)) confirm with ctrl+shift+enter
If this formula returns zero then you mave a match with the required set of values. Set these formula cells equal to zero as a constraint.
The problem is non-linear so you might need to play around with solver settings.
hope this works,
(had to edit because I forgot a constraint, doh! )
After playing around, I think its a bit much for solver to handle. You really need to keep things linear for solver to have a chance. I've reworked the solver model, its a bit more complicated but everything is linear so it should work. You might be waiting a while for a large problem instance though.
Last edited by Paul Corry; September 11th, 2006 at 10:02.
That's a great solutions. Thanks. I'll apply it to my full model.
The original idea like you said caused solver to blow a gasket. I'm not sure exactly what this linearity issue is. Could you explain it in simple language? Is it that solver can only find an answer when it makes small, discrete steps?
Glad I could help, your problem was a fun little exercise. Here is a carsh course on linearity.
if your variables are denoted X1, X2 and X3, a linear expression takes the form:
aX1 + bX2 + cX3
where a,b and c are constants.
examples of non-linear expressions are:
aX1^2, sin(X1), abs(X1), max(X1,X2), etc.
Models which have only linear expressions have search spaces with smooth and predictable geometry, so given enough time Solver should be able to find a solution.
Non-linear models have "bumpy" and unpredictable search spaces so Solver can easly get trapped, unable to continue its search. Often (but not always), a non-linear model can be transformed into a linear model by introducing new variables and constraints.
The moral of the story is to try to use a linear formulation wherever possible.
There's my carsh course in linearity, hope it made sense,
First you said:
then:What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
So, now I'm confused, did you try my solver set-up because I think it works for any values in the range as long as they are integers and there is only one solution (like you said at first)?My problem is that the solution (I'm going to be using many more columns and perhaps 200 rows when I do this for real) is almost certainly going to involve many intermediate values, and I require that they be drawn exclusively from pre-existing values in my data column. My constraint needs to limit A5 to the specific values contained in a specific range.
There are currently 1 users browsing this thread. (0 members and 1 guests)