Curve Fitting with Excel Using the TREND Function

 Last reviewed: August 20, 1995
Article 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.

  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)

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

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

KBCategory: kbusage
KBSubcategory:

Additional reference words: 7.00 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 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 20, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.