• About_OpenFormula

The following information is to help orient new members of the Formula SC.

OpenFormula is the informal name of the OpenDocument formula specification. The OpenFormula draft specification describes how to exchange recalculated formulas between any two applications (primarily spreadsheet programs).

Draft Formula SC Guidelines

• Note the scope.OpenFormula covers only recalculated formulas (such as those for spreadsheets). For the display of arbitrary mathematical expressions, OpenDocument uses MathML, the W3C standard for displayed mathematical expressions in XML-based documents. MathML and OpenFormula are complementary standards, for example, the OpenFormula specification uses MathML to describe functions.

• Take into account the features and behaviors of many different implementors. Although we do not have the explicit goal to mimic the behavior of any single legacy spreadsheet implementation, we should be broadly aware of the state of spreadsheets today and specify consensus behavior where it exists today and where it is not blatantly wrong. The TC has participation from spreadsheet implementors, including OpenOffice.org StarOffice (Eike Rathke), KDE KOffice (David Faure), Gnumeric (Dr. Andreas J. Guelzow), IBM/Lotus Symphony (Rob Weir), and Microsoft Excel (Eric Patterson). Seek out their assistance if you need to understand better how a particular implementation works.

• Seek out domain experts whenever possible. We can tap into a wider circle of reviewer experts in statistics, engineering, finance and other disciplines. If you need confirmation of a function definition, raise the issue to the list and we can help track this down.

• Look at analogous standards. In particular note the specification of functions in the W3C's XPath REC and the library functions defined in ISO C++. These give a model for the definition of expression language syntax and function semantics.

• Consider tomorrow, not just today. Think beyond current implementation limitations. Ensure that the syntax works into the foreseeable future. For example, allows an arbitrary number of columns, while also allowing arbitrary names of values. Every few years applications start widening the number of allowed columns, yet many formats are not designed to handle this predicable change. Supports arbitrary names of named expressions and sheets, including international characters, so users can use names that are natural for them, no matter what their language.

• Embed test cases. Specifications often give a few examples, hidden in the body of the text. In OpenFormula we should include a large number of test cases, ones that test and demonstrate the specification including "edge cases" that people often forget. Additionally, we should do this in a structured way, so that we can automatically extract them from the specification and create a spreadsheet that tests applications. This gives us a self-testing specification.

• Write rigorous definitions. The test cases help us be far more rigorous. In addition, we need to define the types for each function (as prototypes of each function). Spreadsheet functions are very sensitive to the types of their parameters. We must clearly identify the type of every parameter and the type of the result. We need to delve in deep into function definitions, making sure they are defined sufficiently for independent implementations to correctly interoperate. For example YEARFRAC() has subtle behavior in the leap years, with different behaviors required by various financial bodies (AFB, ISMA, ISDA). We do the research and define the exact behavior, where other specifications leave the details unspecified. And in cases like this we include a large number of test cases, so that users can be confident that applications will get it right.

• Doesn't mandate mistakes. Just because one program gets something wrong doesn't mean that everyone should make the same mistake. The specification is carefully written to not require certain bugs, just because someone has a bug. For example, one existing spreadsheet incorrectly believes that 1900 was a leap year, and does not allow dates before 1900. Instead, ODF stores dates as dates (not just numbers), and thus does not require that applications have this leap year bug. Similarly, many applications handle complex numbers in a very clumsy way; we've devised the specification to make sure that future applications can support better approaches, instead of encoding a dependency on suboptimal approaches. If we constantly compare many different independent implementations, it will be easy for us to detect when an application makes a mistake.

• Internationalization. We need to ensure that OpenFormula adapts to linguistic and cultural practices from around the world. For example, not everyone uses the "." as the decimal point. Similarly, named expressions can have names in local character sets.