Interpolating an x, y point from known x's and y's

Hi

This is probably a simple task and it is my own lack of experience in
Excel -- I would like to derive a y value for an arbitrary x value from a
array of known x's and known y's for some unknown function y = f(x) a la:

known x, y
0.123, 4.567
0.257, 10.4567
0.4321, 20.3241
0.703, 10.345
0.804, 2.345

say I want to derive a y value for x=0.5 from this data set using a linear
or higher order fit -- is there an appropriate worksheet function for this
or do I have to resort to programming?

Many thanks in advance.
Steve

You can use the FORECAST() function to interpolate as well as extrapolate.

See Excel Help
--
Gary's Student


"Steve" wrote:

> Hi
>
> This is probably a simple task and it is my own lack of experience in
> Excel -- I would like to derive a y value for an arbitrary x value from a
> array of known x's and known y's for some unknown function y = f(x) a la:
>
> known x, y
> 0.123, 4.567
> 0.257, 10.4567
> 0.4321, 20.3241
> 0.703, 10.345
> 0.804, 2.345
>
> say I want to derive a y value for x=0.5 from this data set using a linear
> or higher order fit -- is there an appropriate worksheet function for this
> or do I have to resort to programming?
>
> Many thanks in advance.
> Steve
>
>
>

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:833A09AB-EC48-4E3C-AB54-DD966B4BEFE0@microsoft.com...
> You can use the FORECAST() function to interpolate as well as extrapolate.

FORECAST seems to do only linear line fit -- my data is an arbitrary fixed
waveform for which Im trying to fill in missing points -- is there some
trick I need to apply to make forecast work at a higher order?

>
> See Excel Help
> --
> Gary's Student
>
>
> "Steve" wrote:
>
>> Hi
>>
>> This is probably a simple task and it is my own lack of experience in
>> Excel -- I would like to derive a y value for an arbitrary x value from a
>> array of known x's and known y's for some unknown function y = f(x) a la:
>>
>> known x, y
>> 0.123, 4.567
>> 0.257, 10.4567
>> 0.4321, 20.3241
>> 0.703, 10.345
>> 0.804, 2.345
>>
>> say I want to derive a y value for x=0.5 from this data set using a
>> linear
>> or higher order fit -- is there an appropriate worksheet function for
>> this
>> or do I have to resort to programming?
>>
>> Many thanks in advance.
>> Steve
>>
>>
>>

You can nearly always fit N points to polynomial of N-1 power.
You can make a chart and use Add Trendline
To put the coeffienceints in worksheets cell use LINEST
For more on:Polynomial, non-linear, Trendline Coefficients
and Regression Analysis

http://www.tushar-mehta.com/excel/ti...efficients.htm
http://www.stfx.ca/people/bliengme/E.../Polynomial.ht

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve" <steve_mowbray@hotmail.com> wrote in message
news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
> Hi
>
> This is probably a simple task and it is my own lack of experience in
> Excel -- I would like to derive a y value for an arbitrary x value from a
> array of known x's and known y's for some unknown function y = f(x) a la:
>
> known x, y
> 0.123, 4.567
> 0.257, 10.4567
> 0.4321, 20.3241
> 0.703, 10.345
> 0.804, 2.345
>
> say I want to derive a y value for x=0.5 from this data set using a linear
> or higher order fit -- is there an appropriate worksheet function for this
> or do I have to resort to programming?
>
> Many thanks in advance.
> Steve
>
>

As far as I know, there is no built-in function to perform
interpolation from a table.

You might be able to use TREND (simpler to use than LINEST IMO) to fit
a polynomial curve to a set of data. But this isn't really
interpolation, it's equation fitting. Still, if you can get a good fit
with a polynomial function then TREND might work for you. TREND/LINEST
is also useful if you're working with raw data that has scatter,
because it will find the polynomial that fits the all of data with
minimum error.

For a linear fit:
New_y = TREND(Known_y's, Known_x's, New_x)
For a nth-order polynominal fit:
New_y = TREND(Known_y's, Known_x's^{1,2,...n}, New_x^{1,2,...n})

For true linear interpolation/extrapolation I wrote the following VBA
function. to perform linear intepolation/extrapolation. This function
will linearly interpolate from point-to-point in a set of data. Note,
that the data must be sorted by x.

Function Interpolate(XData As Range, YData As Range, X As Double)
As Double
' Function to linearly interpolate from array of data.
' xdata - Range containing known x's
' ydata - Range containing known y's
' x - Desired value of x
' Interpolate - Interpolated value of y at desired value of x
'
' Note:
' 1. xdata and ydata must have same number of points.
' 2. xdata values must be monotonically increasing.
' 3. y will be extrapolated if x lies outside upper or lower bounds
of xdata.
Dim nxp As Integer, ipmin As Integer, ip As Integer
Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
nxp = Application.Count(XData) ' Number of x data points
' Extrapolate if x is less than xdata lower bound.
If X < XData.Cells(1).Value Then
x1 = XData.Cells(1).Value
x2 = XData.Cells(2).Value
y1 = YData.Cells(1).Value
y2 = YData.Cells(2).Value
Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
' Extrapolate if x is greater than xdata upper bound.
ElseIf X > XData.Cells(nxp).Value Then
x1 = XData.Cells(nxp - 1).Value
x2 = XData.Cells(nxp).Value
y1 = YData.Cells(nxp - 1).Value
y2 = YData.Cells(nxp).Value
Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
' Otherwise, interpolate within data range
Else
For ip = 1 To nxp - 1
x1 = XData.Cells(ip).Value
x2 = XData.Cells(ip + 1).Value
y1 = YData.Cells(ip).Value
y2 = YData.Cells(ip + 1).Value
If X >= x1 And X <= x2 Then
Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
End If
Next ip
End If
End Function

Dave

Bernard Liengme wrote:
> You can nearly always fit N points to polynomial of N-1 power.
> You can make a chart and use Add Trendline
> To put the coeffienceints in worksheets cell use LINEST
> For more on:Polynomial, non-linear, Trendline Coefficients
> and Regression Analysis
>
> http://www.tushar-mehta.com/excel/ti...efficients.htm
> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Steve" <steve_mowbray@hotmail.com> wrote in message
> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
> > Hi
> >
> > This is probably a simple task and it is my own lack of experience in
> > Excel -- I would like to derive a y value for an arbitrary x value from a
> > array of known x's and known y's for some unknown function y = f(x) a la:
> >
> > known x, y
> > 0.123, 4.567
> > 0.257, 10.4567
> > 0.4321, 20.3241
> > 0.703, 10.345
> > 0.804, 2.345
> >
> > say I want to derive a y value for x=0.5 from this data set using a linear
> > or higher order fit -- is there an appropriate worksheet function for this
> > or do I have to resort to programming?
> >
> > Many thanks in advance.
> > Steve
> >
> >
Bernard Liengme wrote:
> You can nearly always fit N points to polynomial of N-1 power.
> You can make a chart and use Add Trendline
> To put the coeffienceints in worksheets cell use LINEST
> For more on:Polynomial, non-linear, Trendline Coefficients
> and Regression Analysis
>
> http://www.tushar-mehta.com/excel/ti...efficients.htm
> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Steve" <steve_mowbray@hotmail.com> wrote in message
> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
> > Hi
> >
> > This is probably a simple task and it is my own lack of experience in
> > Excel -- I would like to derive a y value for an arbitrary x value from a
> > array of known x's and known y's for some unknown function y = f(x) a la:
> >
> > known x, y
> > 0.123, 4.567
> > 0.257, 10.4567
> > 0.4321, 20.3241
> > 0.703, 10.345
> > 0.804, 2.345
> >
> > say I want to derive a y value for x=0.5 from this data set using a linear
> > or higher order fit -- is there an appropriate worksheet function for this
> > or do I have to resort to programming?
> >
> > Many thanks in advance.
> > Steve
> >
> >

<dgp@dodgeit.com> wrote in message
news:1151353235.936527.252830@p79g2000cwp.googlegroups.com...
> As far as I know, there is no built-in function to perform
> interpolation from a table.
>
> You might be able to use TREND (simpler to use than LINEST IMO) to fit
> a polynomial curve to a set of data. But this isn't really
> interpolation, it's equation fitting. Still, if you can get a good fit
> with a polynomial function then TREND might work for you. TREND/LINEST
> is also useful if you're working with raw data that has scatter,
> because it will find the polynomial that fits the all of data with
> minimum error.
>
> For a linear fit:
> New_y = TREND(Known_y's, Known_x's, New_x)
> For a nth-order polynominal fit:
> New_y = TREND(Known_y's, Known_x's^{1,2,...n}, New_x^{1,2,...n})

I did a linear fit in the first instance:

=TREND($G$6:$G$21,$E$6:$E$21,K6)

This worked but the data does not fit a straight line -- when I tried to
create a cubic fit syntax above

=TREND($G$6:$G$21,$E$6:$E$21^(1,2,3), K6^(1,2,3))

I get a formula error -- is there something else I need to add to the
syntax?

>
> For true linear interpolation/extrapolation I wrote the following VBA
> function. to perform linear intepolation/extrapolation. This function
> will linearly interpolate from point-to-point in a set of data. Note,
> that the data must be sorted by x.
>
> Function Interpolate(XData As Range, YData As Range, X As Double)
> As Double
> ' Function to linearly interpolate from array of data.
> ' xdata - Range containing known x's
> ' ydata - Range containing known y's
> ' x - Desired value of x
> ' Interpolate - Interpolated value of y at desired value of x
> '
> ' Note:
> ' 1. xdata and ydata must have same number of points.
> ' 2. xdata values must be monotonically increasing.
> ' 3. y will be extrapolated if x lies outside upper or lower bounds
> of xdata.
> Dim nxp As Integer, ipmin As Integer, ip As Integer
> Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
> nxp = Application.Count(XData) ' Number of x data points
> ' Extrapolate if x is less than xdata lower bound.
> If X < XData.Cells(1).Value Then
> x1 = XData.Cells(1).Value
> x2 = XData.Cells(2).Value
> y1 = YData.Cells(1).Value
> y2 = YData.Cells(2).Value
> Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> ' Extrapolate if x is greater than xdata upper bound.
> ElseIf X > XData.Cells(nxp).Value Then
> x1 = XData.Cells(nxp - 1).Value
> x2 = XData.Cells(nxp).Value
> y1 = YData.Cells(nxp - 1).Value
> y2 = YData.Cells(nxp).Value
> Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> ' Otherwise, interpolate within data range
> Else
> For ip = 1 To nxp - 1
> x1 = XData.Cells(ip).Value
> x2 = XData.Cells(ip + 1).Value
> y1 = YData.Cells(ip).Value
> y2 = YData.Cells(ip + 1).Value
> If X >= x1 And X <= x2 Then
> Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> End If
> Next ip
> End If
> End Function
>
> Dave
>
> Bernard Liengme wrote:
>> You can nearly always fit N points to polynomial of N-1 power.
>> You can make a chart and use Add Trendline
>> To put the coeffienceints in worksheets cell use LINEST
>> For more on:Polynomial, non-linear, Trendline Coefficients
>> and Regression Analysis
>>
>> http://www.tushar-mehta.com/excel/ti...efficients.htm
>> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Steve" <steve_mowbray@hotmail.com> wrote in message
>> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
>> > Hi
>> >
>> > This is probably a simple task and it is my own lack of experience in
>> > Excel -- I would like to derive a y value for an arbitrary x value from
>> > a
>> > array of known x's and known y's for some unknown function y = f(x) a
>> > la:
>> >
>> > known x, y
>> > 0.123, 4.567
>> > 0.257, 10.4567
>> > 0.4321, 20.3241
>> > 0.703, 10.345
>> > 0.804, 2.345
>> >
>> > say I want to derive a y value for x=0.5 from this data set using a
>> > linear
>> > or higher order fit -- is there an appropriate worksheet function for
>> > this
>> > or do I have to resort to programming?
>> >
>> > Many thanks in advance.
>> > Steve
>> >
>> >
> Bernard Liengme wrote:
>> You can nearly always fit N points to polynomial of N-1 power.
>> You can make a chart and use Add Trendline
>> To put the coeffienceints in worksheets cell use LINEST
>> For more on:Polynomial, non-linear, Trendline Coefficients
>> and Regression Analysis
>>
>> http://www.tushar-mehta.com/excel/ti...efficients.htm
>> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Steve" <steve_mowbray@hotmail.com> wrote in message
>> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
>> > Hi
>> >
>> > This is probably a simple task and it is my own lack of experience in
>> > Excel -- I would like to derive a y value for an arbitrary x value from
>> > a
>> > array of known x's and known y's for some unknown function y = f(x) a
>> > la:
>> >
>> > known x, y
>> > 0.123, 4.567
>> > 0.257, 10.4567
>> > 0.4321, 20.3241
>> > 0.703, 10.345
>> > 0.804, 2.345
>> >
>> > say I want to derive a y value for x=0.5 from this data set using a
>> > linear
>> > or higher order fit -- is there an appropriate worksheet function for
>> > this
>> > or do I have to resort to programming?
>> >
>> > Many thanks in advance.
>> > Steve
>> >
>> >
>

Use curly brackets around the exponents:
^{1,2,3}

Not parentheses:
^(1,2,3)

Note, that interpolating by equation fitting will not neccesarily match
the data points in the table - there will be some error. The only time
the polynomial will exactly match the data is if the polynomial order
is equal to the number of data points minus one. Increasing the order
of the polynomial can lead to some very wild lines though.

Dave

Steve wrote:
> <dgp@dodgeit.com> wrote in message
> news:1151353235.936527.252830@p79g2000cwp.googlegroups.com...
> > As far as I know, there is no built-in function to perform
> > interpolation from a table.
> >
> > You might be able to use TREND (simpler to use than LINEST IMO) to fit
> > a polynomial curve to a set of data. But this isn't really
> > interpolation, it's equation fitting. Still, if you can get a good fit
> > with a polynomial function then TREND might work for you. TREND/LINEST
> > is also useful if you're working with raw data that has scatter,
> > because it will find the polynomial that fits the all of data with
> > minimum error.
> >
> > For a linear fit:
> > New_y = TREND(Known_y's, Known_x's, New_x)
> > For a nth-order polynominal fit:
> > New_y = TREND(Known_y's, Known_x's^{1,2,...n}, New_x^{1,2,...n})
>
> I did a linear fit in the first instance:
>
> =TREND($G$6:$G$21,$E$6:$E$21,K6)
>
> This worked but the data does not fit a straight line -- when I tried to
> create a cubic fit syntax above
>
> =TREND($G$6:$G$21,$E$6:$E$21^(1,2,3), K6^(1,2,3))
>
> I get a formula error -- is there something else I need to add to the
> syntax?
>
> >
> > For true linear interpolation/extrapolation I wrote the following VBA
> > function. to perform linear intepolation/extrapolation. This function
> > will linearly interpolate from point-to-point in a set of data. Note,
> > that the data must be sorted by x.
> >
> > Function Interpolate(XData As Range, YData As Range, X As Double)
> > As Double
> > ' Function to linearly interpolate from array of data.
> > ' xdata - Range containing known x's
> > ' ydata - Range containing known y's
> > ' x - Desired value of x
> > ' Interpolate - Interpolated value of y at desired value of x
> > '
> > ' Note:
> > ' 1. xdata and ydata must have same number of points.
> > ' 2. xdata values must be monotonically increasing.
> > ' 3. y will be extrapolated if x lies outside upper or lower bounds
> > of xdata.
> > Dim nxp As Integer, ipmin As Integer, ip As Integer
> > Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
> > nxp = Application.Count(XData) ' Number of x data points
> > ' Extrapolate if x is less than xdata lower bound.
> > If X < XData.Cells(1).Value Then
> > x1 = XData.Cells(1).Value
> > x2 = XData.Cells(2).Value
> > y1 = YData.Cells(1).Value
> > y2 = YData.Cells(2).Value
> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> > ' Extrapolate if x is greater than xdata upper bound.
> > ElseIf X > XData.Cells(nxp).Value Then
> > x1 = XData.Cells(nxp - 1).Value
> > x2 = XData.Cells(nxp).Value
> > y1 = YData.Cells(nxp - 1).Value
> > y2 = YData.Cells(nxp).Value
> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> > ' Otherwise, interpolate within data range
> > Else
> > For ip = 1 To nxp - 1
> > x1 = XData.Cells(ip).Value
> > x2 = XData.Cells(ip + 1).Value
> > y1 = YData.Cells(ip).Value
> > y2 = YData.Cells(ip + 1).Value
> > If X >= x1 And X <= x2 Then
> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
> > End If
> > Next ip
> > End If
> > End Function
> >
> > Dave
> >
> > Bernard Liengme wrote:
> >> You can nearly always fit N points to polynomial of N-1 power.
> >> You can make a chart and use Add Trendline
> >> To put the coeffienceints in worksheets cell use LINEST
> >> For more on:Polynomial, non-linear, Trendline Coefficients
> >> and Regression Analysis
> >>
> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
> >> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
> >>
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "Steve" <steve_mowbray@hotmail.com> wrote in message
> >> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
> >> > Hi
> >> >
> >> > This is probably a simple task and it is my own lack of experience in
> >> > Excel -- I would like to derive a y value for an arbitrary x value from
> >> > a
> >> > array of known x's and known y's for some unknown function y = f(x) a
> >> > la:
> >> >
> >> > known x, y
> >> > 0.123, 4.567
> >> > 0.257, 10.4567
> >> > 0.4321, 20.3241
> >> > 0.703, 10.345
> >> > 0.804, 2.345
> >> >
> >> > say I want to derive a y value for x=0.5 from this data set using a
> >> > linear
> >> > or higher order fit -- is there an appropriate worksheet function for
> >> > this
> >> > or do I have to resort to programming?
> >> >
> >> > Many thanks in advance.
> >> > Steve
> >> >
> >> >
> > Bernard Liengme wrote:
> >> You can nearly always fit N points to polynomial of N-1 power.
> >> You can make a chart and use Add Trendline
> >> To put the coeffienceints in worksheets cell use LINEST
> >> For more on:Polynomial, non-linear, Trendline Coefficients
> >> and Regression Analysis
> >>
> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
> >> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
> >>
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "Steve" <steve_mowbray@hotmail.com> wrote in message
> >> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
> >> > Hi
> >> >
> >> > This is probably a simple task and it is my own lack of experience in
> >> > Excel -- I would like to derive a y value for an arbitrary x value from
> >> > a
> >> > array of known x's and known y's for some unknown function y = f(x) a
> >> > la:
> >> >
> >> > known x, y
> >> > 0.123, 4.567
> >> > 0.257, 10.4567
> >> > 0.4321, 20.3241
> >> > 0.703, 10.345
> >> > 0.804, 2.345
> >> >
> >> > say I want to derive a y value for x=0.5 from this data set using a
> >> > linear
> >> > or higher order fit -- is there an appropriate worksheet function for
> >> > this
> >> > or do I have to resort to programming?
> >> >
> >> > Many thanks in advance.
> >> > Steve
> >> >
> >> >
> >

"dgp@dodgeit.com" wrote:

> Note, that interpolating by equation fitting will not neccesarily match
> the data points in the table - there will be some error. The only time
> the polynomial will exactly match the data is if the polynomial order
> is equal to the number of data points minus one. Increasing the order
> of the polynomial can lead to some very wild lines though.

That is why (when interpolating) you only pass TREND or FORECAST the points
necessary for the interpolation, not the entire data set.

Jerry

Thanks -- that works -- at least in the sense that its intended in Excel --
will need to experiment with the right type of fit though.
Much appreciated
Steve

<dgp@dodgeit.com> wrote in message
news:1151410444.689390.83990@x69g2000cwx.googlegroups.com...
> Use curly brackets around the exponents:
> ^{1,2,3}
>
> Not parentheses:
> ^(1,2,3)
>
> Note, that interpolating by equation fitting will not neccesarily match
> the data points in the table - there will be some error. The only time
> the polynomial will exactly match the data is if the polynomial order
> is equal to the number of data points minus one. Increasing the order
> of the polynomial can lead to some very wild lines though.
>
> Dave
>
> Steve wrote:
>> <dgp@dodgeit.com> wrote in message
>> news:1151353235.936527.252830@p79g2000cwp.googlegroups.com...
>> > As far as I know, there is no built-in function to perform
>> > interpolation from a table.
>> >
>> > You might be able to use TREND (simpler to use than LINEST IMO) to fit
>> > a polynomial curve to a set of data. But this isn't really
>> > interpolation, it's equation fitting. Still, if you can get a good fit
>> > with a polynomial function then TREND might work for you. TREND/LINEST
>> > is also useful if you're working with raw data that has scatter,
>> > because it will find the polynomial that fits the all of data with
>> > minimum error.
>> >
>> > For a linear fit:
>> > New_y = TREND(Known_y's, Known_x's, New_x)
>> > For a nth-order polynominal fit:
>> > New_y = TREND(Known_y's, Known_x's^{1,2,...n}, New_x^{1,2,...n})
>>
>> I did a linear fit in the first instance:
>>
>> =TREND($G$6:$G$21,$E$6:$E$21,K6)
>>
>> This worked but the data does not fit a straight line -- when I tried to
>> create a cubic fit syntax above
>>
>> =TREND($G$6:$G$21,$E$6:$E$21^(1,2,3), K6^(1,2,3))
>>
>> I get a formula error -- is there something else I need to add to the
>> syntax?
>>
>> >
>> > For true linear interpolation/extrapolation I wrote the following VBA
>> > function. to perform linear intepolation/extrapolation. This function
>> > will linearly interpolate from point-to-point in a set of data. Note,
>> > that the data must be sorted by x.
>> >
>> > Function Interpolate(XData As Range, YData As Range, X As Double)
>> > As Double
>> > ' Function to linearly interpolate from array of data.
>> > ' xdata - Range containing known x's
>> > ' ydata - Range containing known y's
>> > ' x - Desired value of x
>> > ' Interpolate - Interpolated value of y at desired value of x
>> > '
>> > ' Note:
>> > ' 1. xdata and ydata must have same number of points.
>> > ' 2. xdata values must be monotonically increasing.
>> > ' 3. y will be extrapolated if x lies outside upper or lower bounds
>> > of xdata.
>> > Dim nxp As Integer, ipmin As Integer, ip As Integer
>> > Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
>> > nxp = Application.Count(XData) ' Number of x data points
>> > ' Extrapolate if x is less than xdata lower bound.
>> > If X < XData.Cells(1).Value Then
>> > x1 = XData.Cells(1).Value
>> > x2 = XData.Cells(2).Value
>> > y1 = YData.Cells(1).Value
>> > y2 = YData.Cells(2).Value
>> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
>> > ' Extrapolate if x is greater than xdata upper bound.
>> > ElseIf X > XData.Cells(nxp).Value Then
>> > x1 = XData.Cells(nxp - 1).Value
>> > x2 = XData.Cells(nxp).Value
>> > y1 = YData.Cells(nxp - 1).Value
>> > y2 = YData.Cells(nxp).Value
>> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
>> > ' Otherwise, interpolate within data range
>> > Else
>> > For ip = 1 To nxp - 1
>> > x1 = XData.Cells(ip).Value
>> > x2 = XData.Cells(ip + 1).Value
>> > y1 = YData.Cells(ip).Value
>> > y2 = YData.Cells(ip + 1).Value
>> > If X >= x1 And X <= x2 Then
>> > Interpolate = (X - x1) / (x2 - x1) * (y2 - y1) + y1
>> > End If
>> > Next ip
>> > End If
>> > End Function
>> >
>> > Dave
>> >
>> > Bernard Liengme wrote:
>> >> You can nearly always fit N points to polynomial of N-1 power.
>> >> You can make a chart and use Add Trendline
>> >> To put the coeffienceints in worksheets cell use LINEST
>> >> For more on:Polynomial, non-linear, Trendline Coefficients
>> >> and Regression Analysis
>> >>
>> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
>> >> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>> >>
>> >> --
>> >> Bernard V Liengme
>> >> www.stfx.ca/people/bliengme
>> >> remove caps from email
>> >>
>> >> "Steve" <steve_mowbray@hotmail.com> wrote in message
>> >> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
>> >> > Hi
>> >> >
>> >> > This is probably a simple task and it is my own lack of experience
>> >> > in
>> >> > Excel -- I would like to derive a y value for an arbitrary x value
>> >> > from
>> >> > a
>> >> > array of known x's and known y's for some unknown function y = f(x)
>> >> > a
>> >> > la:
>> >> >
>> >> > known x, y
>> >> > 0.123, 4.567
>> >> > 0.257, 10.4567
>> >> > 0.4321, 20.3241
>> >> > 0.703, 10.345
>> >> > 0.804, 2.345
>> >> >
>> >> > say I want to derive a y value for x=0.5 from this data set using a
>> >> > linear
>> >> > or higher order fit -- is there an appropriate worksheet function
>> >> > for
>> >> > this
>> >> > or do I have to resort to programming?
>> >> >
>> >> > Many thanks in advance.
>> >> > Steve
>> >> >
>> >> >
>> > Bernard Liengme wrote:
>> >> You can nearly always fit N points to polynomial of N-1 power.
>> >> You can make a chart and use Add Trendline
>> >> To put the coeffienceints in worksheets cell use LINEST
>> >> For more on:Polynomial, non-linear, Trendline Coefficients
>> >> and Regression Analysis
>> >>
>> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
>> >> http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
>> >>
>> >> --
>> >> Bernard V Liengme
>> >> www.stfx.ca/people/bliengme
>> >> remove caps from email
>> >>
>> >> "Steve" <steve_mowbray@hotmail.com> wrote in message
>> >> news:h9xng.91194$wl.87348@text.news.blueyonder.co.uk...
>> >> > Hi
>> >> >
>> >> > This is probably a simple task and it is my own lack of experience
>> >> > in
>> >> > Excel -- I would like to derive a y value for an arbitrary x value
>> >> > from
>> >> > a
>> >> > array of known x's and known y's for some unknown function y = f(x)
>> >> > a
>> >> > la:
>> >> >
>> >> > known x, y
>> >> > 0.123, 4.567
>> >> > 0.257, 10.4567
>> >> > 0.4321, 20.3241
>> >> > 0.703, 10.345
>> >> > 0.804, 2.345
>> >> >
>> >> > say I want to derive a y value for x=0.5 from this data set using a
>> >> > linear
>> >> > or higher order fit -- is there an appropriate worksheet function
>> >> > for
>> >> > this
>> >> > or do I have to resort to programming?
>> >> >
>> >> > Many thanks in advance.
>> >> > Steve
>> >> >
>> >> >
>> >
>