Re: [office] YEARFRAC information

[robert_weir@us.ibm.com]

Hi David,

Thanks for tracking these down.  I
know this has been a lingering task for OpenFormula for some time now.
 Although were were hoping that the OOXML text would provide clarification
in this area, not additional ambiguity, it looks like your brute force
approach has revealed how Excel actually defines these functions.

My understanding is that JTC1/SC34 does
not have a mechanism in place yet for accepting defect reports on OOXML.
 But they are working on such a mechanism, and when it is place we
can ask Patrick to submit a defect report on behalf of the ODF TC on this
point.

I agree with you that these inconsistencies
will likely be resolved by changing OOXML to match Excel's behavior.  Since
we require resolution now, and can not wait for OOXML maintenance activities,
we are probably best off matching what Excel does based on the formulas
you derived.

But I'm wondering if we can take this
even one level further?  If you compare your formulas with the documented
financial authorities' definitions of day count conventions, such as those
listed on Wikipedia:  http://en.wikipedia.org/wiki/Day_count_convention,
do any of them match exactly?

In other words, if we can say that Basis
0 matched NASD rule 27 as stated in this or that official document, and
this matches what Excel does perfectly, then this might be more helpful
than merely giving an arbitrary formula seemingly pulled out of the thin
air. 

-Rob
___________________________

Rob Weir
Software Architect
Workplace, Portal and Collaboration Software
IBM Software Group

email: robert_weir@us.ibm.com
phone: 1-978-399-7122
blog: http://www.robweir.com/blog/

"David A. Wheeler" <dwheeler@dwheeler.com>
wrote on 05/19/2008 08:43:51 AM:

> I've published detailed information on YEARFRAC, including
> a detailed definition that matches what Excel _actually_ does
> instead of what OOXML says (they are incompatible):
> http://www.dwheeler.com/yearfrac/excel-ooxml-yearfrac.pdf
> http://www.dwheeler.com/yearfrac/excel-ooxml-yearfrac.odt
> 
> We now need to decide what to do with OpenFormula; this affects
> 26 functions.  The 'obvious' answer is to use the definitions
that
> are actually compatible with Excel, since there are far more Excel
> .docx spreadsheets than OOXML spreadsheets
> (to my knowledge, there are no OOXML spreadsheet documents).
> If OOXML decides to stay with their incompatible basis
> definitions, we could assign new basis values for
> those algorithms, so we could handle both OOXML and .docx.
> 
> We could assign basis values 16..31 as meaning "the same as
> OOXML basis 0..15"; I think we can appeal to the definition of
an
> external specification if it's a standard :-).  I suggest these
> be optional.
> 
> --- David A. Wheeler
> 
> ---------------------------------------------------------------------
> To unsubscribe from this mail list, you must leave the OASIS TC that
> generates this mail.  You may a link to this group and all your
TCs in OASIS
> at:
> https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php
>