Excel 2010 Solver Offers More Power for Optimization, More Help for Users

Share Article

The Solver feature in Excel 2010, now available in retail outlets as part of Microsoft Office 2010, is enhanced with methods from linear programming to genetic algorithms, giving users more power to find optimal solutions.

Excel 2010 Solver dialog

The Solver feature in Excel 2010 is enhanced to solve the full spectrum of optimization problems, from simple linear programming to complex Excel models that require genetic algorithm methods. It's available now in retail outlets as part of Microsoft Office 2010.

Solver is a planning and analysis tool that enables users to find optimal solutions for Excel models that maximize profit, or minimize cost or risk, by automatically adjusting multiple input cells. It is used in a wide range of industries, with a common thread of finding the best way to allocate scarce resources. Examples range from scheduling trucks and blending chemicals to controlling hydroelectric power generators and allocating funds in a stock portfolio.

Solver in Excel 2010, as well as Solver in previous Excel versions dating back 20 years to Excel 3.0, was developed by Frontline Systems Inc. and licensed to Microsoft. Frontline offers more powerful Solvers, for use inside and outside Excel, directly to end users via its Website http://www.solver.com.

Evolutionary Solver Uses Genetic Algorithms

Previous versions of Solver included algorithmic engines – called “solving methods” in Excel 2010 – for linear programming and nonlinear optimization, both with integer variables. Linear programming is sufficient for a wide range of problems – like the scheduling and blending examples cited earlier – but other problems like controlling power generators or allocating funds to stocks typically require nonlinear optimization. Excel users have enjoyed this power for two decades.

But Excel’s rich formula language can express many models that don’t fit the requirements of linear or nonlinear optimization: Mathematically these models use non-smooth or discontinuous functions, in practice they may use built-in functions like IF or LOOKUP, or arbitrary user-written functions. To support these models, Solver in Excel 2010 includes an Evolutionary Solver, which combines methods from genetic algorithms with classical linear and nonlinear optimization methods.

Methods to Find Globally Optimal Solutions

Excel models that don’t fit the requirements of linear (or convex nonlinear) optimization are much harder to solve; classical methods, like the GRG Solver in previous versions of Excel, typically find a locally optimal solution, close to the starting values of the input variables. Solver in Excel 2010 includes two approaches to finding globally optimal solutions: the Evolutionary Solver, and multistart methods that work with the GRG Solver. While there’s no ‘free lunch’ since these methods cannot mathematically prove global optimality, they often find better solutions than the methods in previous versions of Excel.

Extensive Help for Solver Users on Solver.com

As one of many features in Microsoft Excel, Solver has had only limited online Help in previous Excel versions. In Excel 2010, online Help for Solver links directly to a special section of Frontline Systems' website Solver.com, where hundreds of pages of Excel Solver Help and background on linear programming, nonlinear optimization, genetic algorithms and other topics is available.

Frontline Systems, Inc. (http://www.solver.com) is a leading developer of optimization and simulation software, and the leader in spreadsheet optimization software that helps analysts and managers optimally allocate scarce resources – money, equipment, and people – to realize substantial cost savings. Frontline developed the solvers/optimizers in Microsoft Excel, Lotus 1-2-3 and Quattro Pro, distributed to more than 500 million spreadsheet users. Founded in 1987, Frontline is based in Incline Village, Nevada.

# # #

Share article on social media or email:

View article via:

Pdf Print

Contact Author

Daniel Fylstra
Visit website