OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Array Formula Difference from Excel

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Guest






PostPosted: Wed Feb 04, 2004 7:28 am    Post subject: Array Formula Difference from Excel Reply with quote

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
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Feb 04, 2004 8:49 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Guest






PostPosted: Wed Feb 04, 2004 9:25 am    Post subject: Reply with quote

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
Back to top
Guest






PostPosted: Wed Feb 04, 2004 9:29 am    Post subject: Reply with quote

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
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Feb 04, 2004 10:13 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Feb 04, 2004 10:18 am    Post subject: Reply with quote

Quote:

If the fomula was written as:

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

would return 300


Ok the sum will return only one value and then I don't understand why do you use

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

instead of

=sum(if(year(a1:a3)=1996;b1:b3,0))
_________________
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
View user's profile Send private message Visit poster's website
Guest






PostPosted: Wed Feb 04, 2004 10:40 am    Post subject: Reply with quote

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
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group