Solver parameter constraint question

M

Morrigan

Here is my question:

Let's say cell A1 is the cell to be changed and I want to restrict th
value of it to 1, 2, 3, 4, 7, 10, 100, or 500. A1 must be chosen fro
one of those values, how can I set up the constraints for thi
situation?


Thank you
 
M

Morrigan

As Far as I know, under "Subject to the Constraints", constraint must be
a number, simple reference, or formula with numeric value. It does not
allow formula with variable output.

Please correct me if I am mistaken or that's not where you would setup
constraints.
 
M

MrShorty

Flintstone's formula would allow you to check to see if Solver returned
one of the desired values. As for constraining solver to one of those
values, I didn't readily see how to do it. I tried setting a
constraint that Flintstones formula = TRUE, but it just gave me an
error.

Are those the only seven possibilities? If I only had seven choices to
choose from, I'd probably do it by hand. Evaluate my
formula/spreadsheet for each of the seven inputs and use the computer
between my ears to determine which of the inputs satisfied my
criteria.

Alternatively, it could be set up over seven rows or sheets (depending
on complexity), then use MAX(), MIN(), or some other combination of
worksheet functions to extract the desired outcome.
 
D

Dana DeLouis

Hi. The use of an "Or" function will not work with Excel as Solver does not
know what to do when it is trying out a values between those given. Here
is one common way to do this type of problem using Excel's Solver.
Here, we will try to have Solver change A1 to one of the 8 values that you
listed.
1). List those 8 values in B1:B8 (ie 1,2,3,4,7,10,100,500)
2). Select C1:C8, and give them a different background color for now to
help you keep track of this range. Solver is going to adjust this range:
C1:C8
3) In A1, enter the following function: =SUMPRODUCT(B1:B8,C1:C8)
4) In C9, enter the following function: =SUM(C1:C8)
5) Now, set up Solver. Add the constraint that C1:C8 are "Bin" This
limits the values to 0 or 1. But, we only want one of those cells to be 1,
the others 0. So, we add the constraint that C9 =1.
However, Excel's solver is not too sophisticated. It won't use an exact
value of 1 in C1:C8. So, as a "technique", make the constraint in C9 both
the following....C9>=0.99, and C9 <= 1.01.

Now, when Solver stops, one of the values in C1:C8 will be 1, and the
corresponding value in column B will be displayed in A1.

Here's another technique.:

Enter the following long formula in A1.

=252+B1*(-(13343/21)+B1*(110749/180+B1*(-(2708/9)+B1*(11705/144+B1*(-(1757/144)+(679/720-(29*B1)/1008)*B1)))))

This is simply a polynomial of the 8 values. We use a helper cell in B1.
Add the constraint that B1 is "INT", (an integer value).
Add the constraints that B1 >=.99, and B1 <= 8.01
As B1 varies from 1,2,3,4,5,6,7 or 8, the corresponding value in A1 will be
one of your 8 values.

Hope this helps.
 
M

Morrigan

Thanks Dana, I understand the logic but cannot get it to work. Here is
what I have:

B2:H2 = 10,15,20,25,30,35,40
B3:H3 = 100,150,200,300,400,600,800
B4:H4 are helper cells to be changed which are constrainted to binary
(ie. either 0 or 1)
B5:H5 = ROUND(B4:H4,0)
I5 = SUM(B5:H5) which is constrainted to = 1

I have a second table that is similar to this one but contains
different values.

B9:H9 = 10,15,20,25,30,35,40
B10H10 = 150,200,250,350,500,800,1000
B11:H11 are helper cells to be changed which are constrainted to binary
(ie. either 0 or 1)
B12:H12 = ROUND(B4:H4,0)
I12 = SUM(B5:H5) which is constrainted to = 1

C17 = SUMPRODUCT(B2:H2,B4:H4)
D17 = HLOOKUP(C17,B2:H3,2)
C18 = SUMPRODUCT(B9:H9,B11:H11)
D18 = HLOOKUP(C18,B9:H10,2)

My target cell is C22 =
(40000*D17+60000*D18+(40000/C17*110+60000/C18*110)*8*5)/1000000 and is
set to find the minimum.

As a starting point, I set B4=1 and B11=1. By trial and error, the end
result should be D4=1 and D11=1. Yet, excel solver does not generate
that result; instead, it says the starting values is the solution.

I tried giving more constraints such as I5 >=0.99, I5 <=1.01, I12
=0.99, I12 <=1.01 but still won't work.
Click to expand...

Any ideas will be appreicated, thank you.
 
D

Dana DeLouis

I'll try to follow what you have in a little bit. But for now, know that
functions like "Round" will not work with Solver. I know you want to use
it, but Solver can't work with it. The function "HLOOKUP" won't work
either, but at this moment, I don't see how it is being used.
Let Solver run without the round function as best it can. In Solver's
options, increase the precision and tolerance also to help limit the small
errors.
When Solver finishes in a macro, I will usually go back to all the Binary
constraint cells and round them to make them exactly 0 or 1. This does
nothing more that to clean it up a little, and to check that all constraints
are still satisfied. Hope this helps a little.
 
D

Dana DeLouis

Just an option. You don't have to, but another option might be to use this
simplified equation for your target cell (C22):
=176/C17 + 264/C18 + (2*D17 + 3*D18)/50

Perhaps copy the formula you are now using, and insert it as a Cell Note for
future reference.
By looking at this formula, it's a little easier to see that to Minimize the
equation, you want C17 & C18 as large as possible, and D17 & D18 as small as
possible.
The use of HLOOKUP is definitely a problem with your model setup. We'll
have to find an alternative method. :>(
 
Top