StatTutor: Cellphone ownership (Question 2)

From WikiEducator
Jump to: navigation, search

Description of activity and Excel instructions: StatTutor Cellphone ownership (Question 2)

Dataset: Direct link for cell_phones.xls not available.

Exploratory Analyses

This exercise begins by taking a look at the data.

Calculate percentage in each category

  • Highlight the block of columns containing the data for the relevant variable.
  • Click Data > DataPilot > Start....

The Select Source dialog displays.

  • Click Current selection.
  • Click OK.

The DataPilot dialog displays.

  • Drag and drop the relevant variable into the Row Fields window and into the Data Fields window.
  • Revise the Data Fields variable so it will display Counts.
  • Expand the dialog box, using the More option in the lower right, and specify where to put the two-way table.
  • Click OK to exit the DataPilot dialog.

You should now have a table that shows the counts for each value in the variable, along with a total.

Convert the 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.

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.


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

Return to the StatTutor exercise for Question 2 to report results.

Test hypothesis

  • Calculate the test statistic using the formula:
[math]z = \frac{\hat{p} - p}{\sqrt{\frac{p(1-p)}{n}}}[/math]

where p = p0, the population proportion.

  • Calculate the p-value using the Calc function NORMSDIST
  • Adjust the formula so that the resulting value is the area in the extreme of one or both tails, depending on the Ha statement specified.

Return to the StatTutor exercise for Question 2 to report results and draw conclusions.