Re: [office] YEARFRAC, etc.

["Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>]

On Mon, 2008-04-14 at 13:26 -0400, Patrick Durusau wrote:
> Andreas J. Guelzow wrote:
> > On Mon, 2008-04-14 at 11:13 -0400, Patrick Durusau wrote:
> >   
> >> David,
> >>
> >> David A. Wheeler wrote:
> >>     
> >>> Rob: Thanks for posting the YEARFRAC information from Microsoft XML.  Sadly, it's not specific enough for a real implementation.
> >>>
> >>>   
> >>>       
> >> In what way is it "not specific enough?" As I recall, our definition did 
> >> not state the distinction between options 1 and 4 other than by example, 
> >> which is also insufficient for implementation.
> >>     
> >
> > Pardon me? The fact that our definition is yet not exact is a reflection
> > of a misguided attempt to make it look like what MS XL does. I believe
> > OpenFormula has not yet been approved as a Standard. Finding utter
> > nonsense in an approved standard is quite a different issue.
> >  
> >   
> No, actually since we are trying to make a standard ourselves, it is our 
> job to find when we have failed to be specific enough.
> 
> This was an issue I uncovered months ago and strikes at the heart of an 
> entire set of financial calculations.

And I believe we are working on that. Wasn't that the point of looking
at what MS XML is doing
> 
> David assured me and I actually verified for myself, by chasing one 
> accounting society after another until I finally reached "the man" on 
> the issue who said: "whatever MS Excel does." ;-)
> 
> Admittedly not the answer I wanted to hear but the purpose of standards, 
> particularly one like formula, is not to specify the *correct* answer 
> for something like YEARFRAC, which has never been defined, formally, 
> anywhere.

If there is a correct answer then that is what we should use!

> 
> The goal is to define YEARFRAC in such a way that everyone and I do mean 
> everyone, gets the same answer by applying it.

Excuse me but that is clearly nonsense. We could surely define YEARFRAC
to return 5, always 5. Then obviously everybody would get the same
answer. 

IF all spreadsheet programs claim that 1900 had a leap year, then since
this is not correct it surely should not be standardized to that
incorrect value.

If there is no "correct answer", then we may want to decide on something
just for the sake for consistency, but clearly not if there is one.

>  Being contrary may be a 
> value in some cases but not with standards.
> >>> I can try to get more information.  But it's clear that we are
> >>>       
> >> holding ourselves to a higher standard than ISO requires, since ISO
> >> doesn't require accurate definitions of YEARFRAC.
> >>     
> >
> > I guess we have all seen that ISO does not hold itself to any standard. 
> >   
> >>>   
> >>>       
> I thought we were supposed to be writing our standard. Yes?

Yes. Which means we as a group should be holding ourselves to a standard
(which hopefully included not to standardized objectively wrong
calculations.)

> >> SC 34 is setting up an errata process to address that type of concern. I 
> >> am assuming that if we can propose a solution to the problem and obtain 
> >> agreement, even informal, that no one is going to deliberately adopt a 
> >> different definition of YEARFRAC.

Nobody knows whether SC34 will ever come up with anything useful. 

> >>
> >>     
> >
> > I suggest that for the purposes of defining the behaviour of YEARFRAC in
> > OpenFormula, we essentially ignore whatever MS has included in their
> > definition but provide an exact (and therefore implementable)
> > definition. Obviously we may want to used something which in general
> > usage may resemble what XL does but since even MS was unable to define
> > what they happen to calculate it is silly to try to emulate them to
> > closely.
> >
> >   
> A better solution would be to provide an agreed upon and exact (and 
> therefore implementable) definition that everyone will be using.

That would be great.

> 
> Hope you are having a great day!

I hope so do you

Andreas

>