Analysing data

[Steven Davidson]

Is there an Access equivalent of the LINEST function in
Excel (which calculates the statistics for a line by using
the "least squares" method to calculate a straight line
that best fits the data).

A few functions that combine to do the same job would be
OK.

Can anyone help?.

 

[Henry Smith]

To give you a most positive answer, Access can perform most any mathematical
calculation. The problem is the mathematical operations available within
Access and VBA tend to be on the fundamental side. So to perform high level
mathematics within Access requires a bit of integrating together some basic
mathematic functions to achieve a higher level function to get to the answer
you want. VBA and Access provide many math functions (average, product-sum,
product, addition, subtraction, division, Boolean math, integer math, to
name a few), so to achieve your answer you have to break down the high level
math you are trying to achieve to more simple steps. After you break down
the process you can apply the math functions available to achieve your
answer. Look up in the VBA and Access help files for the various basic math
functions that will combine to meet the functions available in Excel. I
have found that VBA and Access provide almost all the math capabilities that
Excel provides. Perhaps in somewhat less convenient form. Understanding
how Excel performs the math will help you derive a solution in Access.

Cheers,
Henry

 

[PC Datasheet]

Steven,

I can do this for you! If you would like my help, contact me at the email
address below.

 

[Adrian Jansen]

There was just posted a note on how to call the Excel functions from Access:

Microsofts article 198571, Example 2 shows how to call the Excel Chi-Squared
function.
Example 2 code adjusted for Access 2002

Sub xlMedian()
Dim objExcel As Excel. Application
Set objExcel = CreateObject("Excel.Application")
MstBox objExcel.Application.WorksheetFunction.ChiInv(0.05, 10)
objExcel.Quit
Set objExcel = Nothing
End Sub

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control