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.
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?
7. Save your workbook.
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.
11. Save your workbook.
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.
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?
7. Save your workbook.
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.
11. Save your workbook.
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.
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.
6. Save your workbook.
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.
13. Save your workbook.
Go to the "Children" worksheet and enter your name in cell B1. The data in column B of this worksheet represents the number of children in the families of MAT 120 students. Summarize this data using a frequency distribution, a chart, and appropriate descriptive statistics.
Go to the "Ages" worksheet and enter your name in cell B1. The data in column A represents the ages of MAT 120 students. Summarize this data using appropriate descriptive statistics.
My solutions are in the workbook DescriptiveStats01Done.xlsx.