469,306 Members | 1,901 Online

# Creating a slope formula for Access 1
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)
May 26 '11 #1
9 7422 NeoPa
32,173 Expert Mod 16PB
It is possible to use Excel Functions in Access. Hopefully this will help.
May 26 '11 #2
Rabbit
12,516 Expert Mod 8TB
Slope runs a linear regression on the data. It is most likely returning the Pearson coefficient. The formula for the Pearson coefficent is May 27 '11 #3
Rabbit
12,516 Expert Mod 8TB
I was wrong. According to the microsoft website, it uses a slightly different formula. http://office.microsoft.com/en-us/ex...005209264.aspx
May 27 '11 #4
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
1. Dim aintYs(1 To 5) As Integer
2. Dim aintXs(1 To 5) As Integer
3. Dim intCtr As Integer
4. 'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
5. Dim objExcel As Excel.Application
6.
7. Set objExcel = CreateObject("Excel.Application")
8.
9. For intCtr = 1 To 5
10.   aintYs(intCtr) = intCtr
11.     Select Case intCtr
12.       Case 1
13.         aintXs(intCtr) = 6271
14.       Case 2
15.         aintXs(intCtr) = 6817
16.       Case 3
17.         aintXs(intCtr) = 9202
18.       Case 4
19.         aintXs(intCtr) = 7629
20.       Case 5
21.         aintXs(intCtr) = 11721
22.     End Select
23. Next
24.
25. MsgBox "The Slope of know Ys and Xs is: " & objExcel.Application.Slope(aintYs, aintXs)
May 28 '11 #5
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
1. 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?
May 28 '11 #6
8,800 Expert 8TB
@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.
May 28 '11 #7
NeoPa
32,173 Expert Mod 16PB
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).
May 28 '11 #8
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
1. Dim aintYs(6) As Integer
2. Dim aintXs(6) As Integer
3. Dim intCtr As Integer
4.
5. Dim objExcel As Excel.Application
6. Set objExcel = CreateObject("Excel.Application")
7.
8. For intCtr = 0 To 6
9.   Select Case intCtr
10.     Case 0
11.       aintYs(intCtr) = 2
12.       aintXs(intCtr) = 6
13.     Case 1
14.       aintYs(intCtr) = 3
15.       aintXs(intCtr) = 5
16.     Case 2
17.       aintYs(intCtr) = 9
18.       aintXs(intCtr) = 11
19.     Case 3
20.       aintYs(intCtr) = 1
21.       aintXs(intCtr) = 7
22.     Case 4
23.       aintYs(intCtr) = 8
24.       aintXs(intCtr) = 5
25.     Case 5
26.       aintYs(intCtr) = 7
27.       aintXs(intCtr) = 4
28.     Case 6
29.       aintYs(intCtr) = 5
30.       aintXs(intCtr) = 4
31.   End Select
32. Next
33.
34. 'Both approaches will generate the same Results
35. MsgBox "The ADezii Slope of known Ys and Xs is: " & _
36.         FormatNumber(objExcel.Application.Slope(aintYs, aintXs), 6)
37. MsgBox "The NeoPa Slope of known Ys and Xs is: " & _
38.         FormatNumber(Excel.WorksheetFunction.Slope(aintYs, aintXs), 6)
May 28 '11 #9
NeoPa
32,173 Expert Mod 16PB
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).
May 28 '11 #10