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. :(
--
Dana DeLouis
Win XP & Office 2003


"Morrigan" wrote in
message ...

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.

Any ideas will be appreicated, thank you.





Dana DeLouis Wrote:
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.

--
Dana DeLouis
Win XP & Office 2003


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile:
http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=376929