How to use Goal Seek in Excel sheet
The Goal Seek function in Excel allows you to simplify your work with data and save valuable time. ONLYOFFICE Docs 8.0 has introduced the Goal Seek feature in spreadsheets. In this article you will find out what it is and how to use it. Happy reading!
What is Goal Seek in Excel?
Goal Seek in spreadsheets is a What-If Analysis type function that allows you to trace the formula needed to obtain a given result. This function works backwards, and is therefore useful when you already know the data you need but do not know how to get there. It can be a valuable ally in your business as a financial tool, but also in family budget planning and for everyday use. Here are some situations in which you could use Goal Seek:
- Loan Repayment Calculation: You have a loan with a fixed interest rate and fixed monthly payments. You can use Goal Seek to determine the initial loan amount needed to achieve a specific monthly payment.
- Investment Planning: You have a target amount for retirement savings and know the expected return on investment, so you use Goal Seek to determine the required monthly contribution to meet your goal.
- Budgeting: You have a fixed budget and want to allocate funds to different categories so you use Goal Seek to determine the optimal allocation that maximizes the overall budget utilization.
- Analysis: In a business context, you want to determine the number of units you need to sell to break even. You use Goal Seek to find the sales volume needed to achieve a certain profit.
How to use Goal Seek in Excel?
To understand how the tool works, let’s see it in action as we try to solve a simple problem. To do this we will make use of ONLYOFFICE spreadsheets.
The problem is the following:
In order to be eligible for my final exam, I need to achieve an average grade of 70 or higher by summing the grades of the exams I passed. So far I have taken 3 out of 4 exams. What grade should I get on the last exam to achieve an average grade of 70?
1. Open your file and insert the data that you need.
2. Apply the proper formula in the cell where you want your new result to be displayed. In our case, we applied the formula =AVERAGE(B2:B5) in B7.
3. Open the Data tab and click the Goal Seek icon on the top toolbar. Then, insert the data you need.
- In Set cell enter the reference to the cell that contains a formula. You can even use the icon to select the cell.
- In To value enter the result you want to get in the cell containing the formula.
- In By changing cell enter the reference to the cell that contains the value you want to change. You can use the icon to select the cell. This cell must be referenced by the formula located in the cell which you specified in the Set cell field.
4. Click OK. The Goal Seek Status window will display the result.
5. Click OK to replace the values in the cells specified in the Set cell and By changing cell fields.
And here we are! Now we know what grade we need to score on the last exam to reach an average of 70, which is 85.
Now that you understand where to find Goal Seek in Excel and ONLYOFFICE spreadsheets, you can start using it to solve numerous tasks, from the simplest problems of everyday life to the most complex calculations.
In ONLYOFFICE Docs 8.0 you will also find countless functions and new features that have been added with the release of the latest version. If you don’t have an account yet, you can create one right now and test all the capabilities of ONLYOFFICE editors.
