[Phys-L] Re: Charts or Graphs, and other Excel stuff

[jsd at AV8N.COM (John Denker)]

Edmiston, Mike wrote in part:

> (3) Suppose the intercept of a linear regression is an important
> parameter for the report.  Excel automatically runs the regression line
> from the first data point to the last data point.  It doesn't extend the
> regression line to visually show the intercept unless you manually
> extend it.  I expect students to do this, but many don't, even after I
> tell them.

At the risk of ruining my reputation by actually knowing something
about windows and even saying something not-totally-nasty about it,
I don't see what the problem is here.

Excel has a usable (not very good, but usable) worksheet function
called LINEST.  It will fit a straight line (y = m x + b) to your data,
and return you m, b, and a bunch of additional statistics, including
the standard error of m and the standard error on b.

1) Since we are supposing that the intercept is an important parameter,
everybody should be using LINEST to calculate this parameter!

Also it wouldn't kill them to calculate the standard error on the
intercept, either.

2) Having calculated m and b, it is straightforward to draw the
fitted line, extended however you please.

3) Yes, I am aware that y = m x + b is not necessarily the most
numerically-stable representation for a straight line.  There are
tricks you can do to improve the stability of LINEST, but the
details are beyond the scope of this note.

========

Just because the prog has an automatic feature doesn't mean you
are obliged to use it.  I didn't even know there was an "automatic"
regression line feature.  I don't plan to pollute my brain by
learning how to use it.

It may be that LINEST is a not-quite standard feature that has to
be loaded from some library.  I wouldn't know such details.  But
if so, anybody enrolled in physics should have long-since loaded
whatever libraries are needed for stuff like this.

========

LINEST is an "array function".  Be sure you know the trick for entering
an array function.  (Mostly:  control-shift-enter.)