Calc unit tests

Winfried Donkers W.Donkers at dci-electronics.nl
Thu Mar 23 07:12:38 UTC 2017


Hi Markus,

>> I'm working on fixes for Calc functions (LEGACY.)CHITEST and CHISQ.TEST 
>> that came forward when Raal compared results between Calc and Excel.
>> When compiling my changes I came across unit test functions in 
>> sc/qa/unit/ucalc_formula.cxx, as these failed because of my changes.
>> They tested the current behaviour, not the ODFF1.2 specs.

> So for now I suppose the best way forward is to just adapt the existing tests to conform to your improved implementation.

No problem, will do.

That brings me to the next issue: ODFF1.2 interpretation and Excel interoperability.
ODFF1.2 states for LEGACY.CHITEST that "For an empty element or an element of type Text or Boolean in A the element at the corresponding position of E is ignored, and vice versa.". This was not the case and I am fixing that.
Excel ignores empty elements AND retains the value for the number of rows and columns of the ranges, i.e. a range of 3x3 and a range of 3x4 (same value plus an empty row somewhere) produce different results. I can't see any mathematical reason in retaining the number of rows and columns of the ranges in case of empty elements, but it very much simplifies the calculation and with my fix Calc reproduces Excel's behaviour.

But what if ALL elements are empty? All ignored produces a Xi^2 of 1, i.e. a function result of 1. But Excel returns an error (#DIV/0!, even) and that I cannot explain or defend.
IMHO Calc should return 1 or return IllegalArgumentError plus corresponding explanation in help/ODFF1.2 that the ranges must have at least 1 data pair (i.e. a change request to add a constraint to ODF1.2 for LEGACY.CHITEST). That way we block a useless use case and retain interoperability with Excel (even though the error type differs).
I would like to hear your opinion in this, as it seems we have to choose between a mathematic approach/ODF1.2 and Excel's behaviour.

(And Boolean is a type I cannot distinguish from numeric, so that part of ODFF1.2 I have to ignore.)

Winfried


More information about the LibreOffice mailing list