Solved

Excel Interpet Statistical Function converted to VB6

Posted on 2011-02-22
6
424 Views
Last Modified: 2013-11-26
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

Open in new window

0
Comment
Question by:xsawkins
  • 3
6 Comments
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 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

by:roger_karam
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

by:roger_karam
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

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

Expert Comment

by:mlmcc
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now