Linear regression using dates in excel and access

[elohelae]

Good afternoon. I have been using the slope, intercept and linest functions to help me with linear regression and have been using the dsimpleregress function in msaccess. Everything works great for numbers, however i have a set of data where the x access is dates. The function does not seem to like dates. Has anyone had any experience of this to help find a way for the functions to accomodate this.

The intervals where the data is taken is pretty regiment, so i can create an index everytime the data is entered and multiply that out by the time interval and use the index instead, however i wonder if there is a cleaner way.

Your help would be appreciated.


​

 

[SkipVought]

Of course you realize that Dates are just numbers, the number of days since Dec 31 1899.

So maybe the fact that these are relatively big numbers on the order of 40k, might be an issue.

What does the HELP indicate on this function?

 

[elohelae]

thanks for the reply, the built in function does not handle the date field. i do not want to go through changing the format of the field back and forth if i can help it to accomodate the function. that process is relatively easy in excel, not so much in msacces when doing queries. However i will take you suggestion in mind and i look for anything more to help with the date field in its current format

 

[combo]

Could you give an example what does not work? I did a simple test with table with date field, without any conversion:
Table:
RegressionTest
[pre] x y
01-02-2015 2
17-04-2015 4
22-03-2015 5
18-05-2015 7
11-07-2015 11
31-03-2015 5[/pre]
Support query 1:
AxgXY
[pre]SELECT Avg(RegressionTest.x) AS AvgX, Avg(RegressionTest.y) AS AvgY
FROM RegressionTest;[/pre]
Support query 2:
supp
[pre]SELECT Sum(([x]-[AvgX])*([y]-[AvgY])) AS Supp1, Sum((([x]-[AvgX])^2)) AS Supp2
FROM RegressionTest, AxgXY;[/pre]
Result query 2:
RegressionResult
[pre]SELECT [Supp1]/[Supp2] AS b, [AvgY]-*[AvgX] AS a
FROM AxgXY, supp;[/pre]



combo