Distribution Means

The goal of this lab is to compute the means of the frequency distributions considered in the previous Lab "Representing data by distributions and charts".

PROBLEM 1. Recall that you made a frequency distribution for the following 30 scores on a 10-point quiz:

6, 8, 6, 10, 4, 8, 1, 3, 8, 6, 5, 1, 9, 8, 3,
6, 7, 6, 8, 5, 3, 6, 3, 8, 5, 3, 2, 9, 3, 6,

The data was placed in range A4..A33, the Bin was placed in range B4..B13, and the output of the Histogram Data Analysis placed a copy of the Bin in column D and the frequency in column E. You are to compute the mean in two ways.

a. First we compute the mean of the frequency distribution in columns D and E. In cell F3 enter the label "x*f". Then enter the formula =D4*E4 in cell F4 and copy to the range F5..F13. Go to cell F15 and enter the formula =sum(F4:F13) to sum up the products. In cell E17 enter the label "Mean =" and in cell F17 enter the formula =F15/sum(E4:E13).

b. The second calculation goes in cell F18 (just below the mean you computed in part a). Enter
=sum(A4:A33)/count(A4:A33) in this cell. In a textbox explain why the methods used to compute the values in F17 and F18 give the same result. Print the worksheet.

PROBLEM 2. Recall that the distribution of weekly sales (in $) for salesmen of XYZ company are as follows.

Amt Sales ($) #Salesmen
0-999 2
1000-1999 7
2000-2999 14
3000-3999 7

a. First estimate the total $ sales for XYZ. To do this place the label "Midpt" in cell C3 and enter the values 500, 1500, 2500, and 3500 in cell range C4..C7. (Explain why these values were chosen.) Place the label "f*m" in cell D3 and place the formula =C4*B4 in cell D4. Copy to the other cells, then use the values in the D column to estimate the total sales for the week. State your conclusion in sentence form.

b. Determine the average sales value (in $).

c. Explain why your answer in Part b is only an approximation to the average sales and why it is not possible in this problem to find the actual average.

PROBLEM 3. Recall the unemployment rates for the years 1970-1999 which you entered in range A4..A33 in the previous lab.

4.9, 5.9, 5.6, 4.9, 5.6, 8.5, 7.7, 7.1, 6.1, 5.8
7.1, 7.6, 9.7, 9.6, 7.5, 7.2, 7.0, 6.2, 5.5, 5.3
5.6, 6.8, 7.5, 6.9, 6.1, 5.6, 5.4, 4.9, 4.5, 4.2

Recall that the bin values 4.5, 5.0, . . . , 10.0 were placed in range B4..B15 (and also in D4..D15), the frequency count was placed in E4..E15 and the midpoint value of each interval was placed in the C-column.

a. Again, compute the mean unemployment rate in two ways. First, compute from the data. Place the label "Data xBar" in cell B19 and in cell C19 compute the mean of the data in cells A4..A33 by totaling the values and dividing by the number of data points. (See Problem 1.b.)

b. Secondly, compute the mean from the frequency distribution. Place the label "f-xBar" in cell B20. Compute the mean from the frequency distribution in the same way you did in Problem 1.a (Hint: first calculate f*m in column F using columns C and E, then sum the F-column values and divide by the total number of data points.)

c. Explain the difference in the two means: which mean, the Data xBar or the f-xBar, is more accurate, and why?