# Extracting intermediate values from start and end points of line

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

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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## 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)=(X±n, Y±nm) 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.  Register To Reply

5. ## 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  Register To Reply

6. ## 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   Register To Reply

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