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 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?
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.
But instead, you could open the Goal-Seek function by clicking on “What-If Analysis” in the Data ribbon, and then on “Goal Seek…”.
“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”.
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:
- Cell C5 (the one which is being changed) can’t contain formulas but only values.
- The input field must have an effect on the result. So if you change the input value, also the result should change.
- If you want to simulate more than one input value, you have to use the Solver add-in, which is provided by Microsoft (maybe you still have to enable it within the Options –> Add-Ins).
- The result and the input cell can be located on different worksheets.