﻿ Statistics

# Statistics Lab

## Objectives

• Practice summarizing ungrouped data using a frequency distribution.
• Practice summarizing data using a column chart.
• Learn how to summarize data using descriptive statistics.

## Rolling a Pair of Dice

A single trial in an experiment consists of rolling a pair of dice and recording the sum of the dots (the dot sum). The experiment consists of 200 trials.

1. Open the workbook DescriptiveStats01.xlsx and put your name in cell B1 of the "Pair of Dice" worksheet.

2. The results of the 200 rolls are listed under the heading "Dot Sum" in column A. In the frequency distribution section, the possible dot sums for a single trial are listed.

### Use a Frequency Distribution to Summarize the Data

3. In cell G6, use the COUNTIF function to determine the number of times the dot sum was 2 in this experiment. Be careful to use absolute references where needed.

4. I suggest that you copy this cell and use the paste special to paste just the formula in the rest of cells in the frequency column. You can also use the fill handle to copy the COUNTIF formula down the rest of the column but you'll have to reformat the last cell to display a bottom border.

5. Use the AutoSum tool button to find the sum of the frequencies. This number is n, the sample size. If you didn't get n = 200, fix your mistake(s) before continuing.

6. Insert a column chart illustrating the distribution of dot sums. Does the distribution appear to be approximately normal?

### Use Descriptive Statistics to Summarize the Data

8. The mean can be found using the Excel function, AVERAGE(range), where range refers to the cells  used to store the raw data. Display the result to one decimal place.

9. The standard deviation for a set of sample data can be found using the Excel function, STDEV.S(range). Display the result to one decimal place.

10. All of the values in the five-number summary are calculated using the Excel function, QUARTILE.INC(range, quartile), where the quartile argument specifies which quartile as follows:

0 - Minimum
1 - First Quartile
2 - Second Quartile (the Median)
3 - Third Quartile
4 - Maximum

Display the quartiles to one decimal place.

## Flipping 10 Coins

A single trial in an experiment consists of flipping 10 coins and recording the number of heads. The experiment consists of 1,000 trials.

1. Go to the "Ten Coins" worksheet and put your name in cell B1.

2. The results of the 1,000 trials are listed under the heading "Heads" in column A. In the frequency distribution section, the possible numbers of heads for a single trial are listed.

### Use a Frequency Distribution to Summarize the Data

3. Use the COUNTIF function to determine the number of times the number of heads was zero in this experiment. Be careful to use absolute references where needed.

4. I suggest that you copy hour COUNTIF formula and use the paste special to paste just the formula in the rest of cells in the frequency column. You can also use the fill handle to copy the COUNTIF formula down the rest of the column but you'll have to reformat the last cell to display a bottom border.

5. Use the AutoSum tool button to find the sum of the frequencies. This number is n, the sample size. If you didn't get n = 1,000, fix your mistake(s) before continuing.

6. Insert a column chart illustrating the distribution of the numbers of heads. Does the distribution appear to be approximately normal?

### Use Descriptive Statistics to Summarize the Data

8. Find the mean number of heads and display the result to one decimal place.

9. Find the standard deviation and display the result to one decimal place.

10. Find the five-number summary and display the results to one decimal place.

## Student Absences

A university registrar took a random sample of 500 students and determined how many absences each student had in a required math class.

1. Go to the "Class Absences" worksheet and put your name in cell B1.

2. The number of absences for these 500 students are listed under the heading "Absences" in column A.

### Use Descriptive Statistics to Summarize the Data

3. Find the mean number of absences and display the result to one decimal place.

4. Find the standard deviation and display the result to one decimal place.

5. Find the five-number summary and display the results to one decimal place.

### Use a Frequency Distribution to Summarize the Data

7. The minimum number of absences is obviously zero. Use your five-number summary to determine the maximum number of absences in this data set. In the frequency distribution section, list the possible number of absences from 0 up to the maximum for this data set.

8. Use the COUNTIF function to determine the number of times the number of absences was zero. Be careful to use absolute references where needed.

9. Use the fill handle to copy the COUNTIF formula down the rest of the column.

10. Using your other two worksheets as a models, format the bottom row in the frequency distribution to display a bottom border. Put the text "n =" in the left column below the bottom border. Use the AutoSum tool button to find the sum of the frequencies. This number is n, the sample size. If you didn't get n = 500, fix your mistake(s) before continuing.

11. Insert a column chart illustrating the distribution of the number of absences. Does the distribution appear to be approximately normal?

12. Since the distribution doesn't look very normal, the mean and the standard deviation may not be appropriate descriptive statistics for this data set.