Re: Constrain a polynomial trend line to intercept y-axis horizontally

[John Denker <jsd av8n com>]

On 06/14/2013 06:44 AM, Jean Bréfort wrote:
> you can evaluate such a regression in
> the sheet, usin x^2, x^3, and so on as independent data in the linest
> function.

That is excellent advice.

More generally, it is a good practice to /never/ use the trend-line
features of the plotting subsystem, for several reasons:

1) The trend line should be considered /fitting/ a model to the data.

1a) Very commonly, you want to fit to something other than a simple
 polynomial.  In the case under discussion, you might want a polynomial
 where the "x^1" monomial term does not appear.  Or, if the data is
 periodic, you might want a Fourier cosine series.  Linest can handle 
 this sort of thing with ease.

1b) Very commonly, you want to see the numerical values for the
 fitted coefficients of the model.  Linest provides them.

1c) Very often, proper modeling requires a /weighted/ fit to the 
 data.  You can trick linest into doing this for you.

1d) Continuing the previous thought:  Very often you want to fit to
 the data in one representation, then transform the data (and the fit)
 to another representation for plotting.

=========

Note that you can use /array constants/ to simplify the specification
of the basis functions you want.  Example:  A1:A100^{0,2,3,4} ... where
for the case under discussion, you want the power "1" to be left out
of the list.

The "affine" option to linest provides an alternate way of specifying
the x^0 basis function.

For details on all this, see
  http://www.av8n.com/physics/linear-least-squares.htm