formula for linear regression

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.

When you highlight the range XL analyses it to see what it is incrementing
by and continues the series when you pull down on the "Fill Handle" (the +
at the bottom). The get this by a formula enter:

=A5+10

(assuming the the original range was in A1:A5)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"DBane" wrote in message
...
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.

Sandy Mann,
I am a foolish polish truck driver from Pittsburgh. The numbers I am
placing in the columns vary. say example 46,60,50,49,58,this time .
21,39,60,50,61,the next. the figures change each time. What I want to do is
put a formula in the cell range to give me the result linear regression would
give .

"Sandy Mann" wrote:

When you highlight the range XL analyses it to see what it is incrementing
by and continues the series when you pull down on the "Fill Handle" (the +
at the bottom). The get this by a formula enter:

=A5+10

(assuming the the original range was in A1:A5)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"DBane" wrote in message
...
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.

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

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

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

Okay, is anyone out there familiar with thorobred horse racing and the DRF .
I am trying to create a form cycle on my spreadsheet by using Beyer speed
figures and also equibase speed figures. I want to use a formula for linear
reggression and also the power function used by the chart wizard. instead of
using the fill handle and wizard to predict the values, I want to enter a
formula which would return the same results. Can someone make this simple for
a foolish polish truck driver from Pittsburgh who likes to take in the ponies.

"Conrad Carlberg" wrote:

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

On Sat, 27 Aug 2005 09:04:01 -0700, "DBane"
wrote:

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


Did you try the formula I posted?

If you did, and looked at HELP, you would see that B3:B7 are in the slot for
"known y's"; that the slot for "known x's" is blank; and that the slot for
Constant (an optional argument) is not used.

B3:B7 is the location of your column of figures 20,30,40, etc.

Since these represent x=1 to x=5; and since you are interested in what is 'y'
when x=6, the 6, is in the 'new x' slot.

I will leave it to you to figure out what Excel is doing with regard to the
"known x's". If you cannot figure that out, post back as otherwise you will
not be able to understand what is going on.

Since the length of the list will vary, try this formula instead -- it is the
equivalent except it can deal with a list of varying length. The formula
assumes that your first entry is in B3, that the entries are in sequential
cells down to no further than B20, and that the cells after your entries are
blank; so alter that if the assumptions I've posted are incorrect:

=TREND(OFFSET(B3,0,0,COUNT(B3:B20)),,COUNT(B3:B20) +1)


--ron

Ron,
Thank you so very much. This was what I was seeking. Your answer made
putting it the cell range so simple, even for a foolish polish truck driver
that I am. I played with that formula for a month, it was totally
frustrated. I am Probably stretching my luck and your patience. Could you
possibly give me what is needed to return the result the chart wizard uses
when using the power function forcasting 1 period forward. Using the the same
variables used in the trend function. You are very knowedgable in this field.
I believe you have an idae of what I am trying to accomplish. I use the same
range of numbers, using th forcast function with a median. what I am doing is
taking the 3 functions and averaging them. My question is if you were doing
this , would you use just 1 function,and if so which would be your choice.
You have been so very helpful so far, that I feel bad about having you do my
work for me. Bottom line is you know what your doing.
"Ron Rosenfeld" wrote:

On Sat, 27 Aug 2005 09:04:01 -0700, "DBane"
wrote:

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


Did you try the formula I posted?

If you did, and looked at HELP, you would see that B3:B7 are in the slot for
"known y's"; that the slot for "known x's" is blank; and that the slot for
Constant (an optional argument) is not used.

B3:B7 is the location of your column of figures 20,30,40, etc.

Since these represent x=1 to x=5; and since you are interested in what is 'y'
when x=6, the 6, is in the 'new x' slot.

I will leave it to you to figure out what Excel is doing with regard to the
"known x's". If you cannot figure that out, post back as otherwise you will
not be able to understand what is going on.

Since the length of the list will vary, try this formula instead -- it is the
equivalent except it can deal with a list of varying length. The formula
assumes that your first entry is in B3, that the entries are in sequential
cells down to no further than B20, and that the cells after your entries are
blank; so alter that if the assumptions I've posted are incorrect:

=TREND(OFFSET(B3,0,0,COUNT(B3:B20)),,COUNT(B3:B20) +1)


--ron

On Sun, 28 Aug 2005 09:17:01 -0700, "DBane"
wrote:

Ron,
Thank you so very much. This was what I was seeking. Your answer made
putting it the cell range so simple, even for a foolish polish truck driver
that I am. I played with that formula for a month, it was totally
frustrated. I am Probably stretching my luck and your patience. Could you
possibly give me what is needed to return the result the chart wizard uses
when using the power function forcasting 1 period forward. Using the the same
variables used in the trend function. You are very knowedgable in this field.
I believe you have an idae of what I am trying to accomplish. I use the same
range of numbers, using th forcast function with a median. what I am doing is
taking the 3 functions and averaging them. My question is if you were doing
this , would you use just 1 function,and if so which would be your choice.
You have been so very helpful so far, that I feel bad about having you do my
work for me. Bottom line is you know what your doing.
"Ron Rosenfeld" wrote:


I'm not sure I can help you with the Power function forecasting. You might be
better off, for now, using the show formula option on the chart and copying the
values. I have devised a formula which I think should work, but it has some
strange anomalies that I don't understand, so I would not advise using it.


I will ask the question specifically and see if someone has an answer.

I have no experience with horse racing, so cannot advise you any further on
what approach to use. I prefer the stock market, myself. :-)


--ron