P
prspilot
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?
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?