http://www.zorba-xquery.com/modules/excel/math

import module namespace excel = "http://www.zorba-xquery.com/modules/excel/math";

This is a library module offering a part of the set of functions defined by Microsoft Excel 2003.

Daniel Turcanu

xquery version "3.0" encoding "utf-8";

• the XQuery module can be found here.

For more details please also see:

• Excel 2003 Documentation: Math Functions

abs

declare function excel:abs (
            $arg as xs:anyAtomicType 
 ) as xs:anyAtomicType

Compute the abs of a numeric value. The value can also be a string and it will be casted to the appropriate numeric first.

• $arg The parameter can be a number, string, boolean value.

• The abs value as a numeric type.

• excel-err:Value if arg cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052089781033.aspx

• abs1.xq
• abs2.xq
• abs3.xq
• abs4.xq
• abs5.xq
• abs6.xq

cast-as-numeric

declare function excel:cast-as-numeric (
            $number as xs:anyAtomicType 
 ) as xs:anyAtomicType

Cast the xs:anyAtomicType to a numeric type. If the value is already of a numeric type then nothing is changed. Otherwise the value is casted to the numeric type that is most appropriate.

• $number The parameter can be a number, string, boolean value.

• The casted value.

• excel-err:Value if the value cannot be casted to numeric type.

ceiling

declare function excel:ceiling (
            $number as xs:anyAtomicType, 
            $significance as xs:anyAtomicType 
 ) as xs:anyAtomicType

Returns number rounded up, away from zero, to the nearest multiple of significance. Significance must have the same sign as number. Number and significance must be of a numeric type or castable to numeric. Significance must not be zero.

• $number The value you want to round.
• $significance The multiple to which you want to round.

• The rounded value.

• excel-err:Value if parameters cannot be casted to numeric type.
• excel-err:Num if significance is zero or it doesn't have the same sign as number.

• http://office.microsoft.com/en-us/excel/HP052090071033.aspx

• ceiling1.xq
• ceiling2.xq
• ceiling3.xq
• ceiling4.xq
• ceiling5.xq
• ceiling6.xq
• ceiling7.xq

degrees

declare function excel:degrees (
            $radian as xs:double 
 ) as xs:integer

Converts radians into degrees.

• $radian The value in radians.

• The value in degrees 0 .. 360 or 0 .. -360.

• http://office.microsoft.com/en-us/excel/HP052090561033.aspx

even

declare function excel:even (
            $number as xs:anyAtomicType 
 ) as xs:anyAtomicType

Returns number rounded up to the nearest even integer. Regardless of the sign of number, a value is rounded up when adjusted away from zero.

• $number The value to round.

• The rounded value casted as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052090801033.aspx

• even1.xq
• even2.xq
• even3.xq
• even4.xq
• even5.xq
• even6.xq

fact

declare function excel:fact (
            $number as xs:anyAtomicType 
 ) as xs:integer

Returns the factorial of a number.

• $number The nonnegative number you want the factorial of. If number is not an integer, it is truncated.

• Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.

• excel-err:Num if the number is smaller than zero

• http://office.microsoft.com/en-us/excel/HP052090841033.aspx

• fact1.xq
• fact2.xq
• fact3.xq
• fact4.xq
• fact5.xq

factdouble

declare function excel:factdouble (
            $number as xs:integer 
 ) as xs:integer

Returns the double factorial of a number. Computes the double factorial of n as n(n-2)(n-4)...

• $number The positive integer value.

• The result as integer.

• excel-err:Num if the number is negative.

• http://office.microsoft.com/en-us/excel/HP052090851033.aspx

• factdouble1.xq
• factdouble2.xq

floor

declare function excel:floor (
            $number as xs:anyAtomicType, 
            $significance as xs:anyAtomicType 
 ) as xs:anyAtomicType

Rounds number down, toward zero, to the nearest multiple of significance. Significance must have the same sign as number.

• $number The value you want to round. The value is casted to numeric.
• $significance The multiple to which you want to round.

• The rounded value as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.
• excel-err:Num if significance is zero or it doesn't have the same sign as number.

• http://office.microsoft.com/en-us/excel/HP052090941033.aspx

• floor1.xq
• floor2.xq
• floor3.xq
• floor4.xq
• floor5.xq

gcd

declare function excel:gcd (
            $numbers as xs:integer+ 
 ) as xs:integer

Returns the greatest common divisor GCD of a sequence of integers. The sequence can have one or more positive integers.

• $numbers The sequence of positive integers.

• The GCD as integer.

• excel-err:Num if any number is smaller than zero.

• http://office.microsoft.com/en-us/excel/HP052091041033.aspx

• gcd1.xq
• gcd2.xq
• gcd3.xq
• gcd4.xq
• gcd5.xq
• gcd6.xq
• gcd7.xq
• gcd8.xq

int

declare function excel:int (
            $number as xs:anyAtomicType 
 ) as xs:integer

Rounds a number down to the nearest integer. Positive numbers are rounded toward zero, negative numbers are rounded away from zero.

• $number The value to be rounded.

• The rounded integer.

• excel-err:Value if parameter cannot be casted to numeric type

• http://office.microsoft.com/en-us/excel/HP052091421033.aspx

• int1.xq
• int2.xq
• int3.xq
• int4.xq

is-a-number

declare function excel:is-a-number (
            $value as xs:anyAtomicType 
 ) as xs:boolean

Checks if the xs:anyAtomicType argument is actually a numeric type or can be converted to numeric.

• $value Parameter to be checked.

• true if the value can be casted to numeric.

lcm

declare function excel:lcm (
            $numbers as xs:integer+ 
 ) as xs:integer

Returns the least common multiple of integers.
LCM for two numbers is computed by multiplying them and dividing with GCD.
The function is applied recursively replacing the first two numbers in the sequence with their LCM.

• $numbers The sequence of one or more positive integers.

• The LCM as integer.

• excel-err:Num if any number is smaller than zero.

• http://office.microsoft.com/en-us/excel/HP052091521033.aspx

• lcm1.xq
• lcm2.xq
• lcm3.xq
• lcm4.xq
• lcm5.xq

mod

declare function excel:mod (
            $number as xs:anyAtomicType, 
            $divisor as xs:anyAtomicType 
 ) as xs:anyAtomicType

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

• $number The number for which you want to find the remainder.
• $divisor The number by which you want to divide number. This cannot be zero.

• The remainder from division as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.
• excel-err:Div0 if divisor is zero after casting to numeric.

• http://office.microsoft.com/en-us/excel/HP052091821033.aspx

• mod1.xq
• mod2.xq
• mod3.xq
• mod4.xq

mround

declare function excel:mround (
            $number as xs:anyAtomicType, 
            $multiple as xs:anyAtomicType 
 ) as xs:anyAtomicType

Returns a number rounded to the desired multiple. MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple. MROUND is computed through floor function.

• $number The value to round, castable to numeric type.
• $multiple The multiple to which you want to round number.

• The rounded number up to the desired multiple.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052091851033.aspx

• mround1.xq
• mround2.xq
• mround3.xq

odd

declare function excel:odd (
            $number as xs:anyAtomicType 
 ) as xs:integer

Returns number rounded up to the nearest odd integer, away from zero.

• $number The value to round.

• The odd integer.

• excel-err:Value if parameter cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092031033.aspx

• odd1.xq
• odd2.xq
• odd3.xq
• odd4.xq
• odd5.xq
• odd6.xq

pi

declare function excel:pi (
 ) as xs:decimal

Return the value of PI as decimal with 15 digits.

• The value of PI with 15 digits.

• http://office.microsoft.com/en-us/excel/HP052092141033.aspx

power

declare function excel:power (
            $number as xs:anyAtomicType, 
            $power as xs:integer 
 ) as xs:anyAtomicType

Returns the result of a number raised to a power. The result is computed through successive multiplications.

• $number The base number.
• $power The exponent as integer (cannot be floating point like in Excel).

• The result as numeric type.

• excel-err:Value if parameter cannot be casted to numeric type.
• excel-err:Value if power is smaller than zero.

• http://office.microsoft.com/en-us/excel/HP052092171033.aspx

• power1.xq
• power3.xq
• power4.xq
• power5.xq

product

declare function excel:product (
            $numbers as xs:anyAtomicType* 
 ) as xs:anyAtomicType

Multiplies all the numbers given as arguments and returns the product.

• $numbers The sequence of arguments convertable to numeric types. The sequence can be of any length.

• The multiplication result as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092231033.aspx

• product1.xq
• product2.xq
• product3.xq
• product4.xq
• product5.xq

quotient

declare function excel:quotient (
            $numerator as xs:anyAtomicType, 
            $denominator as xs:anyAtomicType 
 ) as xs:integer

Returns the integer portion of a division.

• $numerator The divident.
• $denominator The divisor. It cannot be zero.

• The result value as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.
• excel-err:Div0 if denominator casted as numeric type has value zero.

• http://office.microsoft.com/en-us/excel/HP052092271033.aspx

• quotient1.xq
• quotient2.xq
• quotient3.xq
• quotient4.xq

radians

declare function excel:radians (
            $degree as xs:integer 
 ) as xs:decimal

Converts degrees to radians.

• $degree An angle in degrees that you want to convert.

• The value in radians.

• http://office.microsoft.com/en-us/excel/HP052092281033.aspx

• radians1.xq
• radians2.xq
• radians3.xq

roman

declare function excel:roman (
            $number as xs:integer 
 ) as xs:string

Converts an arabic numeral to roman, as text. Only the clasic format is supported (out of all formats Excel requires).
M is the largest digit, it represents 1000. Numbers bigger than 2000 will be represented by a sequence of "M".
D = 500, C = 100, L = 50, X = 10, V = 5, I = 1.

• $number A positive integer.

• The roman string representation.

• excel-err:Num if the input integer is negative

• http://office.microsoft.com/en-us/excel/HP052092381033.aspx

• roman1.xq
• roman2.xq
• roman3.xq

round

declare function excel:round (
            $number as xs:anyAtomicType, 
            $precision as xs:integer 
 ) as xs:anyAtomicType

Rounds a number to a specified number of digits. If precision is greater than 0 (zero), then number is rounded to the specified number of decimal places. If num_digits is 0, then number is rounded to the nearest integer. If num_digits is less than 0, then number is rounded to the left of the decimal point. The 0.5 is rounded away from zero.

• $number The number to round, castable to a numeric type.
• $precision The number of decimal places to keep.

• The rounded number as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092391033.aspx

• round1.xq
• round2.xq
• round3.xq
• round4.xq

rounddown

declare function excel:rounddown (
            $number as xs:anyAtomicType, 
            $precision as xs:integer 
 ) as xs:anyAtomicType

Rounds a number down, toward zero. If num_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places. If num_digits is 0, then number is rounded down to the nearest integer. If num_digits is less than 0, then number is rounded down to the left of the decimal point.

• $number The number to round, castable to numeric type.
• $precision The number of decimal places to keep.

• the truncated number toward zero, as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092411033.aspx

• rounddown1.xq
• rounddown2.xq
• rounddown3.xq
• rounddown4.xq
• rounddown5.xq

roundup

declare function excel:roundup (
            $number as xs:anyAtomicType, 
            $precision as xs:integer 
 ) as xs:anyAtomicType

Rounds a number up, away from 0 (zero). If num_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places. If num_digits is 0, then number is rounded down to the nearest integer. If num_digits is less than 0, then number is rounded down to the left of the decimal point.

• $number The number to round, castable to numeric type.
• $precision The number of decimal places to keep.

• The truncated number away from zero, as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092421033.aspx

• roundup1.xq
• roundup2.xq
• roundup3.xq
• roundup4.xq
• roundup5.xq

sign

declare function excel:sign (
            $number as xs:anyAtomicType 
 ) as xs:integer

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

• $number The argument castable to numeric type.

• The sign as (-1, 0, 1).

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092551033.aspx

• sign1.xq
• sign2.xq
• sign3.xq

sort-numbers

declare function excel:sort-numbers (
            $numbers as xs:anyAtomicType* 
 ) as xs:anyAtomicType*

Helper function.
Sorts a sequence of numbers or arguments castable to numeric. It first casts all arguments to numeric and then sorts ascending.

• $numbers The sequence of arguments castable to numeric.

• The sorted sequence as numeric types.

• excel-err:Value if parameters cannot be casted to numeric type.

sum

declare function excel:sum (
            $numbers as xs:anyAtomicType* 
 ) as xs:anyAtomicType

Adds all the numbers in the sequence.

• $numbers The sequence of arguments castable to numeric types. The sequence can be of any length.

• The sum as numeric type.

• excel-err:Value if parameters cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052092901033.aspx

• sum1.xq
• sum2.xq
• sum3.xq

trunc

declare function excel:trunc (
            $number as xs:anyAtomicType 
 ) as xs:integer

Truncates a number to an integer by removing the fractional part of the number.

• $number The argument castable to numeric type.

• The integer value.

• excel-err:Value if parameter cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052093241033.aspx

• trunc1.xq
• trunc2.xq

trunc

declare function excel:trunc (
            $number as xs:anyAtomicType, 
            $precision as xs:integer 
 ) as xs:anyAtomicType

Truncates a number down to precision. This behaves exactly like rounddown.

• $number The argument castable to numeric type.
• $precision The number of decimal places to keep .

• The integer value.

• excel-err:Value if parameter cannot be casted to numeric type.

• http://office.microsoft.com/en-us/excel/HP052093241033.aspx

• trunc3.xq