# Excel

posted by .

Chapter Three

1. Using the following data for high school track athlete’s times (in seconds) for sprinting the 100-m dash, calculate the following:
a. Mean
b. Median
c. Mode
d. Lower Quartile
e. Upper Quartile
f. 80th Percentile
g. What percentile would a racer be if they finished with a time of 17.5 seconds?
h. What percentile is the racer who finished a time of 13.9 seconds?
14.8 16.3 13.9
16.2 12.7 14.1
14.4 13.7 15.2
14.1 17.0 13.4
18.8 14.4 14.3
13.1 11.5 15.8
13.2 15.6 18.1

2. The grading for this course is made up of the following components:
Professionalism 5%
Problem Sets 18%
Project 25%
Midterms 22%
Final 30%
Calculate the final grade of a student who had the following scores:
Professionalism 10/10
Problem sets 53/60
Project 193/200
Midterms 17/90
Final 39/75
Next, calculate what their grade would be if each component had equal weighting. Why wouldn’t a professor use equal weighting?

3. The following data is on the number of books checked out at the Fraser Valley Regional Library’s Langley Branch over the course of 12 months.
a. Assume this data is a sample. Compute the range and standard deviation. Use the “long way” of calculating standard deviation by breaking it into its constituent parts like in the demonstration spreadsheet.
b. Now assume this data is a population (i.e. this branch has only been open for a year). Compute the standard deviation.
c. Indicate why these numbers are different. (I.e. why are the formulae for samples and populations different? What purpose does it serve?)
5,175 5,781 5,384
4,139 4,531 4,871
5.005 5,233 5,670
6.921 4,197 4,188

4. Use the following two distributions.
Distribution A Distribution B
µ = 45,600 µ = 33.4
ó = 6,333 ó = 4.05
a. Compute the coefficient of variation for each distribution.
b. If a value drawn from A is 50,000 and a value drawn from B is 40, convert each value into a z-score and indicate which is relatively closer to its respective mean.

5. In the early 1970s, a Vessel Sanitation Program (VSP) was started for the cruise ship industry because several diseases had broken out on cruise ships. The VSP protects the health of passengers and crew by scoring each ship based on a 100-point scale for sanitation. A boat that achieves a score of 86 or higher is considered to have satisfactory sanitation. (Data from a recent inspection are provided in an accompanying file.)
a. Calculate the mean, standard deviation, median and inter-quartile range
b. Look at the distribution of score visually. Would the Empirical Rule or Chebyshev’s theorem be preferred to describe this data set?
c. If a passenger only wanted to travel on ships that were at the 90th percentile or higher in terms of VSP ratings, what is the lowest sanitation score they would find acceptable?

6. (Based on Case 3.4 in edition 8) AJ Fitness is a new fitness club in town. AJ Reeser bought out an existing health club (known as the Park Centre Club) and conducted a survey of its existing membership of 1,833 to find out their current level of satisfaction. (AJ knows it is a lot cheaper to keep existing customers than trying to attract new ones.) The response rate to the survey was very good for a mail-out survey – 1,214 members returned the survey. AJ hired a business student from the local university to conduct an analysis of the results. When the student asked him for some direction in the project, AJ’s reply was, “That’s what I hired you for. I just want a descriptive analysis of these results. Develop whatever charts, graphs and tables that will help us understand our customers. Also use whatever pertinent numerical measures that will help us.” AJ set up a time next week to meet to discuss the student’s work.
Develop descriptive statistics (both graphical and numerical) that could be used in the report