[RESOLVED] Linear Regression Projection from Known Point-VBForums
Results 1 to 14 of 14

Thread: [RESOLVED] Linear Regression Projection from Known Point

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Resolved [RESOLVED] Linear Regression Projection from Known Point

    Per title I want to project a regression line but use the previous data point NOT included in the dataset for calculating the regression line as an anchor such that the regression line starts at that previous data point. NOTE: If the previous data point needs to be included that is also acceptable.

    The following code calculates the regression line slope.
    One thought was to have the last point be the YIntercept, but
    not sure how -- or -- if the formula for the slope needs to be modified to have the previous point be the YIntercept?

    Given the formula Y = mx + b one would think that
    setting b = YIntercept = last data point, this would give the correct result.

    Any other solutions appreciated.

    Code:
    Private Function fLFit()
    'IN PROCESS  -- 20180319
    'Linear Regression 
    
        Dim i As Integer
        Dim n As Integer             '# Data Pts.    
        Dim SX As Double           'Sum X
        Dim SY As Double           'Sum Y
        Dim SX2 As Double         'Sum X Squared
        Dim SXY As Double         'Sum X * Y
        Dim SY As Double           'Sum Y
        Dim Num As Double        'Numerator of Fraction
        Dim Dem  As Double       'Denominator of Fraction
        
        n = CInt(msngInput1)
        
        SX = 0
        SY = 0
        
        For i = 1 To n
            SX = SX + X(i)
            SX2 = SX2 + X(i) ^ 2
            SY = SY + Y(i)
            SY2 = SY2 + Y(i) ^ 2
            SXY = SXY + X(i) * Y(i)
        Next i
     
        'Calculate Slope
        Num = (n * SXY) - (SX * SY)
        Dem = (n * SX2) - (SX * SX)
        Slope = Num / Dem
     
    End Function
    Last edited by vb6forever; Mar 20th, 2018 at 09:16 PM.

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,562

    Re: Linear Regression Projection from Known Point

    Since any (linear) regression-line always goes "through" an anchor-point which is located at the "gravity-center" (so to say):
    -> the point, which is given by the Avg(x(i)) and Avg(y(i))...
    then you already have a solution to your problem I'd think.

    Just calculate (for any new "Point-Group") that averaged center-point - and connect it with the End-Point of the previous group,
    by calculating the new slope (m_new) as:

    (cpAvgY - epPrevY)
    ----------------------------
    (cpAvgX - epPrevX)

    The only thing you have to choose how to handle (since the StartPoint is known, as well as the slope m_new),
    is, where you "let the whole thing end" (meaning: the calculation of the new "End-Point" for the current group).

    Olaf

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: Linear Regression Projection from Known Point

    Olaf, as always. thanks for your input:

    Unless I'm missing something in your comments my take is this:

    Whether the starting point "is" or "is not" included the the determination of the line
    there will be "by definition" a slope such that all points are minimized. This would mean
    that the line "most likely" will not pass through the starting point. If that slope is maintained
    in order to get the line to pass through the starting point -- and keep the same slope --
    all Y values -- which represent the line - would have to be adjusted upward or downward
    in order to get it to pass through the starting point.

    If we just adjusted the line to use the starting point and the center point of the line that
    represents the projected points, then the slope would change and the line would now NOT
    represent the minimum distance to all points.

    Hence, I don't think there is a solution to this problem.

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,562

    Re: Linear Regression Projection from Known Point

    Without a more concrete example, there is not much one can do.
    (So far, I don't really understand your scenario - you need to describe it a bit more).

    I only guessed (from your opener post) - that there might be "more than one set of points"...

    So, when you talk about "points"...:
    - Is - or is there not - more than one such "set of points"?
    - If there is only "one set of points" - what do you mean with "previous data point"?

    As said, a little example (perhaps based on a point-set of 3 x/y-Pairs, maybe accompanied by a little Image)
    would be helpful, to understand better, "what you have as Input" - and "what you want as Output"...

    Olaf

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: Linear Regression Projection from Known Point

    Here's the situation.

    1) We have sales data going back one year.
    2) The last point for the prior year would be 12/31/yyyy.
    3) We are now into a new year and have daily sales data just for January.
    4) So what I wanted to do is draw a regression line just for January but
    have that line start (pass through) the 12/31/yyyy point.

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,562

    Re: Linear Regression Projection from Known Point

    Quote Originally Posted by vb6forever View Post
    Here's the situation.

    1) We have sales data going back one year.
    2) The last point for the prior year would be 12/31/yyyy.
    3) We are now into a new year and have daily sales data just for January.
    4) So what I wanted to do is draw a regression line just for January but
    have that line start (pass through) the 12/31/yyyy point.
    Then I did not misunderstand you in my first posting...

    A line can be defined exactly, when we have two points it has to "pass through":
    - the first point (since that is your requirement) would be: LastX_PriorMonth, LastY_PriorMonth (x=date, y=Value)
    - the second point would be: SumX(AllPointsCurMonth) / PCountCurMonth, SumY(AllPointsCurMonth) / PCountCurMonth

    Can you explain in more detail, why that suggestion can't be applied?

    Olaf

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: Linear Regression Projection from Known Point

    Can you explain in more detail, why that suggestion can't be applied?
    Your suggestion seems logical as the average "should" represent a point on the regression line
    half way up the line. The only possible issue I can see off hand is that by taking an average,
    the X or Y point(s) may rest between actual plotted data points. For example if X is from 1 to 8, the average of X would be 4.5. One would then need to somehow determine how to plot that point such as changing the X graph scale.

  8. #8
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    4,327

    Re: Linear Regression Projection from Known Point

    Quote Originally Posted by vb6forever View Post
    Your suggestion seems logical as the average "should" represent a point on the regression line
    half way up the line. The only possible issue I can see off hand is that by taking an average,
    the X or Y point(s) may rest between actual plotted data points. For example if X is from 1 to 8, the average of X would be 4.5. One would then need to somehow determine how to plot that point such as changing the X graph scale.
    I don't see how that would be an issue in a typical graph.
    It is highly unlikely that a graph of the values with an X range of 1 to 8 would only be drawn 8 pixels wide. If the graph was 16 pixels wide then you could hit that 4.5 right on the pixel.

    But, most likely the graph would be hundreds of pixels wide, so a small range point could be plotted with a fair amount of precision. In most typical graphs, you don't extract the exact value of a data point from the graph as there is always some amount of rounding going on since you have discrete pixels to draw with. Usually you may be dealing with larger ranges than you have pixels, so the points plotted will encompass the actual data points since the actual data point is sub pixel in placement.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: Linear Regression Projection from Known Point

    Olaf:
    Forgot to thank you for your solution. Thanks.

    passel:
    Thanks for responding.

    Your comment is well taken.
    My comment was intended "just to point out" that the computed point may lie between
    the actually plotted data points and "may" present a problem. For example if the actual
    data points are contained within an array, and that array is being looped to plot actual
    points at some increment X, adding the computed (new) points to that array most likely would result
    in the line plotted incorrectly.

  10. #10
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    4,327

    Re: [RESOLVED] Linear Regression Projection from Known Point

    That is true, but you wouldn't normally add a linear regression computed point to your data. You would plot the linear regression plot, generally only using two points, the beginning and end of a particular segment, or your data points plot, or both. You wouldn't merge them.

    But I could be wrong. If the array represented fixed deltas in X (so didn't need to contain X), then if the linear regression midpoint did agree with a specific X, would you clobber the data point at that index in the array. If you inserted it, it would shift all your other X values. I guess I'm not clear on the problem, so will just let it be. I've usually worked with data that had both X and Y values, so the delta between sample points didn't need to be a fixed interval

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: [RESOLVED] Linear Regression Projection from Known Point

    you wouldn't normally add a linear regression computed point to your data.
    Not my intent. Sorry if I implied otherwise and misled you.

    Was just trying to point out:
    If one is looking at sales data, the data set for the X Axis most likely is time, which would be a fixed increment.
    Using Olaf's suggestion and using the last data point and the averages for the datasets (both X and Y)
    then the X Axis point - as previously discussed - may lie between plotted X Axis Points.
    If one just draws a line (using two points), then -- as previously discussed -- one would have to pick a pixel
    that lies halfway between the plotted points. If ones data loops are based on plotting at the X axis
    increment, then some other function needs to be used to find that X point.

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,562

    Re: [RESOLVED] Linear Regression Projection from Known Point

    Quote Originally Posted by vb6forever View Post
    If one is looking at sales data, the data set for the X Axis most likely is time, which would be a fixed increment.
    Using Olaf's suggestion and using the last data point and the averages for the datasets (both X and Y)
    then the X Axis point - as previously discussed - may lie between plotted X Axis Points.
    I thought that it was (implicitely) clear, that the "Average-Point-of-the-current-set" was a "virtual one"...
    (only serving, to be able to calculate y-values for any given x-values later).

    Here an example:
    Code:
    Option Explicit
    
    Private MprvX, MprvY 'last point in previous month
    Private McurX(1 To 31), McurY(1 To 31) 'the "set of points" of the current month
    
    Private Sub Form_Click()
      AutoRedraw = True: DrawWidth = 10: Scale (0, 110)-(31, 0): Cls
      
      MprvX = 0: MprvY = 100 'last point of the previous Month
      PSet (MprvX, MprvY), vbGreen 'draw this point in green
      MsgBox "the green starting-point of the prev. month at 'day-index'=0 and value=100"
      
      'let's say, the current month has only two Values (at days 14 and 15)
      McurX(14) = 14: McurY(14) = 80
      McurX(15) = 15: McurY(15) = 60
      'let's draw these two current-Month-points in red
      PSet (McurX(14), McurY(14)), vbRed
      PSet (McurX(15), McurY(15)), vbRed
      MsgBox "the red points for data of the current month at 'days' 14 and 15"
      
      Dim MavgX: MavgX = (McurX(14) + McurX(15)) / 2
      Dim MavgY: MavgY = (McurY(14) + McurY(15)) / 2
      PSet (MavgX, MavgY), vbBlack
      MsgBox "the black point representing the current months average"
      
      'now calculate m and n, to be able to apply the "line-function" y = m*x + n
      Dim m: m = (MavgY - MprvY) / (MavgX - MprvX)
      Dim n: n = MprvY
          
      'now we can draw a line, no matter what we choose for x1 and x2
      Dim y1, x1: x1 = 1:  y1 = m * x1 + n 'calc y1 for x1=1
      Dim y2, x2: x2 = 30: y2 = m * x2 + n 'calc y2 for x2=30
      DrawWidth = 2
      Line (x1, y1)-(x2, y2), vbMagenta
    End Sub
    HTH

    Olaf

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Feb 2017
    Posts
    174

    Re: [RESOLVED] Linear Regression Projection from Known Point

    Olaf:
    Nice little example.
    Thanks for your additional effort on my behalf.
    Hopefully others appreciate it as well.

    David

  14. #14
    New Member
    Join Date
    Apr 2018
    Posts
    1

    Re: [RESOLVED] Linear Regression Projection from Known Point

    In my reading, studying and comparison of all written above, I equally agreed that I'm a novice, contrary to what I thought of myself! You guys have indeed added a bit to my knowledge!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width