https://bugs.documentfoundation.org/show_bug.cgi?id=106796
Bug ID: 106796
Summary: function CHITEST and CHISQ.TEST - empty cells
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
> see row 9 - excel returns value, Calc returns error.
>
Winfried:
CHISQ.TEST (Excel) and CHITEST (Excel and LEGACY.CHITEST in ODFF1.2) are
identical, so any changes to the code will apply to both functions.
In row 9 you use ranges L4:L10 and M4:M10, but the data is in L3:M9: Excel
handles empty cells differently than Calc does.
In Excel the data ranges have 6 rows.
In Calc 7 rows and the last row contains no data (Error502, illegal argument).
I did some tests and Excel reduces the range when any of the two ranges has one
or more empty cells.
So range (_ being an empty cell)
A E
1 1
_ _
2 2
3 _
4 4
_ 5
_ _
is treated in Excel as a 3-row range. In Calc this produces an error, because
not all 7 rows have valid data.
Note that 0 in the range for expected values is not allowed because of the
algorithm (DIV/0), so empty cells are treated as empty, not as 0.
Now the difficult part: is it a bug and if so, Excel's or Calc's or both?
1. The skipping of empty rows (in both ranges) seems consistent to me (with
e.g. SUM it is common practice to enter a range which is much bigger than the
amount of values to be added, so that values added later will be incorporated
in SUM automatically). IMO this applies for both empty rows at the end of the
range as for empty rows in between, simply to be consistent with other
functions using ranges. That would mean there is a bug in Calc to be fixed.
2. The skipping of rows when only one of the two is missing is illegal as far
as I am concerned. Excel says "If actual_range and expected_range have a
different number of data points, CHISQ.TEST returns the #N/A error value." and
ODFF has constraints "ROWS(A) == ROWS(E)" and "COLUMNS(A) == COLUMNS(E)". So
this looks like a bug in Excel and behaviour that should _not_ be copied into
Calc.
As the ODFF text for LEGACY.CHITEST looks copied from Excel (with respect to
the algorithms) I propose we fix Calc to handle empty rows -when occurring
simultaneously in both ranges- just like Excel, i.e. as non-existent rows.
Steps to Reproduce:
=CHISQ.TEST(L4:L10,M4:M10) but the data are only in L4:L9 and M4:M9
Actual Results:
error
Expected Results:
-
Reproducible: Always
User Profile Reset: No
Additional Info:
User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101
Firefox/52.0
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
