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

Excel 2020: Back into an Answer by Using Goal Seek

October 29, 2020 - by

Back into an Answer by Using Goal Seek. Photo Credit: Jason Wong at Unsplash.com

Do you remember from “Discover New Functions by Using fx?

If you are about the same age as me and spent your summers watching TV, you might remember a crazy game show called The Price Is Right. Long before Drew Carey, the venerable Bob Barker would give away prizes using a variety of games. One that I recall is the Hi Lo game. Bob would give you the car if you could state the price of the car. You would guess. Bob would shout “Higher” or “Lower.” I think you had 20 seconds to narrow your guesses to the exact price.

A lot of times, I feel like those summers watching Bob Barker trained me to find answers in Excel. Have you ever found yourself plugging in successively higher and lower values into an input cell, hoping to arrive at a certain answer?

You are trying to guess the correct car price to lead to a $425 monthly car payment. Keep plugging in higher/lower values until arrive at $425.

A cartoon game show host manages to say "higher", "lower". At the same time, a cartoon spreadsheet shouts "higher", "lower" 8 times.
Illustration: Chad Thomas

A tool that is built in to Excel does exactly this set of steps. Select the cell with the Payment formula. On the Data tab, in the Data Tools group, look for the What-If Analysis dropdown and choose Goal Seek….

A simple model calculates a loan payment. Choose the payment cell.
Under What-If Analysis, choose Goal Seek.

The figure below shows how you can try to set the payment in B5 to $425 by changing cell B1

In the Goal Seek dialog, Set Cell B5 to $425 by changing cell B1.

Goal Seek finds the correct answer within a second.

Excel quickly finds that you can get to a $425 car payment if you can get to a $22,384.9 car price.

Note that the formula in B5 stays intact. The only thing that changes is the input value typed in B1.

Also, with Goal Seek, you are free to experiment with changing other input cells. You can still get the $425 loan payment and the $25,995 car if your banker will offer you a 71.3379-month loan!

You can Goal Seek based on other cells. Set the Payment to $425 by changing the term and you need a 71.3379 month loan.

Thanks to Jon Wittwer of Vertex42.com and to @BizNetSoftware for suggesting this Goal Seek trick.

Title Photo: Jason Wong at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.