Excel Growth Function
Equation for an Exponential Growth Curve
For a single range of x-values, the equation for an exponential curve is:
y = b * m^x
where,
- x is the independent variable;
- y is the dependent variable;
- m is a constant base for the x value;
- b is a constant which is the value of y when x = 0.
If there are multiple ranges of x-values, the exponential curve equation is:
y = b * m1^x1 * m2^x2 * ...
where,
- the x's are the independent variable ranges;
- y is the dependent variable;
- the m's are constant bases for the x values;
- b is a constant.
Function Description
The Excel Growth function calculates the exponential growth curve through a given set of y-values and (optionally), one or more sets of x-values. The function then extends the curve to calculate additional y-values for a further supplied set of new x-values.
The syntax of the function is:
GROWTH( known_y's, [known_x's],
[new_x's], [const] )
Where the arguments are as follows:
| known_y's | - | A set of known y-values. |
| [known_x's] | - |
A set of known x-values. This is an optional argument of one or more arrays of x-values. If provided, the [known_x's] should have the same length as the set of known_y's. If omitted, the set of [known_x's] takes on the value {1, 2, 3, ...}. |
| [new_x's] | - |
A set of new x-values, for which the function calculates corresponding new y-values. If omitted, the set of [new_x's] is assumed to be the same as the [known_x's] and the function returns the y-values that lie on calculated exponential growth curve. |
| [const] | - |
An optional logical argument that specifes whether the constant 'b', in the equation y = b * m^x, should be forced to be equal to the value 1. If [const] is TRUE (or if this argument is omitted) the constant b is treated normally; If [const] is FALSE the constant b is set to 1 and the equation becomes y = m^x. |
The Growth Function as an Array Formula:
If more than one new y-value is to be calculated by the Excel Growth function, the new values will be returned as an array. Therefore, the function must be entered as an Array Formula (see the examples below).
To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.
See the Excel Array Formulas page for further details.Growth Function Example
In the spreadsheet below, the Excel Growth Function is used to extend a series of x- and y-values that lie on the exponential growth curve y = 5 * 2^x. These are stored in cells A2-B5 of the spreadsheet and are also shown in the spreadsheet graph.
The Growth function calculates the exponential growth curve that has the best fit for the supplied known x- and y-values. In this simple example, the curve of best fit is the exponential curve y = 5 * 2^x.
Once Excel has calculated the exponential growth curve equation, it can use this to calculate the new y-values for the provided new x-values in cells A8-A10.
In this example, the values of the [new_x's] are stored in cells A8-A10 and the Excel Growth function has been entered into cells B8-B10. As shown in the formula bar, the formula for this is:
=GROWTH( B2:B5, A2:A5, A8:A10 )
It is seen that the Growth function in the formula bar is encased in curly braces { }. This indicates that the function has been input as an Array Formula.
Note that, although the points in the above example fit exactly along the curve y = 5 * 2^x, this is not essential. The Excel Growth function will find the curve of best fit for any set of values provided to it.
For further examples of the Excel Growth function, see the Microsoft Office website.
Growth Function Errors
If you get an error from the Excel Growth function, this is likely to be one of the following:
Common Errors
| #REF! | - | Occurs if the [known_x's] array has a different length to the known_y's array. |
| #NUM! | - | Occurs if any of the values in the known_y's array are less than or equal to 0. |
| #VALUE! | - | Occurs if any of the values in the supplied known_y's, [known_x's] or [new_x's] arrays are non-numeric. |