Microsoft Office Tutorials and References
In Depth Information
Using LOGEST to Perform Exponential Regression
33. Enter a new table in A29:C36. You want to find the forecasted re-
quirements for 2013 and 2014 for the possibility that there are
zero, one, two, or three hurricanes. The year, recession, and hur-
ricane columns must be in the same format as the known_x'sin
44. Keep in mind that because the TREND function is an array func-
tion, it can return several answers from one formula. Select the
range D26:D33. With that range selected, start to type the for-
55. Enter D5:D23
D5:D23 for known_y's, which are past sales. Enter A5:C23
for known_x's. The new x values are the data in A26:C33.
66. Ensure that your formula is now
=TREND(D5:D23,A5:C23,A26:C33). To finish the formula, hold down
Ctrl+Shift while pressing Enter.
to Perform Exponential Regression
Some patterns in business follow a linear regression. However, other items
are not linear at all. If you are a scientist monitoring the growth of bacteria
in a Petri dish, you see exponential growth in the generations.
If you try to fit an exponential growth to a straight line, you have a large
error. If the r-squared from linear regression is too low, you can try using
exponential regression to see if the pattern of data matches exponential re-
gression better. For exponential regression, you use the LOGEST function,
which is similar to the LINEST function.
LOGEST to Perform Exponential Regression
In regression analysis, the LOGEST function calculates an exponential
curve that fits the data and returns an array of values that describes
the curve. Because this function returns an array of values, it must be
entered as an array formula. The equation for the curve is y = b*m^x or y =
(b*(m1^x1)*(m2^x2)*_) (if there are multiple x values), where the dependent y
value is a function of the independent x values. The m values are bases that
correspond to each exponent x value, and b is a constant value.
The LOGEST function takes the following arguments: