SOLVER does not iterate / work

[BHatMJ]

I am using SOLVER for the first time and it appears that it is not iterating.
The initial objective and "change" values do not change at all after running
SOLVER.

My objective is to minimize the difference^2 between two columns of data by
optimizing one cell which contains a "cut" value.

- The first column of data contains fixed integer values of 0 and 1 for
10,000 cases.
- The second column of data contains decimal values between 0 and 1 for
10,000 cases.
- A third column is calculated based on a cut value ("change" value) where
values in the second column >= cut are set to 1 and values < cut are set to 0.
- The objective/target is to minimize the sum of the differences between the
1st and 3rd column squared.

Can anyone help?

 

[Dana DeLouis]

- A third column is calculated based on a cut value ("change" value)
where

values in the second column >= cut are set to 1 and values < cut are

set to 0.

Hi. Just a guess of course. Are you using IF() functions to make this
decision? If so, Solver "usually" will not work if you use these types
of functions.
Don't know exactly how you are set up to offer any suggestions.
= == = =
Dana DeLouis

 

[BHatMJ]

Yes, the third column is based on IF() statements. Would you know of any
solutions to this issue or a work-around?

 

[Mike Middleton]

BHatMJ -

One workaround is to use a slightly more advanced version of Solver, instead
of the standard Solver that ships with Excel. For example, the Premium
Solver for Education may be able to work with your discontinuous functions.
Visit www.solver.com.

Another workaround is to convert the IF statements to a set of binary
variables. The specifics depend on your current problem and your skills at
what might be a considerable reformulation. But then you could likely use
standard Solver for the mixed integer programming problem.

- Mike

http://www.MikeMiddleton.com

 

[Dana DeLouis]

minimize the sum of the differences ... squared.

Hi. Speaking very generally, Solver may see many "local" minimums when
doing this type of model. There may be a way to change your model.

Having said that, sometimes this can be quick way to visualize what's
going on. If the precision of the "cut" is not too great, perhaps have
a macro step thru the model, and record the output. Then graph the
data, and see what you got.
Perhaps have "cut" change from 0 to 1 step 0.01, and record the output
next to the cell. Then do a line graph of your data.

In some cases, this can be a quick workaround for a difficult problem.
It may also bring out any logic problems in the model setup.

= = = = = = = = =
Dana DeLouis