On Dec 6, 10:08*pm, Kerry wrote:
On Dec 2, 1:52*pm, Kerry wrote:



On Dec 2, 12:09*pm, Tushar Mehta <ng-underscore-poster-at-tushar-

hyphen-mehta.see-oh-em wrote:
I haven't read your post in detail but a long time ago I shared
several tips on how to convert various non-linear criteria into linear
ones.

See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro...

If I can count correctly, the 3rd post by me shows how to "linearize"
a IF condition.

On Tue, 1 Dec 2009 13:53:45 -0800 (PST), Kerry
wrote:

I have 4 cells that Solver is supposed to change to minimize the value
of one target cell, where the target cell sums a bunch of rows that
have changed values depending on the 4 aforementioned cells.

Whether I click min or max in solver, it says it solves the equation
fully but the results do not change for min or max compared to the pre-
solver values. To test if it was working, I (in short) click for the
solver to find an exact target value I know for a fact exists locally
(and required only changing one cell value), but Solver says it cannot
find a feasible solution.

All of this tells me that the proper operations or operation sequence
is not happening during the execution of solver. I thought I'd fixed
the issue at first, when I rearranged the involved formulas so that
everything was on the same Excel sheet, but it didn't work.

Are there other Solver limitations I need to know about that could be
causing the issue? I was surprised that Microsoft Support and the
Solver Help file did not mention that I need to have all formula
references on the same tab, and so am concerned there's other
limitations I am not aware of.

I have tried changing all Solver Options too, but no help.

I will try to simplify the involved data below:

Solver changes these constants...
Cell A2 = 0.31
Cell A3 = 0.25.
Cell A4 = 0.67
Cell A5 = 0.52

to minimize the value of a target cell with the formula:
=SUM
(D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y200 0,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000, AW41:AX2000,BB41:BC2000)

The columns summed in the target cell formula above have their own
formulas. An example would be:
=IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0),
where C41 and B41 are constants I am not changing in solver, A$2 is
one of the constants I am changing in solver, and A$6 and A$9 have
formulas that reference some of the constants I am changing but are
not directly inputted to the solver.

Thus, solver changing any of A$2 through A$5, will change A$9 and/or A
$6, which in turn changes the column values that are summed in the
target cell formula, thus changing the target cell value.

Thanks for any help,
K

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-presentwww.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

Hi,

I found where you address the IF statement, but I'm having trouble
following. I think this is on the right path for me, so I'd appreciate
any patience and help to clarify. I've posted your previous post below
with questions at the end of each sentence:

"First, the IF statement. *Suppose that a firm has a choice of 2
plants where it can produce a product. *If it uses a particular plant
to
produce any amount of the product, it incurs a fixed cost of say
$50,000." -----Do I understand this as choose the plant that creates
more product for the fixed $50,000 price?

"This has the nature of an IF statement of the type [IF x0 then K
else 0], where K is a constant." -----I don't get what x or K
represent. Does x = amount of product and K = $50,000?

"One can replace the IF with linear equations by introducing a binary
variable, b, and a large constant, say, M. *Now, the IF statement
becomes
* * K*b
* * x <= M*b
* * b = 0/1 (b is binary)
* * x = 0
How does it work? *If x is anything other than 0, the x <= M*b will
be
satisfied only if b is 1. *If b is 1, the K*b will evaluate to K!
Also,
since M is a very large number, once b is 1, x <= M*b will always be
true no matter how large x becomes". ----Does x represent essentially
the binary threshold (i.e. less than x then with this plant, more than
x go with the other plant). If so, can it be a non-zero number? ----
Also I don't get how the binary is applied in Excel.

Thanks!
K

Can anyone help clarify this please? I've seen a similar solution
elsewhere but I can't make out the explanations and thus how to fit to
my data. What is a binary variable in excel, how do I incorporate it
and wouldn't it also cause the function gaps or sudden jumps that
Solver has issues with? To simplify my example above I have the below
example, though the real Excel equaitions are more complicated:

I have a column with:
C1 =IF(A1<B1,1,0)
C2 =IF(A2<B1,1,0)
....
C1000 =IF(A1000<B1,1,0)

Then, D1 = sum(C1:C1000)

Solver is asked to reduce D1 (i.e. target cell) by changing B1.

I found a method using absolute values in the C column equation, e.g.
C1 =((A1-B1)+ABS(A1-B1))*(1/(2*(A1-B1))). This creates values of 0 or
1, but I think this will have the same issue since absolute values can
mess up functions too. I'm currently trying to work it in, which is
tough because the real C columns are IFAND arguments.

Thanks again,
K

Actually, I think I may be able to just remove the ABS and attempts to
make the values 0 and 1, and instead just try to find the max value in
solver. This will maximize the # of positive values, which are all the
ones that would be 0s and not 1s in my case. Then, post-solver, I can
simply count the number of positive values. I'll post again the
results!