*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

**Follow-Ups**:

**References**:**Constrain a polynomial trend line to intercept y-axis horizontally***From:*Mike Simms

**Re: Constrain a polynomial trend line to intercept y-axis horizontally***From:*Jean BrÃ©fort

**Re: Constrain a polynomial trend line to intercept y-axis horizontally***From:*John Denker

**Re: Constrain a polynomial trend line to intercept y-axis horizontally***From:*Mike Simms

[Date Prev][Date Next] [Thread Prev][Thread Next] [Thread Index] [Date Index] [Author Index]