Apache OpenOffice (AOO) Bugzilla – Issue 112773
Exponential fit is broken for small values
Last modified: 2017-05-20 11:41:48 UTC
Description: Ubuntu 10.04 LTS Release: 10.04 openoffice.org-calc:1:3.2.0-7ubuntu4.1 0 The exponential fit is broken. Using the following data plot A B 0.000568 0.22 0.001136 0.05 0.00170 0.02 0.000136 0.97 0.000329 0.42 Plot and add a trend-line. Make the trend-line exponential and you get a garbage fit. If you multiply column A by 4, the fit works. Multiply by 3 and it fails. It appears that the algorithm used for this type of trend-line is flawed and has limitations that do not occur in MS Excel.
An "exponentiell trend" means that you want to use a model y = a * b^x. Using your data the basis b would be exp(-2440). That is so near to zero, that it underflows to zero using double precision. The trend line calculation should not use a and b to calculate the y-values, but use ln(a) and ln(b) and first calculate ln(y), same as it is done in ScInterpreter::CalculateTrendGrowth(). As workaround you can calculate intermediate value pairs using GROWTH and add them as additional data series to your chart.
Change component to Chart.
Created attachment 74879 [details] example document showing the problem
taking issue
Created attachment 75014 [details] Changed output for cases with underflow. Because my problems with charts (see PM) an intensely testing is currently not possible. Please comment, whether the equation is in the desired format, otherwise I will change it.
Thanks Regina! The patch looks very good already :-) . I think it would be cool to have the equation always in the new format. That would better fit to the ODF spec anyhow. In addition I would skip the multiplier point as it is so longish.
Created attachment 75073 [details] Testcases. Do not forget to update the charts.
Created attachment 75074 [details] Patch for trendline itself and for equation
I have changed the equation in a way that for all normal cases it has the form "f(x)=Intercept exp(logSlope x)". If Intercept is near 1 but Slope is not near 1, I use "f(x)=exp(logSlope x)" instead of "f(x)= 1 exp(logSlope x)". If both Intercept and Slope are near 1, I use the form "f(x)=exp(logIntercept + logSlope x)". If Intercept is near 0, I use "f(x)=exp(logIntercept + logSlope x)" otherwise the output would be "f(x)=0 exp(logSlope x)" which is not informative for the user. If all y-Values are not positive the equation has the NaN error message inside the string. But in those cases a trend line is not drawn at all, so the user should already notice that there is an error.
The new patch looks very good. Thanks a lot Regina! :-) I've created CWS chart51 and applied it there.
Thomas, please verify in CWS chart51.
See ok in cws -> verified