prspilot -
The standard Solver that ships with Excel cannot usually handle
discontinuous functions like IF, CHOOSE, etc.
In some models you can replace the discontinuous functions with
relationships using integer variables, and then use the standard Solver to
get a solution.
Frontline Systems, the developer of Excel's standard Solver, also has
advanced versions that perform better with discontinuous functions. Many
quantitative methods textbooks include their Premium Solver. (Microsoft has
chosen to continue distributing only the original standard Solver with
Excel.) You can get information about Premium Solver at
http://www.solver.com/xlspremsolv.htm
- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
"prspilot" wrote in message
...
I have built a worksheet that calulates the theoretical return on
investment
during a one year period. I have 300 days of the price of the stock in
column A. Column B is the daily % increase. The next several columns
contain formulas with lots of "If..Then..Else" statements based on a set
of 5
variables. The last column uses all of the previous columns to calculate
the
balance at the end of that day. The balance in the last column also of
course depends on the balance from the previous day. When I change
variables
manually, the sheet works fine.
I want to solve for the maximum value of the balance on the 300th day by
playing with my 5 variables. Obviously, there are a lot of calulations
happening on the sheet every time I change a variable (300 rows times 5
columns worth of formulas), but I can do this manually, and get the result
very quickly.
This seems like a perfect problem for Solver, but when I set the balance
cell on the 300th day as my target, and tell it where my 5 variable cells
are, it acts like it solves the problem, but the variables never change,
and
neiher does the answer. I have a less than optimum solution that I
arrived
at by just playing with one variable at a time, but I would really like to
find the optimal solution. Any ideas?