Creating a slope formula for Access

[RhythmlinkSam]

Hi everyone,

I need some help with a slope formula.

My company uses a spreadsheet where one line uses the SLOPE formula. I found from my research on the internet that there is no similar function type in Access 2003.

I found a thread in BYTES from a kux who asked to, Calculate slope of a data set or trend line

A <email removed> answered kux question, but I have no clue what his formula meant. I had some others look at the formula and they left scratching their heads.

I just want to find out how to write a formula which will give me the slope.

Below is the information that is on our Excel spreadsheet which covers a 5 month period and the sales of two different products. At the bottom of each is the SLOPE calculated from Excel.

Thank you all for your help

Product #1

Period Sales

1 5
2 2
3 40
4
5 10

Slope 3 (rounded)


Product #2

Period Sales

1 6271
2 6817
3 9202
4 7629
5 11721

Slope 1171.2 (rounded)

[NeoPa]

It is possible to use Excel Functions in Access. Hopefully this will help.

[Rabbit]

Slope runs a linear regression on the data. It is most likely returning the Pearson coefficient. The formula for the Pearson coefficent is

You can find more info on the formula here http://en.wikipedia.org/wiki/Pearson...on_coefficient

[Rabbit]

I was wrong. According to the microsoft website, it uses a slightly different formula.

http://office.microsoft.com/en-us/ex...005209264.aspx

[ADezii]

As hinted to by NeoPa in Post #2, you can:

1. Collect your Access Data and populate 2 Arrays representing your known Y and X Values.
2. Pass those Arrays to Excel's Slope() Function for processing via Automation Code.
3. I know of no method to do this entirely within the confines of Access.
4. Using Data from your 2nd Example:
   

   Dim aintYs(1 To 5) As Integer
   Dim aintXs(1 To 5) As Integer
   Dim intCtr As Integer
   'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
   Dim objExcel As Excel.Application
    
   Set objExcel = CreateObject("Excel.Application")
    
   For intCtr = 1 To 5
     aintYs(intCtr) = intCtr
       Select Case intCtr
         Case 1
           aintXs(intCtr) = 6271
         Case 2
           aintXs(intCtr) = 6817
         Case 3
           aintXs(intCtr) = 9202
         Case 4
           aintXs(intCtr) = 7629
         Case 5
           aintXs(intCtr) = 11721
       End Select
   Next
    
   MsgBox "The Slope of know Ys and Xs is: " & objExcel.Application.Slope(aintYs, aintXs)

[NeoPa]

There may be some problems with that ADezii :-(

First, the article is about using another application (but not automation). It is there to illustrate that such usage of functions is much more straightforward than having to set up automation and actually open up an instance of the related application that needs to be closed after use. A reference is all that is required.

Next, although the OP never says so explicitly in the question (which is certainly unfortunate), the SLOPE function is an Excel Worksheet function specifically (those that can be used in cell formulas rather than those available to VBA). As such I think your line #25 would fail (I tried it and it failed for me). What you need is a reference to Excel's WorksheetFunction object :

Excel.WorksheetFunction.Slope(...)

No Excel application required.

While referring to your code, isn't it a shame that VBA has such trouble with handling arrays. The Array() function returns an array, but the returned result cannot be assigned directly to a DIMmed array variable. How inconvenient is that?

[ADezii]

@NeoPa - I executed both Versions of the Code (mine and yours) against Excel's Sample Data and Results in the Help File. When rounded to 6 significant Digits, the results are exactly the same. I cannot see where it is failing on your end.

[NeoPa]

I didn't express that quite accurately ADezii. I meant I couldn't even find the routine at that address. Intellisense and the Object Browser both indicated that it was only found as a property of the .WorksheetFunctions object (not within the Excel.Application object). Are you saying that regardless of that, the code does actually execute (I didn't want to set up the whole environment just to check something which the Intellisense + Object Browser indicated was not found in that place but I will later anyway as you've called that into question)?

BTW. The Help system gave no clues as it didn't find it within VBA Help at all - Only from Excel itself. This further indicated for me that it shouldn't be available directly from the Excel.Application object. Still, only testing will tell (which I'll do as soon as I have a spare minute).

[ADezii]

Dim aintYs(6) As Integer
Dim aintXs(6) As Integer
Dim intCtr As Integer
 
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
 
For intCtr = 0 To 6
  Select Case intCtr
    Case 0
      aintYs(intCtr) = 2
      aintXs(intCtr) = 6
    Case 1
      aintYs(intCtr) = 3
      aintXs(intCtr) = 5
    Case 2
      aintYs(intCtr) = 9
      aintXs(intCtr) = 11
    Case 3
      aintYs(intCtr) = 1
      aintXs(intCtr) = 7
    Case 4
      aintYs(intCtr) = 8
      aintXs(intCtr) = 5
    Case 5
      aintYs(intCtr) = 7
      aintXs(intCtr) = 4
    Case 6
      aintYs(intCtr) = 5
      aintXs(intCtr) = 4
  End Select
Next
 
'Both approaches will generate the same Results
MsgBox "The ADezii Slope of known Ys and Xs is: " & _
        FormatNumber(objExcel.Application.Slope(aintYs, aintXs), 6)
MsgBox "The NeoPa Slope of known Ys and Xs is: " & _
        FormatNumber(Excel.WorksheetFunction.Slope(aintYs, aintXs), 6)

[NeoPa]

I got time to run your code ADezii. Obviously, you were absolutely correct in saying it worked. Excel.Application.Slope() also works of course, as well as objExcel.Slope().

Notwithstanding that, I still couldn't find any documented support of using it that way, no matter where I looked (within the application itself - I'm not saying I wouldn't have found anything on the web - it's here now after all).