Interpolation
Posted by Del Booth on September 12, 2001 6:27 AM
Hi.
I have an excel graph with two plots on.
When one crosses over the other I want to find the x and y values for this.
I already have the x values I need to calculate the corresponding y value
for this point. Therefore I am needing to interpolate to the corresponding point.
Can excel do this for me or is there a simple macro.
Thanks
Posted by Mark W. on September 12, 2001 12:44 PM
Suppose...
Suppose that you graphed the data...
{"X","Y1","Y2"
;1,18,28
;3,12,6}
...which is entered into cells A1:C3. Your
chart's series functions would be...
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$3,Sheet1!$B$2:$B$3,1)
=SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$3,Sheet1!$C$2:$C$3,2)
The equations for these lines are...
y=-3x+21
y=-11x+39
These equations can be derived from the following
functions entered into E1:F1 and E2:F2 respectively...
{=LINEST(B2:B3,A2:A3)}
{=LINEST(C2:C3,A2:A3)}
Note: LINEST should be entered as an array function
using the Shift+Control+Enter key combination. The
outermost braces, {}, are not entered by you...they're
supplied by Excel in recognition of a properly entered
array formula.
The formulas for the values of X and Y respectively
at the intersection of these lines are...
=(F1-F2)/(E2-E1)
=E2*F3+F2
Posted by Mark W. on September 12, 2001 1:59 PM
Re: Suppose...
I should have stated that the formula for X,
=(F1-F2)/(E2-E1), was entered into cel F3, and
is referenced by the formula for Y, =E2*F3+F2.
