435,561 Members | 3,083 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 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
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. 