Question

You want to determine the speed limit for a local highway.   

Distribution data for the driving speed on a given street is given below; all speeds have been rounded to the nearest integer.  

Speed(x)	Car Count
39	8
40	8
41	24
42	24
43	48
44	96
45	208
46	200
47	200
48	136
49	16
50	16
51	8
52	0
53	8

Part a: What is the average(mean) and standard deviation of this sample of data? (Hint, you will need to manipulate the data – you cannot use the average or stdev functions in excel on the data as is.)

Part b: What is the value that 85% of the drivers fall under (the 85% speed limit) that you would recommend using this data? Answer the following questions to help you think through this problem.  

• When do you use the respective distribution or inverse functions in excel? Which situation do you have here and how do you know?

• Using that information, what would you recommended speed limit this road? (Think about speed limits and how they are posted?)

Part c: What percent of people drive within 5 mph of your recommended 85% speed limit, using this data?

Answer 1

a)

Speed	Count	Speed*Count	(Mean -X)^2	Count*(Mean-X)^2
39	8	312	46.45786	371.6628
40	8	320	33.82586	270.6068
41	24	984	23.19386	556.6525
42	24	1008	14.56186	349.4845
43	48	2064	7.929856	380.6331
44	96	4224	3.297856	316.5942
45	208	9360	0.665856	138.498
46	200	9200	0.033856	6.7712
47	200	9400	1.401856	280.3712
48	136	6528	4.769856	648.7004
49	16	784	10.13786	162.2057
50	16	800	17.50586	280.0937
51	8	408	26.87386	214.9908
52	0	0	38.24186	0
53	8	424	51.60986	412.8788

mean =    ΣX/n =    45816.000   /   1000   =   45.8160

sample std dev =   √ [ Σ(X - X̄)²/(n-1)] =   √   (4390.144/999)   =       2.0963

b)

µ=   45.816                  
σ =    2.0963                  
proportion=   0.85                  
                      
Z value at    0.85   =   1.04   (excel formula =NORMSINV(   0.85   ) )
z=(x-µ)/σ                      
so, X=zσ+µ=   1.04   *   2.0963   +   45.816  
X   =   47.99 = 48

c)

µ =    45.816                              
σ =    2.0963                              
we need to calculate probability for ,                                  
P (   42.99   < X <   52.99   )                  
=P( (42.99-45.816)/2.0963 < (X-µ)/σ < (52.99-45.816)/2.0963 )                                  
                                  
P (    -1.348   < Z <    3.422   )                   
= P ( Z <    3.422   ) - P ( Z <   -1.348   ) =    0.9997   -    0.0888   =    0.9109

= 91.09%

Please let me know in case of any doubt.