Array Formula Difference from Excel

Guest

Sorry if this has been asked (no way to search the forum). Assume I have dates in cells A1:A5. In Excel the following formula works fine. In Calc it does not (I get #VALUE). Is this a bug or does Calc intentionally handle array forumlas differently than Excel (i.e. will it ever be changed).

Formula:

In Excel
{=sum(if(year(a1:a5)=1996,1,0))}

In Calc
{=sum(if(year(a1:a5)=1996;1;0))}

SergeM · Posted: Wed Feb 04, 2004 8:49 am Post subject:

Sorry, I don't understand the semantic of your formula :

Guest · Posted: Wed Feb 04, 2004 9:25 am Post subject:

This is a cut down version of a true formula which is more complicated. Basically if you picture the following:

1996-01-01 100
1996-02-01 200
1997-01-01 100

I want to sum up all of the values for 1996. Again, this is simplified, the complicated formula makes using database functions difficult.

If the fomula was written as:

{=sum(if(year(a1:a3)=1996;b1:b3,0))}

would return 300

Guest · Posted: Wed Feb 04, 2004 9:29 am Post subject:

Also if I do use {=sum(if(yeara1:a3)=1996;1;0))} then I would expect the value to return 2, so that I knew I had 2 rows with a year of 1996.

SergeM · Posted: Wed Feb 04, 2004 10:13 am Post subject:

I cann't find a solution because I have not enough experience with OOocalc.
The first problem is the semantic of IF which is not what you expect.
=IF(YEAR(A1:A5)=1996) is true only on the case that all the years are equal to 1996...
Have a loock perhaps on the function COUNTIF ...
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide

SergeM · Posted: Wed Feb 04, 2004 10:18 am Post subject:

Guest · Posted: Wed Feb 04, 2004 10:40 am Post subject:

What should be happening (I shouldn't say should because I don't know what really should be happening, but how Excel is currently working and how my spreadsheets are created to work) is that the if test will check the year for each row one at a time, for any whose year is equal to 1996, it will grab the matching value in the true array.

The formula you present does not do this since it requires an array formula.

Now I don't know if Excel is working incorrectly or bastardizing the meaning of array formulas, but it is how it works. So for compatibility reasons either Calc needs to work this way or it needs to convert the formula to one which does fit into Calc's model.

Otherwise, at least for me, I don't really have compatible products and any solution either forces me to pick one or redesign all my worksheets depending on the environment.