GRAPHING YOUR DATA IN EXCEL: PINTO SEED LAB EXAMPLE



GRAPHING YOUR DATA IN EXCEL: PINTO SEED LAB EXAMPLE

Steps for creating your histograms: (Total of 3 Histograms – one for each variable!)

1. Record your data in excel :

• columns (independent variable: dry/wet seed measurements)

• rows (dependent variable: # of trials/measurements)

|Mass (g) |Dry Seed |Wet Seed |

| |0.1 |0.9 |

| |0.1 |1.1 |

| |0.2 |0.8 |

| |0.2 |0.9 |

| |0.2 |0.8 |

| |0.2 |0.8 |

| |0.2 |0.7 |

| |0.2 |0.8 |

| |0.2 |0.9 |

| |0.2 |0.7 |

| |0.2 |0.9 |

| |0.2 |0.8 |

| |0.3 |1.1 |

| |0.3 |1 |

2. Highlight one column of data and click on sort and filter (upper right in the tool bar).

3. Select sort smallest to largest.

4. It will ask you if you want to expand your selection, click “continue with the current selection.”

5. You are now ready to set up your ranges/# of seeds in each range. Create a data table off to the side of your data like the example below. You may have more ranges – it depends on your data but your goal is 3-5 ranges.

|Size |0 - .3 |.4 - .6 |.7 - 1 |1.1 - 1.2 |

|Dry Pinto Seeds |15 |25 |30 |25 |

|Wet Pinto Seeds |20 |15 |45 |15 |

6. Highlight your range data table (including the words!), then go to Insert, and click on charts. Insert the bar chart for this type of data.

[pic]

7. Once the graph is inserted, go under chart layouts and insert the correct format shown down below. You can easily change the axis titles and main title of the graph.

[pic]

[pic]

Steps for using Excel to calculated Descriptive Statistics:

1. Find the Mean for each column of data. Click on the box where you are inserting the Mean (average), then go to Formulas, and insert function. Search for “Average”. Click on the table icon and highlight all the numbers in the column to find the average. Click OK. Repeat for all columns.

2. Find the Median for each column of data. Click on the box where you are inserting the Median, then go to Formulas, and insert function. Search for “Median”. Click on the table icon and highlight all the numbers in the column to find the average. Click OK. Repeat for all columns.

3. Find the Mode for each column of data. Click on the box where you are inserting the Mode, then go to Formulas, and insert function. Search for “Mode”. Click on the table icon and highlight all the numbers in the column to find the average. Click OK. Repeat for all columns.

4. Find the Range for each column of data. Click on the box where you are inserting the Range, subtract the smallest number from the largest number enter the value into the cell.

5. Find the standard deviation for each column of data. Click on the box where you are inserting the standard deviation, then go to Formulas, and insert function. Search for “ST DEV”. Click on the table icon and highlight all the numbers in the column to find the standard deviation. Click OK. Repeat for all columns.

| |Mass (g) |

|Descriptive Statistics |Dry Seed |Wet Seed |

|Mean |0.497727 |0.8337879 |

|Median |0.5 |0.8 |

|Mode |0.4 |0.8 |

|Range |  |  |

|Standard Deviation |0.17056 |0.1638329 |

Now you are ready to create your graphs:

1. Highlight the mean (average) information (including the words!), then go to Insert, and click on charts. Insert the bar chart for this type of data.

[pic]

2. Once the graph is inserted, go under chart layouts and insert the correct format shown down below. You can easily change the axis titles and main title of the graph.

[pic]

[pic]

3. Click on the bars, then go up to the tool bar and locate the layout tab. Click on the Error bars and scroll down to error bar options.

[pic]

4. In error bar options, under vertical error bars click on both directions. Under the error amount click on custom.

[pic]

5. You will highlight all standard deviation information for both the positive and negative values, click OK. Your error bars should show up on each of your bar graphs. The % error bar with standard deviation represents the variability of the data or the range of 68% of your data.

[pic]

[pic]

6. You will insert your table information and your graph into your lab report. Copy and paste it into the word document and make sure to title your chart and graph using APA format. For example:

• All graphs are titled – Figure #. Title

• All data tables are titled – Table #. Title

FINDING YOURT T-TEST VALUES

Descriptive statistics

There are two major summary statistics that one usually wants. One is a measure of the central tendency of the numbers, like a mean (average), median, or mode. The second is a measure of how different the numbers are, which we measure using standard deviation, standard error, or variance.

• Enter your data in columns.

• Click on the empty cell after your last number in a column.

• Click on formulas

• Click on insert function

• Click on "average"

• If it is not on the list, search for it and highlight it before moving on.

• Do the same thing (using the next empty cell) to get "STDEV". This gives you the standard deviation.

T tests - T tests can be used to compare two groups or treatments.

• Click on any empty cell.

• Click on formulas

• Click on insert function

• Click on “TTEST”

• If it is not on the list, search for it and highlight it before moving on.

• A dialog box will appear. Click in the box next to "Array 1".

• Drag the dialog box out of the way, then highlight your first column of numbers.

• Click in the box next to "Array 2" and highlight your second column of numbers.

To answer the "tails" question, remember your prediction about the direction of the difference between the groups. If you predicted group A would be lower than group B, pick 1 tail. If you predicted group B would lower than group A, pick one tail. If you didn’t predict which would be higher, use 2 tails. You can’t change your mind after the data are gathered.

There are three types of T test you can use on Excel. Let’s say you wanted to test whether heart rate increased after drinking a cup of hot sauce (don’t actually try this!) or whether plant growth would increase after adding fertilizer to pots of soil. In these cases you would be comparing the heart rate of the same people, or the growth of the same pot of plants before and after the treatment. This would require a "paired" or "dependent" T test. Excel calls this a "type 1" test.

Let’s look at another situation. Say you want to know whether nursing students consume more coffee than do biology students. You would then have two groups of test subjects rather than taking 2 measurements on each person. Now you would use an "unpaired" or "independent" T-test. Excel calls these "type 2" or "type 3" tests. Now the tricky part is to decide which of these to use. Are the standard deviations about the same for both groups, or are they different? You can test this statistically, but let’s just work with how they seem. If in doubt, go with "type 3" for unequal variances.

Now hit "OK" and see what the number is. This is your P-value.

A P-value below 0.05 is generally considered statistically significant (your null hypothesis is rejected) -your two groups are different!

A P-value above 0.05 is generally considered not statistically significant (your null hypothesis fails to reject) – your two groups are too similar to claim they are different!

If your number looks like this: 2.03188E-7, Excel is giving you the number in its version of scientific notation. This number is actually 2.03 X 10 -7, or 0.000000203.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download