What it does – Predict future values based on historical data.
Syntax=GROWTH(known_data_y, [known_data_x], [new_data_x], [b])known_date_y – The range containing the known dependent (y) values.known_data_x – [Optional] The range of already known values of the independent variable (x).new_data_x – [Optional] The range of values of x for which the GROWTH function returns a corresponding y value.
b – [Optional] True by default. If FALSE the value of b in a general exponential equation of the form of y = b*m^x is forcefully set equal to 1 and only m values are calculated. In case this is TRUE the value of b is calculated normally.
Sample Usage=GROWTH(B2:B6, A2:A6, A7, TRUE)
//This will return the predicted value for the year in A7 based on the values in cells B2:B6–the corresponding values of the years in A2:A6.
Learn how to use the GROWTH function in Google Sheets.
Introduction to Growth in Google Sheets
As the name suggests the GROWTH function is used to predict growth based on the available past data. For instance, a company that wants to predict its sales for the next quarter based on the performance in the previous quarters can simply use the GROWTH function to forecast the same.
The GROWTH formula can also help the company devise a plan to define its sales targets.
For example, say we have data points 1,2,3,4,5. In this case, the growth function will return a value of 0.6, which is the growth rate of the dataset.
GROWTH function has two inputs parameters
✅ Value that you want to calculate growth for
✅ Number of periods over which you want to calculate growth
Unlike the TREND function in Google Sheets, which is a linear best fit ie straight line; the Growth function in Google Sheets is basis an exponential trend fit.
Data Setup for Growth in Google Sheets
The data shown below demonstrates the sales made by a company from 2017 to 2021. Based on this past sales data we want to predict the sales for the year 2022.
Here values in column A and column B refer to the known_data_x and known_data_y values. 2022 is the new_data_x for which the sales prediction will be calculated.
Now, let’s have a look at how to do the same using the google sheets growth formula.
Using the GROWTH Function
- In the cell corresponding to the sales for the year 2022, start typing ‘=’ followed by the name of the function ‘GROWTH’ in the cell. As you type, Google Sheets will automatically suggest the required function. Choose this function.
- The following formula calculates the value of predicted sales:
=GROWTH(B2:B6, A2:A6, A7, TRUE)
- Hit Enter. The function returns the sales prediction value for the year 2022 as output.
Why am I getting #VALUE! Error?
The #VALUE! Error is displayed if any of the values among known_data_y, known_data_y, or new_data_x is non-numeric.
To avoid getting this error, ensure that you provide numeric values as arguments to the GROWTH function in Google Sheets.
Common Errors for Growth Function in Google Sheets
Following are some additional errors that we can encounter while working with the GROWTH function:
- #REF! error – This error occurs when the length of the known_data_x array differs from that of the known_data_y array.
- #NUM! error – The error occurs when any value in the known_data_y array is less than or equal to zero.
To avoid getting these errors make sure you provide valid data as arguments to the GROWTH Function.
Conclusion
In this article, we learnt how the GROWTH formula in Google Sheets is helpful for forecasting future performance by extrapolating past growth in an exponential function.
If you are looking for a visual representation of historical growth, you may also consider using the Slope function to measure steepness using our step-by-step guide on how to calculate slope in Google Sheets.
If you liked reading this article, you might enjoy our definitive guide on Google Sheets for hundreds of tutorials and how-to articles on Google Sheets.
See More
Share your Google file as a PDF – Avoid edits on your Google Sheet by exporting as PDF