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