Chapter 36Analyzing Data Using Goal Seeking and Solver

IN THIS CHAPTER

  1. Performing what-if analysis — in reverse
  2. Single-cell goal seeking
  3. Introducing Solver
  4. Looking at Solver examples

The preceding chapter discussed what-if analysis — the process of changing input cells to observe the results on other dependent cells. This chapter looks at that process from the opposite perspective: finding the value of one or more input cells that produce a desired result in a formula cell.

What-If Analysis, in Reverse

Consider the following what-if question: “What is the total profit if sales increase by 20%?” If you set up your worksheet model properly, you can change the value in one or more cells to see what happens to the profit cell. The examples in this chapter take the opposite approach. If you know what a formula result should be, Excel can tell you the values that you need to enter in one or more input cells to produce that result. In other words, you can ask a question such as “How much do sales need to increase to produce a profit of $1.2 million?” Excel provides two tools that are relevant:

  • Goal Seek: Determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell.
  • Solver: Determines the values that you need to enter in multiple input cells to produce a result that you want. Moreover, because you can specify certain constraints to the problem, you gain significant problem-solving ability.

Single-Cell Goal ...

Get Excel 2016 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.