+ Reply to Thread
Results 1 to 6 of 6

Making a linear fit pass through a specific point

  1. #1
    Registered User
    Join Date
    05-14-2019
    Location
    Aachen
    MS-Off Ver
    2018
    Posts
    3

    Unhappy Making a linear fit pass through a specific point

    Hey, I have a scatter graphic with several points and I need to get a linear trenline in order to get an equation out of it. Since it involves standard test conditions, I need to make that line pass through the point (1000,100). Is there any way to force the trendline to pass through this point? I know you can force it to pass through the origin for example, but I cant find anywhere that allows me to choose another point.
    Attached Files Attached Files
    Last edited by mariamonney; 05-15-2019 at 04:50 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Making a linear fit pass through a specific point

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Making a linear fit pass through a specific point

    It sounds like you are using the chart trendline feature, is that correct? Are you willing to do the regression in the spreadsheet, or must you use the chart trendline feature?

    I do my regressions in the spreadsheet using the LINEST() function (https://support.office.com/en-us/art...a-fa7abf772b6d ). Most of the work is the algebra to "transform" the basic linear function (y=mx+b) into a new function that can account for the x0,y0 point:

    Basic regression equation: y=mx+b
    constraint: equation goes through x0,y0 -- meaning y0=m*x0+b
    Use this equation to find what b will be, once I find m: b=y0-m*x0
    If I substitute this back into my regression equation: y=mx+(y0-m*x0)
    Rearrange: y-y0=m*(x-x0)
    If needed so you can see, let u=x-x0 and v=y-y0 and the regression equation becomes v=m*u(+0) which is a basic linear regression with the constant forced through the origin.

    Programming this into the spreadsheet, I would:

    1) Because I like to use helper cells to see the steps in this kind of algorithm, I would add helper columns that compute u and v.
    2) Use LINEST() on these helper columns =LINEST(known_v,known_u,FALSE) to get the slope m.
    3) If I need b, then I can compute b from the constraint equation.

    If you must use the chart trendline feature, plot u and v in a scatter chart and add a trendline that goes through the origin.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    05-14-2019
    Location
    Aachen
    MS-Off Ver
    2018
    Posts
    3

    Re: Making a linear fit pass through a specific point

    I just updated it, can you check it out? I Need it to intersect that last Point

  5. #5
    Registered User
    Join Date
    05-14-2019
    Location
    Aachen
    MS-Off Ver
    2018
    Posts
    3

    Re: Making a linear fit pass through a specific point

    Thank you so much for your answear Mr.Shorty. Do you think you could explain it to me with the example I uploaded? Im a bit confused. Thank you very much.

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

    Re: Making a linear fit pass through a specific point

    I would expect:

    1) Enter 1000 in C9 and 100 in D9
    2) Enter "u" in C1 and "v" in D1.
    3) Enter =A2-C$9 in C2 and copy/fill down to C7 -- note the mix of relative and absolute references for easy copying.
    4) Copy C2:C7 and paste in D2:D7
    5) Enter the LINEST() function in a convenient pair of cells. Maybe select C11:D11 and array enter (ctrl-shift-enter) =LINEST(D2:D7,C2:C7,FALSE)
    6) If you need b, enter =D9-C11*C9 into C12.
    7) Review and debug results.

    Which step do you have trouble with?

+ 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. Sample Point X - Pass - Caution - Fail - No Test (Data Charted)
    By Darkprophecy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-09-2019, 11:01 AM
  2. Generating student pass/fail average based on 4-point rubric
    By stuartgannon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2015, 05:48 PM
  3. Pass or Fail field involving a scattered/ linear chart.
    By 1Rookie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-20-2013, 11:51 PM
  4. Making last known data point show as value on graph?
    By Daniel86 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-21-2013, 07:42 AM
  5. Making a reference point for my macro
    By afriedman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2010, 03:02 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

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