The following data were collected on speeds on H1, a major highway, using excel’s statistical functions, complete the following questions: 90, 75,57,64,68,60,82,67,78,57,71,52,65,63,69,61,55,55. What is the mean of the data set for H1? what is the standard deviation of the data set for speed on H1? construct a histogram for the speed data set using 5mph as your class width starting with 50 as your lowest class.
The following data were collected on speeds on H1, a major highway, using excel's statistical functions
Expert Answer
Mean = sum of all speeds/no. of data points
| S. no. | |
| 1 | 90.00 |
| 2 | 75.00 |
| 3 | 57.00 |
| 4 | 64.00 |
| 5 | 68.00 |
| 6 | 60.00 |
| 7 | 82.00 |
| 8 | 67.00 |
| 9 | 78.00 |
| 10 | 57.00 |
| 11 | 71.00 |
| 12 | 52.00 |
| 13 | 65.00 |
| 14 | 63.00 |
| 15 | 69.00 |
| 16 | 61.00 |
| 17 | 55.00 |
| 18 | 55.00 |
| Total | 1,189.00 |
| Mean | 66.06 |
Mean = 1189/18 = 66.06. In excel the “average” function can be used. The syntax is: = average(selection of all the data cells pertaining to speed)
Standard deviation = [sum of (x- mean of x)^2/n-1]^0.5
In excel standard deviation can be computed using the “stdev” function. The syntax is: = stdev(selection of all the data cells pertaining to speed)
Using this function we get standard deviation as 10.21
Histogram is provided in the attached image below. Select the data in input range and provide bin range starting with 50 and then using a gap of 5.
| S. no. | Input range | Bin range |
| 1 | 90.00 | 50.00 |
| 2 | 75.00 | 55.00 |
| 3 | 57.00 | 60.00 |
| 4 | 64.00 | 65.00 |
| 5 | 68.00 | 70.00 |
| 6 | 60.00 | 75.00 |
| 7 | 82.00 | 80.00 |
| 8 | 67.00 | 85.00 |
| 9 | 78.00 | 90.00 |
| 10 | 57.00 | 95.00 |
| 11 | 71.00 | |
| 12 | 52.00 | |
| 13 | 65.00 | |
| 14 | 63.00 | |
| 15 | 69.00 | |
| 16 | 61.00 | |
| 17 | 55.00 | |
| 18 | 55.00 |
Image of histogram:
