Google Sheets goal seek feature is a strong data analysis tool. It is essentially an algorithm that allows you to supply the desired output and then returns the value of a given input variable that will assist in achieving the desired output. In this article, let us learn everything about Goal Seek analysis with the help of Google Sheet tips provided on this page.
|
Table of Contents |
Goal Seek Algorithm in Google Sheets
The Goal Seek algorithm simply does a series of what-if calculations, gradually changing the input values until you get the desired result. For example, let’s consider we have employee salary data. If you want to reduce the salary by some percentage, then you can alternate the same by using the Goal Seek tool.
That is you can specify the percentage of salary you to need reduce by mentioning the cells where you need to make the alterations.
- How to Use IMPORTRANGE Function in Google Sheets (with Examples)
- How to Compare Two Columns in Google Sheets (for matches & differences)
- How To Move A Column In Google Sheets (3 Easy Methods)
A Goal Seek algorithm is made up of three parts:
- A variable whose value is unknown
- A predetermined output value
- To get to the known output, use a formula that includes the unknown input
How to Use Goal Seek in Google Sheets?
In order to use Goal Seek in Google Sheet, we need to install the Goal Seek add-on function to Google Sheets. The step to install Goal Seek Add-on are given below:
- Step 1: On the homepage of Google Spreadsheet click on the “Add-ons” button in the menubar.
- Step 2: Now select “Get Add-ons” from the drop-down menu.
- Step 3: In the Google Workspace Masterplace search bar, enter “Goal Seek“.
- Step 4: The Goal Seek application will pop on the screen. Now click on the app and then hit on the “Install” button.
- Step 5: To install Goal Seek, you will be prompted for permission. Continue by pressing the Enter key.
- Step 6: You will be asked to confirm that you want Goal Seek to have access to your Google account. Allow button should be chosen.
- Step 7: The notification ‘Goal Seek has been installed‘ should now appear on the screen. After that, click on the Done button.
- Step 8: The Google Workspace Marketplace window should now be closed. The Goal Seek add-on should now be available as a menu item under the ‘add-ons’ menu.
How to Enable Goal Seek Window on Google Sheets?
To enable the Goal Seek window in Google Sheets, follow the steps as listed below:
- Step 1: Click on the “Add-On” tab from the menubar.
- Step 2: Select “Goal Seek” from the drop-down menu and further select “Open” from the submenu.
- Step 3: You should be able to see the Goal seek window as a sidebar panel of your Google Sheets window once the app has launched.
- Step 4: Now you will see 3 parameters as “Set Cell, To Value, and By Changing Cell“. If you see this, then it means that the Goal Seek window is enabled on Google Sheets.
How to Set Up Goal Seek Options?
You may just put in your desired values and let Goal Seek do the rest once the window has opened.
1. Set Cell in Goal Seek
There are two options for providing this information. One method is to type the cell reference of the cell containing the known output value directly into the cell (D17).
Another option is to choose cell D17 and then click the ‘Capture selected cell’ button in the Goal Seek pane’s ‘Set Cell’ field.
2. To Value in Goal Seek
Type your goal value in the box labeled “To Value”. In our case, it is 567832 in our case. Make sure the values you have entered don’t contain currency signs or separators in your text.
3. By Changing Cell
The cell reference for the cell containing the unknown value must be provided here. This reference can be provided in two ways, similar to the Set cell value method. One option is to type the cell reference directly (d4).
Another option is to choose cell d4 and then click the ‘Capture selected cell’ button in the Goal Seek pane’s ‘By changing cell’ field. It’s important that the cell reference entered in the ‘Set cell’ field depends on the reference given in the ‘By changing cell’ field.
How to Use Goal Seek in Google Sheets?
Once you have decided what you want to put in Goal Seek’s input options, you can move on to the next step:
- Step 1: In the ‘Set cell,’ ‘To value,’ and ‘By altering cell‘ inputs, type the values you need.
- Step 2: Select “Solve” from the drop-down menu.
- Step 3: Google Sheets will do the necessary calculations and update the values in cells D4 and D17 until the desired value in cell D17 is reached.
- Step 4: You will receive a notification once the Goal Seek is completed, indicating that the required Goal Seek has been accomplished.
You can also scroll down to the bottom of the Goal Seek panel to see the “Solve Status” at this point. Here you will find all of the input values as well as other relevant information such as Goal Seek start time, running time, and so on.
