**pre-reqs**: [kinematics](http://blog.dotphys.net/2008/09/basics-kinematics/) *I don’t think you need [part I of this](http://blog.dotphys.net/2008/09/basics-making-graphs-with-kinematics-stuff/) if you don’t want*

So, you still want to make a graph with that kinematics data? You think that graphs on paper are too barbaric? Well, if you are ready, you can use a spreadsheet. But be careful. If you don’t know what you are doing, you can cause some damage (much like flying a 747 after reading a blog about it). Speadsheets allow you to do a couple of things.

- make pretty graphs
- fit mathematical functions to data

Of course they actually do much more – but you need [“clippy”](http://en.wikipedia.org/wiki/Clippy) to help you with that.

First, what software do you use? I think most people will immediately go for Microsoft Excel. I have to admit, this is what I use because I am so familiar with it. Many people already have this also. Truthfully, it is a good spreadsheet program (but not perfect). There are some free alternatives:

- Open Office – I use the Mac OS X variant Neo Office
- Online spreadsheet like Zoho) or Google Docs. Both of these are fairly useable.
- Other – like Apple’s spreadsheet or other non-free stuff.
- A final excellent option is Vernier’s Logger Pro. Although it is not free (nor perfect) it is not too expensive and can be covered by a school site license

For this tutorial, I will show explicitly how to make graphs using MS Excel. I was going to use open office, but in order to fit a polynomial to data, you have to do some more serious stuff. The basic idea is the same no matter what you use.

Picking up from [Part I](http://blog.dotphys.net/2008/09/basics-making-graphs-with-kinematics-stuff/) I will make a graph using the same position and time data as before (just in case you want to compare). In that situation, I pretended to throw a ball in the air and use video analysis to get position and time data. This is what it looks like:

The first thing to do is just make a graph. To do that, I need to put the data into the spread sheet. It should look something like this

![Data Entry](http://blog.dotphys.net/wp-content/uploads/2008/09/data-entry.jpg)

Make sure that you label your columns (trust me). If you do this, when you go to make a graph it will make it easier to tell what is what. Now for the graph. Highlight the data including the names at the top of the columns. Click the “make chart” button (or whatever it is called). In Neo Office it looks like this: ![Chart Button](http://blog.dotphys.net/wp-content/uploads/2008/09/chart-button.jpg)

This will bring up a “chart wizard” or something like that. Basically, it will ask you a series of questions about what kind of graph you want to make. (Neo Office first “asks” you where you want your graph to be – you need to drag and highlight the size and position of where the graph will appear. MS Excel asks you this at the end of the wizard.)

The only non-default thing that is really important (if you have the data entered like I do) is to tell the program what TYPE of graph. Choose XY Chart in Neo Office or XY Scatter in Excel.

![Excel Wizard 1](http://blog.dotphys.net/wp-content/uploads/2008/09/excel-wizard-1.jpg)

The other thing it will ask you is if you want just “dots”, “dots with lines”, or just a “line”. It really doesn’t matter *too* much, but technically it would be best to just have the “dots”. The last things is the titles. If you are going to include this graph in a lab report OR if you plan on looking at it in the future, make sure you give appropriate titles and axes labels (with units).

Here is the graph I get (I turned off the gray background – yours may look different).

![Excel Graph 1](http://blog.dotphys.net/wp-content/uploads/2008/09/excel-graph-12.jpg)

Well, is that it? How easily you forget. What is the point of making a graph? On point may be to present data – ok, did that. The other important reason is to fit mathematical function to the data. Most spread sheets can do that.

First select the graph (click on it once) then right-click on one of the data points. This should bring up a menu like this:

![Pull Down Menu](http://blog.dotphys.net/wp-content/uploads/2008/09/pull-down-menu.jpg)

Choose “Add trendline”. What is a trendline? Exactly the point, no one really knows. I guess it is what business people call a regression line. Nonetheless, it will bring up this window:

![fit 1](http://blog.dotphys.net/wp-content/uploads/2008/09/fit-1.jpg)

Choose a polynomial fit of order 2. Before you click “ok” also click the “options” tab.

![options](http://blog.dotphys.net/wp-content/uploads/2008/09/options.jpg)

Make sure you check “display equation on chart”. You can also check to include the R squared value (which gives a measure of how close your function fits your data. An R-squared value of “1” is a perfect fit.) Here is what I get:

![Graph fit](http://blog.dotphys.net/wp-content/uploads/2008/09/graph-fit.jpg)

A couple of points:

- Excel hates me. Why oh why Excel? I want to plot vertical position (y) vs. time (t). Excel fits a function to this using the variable y and x. Why not use what I want? There is no option to change this (at least not in Office 2003 for the Mac). Ok, well, just realize that this is not a trajectory plot of y vs. x
- Not as big of an issue, but notice how Excel makes a “trendline” that does not go past the range of the data I used to plot that line. I guess this can’t be used to project trends – can it?
- **Excel sits on a throne of lies**. What? I thought you liked Excel. Sort of. The problem is that the way Excel calculates the fitting equation is not correct. It gives an ok answer, but if you did it by hand (which is possible) it would give slightly different coefficients. For your uses, this won’t matter too much.

**Now for some kinematics stuff**

Ok. Now I have a mathematical model that seems to fit the data, what do I do with it? For motion with a constant acceleration, there is a [known relationship between position, velocity, and time](http://blog.dotphys.net/2008/09/basics-kinematics/). All need to do is compare this to my function that fit my data. Maybe this picture will help:

![Matching Equations](http://blog.dotphys.net/wp-content/uploads/2008/09/matching-equations.jpg)

Here I am matching the kinematic equation with the output from Excel. Pretend that the “x’s” from Excel are the “t’s” in the kinematic equation. This allows me to match up the coefficients and I get:

![matching](http://blog.dotphys.net/wp-content/uploads/2008/09/matching.jpg)

The thing that throws people is that the acceleration is TWICE the coefficient in front of the squared term. The other important thing to realize is units. Excel is just a computer and not very bright. Even if you label the column “position (m)” thus clearly indicating that your positions are in units of meters, excel just thinks it’s a number. You are responsible for the units. In this case, the acceleration would be in m/s^{2}.

The value obtained for the acceleration seems reasonable – it is in the negative y direction and around 9.8 m/s^{2}.

What about y_{0} and the initial velocity? In this case, the initial position is 2.3176 meters (really, that is overly precise – but it doesn’t matter for now). That means that is the position when the time was 0.0 seconds (note that *t* = 0 seconds is an arbitrary designation. It is when the clock, whatever that was, was started.)

So that is it, you should be able to go out and do some good. Sorry that I used Excel, I will try to come back and add notes on how to do this with Open Office at some time in the future.

Try gnumeric – http://www.gnome.org/projects/gnumeric/

It’s open source, largely Excel compatible (even emulates a bunch of Excel bugs if you ask it to) and at least current versions can for instance do curve fitting just fine.

I very much prefer Gnumeric over OpenOffice.

If you want to print out a nice copy of the graph with an equation that looks good, you can just click in the equation and delete the x’s to replace with t’s, or add units, or whatever.

If you are using Office 2007 the menus look a bit different.

The thing to do if you want to interpolate or extrapolate data in Excel (dirty, but often good enough for a quick look) is to enable the Analysis Toolpak and do a second-order regression on your data. For plotting you then make a dummy row/column containing the increments in your independent variable, use the coefficients you get from the regression to evaluate the quadratic at each of those increments, and plot. This is not quite the same as what Excel does for a trendline.

If you get really serious and have access, though, I recommend Matlab and its statistical toolbox. Many more built-in functions (in case you want to see if something is a better fit than just a polynomial but still pretty quickly), better control on bugs, much better free libraries available around the internet.

You can EASILY extend line past the data in EXCEL. Examine the Options window after you choose Add trendline. You see a couple of boxes (Forward Backward) bundled together and labeled “Forecast”. Simply select a number of x units … Voila!