The Formula Issue in Detail.

The concerns that ISO 26300 does not support Spreadsheet Formulas was a peculiar one. This commentary will show where this misunderstanding originates from, and will hope to allay the fears. It will also go in technical detail in the two examples brought about from the Public Comment and TC4 presentation. Ultimately this commentary will dispel the myth that ISO 26300 does not support Spreadsheet Formulas.

We should take interest in this issue as it is the most significant Technical complaint against ISO 26300 and at the end of this technical  commentary, agree that the concerns can be allayed due to misunderstandings of the concerned parties, and we can continue in adopting ISO 26300 as a Malaysian Standard.

The basic concern that “there are no formulas supported by ISO 26300.”

No specific technical reason was given by the majority of concerned public comments. The general statements were “We also understand that the standard does not as yet fully support some key features which are important to us, namely embedded formulas” or “ODF provides adequate support for basic user, but has some severe technical limitations for novice and expert users. For example, ODF does not support spreadsheet formula”

Somehow these users and developers have the impression that ISO 26300 will not support basic spreadsheet formula like '=SUM( A1: A7 )' which is the sum of the cells A1 to A7. This is strange because the first ever spreadsheet had this feature and its the minimum requirement to make a spreadsheet useful.

There are many users of ODF enabled spreadsheets over many years, and they have no problems with spreadsheet formulae and other complex features. This is also confirmed by the many public  comments which support the standardization of ISO 26300 as a Malaysian Standard.

As such the public concerns of this nature is due to misinformation and misunderstanding by users who have not used alternative software suites and this can easily be allayed with education or a simple demonstration of the productivity suites which offer ODF support.

The specific concern that “there is no formula specification is defined in ISO 26300”

The source of the prior misunderstanding becomes clearer with the more detailed complaint highlighted from the public comment and a vendor's presentation at the TC4 meeting. The specific concern was: “Lack of support for spreadsheet formula language specification will cause interoperability problems between ODF enabled applications”

The examples shown were illuminating:

A Developer showed that a formula in a ISO 26300 spreadsheet would be SUM(A1...A3). Their assertion is that: “since the ODF does not specify a format or syntax for representing the formula itself I could easily have said that the formula at C1 could be C1:SUM[A1 to A3] or any other syntax I should choose. ... In conclusion, the ODF does not specify the format or syntax of a spreadsheet formula, allowing different vendors to specify their own syntax rendering incompatibilities between spreadsheet programs using the ODF format.”

The Vendor's example showed two applications OpenOffice.org and KOffice attempting to exchange spreadsheets. The claim was that OOo has the format:

oooc:=SUM([.A1];[.A3];CEILING([.A4];3;2)) + LOG([.A1]+10;10) – SQRT([.A4])

and this becomes incompatible with KOffice, because KOffice only recognizes:

SUM(A1;A3;CEILING(A4)) + LOG(A1+10)- SQRT(A4) 

The Vendor then goes on to review the OASIS specification for ODF v1.0 at Section 6.7.6  on Formula which indicated that formulas are just 'strings' or 'free text'. With this they asserted that:

1. Both OpenOffice and KOffice are ODF compliant
2. Both cannot read each other's files just for one simple example
3. There could be  more problems – and we need to understand this in more detail and study the impact

Both conclusions of the two commentators are wrong. They were mislead during their evaluation of the ISO 26300 specifications. The hint to where to look for this definition was within the XML tags included in their case studies, indicates:

   <table: table-cell table: formula= ...>

They both did not find the correct specification for Spreadsheet formula. They should be looking at Section 8.1.3 which is the definition of a Table Cell and Formula.

Here is an extract of the freely available open standard ISO 26300:

8.1.3 Table Cell

 :

Formula

Formulas allow calculations to be performed within table cells. Every formula should begin with a namespace prefix specifying the syntax and semantics used within the formula. Typically, the formula itself begins with an equal (=) sign and can include the following components:

• Numbers.

• Text.

• Named ranges.

• Operators.

• Logical operators.

• Function calls.

• Addresses of cells that contain numbers. The addresses can be relative or absolute, see section 8.3.1. Addresses in formulas start with a “[“ and end with a “]”. See sections 8.3.1 and 8.3.1 for information about how to address a cell or cell range.

The following is an example of a simple formula:

=sum([.A1:.A5])

This formula calculates the sum of the values of all cells in the range “.A1:.A5”. The function is “sum”. The parameters are marked by a “(“ at the start and a “)” at the end. If a function contains more than one parameter, the parameters are separated by a “;”. The following is a variation of the formula shown above:

=sum([.A1];[.A2];[.A3];[.A4];[.A5])

The result of this formula is the same. The components used in the formula depend on the application being used.

The table:formula attribute contains a formula for a table cell.

 

So at section 8.1.3 in ISO 26300 standard, all the assertions by the two concerned commentators that Formula Specification is not defined in the standard, is false. The reason is that the 8.1.3 Section clearly states the correct use of namespaces, special characters like “[ ]” for Addresses, “( )” for Parameter start/end points and “;” for parameter separation, to rigidly define the formula specification.

This should be enough to show that the examples and subsequent conclusions provided by the two commentators were flawed as it was a simple misunderstanding of the specifications. We can conclude now that ISO 26300 does clearly specify a standard in describing formula specification, and the specification is not flawed in any way. As such we can continue to adopt ISO 26300 as a Malaysian Standard.

However to satisfy our intellectual curiosity, the Vendor's example begs the question: “Why wasn't OpenOffice.org and KOffice interoperable?”

The example picked up by the Vendor is based on a relatively old (20^th September 2005) newsforge.com article: “OpenDocument office suites lack formula compatibility”

The article compares OpenOffice.org v1.9.117 and KOffice v1.4. As of today, the current updated versions have evolved to v2.0.4 and v1.6 respectively.

So, the reason why they were not interoperable in the article, is very simple. ODF is based on OpenOffice.org base format, and at that point in time, OpenOffice.org provided the best example of an ODF v1.0 implementation.  We must also understand that KOffice, before the OASIS initiative, had its own competing file format, and only recently turned to ODF as its default format. KOffice's support for ODF is still under development. This transition should conclude by version 2.0 when it will be fully compliant to ISO 26300. That is why, when inspecting the formula from KOffice v1.4 reveals that it does not conform to the ODF specifications in that the Address Usage “ [ . ] ” and namespaces was not used.

It is not uncommon for an application to pledge support for a standard and slowly work towards meeting all the requirements of that standard. Take for example Microsoft Office and its support for ISO 26300 with the Add-In. Currently interoperability is lacking, not because of the standard, but because of the current implementation. Eventually however, given time, as the implementation improves and converges, interoperability is guaranteed BECAUSE of the predefined mature standard.

To conclude I hope that the examples provided by the two concerned commentators has been educational in the difference between a Standard (ISO 26300) and the Implementation of a standard (KOffice and Microsoft Office).

Again, I would like to reiterate that ISO 26300 has stood its technical trials on an international level as it should do the same on a Local level, and as such I would fully recommend Malaysia to adopt this mature standard as a National Standard to increase the interoperability between the many office suites to provide more choice and competition for us users.

yk.