Share
Explore BrainMass

Statistical measurements using Spreadsheet

a) Construct a scatter diagram for the following data:

x 1 2 3 4 5 6 7 8 9 10
y 10 10 11 12 12 13 15 18 21 25

x 11 12 13 14 15 16 17 18 19 20
y 26 29 33 39 46 60 79 88 100 130

Find the coefficient of correlation and the coefficient of determination. Now find the log of y and recalculate the two statistics and interpret your results.

b) The mileage recorded for a sample of company vehicles during a given week yielded the following data:
138 164 150 132 144 125 149 157
146 158 140 147 136 148 152 144
168 126 138 176 163 119 154 165
146 173 142 147 135 153 140 135
161 145 135 142 150 156 145 128

Determine the range, quartile deviation and standard deviation from these figures. The data below now becomes available on the mileages of the other ten cars belonging to the company.

234 204 267 198 179 210 260 290 198 199

Recalculate the range, quartile deviation and standard deviation, and comment on the changes to these statistics. You are also required to create a scatter graph and a histogram from the initial data using Microsoft Excel graphs and charts.

Attachments

Solution Summary

The solution computes the scatter diagram, coefficient of correlation, coefficient of determination, range, quartile deviation and standard deviation from the given data.

The solutions are contained in two (2) spreadsheet files showing the detailed spreadsheet formula.

$2.19