The information in this article applies to:
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:
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:
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.
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:
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 : |
Last Reviewed: March 12, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |