Bug 153208 - Regression Analysis Error: LINEST array #VALUE! errors on specific dependent variables
Summary: Regression Analysis Error: LINEST array #VALUE! errors on specific dependent ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function Data-Statistics
  Show dependency treegraph
Reported: 2023-01-25 21:16 UTC by Luis Salcido
Modified: 2023-01-26 17:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

example error regression (11.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-01-25 21:17 UTC, Luis Salcido

Note You need to log in before you can comment on or make changes to this bug.
Description Luis Salcido 2023-01-25 21:16:38 UTC
It marks an error in the regression analysis of four variables, specifically when one of the values of the variable is equal to 2, any other value in this data does not cause an error. I mark it with an asterisk. Data:
Y	X2	X3	X4
4	*2	4	1
7	4	7	2
8	6	5	3
5	8	8	4
9	10	4	5
6	12	8	6
5	14	5	7

Steps to Reproduce:
1. Open Calc
2. Enter the data that I have shared with you in the comment, except do not include the asterisk.
3. go to Data/Statistics/Regression with the following options:

Select all independent variables (X) and their names.
select the dependent variable (E) and its name.
check the checkbox indicating that variables have labels.
select the cell where the results will be returned
select the check box grouped by columns
select the Linear Regression check box
the confidence level of 0.95
do not check the Calculate Residuals box.
Nor force intercept to zero

Actual Results:
results will appear with #¡VALOR!

Modelo de regresión	Lineal					
Salida bruta de ESTIMACION.LINEAL						
Estadísticas de regresión						
R²	#¡VALOR!					
Error estándar	#¡VALOR!					
Recuento de variables X	3					
Observaciones	7					
R² ajustado	#¡VALOR!					
Análisis de varianza (ANOVA)						
	df	SS	MS	F	Precisión F	
Regresión	3	#¡VALOR!	#¡VALOR!	#¡VALOR!	#¡VALOR!	
Residuo	#¡VALOR!	#¡VALOR!	#¡VALOR!			
Total	#¡VALOR!	#¡VALOR!				
Nivel de confianza	0.95					
	Coeficientes	Error estándar	Estadística t	Valor P	Inferior 95%	Superior 95%
Intersección	#¡VALOR!	#¡VALOR!	#¡VALOR!	#¡VALOR!	#¡VALOR!	#¡VALOR!

Expected Results:
would expect values to appear

Reproducible: Always

User Profile Reset: No

Additional Info:
[Information automatically included from LibreOffice]
Locale: es
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
Comment 1 Luis Salcido 2023-01-25 21:17:45 UTC
Created attachment 184913 [details]
example error regression
Comment 2 Stéphane Guillou (stragu) 2023-01-26 09:01:03 UTC
Thank you Luis!

I can see the same in:

Version: / LibreOffice Community
Build ID: 85569322deea74ec9134968a29af2df5663baa21
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded


Version: (X86_64) / LibreOffice Community
Build ID: e125e6623fa1c0f39d927bb37547ca6d1e299cb1
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Same thing in:

OpenOffice.org 3.3.0
OOO330m20 (Build:9567)

Trying to find some logic in it, I think it has to do with having two series with a skewness of 0. Changing any number in X2 or X4 will change the skewness and give results in the LINEST array.

MS Excel does *not* have the same issue.

Microsoft® Excel® for Microsoft 365 MSO (Version 2212 Build 16.0.15928.20196) 64-bit
Comment 3 Laurent Balland 2023-01-26 17:54:21 UTC
UI Regression dialog ask for independent X variables. As X2 = 2 * X4, your variables are not independent. X3 has no effect on the bug.