openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rob Weir <>
Subject Example of spreadsheet formula testing
Date Fri, 16 Aug 2013 17:32:30 GMT
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

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.

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.


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message