Statistics I: Calculation Process
The Excel template for this unit is called Stats1
You will have no problem finding data which can be analyzed using descriptive and inductive statistics. Remember to pay attention to the way each variable is measured: for anything other than Chi-square, you will need data that are measured on an interval scale.
You will also find it fairly easy to calculate the various statistics. The template includes a very large section, labeled Data Input Sheet. This table calculates many values, which will also be used later for correlation and ANOVA. The first two columns provide space for data entry (indicated by the row of dots). You may give names to “Variable 1” and “Variable 2” in cells B28 and C28 if you wish, and the name will be propagated to other cells in the template as appropriate. There are 50 rows for data entry; you may add or subtract as needed (but be careful not to erase other formulas, or to add rows outside the range that the formulas contain). The headings for the input sheet look like this (don’t be concerned about those strange notations in some of the cells—they will be replaced with calculated values once you enter some data):
|
Data Input
Sheet |
|
|
|
|
|
|
|
|
Observed
- Average |
Deviation
Squared |
||
|
Variable
1 |
Variable
2 |
Variable
1 |
Variable
2 |
Variable
1 |
Variable
2 |
|
……… |
………. |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
|
……… |
………. |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
Descriptive statistics are computed automatically from the Data Input Sheet—mean, standard deviation, and number of observations in your sample. If you specify a value which you suspect is the population mean, the template will also calculate the t-statistic. If you want to compare Variable 1 with Variable 2, enter the mean for Variable 2 as the population mean for Variable 1 and the template will calculate the t-statistic. The Summary Statistics table looks like this:
|
Summary
Statistics |
|
|
|
|
Variable
1 |
Variable
2 |
|
n of
Cases |
0 |
0 |
|
Average |
#DIV/0! |
#DIV/0! |
|
Standard
Deviation |
#VALUE! |
#VALUE! |
|
|
|
|
|
t-Test
Population Mean |
…….. |
……….. |
|
t-statistic |
#DIV/0! |
#DIV/0! |
To the right of the Summary Statistics table on the template, there is a table for calculating Chi-square. The chi-square matrix is set for a 3´3 contingency table. You may expand it (remember to copy the formulas as needed) or contract it (you must delete the unused rows or columns, otherwise the template will treat them as cells with “0” as the observed value). You must enter the number of observations for each cell of the matrix, and specify the number of rows and columns. The template will calculate the row and column totals and the grand total. The second part of the table requires no input from you. This section transfers the observed frequencies you specified in the matrix, calculates the expected values, the difference between the observed and expected values, ssquares hat value, and provides the grand total (which is the Chi-square statistic). It will also calculate the degrees of freedom.
|
|
Chi-Square
Analysis |
|
|
|
|
|
|
|
|
|
|
# Rows |
…….. |
|
|
|
|
# Columns |
…… |
|
|
|
|
|
|
|
|
Row
Totals |
|
|
………… |
……… |
…….. |
0 |
|
|
………… |
……… |
…….. |
0 |
|
|
………… |
……… |
…….. |
0 |
|
Column
totals |
0 |
0 |
0 |
0 |
|
|
|
|
|
|
|
|
Chi-Square
Table |
|
|
|
|
Observed |
Expected |
Difference |
Squared |
|
|
………… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
……… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
…….. |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
………… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
……… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
…….. |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
………… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
……… |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
…….. |
#DIV/0! |
#VALUE! |
#VALUE! |
|
|
|
|
Chi-Square = |
#VALUE! |
|
|
|
|
df = |
#VALUE! |
|
© 1996 A.J.Filipovitch
Revised 11 March 2005