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

Non-contiguous range in functions with multiple parameters

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


Joined: 05 Sep 2008
Posts: 1

PostPosted: Fri Sep 05, 2008 6:46 pm    Post subject: Non-contiguous range in functions with multiple parameters Reply with quote

Non-contiguous cells can be used in functions with one parameter by using the semicolon. For example,
Code:
SUM(A1:A2;B2)
returns the intended result. However, if the function has more then one parameter, then Calc cannot distinguish between the semicolon in the non-contiguous range and the semicolon separating parameters. For instance,
Code:
IRR(A1:A2;B2)
treats the B2 cell as the "guess" parameter instead of part of the values range.

I've attempted to add the guess with another semicolon or to enclose the non-contiguous range in parenthesis to no avail. Neither do commas work to distinguish between ranges and parameters as with other programs.

Is there a workaround other than redesigning the spreadsheet to force a contiguous range? Perhaps there is a function that takes a non-contiguous range as a function and returns a contiguous range? Something like:
Code:
IRR(RANGE(A1:A2;B2);.5)
If not, that would be a nice addition.
Back to top
View user's profile Send private message
drking
Power User
Power User


Joined: 25 Oct 2006
Posts: 91

PostPosted: Sat Sep 06, 2008 10:16 am    Post subject: Reply with quote

SUM takes a list of parameters (up to 30 of them)

IRR takes single parameters.

They're quite different, and behave as defined.

However ODFF (a new international specification) defines concatenation of references - it uses a ~ character. I think I've seen it being implemented somewhere in the Calc Issues list - but it seems not to have made it into Ooo2.4.1 as it doesn't work for me. Maybe in Ooo3? If it's in ODFF it will appear in Calc at some time.

Very few functions can return ranges. INDIRECT is a possible, but I can't get that to do it. OFFSET ditto.

Sorry - that's not very helpful, except in the negative
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 06, 2008 11:32 am    Post subject: Reply with quote

The only function I'm aware of is INDEX with a set of ranges in braces as first argument and the fourth argument, specifying the index of the range in question.
=INDEX((A1:B9;D1:E9;G1:H9);1;2;3) references H1 (first row, second column of third range).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
kafeenstra
Newbie
Newbie


Joined: 13 May 2012
Posts: 1

PostPosted: Sun May 13, 2012 9:48 am    Post subject: Reply with quote

Use brackets, like: RANGE(A1,(A1:A10,C1:C10))
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