View previous topic :: View next topic 
Author 
Message 
Guest

Posted: Wed Feb 04, 2004 7:28 am Post subject: Array Formula Difference from Excel 


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

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France

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


Sorry, I don't understand the semantic of your formula :
Quote:  {=sum(if(year(a1:a5)=1996;1;0))} 
"if(year(a1:a5)=1996;1;0)" return for me a one if all years are 1996 and 0 otherwise. I am not surprise by this result !
and then I don't see why a sum after this operation which can only return 0 or 1 !
and then I don't understand how we can expect values and not only one value !
Can you give exaple of what Exel is doing with this formula ?
[/quote] _________________ 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 

Back to top 


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:
19960101 100
19960201 200
19970101 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 

Back to top 


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. 

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


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. 

Back to top 


