Goal Seek is a built-in tool in Excel that allows us to determine the value of one variable in our formula to obtain the desired result. It shows, therefore, how one value affects another value in our worksheet, provided that the two values are connected through a formula.
Figure 1. Final result: How to use Goal Seek
The Goal Seek tool is very useful in various fields such as finance, business and even in chemistry and physics applications.
Where is Goal Seek?
Goal Seek can be accessed through the Data tab > What-If-Anaysis button > Goal Seek
Figure 2. Goal Seek in Data tab
Prepare data for Goal Seek
Suppose we have below data for preparation of a mixture in a laboratory. The concentration of the mixture is given by the formula
=(C4*D4)/C6
which can be interpreted as the volume of the pure chemical divided by the volume of the whole mixture.
Figure 3. Sample data for Goal Seek
Our goal is to obtain a mixture concentration of 3% by determining the required volume of the chemical, with the volume water remaining constant. We have to remember our Goal and what we Seek.
Goal : 3% mixture concentration in cell D6
Seek: volume of chemical in cell C4
How to use Goal Seek?
We need not be intimidated by Goal Seek. It actually requires only three information for it to perform the calculations. Follow this step-by-step procedure to learn how to use Goal Seek:
- Click Data tab > What-If-Analysis button > Goal Seek
The Goal Seek dialog box will appear.
Figure 4. Goal Seek dialog box
- As discussed above, we need to remember our Goal and what we Seek. The three information needed by Goal Seek are:
- Set cell – the cell containing our goal, which is cell D6
- To value – the goal, which is 3%
- By changing cell – this is what we seek, the required volume of chemical which is cell C4
- Enter the information into the Goal Seek textboxes as shown below and click OK.
Figure 5. Entering the values
Within a few seconds, Goal Seek will be done with the calculations and the values in our worksheet will be changed accordingly.
Excel will show a prompt message of the Goal Seek Status saying something like:
Goal Seeking with Cell D6 found a solution.
Target Value: 0.03
Current Value: 2.99%
Figure 6. Output: Goal Seek Analysis
Note, however, that Goal Seek might return a value that is not exactly equal to our target value. In this case, the current value is only 2.99% versus 3%. This low precision can be corrected by changing the settings for Formulas in Excel Options.
Increase precision for Goal Seek Analysis
- Click File > Options
- In Excel Options dialog box, select Formulas and we will see a textbox for Maximum Change with a default value of 0.001.
Figure 7. Maximum Change textbox in Excel Options
- Change to a smaller value, such as 0.0000001 and click OK
We can perform the Goal Seek calculation again and see the improvement in the results.
Figure 8. Output: Goal Seek Analysis with increased precision
By changing the Maximum Change value, we have significantly improved the precision of our Goal Seek calculation.
Instant Connection to an Excel Exper
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment