Chronology Current Month Current Thread Current Date
[Year List] [Month List (current year)] [Date Index] [Thread Index] [Thread Prev] [Thread Next] [Date Prev] [Date Next]

Re: Regression Lines (was Computers)



Donald E. Simanek sent this data
x y

0.002 0.000
0.117 0.025
0.238 0.050
0.356 0.075
0.472 0.100
0.597 0.125

IBM Excel gives a = 4.751429, b = 0.0050502
MAC Excel gives a = 4.7514 , b = -0.0002


I used Excel 5 on a Mac 68LS040 Performa.

Fitting a "trend line" to the data gave me slope of 4.7520 and intercept of
1.0574E-16

To get uncertainties in values I used the LINEST function. (A LOGEST is
also around.)

Select an area of 5 rows and 2 columns. In first cell write
LINEST(B1:B5, A1:A5,,TRUE)
that is the range of y-values, the range of x-values, TWO commas (to avoid
forcing the fit through zero), and TRUE to do the whole set of statistics.

Now hold the Apple key down (control and shift on the Wintel I think) and
hit the return key.

The following appears
4.752 1.4803E-16
0.02475018 0.00187337
0.9998915 0.00258844
36863.4627 4
0.2469852 2.68E-05

First row : slope and intercept
Second row : the standard errors in the slope and intercept.
Third row : r-squared and the standard error for the y-estimate
Fourth row : the F-statistic and the degrees of freedom
Fifth row : the regression sum of squares and the residual sum of squares.

In the Function Reference manual (for Excel 4 which is near me) the details
are spelled out.

Multiple regression statistics are similarly available.

Now the intercepts from trend line and LINEST do not agree. Hmm, I wonder
why, maybe they got a good deal on old Pentium chips with which to add!

Dr. Vern Lindberg
Dept. of Physics
Rochester Institute of Technology
vwlsps@rit.edu