3. Mathematical functions
Prev | Chapter 4. Predefined functions | Next

## 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

  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
1 | 10
2 | 20
3 | Thirty

- 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.

Investment | ROI
10000 | 10000
12000 | 15000
9000 | 17000
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.

Prev | Up | Next
2. Logical functions | Home | 4. Spreadsheet functions