Re: [office-formula] Suggestions if you just can't get enough precise infoon a function

[Zhi Yu Yue <yuezhiyu@cn.ibm.com>]

Hi David,

I suggest we have someone to review
and check the formula definitions after the author completes (at least
if the author is not so sure about) them. Review can improve the quality
much. Not sure if others have such requirement. In my case, I need someone
to review several finance functions although I have spent a lot of time
to refine them but still not quite sure if these test cases are really
meaningful and perfect.

Best Regards,
Helen Yue

"David A. Wheeler" <dwheeler@dwheeler.com>

10/12/2006 06:15 AM

Please respond to
dwheeler@dwheeler.com

To	office-formula@lists.oasis-open.org
cc
Subject	[office-formula] Suggestions if you just can't get enough precise info on a function

I've been asked what people should do if they don't
know of
a formula (or algorithm) to precisely define a function.  Here are
some suggestions
if you're stuck in that circumstance; if anyone has further suggestions,
please post them here!

First, look at documentation from various spreadsheet implementations.
You can find pointers here: http://www.openformula.org/Research_Data
For financial formulas, check your local library for books on business
calculations (there are several at my local library).  For math and
statistics, CRC's books have a lot of valuable info.

Worse comes to worse:
1. Give a simple English description (even if it's
not as precise as you'd like), based on extant documentation.
2. Then create several test cases that try to stress different kinds of
data sets.
   E.G., for functions that compute trends, I'd include both an even
and odd set of data.
   A large number of different test cases, covering various parameters,
is even
   more imporant when you have a hard time getting details.
3. Send in what you have.
4. Most important: Post an announcement on office-formula,
   saying that you have a starting point but you would like help on
making them more
   rigorous, telling us specifically which functions you need help
in.
   The simple English description and the test cases will help us
work together
   to make sure that we move in the right direction.

We can look at code, but the danger is that it may not be correct!
E.G., Microsoft's RAND() function occasionally produces negative numbers,
even though it shouldn't.  In any case we want a particular effect...
not
a particular algorithm. We're better off creating a test cases, and THEN
going to
code if we need to. That way, we'll be able to tell if that code is getting
the same
answers as other code, so that we're not led astray by any one implementation.

I did this with YEARFRAC, for example - I couldn't find any details, so
I
created high-level English description and a whole bunch of test cases.
The test cases revealed all sorts of info!  I then posted the information
I'd gathered,
and asked for help on the office-formula mailing list.
Rob Weir then came up with a detailed spec for various methods... and the
test cases enabled us to verify exactly which method was intended.

By combining the knowledge of many, and using test cases to check correctness,
we should come up with good results.  An accurate description, even
if it
lacks detail, is still helpful as a starting point.

Hope that helps.

--- David A. Wheeler