Statistics with Excel



Statistical Tests in Excel

This handout is by no means comprehensive, but does include some tests you might use in analyzing your nature experience data in Excel.

In statistical tests, a test statistic is calculated and compared to the critical value of that statistic for the degrees of freedom and desired significance level. The probability of obtaining a particular value of the test statistic by chance alone is calculated. If the test statistic is higher than the critical value, the probability of getting that result by chance is very small (a 5% or less chance), and we can reject the null hypothesis. If the test statistic is less than the critical value, the probability of calculating that value will be higher than 5% and the null hypothesis can not be rejected.

Parametric tests: t test and ANOVA

The following tests are parametric. This means that they require several assumptions to be made. One assumption is that the data are normally distributed. Another is that the variances are equal (unless otherwise indicated). Parametric methods work best with continuous variables. Discontinuous variables, such as counts, or derived variables may be used if they meet the assumptions. Sometimes transforming the data by taking the log or square root may make the data meet the normality assumption.

T test: testing for differences between two groups

T test: paired two-sample for means, Use when natural pairing of observations in the sample, such as when a group is sampled before and after an experiment. Assumes the variances of both populations are equal (although the help section of Excel states the opposite).

T test: Two sample assuming equal variances.

This test is for two independent groups of data. It assumes that both groups have equal variances.

T test: Two samples assuming unequal variances.

This test is for two independent groups of data. It assumes that the two groups have unequal variances.

Example of a t test:

After choosing a t test, a window appears:

Variable 1 range: choose cells containing data for first group

Variable 2 range: choose cells containing data for second group

Hypothesized mean difference: default is 0, but it can be set to any value.

Labels box: check if you included the cells containing the data labels. If not checked, and the labels were included in the ranges, an error message of "Non-numeric data included" will appear.

Alpha: default is 0.05 (5%). This is the significance level.

The output options are three buttons (choose one):

Output range: must specify the cell where you want the output to begin (it will take up three columns and up to thirteen rows).

New worksheet ply: may type in name you want for output sheet

New workbook: if you choose this option, the output will be placed in a separate file.

Output from the t test

The output from the t test is shown below. The most important information is the t statistic and the p value (probability value). The t stat (t statistic) is the test statistic calculated for the t test. It is compared to the critical value of the t statistic. The p value is the probability of having as high a value of the t test statistic by chance alone. Values above 0.05 correspond to non-significant results and the null hypothesis can not be rejected. Values of 0.05 or less correspond to significant results, leading to the rejection of the null hypothesis of no significant differences between the two groups. When running the test, you must decide if your alternative hypothesis is that the means of the two groups differ, in which case you would use the p value and critical t value for the two-tailed test. If your alternative hypothesis is that the two groups differ in a specific way (the variable being measured is either higher or lower in value in one group than the other), you would use the one-tailed test.

Table 1. Results from the paired t test.

|t-Test: Paired Two Sample for Means | | |

| | | |

| |Variable 1 |Variable 2 |

|Mean |4 |3.5 |

|Variance |1.428571429 |2.28571429 |

|Observations |8 |8 |

|Pearson Correlation |0.632455532 | |

|Hypothesized Mean Difference |0 | |

|df |7 | |

|t Stat |1.183215957 | |

|P(T ................
................

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

Google Online Preview   Download