Goals/levels/packaging/complex numbers

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

(From here on, let's reply to THIS message on goals/levels/packaging
and complex numbers, so that it'll be easier to follow the email threads).

I originally posted:
> * Issue: Goals/range.  Rob Weir and David A. Wheeler both want it to support both limited resources (e.g., tiny PDAs) and massive capabilities.  Yet if everything is optional, no one will know what is supported, and users won't have real interoperability.  A solution for this problem is to define "levels" (OpenFormula did this).  Do we agree that we should have multiple levels?
>   

Daniel Carrera thirded (?) the notion of supporting 
PDAs/restricted-resource implementations.
I should note that the restricted resource might be developer time; the 
spreadsheet may be an
incidental capability of something ELSE, and they only want to implement 
"enough" to satisfy
a subset of all possible users.

> ** If we have levels, must define the levels.  Discuss briefly whatwe  want, what are the levels.  Can we try to create rough resolution by March 17 on the levels, if we agree to define levels? (OpenFormula had four levels; we don't need to have 4, or use its breakdown, unless we like it.)
>   
...
> * Complex Numbers.  We don't have time to go into it at the beginning of this process, but although many implementations support complex numbers, their support is really HORRIBLE to users.  Later on we'll need to determine what we should do about them.
>   

And there are LOTS of comments about this.
Let me first describe the OpenFormula experience with levels,
then respond to some specific points.

OpenFormula defined 4 levels. Most of our time was spent on "level 1"
(the minimal set) and all of the framework necessary to define level 1
(what are types, type conversions, etc.), though.

Level 1 was the minimal level support for limited resource environments.
We determined this by finding out what Palm spreadsheet 
(SpreadsheetToGo) _actually_ supported,
and removed what wasn't supported by some other typical implementation 
(Excel, OpenOffice.org,
Gnumeric, and later Lotus 1-2-3v9 and Quattro Pro).  This turned out to 
NOT be very limiting
at all; it has 109 functions including database (DSUM, etc.), trig (SIN, 
COS),
financial (IRR, PV), and so on.  We could certainly have defined an even 
smaller set.
The negative of a large level 1 is that it's more work for implementors; 
the positive of a large
level 1 is that it means that users can do a LOT and still be interoperable.

Here's the complete list of OpenFormula level 1 functions (all 109): ABS 
<http://www.openformula.org/ABS> ACOS <http://www.openformula.org/ACOS> 
AND <http://www.openformula.org/AND> ASIN 
<http://www.openformula.org/ASIN> ATAN <http://www.openformula.org/ATAN> 
ATAN2 <http://www.openformula.org/ATAN2> AVERAGE 
<http://www.openformula.org/AVERAGE> CHOOSE 
<http://www.openformula.org/CHOOSE> COLUMNS 
<http://www.openformula.org/COLUMNS> COS 
<http://www.openformula.org/COS> COUNT 
<http://www.openformula.org/COUNT> COUNTA 
<http://www.openformula.org/COUNTA> COUNTBLANK 
<http://www.openformula.org/COUNTBLANK> COUNTIF 
<http://www.openformula.org/COUNTIF> DATE 
<http://www.openformula.org/DATE> DAVERAGE 
<http://www.openformula.org/DAVERAGE> DAY 
<http://www.openformula.org/DAY> DCOUNT 
<http://www.openformula.org/DCOUNT> DCOUNTA 
<http://www.openformula.org/DCOUNTA> DDB 
<http://www.openformula.org/DDB> DEGREES 
<http://www.openformula.org/DEGREES> DGET 
<http://www.openformula.org/DGET> DMAX <http://www.openformula.org/DMAX> 
DMIN <http://www.openformula.org/DMIN> DPRODUCT 
<http://www.openformula.org/DPRODUCT> DSTDEV 
<http://www.openformula.org/DSTDEV> DSTDEVP 
<http://www.openformula.org/DSTDEVP> DSUM 
<http://www.openformula.org/DSUM> DVAR <http://www.openformula.org/DVAR> 
DVARP <http://www.openformula.org/DVARP> EVEN 
<http://www.openformula.org/EVEN> EXACT 
<http://www.openformula.org/EXACT> EXP <http://www.openformula.org/EXP> 
FACT <http://www.openformula.org/FACT> FALSE 
<http://www.openformula.org/FALSE> FIND 
<http://www.openformula.org/FIND> FV <http://www.openformula.org/FV> 
HLOOKUP <http://www.openformula.org/HLOOKUP> HOUR 
<http://www.openformula.org/HOUR> IF <http://www.openformula.org/IF> 
INDEX <http://www.openformula.org/INDEX> INT 
<http://www.openformula.org/INT> IRR <http://www.openformula.org/IRR> 
ISBLANK <http://www.openformula.org/ISBLANK> ISERR 
<http://www.openformula.org/ISERR> ISERROR 
<http://www.openformula.org/ISERROR> ISLOGICAL 
<http://www.openformula.org/ISLOGICAL> ISNA 
<http://www.openformula.org/ISNA> ISNONTEXT 
<http://www.openformula.org/ISNONTEXT> ISNUMBER 
<http://www.openformula.org/ISNUMBER> ISTEXT 
<http://www.openformula.org/ISTEXT> LEFT 
<http://www.openformula.org/LEFT> LEN <http://www.openformula.org/LEN> 
LN <http://www.openformula.org/LN> LOG <http://www.openformula.org/LOG> 
LOG10 <http://www.openformula.org/LOG10> LOWER 
<http://www.openformula.org/LOWER> MATCH 
<http://www.openformula.org/MATCH> MAX <http://www.openformula.org/MAX> 
MID <http://www.openformula.org/MID> MIN 
<http://www.openformula.org/MIN> MINUTE 
<http://www.openformula.org/MINUTE> MOD <http://www.openformula.org/MOD> 
MONTH <http://www.openformula.org/MONTH> N 
<http://www.openformula.org/N> NA <http://www.openformula.org/NA> NOT 
<http://www.openformula.org/NOT> NOW <http://www.openformula.org/NOW> 
NPER <http://www.openformula.org/NPER> NPV 
<http://www.openformula.org/NPV> ODD <http://www.openformula.org/ODD> OR 
<http://www.openformula.org/OR> PI <http://www.openformula.org/PI> PMT 
<http://www.openformula.org/PMT> POWER 
<http://www.openformula.org/POWER> PRODUCT 
<http://www.openformula.org/PRODUCT> PROPER 
<http://www.openformula.org/PROPER> PV <http://www.openformula.org/PV> 
RADIANS <http://www.openformula.org/RADIANS> RATE 
<http://www.openformula.org/RATE> REPLACE 
<http://www.openformula.org/REPLACE> REPT 
<http://www.openformula.org/REPT> RIGHT 
<http://www.openformula.org/RIGHT> ROUND 
<http://www.openformula.org/ROUND> ROWS 
<http://www.openformula.org/ROWS> SECOND 
<http://www.openformula.org/SECOND> SIN <http://www.openformula.org/SIN> 
SLN <http://www.openformula.org/SLN> SQRT 
<http://www.openformula.org/SQRT> STDEV 
<http://www.openformula.org/STDEV> STDEVP 
<http://www.openformula.org/STDEVP> SUBSTITUTE 
<http://www.openformula.org/SUBSTITUTE> SUM 
<http://www.openformula.org/SUM> SUMIF 
<http://www.openformula.org/SUMIF> SYD <http://www.openformula.org/SYD> 
T <http://www.openformula.org/T> TAN <http://www.openformula.org/TAN> 
TIME <http://www.openformula.org/TIME> TODAY 
<http://www.openformula.org/TODAY> TRIM 
<http://www.openformula.org/TRIM> TRUE <http://www.openformula.org/TRUE> 
TRUNC <http://www.openformula.org/TRUNC> UPPER 
<http://www.openformula.org/UPPER> VALUE 
<http://www.openformula.org/VALUE> VAR <http://www.openformula.org/VAR> 
VARP <http://www.openformula.org/VARP> VLOOKUP 
<http://www.openformula.org/VLOOKUP> WEEKDAY 
<http://www.openformula.org/WEEKDAY> YEAR <http://www.openformula.org/YEAR>


Here are the current set of OpenFormula levels (they changed during it 
development,
and could easily change again, but they give a flavor of the idea):

* Level 1: Minimum implementation. This level provides the minimal 
capabilities (including functions, types, and their meanings) that are 
very widely implemented with spreadsheet applications, even in 
resource-contrained environments. It includes around 100 functions.
* Level 2: Minimum desktop implementation. This level provides the 
minimal capabilities expected from typical desktop spreadsheets. Many 
current desktop implementations meet or nearly meet this level. Level 2 
was calculated as a "majority intersection" of the capabilities in 
common desktop spreadsheet programs (e.g., /most/ spreadsheet 
implementations for the desktop provide those capabilities), considering 
implementations such as Microsoft Excel 2003, Corel Word Perfect Office 
12, IBM/Lotus 1-2-3 version 9.8, OpenOffice.org Calc, and Gnumeric.
* Level 3: Basic implementation. This level provides all the 
capabilities necessary for typical desktop spreadsheet use. Many current 
implementations meet or nearly meet this level. This adds a 
distinguished logical type and support for complex numbers. The 
intersection of Excel and Gnumeric were used as models here.
* Level 4: Full implementation. This level provides some additional, 
less-commonly used features intended for advanced users.  This 
approached a union; we didn't do much work here.

Here's the conformance text of OpenFormula: "A conforming application 
/MUST/ report the highest level it conforms to, if it claims conformance 
to this specification. Unless stated otherwise, all requirements of this 
specification apply to any application that claims to meet the 
OpenFormula specification. In particular, all conforming applications 
/MUST/ meet the level 1 requirements to claim conformance to this 
specification. Applications /MAY/ accept extensions as long as they do 
not interfere with processing data in the format defined here. In 
particular, applications /MAY/ support additional formulas and 
operations, additional optional parameters for functions, or make 
certain function parameters optional when they are required by this 
specification. It is /RECOMMENDED/ that applications clearly document 
their extensions in all user documentation, both online and paper, in a 
manner that users would be very likely to be aware when they are using a 
non-standard extension. Applications /MAY/ partly implement a higher 
level of the specification."


The specific details of the levels aren't deeply embedded in the 
OpenFormula spec, and in fact, they changed over time in OpenFormula.

Okay, now for some comments.  Daniel Carrera asked:
 > I sure can't see any other option. I do have a question. Based on the 
OpenFormula experience, would you say that level 1 would still be large 
enough to be useful?

I think so.  109 functions is not a small list, and I think it's clear 
that LOTS of spreadsheets are within that minimal set.  Even if we made 
level 1 smaller (e.g., removed database functions), that'd still be useful.

 > The only drawback of levels is that we might see spreadsheets 
complying to level 1 only and then saying to their customers "we're 
compliant". For this reason, I'd like level 1 to be good and meaningful 
for the average user.

100% agree. But a level 1 set like this one, with over 100 functions, is 
VERY meaningful for the average user.

Tomas Mecir said:

> Defining levels first is good. What I didn't like on OpenFormula's
> approach was that features got moved between levels on a case-by-case
> basis, without any clear reasoning other than "it's a problem for app X".

Fair enough.  Ours was the first attempt at trying to
create levels, and you learn a lot when you try the first time :-).
Also, when you define something as the strict intersection
of a set of apps, if ANY app has a problem, then it gets moved up.
Perhaps we shouldn't be so strict... we should KNOW when we cause
work for an implementation, but if we really think that function XYZ
should be in level 1, it might be better to just do it.  Adding a function
implementation isn't hard, and just filling in a few places would
simplify users' lives later.


Rob Weir said:
 > So maybe we can start by standardizing a way for implementations
 > to define profiles, but we don't standardize on any profiles ourselves?

Profiles might be a good idea, and functions are typically grouped by 
category... creating
a ready-made set of profiles.  E.G., we could group all complex number 
functions
into a "complex" package, and an application could say "I implement 
level 2 plus
the complex package" or some such.

But there MUST be a minimal set. C/C++ include large MANDATED libraries
so that it's possible to write portable code.  A spreadsheet without SUM
is REDICULOUS.  And if there are a large number of different packages, it
gets hard to keep track of them... at which point you're back to levels
if you wish to keep your sanity.

There's also value in standardizing functions that NOT everyone does 
yet... so
that when they DO get implemented elsewhere, they're compatible.

I think 3-4 levels (minimal/typical/large or 
minimal/common/large-typica/large),
with maybe a few specialized packages (e.g., for complex numbers), would be
a plausible compromise.

--- David A. Wheeler