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

• From: John Denker <jsd av8n com>
• To: gnumeric-list gnome org
• Subject: Re: Constrain a polynomial trend line to intercept y-axis horizontally
• Date: Fri, 14 Jun 2013 11:38:48 -0700

```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.
```
```

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

```

• Follow-Ups: