Calc Guide

Reference concatenation operator
The concatenation operator is written as a tilde. An expression using
the concatenation operator has the following syntax:
reference left ~ reference right
The result of such an expression is a reference list, which is an ordered
list of references. Some functions can take a reference list as an
argument, SUM, MAX or INDEX for example.
The reference concatenation is sometimes called 'union'. But it is not
the union of the two sets 'reference left' and 'reference right' as
normally understood in set theory. COUNT(A1:C3~B2:D2) returns 12
(=9+3), but it has only 10 cells when considered as the union of the
two sets of cells.
Notice that SUM(A1:C3;B2:D2) is different from SUM( A1:C3~B2:D2)
although they give the same result. The first is a function call with 2
parameters, each of them is reference to a range. The second is a
function call with 1 parameter, which is a reference list.
Intersection operator
The intersection operator is written as an exclamation mark. An
expression using the intersection operator has the following syntax:
reference left ! reference right
If the references refer to single ranges, the result is a reference to a
single range, containing all cells, which are both in the left reference
and in the right reference.
If the references are reference lists, then each list item from the left is
intersected with each one from the right and these results are
concatenated to a reference list. The order is to first intersect the first
item from the left with all items from the right, then intersect the
second item from the left with all items from the right, and so on.
Examples
A2:B4 ! B3:D6
This results in a reference to the range B3:B4, because these cells
are inside A2:B4 and inside B3:D4.
(A2:B4~B1:C2) ! (B2:C6~C1:D3)
First the intersections A2:B4!B2:C6, A2:B4!C1:D3, B1:C2!B2:C6 and
B1:C2!C1:D3 are calculated. This results in B2:B4, empty, B2:C2,
and C1:C2. Then these results are concatenated, dropping empty
parts. So the final result is the reference list B2:B4 ~ B2:C2 ~
C1:C2.
188 OpenOffice.org 3.x Calc Guide