Hi DBane,
Forgive me if the following is too basic, but I can't tell from the earlier
posts in this thread how familiar you are with regression. (If you like the
Dummies books, you might want to pick up Statistical Analysis with Excel For
Dummies, by Joseph Schmuller; if you don't, or even if you do, have a look
at Mike Middleton's book on data analysis using Excel. Both are excellent
references.)
"Simple" linear regression seeks to predict the value of one variable from
the value of another variable, given an existing set of values for both
variables. For example, you might have data on the weight and height of 50
people, in, say, A1:A50 (weight, the known_y's, the value you want to
predict) and in B1:B50 (height, the known_x's, the values you want to use as
the basis for your prediction).
You chance your arm by measuring the height of a random pedestrian. Having
survived that pedestrian's annoyance, you return to your computer and put
his height in cell B51. In some other blank cell, you enter this formula:
=TREND(A1:A50,B1:B50,B51)
which returns the predicted weight of that pedestrian, given his/her height
(in B51) and the relationship between weight and height according to the
values in A1:A50 and B1:B50.
The TREND function will take account of the numeric relationship between
height and weight that's defined by the values in A1:B50. This relationship
is something like "The greater the height, the greater the weight." But only
by numerically analyzing the height values and the weight values can
regression establish a formula that expresses the relationship between the
two. The TREND function, which uses regression to calculate that
relationship, applies that numeric relationship -- that is, a formula -- to
the value in cell B51, and on that basis predicts the weight of the
pedestrian whose height you have entered in B51.
The TREND function returns a predicted value. If you want to know the
equation that Excel uses to predict that value, use LINEST.
The following might come under the heading of Too Much Information, but it's
also possible to predict one variable, say Weight, from more than one other
variable, say Height and Sex. This is termed "multiple regression." Multiple
regression, whether applied by Excel or a true statistics package, combines
the multiple predictor variables, in this example Height and Sex, in an
equation that defines the strongest relationship between the combination of
those predictor variables and the predicted variable. This equation is
termed the "regression equation." (Way Too Much Information: you can predict
multiple outcome variables from multiple predictor variables, in a process
termed "canonical correlation," which Excel doesn't offer as a built-in
function, and Excel is wise not to do so.)
For example, multiple regression analysis might tell you to multiply height
by 10.0 and sex (1=female, 2=male) by 0.5. (I'm making these numbers up;
it's a technique I learned from DBarry.) Excel's TREND and LINEST functions
add the results of those multiplications together to create a new variable,
which combines height and sex. Finally, multiple regression predicts weight
by the combination of height and sex as expressed by that new variable.
Both LINEST and TREND are capable of multiple regression analysis. With
TREND, be sure that you supply as many known_x variables in the third
argument as you do in the second argument.
As to the constant (aka intercept), that's just a number you add into the
regression equation, whether that equation is derived by TREND or LINEST.
It's best to let Excel calculate the constant "normally" by setting the
fourth argument of TREND or the third argument of LINEST to TRUE or just
omitting it, as in the TREND example I give above. The alternative is to
force the constant to equal zero, which can cause spurious results.
C^2
Conrad Carlberg