MrExcel.com - Your One Stop for Excel Tips & Solutions

Back in to an Answer Using Goal Seek

March 08, 2018 - by

Back in to an Answer Using Goal Seek

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 =PMT(B3/12,B4,-B2).

The payment is too high
The payment is too high

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.

Keep trying higher or lower numbers
Keep trying higher or lower numbers

The answer is to use Goal Seek. Choose the answer cell in B7. Look for this drop down on the Data tab.

Powerful feature buried on the data tab.
Powerful feature buried 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.

Set up the goal seek
Set up the goal seek

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.

Good luck finding a bank who will pay you to take their money
Good luck finding a bank who will pay you to take their money

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.

Note

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.

Reverse icon order
Reverse 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:

This spreadsheet could win the car on The Price is Right every time.
This spreadsheet could win the car on The Price is Right every time.

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