Microsoft KB Archive/34318

Curve Fitting with Excel Using the Newton Method

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

SUMMARY

The following instructions describe how to perform polynomial curve fitting with Microsoft Excel. This technique will utilize a user-defined function (NEWTON) based on the algorithm of Newton's Divided Differences. For more information on curve fitting with Microsoft Excel, query on the words:

   trend and fit and excel

The TREND function can be used when there is a wide range of x-values, and the NEWTON function is much more accurate on the range -4 to +4. The TREND instructions end with a brief outline of the math involved. An example of curve fitting is where a scientist has data, such as current versus voltage, and needs to make projections beyond the data range (extrapolation at voltage 9, using TREND) or make estimates between the acquired data points (that is, interpolation at voltage 3.5, using NEWTON).

Assume the following data:

   At Voltage (x's)   The Resulting Current (y's)
   ----------------   ---------------------------

           1                    1.5
           2                    3.7
           3                    5.0
           4                   13
           5                   16
           6                   30
           7                   35
           8                   50

This data is definitely nonlinear (that is, 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.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements.

You can create your own function for polynomial curve fitting and interpolation. It is assumed here that the following code has been entered on a macro sheet named "Macro1". Before starting, you should know the following Microsoft Excel macro functions (page numbers refer to the "Microsoft Excel Macros and Functions" manual for versions 2.x):

   1. =RESULT(type) (page 343)
   2. =ARGUMENT(type) (page 251)
   3. =INDEX(array, position) (page 60)
   4. =SET.NAME(variable, value) (page 354)
   5. =SET.VALUE(ref,value) (page 356)
   6. =FOR()..=NEXT() (page 280)
   7. =RETURN(value) (page 343)

   Starting in cell A1 of the macro sheet, enter the code as follows:

   A1:  newton
   A2:  =RESULT(64)
   A3:  =ARGUMENT("xo",64)
   A4:  =ARGUMENT("fx",64)
   A5:  =ARGUMENT("newx",64)
   A6:  v=INDEX(xo,1)
   A7:  w=INDEX(xo,2)
   A8:  x=INDEX(xo,3)
   A9:  y=INDEX(xo,4)
   A10: =SET.NAME("Result",Result.area)
   A11: =SET.NAME("size",ROWS(xo))
   A12: =FOR("k",1,size,1)
   A13: =SET.VALUE(INDEX(Result,k,1),INDEX(fx,k))
   A14: =NEXT()
   A15: =FOR("i",2,size,1)
   A16: =FOR("j",2,i,1)
   A17: =SET.VALUE(INDEX(Result,i,j),((INDEX(Result,i,j-1)-
        INDEX(Result,i-1,j-1)))/(INDEX(xo,i)-INDEX(xo,i-j+1)))
   A18: =NEXT()
   A19: =NEXT()
   A20: =RETURN(C1+D2*(newx-v)+E3*(newx-v)*(newx-w)+F4*(newx-
        v)*(newx-w)*(newx-x)+G5*(newx-v)*(newx-w)*(newx-x)*(newx-
        y))

The argument "xo" refers to the known x's, the argument "fx" refers to the known y's, and the argument "newx" refers to the new value of x in question. The value returned is a function of a fourth degree approximating polynomial.

You must now define a range to hold the results of the various calculations made during the operation of this macro. Highlight the range C1 through V30 and name this range "Result.area" by choosing Define Name from the Formula menu. This range is large enough to handle up to 20 data points (known x's). Name this as a function macro by choosing Define Name from the Formula menu and checking it as a function.

It is assumed that the known x data listed above has been placed in cells D1 through D8, and the known y data has been placed in cells E1 through E8 on a worksheet named "Sheet1". To use this function, do the following:

Select cell G1 on Sheet1. From the Formula menu, choose Paste Function and select Paste Arguments. Find the NEWTON function at the bottom of the function list, highlight it, and press ENTER.
For the arguments, type the following and press ENTER:

$D$1:$D$8,$E$1:$E$8,D1

Highlight the range G1 through G9 and choose Fill Down from the Edit menu. The curve-fitted values will now appear in cells G1 through G9.

For an example of interpolation, choose cell H1 and enter the NEWTON function using Paste Function from the Formula menu. For the arguments, type the following and press ENTER:

   $D$1:$D$8,$E$1:$E$8,3.5

Please note that for interpolation, the NEWTON function is usually more accurate than the TREND function on short intervals close to zero.

NOTE: in Microsoft Excel version 5.0, it is not necessary to create your own function for polynomial curve fitting and interpolation. The functionality is built-in. For additional information, choose Search for Help on.... from the Help menu, type the word "trendlines" (without the quotation marks), choose Show Topics; select a topic and choose Go To.

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 :