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

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

On Fri, 2008-04-18 at 10:13 -0700, Warren Turkal wrote:
> 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)
> 
> 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. I
> cannot think of any valid use of that technique.

Well, how would you translate the formula
YEARFRAC([.A5];[.B5];[.C5])

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

But that does not give you the same formula. Potentially, the content of
cell C5 is calculated dependent on the type of a certain investment. SO
this may be indeed a valid use case for a spreadsheet.

>  It's not
> that big of a deal. Just map the basis IDs to strings.

How do you identify which numbers in teh program would need to change to
strings?

>  Maybe you would
> vary the map depending the version of the program that produces the
> file being imported.
> 
> >  I'd expect most basis values to be embedded constants.  But if we can make life easier for
> >  those doing transitions, or having to convert back-and-forth, we should try.
> 
> The help that we provide should be a consistent representation of
> various algorithms. For instance,
> YEARFRAC(A5;B5;"msexcel_2007_actual_actual") would be the
> representation for calculating YEARFRAC with the reverse engineered MS
> Excel 2007 Actual/Actual basis algorithm. If you want to only specify
> the basis in one place, create another sheet and store it there. For
> example:
> 
> Config.A1="msexcel_2007_actual_actual"
> Calc.A5=a date
> Calc.B5=b date
> Calc.C5==YEARFRAC(A5;B5;Config.A1)

This is all nice and cute but completely useless for existing files.

For example let's say a sheet has Config.A1=0
and that cell is used both as an index into a table and as a base for
the yearfrac. How would you automatically modify the sheet on import?

> 
> Spreadsheet programs could provide an interface to alter the basis of
> all formulas if they wanted.

You are assuming that the basis is fixed for the whole sheet. Different
investments require different bases so it is very likely that the same
sheet will in fact use several bases, or bases calculated or looked up
for the different investments.

>  They could even ask whether the user
> wants to emulate the basis calculations of the spreadsheet being
> imported on opening the file.
> 

see above

Andreas