CODEWITHSUNDEEP
optimizationvbaexcelsolver
pogo
pogo

Reputation: 1550

Saving the objective function values while using Solver, EXCEL

I have an optimization problem set up in Excel. When I optimize (using the gui), I get the final (optimum) value of the objective function. I can see that excel calculates the objective function values for a lot of points and settles on the optimum one. Is there a way to save all the objective function values to some range in the excel sheet. It would be great if I can do it using the solver GUI. If not, a VBA based solution would do too.

Thanks.

Upvotes: 3

Views: 3063

Answers (1)

Ram Narasimhan
Ram Narasimhan

Reputation: 22496

This is possible to do, but is manually tedious. When in Excel/Solver, select Options in the Solver Parameters Dialog. (Data->Solver->Options in Excel) Select the Show Iterations Results check box. When you do this, Excel's Solver will pause after each iteration, and update the cells with the current variable values.

Now, when you run the model by clicking "Solve," Excel will pause at each intermediate iteration. Solver considers each intermediate step to be a "Scenario." You can save each one by giving it a name in the Scenario dialog box that pops up after each iteration. (In the text box, enter something logical, like i1,i2, i3...)

Once the Solver is done, you can access the 'Scenario Manager" Excel->Options->Scenarios. You will see all the scenarios that you saved listed there. To get them all into a named range like you desire, press the "Summary" button, and in the dialog box choose "Scenario Pivot Table Report." This will display all the intermediate Objective function values in a nice Pivot Table for you to analyze.

A couple of limitations to keep in mind: 1. Enabling the "Show Iteration Results" option severely slows down the solution 2. If your LP is even mid-sized, you will have a huge number of iterations. Manually recording the scenarios will not be realistically feasible. However, you can "sample" a few iterations by pressing the ESC button, storing the Scenario and continuing.

Upvotes: 1

Related Questions