PC Labs for SO5041: Week 7

The following table is available here as a spreadsheet. Fill in the missing row, column and grand totals, using the =sum() function, marking the relevant cells with the mouse. For example, type in the first row, go to where you want the row total to be, enter =sum(, mark the row cells with the mouse, enter ) and press return.

Do not type in the numbers in bold! Use formulas instead.

Another tip: rather than entering the same formula repeatedly, you can copy it – for instance, if you copy the row 1 total formula down one, it now totals row 2. Formulas by default use relative references to other cells. For instance, a formula in B1 referring to A1 is really referring to "one cell to the left", so if we copy it to C23 the new formula refers to B23.

Copy the entire table to a nearby location and delete the numbers in the body. Calculate the row proportion (percentage) in each cell by using a formula that divides the corresponding cell in the original table by the row total. If you want to copy this formula from the "male" column to the "female" one, you need to "de-relativise" it a bit because the position of the row total does not move. To do this just put a $ in the reference to the row total: for instance, =C4/$E4. If you copy this one cell right it will become =D4/$E4.

To get percentages, you can either multiply by 100, or set the format (Format -> Cells -> Percentage).

If there is no association between two variables, we would expect the row percentages in each row to follow more or less the same pattern as the percentages across the column totals (also true for column percentages). We can calculate the .expected values/ therefore, from the column and row totals. One way is for each cell to multiply the column total percentage by the row total number. Another way is to multiply the row total by the column total and divide by the grand total.

Copy the table again (for simplicity, perhaps key in the totals). For each cell replace the observed value with a formula for the expected value. Can you see big differences between the observed and expected values?

Calculate percentages based on the expected values, and verify that they are the same column by column, row by row.

A standard test for association in tables is called the chi-squared test. This involves calculating for each cell the quantity (O-E)²/E where O means observed value and E means expected value. When added up for the entire table this quantity is called the chi-squared statistic: the larger the individual differences between the observed and expected values the larger this will be. This statistic has a known distribution. For instance, for a table this size, there is only a 5% chance that sampling variability would result in a chi-squared statistic of 9.488 or greater, when there was really no association in the population. Thus if the chi-squared statistic is less than 9.488 we consider the table as being consistent with there being no association in the population, but if it is greater than 9.488 we consider it evidence that there probably is association in the population.

Calculate the chi-squared statistic and assess whether there seems to be association or not.