Re: Example of spreadsheet formula testing

[Rob Weir]

On Mon, Aug 19, 2013 at 7:47 AM, janI j...@apache.org wrote:
 On 19 August 2013 13:28, Rob Weir robw...@apache.org wrote:

 On Mon, Aug 19, 2013 at 6:53 AM, Regina Henschel
 rb.hensc...@t-online.de wrote:
  Hi Rob,
 
  Rob Weir schrieb:
 
  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.
 
 
  Reading ODF1.2 specifications will not help for all functions. For
 example
  the specification of the Bessel-functions rely on the fact, that
 interested
  readers will find the definition other where, using the function names.
 [I
  know you will say, write an issue and make a proposal ;)]
 

 That's fine.  You rely on other sources that are more trusted than the
 standard or the implementations.  Tables of special functions, for
 example, can be found in books like Abramowicz and Steugen.  Also, the
 NIST's newer Digital Library of Mathematical Functions, which has a
 nice table of software applications that implement each function:

 http://dlmf.nist.gov/software/

 The main point is that we should not rely on a self-referential
 definition of a function, automatically taking AOO behavior as the
 correct behavior.  And I would not trust Excel in all cases, since
 there is ample published criticism of some of their numeric
 algorithms.  So I'd rely more on specialized software.


 We have a good saying from the the viking times, you need to bake your
 bread, before you can eat it.

 Before we get out on a very long math trail (which of course is correct),
 lets not loose the target.

 If we just had one or more spreadsheets, that could check that all
 functions worked like the did yesterday (regression testing), we would have
 taken a giant step towards automated testing and at the same made better
 quality and saved tester time to more special problems

 So in short, lets focus on testing what we have. Assuming it works today is
 to me a good assumption, and in the few cases where it turns out that it
 does not, we simply adapt the test spreadsheet. This approach would have
 the nice benefit, that we can tell our users exactly which functions have
 changed in a release.


As mentioned before, if we make test spreadsheets per the example I
gave earlier then you automatically record the current behavior of
AOO.   It requires no extra work.  And you also validate that this is
the correct value, which is worth doing as well, though that part does
require more work.  But in either case a large portion of the work is
designing the test so you pick the right input values to fully test
the function's behavior, e.g., error cases and other edge values.
That is the core essential work, regardless of what kind of automation
we use or don't use.  That is where the real mental work occurs.

-Rob



 rgds
 jan I.




 Regards,

 -Rob


  Kind regards
  Regina
 
 
  -
  To unsubscribe, e-mail: qa-unsubscr...@openoffice.apache.org
  For additional commands, e-mail: qa-h...@openoffice.apache.org
 

 -
 To unsubscribe, e-mail: qa-unsubscr...@openoffice.apache.org
 For additional commands, e-mail: qa-h...@openoffice.apache.org



-
To unsubscribe, e-mail: qa-unsubscr...@openoffice.apache.org
For additional commands, e-mail: qa-h...@openoffice.apache.org

Example of spreadsheet formula testing

[Rob Weir]

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:

http://www.robweir.com/basis-test.xls

(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.

-Rob

-
To unsubscribe, e-mail: qa-unsubscr...@openoffice.apache.org
For additional commands, e-mail: qa-h...@openoffice.apache.org