Question

transpo oprtn and logistics managment/ please use excel

Problem 11. (10 marks) The accompanying data are the travel times (min) on an arterial street collected by ten test vehicles. Vehicle 5 6 7 1 2 3 4 9 10 16 10 1 22 Travel Time (min) 10 14 9 10 12 11 Use Excel for Analysis a. Calculate the values of the sample mean, median, and mode b. From the relative locations of the above summary statistics, can you conjecture the shape of the distribution (hint: probably develop a histogram)? Would it be symmetric, skewed right, or skewed left? c. Compute the 25th percentile and the 75th percentile d. Compute the sample standard deviation and the interquartile range. What would you recommend as a measure of variability for these data? Why?

Answer 1

82 MIcrosoft Excel PrvotTable Tools Home Insert Page Layout Formulas Data Review View Options Design E AutoSum Cut = = = A A Wrap Text Calibri 11 General Fill Copy Paste Merge & Center Conditional Format Cell Insert Delete Format Sort & Find & BIU Format Painter Clear % - Formatting s Table Styles Filter Select Alignment Number Cells Editing Clipboard Font Styles fCount of Travel Time F2 GHIJK O P C D N A PivotTable Field List Travel Time (b.) THE HISTOGRAM IS SYMMETRIC Vehicle 1 Choose fields to add to report PIVOT TABLE in min Count of Travel Time 2 10 Row Labels HISTOGRAM 1 Timein min 14 2-3 3 10 10-11 6 5 12 12-13 1 7 6 16 14-16 2 8 7 10 Grand Total 10 9 11 10 0 11 10 11 2-3 R-C 10-11 12-13 14-16 12 13 (a.) 14 Mean TRAVEL TIME in min 10.5 Drag fields between areas below: Y Report Fiter Column Labels 15 Median= 10.5 16 Mode (d.) Std Deviation 10 17 3.6591 1st quartile, Q 10 18 2nd quarrtile, Inquartile-Q3 19 (c.) 20 25th percentile 11.75 Row Labels E Values 10 1.75 Count of Tra.. Travel Time.. 21 75th percentile: 11.75 22 23 Defer Lavout Update Undate 14 Sheet1 Sheet2Sheet3 Ff 100 % Ready 11:11 AM 08/06/2019 FREQUENCY inm

82 Microsaft Excel PrvotTable Tools X Home Insert Page Layout Formulas Data Review View Options Design Trace Precedents Show Formulas Trace Dependents Remove Arrows Define Name - Calculate Now Error Checking Use in Formula Calculation Calculate Sheet AutoSum Recently Financial Logical Date & Lookup & Time Reference Insert Text Math More Name Watch Evaluate Formula Manager Create from Selection Trig Functions Function Used Window Options Defined Names Formula Auditing Calculation Function Library fCount of Travel Time F2 H J K A C D F G Travel Time (b.) Vehicle PIVOT TABLE in min 1 Count of Travel Time 2 1 10 Row Labels 14 3 2 s 4 10 10-11 6 5 12 12-13 7 6 16 14-16 2 8 7 10 Grand Total 10 9 8 11 10 9 11 10 11 12 13 (a.) =AVERAGE(B2:B11) -MEDIAN ( B2: B11) -MODE ( B2 : B11 ) 14 Mean= 15 Median= 16 Mode (d.) Std Deviation= 1st quartile, Q1= =STDEV(B2:B11) -QUARTILE ( B2:B11,1 -QUARTILE (B2:B11,3 17 18 2nd quarrtile, Q2 19 (c.) 20 25th percentile Inquartile Q3-Q1 = PERCENTILE ( B2:B11,0.25) =PERCENTILE(B2:B11,0.75) -F19-F18 21 75th percentile 22 23 Sheet1 Sheet2Sheet3 14 Fi 100% Ready 11:13 AM 08/06/2019