What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.

The x values are in days, but 1/1/2001 is not day one. Day one is 1/1/1900,
and 1900 was treated as a leap year. If you want to make 1/1/2001 day one,
use the date minus 12/31/2007 for your x values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Aaron Davies" wrote in message
...
What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.

Sorry, that's what I meant by "the integer of the date". 1/1/2001 is
36892, and using 36892 for x in the equation given by Excel yields
1.361956e+09, not 1.

On Feb 5, 12:13 pm, "Jon Peltier"
wrote:
The x values are in days, but 1/1/2001 is not day one. Day one is 1/1/1900,
and 1900 was treated as a leap year. If you want to make 1/1/2001 day one,
use the date minus 12/31/2007 for your x values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Aaron Davies" wrote in message

...

What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.

I think I figured out the problem: sixth-order polynomials are just
wrong. A sixth-order trend looks fine on my sample data below, but the
equation is nonsense. With my real data, a fifth-order equation works
fine.

On Feb 5, 12:51 pm, Aaron Davies wrote:
Sorry, that's what I meant by "the integer of the date". 1/1/2001 is
36892, and using 36892 for x in the equation given by Excel yields
1.361956e+09, not 1.

On Feb 5, 12:13 pm, "Jon Peltier"
wrote:

The x values are in days, but 1/1/2001 is not day one. Day one is 1/1/1900,
and 1900 was treated as a leap year. If you want to make 1/1/2001 day one,
use the date minus 12/31/2007 for your x values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Aaron Davies" wrote in message

...

What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.

Two points:

Make sure the trendline formula is viewed with sufficient significant
figures. Apply a number format of 0.000000000000000E00.

A poly fit may be useful for making a line that kind of follows the points,
but in most cases there is no physical basis for such a high order equation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Aaron Davies" wrote in message
...
I think I figured out the problem: sixth-order polynomials are just
wrong. A sixth-order trend looks fine on my sample data below, but the
equation is nonsense. With my real data, a fifth-order equation works
fine.

On Feb 5, 12:51 pm, Aaron Davies wrote:
Sorry, that's what I meant by "the integer of the date". 1/1/2001 is
36892, and using 36892 for x in the equation given by Excel yields
1.361956e+09, not 1.

On Feb 5, 12:13 pm, "Jon Peltier"
wrote:

The x values are in days, but 1/1/2001 is not day one. Day one is
1/1/1900,
and 1900 was treated as a leap year. If you want to make 1/1/2001 day
one,
use the date minus 12/31/2007 for your x values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Aaron Davies" wrote in message

...

What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known
points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.

I was viewing it at maximum-precision scientific notation (thirty
places). All the constants were displayed at fifteen significant
figures, which sounds about right for normal sixty-four-bit precision
floating point math.

As for the appropriateness of the model, it follows the curve quite
nicely when projected outward. This data set seems to be somewhere
between polynomial and exponential, and the fifth-order polynomial
gives a much better R^2 (0.9991) than the exponential model (0.9337).

On Feb 5, 2:02 pm, "Jon Peltier"
wrote:
Two points:

Make sure the trendline formula is viewed with sufficient significant
figures. Apply a number format of 0.000000000000000E00.

A poly fit may be useful for making a line that kind of follows the points,
but in most cases there is no physical basis for such a high order equation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Aaron Davies" wrote in message

...

I think I figured out the problem: sixth-order polynomials are just
wrong. A sixth-order trend looks fine on my sample data below, but the
equation is nonsense. With my real data, a fifth-order equation works
fine.

On Feb 5, 12:51 pm, Aaron Davies wrote:
Sorry, that's what I meant by "the integer of the date". 1/1/2001 is
36892, and using 36892 for x in the equation given by Excel yields
1.361956e+09, not 1.

On Feb 5, 12:13 pm, "Jon Peltier"
wrote:

The x values are in days, but 1/1/2001 is not day one. Day one is
1/1/1900,
and 1900 was treated as a leap year. If you want to make 1/1/2001 day
one,
use the date minus 12/31/2007 for your x values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Aaron Davies" wrote in message

...

What are the units of x in a date-based trend equation? If I take a
simple integer-integer dataset,

1 1
2 4
3 9
4 16

and fit a second-order polynomial to it, I get y = 1x^2 +
5.6843418860808e-14, which is reasonably accurate at the known
points.
If, however, I have dates in my x axis, I can't figure out the units
of the equation.

1/1/2001 1
1/2/2001 4
1/3/2001 9
1/4/2001 16
1/5/2001 25
1/6/2001 36
1/7/2001 49
1/8/2001 64
1/9/2001 81
1/10/2001 100

yields y = x^2 + 7.3782x + 1.360945881, but what values for x yield
the y's I already know? It's not the integer of the date--that series
starts 1.361292e+09, 1.361366e+09, 1.361439e+09.