Anomolous behaviour of slope function.

[Guest]

Xcel XP. I frequently chart a variable (y) against a continous sequence of
dates(x). I use the resident slope function in xcel to obtain a slope of a
linear regression of the data. I also plot the curve and print-out the
formula for the curve in the form: y=mx+b, where m should equal the same
value as that derived from the resident slope function. This does occur
when I set the x axis(the date component) as "category", in addition when I
do this "b" equals the proper y intercept. When I set the x axis as "time
scale" the y intercept is displayed as an unusually large value and there is
a small discrepancy in the slope(m). Can any one tell me why this
discrepancy occurs.
Thanks
Chas

 

[MrShorty]

Without your data, it's kind of hard to answer real specifically, but I
suspect it has to do with exactly what the regression is using for x.

The large difference in intercept is probably because, as a category
axis, the regression doesn't use the values on the axis, but instead
uses 1,2,3,... for x. As a time value axis, the regression will use
the values on the axis, where the values on the axis will be days since
Jan 1, 1900 (default setting) or Jan 4 1904 (with 1904 date setting
selected). An x of 0 has two very different meanings. As a category
axis, day 0 is the day before the first entry. As a time scale axis,
day 0 is very early in the 20th century depending on which reference
date is selected.

You don't say how large the small discrepancy in the slope is, but it
is often mentioned here that the worksheet functions use a slightly
different algorithm to perform the regression than the chart trendline
function, which could explain the slight difference. There's always
round off error, too, that could come into play.

 

[Guest]

Thanks MrShorty;
From your explanation it appears that the category function is more
applicable to my work, as I am more interested in the equal spacing of the
values than the dates.
It looks like the chart function uses the spacing of the data on the x-axis
and the worksheet function use the values in the column holding the dates,
which fits your explanation.
Thanks
chas