How To Use Goal Seek In Excel For Mac
Goal Seek Macro with Goal as a Formula. Ask Question. I'm trying to solve a circular reference problem in Excel by creating a goal seek macro - basically. S3 = M + S2, S3 = L. G. I want to goal seek S3 to. (the goal seek cell) contains '=F18+G18' and the goal cell (H32) contains another formula. Is it possible to use a cell reference. In the Customize Toolbars & Menus dialog, open the Tools menu on the Worksheet Menu Bar, then hold the Option key while you drag the Goal Seek command to copy it to your toolbar. It will appear as the text Goal Seek, but if you add it to a floating toolbar you can right-click it, select Properties then choose a button icon for it.
Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you.
I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the Year. This will tell me that Valentine’s Day — 14 Feb — is the 45th day of the year, or my birthday is day 311 this year.
The quickest way to find out what Date the 250th day of the year falls on is to use Goal Seek. For this example, here are the steps to take.
- Choose Data > What-If-Analysis > 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,0)
- Click the To value box and type in 250, which is the outcome I want
- Click the By changing cell box and select cell A2, the value that Goal Seek will change
- Click OK
Goal Seek searches for a solution and displays the following results.
Click OK to accept the changes, or click Cancel to restore the original value and leave the spreadsheet unchanged.
Note: Goal Seek works only with one variable input value.
Check out Goal Seek next time you know what a formula should be, but don’t have the time, inclination or smarts to figure it out.
Related posts:
Bamboo paper tutorial. Bamboo Paper is a great because it integrates with your work flow and provides you with the capabilities of sharing your work with others. The ability to be able to share your work across other platforms and syncing your work makes it possible to work from almost anywhere.
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.