4. Spreadsheet functions

4.1. union

union(nodeset1, nodeset2+)

Returns the union of all its nodeset arguments.

References such as A1, A;A, $B$2:$C$3,etc, all return nodesets, with one XML node per table cell. For example: union(A1,A2,A3) is equivalent to A1:A3.

See also intersection, difference.

4.2. intersection

intersection(nodeset1, nodeset2)

Returns all XML nodes found in both nodeset1 and nodeset2.

References such as A1, A;A, $B$2:$C$3,etc, all return nodesets, with one XML node per table cell. For example: intersection(A:A,2:2) is equivalent to A2.

See also union, difference.

4.3. difference

difference(nodeset1, nodeset2)

Returns all XML nodes found in nodeset1 but not in nodeset2.

References such as A1, A;A, $B$2:$C$3,etc, all return nodesets, with one XML node per table cell. For example: difference($A:$A,$A$10) returns all the cells of column $A except cell $A$10.

See also union, intersection.

4.4. apply

apply(transform, nodeset, strict?)

Returns a nodeset after transforming each node of its argument nodeset using expression transform.

A node is converted to a string before being passed as argument x to expression transform. Expression transform computes a value based on x and returns this value. This value is converted to a string and put into a newly created text node. (This text node is not attached to a document and has no parent or siblings.)

Expression transform must reference variable x, which represents the string value of the node. Other than that, transform may be arbitrarily complex.

By default apply is lenient. That is, if transform fails to be evaluated, the raw string value of the node is silently used. Specify strict as TRUE, if you want apply to report an error when transform fails to be evaluated.

Investment
10,000.00
12,000.00
9,000.00
15,000.00
8,000.00

Examples (the above XHTML table has attribute id="roi2"):

  • Compute the sum of all investments larger than EUR10000: sumif("roi2"!$A$2:$A$6, "x >= 10000") = 0

    This does not work because investments such as 10,000.00 use a localized format which cannot be parsed. That is, "10000.00" can be parsed as a number. "10,000.00" cannot.

  • Compute the sum of all investments larger than EUR10000: sumif(apply("numbervalue(x, '', 'en-US')", "roi2"!$A$2:$A$6), "x >= 10000") = 37000

    Note that in transform "numbervalue(x, '', 'en-US')", we have used single quotes to quote the default format '' and the 'en-US' locale. It could have been possible to specify the same transform as "numbervalue(x, """", ""en-US"")" but this is much less readable.

  • sumif(apply("numbervalue(x, '', 'en-US')", "roi2"!$A:$A, TRUE), "x >= 10000") = ERROR

  • sumif(apply("numbervalue(x, '', 'en-US')", "roi2"!$A:$A, FALSE), "x >= 10000") = 37000