By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Replicating excel function LinEst in Access VBA

P: n/a
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

Aug 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Aug 24 '06 #2

P: n/a
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?

Aug 25 '06 #3

P: n/a
bump... any more input on this?

Aug 29 '06 #4

P: n/a
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.

Aug 30 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.