Re: Range operators



On Sat, 2009-03-21 at 21:58 -0600, Rita & Klaus Kuhnlein wrote:
Hi Andreas,

that means: there is no range set union operator (comma) at all.  
Neither in Excel nor in Gnumeric. I understand!?

Technically there is an operator that is called the union operator, in
Excel and Gnumeric it is a comma, in OpenOffice3 it is denoted by ~.
According to the OpenFormula draft it is also the concatenation
operator:

------------------------------------------------------------------------
Infix Operator Reference Concatenation ("~") (aka Union)
Summary: Concatenate two references

Syntax: Reference Left ~ Reference Right

Returns: ReferenceList

Constraints: None

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).
Note that this is notated as "~" in OpenFormula format, but as a comma
or “+” in some user interfaces.
------------------------------------------------------------------------

The draft also contains the annotation:
R------------------------------------------------------------------------
ationale: OpenOffice.org 1.1.3 and 2.0.2 do not include this operator.
However, other applications, such as Microsoft Excel and Gnumeric,
include this operation. In Excel this is represented using the comma
(",") character, the same symbol used as the parameter separator for
function calls. This is a very poor choice with a number of unfortunate
ramifications. One problem is that concatetating cells in a function
parameter requires surrounding the cells with additional parentheses in
Excel display syntax. For example, AREAS(A1:A3,B2:B4) is a function call
with two parameters, while AREAS((A1:A3,B2:B4)) is a function call with
one parameter. Another problem is that the comma interferes with the use
of "," as a decimal separator (as it is used in many locales) when using
traditional entry formats (which do not mark cell addresses with "..").
Gnumeric uses "+" as the cell concatenation operator in its display, but
this has its own problems: it interferes with the use of "+" as a matrix
addition operator. There are many alternatives, e.g., other characters
(such as "~", "|", and "\"), or requiring a function syntax for this
purpose. The character "_" would be a poor choice because formula
variables can also include this character in their name (complicating
parsing when "[..]" are not used; is B3_B2 a formula variable, or are B2
and B3 concatenated?). Because of these issues, this specification uses
"~" as the cell concatenation symbol.
------------------------------------------------------------------------

(I am not sure where the claim that Gnumeric uses + in its display comes
from, perhaps an old version?)

Andreas

-- 
Andreas J. Guelzow <aguelzow pyrshep ca>




[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]