[an error occurred while processing this directive]

Spreadsheets in Physics

In the first and second year physics labs, students learn how to use spreadsheets for graphing, simple data analysis, and linear and non-linear curve fitting. Students in upper level courses are expected to use the skills they acquire in their first two years.

General Physics Laboratories: graphing and analysis

About half of the general physics laboratories use student Thinkpad computers to control the experiments and acquire data. Although the Pasco Science Workshop software includes useful spreadsheet and analysis functions, we prefer to export the data to Excel for analysis. Since the students use Excel in many courses, we can take advantage of expertise they have already developed and also prepare them better for the use of Excel in other courses.

In these labs, the spreadsheet is used principally for graphing and simple data analysis (averaging, etc.).

Second year labs: least squares fitting

In the second year physics courses, physics students learn to use Excel for curve fitting. Students are first shown how to analytically determine the least-squares slope and intercept for a linear fit.

After they have implemented these expressions in spreadsheets and grown accustomed to their use, we show them the built-in linear regression functions.

Next students are introduced to nonlinear curve fitting. Least squares values of fit parameters can be found analytically only for the simple expressions, principally those that are linear in the fit parameters. Few expressions other than polynomials meet this criterion.

Without an analytic solution, some iterative scheme of guessing the correct answer must be employed.

Students are presented with data to be modeled with a non-linear expression. Radioactive decay is often used. Students define the fit function column in terms of cells holding the fit parameters. The define another column holding a measure of the deviation of the points from the theoretical expression. For least-squares analysis, this column will hold the squares of the difference between the measured and fitted curves.

The deviation column is summed to form a measure of goodness of fit. For least squares analysis, this is chi squared.

Next students manually adjust the fit parameters to achieve the best fit they can, trying to minimize chi squared or other measure of goodness of fit. By this process, the students develop a good appreciation of the process of fitting the data.

Next they are introduced to the "Solver" function in Excel. Solver will minimize or maximize the contents of a chosen cell by adjusting the values of other specified cells, just what we need for curve fitting. The students record the best chi squared they achieved by hand, and then they use solver to repeat the process automatically.

While Solver is not as elegant as the Levinberg-Marquardt algorithm included in many scientific graphics packages, it is very simple to use and is built in to software the students are already using. We have found it suprisingly effective. A notable deficiency is the lack of any estimate of the accuracy of the fit parameters.

At right is a fit of a student's data from the Cavendish experiment measuring the universal gravitational constant. The students fit the deflection of the arms with the expression from a damped harmonic oscillator, from which they can extract the mean deflection and the spring constant. You may wish to view the handout "instructions on fitting data with Excel," given to the second year lab students.

It is evendent that Excel's Solver function can fit even rather complex data. As with the Marquardt algorithm or any local minimization method, reasonable guesses of the fit parameters are needed.

Comments on this page should be sent to matthews@wfu.edu.

[an error occurred while processing this directive]