Goal seek excel Function – A Step By Step Tutorial

Microsoft Excel is one of the most widely used applications for data analysis.

Professionals from financial industry to information technology and researchers to academics make extensive use of Excel on a day to day basis.

There is no denying the fact that the range of features that Microsoft Excel offers is truly amazing.

This is the very basis of the learning curve that is commonly associated with the program.

With the widest range of features, it only makes sense for a potential user to take some time to master the art of using Microsoft Excel.

However, there are a few things such as goal seek excel which may not be known for an average user even after going through the learning curve.

For instance, it is a common understanding that Excel can be used for data analysis to get to the output that is derived from the input values being entered. But how many of you actually know that it can work the other way around as well?

Yes, it is possible with the use of Microsoft Excel to predict the input value from the output value if the part of the input is known.

Picture this, you work for a T-shirt manufacturer. Each of your items is priced at $5. You are entrusted with the task to make a calculation of how many pieces are to be sold before the total revenue for the company will become $1 million. Since you know the output ($1 million) and part of the input ($5 per piece), it is possible for Microsoft Excel to automatically calculate the number of pieces (remaining part of the input) to be sold.

If this sounds like an interesting idea, you are going to love this tutorial on excel goal seek.

What Is The Excel goal seek Function?

The goal seek excel function is also known as the What-If analysis that is particularly valuable for the financial industry that requires complicated sensitivity analysis to be performed.

In the simplest of terms, goal seek in excel is a function that uses trial and error approach to predict the input value from the conjunction of the output and the rest of the input values.

It is synonymous to back-solving a given equation.

For instance, suppose that you have a list of five students and what they scored in your subject on Microsoft Excel.

The data available at your disposal is that of the four students (part of the input values) as well as the average score of the five students in total (output value). From this data, you can use Excel to automatically calculate the score of the fifth student that will satisfy the average score.

How to use goal seek in excel?

Now that you have an understanding of what the goal seek feature in Excel is, it is time that we dive into what you came here for.

The good news is that the answer to how to use goal seek in excel isn’t a complicated one.

You can follow the steps given below in order to perform the What-If Analysis on the given data. For the sake of this tutorial, we are going to use the example stated above.

1. Launch Microsoft Excel and click on the “Create New Sheet” button.
2. Click on cell A1 of column A. Double click to enter the editing mode. Type “Student Names” in this cell to highlight what the entries in this column represent.
3. Now double click on cell B1 of column B to enable editing. Write “Score” in this cell to highlight what the entries in this column represent.
4. In the next five cells of each column, type in the student names and their individual scores respectively.
5. Since we are supposing that you don’t know what the fifth student scored in your subject, cell B6 will remain empty. Please take a look at the picture below for better understanding.

1. Now edit the cell A8 and write “Average Class Score”. In the corresponding cell B8, type in the AVERAGE formula as =AVERAGE(B2:B6). This will calculate the average of all values from cell B2 to B6.

1. Click on the cell B6 that is currently empty since you don’t know what Erica scored in your subject.
2. Look at the menu bar of Excel right below the title bar. Among the multiple tabs, locate the one that reads “Data”. Click on this tab.
3. Within the data tab, towards the right corner of the screen, you will find the “Forecast” group that highlights two options to choose from namely What-If Analysis and Forecast Sheet.

1. As evident, click on the What-If Analysis button. From the new pop-up window, click on the “Goal Seek” option.
2. A new window will appear. In the “Set Cell” option, choose cell B8. In the next option that reads “To Value”, type “70” which is the average class score considered in this example. In the box “By Changing Cell”, selected B6; the cell whose value is to be predicted.

1. Click “Ok” and you’re done. You will now be able to see the marks that Erica must have scored in your subject for the class average score to be 70.

This is all you have to do to use the excel goal seek feature and predict one of your input values by using the output and the rest of the input values.

The Precision Of Goal seek excel

Since a trial and error approach is being used by Microsoft Excel to execute the goal seek function, it usually displays the approximate solution to your equation.

The good news, however, is that it is well within your authority to improve the precision of calculation. You can use the following steps to ensure maximum precision for calculations entailing excel goal seek function.

1. Locate the “File” tab at the top left corner of the screen. Click on it.
2. Under the file tab, look for the “Options” tab.
3. Click on the “Options” tab to get a new window.
4. On the left-side panel of this new window, you will find multiple options. Click on the “Formulas” tab from these options.
5. Towards the right, you will find a box that is labeled “Maximum Change”. It’ll have an editable value such as “0.001” written in front of it.

1. In order to improve the precision of goal seek excel, all you have to do is to type additional zeros between the decimal point and the number 1. For instance, you can change this value to “0.0000000001”.
2. Once done, click on “Ok”. You have now increased the precision of excel goal seek function by multiple folds.

Goal seek in excel – Limitations

Much like any other function in Microsoft Excel, there are a few limitations to using Goal seek in excel as well.

For instance, the function is only applicable when you can find your answer by changing a single variable only.

In our example, we can change the average class score only to predict what the fifth student must have scored in the subject.

If more than one variables are to be changed, the goal seek function is no longer applicable.

Furthermore, excel goal seek doesn’t work directly with the numbers. It is a requirement that the output cell must be a formula.

If you take a look at our example again, the output cell B8 used the “Average” formula to calculate the class average score.

If you would have wanted to type in the number 70 directly in cell B8 and use goal seek feature to predict the marks obtained by the fifth student to make the overall average be 70, you would have ended up with an error.

The rest of the input values, however, must be typed and cannot contain a formula. Lastly, if you are dealing with percentages, it is important to enter the percentage sign (%) in the option “To Value”, otherwise you will not be able to get the correct answer.

Conclusion

In light of the information mentioned above, it is fair to conclude that excel goal seek is a very valuable feature for analysis of various forms of data. We have elaborated everything that you need to know about the goal seek excel function.

Now it’s your turn to practice it as much as you can with multiple sets of data in order to understand it fully.

So, what are you waiting for? Open your dataset in excel and start practicing.

Feel free to leave a comment in case of any further queries about goal seek in excel. We are constantly reading through the feedback of our valuable audience and our experts will surely get back to you to answer your query as soon as possible.