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

[rlamont at POSTOFFICE.PROVIDENCE.EDU (rlamont)]

People - This is not rocket surgery!

As almost any one of your students will tell you, if you wish to
project a trendline on a chart in Excel, go to your graph, right
click on your data curve, choose ADD Trendline, click on the
OPTIONS and choose FORECAST (either backward or forward) and you
can extrapolate the trendline as far as you want in either
direction.

I am constantly learning the power of Excel. I no sooner tell my
students in a lab why you can't do a certain thing in Excel but
someone walks up and quietly explains to me how Excel already has
a procedure to do what I want built right into it.

I hope you've all found the trick where Excel can place a bar
graph and an x-y plot on the same chart. Wonderful for a
statistics of measurement lab! You can place the raw data and a
smooth Gaussian distribution on the same chart.

Learn not to fight Excel - go with the force!

Bob at PC

> -----Original Message-----
> From: Forum for Physics Educators [mailto:PHYS-
> L@list1.ucc.nau.edu] On Behalf Of John Denker
> Sent: Wednesday, October 05, 2005 2:36 PM
> To: PHYS-L@LISTS.NAU.EDU
> Subject: Re: Charts or Graphs, and other Excel stuff
>
> 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.)