Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel Interpet Statistical Function converted to VB6

Posted on 2011-02-22
Medium Priority
435 Views
I have and Excel spread sheet that has cells that containing the following values:
Known Y   Known X
2                6
3                5
9                11
1                7
8                5

In a cell I use the INTERCEPT function which Calculates the point at which a line will intersect the y-axes by using a best fit regression line plotted through the known x-values and y-values.

I need to do this in VB6 can you update the code below so that it will calculate the line of Intercept?

When it works correctly the Intercept value should equal 0.048387

Thanks
``````Public Function Intercept() As Double

Dim KnownY(1 To 5) As Double
Dim KnownX(1 To 5) As Double

KnownY(1) = 2
KnownY(2) = 3
KnownY(3) = 9
KnownY(4) = 1
KnownY(5) = 8

KnownX(1) = 6
KnownX(2) = 5
KnownX(3) = 11
KnownX(4) = 7
KnownX(5) = 5

Intercept =

End Function
``````
0
Question by:xsawkins
• 3

LVL 5

Accepted Solution

roger_karam earned 2000 total points
ID: 34957128
I don't know if you can import the functions into regular VB, so here is the math solution for you following excel's explanation for the intercept function. You could also make it so it takes larger fields (more than 5 knowns) but this will do for your example:

Public Function Intercept() As Double

Dim KnownY(1 To 5) As Double
Dim KnownX(1 To 5) As Double
Intercept = 0

KnownY(1) = 2
KnownY(2) = 3
KnownY(3) = 9
KnownY(4) = 1
KnownY(5) = 8

KnownX(1) = 6
KnownX(2) = 5
KnownX(3) = 11
KnownX(4) = 7
KnownX(5) = 5

Dim dAverageY As Double
Dim dAverageX As Double
Dim dSlope As Double
Dim dSlopeTop As Double
Dim dSlopeBottom As Double

dAverageY = 0
dAverageX = 0
dSlope = 0

For i = 1 To 5

dAverageY = dAverageY + KnownY(i)
dAverageX = dAverageX + KnownX(i)

Next i

dAverageY = dAverageY / 5
dAverageX = dAverageX / 5

For i = 1 To 5

dSlopeTop = dSlopeTop + ((dAverageX - KnownX(i)) * (dAverageY - KnownY(i)))
dSlopeBottom = dSlopeBottom + (dAverageX - KnownX(i)) ^ 2

Next i

dSlope = dSlopeTop / dSlopeBottom

For i = 1 To 5

Intercept = Intercept + KnownY(i) - dSlope * KnownX(i)

Next i

Intercept = Intercept / 5

End Function
0

LVL 5

Expert Comment

ID: 34957164
oh, my bad. if you are using VB 6, you need to switch the "Next i" statements for just "loop"

-RK
0

LVL 5

Expert Comment

ID: 34957637
of course, if i totally understood you wrong and you are doing this in vba, just use:

intercept = Application.WorksheetFunction.Intercept(KnownY(), KnownX)
0

Author Comment

ID: 34964849
Thanks for the input, let try this, this afternoon and I'll get back to you.
0

LVL 101

Expert Comment

ID: 35213397
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month15 days, 14 hours left to enroll