Excel Scientific and Engineering Cookbook

Chapter 13. Performing Optimization Analyses in Excel

Introduction

Optimization problems come up frequently. A businessman may try to optimize profit given production performance from several different production facilities. An engineering manager may try to optimize his labor force to meet forecast demands. Engineers often try to optimize design problems to produce optimum performing designs at minimum cost or perhaps minimum weight. The variety of applications is incredible.

By now you’re familiar with Solver; we’ve used it for least-squares curve fitting and for solving nonlinear equations and systems of equations. In reality, Solver is an optimization tool that has been very nicely integrated with Excel. You can use Solver to handle many different types of optimization problems directly within Excel. You can use Solver for linear programming types of problems and for nonlinear optimization problems (sometimes called dynamic programming ). I use Solver very often for engineering design optimization and some business optimization problems. While Solver is very capable, it isn’t your only option, and sometimes it’s not necessarily the best option.

In this chapter, I will show you how to leverage Excel’s built-in features, like Solver, to solve several typical types of optimization problems. Further, I will show you how you can use Excel and VBA to program a nontraditional algorithm (a genetic algorithm) to solve optimization problems.

13.1. Using Excel for Traditional Linear Programming ...