openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Inge Wallin <>
Subject Re: Example of spreadsheet formula testing
Date Thu, 01 Jan 1970 00:00:00 GMT
On Friday, August 16, 2013 13:32:30 Rob Weir wrote:
> Moving this topic to its own thread.
> It should be possible to code a very thorough set of test cases in a
> spreadsheet, without using macros or anything fancy.  Just careful
> reading of the ODF 1.2 specification and simple spreadsheet logic.
> I'd like to share an example of this that I created for one of the ODF
> Plugfests.  This is a test of a single function -- YEARFRAC.  You
> probably have never touched this function, but it exhibits all the
> pathological behavior, in a purer form, of the other financial
> functions.  Specifically, it is a pure test of our "date counting
> conventions", the various ways that accountants handle date
> calculations.
> The test document is here:
> (I did it in XLS format since I wanted to make sure Microsoft could
> use it at the Plugfest as well.  At that time they were not able to
> read ODF formulas.)
> This is likely the most complicated set of test cases of any
> spreadsheet formula.  So if we can test YEARFRAC this way then we can
> test any function this way.
> Column C is the formula to evaluate.  Column F is the expected value,
> which is calculated by hand, according to the ODF standard.  And
> colu,mn G reports whether they match or not.  (This would be a good
> place for us to use conditional formatting as well, though in the
> Plugfest case I needed to make the spreadsheet be as vanilla as
> possible so every editor could load it)
> Note that this is an exhaustive set of test cases that aim to test
> every corner of the formula.  It is a torture test.  Excel gets all
> the test cases right.  Not a surprise, since we took Excel's behavior
> as normative when writing this part of the standard.

Good test!

So how is AOO doing?

Just FYI, Calligra Sheets nails the test. :)


> If we used an approach like this on the other spreadsheet functions,
> we could have a semi-automated test suite that would practically
> guarantee that Calc is free of calculations errors.  Once we're
> written the test cases, a modest upfront investment, it will benefit
> us with every release we do.  Heck, it would benefit LibreOffice,
> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
> they might already have such test cases defined internally.
> Anyone interesting in helping with this kind of test case development?
> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
> we're not starting from a  perfect score.  But we should find an easy
> way to report on regressions.
> -Rob
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:
  • Unnamed multipart/alternative (inline, 7-Bit, 0 bytes)
View raw message