Yes, I did in 1998.
So I checked it again against:
http://www.bbc.co.uk/education/asgur...inciples.shtml
----
BBC example:
x y
1 1
3 7
Simplifying this expression:
y - 1 = 3 × 1.179 = 3.537 Implies that y = 4.537
----
I created my data table and entered
Data
X Y
1.0000 1.0000
3.0000 7.0000
2.1790
----
I ran my code and got:
X Y
1.0000 1.0000
3.0000 7.0000
2.1790 4.5370
****
As I explained this code was written many years ago for Access 97,
maybe even Access 95.
Because newer versions of Access often have ADO referenced before DAO
minor modifications may be required as follows:
Dim m As Currency, b As Currency
Sub sInterpolation( )
Dim strSQL As String
Call sRegressionLine
strSQL = "UPDATE Data SET Data.Y = " & _
"(" & m & ")" & " * Data.X + (" & b & ")" & _
" WHERE (((Data.Y) Is Null) AND ((Data.X) Is Not Null));"
Debug.Print strSQL
CurrentDb.Execu te strSQL
Debug.Print m
Debug.Print b
End Sub
Sub sRegressionLine ()
Dim dbs As DAO.Database, rcs As DAO.Recordset
Set dbs = CurrentDb()
Set rcs = dbs.OpenRecords et("SELECT Sum(Data.X) AS SumX, " & _
"Sum([X]*[X]) AS SumXX, Sum(Data.Y) AS SumY, Sum([X]*[Y]) AS SumXY," &
_
"Count(*) AS N FROM Data " & _
"WHERE (((Data.X) Is Not Null) AND ((Data.Y) Is Not Null));")
m = (rcs!N * rcs!SumXY - rcs!SumX * rcs!SumY) / (rcs!N * rcs!SumXX -
rcs!SumX ^ 2)
b = (rcs!SumY * rcs!SumXX - rcs!SumX * rcs!SumXY) / (rcs!N * rcs!SumXX
- rcs!SumX ^ 2)
End Sub
