Re: [office] Excel 2007 != Ecma spec YEARFRAC. Not even slightly.What should we do?

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

In general, the formula specification tries to follow EXISTING
conventions wherever reasonable.  _All_ spreadsheet implementations
use integer "basis" values for all their functions that have
such a parameter.  I agree that using a string has its merits,
but using an integer is reasonable enough.
In fact, integers make it easy to create "ranges", and would
actually _ease_ transitions if there are differing interpretations
of basis values.

I think we should try to AVOID spec'ing something incompatible
with all existing implementations unless we have a pressing
need to do so, and I don't think this is compelling.  That said...


> On Fri, Apr 18, 2008 at 7:00 AM, David A. Wheeler <dwheeler@dwheeler.com> wrote:
> >  The problem is that basis values need not be a constant in the cell; it might be
> >  from another cell and/or a calculation (directly or indirectly), e.g.:
> >  YEARFRAC([.A5];[.B5];[.C5]+1)
 
Warren Turkal:
> This seems like an unrealistic use case. In all sincerity, why would
> someone do that? Is it common? I just don't see folks selecting the
> basis by saying I want whatever the basis is identified by taking the
> identifier of some basis (e.g. actual/actual) and adding 1 to it.

You're right, that was a silly example.

But a more realistic one is:

 YEARFRAC([.A5];[.B5]; IF(CONVENTION="US"; 0; 4))

Now the filter has to start digging through the expression tree,
or end up with a complicated resulting expression.  Ugh.

> Having said that. If people do it, import filters can just map it to
> whatever fixed string it's value corresponds to on import...

Yes, import filters can fix it... but let's try to make it easy on the filters!
Simple implementation is very important for getting a spec implemented.

Switching from an integer to a string representation does NOT
really ease the "different basis interpretation" problem at all.
It just means that EVERYONE has to convert, instead of only
SOME people having to convert.  That makes things worse, not better.
Integers also tend to be more efficient, and make it easy to
do conversions of whole groups (e.g., we could make the
Excel 2007 and OOXML conventions differ by 128, and then
adding/subtracting 128).

Since everyone uses integers NOW for basis values, let's try to stick
with that if it's practical to do so.  And I think it is.

--- David A. Wheeler