Exploring optimization problems in Excel

Excel is able to solve optimization problems. Two commonly available tools are the build-in Solver tool and the Excel plugin for Microsoft Solver Foundation (MSF). The former is not installed by default but can be easily enabled through the Excel Options menu. The latter is a separate download available from Microsoft.

For a simple comparison of the performance of the two, the non-linear data fitting example from the MSF is used as benchmark.
msf-1

MSF provided additional menu pane within Excel for complex optimization operations.
msf-2

Optimization results and log of this benchmark run of a non-linear data fitting sample from the MSF, based on an NIST sample.
msf-10
msf-3

Goals setting screen.
msf-5

Model Display.
msf-6

On the other hand, the built-in Solver offered a simpler interface but still provide detailed reports, including answer, sensitivity, and limits reports in separate spreadsheets.
msf-7

msf-8

The built-in Excel Solver offered easy to use interface, while the Microsoft Solver Foundation is more capable for complex problems and modelling.

The NelderMead solver is selected in this benchmark by the MSF. Check out this previous installment for details of running Nelder-Mead on TI Nspire. The same data set is performed on the Nspire using Nelder-Mead to obtain the following results.
msf-9
msf-11

Advertisements

One thought on “Exploring optimization problems in Excel

  1. Pingback: Microsoft Solver Foundation for Excel | gmgolem

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s