https://bugs.documentfoundation.org/show_bug.cgi?id=165355
Andrea Sosso <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Ever confirmed|1 |0 Status|NEEDINFO |UNCONFIRMED --- Comment #3 from Andrea Sosso <[email protected]> --- (In reply to Olivier Hallot from comment #2) > The trend line equation shown is f(x) = -0.000123176136427588 x + > 0.000205293718377051 > > However > SLOPE(B2:B1312,A2:A1312) = 1.49601537938155E-07 > INTERCEPT(B2:B1312,A2:A1312) = 0.000205268824981841 > > So the trend line equation should be > > f(x) =1.49601537938155E-07 x + 0.000205268824981841 > > Please confirm if the discrepancy is in the slope of the trend line > equation. Does the suggested equation fits your expectation? I'm sorry my description of the issue was possibly misleading. I'll try to make things clearer. The issue IN MY TEST was with the TREND function: I found detectable errors in both coefficient values. §§§ The alleged faulty TREND function is calculated in col E: E(n)=value of trendline in A(n) TREND(B$2:B$1312,A$2:A$1312,A(n), ) # linear fit to B(A) §§§ In col F, I calculate residuals: y-data (in col B) minus fit (in col E). Since I'm subtracting constant and linear terms from the original function B(A), residuals must contain only >/= 2nd order terms. But this is not the case! §§§ See lower plot on the right: data from column F show a clear linear trend in the graph and the equation displayed plotting the trendline of data provides a quantitative estimate of the error values of the should-be-zero coefficients §§§ Further subtracting (in col G) this linear error contribution form the "sloped" residuals, I finally get the expected results graphed in red in the same bottom-right plot and in upper plot §§§ That said, of course even this second "correction" fit will be subjected to the same errors. I notice that you focused on this, instead of my TREND calculations, basically reproducing by yourself, and differently, the same test. §§§ Finally I answer to your question: yes the discrepancy is only in the slope of the trendline. My assumption that both slope and intercept were bad came from the nonzero crossing point in the plot of residuals, but I realize now that this may be just a side-effect of the slope error. §§§ I didn't know about SLOPE and INTERCEPT functions that are apparently working fine. Looks strange TREND is not using the same routines. -- You are receiving this mail because: You are the assignee for the bug.
