# Thread: [RESOLVED] Linear Regression Projection from Known Point

1. ## [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```

2. ## 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))...

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. ## Re: Linear Regression Projection from Known Point

Olaf, as always. thanks for your input:

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. ## 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. ## 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. ## Re: Linear Regression Projection from Known Point

Originally Posted by vb6forever
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. ## 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. ## Re: Linear Regression Projection from Known Point

Originally Posted by vb6forever
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. ## Re: Linear Regression Projection from Known Point

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

passel:
Thanks for responding.

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. ## 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. ## 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. ## Re: [RESOLVED] Linear Regression Projection from Known Point

Originally Posted by vb6forever
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. ## Re: [RESOLVED] Linear Regression Projection from Known Point

Olaf:
Nice little example.
Hopefully others appreciate it as well.

David

14. ## 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