Simple data/Hard statistics

Hi Group,
This is a little difficult to explain, but the underlying data is simple, so
please bear with me. I have Dates and Closings for the Dow Jones Industrial.
Similar to below:
Date Adj. Close*
01/03/05 10729.43
12/27/04 10776.13
12/20/04 10661.6
12/13/04 10638.32
The above is easy to graph and I have automated the process, which includes
a 6th Order Polynomial Trend line added to the graph. What I am trying to do
is find the data points associated with the 6th Order Polynomial Trend line.
It has simply been just too long since I have done this type of statistics. I
believe it may be necessary to create a new table to find these data points,
which I am willing to do. I can calculate the sample mean, number of sample
variables, sample variance, sample standard deviation, etc., but it has just
been too many years to bring the necessary statistical expertise to arrive at
the data points. I am trying to get a table that looks something like this:
Date Adj. Close* Trend
01/03/05 10729.43 10730.25
12/27/04 10776.13 10750.31
12/20/04 10661.6 10765.03
12/13/04 10638.32 10750.00
I have tried using some of the built in functions, but they do not yield the
same data points that have been graphed by the 6th Order Polynomial Trend
line. I have tried Trend and Forecast. I created a table many years ago,
which I think calculated the data points, but it has simply been to many
years and I have lost the statistical expertise. Any help would be greatly
appreciated.
--
David

one of the options on the graph is to display the equation of the trendline.
You need to format the equation to show many decimal places of precision.
Once you have the constants/coefficients associated with the terms of the
equation, you can calculate the predicted points.



--
Regards,
Tom Ogilvy

"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Group,
> This is a little difficult to explain, but the underlying data is simple,
so
> please bear with me. I have Dates and Closings for the Dow Jones
Industrial.
> Similar to below:
> Date Adj. Close*
> 01/03/05 10729.43
> 12/27/04 10776.13
> 12/20/04 10661.6
> 12/13/04 10638.32
> The above is easy to graph and I have automated the process, which
includes
> a 6th Order Polynomial Trend line added to the graph. What I am trying to
do
> is find the data points associated with the 6th Order Polynomial Trend
line.
> It has simply been just too long since I have done this type of
statistics. I
> believe it may be necessary to create a new table to find these data
points,
> which I am willing to do. I can calculate the sample mean, number of
sample
> variables, sample variance, sample standard deviation, etc., but it has
just
> been too many years to bring the necessary statistical expertise to arrive
at
> the data points. I am trying to get a table that looks something like
this:
> Date Adj. Close* Trend
> 01/03/05 10729.43 10730.25
> 12/27/04 10776.13 10750.31
> 12/20/04 10661.6 10765.03
> 12/13/04 10638.32 10750.00
> I have tried using some of the built in functions, but they do not yield
the
> same data points that have been graphed by the 6th Order Polynomial Trend
> line. I have tried Trend and Forecast. I created a table many years ago,
> which I think calculated the data points, but it has simply been to many
> years and I have lost the statistical expertise. Any help would be greatly
> appreciated.
> --
> David

Hi Tom,
I tried that, but the equation showed up on the graph and even after copying
the equation out I was unable to figure out how to use it, where to put it,
etc. Thought I might be able to put it in a new column, "Trend" and calulate
the points, but could not figure it out. Thanks, I will go back and try that
again, but more help would be greatly appreciated.

"Tom Ogilvy" wrote:

> one of the options on the graph is to display the equation of the trendline.
> You need to format the equation to show many decimal places of precision.
> Once you have the constants/coefficients associated with the terms of the
> equation, you can calculate the predicted points.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Group,
> > This is a little difficult to explain, but the underlying data is simple,
> so
> > please bear with me. I have Dates and Closings for the Dow Jones
> Industrial.
> > Similar to below:
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > The above is easy to graph and I have automated the process, which
> includes
> > a 6th Order Polynomial Trend line added to the graph. What I am trying to
> do
> > is find the data points associated with the 6th Order Polynomial Trend
> line.
> > It has simply been just too long since I have done this type of
> statistics. I
> > believe it may be necessary to create a new table to find these data
> points,
> > which I am willing to do. I can calculate the sample mean, number of
> sample
> > variables, sample variance, sample standard deviation, etc., but it has
> just
> > been too many years to bring the necessary statistical expertise to arrive
> at
> > the data points. I am trying to get a table that looks something like
> this:
> > Date Adj. Close* Trend
> > 01/03/05 10729.43 10730.25
> > 12/27/04 10776.13 10750.31
> > 12/20/04 10661.6 10765.03
> > 12/13/04 10638.32 10750.00
> > I have tried using some of the built in functions, but they do not yield
> the
> > same data points that have been graphed by the 6th Order Polynomial Trend
> > line. I have tried Trend and Forecast. I created a table many years ago,
> > which I think calculated the data points, but it has simply been to many
> > years and I have lost the statistical expertise. Any help would be greatly
> > appreciated.
> > --
> > David
>
>
>

Hi Again,

This is the literal equation:
y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17

What I copied out and pasted was:
=-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17

The equation was corrected by Excel to:
=-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17

This gives me a Value Error, not recognizing the "E"? I also ws expecting
references to ranges and stuff and hoping I could copy a formula down my
sheet to arrive at specific values accross from Dates and data values.

Thanks again.


"Tom Ogilvy" wrote:

> one of the options on the graph is to display the equation of the trendline.
> You need to format the equation to show many decimal places of precision.
> Once you have the constants/coefficients associated with the terms of the
> equation, you can calculate the predicted points.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Group,
> > This is a little difficult to explain, but the underlying data is simple,
> so
> > please bear with me. I have Dates and Closings for the Dow Jones
> Industrial.
> > Similar to below:
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > The above is easy to graph and I have automated the process, which
> includes
> > a 6th Order Polynomial Trend line added to the graph. What I am trying to
> do
> > is find the data points associated with the 6th Order Polynomial Trend
> line.
> > It has simply been just too long since I have done this type of
> statistics. I
> > believe it may be necessary to create a new table to find these data
> points,
> > which I am willing to do. I can calculate the sample mean, number of
> sample
> > variables, sample variance, sample standard deviation, etc., but it has
> just
> > been too many years to bring the necessary statistical expertise to arrive
> at
> > the data points. I am trying to get a table that looks something like
> this:
> > Date Adj. Close* Trend
> > 01/03/05 10729.43 10730.25
> > 12/27/04 10776.13 10750.31
> > 12/20/04 10661.6 10765.03
> > 12/13/04 10638.32 10750.00
> > I have tried using some of the built in functions, but they do not yield
> the
> > same data points that have been graphed by the 6th Order Polynomial Trend
> > line. I have tried Trend and Forecast. I created a table many years ago,
> > which I think calculated the data points, but it has simply been to many
> > years and I have lost the statistical expertise. Any help would be greatly
> > appreciated.
> > --
> > David
>
>
>

Assume you values are in A2

Then you would modify what you copied to

= -6E-11*A2^6 + 1E-05*A2^5 - 1.2819*A2^4 + 65247*A2^3 - 2E+09*A2^2 +
3E+13*A2 - 2E+17

I paste that in the formula bar

and get a result. However, you need the to select the trendline formula and
format it to display more precision.

--
Regards,
Tom Ogilvy
..


"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Again,
>
> This is the literal equation:
> y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
>
> What I copied out and pasted was:
> =-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
>
> The equation was corrected by Excel to:
> =-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17
>
> This gives me a Value Error, not recognizing the "E"? I also ws expecting
> references to ranges and stuff and hoping I could copy a formula down my
> sheet to arrive at specific values accross from Dates and data values.
>
> Thanks again.
>
>
> "Tom Ogilvy" wrote:
>
> > one of the options on the graph is to display the equation of the
trendline.
> > You need to format the equation to show many decimal places of
precision.
> > Once you have the constants/coefficients associated with the terms of
the
> > equation, you can calculate the predicted points.
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Group,
> > > This is a little difficult to explain, but the underlying data is
simple,
> > so
> > > please bear with me. I have Dates and Closings for the Dow Jones
> > Industrial.
> > > Similar to below:
> > > Date Adj. Close*
> > > 01/03/05 10729.43
> > > 12/27/04 10776.13
> > > 12/20/04 10661.6
> > > 12/13/04 10638.32
> > > The above is easy to graph and I have automated the process, which
> > includes
> > > a 6th Order Polynomial Trend line added to the graph. What I am trying
to
> > do
> > > is find the data points associated with the 6th Order Polynomial Trend
> > line.
> > > It has simply been just too long since I have done this type of
> > statistics. I
> > > believe it may be necessary to create a new table to find these data
> > points,
> > > which I am willing to do. I can calculate the sample mean, number of
> > sample
> > > variables, sample variance, sample standard deviation, etc., but it
has
> > just
> > > been too many years to bring the necessary statistical expertise to
arrive
> > at
> > > the data points. I am trying to get a table that looks something like
> > this:
> > > Date Adj. Close* Trend
> > > 01/03/05 10729.43 10730.25
> > > 12/27/04 10776.13 10750.31
> > > 12/20/04 10661.6 10765.03
> > > 12/13/04 10638.32 10750.00
> > > I have tried using some of the built in functions, but they do not
yield
> > the
> > > same data points that have been graphed by the 6th Order Polynomial
Trend
> > > line. I have tried Trend and Forecast. I created a table many years
ago,
> > > which I think calculated the data points, but it has simply been to
many
> > > years and I have lost the statistical expertise. Any help would be
greatly
> > > appreciated.
> > > --
> > > David
> >
> >
> >

Maybe I can start over. I have simplified the data as much as possible and
will be literal with what I have come with on the chart and the equations.
Sample data:

Date Adj. Close*
01/03/05 10729.43
12/27/04 10776.13
12/20/04 10661.6
12/13/04 10638.32
12/06/04 10547.06
……….
01/04/99 9643.32

Equation showing on graph:
y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17

Conversion:
y = -6E-11^6 + 1E-05^5 - 1.2819^4 + 65247^3 - 2E+09^2 + 3E+13x - 2E+17

Tried to make it more literal at this point, but several areas of confusion:
=.000011^6 + .5^5 - 1.2819^4+65247^3 - 9^2 + 3E(confused) +13*A2(confused, x
is a date, ref to cell?)- 2E(?)+17

The 13x appears to be the only ref to a cell? The 3E & 2E I can not tell
what numbers they are in ref to?

When I copied out the equation you provided I end up with ########.....,
which indicated the number might be REALLY big. The actual trend line on the
graph indicates the number is fairly close to 10729, which is the last close
for the date 1/3/05. Maybe it is slightly larger.

"Tom Ogilvy" wrote:

> Assume you values are in A2
>
> Then you would modify what you copied to
>
> = -6E-11*A2^6 + 1E-05*A2^5 - 1.2819*A2^4 + 65247*A2^3 - 2E+09*A2^2 +
> 3E+13*A2 - 2E+17
>
> I paste that in the formula bar
>
> and get a result. However, you need the to select the trendline formula and
> format it to display more precision.
>
> --
> Regards,
> Tom Ogilvy
> ..
>
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Again,
> >
> > This is the literal equation:
> > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> >
> > What I copied out and pasted was:
> > =-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> >
> > The equation was corrected by Excel to:
> > =-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17
> >
> > This gives me a Value Error, not recognizing the "E"? I also ws expecting
> > references to ranges and stuff and hoping I could copy a formula down my
> > sheet to arrive at specific values accross from Dates and data values.
> >
> > Thanks again.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > one of the options on the graph is to display the equation of the
> trendline.
> > > You need to format the equation to show many decimal places of
> precision.
> > > Once you have the constants/coefficients associated with the terms of
> the
> > > equation, you can calculate the predicted points.
> > >
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "David" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Group,
> > > > This is a little difficult to explain, but the underlying data is
> simple,
> > > so
> > > > please bear with me. I have Dates and Closings for the Dow Jones
> > > Industrial.
> > > > Similar to below:
> > > > Date Adj. Close*
> > > > 01/03/05 10729.43
> > > > 12/27/04 10776.13
> > > > 12/20/04 10661.6
> > > > 12/13/04 10638.32
> > > > The above is easy to graph and I have automated the process, which
> > > includes
> > > > a 6th Order Polynomial Trend line added to the graph. What I am trying
> to
> > > do
> > > > is find the data points associated with the 6th Order Polynomial Trend
> > > line.
> > > > It has simply been just too long since I have done this type of
> > > statistics. I
> > > > believe it may be necessary to create a new table to find these data
> > > points,
> > > > which I am willing to do. I can calculate the sample mean, number of
> > > sample
> > > > variables, sample variance, sample standard deviation, etc., but it
> has
> > > just
> > > > been too many years to bring the necessary statistical expertise to
> arrive
> > > at
> > > > the data points. I am trying to get a table that looks something like
> > > this:
> > > > Date Adj. Close* Trend
> > > > 01/03/05 10729.43 10730.25
> > > > 12/27/04 10776.13 10750.31
> > > > 12/20/04 10661.6 10765.03
> > > > 12/13/04 10638.32 10750.00
> > > > I have tried using some of the built in functions, but they do not
> yield
> > > the
> > > > same data points that have been graphed by the 6th Order Polynomial
> Trend
> > > > line. I have tried Trend and Forecast. I created a table many years
> ago,
> > > > which I think calculated the data points, but it has simply been to
> many
> > > > years and I have lost the statistical expertise. Any help would be
> greatly
> > > > appreciated.
> > > > --
> > > > David
> > >
> > >
> > >
>
>
>

format the cell as number. (it defaults to date - that causes the #######;
a negative date)

produces:
-445236297867245000

As I said, you didn't set the formula with enough precision.

There are no cell references in the formula. the formula is like an
algebraic formula you would write using the variable x. Many of the numbers
are in scientific/exponential notation.

--
Regards,
Tom Ogilvy


"David" <[email protected]> wrote in message
news:[email protected]...
> Maybe I can start over. I have simplified the data as much as possible and
> will be literal with what I have come with on the chart and the equations.
> Sample data:
>
> Date Adj. Close*
> 01/03/05 10729.43
> 12/27/04 10776.13
> 12/20/04 10661.6
> 12/13/04 10638.32
> 12/06/04 10547.06
> ....
> 01/04/99 9643.32
>
> Equation showing on graph:
> y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
>
> Conversion:
> y = -6E-11^6 + 1E-05^5 - 1.2819^4 + 65247^3 - 2E+09^2 + 3E+13x - 2E+17
>
> Tried to make it more literal at this point, but several areas of
confusion:
> =.000011^6 + .5^5 - 1.2819^4+65247^3 - 9^2 + 3E(confused) +13*A2(confused,
x
> is a date, ref to cell?)- 2E(?)+17
>
> The 13x appears to be the only ref to a cell? The 3E & 2E I can not tell
> what numbers they are in ref to?
>
> When I copied out the equation you provided I end up with ########.....,
> which indicated the number might be REALLY big. The actual trend line on
the
> graph indicates the number is fairly close to 10729, which is the last
close
> for the date 1/3/05. Maybe it is slightly larger.
>
> "Tom Ogilvy" wrote:
>
> > Assume you values are in A2
> >
> > Then you would modify what you copied to
> >
> > = -6E-11*A2^6 + 1E-05*A2^5 - 1.2819*A2^4 + 65247*A2^3 - 2E+09*A2^2 +
> > 3E+13*A2 - 2E+17
> >
> > I paste that in the formula bar
> >
> > and get a result. However, you need the to select the trendline formula
and
> > format it to display more precision.
> >
> > --
> > Regards,
> > Tom Ogilvy
> > ..
> >
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Again,
> > >
> > > This is the literal equation:
> > > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> > >
> > > What I copied out and pasted was:
> > > =-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> > >
> > > The equation was corrected by Excel to:
> > > =-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17
> > >
> > > This gives me a Value Error, not recognizing the "E"? I also ws
expecting
> > > references to ranges and stuff and hoping I could copy a formula down
my
> > > sheet to arrive at specific values accross from Dates and data values.
> > >
> > > Thanks again.
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > one of the options on the graph is to display the equation of the
> > trendline.
> > > > You need to format the equation to show many decimal places of
> > precision.
> > > > Once you have the constants/coefficients associated with the terms
of
> > the
> > > > equation, you can calculate the predicted points.
> > > >
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "David" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Group,
> > > > > This is a little difficult to explain, but the underlying data is
> > simple,
> > > > so
> > > > > please bear with me. I have Dates and Closings for the Dow Jones
> > > > Industrial.
> > > > > Similar to below:
> > > > > Date Adj. Close*
> > > > > 01/03/05 10729.43
> > > > > 12/27/04 10776.13
> > > > > 12/20/04 10661.6
> > > > > 12/13/04 10638.32
> > > > > The above is easy to graph and I have automated the process, which
> > > > includes
> > > > > a 6th Order Polynomial Trend line added to the graph. What I am
trying
> > to
> > > > do
> > > > > is find the data points associated with the 6th Order Polynomial
Trend
> > > > line.
> > > > > It has simply been just too long since I have done this type of
> > > > statistics. I
> > > > > believe it may be necessary to create a new table to find these
data
> > > > points,
> > > > > which I am willing to do. I can calculate the sample mean, number
of
> > > > sample
> > > > > variables, sample variance, sample standard deviation, etc., but
it
> > has
> > > > just
> > > > > been too many years to bring the necessary statistical expertise
to
> > arrive
> > > > at
> > > > > the data points. I am trying to get a table that looks something
like
> > > > this:
> > > > > Date Adj. Close* Trend
> > > > > 01/03/05 10729.43 10730.25
> > > > > 12/27/04 10776.13 10750.31
> > > > > 12/20/04 10661.6 10765.03
> > > > > 12/13/04 10638.32 10750.00
> > > > > I have tried using some of the built in functions, but they do not
> > yield
> > > > the
> > > > > same data points that have been graphed by the 6th Order
Polynomial
> > Trend
> > > > > line. I have tried Trend and Forecast. I created a table many
years
> > ago,
> > > > > which I think calculated the data points, but it has simply been
to
> > many
> > > > > years and I have lost the statistical expertise. Any help would be
> > greatly
> > > > > appreciated.
> > > > > --
> > > > > David
> > > >
> > > >
> > > >
> >
> >
> >

David -

Two comments:

(1) Data analysis, in general: In my curve-fitting experience, there is
seldom appropriate justification for using a polynomial beyond first or
second order. (First order is linear, second order fits one bend, and third
order allows two bends.)

(2) Data analysis, using Excel: No matter what polynomial order you use,
particularly for second order and above, you need to use many significant
digits for calculations. Either increase decimals displayed in the fitted
trendline, or use LINEST or other worksheet functions to obtain the
coefficients..

- Mike

www.mikemiddleton.com

++++++++++++++++++++++

"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Group,
> This is a little difficult to explain, but the underlying data is simple,
> so
> please bear with me. I have Dates and Closings for the Dow Jones
> Industrial.
> Similar to below:
> Date Adj. Close*
> 01/03/05 10729.43
> 12/27/04 10776.13
> 12/20/04 10661.6
> 12/13/04 10638.32
> The above is easy to graph and I have automated the process, which
> includes
> a 6th Order Polynomial Trend line added to the graph. What I am trying to
> do
> is find the data points associated with the 6th Order Polynomial Trend
> line.
> It has simply been just too long since I have done this type of
> statistics. I
> believe it may be necessary to create a new table to find these data
> points,
> which I am willing to do. I can calculate the sample mean, number of
> sample
> variables, sample variance, sample standard deviation, etc., but it has
> just
> been too many years to bring the necessary statistical expertise to arrive
> at
> the data points. I am trying to get a table that looks something like
> this:
> Date Adj. Close* Trend
> 01/03/05 10729.43 10730.25
> 12/27/04 10776.13 10750.31
> 12/20/04 10661.6 10765.03
> 12/13/04 10638.32 10750.00
> I have tried using some of the built in functions, but they do not yield
> the
> same data points that have been graphed by the 6th Order Polynomial Trend
> line. I have tried Trend and Forecast. I created a table many years ago,
> which I think calculated the data points, but it has simply been to many
> years and I have lost the statistical expertise. Any help would be greatly
> appreciated.
> --
> David

Hi Tom,
I did get the same results after format change, but this is not at all what
I am trying to accomplish. The graph produced has underlying data points
associated with it for each date/data point. That is what I am trying to
find. Maybe I am back to the old stats book and trying to figure that all out
and creating a table, mean, std dev, R squared and all of that. A single
answer that is a negative number is not even close to what I am trying to
find. Thanks for your help.

"Tom Ogilvy" wrote:

> format the cell as number. (it defaults to date - that causes the #######;
> a negative date)
>
> produces:
> -445236297867245000
>
> As I said, you didn't set the formula with enough precision.
>
> There are no cell references in the formula. the formula is like an
> algebraic formula you would write using the variable x. Many of the numbers
> are in scientific/exponential notation.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Maybe I can start over. I have simplified the data as much as possible and
> > will be literal with what I have come with on the chart and the equations.
> > Sample data:
> >
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > 12/06/04 10547.06
> > ....
> > 01/04/99 9643.32
> >
> > Equation showing on graph:
> > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> >
> > Conversion:
> > y = -6E-11^6 + 1E-05^5 - 1.2819^4 + 65247^3 - 2E+09^2 + 3E+13x - 2E+17
> >
> > Tried to make it more literal at this point, but several areas of
> confusion:
> > =.000011^6 + .5^5 - 1.2819^4+65247^3 - 9^2 + 3E(confused) +13*A2(confused,
> x
> > is a date, ref to cell?)- 2E(?)+17
> >
> > The 13x appears to be the only ref to a cell? The 3E & 2E I can not tell
> > what numbers they are in ref to?
> >
> > When I copied out the equation you provided I end up with ########.....,
> > which indicated the number might be REALLY big. The actual trend line on
> the
> > graph indicates the number is fairly close to 10729, which is the last
> close
> > for the date 1/3/05. Maybe it is slightly larger.
> >
> > "Tom Ogilvy" wrote:
> >
> > > Assume you values are in A2
> > >
> > > Then you would modify what you copied to
> > >
> > > = -6E-11*A2^6 + 1E-05*A2^5 - 1.2819*A2^4 + 65247*A2^3 - 2E+09*A2^2 +
> > > 3E+13*A2 - 2E+17
> > >
> > > I paste that in the formula bar
> > >
> > > and get a result. However, you need the to select the trendline formula
> and
> > > format it to display more precision.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > > ..
> > >
> > >
> > > "David" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Again,
> > > >
> > > > This is the literal equation:
> > > > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> > > >
> > > > What I copied out and pasted was:
> > > > =-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> > > >
> > > > The equation was corrected by Excel to:
> > > > =-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17
> > > >
> > > > This gives me a Value Error, not recognizing the "E"? I also ws
> expecting
> > > > references to ranges and stuff and hoping I could copy a formula down
> my
> > > > sheet to arrive at specific values accross from Dates and data values.
> > > >
> > > > Thanks again.
> > > >
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > one of the options on the graph is to display the equation of the
> > > trendline.
> > > > > You need to format the equation to show many decimal places of
> > > precision.
> > > > > Once you have the constants/coefficients associated with the terms
> of
> > > the
> > > > > equation, you can calculate the predicted points.
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "David" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Group,
> > > > > > This is a little difficult to explain, but the underlying data is
> > > simple,
> > > > > so
> > > > > > please bear with me. I have Dates and Closings for the Dow Jones
> > > > > Industrial.
> > > > > > Similar to below:
> > > > > > Date Adj. Close*
> > > > > > 01/03/05 10729.43
> > > > > > 12/27/04 10776.13
> > > > > > 12/20/04 10661.6
> > > > > > 12/13/04 10638.32
> > > > > > The above is easy to graph and I have automated the process, which
> > > > > includes
> > > > > > a 6th Order Polynomial Trend line added to the graph. What I am
> trying
> > > to
> > > > > do
> > > > > > is find the data points associated with the 6th Order Polynomial
> Trend
> > > > > line.
> > > > > > It has simply been just too long since I have done this type of
> > > > > statistics. I
> > > > > > believe it may be necessary to create a new table to find these
> data
> > > > > points,
> > > > > > which I am willing to do. I can calculate the sample mean, number
> of
> > > > > sample
> > > > > > variables, sample variance, sample standard deviation, etc., but
> it
> > > has
> > > > > just
> > > > > > been too many years to bring the necessary statistical expertise
> to
> > > arrive
> > > > > at
> > > > > > the data points. I am trying to get a table that looks something
> like
> > > > > this:
> > > > > > Date Adj. Close* Trend
> > > > > > 01/03/05 10729.43 10730.25
> > > > > > 12/27/04 10776.13 10750.31
> > > > > > 12/20/04 10661.6 10765.03
> > > > > > 12/13/04 10638.32 10750.00
> > > > > > I have tried using some of the built in functions, but they do not
> > > yield
> > > > > the
> > > > > > same data points that have been graphed by the 6th Order
> Polynomial
> > > Trend
> > > > > > line. I have tried Trend and Forecast. I created a table many
> years
> > > ago,
> > > > > > which I think calculated the data points, but it has simply been
> to
> > > many
> > > > > > years and I have lost the statistical expertise. Any help would be
> > > greatly
> > > > > > appreciated.
> > > > > > --
> > > > > > David
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

I am not sure how many times I have to say it. The results are because you
have not set the formula to show enough precision. Doing anything but
setting the formula to display more precision is a waste of time.

=1.5555555*a1^6

will give a lot different answer than

=2*a1^6

With 01/03/05 in A1, the difference is like 1.4 x 10 to 27th power.

This is the type of problem you are having.

You need to increase the precision (number of digits) displayed in the
formula.

--
Regards,
Tom Ogilvy



"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
> I did get the same results after format change, but this is not at all
what
> I am trying to accomplish. The graph produced has underlying data points
> associated with it for each date/data point. That is what I am trying to
> find. Maybe I am back to the old stats book and trying to figure that all
out
> and creating a table, mean, std dev, R squared and all of that. A single
> answer that is a negative number is not even close to what I am trying to
> find. Thanks for your help.
>
> "Tom Ogilvy" wrote:
>
> > format the cell as number. (it defaults to date - that causes the
#######;
> > a negative date)
> >
> > produces:
> > -445236297867245000
> >
> > As I said, you didn't set the formula with enough precision.
> >
> > There are no cell references in the formula. the formula is like an
> > algebraic formula you would write using the variable x. Many of the
numbers
> > are in scientific/exponential notation.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > Maybe I can start over. I have simplified the data as much as possible
and
> > > will be literal with what I have come with on the chart and the
equations.
> > > Sample data:
> > >
> > > Date Adj. Close*
> > > 01/03/05 10729.43
> > > 12/27/04 10776.13
> > > 12/20/04 10661.6
> > > 12/13/04 10638.32
> > > 12/06/04 10547.06
> > > ....
> > > 01/04/99 9643.32
> > >
> > > Equation showing on graph:
> > > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x - 2E+17
> > >
> > > Conversion:
> > > y = -6E-11^6 + 1E-05^5 - 1.2819^4 + 65247^3 - 2E+09^2 + 3E+13x - 2E+17
> > >
> > > Tried to make it more literal at this point, but several areas of
> > confusion:
> > > =.000011^6 + .5^5 - 1.2819^4+65247^3 - 9^2 + 3E(confused)
+13*A2(confused,
> > x
> > > is a date, ref to cell?)- 2E(?)+17
> > >
> > > The 13x appears to be the only ref to a cell? The 3E & 2E I can not
tell
> > > what numbers they are in ref to?
> > >
> > > When I copied out the equation you provided I end up with
########.....,
> > > which indicated the number might be REALLY big. The actual trend line
on
> > the
> > > graph indicates the number is fairly close to 10729, which is the last
> > close
> > > for the date 1/3/05. Maybe it is slightly larger.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Assume you values are in A2
> > > >
> > > > Then you would modify what you copied to
> > > >
> > > > = -6E-11*A2^6 + 1E-05*A2^5 - 1.2819*A2^4 + 65247*A2^3 - 2E+09*A2^2 +
> > > > 3E+13*A2 - 2E+17
> > > >
> > > > I paste that in the formula bar
> > > >
> > > > and get a result. However, you need the to select the trendline
formula
> > and
> > > > format it to display more precision.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > > ..
> > > >
> > > >
> > > > "David" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Again,
> > > > >
> > > > > This is the literal equation:
> > > > > y = -6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x -
2E+17
> > > > >
> > > > > What I copied out and pasted was:
> > > > > =-6E-11x6 + 1E-05x5 - 1.2819x4 + 65247x3 - 2E+09x2 + 3E+13x -
2E+17
> > > > >
> > > > > The equation was corrected by Excel to:
> > > > > =-E6-11*6+E1-5*5-1.2819*4+65247*3-E2+9*2+E3+13*-E2+17
> > > > >
> > > > > This gives me a Value Error, not recognizing the "E"? I also ws
> > expecting
> > > > > references to ranges and stuff and hoping I could copy a formula
down
> > my
> > > > > sheet to arrive at specific values accross from Dates and data
values.
> > > > >
> > > > > Thanks again.
> > > > >
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > one of the options on the graph is to display the equation of
the
> > > > trendline.
> > > > > > You need to format the equation to show many decimal places of
> > > > precision.
> > > > > > Once you have the constants/coefficients associated with the
terms
> > of
> > > > the
> > > > > > equation, you can calculate the predicted points.
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > > "David" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Group,
> > > > > > > This is a little difficult to explain, but the underlying data
is
> > > > simple,
> > > > > > so
> > > > > > > please bear with me. I have Dates and Closings for the Dow
Jones
> > > > > > Industrial.
> > > > > > > Similar to below:
> > > > > > > Date Adj. Close*
> > > > > > > 01/03/05 10729.43
> > > > > > > 12/27/04 10776.13
> > > > > > > 12/20/04 10661.6
> > > > > > > 12/13/04 10638.32
> > > > > > > The above is easy to graph and I have automated the process,
which
> > > > > > includes
> > > > > > > a 6th Order Polynomial Trend line added to the graph. What I
am
> > trying
> > > > to
> > > > > > do
> > > > > > > is find the data points associated with the 6th Order
Polynomial
> > Trend
> > > > > > line.
> > > > > > > It has simply been just too long since I have done this type
of
> > > > > > statistics. I
> > > > > > > believe it may be necessary to create a new table to find
these
> > data
> > > > > > points,
> > > > > > > which I am willing to do. I can calculate the sample mean,
number
> > of
> > > > > > sample
> > > > > > > variables, sample variance, sample standard deviation, etc.,
but
> > it
> > > > has
> > > > > > just
> > > > > > > been too many years to bring the necessary statistical
expertise
> > to
> > > > arrive
> > > > > > at
> > > > > > > the data points. I am trying to get a table that looks
something
> > like
> > > > > > this:
> > > > > > > Date Adj. Close* Trend
> > > > > > > 01/03/05 10729.43 10730.25
> > > > > > > 12/27/04 10776.13 10750.31
> > > > > > > 12/20/04 10661.6 10765.03
> > > > > > > 12/13/04 10638.32 10750.00
> > > > > > > I have tried using some of the built in functions, but they do
not
> > > > yield
> > > > > > the
> > > > > > > same data points that have been graphed by the 6th Order
> > Polynomial
> > > > Trend
> > > > > > > line. I have tried Trend and Forecast. I created a table many
> > years
> > > > ago,
> > > > > > > which I think calculated the data points, but it has simply
been
> > to
> > > > many
> > > > > > > years and I have lost the statistical expertise. Any help
would be
> > > > greatly
> > > > > > > appreciated.
> > > > > > > --
> > > > > > > David
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >