O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Solver

Goal seeking works well for simple problems. But it does have a few limitations:

  • Excel doesn't recognize what you may think of as common-sense limits in your data. In the earlier student grade example, if you were looking to figure out the assignment grade required to get a final grade of 100, Excel would tell you you'd need a score of 113—even though a grade over 100 percent clearly isn't possible.

  • Excel adjusts only one cell. There's no way to ask the goal-seeking tool, for instance, to predict the minimum grade combination you'd need on the two tests to get a particular final grade.

Solver is an Excel feature that goes several steps further than goal seeking. It uses the same basic trial-and-error approach (known to scientific types as an iterative approach), but it's dramatically more intelligent than goal seeking. In fact, it needs to be much more intelligent because it tackles much more complicated problems, including scenarios that have multiple changing cells, additional rules, and subtle relationships. Solver can (with a fair bit of work) suggest an optimal investment portfolio based on a desired rate of return and risk threshold. Incidentally, Solver is an add-in that you have to turn on the first time you use it; Defining a Problem in Solver tells you how.

Note

Although it comes as part of the Excel package, the Solver add-in isn't a Microsoft product. Instead, it's provided by a company called Frontline Systems, which sells more enhanced versions of the Solver tool. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required