Insert Page Layout Formulas Data Review View Times New R | | 12 , A A :- Paste T
ID: 3306722 • Letter: I
Question
Insert Page Layout Formulas Data Review View Times New R | | 12 , A A :- Paste Time (minutes) 3 Atlanta 4 Austin S Baltimore 6 Boston 7 Charlotte 23.3 28.3 24.6 32.1 25.8 38.1 24.9 26.8 9 Cincinnati 10 Cleveland 1 Columbus 12 Dallas 13 Denver 14 Detroit 28.5 28.1 29.3 24.4 23.0 24.8 El Paso 16 Fresno 17 Indianapolis 23.4 28.4 20.1 32.2 21.4 23.8 30.7 24.8 23.6 25.3 31.7 43.8 22.0 21 Little Rock 22 Los Angeles 23 Louisville 24 Memphis 25 Miami 28 Nashville 29 New Orleans 30 New York 31 Oklahoma City 32 Orlando 33 Philadelphia 34 Phoenix 34.2 25.0 Sheet2 Sheet3+Explanation / Answer
First Question
In Excel, the data are assumed to be in cells A1:A48.
(a) Mean commute time = average of all commute times = sum/number of cities = 1291.5/48 = 26.91 minutes. (Excel formula = average(A1:A48))
(b) Median commute time = average of the 24th and the 25th entries when sorted = (25.8 + 26.1)/2 = 25.95 minutes. (Excel formula = median(A1:A48))
(c) Mode corresponds to the entries with maximum frequency. The values 23.4 and 24.8 occur with the most frequency (3 times each). Hence, the modes are 23.4 and 24.8 minutes. (Excel formula = mode(A1:A48))
The values in ascending order are (can use right click, sort over the cells A1:A48 in Excel to get these):
(d) 3rd quartile = Median of the upper half of the data set = average of the 36th and the 37th term when arranged in ascending order = (28.1 + 28.1) / 2= 28.1 minutes. (Excel formula: = percentile(A1:A48, 75%)
Second question
The growth rates for the 5 years are 5.5%, 1.1%, -3.5%, -1.1% and 1.8%. Let us assume that in the beginning of the first year, the revenue for corning supplies were = 100. So, at the end of the first year, it became 100 * 1.055 (increase of 5.5%) = 105.5. At the end of second year, it became 105.5 * 1.011 = 106.6605, and so on. At the end of the fifth year, the revenues became = 100 * 1.055 * 1.011 * 0.965 * 0.989 * 1.018 = 103.627494555765.
So, the overall growth at the end of 5 years is 3.627494555765%. Divide that by 5 to get the mean growth annual rate over the period = 3.627494555765 / 5 = 0.725498911153% = 0.7255% rounded to 4 decimal places.
Third Question
Mean price for a round trip flight to Atlanta = $ 713.46
Mean price for a round trip flight to Salt Lake City = $ 801.90
Yes, Atlanta is less expensive to fly than Salt Lake City. Atlanta is in Georgia. Salt Lake City is in Utah, which is an interior state and perhaps not well connected by air. So, for travelling from some cities in the US to Salt Lake City, one may have to go via connecting flights. Atlanta however is well-connected and from most cities, direct flights may be available. This is why flying to Atlanta is cheaper.
Range: Atlanta - (249.6, 539.6), Salt Lake City - (159.6, 618.4)
Variance: Atlanta - 5517.41, Salt Lake City - 18933.32
Standard Deviation: Atlanta - $ 74.28, Salt Lake City - $ 137.60
For standard deviation, use the formula = stdev(G1:G14) in Excel, where G1:G14 is the data. Variance is the square of the standard deviation. Range is (min, max) of the values (use min(G1:G14) and max(G1:G14) in Excel).
Prices are more variable for Salt Lake City, again indicating higher variability because sometimes a direct flight to Salt Lake City is available and sometimes one has to go via connecting flights resulting in a much higher price.
20.1 20.1 20.2 21.4 22.0 23.0 23.3 23.4 23.4 23.4 23.6 23.6 23.8 24.0 24.4 24.6 24.8 24.8 24.8 24.9 25.0 25.3 25.8 25.8 26.1 26.2 26.4 26.8 26.8 27.1 27.3 28.1 28.3 28.3 28.4 28.5 28.5 29.3 30.7 31.7 31.7 32.1 32.2 32.6 32.8 34.2 38.1 43.8