| View previous topic :: View next topic |
| Author |
Message |
mstepani
Newbie
Joined: 05 Sep 2008
Posts: 1
|
 Posted: Fri Sep 05, 2008 6:46 pm Post subject: Non-contiguous range in functions with multiple parameters |
 |
|
Non-contiguous cells can be used in functions with one parameter by using the semicolon. For example, 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, 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 |
|
 |
drking
Power User
Joined: 25 Oct 2006
Posts: 91
|
| Posted: Sat Sep 06, 2008 10:16 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Sep 06, 2008 11:32 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
kafeenstra
Newbie
Joined: 13 May 2012
Posts: 1
|
| Posted: Sun May 13, 2012 9:48 am Post subject: |
|
|
| Use brackets, like: RANGE(A1,(A1:A10,C1:C10)) |
|
| Back to top |
|
|
|
