RE: [office-formula] Re: Excel's YEARFRAC algorithm

[Doug Mahugh <Doug.Mahugh@microsoft.com>]

David,

I've finished testing and concluded that the VBA sample I had provided does indeed match Excel's behavior in all cases.  The Ruby sample I had provided has a subtle rounding error that occurs very rarely and I haven't been able to resolve.  But in the cases where the Ruby sample doesn't match Excel, the VBA sample does match Excel, so that's not a problem we need to resolve at this point.

To give you a feel for how rarely the rounding error occurs, the attached program matches Excel's output on the first 150,000 test cases and then fails on line 151,482 of yearfrac_data_basis0.txt (although the VBA version does not fail) as follows:

 startdate: 1999-02-28
 enddate:   2001-02-28
 basis:     0
 Excel result: 2.000000
 VBA sample:   2.000000
 Ruby sample:  1.994444

(Note that for these purposes I defined "fail" as a result that differs from Excel's by more than 1E-6 as you suggested.)

So we can proceed on the basis that we know what Excel is doing, and your Python sample and my VBA sample both mirror Excel's actual YEARFRAC implementation accurately.  I'll try to resolve the question of what's happening in the Ruby sample, but that will just be a third implementation of the same behavior and isn't necessary for us to move forward on your YEARFRAC proposal.

Regards,
Doug


-----Original Message-----
From: David A. Wheeler [mailto:dwheeler@dwheeler.com]
Sent: Monday, June 16, 2008 4:08 PM
To: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] Re: Excel's YEARFRAC algorithm

Doug Mahugh:
> > I presume that Microsoft does NOT plan on changing this algorithm?
>
> We're not planning to change it currently.  As a general observation, we put a fairly high priority on backward compatibility in these situations, so I'd guess that we'd be more likely to do a new separate function than make any change to the behavior of the existing YEARFRAC.  But that's just my own speculation, so I'll look into this and see if I can get anything more specific.  (I was traveling Friday and haven't had a chance to discuss this with anyone else yet.)

That's what I would expect, and I think it's a good idea.  What's odd - and sent me on this spree - was that the OOXML drafts I did NOT have the same algorithm as Excel (!).

> How about if I write a loop to run my Ruby code against your test cases, to verify that our algorithms are functionally equivalent in those instances?  Were those results generated by your Python code?  Or by Excel?

Excel 2007.  So if your Ruby or VB code produces the same values as the test cases,
then it's producing the same values as Excel 2007 and the Python code.

> In any event, I could write a loop to run my code against all of the test cases in yearfrac_data_basis_all.zip and flag anywhere we differ more than a final-digit rounding error.

Okay.  I suggest an epsilon of 1E-6; that's enough to distinguish between
1/365 and 1/((365+366)/2)

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

yearfrac_test.rb