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

Excel array notation in formulas

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


Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

PostPosted: Sun Sep 19, 2004 3:22 pm    Post subject: Excel array notation in formulas Reply with quote

I can't figure out if OOo Calc can handle array in logical functions like Excel..

Excel: If A1 is 12 or 14 then value is "Yes", otherwise "No"
=IF(OR(A1={12,14}),"Yes","No")

equivalent to

=IF(OR(A1=12,A1=14),"Yes", "No")

OOo Calc Same functionality can be implemented like the second method...

=IF(OR(A1=12;A1=14);"Yes",;"No")

My question concerns the first Excel approach - possible or not in Calc??
_________________
OOo Calc tips: http://www.openofficetips.com
Back to top
View user's profile Send private message Visit poster's website
Iannz
OOo Advocate
OOo Advocate


Joined: 14 Feb 2004
Posts: 494
Location: Christchurch, New Zealand

PostPosted: Sun Sep 19, 2004 8:20 pm    Post subject: Reply with quote

As far as I know Calc can not have array constants in formulas.
A work around is to have the values in cells.
So =IF(OR(A1={12;14});"Yes";"No") will not work;
But {=IF(OR(A1=E1:E2);"Yes";"No")} will work when entered as an array formula (Ctrl+Shift+Enter).

Another work around, although clunky, is to have a macro formula for creating the array constants. Something like:

Code:
function fnArray(a, b, optional c, optional d, optional e, optional f, optional g)
if ismissing(c) then
   fnArray=array(a, b)
elseif ismissing(d) then
   fnArray=array(a, b, c)
elseif ismissing(e) then
   fnArray=array(a, b, c, d)
elseif ismissing(f)   then
   fnArray=array(a, b, c, d, e)
elseif ismissing(g)   then
 fnArray=array(a, b, c, d, e, f)
else
   fnArray=array(a, b, c, d, e, f, g)
end if
end function


And then =IF(OR(A1=fnArray(12;14));"Yes";"No") will work.
_________________
Cheers, Ian

http://wiki.services.openoffice.org/wiki/Extensions_development_basic a wiki about writing OpenOffice.org extensions.
Back to top
View user's profile Send private message
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