Solved

# Excel Interpet Statistical Function converted to VB6

Posted on 2011-02-22
Medium Priority
510 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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.