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.
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.
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.
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.
product(value
+)
Same as sum except that the product all the arguments is returned instead of the sum.
abs(number
)
Returns the absolute value of its argument (after converting it to a number, if needed to).
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.
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.
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.
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.
cos(number
)
Returns the cosine of its argument (after converting it to a number, if needed to).
cosh(number
)
Returns the hyperbolic cosine of its argument (after converting it to a number, if needed to).
sin(number
)
Returns the sine of its argument (after converting it to a number, if needed to).
sinh(number
)
Returns the hyperbolic sine of its argument (after converting it to a number, if needed to).
tan(number
)
Returns the tangent of its argument (after converting it to a number, if needed to).
tanh(number
)
Returns the hyperbolic tangent of its argument (after converting it to a number, if needed to).
degrees(angle
)
Returns its argument, an angle measured in radians, after converting it to degrees.
radians(angle
)
Returns its argument, an angle measured in degrees, after converting it to radians.
exp(number
)
Returns Euler's number e raised to the power of its argument (after converting it to a number, if needed to).
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.
asinh(number
)
Returns the inverse hyperbolic sine of its argument (after converting it to a number, if needed to).
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.
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.
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
ln(number
)
Returns the natural logarithm of its argument (after converting it to a number, if needed to). Number number must be strictly positive.
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.
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.
sqrt(number
)
Returns the square root of its argument. Number number must be positive. The argument is converted to a number if needed to.
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.
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.
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.
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.
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
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.
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
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
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
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