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

[John Denker <jsd av8n com>]

On 06/17/2013 06:07 AM, Mike Simms wrote in part:

> if i type 
> =linest(A1:A1000^{0,2},b1:c1000,TRUE) then I get some large numbers which
> can't be right. 

For reasons discussed below, I suggest instead either
  =linest(A3:A23,B3:B23^{0,2},FALSE)
or
  =linest(A3:A23,B3:B23^2,TRUE)

1) It works better if you form the basis functions by taking
  powers of x (not y).

  More generally:  Note that the documentation for linest is
  confusing.  It says the arguments are "Known Ys" and "Known Xs"
  but in terms of the usual Y(X) terminology, the second argument
  is not X.  Using dimensional analysis, the second argument doesn't
  even have the same dimensions of X.  (Consider the case where 
  Y(X) is voltage as a function of time.) IMHO the second argument 
  should be called "Known basis functions" or something like that.

2) It works better if you include the x^0 basis function only
 once.  Either
  2a) include it explicitly in the second argument, and set the
   third argument to false, or
  2b) don't include it in the second argument, and set the 
   third argument to true.

If you try both, the system is singular.  It should give #NUM!
as the results.  I'm surprised it gives "large numbers" instead
of #NUM!.

===============

Putting it all together, it works fine for me.  Here's a working
example:
  http://www.av8n.com/computer/linest-intercept.gnumeric

==============

BTW, if you were wondering why you couldn't figure this out
by reading the reference I cited earlier, that's my fault.
Sorry. I messed up one of the links.  It should be better now;
see
  http://www.av8n.com/physics/spreadsheet-tips.htm
and reference 1 therein:
  http://www.av8n.com/physics/spreadsheet-tips.htm#bib-linear-regression
i.e.
  http://www.av8n.com/physics/linear-regression.xls
although the example cited earlier is simpler and more to
the point:
  http://www.av8n.com/computer/linest-intercept.gnumeric