Smooth Curve

[Guest]

If I have 3 (or 4, or 5, ...) values (x1,y1; x2,y2; x3; y3; etc) in a 2xN
array in Excel, how can I derive the formula (y=F(x)) for a simple smooth
curve that would pass through all of those points - so that I can calculate
the value of a new value of Y for a new value of X (not in the table, ie.
interpolation). The simple curve would be similar to the curve traced by a
"spline" held fixed at the points already made available in the 2xN array

 

[Guest]

Hi Tony:

Checkout the description for the FORECAST() function in Excel Help. It can
both extrapolate as well as interpolate between two points.

 

[Lori]

Try fitting a polynomial of degree 1 less than the number of points. eg
suppose columns A and B contain:

A B
1 2
2 5
3 8
4 9
5 10
6 ?

Ideally you would use the trend function to find the missing value

=TREND(B1:B5,A1:A5^{1,2,3,4},A6)

but it doesn't seem to work on my version (2002 SP3), I think these
functions were improved in 2003 version though. An alternative which
does work is:

=SUM(LINEST(B1:B5,A1:A5^{1,2,3,4})*A6^{4,3,2,1,0})

and gives the value 17 for the missing value above.
If there are a large number of data points you can use
TRANSPOSE(ROW(1:n-1)) in place of{1,2,..,n-1}.

 

[Bernard Liengme]

Lori,
But this works, as expected =TREND(B1:B5,A1:A5^{1,2,3,4},A6^{1,2,3,4})

 

[Lori]

Bernard - Thanks for the correction - thought I might be missing
something

 

[Guest]

Thanks for your help. Unfortunately, the suggestions provide for a "straight
line" approximation using the least squares method - correct me if I'm wrong.
I'm looking for a curve approximation to the points provided.

The approach that I'm researching is shown in
http://www.bergen.org/AAST/projects/3DSMaxTutorial/mathematics.html
or, to be even more complicated, in
http://www.mvps.org/directx/articles/catmull/

 

[Lori]

Tony - no, the method above fits a polynomial to the data of the form:

y = a + bx + cx² + dx³ + ...

and passes through all the data points. Try Bernard's formula on each
of the points in the example. To fit a straight line to the data remove
the exponentials: ^{1,2,3,..} from the formula.

You can check this by plotting the points on an XY chart and add a
trendline. If you right click the trendline there are options to adjust
to a polynomial curve and to display the equation.

 

[Guest]

OK - I agree that the TREND function provides for a polynomial fit, and my
testing shows that this provides a suitable solution to my problem. Thanks
to Lori, Bernard, and Gary's student