Lab C Graphing Exercise
|
Age (weeks) |
Height of Plants (cm)
|
|
Plant 1 |
Plant 2 |
Plant 3 |
Plant 4 |
|
1 |
1.0 |
0.8 |
1.1 |
1.2 |
|
2 |
2.1 |
2.0 |
1.8 |
2.3 |
|
3 |
4.0 |
4.3 |
3.6 |
4.1 |
|
4 |
6.2 |
6.0 |
5.0 |
6.2 |
|
5 |
9.0 |
9.2 |
7.5 |
9.3 |
|
6 |
11.5 |
11.3 |
9.1 |
11.0 |
Table 1. Example Control Data for Fast Plant Experiment. These control plants were grown under 24 hour lighting conditions and watered with tap water. Plants from each cell were identified and measured weekly. Measurements were taken from the soil to the end of the main stem of each plant.
|
Age (weeks) |
Height of Plants (cm)
|
|
Plant 1 |
Plant 2 |
Plant 3 |
Plant 4 |
|
1 |
1.0 |
0.8 |
1.1 |
1.2 |
|
2 |
2.1 |
2.0 |
1.8 |
2.3 |
|
3 |
3.0 |
3.3 |
3.2 |
3.1 |
|
4 |
4.2 |
4.0 |
4.5 |
4.2 |
|
5 |
5.0 |
5.2 |
5.1 |
5.3 |
|
6 |
6.5 |
6.3 |
6.1 |
6.0 |
Table 2. Example Experimental Data for Fast Plant
Experiment. These experimental plants were grown under 8 hour lighting conditions
and watered with tap water. Plants from each cell were identified and measured
weekly. Measurements were taken from the soil to the end of the main stem of
each plant.
Inserting Functions in an Excel Spreadsheet
- You should calculate both the "Average" (also called the Mean) and
"Standard Deviation" for your control plant and experimental plant
data.
- In your Excel spreadsheet, choose the box where the functional value will be entered (in the example below, it will be entered in cell "G3").
- Choose "Insert" and "Function" from the Excel menu. You should
see a "Paste Function" box as in the figure below. Select "AVERAGE"
in the "Statistical" Function Category.
- Highlight the values that you want to be calculated in the average or standard deviation. You may need to move the "Function" window to see the cells that you will highlight. (Note that you do NOT want to include the AGE column – in this table "A3" - in this calculation!)
- Be sure you know how to select cells - you will use this same method of cell selection as you prepare a graph using Excel. In this example, we would like to take the AVG of cells B3 to E3.
- Your TA will demonstrate how to cut and paste Functions to fill the spreadsheet.
This eliminates the need to select each individual row for each functional
calculation. Excel automatically shifts the cells to be used in each function.
Using Excel to Prepare a Graph
Step 1 of 4: Chart Type
- Choose the graph wizard which you would like to use (for these data, it’s
best to use a line graph format). Click on the first line graph in
the second row. Notice that there is a description of each of the "Chart
sub-types" when each is selected. In this case, our selected graph is
a "Line with markers displayed at each data value".
- When you are finished with this step (and all other steps), click on the
"Next>" button to get to the next step. If you need to go back to
earlier steps, click on the "<Back" button.
Step 2 of 4: Chart Source Data
- When the "Data Range" tab has been selected, choose the data that
you would like to graph by highlighting the boxes. Make sure "Columns"
is selected in "Series in:". These data will be placed on
the y-axis. You may select the data for all four individual plants, and Excel
will automatically draw four different lines for each of these four "series"
of data. You should automatically see what the graph will look like!
- Note that the data for each plant is labeled "Series 1", etc. At this point,
you must designate your X-axis values. To do this, in "Step 2 of 4" click
on the "Series" tab. Place the cursor in the "Category (X) axis
labels" range box. Then go to your spreadsheet and select the cells that
you want to include in the x-axis (age of plants).
- In this box, you can also change the name for the legend from "series
1" to your choice by selecting each series and writing the new name in the
"Name" box. Here, I’ve changed the name of "Series 1" to "Plant
1". Notice that the name changes in the Legend box at the right of the graph.
Step 3 of 4: Chart Options
(note that you can also access "Chart Options" from the Excel "Chart" Menu item after you have prepared your graph)
- There are many options (tabs) in this step. In "Titles", you can
record a Title for your graph, and label the X- and Y-axes.
- You don’t really need to change anything in any of the other options. You may remove the legend if you only have one line on the graph, but it is necessary to have a legend if you have several lines on the graph (such as in this example). You may also adjust the Gridlines of your graph.
Step 4 of 4: Chart Location
- Save your graph with a descriptive file name. You may keep each graph as a different sheet in your Excel workbook. When you are finished preparing your graph, click on the "Finish" button.
Fine-tuning Your Graph
- When you are finished preparing your basic graph, you may make further changes to it. You may change the position of the titles and labels by selecting them on the graph and dragging them to a new location.
- Adjust font size or style by clicking on each label and making these changes.
- You may also change the appearance of the graph area by clicking on it and changing settings such as the background plot color (the default color is gray).
Adjusting the Scale of the X-axis
- If you look at your graph, you will probably observe that the X-axis values are not arranged correctly.
- To adjust the scale on the X-axis, click on the X-axis of the graph. You should get a box that looks like the one below. If you don’t, it means that you have not clicked on the correct part of the X-axis – try again.
- Make sure the "Value (Y) axis crosses between categories" box is de-selected
(by default it is selected, which leads to odd X-axis values).
- You can also adjust the gridlines that are present on your graph by selecting the "Patterns" tab. Remember that this is just adjusting the X-axis. To make these changes to the Y-axis, you will need to click on the Y-axis on your graph.
Adding Standard Error Values (As Error Bars)
- Graph the average height values (instead of each individual plant)
by selecting the "Average Height" column instead of the four individual plant
columns. On this type of graph, you may include error bars to give an idea
of the variability\ in plant heights that were used to calculate the average
height.
- After preparing a graph of the average heights of the plants at each age,
click on the plotted line. You should see a "Format Data Series" box
as shown below.
- Select the "Y Error Bars" tab. To include the Standard Error bars,
you will want to select to Display "Both" plus and minus errors.
- Select the "Custom" button.
- You will use the same Standard Deviation data for both the "+" and "-" data.
First place the cursor in the "+" box. Then, go back to your data table sheet
(Excel has a great tab system on the bottom of your workbook that you can
use). Select the cells in the Standard Deviation Column (where you inserted
the Standard Deviation function for each age). You should see this range of
data in the "Custom +" box as below.
- Do the same for the "Custom -" box. Excel automatically assigns the
matching standard deviation value to each data point on your graph!
- You can check this out by making sure the + and - error bars that appear
on your graph match your calculated standard deviations.
- Your Teaching Assistant may instruct you to prepare additional statistical analysis of your Fast Plant data.