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:Y2000 ,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,A W41: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

You cannot have conditional functions within a Solver model
So the problem seems to be the IF statements
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Kerry" wrote in message
...
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:Y2000 ,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,A W41: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

On Dec 1, 9:24*pm, "Bernard Liengme"
wrote:
You cannot have conditional functions within a Solver model
So the problem seems to be the IF statements
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP

"Kerry" wrote in message

...

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:Y2000 ,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,A W41: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

I see. Any suggestions for a workaround? I kind of need those IF
statements.

Thanks again,
K

Check will www.solver.com. Maybe one of the premium version will work
best wishes

"Kerry" wrote in message
...
On Dec 1, 9:24 pm, "Bernard Liengme"
wrote:
You cannot have conditional functions within a Solver model
So the problem seems to be the IF statements
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP

"Kerry" wrote in message

...

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:Y2000 ,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,A W41: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

I see. Any suggestions for a workaround? I kind of need those IF
statements.

Thanks again,
K

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 in
http://groups.google.com/group/micro...f16683d9c0b0d5

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-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

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

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

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!

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

I got using ABS to work in creating 0s and 1s and thus removed the IF
statement, but it seems to fail for the same reason as using IF in
SOLVER.

I was thinking I could remove ABS from the above equation C1 =((A1-
B1)+ABS(A1-B1))*(1/(2*(A1-B1))) and turn into:
C1 =(A1-B1). This creates negative and positive number, where
negatives would have = 0 in the ABS equation and 1 for the positives.
Then I'd sum the values in the target cell and ask Solver to maximize
the values. Thus it would try to push as many values above 1 as
possible.

The problem is twofold:
1. There will be a dependency on the magnitude of values, which is
incorrect in my case (all values should be equally important in my
case)
2. Because my real equation which I've simplified above is really not
an IF but instead IFAND statement, I need something that considers
only 1 of the criteria above 0 to be as good ("optimized") as ALL of
the criteria equaling 1)

It seems I need a way to get my neg and pos values to equal 0 and 1
respectively (or vice versa) PRE-solver, without using ABS or any
other Solver stopping functions

K

On Dec 7, 12:02*am, Kerry wrote:
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

I got using ABS to work in creating 0s and 1s and thus removed the IF
statement, but it seems to fail for the same reason as using IF in
SOLVER.

I was thinking I could remove ABS from the above equation C1 =((A1-
B1)+ABS(A1-B1))*(1/(2*(A1-B1))) and turn into:
*C1 =(A1-B1). This creates negative and positive number, where
negatives would have = 0 in the ABS equation and 1 for the positives.
Then I'd sum the values in the target cell and ask Solver to maximize
the values. Thus it would try to push as many values above 1 as
possible.

The problem is twofold:
1. There will be a dependency on the magnitude of values, which is
incorrect in my case (all values should be equally important in my
case)
2. Because my real equation which I've simplified above is really not
an IF but instead IFAND statement, I need something that considers
only 1 of the criteria above 0 to be as good ("optimized") as ALL of
the criteria equaling 1)

It seems I need a way to get my neg and pos values to equal 0 and 1
respectively (or vice versa) PRE-solver, without using ABS or any
other Solver stopping functions

K

By the way, I just tried using SQRT of the squared values instead of
ABS and SOLVER doesn't work with it. I'm not surprised since I guess
Sqrt can cause gaps or jumps just like ABS. Now I'm getting worried.

Kerry -

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested checking www.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, see
http://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- Mike
http://www.MikeMiddleton.com

On Dec 7, 5:33*pm, "Mike Middleton"
wrote:
Kerry *-

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Thanks for the info. I looked at Solver.com but the program that
features the IF function "linearizer" is $3000+! I'm a poor student,
so...But I also feel like I got so close (see last post)! I'd feel
like a overcame a challenge if I got it to work. I still don't quite
get the example provided by Tushar Mehta but at least I know what he
meant be adding a binary constraint. I'll keep working at it.

Thanks,
k

On Dec 7, 10:59*pm, Kerry wrote:
On Dec 7, 5:33*pm, "Mike Middleton"
wrote:



Kerry *-

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Thanks for the info. I looked at Solver.com but the program that
features the IF function "linearizer" is $3000+! I'm a poor student,
so...But I also feel like I got so close (see last post)! I'd feel
like a overcame a challenge if I got it to work. I still don't quite
get the example provided by Tushar Mehta but at least I know what he
meant be adding a binary constraint. I'll keep working at it.

Thanks,
k

Herbert, Goal Seek appears to only be able to change 1 cell (i.e.
variable) to get the target. I have to change 4 cells to optimize the
target cell. Am I right that the sample you provided has only cell for
the "by changing cell" box?

On Dec 7, 10:59*pm, Kerry wrote:
On Dec 7, 5:33*pm, "Mike Middleton"
wrote:



Kerry *-

Are there otherSolverlimitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
PremiumSolvercan automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standardSolvergenerally
cannot.

For the standardSolveradd-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

What is a binary variable in excel, how do I incorporate it and wouldn't
it also cause the function gaps or sudden jumps thatSolverhas issues
with? <

A binary variable is restricted to the values zero or one. On theSolver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, becauseSolveruses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Thanks for the info. I looked atSolver.com but the program that
features the IF function "linearizer" is $3000+! I'm a poor student,
so...But I also feel like I got so close (see last post)! I'd feel
like a overcame a challenge if I got it to work. I still don't quite
get the example provided by Tushar Mehta but at least I know what he
meant be adding a binary constraint. I'll keep working at it.

Thanks,
k

Trying Solver.com program now w/ free trial. First run was way off. I
actually think I have the optimal solution visually so I have a good
min value I know exists. I'm using the evolutionary algorithm as
suggested in the help file for non-smooth functions. I also have
placed bounds on all the variables. I think I will narrow the bounds
next time and let it run all night if possible), that is, if I can
ever get the first run to stop! I total it stop when it reached the
time limit and alse pressed pause. Now it just says it's "pausing..."
and keeps going

On Dec 7, 10:59*pm, Kerry wrote:
On Dec 7, 5:33*pm, "Mike Middleton"
wrote:



Kerry *-

Are there otherSolverlimitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
PremiumSolvercan automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standardSolvergenerally
cannot.

For the standardSolveradd-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

What is a binary variable in excel, how do I incorporate it and wouldn't
it also cause the function gaps or sudden jumps thatSolverhas issues
with? <

A binary variable is restricted to the values zero or one. On theSolver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, becauseSolveruses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Thanks for the info. I looked atSolver.com but the program that
features the IF function "linearizer" is $3000+! I'm a poor student,
so...But I also feel like I got so close (see last post)! I'd feel
like a overcame a challenge if I got it to work. I still don't quite
get the example provided by Tushar Mehta but at least I know what he
meant be adding a binary constraint. I'll keep working at it.

Thanks,
k

Trying Solver.com program now w/ free trial. First run was way off. I
actually think I have the optimal solution visually so I have a good
min value I know exists. I'm using the evolutionary algorithm as
suggested in the help file for non-smooth functions. I also have
placed bounds on all the variables. I think I will narrow the bounds
next time and let it run all night if possible), that is, if I can
ever get the first run to stop! I total it stop when it reached the
time limit and alse pressed pause. Now it just says it's "pausing..."
and keeps going

Excel 2007
Yes, Goal Seek has only one variable,
but a workaround is iteration and RMS.
Illustrated that by adding two more columns.
Graphed it for visualization.
Solution has a range of values.
http://c0444202.cdn.cloudfiles.racks...12_07_09a.xlsm

On Dec 8, 6:58*pm, Herbert Seidenberg wrote:
Excel 2007
Yes, Goal Seek has only one variable,
but a workaround is iteration and RMS.
Illustrated that by adding two more columns.
Graphed it for visualization.
Solution has a range of values.http://c0444202.cdn.cloudfiles.racks...12_07_09a.xlsm

Thanks, I'll take a look tomorrow afternoon. In the meantime, Ray
Koopman over at http://groups.google.com/group/sci.stat.math helped
showed me a good sigmoid function that allows me to keep my data as
continuous but get values to reach very close to 0 and 1 depending on
what side of the thresholds they're on. Excel Solver is still fussy
about this since it is highly non-linear but it at least gave me a
more optimized value than I had eyeballed. That said, I look forward
to going over your suggestion to possibly getting something easier on
Solver since I have to change these data values iteratively (i.e.
after each Solver optimization).

Thanks again,
K

On Dec 7, 5:33*pm, "Mike Middleton"
wrote:
Kerry *-

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Trying Solver.com program now w/ free trial. First run was way off. I
actually think I have the optimal solution visually so I have a good
min value I know exists. I'm using the evolutionary algorithm as
suggested in the help file for non-smooth functions. I also have
placed bounds on all the variables. I think I will narrow the bounds
next time and let it run all night if possible), that is, if I can
ever get the first run to stop! I told it stop when it reached the
time limit and also pressed pause. Now it just says it's "pausing..."
and keeps going.

On Dec 8, 12:36*am, Kerry wrote:
On Dec 7, 5:33*pm, "Mike Middleton"
wrote:



Kerry *-

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Trying Solver.com program now w/ free trial. First run was way off. I
actually think I have the optimal solution visually so I have a good
min value I know exists. I'm using the evolutionary algorithm as
suggested in the help file for non-smooth functions. I also have
placed bounds on all the variables. I think I will narrow the bounds
next time and let it run all night if possible), that is, if I can
ever get the first run to stop! I told it stop when it reached the
time limit and also pressed pause. Now it just says it's "pausing..."
and keeps going.

Solver.com's program doesn't help.. I tried auto-detecting the best
settings and putting them in manually using suggested parameters from
the help file (e.g. evolutionary algorithm). A couple times it said it
got an answer but the answer I already wasn't best. So no help there.

Still holding out hope that someone will explain Tushar Mehta
solution!!!

Thanks,
K

On Dec 8, 1:03*pm, Kerry wrote:
On Dec 8, 12:36*am, Kerry wrote:



On Dec 7, 5:33*pm, "Mike Middleton"
wrote:

Kerry *-

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested *checkingwww.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.

For a brief description of these issues, seehttp://www.solver.com/xlsplatformb.htm

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? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.

- *Mikehttp://www.MikeMiddleton.com

Trying Solver.com program now w/ free trial. First run was way off. I
actually think I have the optimal solution visually so I have a good
min value I know exists. I'm using the evolutionary algorithm as
suggested in the help file for non-smooth functions. I also have
placed bounds on all the variables. I think I will narrow the bounds
next time and let it run all night if possible), that is, if I can
ever get the first run to stop! I told it stop when it reached the
time limit and also pressed pause. Now it just says it's "pausing..."
and keeps going.

Solver.com's program doesn't help.. I tried auto-detecting the best
settings and putting them in manually using suggested parameters from
the help file (e.g. evolutionary algorithm). A couple times it said it
got an answer but the answer I already wasn't best. So no help there.

Still holding out hope that someone will explain Tushar Mehta
solution!!!

Thanks,
K

Solver.com's program seems to inherently transform discontinuous data
(e.g. data using IF statements such as mine) using binaries in Excel,
I assume similar to Tushar Mehta's proposed solution. I think I may
know why my situation might fail for both, however (even though like I
said I don't quite get Tushar's example):

The four variables I change (A,B,C, and D) to optimize my target
variable (X) I do not think are linear. As mentioned, they represent
thresholds that sample values are compared to.

For example, if A = .5 and I have a column of sample data that has
values between say .1 and .7, I check to see how many of those values
are below .5. My aim is to find a
value for A that minimizes the total number of values in the sample
data that are less than A. So far it sounds linear, right?.

But in actuality, each of my sample data points are compared to, for
example, A and B, where I want to minimize the number of data points
that are below both threshold A AND threshold B. That is, being less
than just A OR just B is OK. I think this causes non-linearity, right?
I can't see how to transform my IF functions in a linear way given the
two variable dependency. For instance, changing IF(X<A), then 1,
otherwise 0, is easy (e.g. X-A = a positive or negative which can
represent binaries), but changing IF(X<A AND X<B), then 1, otherwise
0, is tough because there are 4 potential scenarios, only one of which
would = one binary and the three other would = the other binary.

The frustrating thing is I have a pretty narrow boundary where I see
optimization occurring, and only about 1000 data points. It seems like
it's too much to handle manually, but a "try all" algorithm would be
manageable assuming I make the resolution low (e.g. change values by .
01 for each iteration).