Sunday, February 19, 2017
User Defined Functions Can Simplify a Calculation
It's rather difficult to do a curve fit in Excel due to the complexity of the formulas. The following example shows how user defined functions can simplify a procedure that finds a least squares fit to set of four points on the curve y=x3+3x2+3x+1. The point x0=-0.9, y0=0.004 gives the medians for the data and the fit coefficients, fit(_x,_y), of a cubic polynomial are for deviations from this point.
Another user function yfit is used to calculate points on the fitted curve for a particular x value.
The fill and drag procedure was used to fill in the table. A plot of the data is useful to compare the results.