
Mar 20th, 2018, 09:09 PM
#1
Thread Starter
Hyperactive Member
[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.

Mar 29th, 2018, 05:06 PM
#2
Re: Linear Regression Projection from Known Point
Since any (linear) regressionline always goes "through" an anchorpoint which is located at the "gravitycenter" (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 "PointGroup") that averaged centerpoint  and connect it with the EndPoint 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 "EndPoint" for the current group).
Olaf

Mar 30th, 2018, 02:00 PM
#3
Thread Starter
Hyperactive Member
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.

Mar 30th, 2018, 06:14 PM
#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 pointset of 3 x/yPairs, maybe accompanied by a little Image)
would be helpful, to understand better, "what you have as Input"  and "what you want as Output"...
Olaf

Mar 30th, 2018, 09:36 PM
#5
Thread Starter
Hyperactive Member
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.

Apr 1st, 2018, 06:08 PM
#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

Apr 3rd, 2018, 11:03 AM
#7
Thread Starter
Hyperactive Member
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.

Apr 3rd, 2018, 11:34 AM
#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.

Apr 4th, 2018, 09:09 AM
#9
Thread Starter
Hyperactive Member
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.

Apr 4th, 2018, 11:32 AM
#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

Apr 5th, 2018, 09:16 AM
#11
Thread Starter
Hyperactive Member
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.

Apr 8th, 2018, 02:25 PM
#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 "AveragePointofthecurrentset" was a "virtual one"...
(only serving, to be able to calculate yvalues for any given xvalues 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 startingpoint of the prev. month at 'dayindex'=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 currentMonthpoints 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 "linefunction" 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

Apr 9th, 2018, 08:57 PM
#13
Thread Starter
Hyperactive Member
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

Apr 13th, 2018, 11:22 AM
#14
New Member
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

Forum Rules

Click Here to Expand Forum to Full Width
