[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
richhill

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

 Posted: Sun Sep 19, 2004 3:22 pm    Post subject: Excel array notation in formulas 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
Iannz

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

Posted: Sun Sep 19, 2004 8:20 pm    Post subject:

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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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