Solver not working [Charts]

Prev: Show decreases and increases
Next: Charting Question

From: Kerry on 8 Dec 2009 00:36

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

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.

From: Kerry on 8 Dec 2009 00:49

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

From: Kerry on 8 Dec 2009 00:50

From: Kerry on 8 Dec 2009 13:03

On Dec 8, 12:36 am, 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
>
> 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

From: Kerry on 8 Dec 2009 14:21

On Dec 8, 1:03 pm, Kerry <kbro...(a)gmail.com> wrote:
> On Dec 8, 12:36 am, 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
>
> > 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).

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