OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

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

[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

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]