Re: [office] ODD{F,L}, Yield and Price spreadsheet functions.

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

Robert Weir:
"To move forward on this we need to consider three possibly different
definitions of any spreadsheet function:
1) The correct formula, as determined by an external authority, perhaps
another standard, or a standard textbook or handbook in the field.
2) What Excel calculates.  This may differ in some cases with the
external authority, for example with leap year calculations, or the
definition of the CEILING function.
3) The formula that OOXML provides.  We should not assume that this
formula accurately reflects what Excel actually does.  In fact, using
these definitions as a short cut to our own definitions is likely to lead
us astray."

Just to clarify for all, we've already addressed the examples of #2. Here's some basics on how we addressed them.

CEILING is redefined to be a _superset_ of Excel's capabilities and the standard math operator (which Excel is not compatible with).  If you use a one-parameter version, you get the standard mathematical function (Excel doesn't have a 1-parameter version).  But through additional parameters you can say "please use the screwed-up Excel semantics".  Thus, CEILING _looks_ slightly different in OpenFormula, but it has all the capabilities of Excel _AND_ standard math _AND_ OpenOffice.org.  This is one of the few functions where translating between Excel and OpenFormula is slightly more than a syntax change and function rename, but it's still trivial.  The same holds for FLOOR, of course.

The date stuff has been resolved after much discussion. Dates are stored as dates, not as numbers, which eliminates much of the epoch dependency and other weirdness in the first place.   OpenFormula is specifically designed to permit, but NOT require, a screwed-up leap year calculation in 1900.  (So you CAN do exactly what Excel does, if your users demand it, but you are not REQUIRED to get wrong results as the OXML spec mandates.) It also allows a different epoch value (Dec 31 1899) that resolves this weirdness in nearly all cases.  There's also a predefined "Year 1583" option: "An application having the “year 1583” capability can correctly calculate dates correctly starting from the January 1 of the (ISO) year 1583.  This means that the application correctly determines that 1900 was not a leap year, and can handle year values for dates back to at least 1583.  These calculations use the ISO (proleptic Gregorian) calendar, that is, the calculations simply use the usual rules for the ISO (Gregorian) calendar, regardless of locale."  While many countries did NOT use Gregorian in 1583, pretending that everyone DID is actually helpful because it provides a single, uniform measurement system that you can correct other locales to.   It's trivial to implement, so it's a reasonable thing to ask implementors to do.  Year 1583 is merely an option, because many people don't need it, but by including the option in the spec, we'll probably increase the number of implementors that implement it (and thus increase interoperability).  Excel can't handle dates before 1900.

--- David A. Wheeler