Replicating excel function LinEst in Access VBA

[David_from_Chicago]

What I am trying to do is to simulate the LINEST functionality from
Excel in Access through VBA.

When I use LinEst in Excel I can get back five statistical results.
Here are is the formula array I use:

Slope {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}
SE {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}
RSquared {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}
F-Stat {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}
RegSS {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}

My first question is, do Excel functions exist to calculate each of
these statistics independently (not as part of the LINEST array)? So
far I have only found the equivalents for Slope and R-Squared ("=SLOPE"
and "=RSQ" respectively).

Secondly, what are the mathematical formulas that match each of these
Excel formulas? Again, I have only found the formula for slope thus
far which I can work easily into code (see snippet below).

>>>>>

strSQL = _
"SELECT Sum([x]) AS SumX, Sum([x]^2) AS SumXX, " & _
"Sum([y]) AS SumY, sum([y]^2) as SumYY, Sum([x]*[y]) AS SumXY,
Count(*) AS N " & _
"FROM data_table " & _
"WHERE ((([x]) Is Not Null) AND (([y]) Is Not Null));"

rs.Open strSQL, conn, adOpenStatic, adLockReadOnly
If rs.EOF = False Then
Slope = (rs!N * rs!SumXY - rs!SumX * rs!SumY) / (rs!N *
rs!SumYY - rs!SumY ^ 2)
b = (rs!SumYY * rs!SumX - rs!SumY * rs!SumXY) / (rs!N *
rs!SumYY - rs!SumY ^ 2)

>>>>>>>>e

For now, I'd like to set aside any debate as to whether these functions
calculate statistics accurately. What I want to do is duplicate the
Excel results I get within MS Access, and ideally without calling an
instance of Excel to do it.

Thanks in advance,
David_from_Chicago

[pietlinden]

you should be able to use the functions if you reference the right
library in your code. and then it would be like calling your own UDF in
a query.

[David_from_Chicago]

pietlinden wrote:

you should be able to use the functions if you reference the right
library in your code. and then it would be like calling your own UDF in
a query.

Certainly this should be one option. However, I believe the I need to
be able to calculate each LinEst statistic independently and I haven't
found these functions yet. Also, if possible, I would like to avoid
any calls to Excel to make this happen.

Any other ideas?

[David_from_Chicago]

bump... any more input on this?

[CDMAPoster]

David_from_Chicago wrote:

bump... any more input on this?

The definitions you need should be found at:

http://mathworld.wolfram.com

I love these kinds of problems so post back if you have any specific
questions regarding their implementation..

James A. Fortune
CD********@FortuneJames.com

There's a universe nearby
where thought is light
and love is sound.
There is another one too,
where joy's a storm.
And others.
And in a way,
they all fit together.