Cross Tables (Statistics)

From Apache OpenOffice Wiki
Jump to: navigation, search

Apache OpenOffice Calc is perhaps not in league with specialised statistics’ programmes like SPSS or its very recommendable cost free and open source alternative PSPP. Nevertheless you can use it for statistical purposes, one of the most popular being Cross Tables to illustrate the (presumed) dependency of one variable upon another.

Example of survey using two categories and percentages

In a fictional survey we asked 12 people, men and women, to tell us on a scale from 1 to 10 how much they abhorred violent videos. The aim was to see if there is any significant variance between women and men. The raw results are listed in table 5 and the dependencies in cross table 6.

Questionnaire Sex Level of rejection in %
1 M 20
2 F 80
3 F 70
4 M 30
5 F 90
6 F 80
7 M 80
8 F 100
9 M 0
10 F 60
11 M 40
12 F 90

Table 5: Degree of aversion amongst men and women towards violent videos

Sex Level of rejection in %
F 81
M 34
Total 62

Table 6: Same data as above in form of cross table showing that women reject violent videos to a greater degree than men (or pretend to do so)

To obtain the summary as displayed in table 6 do the following:

Wfs025 pivot table.png

Illustration 30: After having dragged the two buttons “Sex” and “Level of …” into their appropriate fields, go to Options and choose Average

  1. Mark the two columns of interest, i.e. “sex” and “extent of rejection” in the raw data table 5, copy them into memory, go to the module Spreadsheet (menu File › New › Spreadsheet) and there reinsert them using shortcut Ctrl + v.
  2. In your spreadsheet again mark the two columns and go to menu Data › DataPilot › Start › Current selection.
  3. Drag and drop the button Sex onto the white area Row Fields and Level of rejection… onto the white area Data Fields; press Options and choose Average; confirm OK and again OK (see illustration 30).
  4. Your new cross table now appears on your spreadsheet.
  5. Mark it and go to menu Format › Cells › Numbers › -1234 with Decimal place value 0 and Leading zero value 1; confirm with OK (see illustration 31).
  6. Copy your newly formatted cross table into computer memory (Ctrl + c).
  7. Back to your text file insert the contents (as opposed to a direct image) of the cross table using menu Edit › Paste Special › Formatted text [RTF] (Shift + Ctrl + v and not simply Ctrl + v)
  8. You can now freely format your newly inserted table.
  9. Finally right click it and add a Caption.
Wfs026 number decimal places.png

Illustration 31: The number of decimal places is reduced to 0

Example of survey involving yes/no answers

In a fictitious yes/no survey we wanted to find out if handedness is related to sex. So we put the question “do you primarily use your right or your left hand for writing?” to a mixed group of people. The raw results are listed in table 7, the summary is to be found in table 8.

To obtain grouped results as in table 8 do the following:

  1. Copy the two right columns Sex and Handedness from table 7 into memory and insert them into the module Spreadsheet.
  2. In your spreadsheet again mark these same two columns.
  3. Open menu Data › Pivot Table › Create › Current Selection.
  4. Drag & Drop the button Sex onto the white area Row Fields and the button Handedness both onto the white area Column Fields and onto the white area Data Fields (see illustration 32).
  5. Under Options choose Count instead of Sum and press More and there choose as Type the option % of row; confirm OK.
  6. A new cross table will appear on your spreadsheet.
  7. Mark the cross table thus created and choose menu Format › Cells › Category › Percent › 13 %; numbers will appear with percent sign and zero decimal places.
  8. Copy the first three columns of your cross table into computer memory (Çtrl+c).
  9. Back in your text file insert the contents of computer memory using the keyboard shortcut Shift + Ctrl + v and choose the option Formatted text [RTF].
  10. Format your newly inserted cross table to your taste.
  11. Finally right click the table and add a Caption.
Questionnaire Sex Handedness
1 M R
2 F R
3 F L
4 M R
5 F R
6 F R
7 M L
8 F R
9 M L
10 F R
11 M L
12 F R

Table 7: Right- and Left-Handedness and Sex (raw data)

Sex Left handedness Right handedness
Women 14% 86$
Men 60% 40%
Total Result 33% 67%

Table 8: Cross table comparison as to handedness dependent on sex

Wfs may2014 032-pull fields onto.png

Illustration 32: Pull the field "sex" onto area Row Fields and the field "handedness" both onto areas Column Fields as well as Data Fields; under Options choose Function "Count" and pressing button More choose “% of row”

< Previous Page

Next Page >

Personal tools