Comparison of quadratic curve fitting

In a [previous post](/dotphysics/2008/09/basics-making-graphs-with-kinematics-stuff-part-ii/), I talked about how to plot kinematics data with a spread sheet and how to fit a quadratic function to the data. In the back of my head I remember "Don't trust Excel". I seem to recall someone claiming that Excel did not do a proper fit. To test this, I collected some data and used several methods to fit the data:

• MS Excel's built in function fitting
• Using the spread sheet (Excel) to manually calculate the best fit parameters
• Vernier's Logger Pro (version 3.6.1)
• Plot 0.997 - http://plot.micw.eu/ - a program derived from Sci-Plot

I already discussed how to add a quadratic fit in Excel using the built in tools. Perhaps later I will also discuss Logger Pro and Plot. But how do you come up with a function to fit data? The basic idea is to create a quadratic function and vary the parameters such that the deviation of the actual data from the function is minimized. That is much detail as I want to go into except for the following two links that I used:

• A good description of Least Squares Fit to Quadratic Data
• Part of a least squares fit involves solving three equations with three unknowns. Here is a description of using Excel's Solver to do this

To create data, I looked at the vertical position of a drop of water from a splash from a slow motion video. I can't remember where I found the video, but here is a screen shot:


I used [Tracker Video Analysis](http://www.cabrillo.edu/~dbrown/tracker/) to get the position and time data for one of the water drops.


I fit this data (using the methods described above) with a function of the form:


Note that there are no real units for position since I didn't take the time to "scale" the video. I could scale it by assuming the acceleration is -9.8 m/s² if I had wanted to. Here is what each method produced for the coefficients:


From this, it seems there is essentially no difference (except in rounding). Maybe Excel gives a bad result for linear fitting, I can try that later.

So, which program should you use to fit data? Here is the low-down.

• Manual: The main benefit of doing something like this manually is that you know exactly what is happening. Plus it makes you feel cool. The down side is that it is rather tedious.
• Excel: If you are familiar with this, go for it. Maybe there are some situations that Excel does it wrong, but looks ok in this case. The down side is that it is not free and the graphs it makes are not pretty
• Logger Pro: I like this program. It not only collects data from the vernier sensors, but makes a great stand-alone graphing program. It is not free, but there a couple of features I like. First, you can easily select which part of a data set you want to fit the function to. Second, it gives you uncertainties in the coefficients of the fitting function.
• Plot 0.997: I call it Plot 0.997 because if you just do a google search for Plot you will never find it. Plot 0.997 is a pretty good program. It makes really nice and customizable graphs. It's free. The down sides: it only runs on Mac OS X (but sci-plot is available for Windows), I found a couple of bugs and sometimes it is difficult to figure things out
• Other Stuff: There are many alternatives here. First, you math programs like Matlab, Mathematica, and Maple - oh and the free Octave. Then there is gnuplot. Also there is gnumeric. I don't have much experience with these.

There you have it. Go forth and fit data.