I have an optimization problem that I try to tackle with solver. Among others, one decision variable is a cell containing a date. Based on a time series, Solver should pick the optimum point in time. However, while it changes the other decision cells, it never changes this one! I even tried dropping the other decision variables (holding them constant) - still no change.
To help you have a lock into my problem, I created and attach an Excel sheet (here: download link) with a simplified problem, where from a time series of stock prices Solver should pick the date with the highest price. (I am aware that this particular problem could easily be solved with the MAX formula, but my real optimization problem is much more complex and wouldn't allow for this solution.)
Solver still doesn't change anything at the initial date value - even in this very simplified setting!
From a mathematical point of view, the present problem could be characterized as a (non-linear) step function, where particular points in time (on the x axis) correspond to different prices (steps, on the y axis).
Is there any tricks involved to get solver to handle time (date) values? If you have luck to find the right set of Solver settings to make it get to the correct solution (which would be Jan 10, 2009), I'll be more than happy to hear about them.
Edit: I found out that non-linear problems with non-smooth discontinuous functions (like this problem, which is basically a staircase function) are generally difficult to tackle by any (non-)linear optimizer. Can someone savvy in the matter help me reformulate the problem in a solvable way. I found a similar topic on stack overflow with a suggested solution (see first answer), but don't know how to apply it to my problem.
Thanks, Steve
