Ever struggled to “guesstimate” a figure when working in Google Sheets? It’s often near impossible to figure out how many of X to make Y without manually rearranging a formula and working it out.
Thankfully, the Google Sheets Goal Seek add-on makes it much easier. Read on to find out more about how this great little tool can help you.
What is Goal Seek?
Google Sheets Goal Seek is a handy and powerful technique for data analysis. It works by setting an output that you wish to achieve, and the program uses a calculation to find the value of the input you need to reach the target value.
It essentially works by performing several “what if” calculations by guessing the input values. Every iteration gets the algorithm closer to the intended target value.
There are three components in the Google Sheets Goal Seek Script. These are:
- The unknown input
- The equation (used to find the unknown input value)
- The known output
A classic example of Goal Seek is calculating the number of sales needed to break even for a business by using other variables such as fixed costs. Goal Seek can be a great accompaniment to a pivot table.
Add Goal Seek in Google Sheets
Goal Seek isn’t integrated into Google Sheets, which means you have to add the Goal Seek add-on to your account.
You can find it by searching for “Goal Seek” in the Google Add-Ons marketplace. Or, you can use this link to go directly to the Goal Seek page.
Once there, just click on install to add the plugin to your Google Sheets. You will see a prompt to link your Google account to Goal Seek. Once done, refresh your Google Sheets, and you will be able to find Goal Seek in the Extensions tab.
How to Use Google Sheets Goal Seek
To help make the learning process more accessible, we will use an example. In this example we want to sell a specific number of items to generate a profit of $20,000. We will have several elements and a formula in the “Profit” cell used to calculate the profit.
However, to use Goal Seek, first, we need to set up our sheet.
Step 1 – Enter Your Data
Open a spreadsheet that you need to use the goal seek formula Google Sheets add-on for. You don’t have to worry about the formulas straight away, but you should make sure your data is clearly labelled.
In this example, we add values for:
- Fixed Costs
- Variable Cost
- Selling Price
- Number of Items
- Profit
Note that the Number of Items is the value we need to find out, and the Profit is the limit we will fix in the following steps.
Step 2 – Add Formulas
We need to calculate some values using other values mathematically. To do this, we use formulas. You can use any formula you’d like as long as one of the cells can provide a value for Goal Seek to find.
In our example, we need to calculate the Profit using the other four values. In this case, we are using the formula:
=(B6*B7)-(B4+(B7*B5))
Using these cell references equates to:
Profit = (Selling Price * Number of Items) - (Fixed Costs + (Number of Items * Variable Cost))
As you can see, the result in our example is -$400,000 as we haven’t yet used Goal Seek to find a fixed profit.
Note: This formula and the data within is arbitrary and only used in our example. Goal Seek can work with many different types of manual formulas to figure out a missing term algebraically.
Using Goal Seek
Now that you have entered your data into the cells, we need to use Goal Seek to find the required value. In this case, we need to find the Number of Items to make a Profit of $20,000.
To do this:
Open Google Sheets Goal Seek
- Navigate to Extensions>Goal Seek>Open
- The Goal Seek dialog box will open on the left-hand side that requires three inputs to make its calculation
Enter the Data
The Set Cell value is the target value we are looking to achieve. For this example (and most Goal Seek functions) its the cell the formula is in.
In this case, the Profit data is the Set Cell we need to input.
- With the Goal Seek sidebar still open, click on the cell you need as your Goal (B8 in our example)
- Click on the green grid icon on the right side of the Set Cell text box. This will automatically add the cell reference to the text box.
- To Value is the output value you want to achieve in the Set Cell box (the goal). In our case, we want to find the number of items we need for a profit of $20,000. So we will input a value of 20000 here.
- The last value is the “By Changing Cell” variable. This is the number that will change to “seek the goal” of 20,000. In our example we want to highlight cell B7 and click the green box next to the value.
- Once you have all the inputs in the Goal Seek Add-on, you will see the Solve button turn blue. Click on it to start the calculations.
- You will begin to see the “By Changing Cell” values change rapidly. Every new value is the one that was used by the add-on to reach the final answer.
- A dialog box will appear in the middle of the screen once it’s done, telling you that the process has been completed. In our example, the Number of Items needed for a $20,000 Profit was 2,100.
How to Troubleshoot a Google Sheets Goal Seek Error
If your attempt at a goal seek Google Sheets script is not working, you may have to check your inputs.
- Make sure the Set Cell is the one containing the formula – and is the “Goal”
- Ensure the By Changing Cell input is the variable that you want to change to reach the goal
- The To Value should be a numerical value
The Bottom Line
That’s just about all there is to the Google Sheets Goal Seek add-on. Check out some of our other Google Sheets tips to help you master this intuitive, yet sometimes complex, spreadsheet program.
