Problem: why is solver not finding the optimal schedule of lifts
regardless of initial guess?
Hi. I may be wrong, but here is my opinion as to "why" Solver is not
working correctly.
The Solver code that I use quickly flagged your Target cell as a possible
problem.
The reason is that Solver can not handle functions that are discontinuous,
or "Jump."
You will notice that your optimal solution had just a few tanks that needed
"lifting." (4).
All the other cells need to be at 0! I mean at 0, and not 0.0001.
You will notice that in your bad scenario "base" that most of the solutions
are at the end of the list, with the last value 0.
This is not a fact, or anything one can prove, but what I believe Solver did
was start close to the solution you see here, and then tried a 0 at the end,
got confused, and then just Quit!!
Why? Let me use a different Target cell formula.
Suppose we use the following which is similar in concept to what you are
doing.
=IF(A1=0,0,5000+2*A1)
When A1 is 2, the output is 5004. For each unit change in A1, the output
changes by 2.
This is a form of Derivative that Excel uses to establish the next guess.
See Solver's Options for Estimates, Derivatives, & Search.
When A1 is 1, the output is 5002. And of course, when A1 is 0, the output
should be 5000. But wait. the output jumped by 5000 to 0, and Solver does
not know why !! It may have tried 0.000001 in A1, and got 5000.000002.
So, Solver does not know why a very small change made it jump by 5000.
Solver will usually quit immediately when just 1 cell exhibits this bad
behavior.
One usually has to use a model that does not use functions that Jump. (ie
Max, Min, If, ..etc)
One possible workaround here "might" be to use a helper column of "Binary"
constraints. The cell would be 1 if cost equation is used, and 0 if not
used.
Anyway, I hope this helps.
--
HTH :)
Dana DeLouis
Windows XP & Office 2003
"deano" wrote in message
ups.com...
KISS : keep It Simple Sam. This is a revised request to help on a
period solver to schedule lifts
You have a tank that contains liquid. You make liquid each day at some
rate. The tank has to hold this daily make. Tank has a max and a min
volume of liquid to hold. You have to lift liquid out of tank so that
it stays within main & max. It costs $5 + $0.42/bbl to lift liquid out.
You know how much liquid you have in tank to start and how much liquid
make for the next 21 days. what is the optimal lift schedule. See the
solver & click show scenarios available here
http://availg.com/images/stories/tools/runout_1.xls
Problem: why is solver not finding the optimal schedule of lifts
regardless of initial guess?
For Example:
go to model tab:
click show scenarios
select base then close
go to Tools solver
click solve
solve does not find optimal schedule.
click show scenarios
select optimal then close
That is the optimal solution.
Your help is greatly appreciated.