Table of Contents
What-if analysis in Google Sheets allows users to analyze data by exploring various scenarios and outcomes. This is done by using functions such as IF, SUMIFS, COUNTIFS, and VLOOKUP to determine the results of different scenarios. With these functions, users can make changes to data and see how they will impact the results. Additionally, users can use data validation to set up dropdown menus and data validation rules that control the data and results of their what-if analysis.
What-if analysis is a type of analysis that allows you to plug in different numbers into formulas to see how the results change.
For example, suppose a store sells three different products at different prices and calculates the total revenue from these products:
Now suppose the store manager would like to know how many more units of product A need to be sold to reach a total revenue of $2,000.
The following step-by-step example shows how to perform this exact what-if analysis in Google Sheets.
Step 1: Get the Goal Seek Add-On
Before we perform what-if analysis, we need to first get the Goal Seek add-on.
To do so, click the Add-ons tab and then click Get add-ons:
Search for “Goal Seek” and then click the first result that says Goal Seek for Sheets.
Then click Install. You will be asked for permission to install Goal Seek. Choose to accept.
The Goal Seek add-on will then be added to the Add-ons tab.
Step 2: Perform What-If Analysis
Next, click the Add-ons tab and then click Goal Seek and then click Open:
In the Goal Seek panel that appears, input the following cell values and then click Solve:
The Goal Seek will try various values in D2 until it’s able to achieve the value 2000 in cell D5.
Here is the result that it finds:
This tells us that the store must sell roughly 860 units of product A in order to increase the total revenue up to $2,000.
The Goal Seek panel also provides us with information about how long the Goal Seek took to find a solution:
It took 20.3 seconds to find a solution and Goal Seek tried 48 iterations until it found the final solution.
Note: Within the Goal Seek panel, there is an “Options” button where you can specify the max number of seconds or iterations to use until Goal Seek stops running. By default, the max iterations is set to 200 and the max time limit is set to 120 seconds.
The following tutorials explain how to perform other common operations in Google Sheets: