+ Reply to Thread
Results 1 to 7 of 7

Help with line of best fit, linear regression, angle of line of best fit VBA

  1. #1
    Registered User
    Join Date
    09-28-2016
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Help with line of best fit, linear regression, angle of line of best fit VBA

    Hello,

    I would greatly appreciate some help with this problem. I am using excel 2003, windows 7.

    I have been trying to work out which worksheet or vba function to use to calculate the following - but I just can't get the right answer. There are multiple facets to the vba sub I am trying to write. Vba is ideal so that I can use the values for other purposes. For starters, let's say I have 3 points which have an R value close to 1/line quite closely:

    x y

    week 75 (date 14/2/1992) 6.81

    week 83 (date 10/4/1992) 6.95

    week 95 (date 3/7/1992) 7.08

    I can use either the week number or the date as the x-axis.

    I would like to work out the line of best fit which should pass through the 1st point. Then I want to work out the slope & intercept of the line of best fit, and the angle of the slope. As far as I can tell from manually plotting the data, the angle should be about 17.8 degrees {I keep getting angle values like 1 degree, 1.8 degrees and 4 degrees}.

    I have tried using the functions slope, linest, intercept, degrees(ATAN(slope) but I don't seem to get the correct angle. And I'm not sure whether I need to make the first x and/or y equal zero for the formulae to work. And I know that with these formulae, I am currently not getting the line of best fit to pass through the first point.

    ----

    Overall what I am trying to achieve is to automate drawing a trend line on a stock price chart, I have the following data:

    x y

    week 75 (date 14/2/1992) 6.81

    week 80 (date 20/3/1992) 7.2

    week 83 (date 10/4/1992) 6.95

    week 88 (date 15/5/1992) 7.52

    week 95 (date 3/7/1992) 7.08

    week 98 (date 24/7/1992) 7.02

    I want to use the 1st point as my reference/anchor point for lines of best fit. (But the reference point can be 1-2% (y-axis) lower than the 1st point, if this creates a better line of fit).

    Starting with the first three points, I would calculate a line of best fit going through the first point. If the R value is to far away from 1 (i.e. the points do not line up as close to possible in a straight line) then I would only keep the either the 2nd or third point (whichever is below the line of best fit) and I would retest for a line of best fit for points 1, 3, and 4. This process would repeat until the R value is close to 1.

    Up to here would be a great starting point if anyone can help.

    Just for argument's sake (if anyone really likes math/vba); the additional rules for this line of best fit would be:

    -If there is no acceptable line of best fit found with 3 points, it is possible to draw a line connecting the 1st and second points, and then use the second point as the reference/anchor point - to search from this point forward for 3 points that have a good line of fit (usually used in cases where the future line of best fit/stock prices, have started to move in a steeper angle up the y-axis.

    - This acceleration can/should occur for future points, even after an initial 3 point line of best fit is established. The new reference/anchor point in this case would be the 3rd of the initial 3 points.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    There's a lot there. Do you need help with all of it, or are there parts that you know how to do?

    One question is how to force your linear regression through a specific point. First some algebra:
    regression equation: y=mx+b
    anchor point: (x0,y0)
    substitute anchor point into regression equation: y0=m*x0+b
    solve for b: b=y0-m*x0
    substitute known expression for b into regression equation: y=m*x+y0-m*x0
    rearrange: y-y0=m(x-x0)
    If it is not obvious, perform this substitution: v=y-y0, u=x-x0
    and substitute v=m*u
    It should be painfully obvious that, with this transformation of the regression equation, our new regression equation shows that v is a straight line function of u going through the origin. Create a new regression matrix that calculates v and u, then use the LINEST() function with the third argument set to FALSE.

    You indicate that you are having trouble converting the slope to an angle. I get ~0.8 degrees when I use the per week slope (based on week number) and ~0.1 degrees when I use the per day slope (based on calendar days). I don't know how you are getting that the angle should be 17.8 degrees. If my trig is correct, 17.8 degrees corresponds to a slope of about 0.3/unit time. This looks about right for a "per 26 week" slope, but I don't know if that is what you are going for on this.

    You also suggest you want to explore different scenarios -- different anchor points and different points included in the regression. I would suggest that, once we get a handle on the previous questions, this will be a simple extension of the previous two questions. If you pay attention to how you arrange the data and formulas (try to keep it all in a single row/column), then we should be able to easily try different combinations by copying the above steps multiple times, with a different combination of inputs to each copy.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    09-28-2016
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    Thanks for taking the time to reply Mr Shorty,
    So from the initial data I now have:
    x y
    75 6.81
    83 6.95
    95 7.08

    u v
    0 0
    8 0.14
    20 0.27

    ** I think for the below extrapolations, I should only be using CORREL, and LINEST, and ANGLE
    SLOPE(V1:V3, U1:U3) = 0.013289
    INTERCEPT(V1:V3, U1:U3) = 0.12632 - but shouldn't this equal zero? Or is there a different intercept function to use with linest ( , ,FALSE)
    CORREL(V1:V3, U1:U3) = 0.99072 - is there a quick way of inferring the at what value the correlation is poor? or do I need trial and error to determine this?
    FORECAST(U1, V1:V3, U1:U3) = 0.012632 - but as for intercept, I think I would want this to be 0 - do I need a different function to use with linest ( , ,FALSE)
    FORECAST(U2, V1:V3, U1:U3) = 0.118947 -- As mentioned in my original question - Am I right in assuming this is the easiest way to test for "only keep the either the 2nd or third point (whichever is below the line of best fit)"
    FORECAST(U3, V1:V3, U1:U3) = 0.278421

    LINEST(V1:V3, U1:U3, FALSE) = 0.01405
    Instead of FORECAST, is it better to use
    u
    0 x 0.01405 = estimated v = 0
    8 x 0.01405 = estimated v = 0.11241
    20 x 0.01405 = estimated v = 0.28103

    ANGLE = DEGREES(ATAN((0.28103 - 0)/(20 - 0))) = 0.80505 Are these the correct values to use in this formula? I can't really use the original u and v values, because that doesn't relate to the calculated line of best fit

    Regarding the angle - I am looking for essentially the angle within a triangle, were one side is the range of the weeks in the line of best fit, and another side is the range in price of the line of best fit. But shouldn't the 3 angles within a triangle equal 180 degrees, and if one is a right angle, shouldn't the result for the ANGLE, be a lot higher?? Or am I missing a conversion?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    The SLOPE(), INTERCEPT(), and FORECAST() functions do not have an option to force the regression through 0. The TREND() and LINEST() functions do have the option of forcing the regression through 0 (Help files for all functions listed alphabetically: https://support.office.com/en-us/art...90033e188#bm20 ). So, if you want to perform a regression like this where you can force the regression through 0, then you should only be using the TREND() or LINEST() functions (because you want to actually return the slope, the LINEST() is probably the best choice in this situation).

    Using CORREL() as your "goodness of fit" measure might be ok, but I am not sure how it works for regressions where b is forced to be 0. It probably depends on exactly how you set it up and exactly what that function (or the RSQ() or PEARSON() or whichever of these common "goodness of fit" measures you decide to use) calculates. I am not enough of a statistician to be able to say which, if any, of these are "statistically rigorous" measures of goodness of fit where the regression is coerced through 0. Someone with a stronger background in statistics will need to comment on that.

    If a statistically rigorous measure is not needed, then I suspect that any of these (or rmsd or whatever) will be adequate for seeing what happens to goodness of fit when you change something, and could be used for judging when you have an adequate fit of your data. If a statistically rigorous measure is needed, then you will need to carefully think through these (perhaps get a statistician's opinion on it) so that you get the right measure for goodness of fit.

    As far as the angle goes, those values work as well as any, and should be correct. Note that your slope (0.014 units per 1 week which is the same as 0.28 per 20 weeks) is a very small slope. Yes the 3 angles of a triangle will always add up to 180 degrees (pi radians). If the one angle is 0.8 degrees, that must mean the other acute angle in a right triangle is 89.2 degrees. It helps me to visualize trig functions on a unit circle. If it will help you, I built this spreadsheet to illustrate the unit circle and the trig functions: http://www.excelforum.com/tips-and-t...han-excel.html

  5. #5
    Registered User
    Join Date
    09-28-2016
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    Hi Mr Shorty,
    Thanks again for the info, I think I am getting my head around this.

    Is there an easy way of allowing the anchor point (x0, y0) or (u0, v0) have a line of best fit where the anchor point can be 1-2% lower on the y - axis if this creates a better line of fit? If i just use linest( , , TRUE) then this would allow the line of best fit to also be higher on the y axis than (x0, y0).

    I have some ability to do this kind of looping calculations with VBA but are there any hints you can give me regarding:
    - whether to drop the data in a worksheet or to just keep the calculations in VBA using variables (I have limited experience with arrays)
    - how to "big picture" organise or layout this kind of loop/tests to follow the below sequences
    * Starting with the first three points, I would calculate a line of best fit going through the first point. If the R value is to far away from 1 (i.e. the points do not line up as close to possible in a straight line) then I would only keep the either the 2nd or third point (whichever is below the line of best fit) and I would retest for a line of best fit for points 1, 3, and 4. This process would repeat until the R value is close to 1.
    * The additional rules for this line of best fit would be:
    * If there is no acceptable line of best fit found with 3 points, it is possible to draw a line connecting the 1st and second points, and then use the second point as the reference/anchor point - to search from this point forward for 3 points that have a good line of fit (usually used in cases where the future line of best fit/stock prices, have started to move in a steeper angle up the y-axis.
    * This acceleration can/should occur for future points, even after an initial 3 point line of best fit is established. The new reference/anchor point in this case would be the 3rd of the initial 3 points.

    Thanks again.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    s there an easy way of allowing the anchor point (x0, y0) or (u0, v0) have a line of best fit where the anchor point can be 1-2% lower on the y - axis if this creates a better line of fit? If i just use linest( , , TRUE) then this would allow the line of best fit to also be higher on the y axis than (x0, y0).
    I'm not sure I understand all that you want to do in letting y0 float a little. If we revisit our algebra:

    Original best fit equation: y=mx+b we get m and b from our first LINEST() formulation on the original y and x.
    We showed that y0=m*x0+b From this equation and the results of the true "best fit" regression in the previous step, we can calculate the "best fit" y0. Compare that to your anchor point y0. This should tell you if moving the anchor y0 fro the u,v regression 1-2% in the desired direction will lead to a better fit or not.

    As for the rest of your questions, I think a lot will depend on whether you decide which language you will be comfortable with. Will you be more comfortable processing in VBA (and the spreadsheet becomes a container for inputs and outputs) or will you be more comfortable doing the processing in the spreadsheet. Myself, I usually prefer to do the processing in the spreadsheet. As I noted in poste #2, I would try to arrange all of the inputs, calculations, and outputs for a given "iteration" in a single row (or column). Pay careful attention to relative and absolute references in all formulas. Then make as many copies of that row (or column) as I need to explore the different input combinations that I want to try. Adjust formulas or manually enter new values into the inputs for each row. When all is said and done, I can look down the column with the goodness of fit and decide which row is the one I want to use.

  7. #7
    Registered User
    Join Date
    09-28-2016
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Help with line of best fit, linear regression, angle of line of best fit VBA

    Cheers Mr Shorty, I'll give it a go!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Angle of Line Chart
    By treva26 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-22-2014, 03:11 AM
  2. [SOLVED] Angle of view & Centre line
    By WCE_123 in forum Excel General
    Replies: 6
    Last Post: 03-04-2014, 10:39 AM
  3. [SOLVED] Return the angle of a drawn line
    By Robert Tosh in forum Excel General
    Replies: 0
    Last Post: 03-10-2006, 11:00 PM
  4. One regression line
    By Prba in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-24-2006, 12:20 PM
  5. [SOLVED] How do I get Excel to tell me the angle of a line
    By drbob2000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2005, 08:05 PM
  6. Perpendicular distance of a point from linear regression line?
    By Winny in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-24-2005, 08:05 PM
  7. Replies: 6
    Last Post: 09-21-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1