Site icon Professor Excel

Goal-Seek in Excel: Easily Achieve Your Desired Result!

goal, seek, excel

You’ve done some calculations in Excel but you aren’t satisfied with the result? So you ask yourself, how you could achieve the desired result. A good starting point for such analysis is the built-in Goal-Seek function in Excel. It’s easy to use but yet powerful. On the other side, it does some reverse calculation and therefore has a reputation of a cheating functionality in order to achieve the target results.

What does the Goal-Seek function do?

The Goal-Seek function changes one input factor until you get your desired results.

The Goal-Seek function changes one input value so that you get your desired result. That’s it. Sounds simple, right? It actually is that easy. 

Let’s say you did some complex calculations. The only problem: The result you expected is far off. So what do you do? You usually would change one input (or more) input variables and see what happens to the result. Excel can do that for you: you just tell Excel what your result should be and which input variable you want to change. Then Excel changes this input variable so that you get the result you want.

How to use the Goal-Seek function?

Example: You want to know the necessary about of product C in order to get a revenue of 160 EUR in total.

Let’s do some simple revenue calculations as an example. You got three products (A, B, C). You know the quantities of product A and B, and the prices for all products A, B and C. Calculating the revenue is easy: Amount x Price = Revenue. But you want to know, what quantity of product C do we have to sell in order to generate our desired revenue.

The desired revenue is 160 EUR. So how do you start? You could just guess any amount in cell C5. For example typing 10 would result in a value too high, 8 would be too low.

Open the Goal-Seek function by clicking on What-If-Analysis and then on Goal Seek…

But instead, you could open the Goal-Seek function by clicking on “What-If Analysis” in the Data ribbon, and then on “Goal Seek…”.

Define the result cell and provide your desired value as well as the input cell you want to change.

“Set cell” is the result cell which you want to have a certain value. In this case it’s cell E6. As the desired result of cell E6 is 160, you type 160 into the “To value” field. As the last step you define, which input cell you want to change. In this case it’s cell C5. So just write “C5” (with or without the $-sign doesn’t matter in this case) into the fiel “By changing cell”. 

The result: 8.14.

After pressing “OK”, Excel will conduct some calculations and cell C5 should now show the value 8.14.

Please note…

Please note the following comments:

Exit mobile version