Below is a set of data that shows the sales made by 20 staff members in the sales department
at XYZ Limited for the months of August and September 2020.
Sales Person August September
1 21900 12600
2 12400 13500
3 9800 22000
4 6800 34000
5 13050 12500
6 14900 25540
7 15250 21700
8 8900 67800
9 16451 18970
10 13230 16450
11 16450 23220
12 12890 21670
13 13670 17600
14 14700 32420
15 10430 23570
16 11670 14700
17 13200 22400
18 11100 21600
19 19800 26400
20 15700 19700
Based on the above table:
(a) Use Excel to calculate the mean, median, standard deviation, coefficient of variation, lower
quartile, upper quartile, and interquartile range using Excel formulas.
(b) What do the values of the mean and median tell you about the shape of the distribution of
data?
(c) Which month has the greatest spread of data?
(a) Here are the calculations using Excel formulas:
- Mean: =AVERAGE(B2:B21) for August sales and =AVERAGE(C2:C21) for September sales
- Median: =MEDIAN(B2:B21) for August sales and =MEDIAN(C2:C21) for September sales
- Standard Deviation: =STDEV.P(B2:B21) for August sales and =STDEV.P(C2:C21) for September sales
- Coefficient of Variation: =STDEV.P(B2:B21) / MEAN(B2:B21) for August sales and =STDEV.P(C2:C21) / MEAN(C2:C21) for September sales
- Lower Quartile: =QUARTILE(B2:B21, 1) for August sales and =QUARTILE(C2:C21, 1) for September sales
- Upper Quartile: =QUARTILE(B2:B21, 3) for August sales and =QUARTILE(C2:C21, 3) for September sales
- Interquartile Range: Upper Quartile - Lower Quartile
(b) The mean and median tell us about the shape of the distribution of data as follows:
- If the mean and median are approximately equal, the distribution is likely symmetric.
- If the mean is greater than the median, the distribution is likely right-skewed.
- If the mean is less than the median, the distribution is likely left-skewed.
(c) To determine which month has the greatest spread of data, we can compare the standard deviation, or look at the range of the data for each month. In this case, September has the greatest spread of data since the sales amounts are more widely distributed compared to August.