Product: Spreadsheet
Type: new
Title: Infix Operator Reference Concatenation ("~") (aka Union)
Posted by: [EMAIL PROTECTED]
Affected: sc
Effective from: cws odff
*Flags*
-------
API/ BASIC [ ]
Configuration [ ]
File format change [ ]
Help/ Guide [x]
Performance test [ ]
Translation [ ]
UI relevant [ ]
*Description*
-------------
Summary: Concatenate two references
Syntax: Reference Left ~ Reference Right
Returns: ReferenceList
Semantics: Takes two references and computes the "cell union", which
is simply a concatenation of the reference Left followed by the
reference Right. This is not the same as a union in set theory;
duplicate references to cells are not removed. The resulting reference
will have the number of areas, as reported by AREAS, as
AREAS(Left)+AREAS(Right). If Left or Right are not references, an
error is returned.
A reference concatenation results in a list of references. A reference
list can be passed as an argument to functions expecting a reference
parameter where passing one reference results in an identical
computation as an arbitrary sequence of single references occupying
the identical cell range. For example, SUM(A1:B2) is identical to
SUM(A1~B2~A2~B1), but COLUMNS(A1:B2), resulting in 2 columns, is not
identical to COLUMNS(A1~B2~A2~B1), where iterating over the reference
list would result in 4 columns. Also many statistical functions depend
on the order of values and will not accept a reference list as
argument. A reference list can not be converted to an array, thus in
array context {ABS(A1~B2~A2~B1)} is an invalid expression, whereas
{ABS(A1:B2)} is not. Passing a reference list in all these cases
generates an error A list of functions accepting this argument is
given below.
Older versions of OpenOffice.org do not understand this operator and
will generate an error when encountered.
The UI syntax known from MS-Excel is also accepted, where two
references are separated by the parameter separator and the entire
expression has to be put in parentheses: (Left;Right). As this form is
easily confusable with normal parameters, e.g. SUM((A1:B2;C3:D4))
indeed is only one argument to the SUM function, and adding
respectively removing parentheses may significantly change the meaning
of the expression, the form using the '~' tilde character was chosen,
which follows the ODFF specification draft. A user input of
(Left;Right) is automatically converted to (Left~Right).
Functions accepting a reference list instead of a range reference as
parameter:
AREAS
INDEX
SUM
AND
OR
ISREF
MIN
MINA
MAX
MAXA
COUNT
COUNTA
STDEV
STDEVA
COUNTBLANK
COUNTIF
SUMIF Note: only in first parameter (criteria) and only if
third parameter (sum range) not given.
NPV
ZTEST
KURT
HARMEAN
GEOMEAN
SKEW
RANK
AVEDEV
GCD
LCM
FREQUENCY
MEDIAN
PERCENTILE
LARGE
SMALL
PERCENTRANK
TRIMMEAN
Parts of the description extracted from the ODFF specification draft
available at
http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
Send feedback to [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
