GROWTH in Google Sheets

What is GROWTH Function in Google Sheets?

The GROWTH function in Google Sheets calculates exponential growth using a given set of data points. It is important to remember that the GROWTH function calculates future values along an exponential trend. It fits an exponential curve to the data and is then used to predict future values. The GROWTH function predicts them based on existing data by fitting an exponential growth curve. This kind of prediction is useful in business and scientific projects. GROWTH in Google Sheets will help you fit an ideal exponential growth trend, thereby providing you with data for analysis about growth trends.

Always ensure that the data used for predicting must follow an exponential trend before using the GROWTH function. In the example below, we use the GROWTH function to predict the number of people who will visit a new store in the future. Type =GROWTH(B2:B6,A2:A6,A7:A9) into cell C7 and press Enter.

The first argument represents the existing number of visitors, and the second (A2:A6) represents the corresponding months. The third argument is the extended months for which we predict the growth.

The GROWTH function calculates predicted values based on exponential growth using a set of data points
The syntax of the GROWTH function:

=GROWTH(y-values, x-values, new_xs, const)

a. y-values: The known dependent values

b. x-values: The known independent values

c. new_xs (optional): The x-values to predict new y-values.

d. const (optional): TRUE or FALSE

You can visualize the predicted values and actual data points by creating a like a line chart.
GROWTH in Google Sheets is very helpful for forecasting future values like population growth, sales predictions, or investment predictions.

Syntax

Before we proceed to study the function in detail, let us look at its syntax.

=GROWTH(y_values, x_values, new_x_values, [constant])

The GROWTH function has the following arguments in its syntax.

y_values: (Required) They are known dependent values.
x_values: (Required) The known independent values are the x-axis values corresponding to the y_values.
new_x_values: Optional. The x-values for which you want to predict the corresponding y-values.
constant: Optional. TRUE or FALSE. If TRUE or omitted, it calculates the best-fit exponential curve with an intercept. If FALSE, it forces the curve to go through the origin (0,0).

How to Use GROWTH Function in Google Sheets?

The GROWTH function in Google Sheets forecasts future values based on the present data points using an exponential growth curve. Here, we will see how to use it with two data sets: known Y’s and known X’s with stepwise explanation. There are two ways to enter the GROWTH function in Google Sheets.

Enter GROWTH manually
Through the Google Menu bar

Enter GROWTH Manually

Using the GROWTH function, here’s how to calculate exponential growth in Google Sheets.

Step 1: We have the following data. Using the GROWTH function, let us try to predict the sales for the sixth year. Select a blank cell to enter the GROWTH function. Here, we use cell B7.

Step 2: Use the GROWTH function in B7, where we must look at the prediction for the sixth year. Next, type the formula “=GROWTH(“ to begin the formula.

Step 3: Let us enter the parameters. In this example, we already know the sales for years 1 to 5. These values are found in the range B2:B6.

Hence, the first argument should be the dependent data that is already known.

The second argument should be the known independent variables in our dataset. Here, they are present in A2:A6.

Next, we’ll provide the new independent variable (year 6) we want to predict for our curve fit.

=GROWTH(B2:B6, A2:A6, A7). After closing the parentheses, press Enter.

It will return the predicted sales value for year six based on the exponential growth trend from the previous years.

Through The Google Menu Bar

The same function can be entered through the Google menu bar by accessing the following.

Choose the cell where you want to enter the formula.
Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Array” ➝ “GROWTH.”

Once the function is entered in Google Sheets, enter the required arguments and press Enter.

Examples

Calculating growth rates is an essential skill that helps you understand changes in your business growth or investments over time. In Google Sheets, this can be simply done using the GROWTH formula. When you must use the GROWTH function, you can easily predict future growth if you have historical data (y-values) for several periods (x-values). It can be illustrated in a series of examples below.

Example #1 – Predict Future Sales

Perhaps the best use of GROWTH in Google Sheets is forecasting future sales based on historical sales data. Let us look at it with an example. We have the sales data of an organization entered as shown below.

Step 1: Enter the GROWTH function as shown below.

=GROWTH(B2:B10,A2:A10,A11:A13)

In this formula:

B2:B10 are the known sales values (y-values).
A2:A10 are the months (x-values).
A11:A13 are the future months for which you want to predict the sales. These are the new independent variables that we want to use for our curve fit.

Step 2: Press Enter. After executing the formula, the GROWTH function gives us the prediction of the future sales values.

Example #2

Below is a business’s revenue given in monthly terms. We can use GROWH in Google Sheets to calculate the monthly revenue growth. Let’s implement this in this example. First, let us enter the data in a table.

Step 1: Since GROWTH requires x-values as the first argument, select the dependent x-values from. Next, enter the independent y-values. Lastly, we are trying to find the growth for months 7 and 6. Enter them as the independent values for which we wish to predict the growth.

y-values is the monthly revenue.
x-values are the months

Step 2: Press Enter.

Here, the result is shown below, which indicates the predicted monthly revenue for the next two months at the current growth.

Example #3 – Predict Population Growth

Suppose we have the historical population data of a region over several years. We wish to predict the future population growth of the region. It is one of the best uses of the GROWTH function. Let us see how to go about it.

Step 1: Set up the population data in a Google Sheet, as shown below.

Step 2: Let us apply the GROWTH function. Here, we want to predict the population for the next two years (2023 and 2024).

=GROWTH(B2:B6, A2:A6, A7:A8)

Here,

B2:B6 are the known population data.
A2:A6 represents the years.
A7:A8 are the future years for which we will predict the population.

Press Enter. Now, we get the predicted values for 2023 and 2024.

Step 3: We can generate a line graph to show how the GROWTH function predicts future values visually. Select the range of cells from the data columns and insert a chart. In the Chart Editor, choose “Line chart” for the chart type.

We have tweaked the Chart Editor pane on the right to show you a detailed graph. A green square marker indicates the predicted values.

As observed, the GROWTH function will give us the predicted population for the next two years based on the historical data. This function allows you to predict exponential growth based on historical trends.

Important Things to Note

The GROWTH function uses the least-squares regression principle to uncover the best-fitting exponential curve for your data.
The GROWTH function will return an error if the data ranges for both the x and y values are different.
If you do not give the new_x_values third argument, the GROWTH function returns the predicted y-values for all x-values in the data.
Visualizing the growth rates shows us a clearer picture of trends. Therefore, the growth curve can be visualized by plotting the x-values and the predicted y-values in a chart.

Frequently Asked Questions (FAQs)

What are the practical uses of GROWTH in Google Sheets?

Here are a few practical uses of the GROWTH formula in Google Sheets.

1. To predict future sales

The most important use of the GROWTH function is to predict future sales with the data on past sales. It helps calculate the exponential growth rate and is used for future predictions.

2. Finding investment growth

Similar to sales predictions, you can use the data on past investment growth to predict future growth with GROWTH.

3. Estimating the growth in population

If you have data on the past population growth and wish to estimate future growth, use the GROWTH formula

What are some of the common errors with the GROWTH function?

Let us look at some common errors when using the GROWTH function.

One of the most common errors is providing arrays of different sizes for the known_data_y and known_data_x arguments. It gives rise to the #REF error with the message “GROWTH has mismatched row size.”

If you provide non-numeric data as arguments, you will get a #VALUE! Error.

Another common error is writing the parameters in the wrong place. If you mix up the parameters, unfortunately, you will not get an error but inaccurate results, which could affect your decision-making process.

What is the difference between the GROWTH and TREND function in Google Sheets?

IN Google Sheets, we use both the GROWTH and TREND functions to predict future values. However, TREND fits a linear trend to your data, and calculates parameters for the future trend, while GROWTH fits an exponential model. The TREND function in Google Sheets predicts future values based on historical data by performing linear regression to calculate a trend line , and GROWTH shows the exponential growth (e.g., population or viral spread).

Download Template

This article must help understand GROWTH Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

GROWTH Function In Google Sheets Download

GROWTH in Google Sheets