Excel 2019 Bible

733

C H A P T E R

32

Analyzing Data Using Goal

Seeking and Solver

IN THIS CHAPTER

Performing what-if analysis—in reverse

Single-cell goal seeking

Introducing Solver

Looking at Solver examples

T

he 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 oppo-

site perspective: finding the value of one or more input cells that produce a desired result in a

formula cell.

Exploring What-If Analysis, in Reverse

Consider the following what-if question: “What is the total profit if sales increase by 20 percent?”

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 to accomplish

this:

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 additional constraints to the problem, you gain

significant problem-solving ability.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.