For complicated excel formulas there are often “What-If” scenarios. Questions such as “How many do we have to sell to make $1,000,000” and “How long until we run out of inventory”. Excel has a helpful built in tool that lets you define what you want the outcome of a formula to be. By showing excel which variable to change in order to get the desired formula result many questions can be answered.
The “What-If” scenarios can be found on the Data tab of the ribbon. Select “Goal Seek” from the drop down list.
Inputting the information is fairly straight forward. Select the cell that contains a formula you would like to solve for. In the below example cell D9 is the total we want to make equal $10,000. Then you MUST select a changing cell that effects the formula.
Excel will then run through an iteration process and slightly alter the changing cell value a series of times until the result is reached. If the selected cell does not effect the result the process will stop after 100 iterations and you will receive an a message that “Goal Seeking may not have found a solution”. Clicking OK will revert the changing cell back to it’s original value
If you know ahead of time that you want to run what-if scenarios it may alter that way you build the spreadsheet. The below example shows a handful of products and a sales total. For the individual sales quantity we assume that sales of some of the items are relative to one main item. Take for example sales of razor blades and handles (for every $10.99 pack of blades we assume .5 handles at $9.99 are sold).
Use Ctrl+` to view the actual formulas in each cell. Goal seek can solve lots of small issues very quickly. It can also be combined with Macros (it is very easy to record a goal seek rule) to automate long trial and error scenarios.
Goal Seek in Excel for Mac
Things are usually slightly different in a Mac version of Excel but Goal Seek is nearly the same. The only complication is where to find the “What-If” scenario manager from the Mac ribbon.
 |
| Data Tab – Under the “What-If” drop down menu |
Everything else about setting up a goal seek is the same. Input the Cell to solve for, the value, and the variable cell (“By Changing cell”) and let the program do all the work.
 |
| Goal Seek Input in Excel for a Mac |