GRAPHING YOUR DATA IN EXCEL: PINTO SEED LAB EXAMPLE



ANALYZING DESCRIPTIVE & INFERENTIAL STATISTICS IN EXCEL

Steps for creating a histogram:

1. Record your data in Excel:

• Columns--independent variable (ex: dry/wet seed measurements)

• Rows--dependent variable (ex: mass)

|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.0 |

2. Highlight one column of data and click on SORT AND FILTER in the upper right in the HOME 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 number ranges. Create a data table off to the side of your data like the example below. Depending on your data, you may have more ranges and they may need to be larger or smaller increments, but your goal is to adequately represent the data.

|Bin Size |0 - .2 |.3 - .5 |.6 - .8 |.9 - 1.1 |

|Dry Pinto Seeds | | | | |

|Wet Pinto Seeds | | | | |

6. Highlight your range data table including the words, then go to Insert, look for the chart choices in the tool bar and click on the BAR OR COLUMN charts. Insert the first “2-D Column” bar chart for this type of data.

[pic]

7. Once the graph is inserted, go under DESIGN. Click QUICK LAYOUT and insert the 8th choice that has all bars touching each other. You can easily change the titles of the graph by clicking on the words MAIN TITLE or AXIS TITLE and then typing in the appropriate heading.

[pic]

Select the entire chart by clicking on it and notice the green “+” sign that appears to the right upper corner of the graph. Click on it and select LENGEND to show the key. You can also use this to add other features to your graph as needed.

Steps for using Excel to calculate 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, click MORE FUNCTIONS then hover over STATISTICAL. Search for “AVERAGE”. When a dialogue box comes up, put your cursor in the NUMBER1 box then highlight all the numbers in the column. 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, click MORE FUNCTIONS then hover over STATISTICAL. Search for “MEDIAN”. When a dialogue box comes up, put your cursor in the NUMBER1 box then highlight all the numbers in the column. 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, click MORE FUNCTIONS then hover over STATISTICAL. Search for “MODE.SNGL”. When a dialogue box comes up, put your cursor in the NUMBER1 box then highlight all the numbers in the column. 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 and enter the value into the cell.

| |Mass (g) |

|Descriptive &Inferential 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 |

|SEM | | |

|± 2 SEM (95% confidence) | | |

5. Now that you have the idea, you will do the same to find STANDARD DEVIATION for each column of data by searching for “STDEVA”.

6. To find the STANDARD ERROR OF THE MEAN (SEM) click on the cell in which you would like to insert the SEM and enter the following equation:

=STDEVA/SQRT(n)

• To enter STDEVA you will click on the box that contains that calculation

• “n” refers to your sample size which is the number of data points you have

7. To find ± 2 SEM click on the cell in which you would like to insert the calculation and enter the following equation:

=SEM*2

• To enter SEM you will actually click on the box that contains that calculation

Now you are ready to create your graphs:

1. Highlight the MEAN calculations and the IV headings, then go to INSERT, look for the chart choices in the tool bar and click on the BAR OR COLUMN charts. Insert the first “2-D Column” bar chart for this type of data.

[pic]

2. The graph that is inserted should be fine, but you can select a different QUICK LAYOUT if it better represents your data. You can easily change the axis titles and main title of the graph as detailed before.

[pic]

3. To add ERROR BARS click on one of the bars of the graph. When the green “+” sign appears to the right upper corner of the graph, click on it. Find the choice that says ERROR BARS in the drop-down menu and hover over it—DO NOT just check the box—click on the black arrow pointing right from the drop-down menu, select “MORE OPTIONS…”.

4. Make sure the CHART icon is selected, under vertical error bar “DIRECTION” chose “BOTH.”

5. Then, scroll to the bottom of this menu and select “CUSTOM.” Click in the box that says “SPECIFY VALUE,” when a dialogue box pops up you will click on POSITIVE ERROR VALUE, clear the current contents then highlight all SEM values from your descriptive statistics chart. Do the exact same thing for the NEGATIVE ERROR VALUE.

[pic]

[pic]

[pic]

6. If you have done this correctly, the error bar will be the same length from each side of the top of the bars on the graph. If you have done this correctly, the error bars will change lengths if you change the SEM value. If you have done this correctly, changing the SEM value for one experimental group will only change the error bar length on the corresponding bar on the graph.

7. 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

T-tests

To analyze whether the data collected shows a statistically significant difference T-tests can be used to compare two experimental groups or treatments.

• Click on any empty cell.

• Click on FORMULAS

• Click MORE FUNCTIONS then hover over STATISTICAL.

• Search for “TTEST”

• A dialog box will appear. Click in the box labeled "ARRAY 1".

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

• Click in the box labeled "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 greater, use 2 tails. You should not change your mind after the data are gathered.

To answer the “TYPE” question, there are three types of T-test you can use on Excel:

• Type 1: Paired T-test.

o Use when you are comparing the same exact participants from one experimental group to another.

o If you use a group of 15 plants to test the effects of using pure water for a month then compare that to the results of the same plants after using fertilizer for a month, you’d use a Type 1 T-test

• Type 2: Two-sample T-test with equal variance.

o Use when you are comparing two different sets of participants from one experimental group to another and the Variance calculations between the groups are within one or two tenths of one another.

o If you use one set of 15 plants to test the effects of using pure water for a month then compare that to the results of a different set of 15 plants after using fertilizer for a month, you’d use a Type 2 or 3 T-test depending on the variance calculations

• Type 3: Two-sample T-test unequal variance.

o Use when you are comparing two different sets of participants from one experimental group to another and the Variance calculations between the groups are greater than one or two tenths different from one another.

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 statistically different!

A p-Value above 0.05 is generally considered NOT statistically significant (your null hypothesis fails to be rejected) your two groups are too similar to claim they are statistically 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