Educators Online |
WebED Education, Outreach and TrainingA Short Course: Using Modeling and StatisticsExcel Graphing Tutorial Many of you may already know how to use Excel or another spreadsheet program to create simple graphics that allow students to explore data relationships. The purpose of this section is to review those methods using an example water quality dataset. You can download the dataset for the Paint Creek Watershed by clicking here. You will see a table that has four columns. The first two show values for the fish and macroinvertebrate biological indicators respectively. The third and fourth show the average and total suspended solids values at the same sites. There are also links for definitions of the variables. Based on the nature of variables, think of a preliminary hypothesis that relates one of the biological indicators to the two different sediment variables. As average sediment goes up, what should happen to the measure of biological diversity? Will average or maximum concentrations be a better indicator of the change? Now lets test this by graphing the relationship. To start, highlight the column of the biological variable of interest as listed below by clicking in the cell at the top of the column and then dragging to the bottom with the mouse button pressed. They use the Edit Copy command to start the copy process.
Now click into an area below the other text and use the Edit Paste command to paste the data in that area as shown below. Then do the same with one of the TSS data columns so that the two copies are side by side in the area below. We are doing this because this makes graphing of the data a little bit more straightforward. For the sophisticated user there are other ways to accomplish the same thing but they get more involved.
Now we are ready to start the graphing process. Highlight the two contiguous columns on the spreadsheet and click on the graphics wizard icon as illustrated. You will now be taken through a series of screens that allow you to create a graph. Your first choice relates to the type of graph you want to create.
We want to choose X-Y scatter. This kind of plot will allow us to see if there is a statistical relationship between two variables. If there is, we would expect the points to line up along some linear or non-linear path through the data. Choose the chart sub-type without any lines for now. Click Next to get to step 2 of the graphing process.
In step 3, fill in a title and the X and Y axis labels to conform to the variables you are plotting. Notice that you see a preview of the graph and that the titles and labels change as you type in the required information. Before you click Next, click on the Legend tab and turn off the legend by clicking in the checkbox you will see. Now click on Next.
Choose the option to put the graphic on on a new sheet. You should change the name to something more descriptive of what it shows from the default chart1. Now click on finish to see the final graph. What does it tell you about the relationship? Is it linear or non-linear? You can actually try to draw in a line that you think most closely fits the points. To do this, go to View Toolbars and turn on the Drawing toolbar. Then select the line icon to draw a straight line on the chart. To draw a curved line, choose Autoshapes, Lines, then the curved line icon. You can try this with a second pair of columns to compare the findings with the average and maximum values. Why do you suppose the results are different for these two measures of the same variable?
In the next section we review how you can extend this type of comparison using statistical analysis to measure the strength of the relationships between the variables.
Please contact Al-Azad Iqbal or Steve Gordon for Questions and Comments - Updated 10/2/07 |
 |
|
|
|
|
|
|
|
|
|
 |
 |
 |
 |
 |
 |
 |
 |
 |
|
 |
|
||||||||
