Back in to an Answer Using Goal Seek
March 08, 2018 - by Bill Jelen
Buried under the What-If Analysis drop down on the Data tab, the Goal Seek function will allow you to back in to an answer quickly and without having to play the Higher!/Lower! Game.
Say that you are trying to buy a car. Your budget is $425 a month. The bank offers you 5.25% for a 60-month loan. The price of the car is $25,995. Set up the model shown here using
The initial payment comes out to $493.54 instead of $425. One approach at this point is to start plugging in higher or lower values. This approach reminds me of Bob Barker, the TV host of The Price is Right trying to guide a person to winning a car.
The answer is to use Goal Seek. Choose the answer cell in B7. Look for this drop down on the Data tab.
In the Goal Seek dialog, you specify that you want to set one cell equal to some particular value by changing another cell. Note that this only works if the target cell has a formula which includes the source cell in its formula dependency tree.
Remember that there are multiple ways to solve this problem. In the image below, I've done three different goal seek commands, one changing the price, one changing the rate, one changing the term.
The benefits of Goal Seek: It is fast. It is accurate. While my original model in the second figure suggested $22,385, the Goal Seek model came up with 22384.934249687 - that's 14 digits of precision.
Here is a look "behind the scenes" for creating Figure 2.
The green & red triangles in row 2 are designed to indicate whether the next guess should be higher or lower. I set those up after I knew the answer by using an icon set and reversing the icon order.
I also ended up building a model to offer the best next guess in response to the series of higher/lower results. Here is a look at the fomulas:
Thoracic Thursday - my favorite heart-pounding features in Excel.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Never trust a pretty model"
Title Photo: Asnim Asnim / Unsplash