One categorical variable

Description of activity and Excel instructions:One categorical variable

Dataset: friends.xls

Create a table of counts

Use Datapilot in Calc to create a table of counts and percents:

• Select A1 in Sheet1, the column labeled Friends.
• Click Data > DataPilot > Start....

The Select Source dialog displays and Calc automatically highlights column A.

• Click Current selection.
• Click OK.

The DataPilot dialog displays. Now we have to tell Calc how to construct the table:

To the right of the various fields windows you should see a button labeled Friends. This is the Friends variable.

• Drag and drop the Friends variable into the Column Fields window.

The various categories in the Friends variable will become the column labels in the resulting table.

• Drag and drop the Friends variable into the Data Fields window.
• Double click the Friends variable in the Data Fields window or select the Options button on the right side.

The Data Field dialog displays.

• Select Count in the Function list.
• Click OK.

You now need to specify where to put the table.

• Click the More button in the bottom right corner of the DataPilot dialog.
• In Results to choose - new sheet - from the drop down menu.

• Click OK to exit the DataPilot dialog.

You should now have a table in a new sheet that shows the number of each type of data entry: "No difference", "Opposite sex", "Same sex", and a grand total. This is the count of each of these entries.

Let's change the entries from counts to percent of total.

• Right click anywhere in the table.
• Select Start....

The DataPilot dialog displays.

• Select the Count - Friends item in the Data Fields window.
• Click Options... on the right.

The Data Field dialog displays.

• Click More in the bottom right.
• In the Type dropdown menu, select % of total. (Note that in this case, % of row provides the same result.)
• Click OK.
• Click OK.

Note that the values displayed are not percents at all but proportions (sadly, Calc has led us astray!). But no matter. We can fix it.

• Highlight the cells that you'd like to display as percents.
• Select Format > Cells....
• Select Percent under Category.
• Under Options, decrease the Decimal places to 1.
• Click OK.

Create a pie chart

First, we want to create a pie chart using the counts data:

• Click the Undo button until you've returned the table to the count data.

Now let's create the chart.

• Select Insert > Chart....

The Chart Wizard displays.

• In 1. Chart Type:
  • select Pie.
  • Select the Normal version.
  • Click Next>>.
• In 2. Data Range:
  • Click the Shrink button, on the righthand side of the Data range window.
  • Highlight the category labels and the counts in the DataPilot table. Do not include the Total result. That would be double counting the data.
  • Click the Maximize button, to return to the Chart Wizard dialog
  • Click Data series in rows.
  • Check First row as label.
  • Uncheck First column as label.
  • Click Next>>.

No changes are needed in 3. Data Series, but check that the Categories window correctly points to the category labels in the table.

• Click Next>>.
• In 4. Chart Elements:
  • Enter a title in the "'Title window.
  • Note that the Display legend box is checked.
• Click Finish.

To see percentages as well as absolute values,

• Double-click on the chart so that a gray border surrounds the area.
• Right-click on one of the pie pieces.
• Select Object Properties....

The Data Series dialog displays.

• Select the Data Labels tab.
• Check Show value as number.
• Check Show value as percentage.
• In the Separator drop down menu, choose Comma.
• Click OK.

Your pie chart is complete. You can move it under the data table for easy viewing.

Return to the OLI One Categorical Variable page to complete the interpretive exercises.