A polynomial fitter for Intermediate and Advanced Laboratory

for Microsoft Excel spreadsheets.

This program is intended for use in the Intermediate and Advanced Laboratory use, but is freely available to anyone who may find it useful in their personal work.

This spreadsheet calculates fits to data with errors. It performs

the following fits.

To use this spreadsheet, open it, and save it immediately to your floppy with an appropriate name. It is READ ONLY on the system disks. You are now ready to enter your own data.

The user must enter data in the form:

If you have more data, insert the necessary number of blank lines somewhere after the first data or before the last data. Before entering your new data, you must first fill the equations by selecting the line of data and formulae just above your blank area. This is done by first clicking on the row number. By dragging on the small box to the left of the highlighted line, you may automatically fill down all of the formulae on that line to the blank lines. You are now ready to type in your data.

Once your data is typed in, the fits are done automatically. You may examine the chi-squares for each point at the end of each line, or the fit value for each data.

To remove data:

This can be done in two ways. First, simply delete the row containing the data. Never delete the first or last rows. Finally, if you wish to temporarily remove a data point from the fit to see how the fit would change you must erase only the formulas to the right of the data. In this way you can keep track of problem points. To put the formulas back into any row select all of the formula cells in the row above the one you wish to restore. Then do a fill down into the blank areas. The fits will then include this row. If you wish only to see the chi-square of this point without it being used in the fit, then fill down only the chi-square column. This column is not used in the fit but is only for your benefit in judging the quality of the fit and the data.

Once you are satisfied with the chi-square and the fits, proceed to the area of the graphs.

The graphs display the data with the error bars, and the fits with error corridors. Most fitting packages do not do this. Many packages calculate chi-square incorrectly, and also ignore the errors, so use caution when using "canned" fitters.

Below the graphs you may find the following information:

1) Reduced chi-square

2) YOUR X

3) Your x error

4) Fit Y

5) Total Y error

The cells labeled "YOUR X" and "Your x error" are included so that the error in the data and the fit can be propagated thro

ugh to the Y fit value for any value of X. This is especially useful with one is applying a calibration curve to your analysis. One can thus get the "total error" for each of your "regular" data points.

If you wish to delete points from the fit there are 2 ways that this can be done. The first is to just delete the row (not the first or last!) which contains that data point. This is not recommended and does not supply with the full information that the point can provide. A better method would be to go to the formula section to the far right of the data that you entered. Note that in between your data and the formula section is a series of calculations. Do not remove these formulas. Select the cells in the formula section for the data you are thinking of eliminating from the fit. Clear the cells of the formulas (command B on Mac, clear all in the PC edit menu). This will remove the point from the calculation, but not from the graph. In addition, the chisquare of that data will still be calculated in the central columns. Once you have satisfied yourself that you wish (or not) to fully remove the point from ALL calculations, then delete the formula in the cell containing the individual chisquare. Now the reduced chisquare will no longer add in this point and can be interpreted as to whether it is acceptable or not.

Finally, there is the area above the graphs which contain the resultant fit coefficients and their associated errors.

can also be found for each of the three fits if you wish to use it.

Polyfit does a linear least squares minimization to fit the polynomials to the data. This simply that the total chisquare is mininized by varying each coefficient. This results in a set of linear equations. To solve linear equations, one must first calculate the matrix elements and then invert the matrix. The matrix

A more complex version of polyfit can be obtained from Prof. Newsom if other combinations such as Y=A+bx² are needed.

Solver should be used if the problem is nonlinear in the coefficients, such as a gaussian peak plus quadratic background or an exponential decay.