How to use the GROWTH function
What is the GROWTH function?
The GROWTH function returns estimated exponential growth based on given data. It calculates the y-values for new x-values based on existing x and y-values, see the following formula and the image above.
Table of Contents
1. Introduction
What is exponential growth?
Exponential growth is a process that increases a quantity over time at a rate that is proportional to the quantity itself. For example, if you have a population of rabbits that doubles every 6 months, then the number of rabbits will grow exponentially as time passes.
Exponential growth can be described by a mathematical function of the form y = abx, where y is the quantity, a is the initial value, b is the growth factor, and x is the time.
The graph of an exponential function is always a curve that rises or falls rapidly, depending on whether the growth factor is greater or less than one.
Related functions
| Function | Description |
|---|---|
| POWER(number, power) | Calculates a number raised to a power. |
| GROWTH(known_y's, [known_x's], [new_x's], [const]) | Returns predicted y-values for exponential growth trend. |
| EXP(number) | Returns e raised to the power of a given number. |
2. GROWTH function Syntax
GROWTH(known_y's, [known_x's], [new_x's], [const])
3. GROWTH function Arguments
| known_y's | Required. Y-values you want to use. |
| [known_x's] | Optional. X-values you want to use. |
| [new_x's] | Optional. The GROWTH function uses these values to calculate the corresponding y-values. |
| [const] | Optional. Boolean value that determines whether to force the constant b to be 1. TRUE - b is calculated. (Default value). FALSE - b is equal to 1 and m-values are adjusted. |
4. GROWTH Function Example 1
A startup company has recorded the following sales figures for its first 4 years: Year 1: $50,000 Year 2: $75,000 Year 3: $112,500 Year 4: $168,750 Use the GROWTH function to calculate the sales figures the upcoming years?
| Year | Sales |
| 1 | 50000 |
| 2 | 75000 |
| 3 | 112500 |
| 4 | 168750 |
The image above shows a data table in cell range B13:D17, it has three column header names: Year, sales, and growth. Data for the first 4 years exists, however, we want to calculate the sales data for the next upcoming years.
The following formula returns an array of values based on existing data and new x values representing the upcoming years. The chart shows a blue curve describing existing data for years 1 to 4, the orange line shows the predicted upcoming years modeled after an exponential growth equation.
Array formula in cell D18:D22:
The formula in cells D18:D22 calculates the sales figures for years 5 to 9 based on the assumption that an exponential growth model finds the best fitting for the data points specified in cells C14:C17.
4.1 How to enter an array formula
Excel 365 subscribers may skip these steps, no need to enter an array formula. To enter an array formula select cell range D18:D22, type the formula and then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.
Don't enter the curly brackets yourself, they appear automatically.
5. GROWTH Function Example 2
A population study is tracking the growth of a certain species over time. The population data for the last 6 years is: Year 1: 1000 Year 2: 1200 Year 3: 1440 Year 4: 1728 Year 5: 2074 Year 6: 2489. Use the GROWTH function to determine the population size for the next three years?
| Year | Population |
| 1 | 1000 |
| 2 | 1200 |
| 3 | 1440 |
| 4 | 1728 |
| 5 | 2074 |
| 6 | 2489 |
The image above shows a data table in cell range B13:D22, it has three column header names: Year, population, and growth. Data for the first 6 years exists, however, we want to calculate the population data for the next upcoming years.
Formula in cell D20:
The formula in cell D20 calculates the population for year 7 based on a exponential growth model to find the best fitting for the data points specified in cells C14:C19.
This formula returns only one value as the third argument is only one cell. The first two arguments are locked or absolute cell references meaning they don't change when we copy the cell to cells below.
6. GROWTH Function Example 3
A scientist is studying the growth rate of a particular strain of bacteria in a controlled environment. The initial population of the bacteria was recorded as 333 cells. After 2 hours, the population grew to 650 cells, and after 4 hours, it grew to 1400 cells. Assuming that the bacteria follow an exponential growth pattern, use the GROWTH function in Excel to model an exponential curve that has the best fitting for the data points given. Here are the data points:
| Hour | Data |
| 1 | 333.3 |
| 2 | 600 |
| 3 | 650 |
| 4 | 1300 |
| 5 | 1400 |
| 6 | 2900 |
| 7 | 3200 |
| 8 | 7000 |
| 9 | 8542.9 |
The image above shows the data points as blue dots, the x-axis title is hours and the y.axis is the number of bacteria cells. The hours are displayed in cell range B16:B25 and the number of cells in C16:C25.
Formula in cell D17:
The formula in cell D17 calculates the new y-values based on the old x and y values in B16:B25 and C16:C25 respectively. These values in D17:D25 are shown as the black exponential curve in the chart above. This curve is the best-fitting curve the formula can find so that the differences between the old and new values are minimized as much as possible.
7. How is the GROWTH Function calculated
The GROWTH function calculates the constant growth rate (b) by finding the value that minimizes the sum of the squared differences between the logarithms of the known data points and the logarithms of the values calculated using the exponential growth model.
This method of minimizing the sum of squared differences is known as the least squares regression method, and is used to find the best-fitting curve or line for a given set of data points.
The mathematical formula behind the GROWTH function is:
y = m * b^x
Where:
y = the predicted value at a specific point in time
m = a constant representing the initial value (the first known data point)
b = the base of the exponential function, representing the constant growth rate
x = the number of periods (time units) elapsed since the initial value
The relationship used is
y=b*m^x
if [const] is TRUE
and y=m^x
if [const] is FALSE.
Functions in 'Statistical' category
The GROWTH function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form