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" wrote in message
. 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" wrote in message
. 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

wrote in message
oups.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" wrote in message
. 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" wrote in message
. 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:
wrote in message
oups.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" wrote in message
. 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" wrote in message
. 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

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

wrote in message
ups.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:
wrote in message
oups.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" wrote in message
. 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" wrote in message
. 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

" 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