Prev: Show decreases and increases
Next: Charting Question
From: Mike Middleton on 7 Dec 2009 17:33
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

From: Kerry on 7 Dec 2009 18:09
On Dec 7, 12:12 am, Kerry <kbro...(a)gmail.com> wrote:
> On Dec 7, 12:02 am, Kerry <kbro...(a)gmail.com> wrote:
>
>
>
> > On Dec 6, 10:08 pm, Kerry <kbro...(a)gmail.com> wrote:
>
> > > On Dec 2, 1:52 pm, Kerry <kbro...(a)gmail.com> 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 <kbro...(a)gmail.com>
> > > > > 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:Y2000,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 x>0 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.

In case anyone is paying attention, for which I am still holding out
desperate hope, I want to update my progress. Any new readers can
start at this post and hopefully understand the entire issue at hand.

To recap, I initially had IF statements that would equal 0 or 1
depending if a value was more or less some threshold value. Here, I
wanted Solver to change this threshold value to minimize the number of
1s (i.e. maximize the number of 0s). But Solver won't work with IF
statements.

Thus, I needed a workaround where I would essentially get 0s and 1s
without using a IF statements. I tried using ABS as a workaround but
Solver has issues with this too.

Currently, I've tried using sigmoidal functions:

-First, I normalize such that all former 1s are now negative numbers
and 0s are positive numbers (remember that I am using the convention
of wanting to minimize 1s and thus negative numbers in this
transformation). This gives me a range of data with no theoretical max
or min, but in practice gives between -0.39 and 1.07.

-Next, I want to cluster this continuous range of pos and neg values
as closely to 0 and 1, respectively so I use a sigmoidal function on
each data value in the general form of:

f(x)=1-1/(1+e^(-weight*x)),

where x = the given data value (the given pos or neg value being
compared to the threshold); e = 2.718; weight = a value I can change
to squeeze/stretch the sigmoid shape; and you can see I subtract all
from 1 in order to reverse 1s and 0s from = pos and neg, to 1=
negative values and 0 = positive values, respectively.

My results show that using weight = 1000 makes the sigmoid squeezed
horizontally enough to make almost all positive values = 0 (i.e. where
the values are close enough to 0 that Excel just outputs them as 0),
but before I reach a weight high enough to make all pos = 0 and neg =
1, I get #NUM! for many of my formerly negative values. It seems that
Excel has a lower resolution for small compared to large numbers, and
this limit of resolution is reached for many of my negative data point
values before I can increase the weight enough to make most of my
values essentially 0 and 1.

Do my explanations sound correct for what is going on? Any suggestions
on how to move forward?

Thanks,
k



From: Herbert Seidenberg on 7 Dec 2009 21:51
Excel 2007
Here is a similar non-linear problem
with if()s that Solver can't readily solve,
but Goal Seek can.
http://www.mediafire.com/file/yztmyuioiy4/12_07_09.xlsx
From: Kerry on 7 Dec 2009 22:59
On Dec 7, 5:33 pm, "Mike Middleton" <M...(a)DecisionToolworks.com>
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
From: Kerry on 8 Dec 2009 00:35
On Dec 7, 10:59 pm, Kerry <kbro...(a)gmail.com> wrote:
> On Dec 7, 5:33 pm, "Mike Middleton" <M...(a)DecisionToolworks.com>
> 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?

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Show decreases and increases
Next: Charting Question