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

["Warren Turkal" <turkal@google.com>]

On Fri, Apr 18, 2008 at 11:56 AM, David A. Wheeler
<dwheeler@dwheeler.com> wrote:
> 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...

I am willing to concede to integer representations. I just thought the
string representations would be less opaque.

>  > 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 this looks like a reasonable example, and a valid one to boot.

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

You are right, but how would you deal with Lotus123 vs. MS Excel's
idea of what basis==0 is? It seems to me that import filters are going
to have a deal with this issue in one form or another.

>  > 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).

It's not that simple as OOXML is ambiguous and Excel 2007 is being
reverse engineered. What about Excel 95, Claris Works, MS Works,
Supercalc, and every version of them?

>  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.

Fine. You convinced me. :-P

wt