Microsoft Office Tutorials and References

In Depth Information

**Regression Function Arguments**

line. The r-squared for the left chart in
Figure 14.12
is 0.985. The r-squared

for the chart in
Figure 14.13
is 0.000001, indicating that no correlation exists.

When you have data like the data in
Figure 14.13
,
it does not mean that you

cannot use regression analysis. It means that you need to think about the

data to see if other factors could help describe the data. Suppose that the

data represents sales of squares of roofing shingles in Florida. If you add

data to the chart that describes the number of category 3+ hurricanes mak-

ing landfall each year, the sales numbers begin to make sense. The r-squared

for predicting sales based on year is nearly 0. The r-squared for predict-

ing sales based on hurricanes is 0.987. Because an r-squared of 1 means al-

most perfect correlation, you could base prediction of sales on a forecast

of hurricanes.

Regression Function Arguments

For all the following regression functions, the arguments list generally in-

cludes these two arguments (for brevity, they are described here once):

•
known_y

known_y''s
—
This is an array or a cell range of numeric dependent

data points. This is the range of data that you want to predict. It might

be the actual sales for the past several years or the population of

bacteria for the past several hours.

•
known_x

known_x''s
—
This is the set of independent data points. These are the

values that you think will lead to a prediction of the y values. For

a simple time series, this might be a list of year numbers. It might be a

list of other independent data points, such as the number of hur-

ricanes making landfall each year.

The arguments must be numbers or names, arrays, or references that contain

numbers. If an array or a reference argument contains text, logical values,

or empty cells, those values are ignored; however, cells that contain the

value 0 are included. If known_y'sand known_x'sare empty or have a dif-

ferent number of data points, the function returns an #N/A error.

Functions for Simple Straight-Line Regression:

Functions for Simple Straight-Line Regression:
SLOPE

SLOPE
and

and
INTERCEPT

INTERCEPT

With many things in Excel, there is a right way to do something. However,

sometimes the powers-that-be decide that the right way is too difficult for

Excel customers, so they offer alternative, easier ways to solve problems.

The LINEST function is powerful, and using it is the right way to calculate

straight-line regression. However, because the LINEST function returns an