Search code examples
vbaexceloptimizationsolver

Excel Solver: Save Candidate Solution and Corresponding Values of Objective Cell for each Iteration

I'm trying to maximize an objective cell in Excel using the "Evolutionary" method, as it's most appropriate for my particular problem. There's just one variable I'm changing and just one objective, with the only constraint being that the variable that changes be an integer.

Solver already finds that the objective is maximized with a value of the variable being 30, and I have confirmed this myself manually. I just want a table that spits out the values of the objective cell at each of the candidates Solver attempts in each iteration (i.e. I want to essentially plot the value of the objective cell as a function of the variable that Solver is changing, showing the local (presumably, global) maximum at the solution that both I reached manually and Solver found).

I've seen suggestions re: saving each Solver iteration as a scenario and then using a PivotTable report from Scenario Manager (as in Saving the objective function values while using Solver, EXCEL), but I'm trying to show as many candidate values (ideally, hundreds) so it is manually intensive to save each scenario and, most critically, when I try even saving just a handful of the iterations, the table I get shows the final value of the objective cell (already maximized) for each candidate (as opposed to the intermediate value, for the candidates of the variable that were not the solution). I know for a fact that Excel is indeed getting values of the objective cell not all equal to the final value (and I of course confirm this manually), so I know this is wrong.

Any idea on how to do this, or what I may be screwing up? It's really not a complicated task, but right now it seems the only alternative that would work is manually changing the reference cell myself, and manually writing down the value of the objective cell at each candidate, manually. Open to any guidance with VBA or otherwise. Thanks!

Solution

  • A VBA and formula-based solution that did not use Solver (and as such may be more general) and that I found helpful: http://www.exceluser.com/excel_help/questions/timetrack.htm.