Curve Fitting with Excel Using the TREND Function
ID: Q34315
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
SUMMARY
The following information describes how to perform polynomial curve
fitting with the TREND function.
An Explanation of Curve Fitting
Curve fitting is useful for making projections beyond a data range
(extrapolation) or for making estimates between acquired data points
(interpolation). For example, suppose a scientist has collected data,
such as voltage versus resulting current, as follows:
Voltage Resulting Current
----------------------------
1 1.5
2 3.7
3 5.0
4 13
5 16
6 30
7 35
8 50
This data is nonlinear; therefore, the corresponding graph is not a
straight line (the actual formula used here was y=x^2-2*x+3 with the
values adjusted at random to represent real laboratory data). An
approximating formula for a curve that would best fit the known data
points is necessary.
Using the TREND Function for Curve Fitting
The following steps use the TREND function to fit a curve to the data
points from the above sample data.
- Enter the following data on a worksheet with the name "Sheet1":
a. Enter the x-data (1, 2, 3,...8) in cells D1 through D8. Also,
enter 9 in cell D9.
b. Enter the corresponding measured y-data (1.5, 3.7, 5, 13, 16,
30, 35, and 50) in cells E1 through E8.
c. Enter the increasing powers of x to regress against your
y-values to achieve a polynomial fit. In cell C1, enter the
formula "=D1^2" (without quotation marks) and select the range
C1 through C9. From the Edit menu, choose Fill Down.
d. Repeat step c in column B with the formula "=D1^3" (without
quotation marks), and in column A with the formula "=D1^4"
(without the quotation marks).
e. Enter the TREND function as an array formula in cells F1 through
F8. Select this range and enter the following formula in cell
F1 (do not press ENTER yet):
=TREND(E1:E8,A1:D8)
- Press CTRL+SHIFT+ENTER to enter this formula as an array formula.
The curve-fitted values will now appear in cells F1 through F8.
These values can be plotted along with the known y-values versus
the known x-values to achieve a graphical representation of the
quality of the initial y-measurements.
- To extrapolate a value at x=9, position the cursor in cell F9 of
Sheet1. Type the formula
=TREND(E1:E8,A1:D8,A9:D9)
and press the ENTER key. Note that when you specify the range of
new x-values, the equivalent higher powers of these x-values must
also be calculated and specified in the new x range (that is, the
new x-values must have the same format as the known x-values).
NOTE: The goal in curve fitting is to find a polynomial that
approximates the function (or set of known x-values and y-values) in
question. For instance, suppose we start with the function y=sin(x).
Most computer languages have the SINE function built in, but the
computer must go through extensive calculations to arrive at an
answer. If this function were nested in a loop, it could slow the
operation of the computer considerably.
MORE INFORMATIONUsing a Simpler Formula in Place of SINE
A quicker approach, if absolute accuracy is not necessary, is to
substitute a different formula in place of the SINE function that
approximates the original function in a predefined interval on the
x-axis. This formula is simpler and therefore faster for the computer
to calculate.
Assume you are interested in the interval from x=0 to x=1. An
approximating polynomial for y=sin(x) of degree 0 might be y=1, of
degree 1 might be y=x, and of degree 3 might be y=x+(x^3)/6. The
degree of the polynomial refers to the highest power of x used.
If you understand Taylor polynomials, you know why you skipped the
polynomial of Degree 2, and how you arrived at the other polynomials.
If you are not familiar with Taylor polynomials, do not worry about it
because Microsoft Excel will handle the details. The following is an
example:
DEGREE
0 1 3
x-value y=sin(x) | y=1 y=x y=x-(x^3)/6
-------------------------------------------------
0.000 0.000 1.000 0.000 0.000
0.200 0.199 1.000 0.200 0.199
0.400 0.389 1.000 0.400 0.389
0.600 0.565 1.000 0.600 0.564
0.800 0.717 1.000 0.800 0.715
1.000 0.841 1.000 1.000 0.833
The higher the degree of the approximating polynomial, the higher the
accuracy. Higher degree polynomials require more calculations, so
there is a point of diminishing returns. Also, high
degree-approximating polynomials may produce inaccurate, highly
fluctuating results (a good rule to follow is to use polynomials of
degree no higher than 5).
Microsoft Excel version 5.0
Microsoft Excel version 5.0 has the built-in capability to insert a
polynomial curve into your existing x-y scatter chart. To insert this type
of curve, follow these steps:
- On the chart, select the series for which you want the polynomial curve.
- From the Insert menu choose Trendline.
- From the Type tab, choose Polynomial with Order 2.
The polynomial curve is now added to your chart.
For information about another curve fitting technique, query on the
following word in the Microsoft Knowledge Base:
newton
Additional query words:
2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0
Keywords :
Version :
Platform :
Issue type :
|