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