Bug 114826

Summary:	Different Results Calculating Logarithmic Regression with two methods of Calc
Product:	LibreOffice	Reporter:	jrodriguez1717
Component:	Calc	Assignee:	Dennis Francis <dennisfrancis.in>
Status:	RESOLVED NOTABUG
Severity:	normal	CC:	dennisfrancis.in, ilmari.lauhakangas, raal
Priority:	medium
Version:	6.0.0.1 rc
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Different Results Calculating Log Regression

Description jrodriguez1717 2018-01-03 17:01:57 UTC

Description:
I use Logest function and Statistic Logarithmic Regression Option (By Menu), to calculate the results to get it but I found that only in the R² are co-occurring, in the rest of result: Slope, Intercept, Standard Error, are different.

If I do the process well, one of the two methods would be wrong or both.

An example is attached to this bug report.

Steps to Reproduce:
1.Set a two data variable set in two different columns. Exm: Y1, X1 Both with 10 values.
2.Select Logest Function and there select Y1 data and then X1 Data, select 0 in the third option and 1 in the last option. And Accept to get the results.
3.Use Menu-Data-Statistics-Regression -Logarithmic Option ( Set Y1 as variable 1 and X1 as variable 2 ). Accept and get the results.
4.Compare the results of both methods,only R² are equals.

Actual Results:  
The same, only R² are equals.

Expected Results:
It should offer the same results calculating the logarithmic regression using both methods.


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.0.0.1
Build ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; 
Locale: es-ES (es_ES.UTF-8); Calc: group


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:57.0) Gecko/20100101 Firefox/57.0

Comment 1 jrodriguez1717 2018-01-03 17:07:14 UTC

Created attachment 138859 [details]
Different Results Calculating Log Regression

Comment 2 Buovjaga 2018-01-29 17:02:05 UTC

raal: can you confirm this? I'm not good at this science.

Comment 3 Dennis Francis 2018-06-17 12:36:40 UTC

I can confirm the differences in results between LOGEST() and that via statistics menu's regression option. I can confirm that the LOGEST function is computing the right answers (compared with Excel online). The formulas generated by the statistics regression tool for logarithmic regression seems to have mixed up the roles of X and Y variables for computing slope, intercept and standard error. I need to compare the results of statistics tool box of Excel desktop (not the free online version as it does not seem to have toolboxes), to decide on what the acceptable behaviour should be.

Comment 4 Dennis Francis 2018-06-21 12:18:54 UTC

Actually LOGEST(Y, X) solves the exponential model y = b*m^x, and gives you the values of b, m, and other statistics, but the regression tool "logarithmic regression" solves a logarithmic model given by "y = a*ln(x) + b" and provides you the slope(a) and intercept(b). So naturally they are different. Note that coefficient of determination (R^2) between ln(y) and x in the exponential model given by LOGEST() will match that of the R^2 given by logarithmic regression tool if you swap the roles of x and y, but not the other results. See the differences and relationship between these two models of regression here :- 
https://help.libreoffice.org/Chart/Trend_Lines

So this is not a bug, but I agree that there needs to be statistics tool for exponential regression as well. I'll try to add that soon.