StatTutor: College integrity (Question 2)

Description of activity and Excel instructions: StatTutor College integrity (Question 2)

Dataset: Direct link for drinking.xls not available.

Create a table of counts & percents

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

• Highlight the column(s) of data you want to use.
• Click Data > DataPilot > Start....

The Select Source dialog displays.

• 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 for each of the variables selected.

• Drag and drop the desired variable(s) into the Column Fields window.
• Drag and drop the desired varibale(s) into the Data Fields window.
• Double click the 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 (including empty for missing data) along with a 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 variable 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.
• Click OK.
• Click OK.

Unfortunately Calc displays the proportion not the percent, but we can fix that.

• 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.

OR

• Go into the DataPilot dialog and change the Displayed value for Count to Normal.

Also check to see if the table displays helpful category labels. If not, you can enter labels adjacent to the data cells, to use in labeling the pie slices in the chart.

To 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:
  • In Data range, use the shrink button to highlight the counts in the table.
  • Adjust the options to fit the data you have highlighted.
  • Click Next>>.
• In 3. Data Series:
  • Use the shrink button next to the Categories field to highlight the category labels.
  • Click Next>>.
• In 4. Chart Elements:
  • Enter a title in the "'Title window.
  • Uncheck Display legend. (We'll show you another way to label each slice.)
• Click Finish.

To see percentages as well as absolute values AND the category labels:

• 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.
• Check Show category.
• In the Separator drop down menu, choose Comma.
• Click OK.

Your pie chart is complete.

Report results

The next step in the StatTutor exercise for Question 2 is to Report Results. The exercise asks you to

Describe the key features of data display and support your description with numerical measures.

Enter your description in BOTH the box provided in the StatTutor exercise AND next to your data analysis for Question 1 in YOUR spreadsheet. Tip: merge a block of cells to create a space on the sheet for your results write-up.

Draw conclusions

Following the Report Results page is the Draw Conclusions section. The Consider what the results mean section asks

What do the results you got indicate about the likelihood of the statistics professor knowing about the cheating?

Enter your response in BOTH the box provided in the StatTutor exercise AND below your description of results for Question 2 in YOUR spreadsheet.