Simple data/Hard statistics

[David]

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.

 

[Tom Ogilvy]

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.

 

[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.

 

[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]

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.

 

[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]

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.

 

[Michael R Middleton]

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]

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]

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.