The following is a demonstration of the use of excell as a tool in statistics. We start by measuring the distance from each fret to the bridge of a guitar.
Fret number Distance to the bridge
| 0 | 64.5 |
| 1 | 60.7 |
| 2 | 57.4 |
| 3 | 54.1 |
| 4 | 51.1 |
| 5 | 48.3 |
| 6 | 45.5 |
| 7 | 43 |
| 8 | 40.6 |
| 9 | 38.4 |
| 10 | 36.3 |
| 11 | 34.2 |
| 12 | 32.3 |
| 13 | 30.4 |
| 14 | 28.7 |
| 15 | 27.1 |
| 16 | 25.6 |
| 17 | 24.2 |
| 18 | 22.8 |
| 19 | 21.6 |
| 20 | 20.4 |
The scatter plot using the fret numbers as the explanatory variable and,
the distancet to the bridge as the response variable looks like this.
The graph appears to be the graph of an exponential function. To test this hypothesis check the ratio
of consecutive distatnce measurement. The ratio in nearly constant so we can procede to find a predictor
equation for the data. This predictor is called a regression equation.
To begin take the natural log of response the variables.
| ln distance |
| 4.16666522380173 |
| 4.10594369806545 |
| 4.05004430332552 |
| 3.99083418585244 |
| 3.93378449720966 |
| 3.87743156065853 |
| 3.8177123259569 |
| 3.76120011569356 |
| 3.70376806660769 |
| 3.64805745959368 |
| 3.5918177412708 |
| 3.53222564406856 |
| 3.47506723022861 |
| 3.41444260841218 |
| 3.35689712276558 |
| 3.29953372788566 |
| 3.24259235148552 |
| 3.18635263316264 |
| 3.1267605359604 |
| 3.07269331469012 |
| 3.01553490085017 |
Now look at the scatter plot of this transformed data. Click here.
This graph appear linear so we construct a least squares regression line. The equation for the LSLR
b is the slope of the LSLR. b = r (the correlation coefficient) times the ratio of the standard deviations of fret data and distance data. So to find the slope b, we need to compute r. To see the formula for r click here.
n = the number of data points, x sub i is each fret value, x bar is the mean of the frets, s sub x is the standard deviation of the fret values, y sub i is each value of the natural log of distance from each fret to the bridge, y bar is the mean of the natural log of the distances, and s sub y is the standard deviation of the natural logs of the distances.
r = -.9999
b = -.0575
a = 4.16
The predictor equation is ln y = 4.16 - .0575x for the LSLR. This is for the tranformed data. To get the predictor equation for the original data, use each side of the equation as an exponent and e as the base. Computation.
The overlayed graph are below.