Here are the current (and previous) formula work areas for OpenFormula; see About_OpenFormula for more information about the project.
EVERYONE: By Wednesday, please let Wheeler know which section(s) you'd like to take on for the next week.
Due each Wednesday 7PM EST - what you've done so far in your section.
- Thomas Metcalf - HYPGEOMDIST
- Tomas Mecir - statistical (A-J)
- Frank Stecher - statistical (K-R)
- Thomas Metcalf -
- (Helen) Zhi Yu Yue - financial (N-Z)
- Wheeler - other
- Daniel Carrera - section 2.1 (small/medium/large), Information functions
- Stefan Nikolaus - array/matrix, financial (A-E), Financial (F-M), Statistical (S-Z), Lookup
- Inge Wallin - external access and date.
Next to assign: byte position.
The following people are accepting OpenDocument Fellowship funding: Carrera, Guelzow, Mecir, Nikolaus, Wheeler.
Old_assignments lists the old assignments.
In our current work process, we have a single draft document; people "own" various sections for a week. We then send the improved sections to Wheeler (dwheeler at dwheeler, dot com), who merges them and posts the updated version. This way, there's no need to worry about merge conflicts.
For the moment, just download the latest merged posting, edit it, and send back the whole document. Wheeler will then cut and paste the relevant sections, make changes to make the whole document "fit together", and then send out the new version. There's no point in turning on change tracking right now, because we already know who edits each section, and it would reveal that many changes are being made (well, we knew that). This process will probably change once all the undefined functions are defined.
If during work you have questions or concerns, please post to the mailing list, office-formula, at lists.oasis-open.org. Public posting is preferred, that way everyone can help at once.
Instructions for Defining Functions
When defining a function, you must follow the "Common Template for Functions and Operators" (section 6.1). This template requires that you include test cases; for more information about test cases, see Test Cases (section 2.3) and the Test Case Data Set (2.4). You can have more test case data added, just coordinate that with the chair. The Types chapter (4) tells you about the pseudotypes and automatic conversions. This isn't hard; use existing function definitions as examples.
Use many different sources for information, not just one source. You may directly copy material from the Lotus 1-2-3 v9.8 help documentation; otherwise, you must not copy due to copyright issues. OpenFormula.org's Research Data page points to data sources, be sure to use many of them.
The test cases are especially important. Formulas need to produce the right answer, and test cases greatly increases the likelihood of this.
Hints (especially about test cases):
Always include at least one test case, but please include many test cases unless it is TRULY obvious. Don't be shy, when in doubt, add test cases. Include test cases for "normal" uses, out-of-bounds data, fractions, negative numbers, different types, etc. For fractions, check if rounding is to the nearest integer or straight truncation; check if it rounds towards 0, positive infinity, or negative infinity. If someone might have a question, or there might be multiple ways of doing it, add a test case - they're easy to add, and we can remove problem ones later. We can easily detect problems with test cases; we cannot detect test cases we don't have
Many numeric results cannot be represented exactly. Where it's convenient to have an exact answer, you should probably have a test case or two that has an exact answer (so that we can tell that it gets exact answers where convenient). But when there is not an exact answer, be sure to include the +/- marker and the error range (usually epsilon). Epsilon is 0.000001, so if you use it, you have to include an answer correct to at least 6 figures after the decimal point. Please include extra digits of the correct answer, at least 9 digits, in case we make epsilon even smaller in the future. The point of all this is to make it easy to automatically test a spreadsheet for conformance; we want to make sure that we identify all real differences, without getting told about non-issues. OOO users: beware, the default is fewer digits, so you'll need to reformat cells to show more digits.
Use a spreadsheet to create the test cases and expected results - then you can copy/paste what you want. If using OpenOffice.org, be sure to select a format that shows lots of digits. Do not assume that any one application is always right. There's at least one study showing that Gnumeric is generally more accurate than Excel; see Spreadsheet_Bugs for more. Note that when we include the expected results, we can easily examine the results of many different applications and detect when there's a difference; we can then determine the correct result and use that. Where possible, try to use alternative methods to ensure that the correct values really are correct.
Try to make test cases test "only what you're testing". Don't include inline arrays - use cell references. Limit any other functions to those that will certainly be there if the function you're testing is there. Depending on most functions in "Small" is typically fine, as is basic related query functions (e.g., any application implementing complex numbers will have IMREAL and IMAGINARY).
- Avoid common mistakes in formula expressions. Begin formulas with "=", use TRUE() not True, FALSE() not FALSE, use semicolon as the function separator, use brackets and "." in cell references
- Use straight quotes, not curly quotes
Remember to include the types, and note that the types defined as input parameters control automatic conversion. The Number type does not convert text to number in all applications (OpenOffice.org does that with inline text, but NOT text referenced through a cell reference, and Lotus 1-2-3 never does it). If you want to say that a particular function always accepts Text or Number, even through a reference (as many date functions do), use a different type. Use Integer if you expect an integer, and define how the integer is determined.... and be sure to test for the various possible conversions so that you can be sure that the application does the right one.
Try to spec anything that is widely agreed on. A good rule of thumb is that if both Excel and OpenOffice.org do it the same way, require it. If one is obviously wrong, boldly require the correct answer, and make sure there are test cases for it. In general, try to avoid creating "committee inventions"; our goal is to define the notional "Spreadsheet" language as it (portably) exists, not to create a new, incompatible language
If there's an obvious extension (e.g., applications only support a limited domain, but an application might support more), consider making the extension implementation-defined, and note it. You can do this by noting "Portable Constraints". A common example of such a constraint is INT(X)=X... that means that applications will often only accept integers, but an application might accept non-integers and do something different. Zero, negative numbers, complex numbers, empty strings, and so on are other plausible examples.
Include tests for known Spreadsheet_Bugs, with the correct answers, so that we can quickly detect and counter known problems.
- To be complete, the test cases need to be right. Be sure to generate a test case spreadsheet and try it out; if it fails, document why, and make sure it fails for the right reason (e.g., make sure there are no syntax errors in your test!).
The goal is a spec that all applications can implement, and current documents can transition to. In a few cases we have functions unique to a particular application, to ease transition from that application's native format into the common format.
Who's done what?
Since Aug. 23, volunteers not in a spreadsheet-implementing company have done these:
- Andreas J. Guelzow
- Tomas Mecir
- * AVERAGEA, B, BINOMDIST, CONFIDENCE, COVAR, DEVSQ, GEOMEAN
- Thomas Metcalf - HYPGEOMDIST, DB, GROWTH
- Wheeler - bit operations, byte position operations, TYPE, ROW, COLUMN, ISOWEEKNUM, DDE, FVSCHEDULE, MDURATION, MIRR, RRI, FORMULA, SHEET, SHEETS, ADDRESS, FTEST, GAUSS, INFO, INDIRECT, CONVERT, EUROCONVERT, LOOKUP
- Daniel Carrera
- Stefan Nikolaus
- * Array: MDETERM, MINVERSE, MMULT, MUNIT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, TRANSPOSE
- * Financial: AMORDEGRC, AMORLINC, COUPNCD, COUPNUM, COUPPCD, CUMIPMT, CUMPRINC, DISC, DOLLARDE, DOLLARFR, DURATION, EFFECT
- Inge Wallin
Plus the work done by those at various companies. See the end of the document for a complete list of contributors.
Sources of Information and Tools
Here are some external locations that may help you:
Daniel Carrera's Test_Suite_Generator (a tool for generating test suites from the specification)
Formulas is the main page of the Formula SC.