I am trying to use an Excel spreadsheet to optimize an implicit variable. Since the variable is implicit, I use the Solver add-in to solve for it once I input all of my data. But, since I can't write an explicit function for the variable, I can't use Solver to maximize or minimize it. I could optimize it by hand using sensitivity analysis and all of that, but I really don't want to spend the time!
Does anyone have any suggestions? I am currently trying to use a circular reference to iteratively solve for the variable, and then use Solver to maximize the final result, but I am not sure if it will work properly.
With regards to using circular references to solve for an implicit variable, are there any good/standard ways to do this? The way I am using works but it is kind of clunky (I basically use an if/then statement based on the relative error between two values in the spreadsheet, and either add or subtract 0.001 to the variable accordingly until the error goes to 1e-6 or less.).
Bookmarks