Methods and Systems for Business Decisions (4BUS1019) Semester B 2012-13
Tutorial Week 2 Statistics
1. Open the Excel file Week 2 Data 1 (t-shirts) from Studynet, Teaching Resources, Week 02 Statistics.
You are going to obtain a summary table by the method given in the lecture slides 6-10.
Click on a convenient cell, e.g. C6, then click ’Insert’ and select ‘Pivot Table’. A dialogue box will
appear. In ‘Table/Range’ enter a1:a121 (this is where the data items including label are located).
Our chosen cell, C6, appears in ‘Location’. Click ‘OK’.
The pivot table appears. Drag ‘Size’ and drop it down in ‘Row Labels’. Categories appear in the pivot
table. Now drag ‘Size’ and drop it down in ‘∑ values’. The counts appear.
How many t-shirts are (a) Small, (b) Medium, (c) Large, (d) XLarge?
2. Open the Excel file Week 2 Data 2 (waiting times) from Studynet, Teaching Resources, Week 02.
You are going to obtain a summary table using groups of 5-9, 10-14, 15-19, 20-24, 25-29, 30-34, 35-39.
In a convenient space, e.g. cell D2, enter u.c.b. (which stands for upper class boundary).
In the 7 cells below enter 9 14 19 24 29 34 39 .
In cell E2 enter frequency .
Now highlight the block of 7 cells E3 down to E9. It is here that the frequencies will appear.
Type =frequency(
The data array (excluding label) is a2 to a101 so Type a2:a101,
The bins array are the block containing 9, 14, 19, ... 39 so Type d3:d9)
(You should now have =frequency(a2:a101,d3:d9)
STOP! READ CAREFULLY – The usual press ‘Enter’ does not work here because we have what is
called an ‘array formula’. Instead, with your left hand hold down the two keys ‘Control’ and ‘Shift’, and
with your right hand press ‘Enter’. This should give you the 7 frequencies. (If it hasn’t worked and you
only got the first frequency then delete cell E3 and try again.)
(a) What frequencies have you obtained?
(b) Sum the cells E3 to E9 and check it matches the original number of data items.
3. In a questionnaire holidaymakers rate their hotel accommodation on a 5 point scale (5=very good,
1=very poor).
Rating 5 4 3 2 1
Sandy Bank Hotel 24 21 17 5 2
Cliffside Hotel 25 31 26 14 6
Calculate the mean rating for each hotel and discuss your results.
4. Data: 42, 46, 53, 40, 44, 51, 48, 50, 25, 42, 49.
(a) Calculate the mean, median and mode.
(b) Explain which you consider to be the most appropriate measure of location in this case.
(c) Calculate the range and the inter quartile range.
(d) Explain which you consider to be the most appropriate measure of spread in this case.
5. Calculate the mean of the number of accident report forms received per day in the following table.
Number of accidents 0 1 2 3 4 5
Number of days (frequency) 25 16 5 3 0 1
6. Without using a calculator explain which of these data sets has the lower standard deviation.
Data set 1: 18, 19, 20, 21, 22 Data set 2: 15, 18, 20, 22, 25
7. The following data, from two workshops, shows the thickness (in mm) of ten paint samples from
a car-body panel, taken as part of a quality control exercise.
Workshop A 1.1, 1.2, 1.1, 1.3, 1.5, 2, 1.8, 1.7, 1.5, 1.2
Workshop B 1.3, 1.2, 1.3, 1.5, 1.1, 1.4, 1.3, 1.2, 1.4, 1.1
(a) Use a calculator (in statistical mode) to find the mean and standard deviation of each data set.
(b) Compare the thicknesses between the two workshops.
8. The following frequency table shows the time in working days between placing an order and receiving
it, by 100 customers of a mail-order company.
Time No of customers
less than 5 6
5 but under 10 13
10 but under 15 21
15 but under 20 30
20 but under 25 25
25 but under 30 4
30 but under 40 1
(a) Set up a table with columns x, f, fx, and fx2 to calculate the mean and standard deviation. You should
assume the mid-points (for your x value) are 2.5, 7.5 etc - but be careful with the final value.
(b) If a sample of customers from a rival mail-order company waits for a mean time of 14 days with a
standard deviation of 9 days, what can you say about the relative performance of the two companies?
Answers (to be confirmed)
1 (a) 37 (b) 39 (c) 26 (d) 18.
2 17, 14, 25, 14, 17, 11, 2.
3 Sandy Bank Hotel 3.87, Cliffside Hotel 3.54.
4(a) Mean 44.5 (1dp), median 46, mode 42. (c) Range 28, IQR 8.
5 Mean 0.8.
6 Data set 1 shows less variability so the standard deviation is lower.
7(a) Workshop A: Mean is 1.44 mm and the sample standard deviation is 0.3134 mm.
Workshop B: Mean is 1.28 mm and the sample standard deviation is 0.1317 mm.
8(a) Mean = 16.075 days. standard deviation = 6.560 days. (Note: the mid-point of last class is 35)
