Relative frequency

Description of activity and Excel instructions: Relative frequency

Dataset: developed from scratch

Create 10 samples

Let's set up a dataset to represent 10 parties with 30 attendees at each party.

• Open Calc with a new worksheet.
• Enter the following labels into the first 10 columns: Party 1, Party 2, etc.
• In rows 2-30 under each labeled column, enter the formula =RANDBETWEEN(1,365).

Convert the formula results to numbers:

• Type the key combination Cntl+A to select all of the data in the worksheet.
• Right-click and choose Copy.
• Right-click and choose Paste Special....

The Paste Special dialog displays

• Check the Text and Numbers boxes only
• Select OK.

The columns list the birthdays of the 30 people attending each of the 10 parties (samples). Now we can determine how many of the samples of size 30 have at least 1 pair of people with the same birthday.

Determine the number of matches in each sample

We use the datapilot function to summarize the data, creating a one-way table for each party.

• Select the cells containing the party data, including the heading.
• Select Data > DataPilot > Start....

The Select Source dialog displays.

• Click Current selection.
• Click OK.

The DataPilot dialog displays.

• Drag and drop the Party 1 variable into the Row Fields window.
• Drag and drop the Party 1 variable into the Data Fields window.

We need to change the function from Sum to Count.

• Double click the Party 1 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 click the shrink button and select a cell under the birthday data, and then return to the dialog box.
• Click OK to exit the DataPilot dialog.

You should now have a table with two columns: the first column of the table lists the 30 birthdays from Party 1 and the second column with an empty heading contains the number of times that the birthday in the first column appeared in the sample of size 30. If more than 1 person at this party has the same birthday, we call this a match.

• Bold or highlight any cells with a count of 2 or greater.

Repeat this process for Party 2 through Party 10.

Return to the OLI's Relative frequency page to complete the interpretive exercises.