Smooth Curve

T

Tony29

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
 
G

Gary''s Student

Hi Tony:

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

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}.
 
B

Bernard Liengme

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

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.
 
T

Tony29

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top