Case II - Two-way table

Description of activity and Excel instructions:Case II

Dataset: nightlight.xls

Create a table of counts and a table of percents

Although the Excel instructions suggest creating the tables with formulas, we will use Datapilot in Calc to create eacg table:

• Highlight the two columns of data labeled Light and Nearsightedness.
• Click Data > DataPilot > Start....

The Select Source dialog displays.

• Click Current selection.
• Click OK.

The DataPilot dialog displays.

• Drag and drop the Light variable into the Row Fields window.
• Drag and drop the Nearsightedness variable into the Column Fields window.
• Drag and drop the Light variable into the Data Fields window. (Actually it doesn't matter which of the variables is dropped in the Data Field.
• Double click the Light 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 two-way table in a new sheet that shows the cross tabulation for the two variables, including row and column totals.

Let's create a second table where the entries are percent of row, for example, of the children who slept with a lamp, the percentage who did not develop nearsightedness.

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

The DataPilot dialog displays.

• Select the Count - Light 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 row.
• Click OK.

Now let's specify a location for this new version.

• Click the More button in the bottom right corner.
• In the Results to row, use the shrink button to specify the top left corner of the table, say A10.
• Click OK.

A second version of the table is displayed, but, sadly, with the proportion in each cell.

• Change the proportions to percents using cell formatting.

Now you have the data to inform an interpretation of results.

Return to the OLI Case II page to complete the interpretive exercises.