3. Mathematical functions

3.1. numbervalue

numbervalue(text, format?, locale?)

Returns its first argument after converting it to a number.

Unless number format format is specified, values are converted to numbers as follows:

  • A string that consists of optional whitespace followed by an optional minus sign followed by a real number followed by whitespace is converted to the IEEE 754 number that is nearest (according to the IEEE 754 round-to-nearest rule) to the mathematical value represented by the string; any other string is converted to NaN.

    Note that scientific notation (example: 0.314E1) is not supported.

  • Boolean TRUE is converted to 1; boolean FALSE is converted to 0.

  • A date/time is converted to the number of seconds since January 1, 1970, 00:00:00 GMT. This number can be negative and can have a fractional part.

  • An XML nodeset is first converted to a string and then converted in the same way as a string argument.

    [Note]Note

    An XML nodeset is converted to a string by returning all the text contained in the node in the nodeset that is first in document order. Text contained in descendant nodes of this first node is taken into account. Except that text contained in comments and processing-instructions is ignored.

    Example: <ul><li>The <b>little</a></li><li> <!--pussy-->cat </li><li>is chasing a mouse.</li></ul> converted to a string gives "The little cat is chasing a mouse.".

The important thing to remember here is that unless a format is specified, numbers cannot be specified using the localized notation. For example: in France, write "3.14" to specify number PI and not "3,14".

In order to parse a localized number, number format format must be specified. Without locale argument locale, this format is interpreted using the current language of the XML document (typically specified using the standard xml:lang attribute, but this can be configured).

Examples, (assume that the language of the XML document being edited is "en-US"):

  • numbervalue("3.14") = 3.14

  • numbervalue("3.14", "#.#") = 3.14

  • numbervalue("3.14", "") = 3.14 ("" is a shorthand notation for the default format)

  • numbervalue("3,14", "#.#") = 3 (everything which is not a number after the number -- that is, the "," after the "3" -- is ignored)

  • numbervalue("3,14", "#.#", "fr") = 3.14

  • numbervalue("3,14", "", "fr-FR") = 3.14

Number formats are explained in the following document http://java.sun.com/j2se/1.4.2/docs/api/java/text/DecimalFormat.html.

Locales are specified using a standard 2-letter language code, optionally followed by a dash and a standard 2-letter country code. Examples: en, en-US, fr, fr-CA, etc.

3.2. checknumber

checknumber(value)

Returns TRUE if its argument can be successfully converted to a number (that is, which is not NaN). Otherwise returns FALSE.

Conversion of values to numbers is explained here.

See also numbervalue.

3.3. sum

sum(value+)

Returns the sum of all its arguments.

  • If an argument is an XML nodeset (example: sum(A1:D4)), each node in the nodeset is converted to a number and this number is added to the total value. Nodes which cannot converted to numbers are simply ignored.

  • If an argument is not a XML nodeset, it is converted to a number if needed to and then added to the total value. If the argument cannot be successfully converted to a number, an error is reported.

Conversion of values to numbers is explained here.

Example:

A
110
220
3Thirty
  • sum(A:A, 30, "40.0", FALSE()) = 100.

  • sum(3.1416, "Thirty") reports an error.

3.4. product

product(value+)

Same as sum except that the product all the arguments is returned instead of the sum.

3.5. abs

abs(number)

Returns the absolute value of its argument (after converting it to a number, if needed to).

3.6. acos

acos(number)

Returns the arc cosine of its argument (after converting it to a number, if needed to). Number number must be in the 0,PI range.

3.7. asin

asin(number)

Returns the arc sine of its argument (after converting it to a number, if needed to). Number number must be in the -PI/2,PI/2 range.

3.8. atan

atan(number)

Returns the arc tangent of its argument (after converting it to a number, if needed to). Number number must be in the -PI/2,PI/2 range.

3.9. atan2

atan2(x, y)

Converts rectangular coordinates (x, y) to polar coordinates (r, theta). This function returns theta by computing an arc tangent of y/x. y/x must be in the -PI,PI range.

3.10. cos

cos(number)

Returns the cosine of its argument (after converting it to a number, if needed to).

3.11. cosh

cosh(number)

Returns the hyperbolic cosine of its argument (after converting it to a number, if needed to).

3.12. sin

sin(number)

Returns the sine of its argument (after converting it to a number, if needed to).

3.13. sinh

sinh(number)

Returns the hyperbolic sine of its argument (after converting it to a number, if needed to).

3.14. tan

tan(number)

Returns the tangent of its argument (after converting it to a number, if needed to).

3.15. tanh

tanh(number)

Returns the hyperbolic tangent of its argument (after converting it to a number, if needed to).

3.16. degrees

degrees(angle)

Returns its argument, an angle measured in radians, after converting it to degrees.

3.17. radians

radians(angle)

Returns its argument, an angle measured in degrees, after converting it to radians.

3.18. pi

pi()

Returns the value of PI, the ratio of the circumference of a circle to its diameter.

3.19. exp

exp(number)

Returns Euler's number e raised to the power of its argument (after converting it to a number, if needed to).

3.20. acosh

acosh(number)

Returns the inverse hyperbolic cosine of its argument (after converting it to a number, if needed to). Number number must be greater than 1.

3.21. asinh

asinh(number)

Returns the inverse hyperbolic sine of its argument (after converting it to a number, if needed to).

3.22. atanh

atanh(number)

Returns the inverse hyperbolic tangent of its argument (after converting it to a number, if needed to). Number number must be in the -1,1 range.

3.23. log

log(number, base)

Returns the log base base of its argument (after converting it to a number, if needed to). Number number must be strictly positive.

See also ln, log10.

3.24. mod

mod(dividend, divisor)

Returns the remainder of the division of dividend by divisor. Divisor and dividend are converted to numbers if needed to. Equivalent to: dividend - divisor*INT(dividend/divisor).

Example: mod(3,2) = 1

3.25. ln

ln(number)

Returns the natural logarithm of its argument (after converting it to a number, if needed to). Number number must be strictly positive.

See also log, log10.

3.26. log10

log10(number)

Returns the log base 10 of its argument (after converting it to a number, if needed to). Number number must be strictly positive.

See also log, ln.

3.27. sign

sign(number)

Returns 1 if its argument is strictly positive, -1 if its argument is strictly negative, 0 if its argument is null. The argument is converted to a number if needed to.

3.28. sqrt

sqrt(number)

Returns the square root of its argument. Number number must be positive. The argument is converted to a number if needed to.

3.29. trunc

trunc(number)

Returns its argument after removing its fractional part. The argument is converted to a number if needed to.

Example: trunc(-8.9) = 8

See also int.

3.30. int

int(number)

Returns the largest value that is not greater than the argument and is equal to a mathematical integer. The argument is converted to a number if needed to.

Example: int(-8.9) = 9

See also trunc.

3.31. rand

rand()

Returns a pseudo-random number between 0 and 1. Use rand()*(b - a) + a to get a random number in the a,b range.

3.32. countif

countif(nodeset, test)

Count each node in nodeset if evaluating boolean expression test returns TRUE for this node.

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

French
10
12
09
15
08

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

  • Count students having 12/20 or more to their French exam: countif("exams1"!$A:$A, "and(checknumber(x), x >= 12)") = 2

  • Count students having between 9/20 and 12/20 to their French exam: countif("exams1"!$A:$A, "and(checknumber(x), x >= 9, x <= 12)") = 3

3.33. sumif

sumif(nodeset, test, sum_nodeset?)

For each node in nodeset which can be converted to a number, evaluates boolean expression test. If test returns TRUE adds node converted to a number to the total. Returns the total.

If sum_nodeset is specified, nodes in nodeset are used to evaluate test but it is the corresponding nodes in sum_nodeset which are added. Ignores nodes in sum_nodeset which cannot be converted to numbers.

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

InvestmentROI
1000010000
1200015000
900017000
15000-20000
8000-1000

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

  • Compute the sum of all investments larger than EUR10000: sumif("roi1"!$A:$A, "x >= 10000") = 37000

  • Compute the return on investment for all investments larger than EUR10000: sumif("roi1"!$A:$A, "x >= 10000", "roi1"!$B:$B) = 5000

3.34. round

round(number, digits)

Returns number number rounded to the specified number of digits digits.

Examples:

  • round(33.14159, 0) = 33

  • round(33.74159, 0) = 34

  • round(33.14159, 2) = 33.14

  • round(33.14159, -1) = 30

  • round(-33.14159, 0) = -33

  • round(-33.14159, 2) = -33.14

  • round(-33.14159, -1) = -30

See also rounddown, roundup.

3.35. rounddown

rounddown(number, digits)

Returns number number rounded down to the specified number of digits digits.

Examples:

  • rounddown(33.14159, 0) = 33

  • rounddown(33.74159, 0) = 33

  • rounddown(33.14159, 2) = 33.14

  • rounddown(33.14159, -1) = 30

  • rounddown(-33.14159, 0) = -33

  • rounddown(-33.14159, 2) = -33.14

  • rounddown(-33.14159, -1) = -30

See also round, roundup.

3.36. roundup

roundup(number, digits)

Returns number number rounded up to the specified number of digits digits.

Examples:

  • roundup(33.14159, 0) = 34

  • roundup(33.74159, 0) = 34

  • roundup(33.14159, 2) = 33.15

  • roundup(33.14159, -1) = 40

  • roundup(-33.14159, 0) = -34

  • roundup(-33.14159, 2) = -33.15

  • roundup(-33.14159, -1) = -40

See also round, rounddown.