RE: [office-formula] COUNTIFS, AVERAGEIFS and ECMA spec vs. Excelbehavior

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

Hi Eike,

It's good to feel useful here. :-)

I've met with some of my colleagues on these functions as well as the others David had mentioned previously (e.g., CUBEMEMBER, BAHTTEXT, CHITEST, etc.), and I'm hoping to have some information to share soon.  As I mentioned before, we're pretty unlikely to change the behavior of a function in general, but I want to provide specific and certain answers so it may take me a little longer to get that for you.

Separately, FYI to you and David, I'm working through the YEARFRAC tests as we discussed (I hadn't realized there were millions of them, actually :-)), and have come across a discrepancy in a small percentage of cases that I would like to understand.  (That is, my code generates a slightly different answer from David's code and Excel, which I'd assume is a bug in my code but I want to be certain.)  I'll try to get this figured out later today and should have confirmation of the tests to you after that.  I'll follow up tomorrow, and hope to have definitive results then.

Regards,
Doug


-----Original Message-----
From: Eike Rathke [mailto:erack@sun.com]
Sent: Wednesday, June 18, 2008 1:13 PM
To: OASIS ODFF SC
Subject: [office-formula] COUNTIFS, AVERAGEIFS and ECMA spec vs. Excel behavior

Hi,

Thoughts addressed especially to Doug ;-)

With the latest revision of the spec uploaded this morning I added drafts for COUNTIFS, AVERAGEIF and AVERAGEIFS.

As already mentioned earlier, the ECMA spec for COUNTIFS has a first parameter 'count-range' that is not implemented by Excel2007. Looking at the definition for AVERAGEIFS revealed that the syntax and arguments description of COUNTIFS seems had been copied from AVERAGEIFS, including its parameter 'average-range' that then was renamed. The examples given for COUNTIFS do match the Excel2007 implementation, but not the ECMA definition.

Furthermore, AVERAGEIFS 'average-range' argument is decribed as "The actual cells that are averaged are determined by using the top, left cell in average-range as the beginning cell, and then including cells that correspond in size and shape to cell-range." This seems to had been copied from the description of AVERAGEIF, but is not implemented by Excel2007. Instead, all ranges have to be of the same dimension in shape and size, else an error is returned. In Excel2007 the described behavior is implemented only for the third optional AVERAGEIF parameter 'average-range'.

Given these discrepancies I followed with my definition what Excel2007 actually implements.

Doug, can you confirm that the Excel2007 implementation is indeed the desired behavior and will not change to what the ECMA spec says instead?

Thanks
  Eike

--
Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.