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
Search JabSto ::




Custom Search