Excel Trend function in Access

[carlmack]

Hi all,
I trying to use the Excel Trend function in Access.

I found some code that works for the Median function

Public Function getMedian(ParamArray aNum())
getMedian = WorksheetFunction.median(aNum)
End Function

The getmedian then works with commas between the values. The trend function requires a range of cells so I am not sure how to start.

Anybody any ideas ?

 

[grahamw]

Hi.
I'll take a look.
My limited experience of access has found it be be limited when requiring statistical functions. Im sure there is no equivalent to TREND in access.
I usually have to write my own functions for stats calculations.
Ive seen Mid and DAvg- where did you find the median function?
Regards
Graham

 

[carlmack]

Graham,

I added the Excel median function into an Access module. Actually I just copied this from somebody else.

I inserted a module and pasted the following.

Public Function getMedian(ParamArray aNum()) 
getMedian = WorksheetFunction.median(aNum) 
End Function

Whilst in the VB window I selected tools references and checked the Microsoft Excel object library.

Then I used it in queries eg. getmedian([a],,[c])

The problem is that the Excel Trend function is a bit more complicated and I am not sure how to set the ParamArray part.

Regards
Carl

 

[grahamw]

Carl.
This looks pretty useful. Once ive investigated this more I'll
see if I can make sense of the TREND function for access.
Regards
Graham