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

validation statistics. For more information about the LOGEST function’s underlying

equations and its arguments, see “The LINEST function” earlier in this chapter.