Business Statistics with Computer Applications I Excel Project #1
EXCEL PROJECT #1
The following question must be done on Excel. The instructor is looking for evidence of
learning on how to carry out simple descriptive statistics using Excel. Please read the
question carefully and answer the following questions.
(Adapted from Hines and Montgomery – p.4). Consider the data below. These data are the
strengths in pounds per square inch (psi) of 100 glass nonreturnable one-litre soft drink
bottles. These observations were obtained by testing each bottle until failure occurred. The
data were recorded in the order in which the bottles were tested, and in this format they do
not convey very much information about bursting strength of the bottles. Questions such
as “what is the average bursting strength?” or “what percentage of bottles burst below
230psi?” are not easy to answer when the data are presented in this form.
265 197 346 280 265 200 221 265 261 278
205 286 317 242 254 235 176 262 248 250
263 274 242 260 281 246 248 271 260 265
307 243 258 321 294 328 263 245 274 270
220 231 276 228 223 296 231 301 337 298
268 267 300 250 260 276 334 280 250 257
260 281 208 299 308 264 280 274 278 210
234 265 187 258 235 269 265 253 254 280
299 214 264 267 283 235 272 287 274 269
215 318 271 293 277 290 283 258 275 251
Required Deliverables:
i) Enter these data into Excel. Define a suitable name for the data.
ii) Compute descriptive statistics using individual commands as well as using Data
– Data Analysis – Descriptive Statistics.
iii) Provide a Five-Point summary (i.e. min., max. and quartiles). Hence, calculate
the IQR (Inter-Quartile Range) to determine inner and outer fences. By
examining the inner and outer fences, identify if there are any outliers. Write
down in a text box if there are any outliers.
iv) Construct a frequency table to group the data and produce one Histogram, two
Ogives and two Frequency polygons (as shown in the lecture notes or Excel
instructions) to summarise the data graphically. The following may be of help.
A frequency distribution is a more compact summary of data than the original
observations. To construct a frequency distribution, we must divide the range of
the data into intervals, which are usually called class intervals. If possible, the
class intervals should be of equal width, to enhance the visual information in the
frequency distribution. Some judgement must be used in selecting the number
of class intervals in order to give a reasonable display. The number of class
intervals used depends on the number of observations and the amount of scatter
or dispersion in the data. A frequency distribution that uses either too few or too
many class intervals will not be very informative. We generally find that between
5 and 20 intervals is satisfactory in most cases, and that the number of class
MS 1023 Business Statistics with Computer Applications I Excel Project #1
intervals should increase with n (sample size). Choosing the number of class
intervals approximately equal to the square root of the number of observations
(i.e. n) often works well in practice.
Since the above data set contains 100 observations, we suspect that about
square root (100) = 10 class intervals will give a satisfactory frequency
distribution. The largest and smallest data values are 346 and 176, respectively,
so the class intervals must cover at least 346 – 176 = 170psi units on the scale.
If we want the lower limit for the first interval to begin slightly below the smallest
data value and the upper limit for the last cell to be slightly above the largest data
values, then we might start the frequency distribution at 170 and end it with at
350. This is an interval of 180 psi units. Nine class intervals, each of width 20psi,
give a reasonable frequency distribution.
v) You must save your file using your last and first name (for example,
Boyd_Michelle_Excel project#1.xlsx). Then submit your file to Blackboard Learn
server by the deadline specified for the project (see Blackboard).
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!
