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?

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?

Thanks Mike,

I was thinking about writing a macro in VB that is just 5 nested FOR loops
that run through the ranges of my 5 variables and looks for a maximum at my
target. Will that work? I don't really care how efficiently I get the
answer.

I haven't used VB to write macros, and am unfamiliar with exectly how to do
it. Is there an example VB macro that would be similar enough to my problem
to give me a jump start?

Barry

"Mike Middleton" wrote:

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?

prspilot -

I was thinking about writing a macro in VB ... <

Or, download the trial version of Premium Solver.

I'd opt for the latter.

- Mike


"prspilot" wrote in message
...
Thanks Mike,

I was thinking about writing a macro in VB that is just 5 nested FOR loops
that run through the ranges of my 5 variables and looks for a maximum at
my
target. Will that work? I don't really care how efficiently I get the
answer.

I haven't used VB to write macros, and am unfamiliar with exectly how to
do
it. Is there an example VB macro that would be similar enough to my
problem
to give me a jump start?

Barry

"Mike Middleton" wrote:

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?