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

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

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

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:

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

2. For the arguments, type the following and press ENTER:

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

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

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

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.