Microsoft KB Archive/34315

From BetaArchive Wiki

Curve Fitting with Excel Using the TREND Function





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


  1. 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)

  1. 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.
  2. 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 INFORMATION

Using 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:


  1. On the chart, select the series for which you want the polynomial curve.
  2. From the Insert menu choose Trendline.
  3. 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 :
Technology :


Last Reviewed: April 12, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.