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: Mon, 17 Jun 2013 07:57:20 -0700
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
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]