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
--
Excel Sales Forecasting for Dummies, Wiley, 2005
"DBane" wrote in message
...
Thank you for your reply.
I am lost as to what values need to be placed in the known y's,known
x's,
I presume the new x's are the range. what is the const. The length does
vary
each week, The range of the figures will be between 20 and 120.
They will change daily. Never more than 8,nor less than 4 figures in the
range. thank you for your patience. My limited knowledge of excel comes
from
dummies 101:Excell 97.
"Ron Rosenfeld" wrote:
On Fri, 26 Aug 2005 15:28:02 -0700, "DBane"
wrote:
I have a column of figures 20,30,40,50,60. when I select the range and
put
the + at the bottom and pull, the result shows 70. the same using chart
wizard, line, right clicking add treadline. Linear, option, forcast 1
forward,the result 70 also. What formula do I have to place in the cell
range
to get the return 70. I have tried trend and forcast. Totally lost.
=TREND(B3:B7,,6)
Or, if the length of your list will vary each week:
--ron
|