Microsoft Office Tutorials and References
In Depth Information
Calculating exponential regression
The SLOPE function
The SLOPE function returns the slope of the linear-regression line. The slope is defined as
the vertical distance divided by the horizontal distance between any two points on the
regression line. Its value is the same as the first number in the array returned by the LINEST
function. In other words, SLOPE calculates the trajectory of the line used by the FORECAST
and TREND functions to calculate the values of data points. The SLOPE function takes the
form =SLOPE( known_y’s, known_x’s ).
To find the slope of the regression line that describes the data set from the example shown
in Figure 17-5, you can use =SLOPE(B2:B19, A2:A19), which returns a value of 20.613.
The STEYX function
The STEYX function calculates the standard error of a regression, a measure of the
amount of error accrued in predicting a y for each given x . This function takes the form
=STEYX( known_y’s, known_x’s ). If you apply this function to the worksheet shown in
Fig ure 17-5, the formula =STEYX(B2:B19, A2:A19) returns a standard error value of 12.96562.
Calculating exponential regression
Unlike linear regression, which plots values along a straight line, exponential regression
describes a curve by calculating the array of values needed to plot it. The equation that
describes an exponential regression curve is as follows:
x1
x2
xn
y = b * m 1
* m 2
* * m n
If you have only one independent variable, the equation is as follows:
y = b * m x
The LOGEST function
The LOGEST function works like LINEST, except you use it to analyze data that is nonlinear,
and it returns the coordinates of an exponential curve instead of a straight line. LOGEST
returns coefficient values for each independent variable plus a value for the constant b . This
function takes the form =LOGEST( known_y’s, known_x’s, const, stats ).
LOGEST accepts the same arguments as the LINEST function and returns a result array in
the same fashion. If you set the optional stats argument to TRUE, the function also returns