While doing many mathematical calculations, you might have come across the problems where you have the result but do not have any input value. If you need to solve a single problem then there is no fuss. But think if you have n number of sets with an outcome and you do not have input values then what will you do? Do not worry as we have a solution that is goal seek excel function that lets you perform what-if analysis data table where you need to put values.
![]() Goal Seek Excel
Suggested Read: Excel functions list
Use Goal Seek to determine the interest rate. Do one of the following: In Excel 2016 for Mac: On the Data tab, click What-If Analysis, and then click Goal Seek. In Excel for Mac 2011: On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek. In Excel for Mac and Excel 2003 choose Tools Goal Seek Click the Set cell box and select cell B2, which has the formula =A2-DATE(YEAR(A2),1,.
The Goal Seek in Excel will let you achieve value by altering the dependent value to achieve the goal. This is the simple method to get the value as a result. For example, you want to calculate the base price after deducting the taxes and other such charges from the total amount.
How to use Excel Goal Seek function?
Also Read: Excel Replace
![]()
This is just an example of a single complex problem but think, if you have n number of problems and you want to solve them then it becomes difficult for you to solve the problem. For this, you need a Goal Seek excel function.
Points to remember about Goal Seek Excel function
Now to perform back-calculation, you do not need to worry about the formula and what function to use. By just using goal seek analysis and with a single click, you can solve complex calculations within a second. Follow the step by step process of using goal seek excel function to get the input values.
The Goal Seek feature in Excel 2010 is a what-if analysis tool that enables you to find the input values needed to achieve a goal or objective. To use Goal Seek, you select the cell containing the formula that will return the result you’re seeking and then indicate the target value you want the formula to return and the location of the input value that Excel can change to reach the target.
The steps below follow a specific example for using Goal Seek to help you better understand how to use this feature. Refer to the figures for guidance. To use Goal Seek to find out how much sales must increase to return a net income of $300,000 in the first quarter, follow these steps:
1Select the cell containing the formula that will return the result you're seeking; in this example, cell C7.
This cell contains the formula that calculates the forecast for the first quarter of 2011.
2On the Data tab, choose What-If Analysis→Goal Seek in the Data Tools group.
This action opens the Goal Seek dialog box. Because cell C7 is the active cell when you open this dialog box, the Set Cell text box already contains the cell reference C7.
![]() 3Select the To Value text box and enter the goal.
This is the value you want the formula in the Set Cell box to reach. In this example, it’s 300000.
4Select the By Changing Cell text box and select the cell that you want to change.
Excel will change the value in this cell reference to try to reach the goal in the To Value box. In this example, cell C3 contains the first quarter sales. The absolute cell address, $C$3, appears in the text box.
5Click OK.
Excel displays the Goal Seek Status dialog box along with the results. In this example, Excel increases the sales in cell C3 from $250,000 to $545,455, which, in turn, returns $300,000 as the income in cell C7.
6If you want to keep the values entered in the worksheet as a result of goal seeking, click OK.
If you want to return to the original values, click the Cancel button instead. Notice that because all of the values in this table are formulas that ultimately are derived from the value in cell C3, all of the values changed when that cell value was updated during the Goal Seek process.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |