Microsoft KB Archive/80277

= Microsoft Knowledge Base =

Excel: How the GROWTH Function Is Calculated
Last reviewed: July 24, 1996

Article ID: Q80277

The information in this article applies to:


 * Microsoft Excel for Macintosh, version 2.x, 3.0, 4.0, 5.0
 * Microsoft Excel for Windows, version 2.x, 3.0, 4.x, 5.x, 7.0, 7.0a
 * Microsoft Excel for OS/2, version 2.2, 3.0

SUMMARY
This article discusses the method that Microsoft Excel uses to calculate the GROWTH function. The results returned by the GROWTH function are identical to those returned by the following formula:

{=EXP(TREND(LN(Known_Y's)))}

MORE INFORMATION
The GROWTH function fits an exponential curve to a set of data points. In contrast, the TREND function fits a best-fit line to a set of data points. To calculate the GROWTH function, you can use the TREND function in conjunction with LN and EXP.

Example
Assume you have a set of data points that resemble an exponential curve. The GROWTH function is calculated by applying the TREND function to the natural log of each of these data points (found by applying the LN function) and then applying EXP to the results.

Enter the following into a worksheet:

A1:  3   B1:  =LN(A1)   C1:                 D1: =EXP(C1) A2:  7   B2:  =LN(A2)   C2:                 D2: =EXP(C2) A3: 20   B3:  =LN(A3)   C3:                 D3: =EXP(C3) A4: 55   B4:  =LN(A4)   C4:                 D4: =EXP(C4) A5: 150  B5:  =LN(A5)   C5:                 D5: =EXP(C5) Note: Enter the formula in cells C1:C5 as an array formula by doing the following:


 * 1) Highlight the range C1:C5.
 * 2) Type the formula &quot;=TREND(B1:B5)&quot; in the formula bar.
 * 3) Enter the formula by pressing CTRL+SHIFT+ENTER simultaneously. On the Macintosh, press Apple+return simultaneously.

The set of data points in column A would resemble an exponential curve if graphed. Taking the natural log of these points returns the values in column B, which are {1.10; 1.95; 3.00; 4.01; 5.01}. These new values closely resemble a line when graphed. Applying TREND to these new points returns the values {1.03; 2.02; 3.01; 4.00; 4.99} in column C, which all are located on the same line. The next step in column D is to apply the EXP function. The values returned are {2.81; 7.56; 20.32; 54.61; 146.75}, which fit exactly on an exponential curve and are similar to the original values.

Note that the values in column D are exactly the same values that are returned by applying the GROWTH function directly to the original values in column A.

The formulas in columns B through D could be consolidated into a single formula by entering =EXP(TREND(LN(A1:A5))) into five contiguous vertical cells and pressing CTRL+SHIFT+ENTER to enter the formula as an array formula.