| View previous topic :: View next topic |
| Author |
Message |
richhill
OOo Advocate
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 |
|
| Back to top |
|
 |
Iannz
OOo Advocate
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. |
|
| Back to top |
|
|
|
|
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
|
