VIEWS: 14 PAGES: 25 POSTED ON: 10/14/2012 Public Domain
CHI SQUARE TEST click on tabs on the bottom to switch to different (if you don't see the tabs, make sure the window for this worksheet is maximized) Steps to using Excel to do chi-square test: First decide how many categories you have (e.g., north vs south would be 2 categories; tree, shrubs, succulents would be 3). Use the table that has the same number of categories as you have (change categories to your's if you want). Type your data in the gray boxes; make sure you press enter after entering all data A p-value less than 0.05 means that the chance of the numbers being evenly randomly distributed is less than 5%. Note: you cannot use chi square test for comparing percentages. Note: you cannot use chi square test for sample sizes less than 30 2 categories Category 1 Category 2 Your Data >>> 120 150 P value 0.068 [Note: if this number = 0.000, then record p Is the p value less than 0.05? If no, then the totals are not significantly dif If yes, then the totals are significantly differ 3 categories Category 1 Category 2 Category 3 Your Data >>> P value #DIV/0! [Note: if this number = 0.000, Is the p value less than 0.05 If no, then the totals are not s If yes, then the totals are sign 4 categories Category 1 Category 2 Category 3 Category 4 Your Data >>> 40 50 60 104 P value 0.000 5 categories Category 1 Category 2 Category 3 Category 4 Category 5 Your Data >>> P value 8 categories Category 1 Category 2 Category 3 Category 4 Category 5 Category 6 Category 7 Your Data >>> Example 1: if you counted 120 saguaros on the N slope and 150 saguaros on the S slope, then p = 0.068 so there is no significant difference in # of saguaros on the two slopes. Example 2: if you counted 40 holes on the N side, 50 on E side, 60 on W side, and 104 on S side of saguaros, then the p < 0.01 so there is a significant difference in # of saguaros on the two slopes. The chi square test compares the observed values to the values expected by the null hypothesis. to different tests bs, succulents would be 3). is less than 5%. umber = 0.000, then record p<0.01] e less than 0.05? totals are not significantly different (accept null hypothesis) e totals are significantly different (accept alternative hypothesis) [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the totals are not significantly different (accept null hypothesis) If yes, then the totals are significantly different [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the totals are not significantly different (accept null hypothesis) If yes, then the totals are significantly different #DIV/0! [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the totals are not significantly different (accept null hypothesis) If yes, then the totals are significantly different Category 8 P value #DIV/0! [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the totals are not significantly different (accept null hypothesis) If yes, then the totals are significantly different e of saguaros, ept null hypothesis) T-TEST Steps to using Excel to do t-test: Click on Group 1 and type in new name for group; do same for Group 2 Replace existing data with your data under each column name Click into the vertical-axis label of the graph, and change the label, remembering to give the units, too Group 1 Group 2 Your Data >>> 3.5 1 average Group 1 1.760 Your Data >>> 0.2 3.2 average Group 2 2.540 Your Data >>> 0.5 3.5 Etc. 1.5 4.2 standard error Group 1 0.668 These values show the variabi 3.1 0.8 standard error Group 2 0.690 P value 0.440 [Note: if this number = 0.000, t Is the p value less than 0.05? If no, then the averages are no If yes, then the averages are s what are you measuring (units) 0.5 1.0 1.5 2.0 2.5 3.0 3.5 0.5 0.0 Group 1 These values show the variability for each group, and are used to make the "error bars" on the graph below [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the averages are not significantly different (accept null hypothesis) If yes, then the averages are significantly different Group 1 Group 2 T-TEST: PAIRED Steps to using Excel to do t-test: Click on Group 1 and type in new name for group; do same for Group 2 Replace existing data with your data under each column name Click into the vertical-axis label of the graph, and change the label, remembering to give the units, too Group 1 Group 2 Your Data >>> 3.5 1 average Group 1 1.760 Your Data >>> 0.2 3.2 average Group 2 2.540 Your Data >>> 0.5 3.5 Etc. 1.5 4.2 standard error Group 1 0.668 These values show the variabi 3.1 0.8 standard error Group 2 0.690 P value 0.581 [Note: if this number = 0.000, t Is the p value less than 0.05? If no, then the averages are no If yes, then the averages are s measuring (units) 3.5 what are you 3.0 2.5 2.0 1.5 1.0 0.5 0.0 Group 1 These values show the variability for each group, and are used to make the "error bars" on the graph below [Note: if this number = 0.000, then record p<0.01] Is the p value less than 0.05? If no, then the averages are not significantly different (accept null hypothesis) If yes, then the averages are significantly different Group 1 Group 2 REGRESSION ANALYSIS SAMPLE Dexterity Productivity Dexterity as a Predictor of Productivit Employee Test Score Units/Hour A 12 55 B 14 63 C 17 67 Productivity (units/hr) 75 D 16 70 70 E 11 51 65 SUMMARY OUTPUT 60 55 Regression Statistics Multiple R 0.955 50 R Square 0.911 91% of variation in y explained by x 10 12 14 Adjusted R Square 0.882 Dexterity Test Score Standard Error 2.757 standard error is 2.757 units/h Observations 5 ANOVA df SS MS F Significance F Regression 1 234 234 30.789 0.012 is p value Residual 3 22.8 7.6 df = n-2 = 3 Total 4 256.8 CoefficientsStandard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 19.2 7.669 2.504 0.087 -5.206 43.606 -5.206 X Variable 1 3 0.541 5.549 0.012 1.279 4.721 1.279 Regression Line is y = 19.2 + 3.0x t = 5.55 p = 0.012 df = 3 RESIDUAL OUTPUT Observation Predicted Y Residuals 1 55.2 -0.2 2 61.2 1.8 3 70.2 -3.2 4 67.2 2.8 5 52.2 -1.2 as a Predictor of Productivity 16 18 Dexterity Test Score Upper 95.0% 43.606 4.721 SIMPSON'S DIVERSITY INDEX Steps to using Excel to do Simpson's diversity index: Type in the number of individuals for each species in the gray boxes The number in yellow is the Simpson's Diversity Index (D). Diversity is a measure of the number of species (richness) and how evenly spread out the individuals are among the species (evenness) For the purposes of this class, let's say that the difference is significant if the Simpson's diversity indices differ by more than 10 percentage points between two areas. Area 1 Area 2 Simpson's Diversity Percent chance that two individuals pulled randomly from 64.8 58.1 Index [actually 1-D] the community will not be from the same species. The higher the number, the more diverse the community. N= 14 73 This is calculated for you and is total number of individuals sampled. Richness = 4 5 This is calculated for you and is total number of species sampled. number of species 1 1 8 You type in these number of species 2 2 12 numbers in the gray number of species 3 3 45 boxes which are number of species 4 2 the number of number of species 5 8 individuals of number of species 6 6 each species (no zeroes) number of species 7 number of species 8 number of species 9 number of species 10 number of species 11 number of species 12 number of species 13 number of species 14 number of species 15 number of species 16 number of species 17 number of species 18 number of species 19 number of species 20 number of species 21 number of species 22 number of species 23 number of species 24 number of species 25 number of species 26 number of species 27 number of species 28 number of species 29 number of species 30 number of species 31 number of species 32 number of species 33 number of species 34 number of species 35 number of species 36 number of species 37 number of species 38 number of species 39 number of species 40 d randomly from he community. of individuals sampled. of species sampled. a3a6fff1-9fff-4226-9c95-9001906edf4c.xls CHI SQUARE TEST First decide how many categories you have (e.g., north vs south would be 2 categories). Use the table that has the same number of categories as you have (change categories to yours). Enter your data in the white boxes under "Obs"; these are your observed data. Leave the expected values ("Exp") as they are unless you expect something other than being evenly divided. Compare total (chi square statistic = X2) that is in the yellow box to the red number above the table (derived from statistics table IF TOTAL IS GREATER THAN 3.84 (df=1) IF TOTAL IS GREATER THAN 5.99 (df=2) THEN THERE IS SIGNIFICANT DIFFERENCE THEN THERE IS SIGNIFICANT DIFFERENCE Categories Obs Exp (O-E)2 (O-E)2/E Categories Obs Exp (O-E)2 (O-E)2/E A 120 135 225 1.67 trees 0 0 0 #DIV/0! B 150 135 225 1.67 shrubs 0 0 0 #DIV/0! total 270 270 TOTAL 3.33 cacti 0 0 0 #DIV/0! total 0 0 TOTAL #DIV/0! X 2= 3.33 Significant if greater than 3.84 X2= #DIV/0! Significant if greater than 5.99 p value = 0.068 Significant if less than 0.05 p value = #DIV/0! Significant if less than 0.05 Terms: df= degrees of freedom = one less than the number of categories; use df to look up test statistic in statistics p value = the probability that you conclude that there is a significant difference when there really is no difference. Page 22 a3a6fff1-9fff-4226-9c95-9001906edf4c.xls (derived from statistics table). IF TOTAL IS GREATER THAN 7.81 (df=3) IF TOTAL IS GREATER THAN 9.49 (df=4) THEN THERE IS SIGNIFICANT DIFFERENCE THEN THERE IS SIGNIFICANT DIFFERENCE Categories Obs Exp (O-E)2 (O-E)2/E Categories Obs Exp (O-E)2 (O-E)2/E north 40 63.5 552.25 8.70 trees 0 0 0 #DIV/0! south 50 63.5 182.25 2.87 shrubs 0 0 0 #DIV/0! east 60 63.5 12.25 0.19 cactuses 0 0 0 #DIV/0! west 104 63.5 1640.25 25.83 forbs 0 0 0 #DIV/0! total 254 254 TOTAL 37.59 grass 0 0 0 #DIV/0! total 0 0 TOTAL #DIV/0! X 2= 37.59 Significant if greater than 7.81 X2= #DIV/0! Significant if greater than 9.49 p value = 0.000 Significant if less than 0.05 p value = #DIV/0! Significant if less than 0.05 k up test statistic in statistics table here really is no difference. Page 23 a3a6fff1-9fff-4226-9c95-9001906edf4c.xls IF TOTAL IS GREATER THAN 14.07 (df=7) THEN THERE IS SIGNIFICANT DIFFERENCE Categories Obs Exp (O-E)2 (O-E)2/E N 0 0 0 #DIV/0! NE 0 0 0 #DIV/0! E 0 0 0 #DIV/0! SE 0 0 0 #DIV/0! S 0 0 0 #DIV/0! SW 0 0 0 #DIV/0! W 0 0 0 #DIV/0! NW 0 0 0 #DIV/0! total 0 0 TOTAL #DIV/0! X2= #DIV/0! Significant if greater than 14.067 p value = #DIV/0! Significant if less than 0.05 Page 24 SHANNON'S DIVERSITY INDEX Steps to using Excel to do Shannon's diversity index: Type in the number of individuals for each species in the gray boxes The number in yellow is the Shannon's Diversity Index (H). Diversity is a measure of the number of species (richness) and how evenly spread out the individuals are among the species (evenness) For the purposes of this class, let's say that the difference is significant if the Simpson's diversity indices differ by more than 10 percentage points between two habitats. Area 1 Area 2 Shannon's Div. Index 66.67 n= 3 3 Richness = 2 2 Variance ni*(ni-1) This is the formula number of species 1 1 1 0 These are number of species 2 2 2 2 calculated for you number of species 3 0 so don't change number of species 4 0 them. number of species 5 0 number of species 6 0 number of species 7 0 number of species 8 0 number of species 9 0 number of species 10 0 number of species 11 0 number of species 12 0 number of species 13 0 number of species 14 0 number of species 15 0 number of species 16 0 number of species 17 0 number of species 18 0 number of species 19 0 number of species 20 0 number of species 21 0 number of species 22 0 number of species 23 0 number of species 24 0 number of species 25 0 log n sum of fi log fi