+ Reply to Thread
Results 1 to 6 of 6

Extracting intermediate values from start and end points of line

  1. #1
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Extracting intermediate values from start and end points of line

    Hello all,

    I consider myself on the lower end of experience when it comes to this type of thing and I suspect it should be easy to accomplish, although I don't know exactly how to word it to find the solution by searching.

    I have several sets of limit lines on data that we test for at work in the format of (5, 0.15) to (1200, 0.25) and now have a customer that needs the intermediate ("Y" values) at the following ("X") points [5, 400, 500, 750, 800, 1000, 1100, 1200].

    Is there a simple formula, graph&function, or code that could accomplish this task? I realize that I could plug it into a graphing calculator, but there are quite a few points on quite a few limit lines that I would need to do this for and I feel that there has got to be a better way.

    Thanks in advance for any advice.

    10:29 CST EDIT: added worksheet I am working on currently as an example in case it is needed. The cells I refrenced in the main post are as follows:
    (5, 0.15) to (1200, 0.25) would be in worksheet "current test specs" and cells (B3, D3) to (C3, E3) and the place I would need to place the results would need to be worksheet "F-40000-A" and cells D2:G2

    I have sanitized the worksheet and removed all the duplicates of the other part numbers, but it should serve to explain any confusion.
    Attached Files Attached Files
    Last edited by spencer_time; 06-10-2021 at 11:35 AM. Reason: Add example

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: Extracting intermediate values from start and end points of line

    If you have two co-ordinates then you can convert that into a formula.

    The gradient = m = ( 0.25 - 0.15 ) / ( 1200 - 5 ) = 0.1 / 1195 = 1/11950

    y = mx +c

    So if x = 5 and y = 0.15 then

    c = 0.25 - 0.1/1195 * 5 = 0.25 - 0.5/1195 = 0.25 - 1/2390


    So plug your x values in to y = mx +c

    For x = 400

    y = 400/11950 + 0.25 - 1/2390 = 0.283054393


    For x = 800

    y = 800/11950 + 0.25 - 1/2390 = 0.316527197
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Extracting intermediate values from start and end points of line

    I appreciate the suggestion, but either I am completely missing something or your example is hitting similar problems I was having when I tried both Y=mX+b and m = (Y1-Y2)/(X1-X2) and subbing that into Y=X+n, Y+nm [where n is the shift in the x axis and m is the slope co-efficient]

    That problem being that I keep getting unexpected results (similar to yours) where the Y value in between my two points is either greater than my ending Y or smaller than my starting Y....to me this doesn't seem to work as the point on the line between the two points should also have a Y value between the two max and min values.

    Do you agree?

    I am generally competent with simple math stuffs, but am really having trouble today.

    13:42 CST EDIT: just to clairify, I am expecting all points between (5, 0.15) and (1200, 0.25) [the points used in my example] to have a Y value above 0.15 and below 0.25 regardless of the X value....again I could be missing something here, but that's what would make sense to me.
    Last edited by spencer_time; 06-10-2021 at 02:44 PM.

  4. #4
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Extracting intermediate values from start and end points of line

    Okay, so after lunch I got back into it and the problem was as simple as the wrong sign in front of the "n" variable(s) in my formula.

    I ended up using m=(Y2-Y1)/(x2-X1) for the slope and used that with (Xn, Yn)=(Xn, Ynm) where n was the shift in the X axis (+ for right shift and - for left shift) **the fatal error was using + for a left shift in the X axis** and m was the slope found earlier.

    Thanks again for your suggestion, you having similar answers as me gave me the motivation I needed to pick it back up and keep trying.

    14:16 CST EDIT: I decided to add an example of the solution I used in case anyone searches for a similar problem in the future, it can be found in the attached spreadsheet in column(s) L through X.
    Attached Files Attached Files
    Last edited by spencer_time; 06-10-2021 at 03:17 PM. Reason: Add example

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: Extracting intermediate values from start and end points of line

    =a1/11950+0.15-1/2390

    x y
    5 0.15
    400 0.183054393
    500 0.191422594
    750 0.212343096
    800 0.216527197
    1000 0.233263598
    1100 0.241631799
    1200 0.25

    The gradient = m = ( 0.25 - 0.15 ) / ( 1200 - 5 ) = 0.1 / 1195 = 1/11950

    y = mx +c

    So if x = 5 and y = 0.15 then

    0.15 = 5/11950 +c
    c = 0.15-5/11950
    c = 0.15 -1/2390
    Last edited by mehmetcik; 06-10-2021 at 03:25 PM.

  6. #6
    Registered User
    Join Date
    08-27-2019
    Location
    Alabama, USA
    MS-Off Ver
    365, 2016, 2010, 2007
    Posts
    57

    Re: Extracting intermediate values from start and end points of line

    Thanks again mehmetcik, this data aligns exactly with my data, I will save the way you derived it in case I need it in the future. I am now very confident in my data.

    Consider this matter SOLVED

+ 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. Line graph with two lines that start at different x values
    By baldbanker in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-12-2014, 10:07 AM
  2. Replies: 2
    Last Post: 01-27-2014, 06:52 PM
  3. Finding intermediate points for graph plotting.
    By Dapos1993 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-08-2013, 12:37 PM
  4. Identify top 3, bottom 3 and intermediate values using IF function
    By nataliebenjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2012, 03:28 PM
  5. Line graph with two lines that start at different x values
    By lmorett in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 10-14-2009, 04:46 AM
  6. [SOLVED] How to get intermediate values from smooth graph in Excel ?
    By Tushar in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 02-13-2006, 04:45 PM
  7. Replies: 1
    Last Post: 02-11-2006, 06:45 AM

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