formula for linear regression

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:


OK, I figured out my error. Here is the formula for the Power Function
projection. As in the previous, it assumes your values are B3:B20

It's a bit more complicated as excel does not have a built-in function for
this; although someone else reading might have a simpler one.

=(COUNT(B3:B20)+1)*10^INDEX(LINEST(LOG(
OFFSET(B3,0,0,COUNT(B3:B20))),LOG(ROW(
INDIRECT("1:"&COUNT(B3:B20))))),1,2)^LINEST(
LOG(OFFSET(B3,0,0,COUNT(B3:B20))),LOG(
ROW(INDIRECT("1:"&COUNT(B3:B20)))))

Good luck at the track!!


--ron

On Mon, 29 Aug 2005 00:13:42 -0400, Ron Rosenfeld
wrote:

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:


OK, I figured out my error. Here is the formula for the Power Function
projection. As in the previous, it assumes your values are B3:B20

It's a bit more complicated as excel does not have a built-in function for
this; although someone else reading might have a simpler one.

=(COUNT(B3:B20)+1)*10^INDEX(LINEST(LOG(
OFFSET(B3,0,0,COUNT(B3:B20))),LOG(ROW(
INDIRECT("1:"&COUNT(B3:B20))))),1,2)^LINEST(
LOG(OFFSET(B3,0,0,COUNT(B3:B20))),LOG(
ROW(INDIRECT("1:"&COUNT(B3:B20)))))

Good luck at the track!!


--ron


Nope, there's still something wrong here, so don't bet on these results :-((


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

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



OK, Finally, I think I have it correct now:


=10^INDEX(LINEST(LOG(OFFSET(B3,0,0,COUNT(
B3:B20))),LOG(ROW(INDIRECT("1:"&COUNT(
B3:B20))))),1,2)*(COUNT(B3:B20)+1)^LINEST(LOG(
OFFSET(B3,0,0,COUNT(B3:B20))),LOG(ROW(
INDIRECT("1:"&COUNT(B3:B20)))))

(My excuse is that it's late at night).


--ron

Ron,
Thank you, I am totally blown away. If your middle name isn't Excel, it
should be. With the stock market being the way it has been of late, if your
thinking about changing your investments. I would be more than happy to
mentor you to the Holy Trinity of Racing. That being, form, class,and speed.
With the age of computors, access and excel, and some knowledge and a lot of
help from others like yourself most of the guess work is removed and solid
selections are able to be made. Still there is a need for luck (random). God
bless, he did me, with your help. Good luck in the market.
"Ron Rosenfeld" wrote:

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:

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



OK, Finally, I think I have it correct now:


=10^INDEX(LINEST(LOG(OFFSET(B3,0,0,COUNT(
B3:B20))),LOG(ROW(INDIRECT("1:"&COUNT(
B3:B20))))),1,2)*(COUNT(B3:B20)+1)^LINEST(LOG(
OFFSET(B3,0,0,COUNT(B3:B20))),LOG(ROW(
INDIRECT("1:"&COUNT(B3:B20)))))

(My excuse is that it's late at night).


--ron

On Tue, 30 Aug 2005 16:00:01 -0700, "DBane"
wrote:

Ron,
Thank you, I am totally blown away. If your middle name isn't Excel, it
should be. With the stock market being the way it has been of late, if your
thinking about changing your investments. I would be more than happy to
mentor you to the Holy Trinity of Racing. That being, form, class,and speed.
With the age of computors, access and excel, and some knowledge and a lot of
help from others like yourself most of the guess work is removed and solid
selections are able to be made. Still there is a need for luck (random). God
bless, he did me, with your help. Good luck in the market.

Thanks for the flowers. Glad to help.

I think I'll stick with the market. I suppose I feel the same about the market
as you do about Racing; except I think that the strategy I am using removes the
"luck" factor :-))


--ron