http://zorba.io/modules/excel/math

View as XML or JSON.

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

Function Summary

abs ($arg as xs:anyAtomicType) as xs:anyAtomicType

Compute the abs of a numeric value.

cast-as-numeric ($number as xs:anyAtomicType) as xs:anyAtomicType

Cast the xs:anyAtomicType to a numeric type.

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.

degrees ($radian as xs:double) as xs:integer

Converts radians into degrees.

even ($number as xs:anyAtomicType) as xs:anyAtomicType

Returns number rounded up to the nearest even integer.

fact ($number as xs:anyAtomicType) as xs:integer

Returns the factorial of a number.

factdouble ($number as xs:integer) as xs:integer

Returns the double factorial of a number.

floor ($number as xs:anyAtomicType, $significance as xs:anyAtomicType) as xs:anyAtomicType

Rounds number down, toward zero, to the nearest multiple of significance.

gcd ($numbers as xs:integer+) as xs:integer

Returns the greatest common divisor GCD of a sequence of integers.

int ($number as xs:anyAtomicType) as xs:integer

Rounds a number down to the nearest integer.

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.

lcm ($numbers as xs:integer+) as xs:integer

Returns the least common multiple of integers.

mod ($number as xs:anyAtomicType, $divisor as xs:anyAtomicType) as xs:anyAtomicType

Returns the remainder after number is divided by divisor.

mround ($number as xs:anyAtomicType, $multiple as xs:anyAtomicType) as xs:anyAtomicType

Returns a number rounded to the desired multiple.

odd ($number as xs:anyAtomicType) as xs:integer

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

pi () as xs:decimal

Return the value of PI as decimal with 15 digits.

power ($number as xs:anyAtomicType, $power as xs:integer) as xs:anyAtomicType

Returns the result of a number raised to a power.

product ($numbers as xs:anyAtomicType*) as xs:anyAtomicType

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

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

Returns the integer portion of a division.

radians ($degree as xs:integer) as xs:decimal

Converts degrees to radians.

roman ($number as xs:integer) as xs:string

Converts an arabic numeral to roman, as text.

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

Rounds a number to a specified number of digits.

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

Rounds a number down, toward zero.

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

Rounds a number up, away from 0 (zero).

sign ($number as xs:anyAtomicType) as xs:integer

Determines the sign of a number.

sort-numbers ($numbers as xs:anyAtomicType*) as xs:anyAtomicType*

Helper function.

sum ($numbers as xs:anyAtomicType*) as xs:anyAtomicType

Adds all the numbers in the sequence.

trunc ($number as xs:anyAtomicType) as xs:integer

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

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

Truncates a number down to precision.

Functions

abs#1

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.

Parameters

arg as xs:anyAtomicType
The parameter can be a number, string, boolean value.

Returns

xs:anyAtomicType
The abs value as a numeric type.

cast-as-numeric#1

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.

Parameters

number as xs:anyAtomicType
The parameter can be a number, string, boolean value.

Returns

xs:anyAtomicType
The casted value.

ceiling#2

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.

Parameters

number as xs:anyAtomicType
The value you want to round.
significance as xs:anyAtomicType
The multiple to which you want to round.

Returns

xs:anyAtomicType
The rounded value.

degrees#1

declare  function excel:degrees($radian as xs:double) as xs:integer
Converts radians into degrees.

Parameters

radian as xs:double
The value in radians.

Returns

xs:integer
The value in degrees 0 .. 360 or 0 .. -360.

even#1

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.

Parameters

number as xs:anyAtomicType
The value to round.

Returns

xs:anyAtomicType
The rounded value casted as numeric type.

fact#1

declare  function excel:fact($number as xs:anyAtomicType) as xs:integer
Returns the factorial of a number.

Parameters

number as xs:anyAtomicType
The nonnegative number you want the factorial of. If number is not an integer, it is truncated.

Returns

xs:integer
Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.

factdouble#1

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

Parameters

number as xs:integer
The positive integer value.

Returns

xs:integer
The result as integer.

floor#2

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.

Parameters

number as xs:anyAtomicType
The value you want to round. The value is casted to numeric.
significance as xs:anyAtomicType
The multiple to which you want to round.

Returns

xs:anyAtomicType
The rounded value as numeric type.

gcd#1

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.

Parameters

numbers as xs:integer
The sequence of positive integers.

Returns

xs:integer
The GCD as integer.

int#1

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.

Parameters

number as xs:anyAtomicType
The value to be rounded.

Returns

xs:integer
The rounded integer.

is-a-number#1

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.

Parameters

value as xs:anyAtomicType
Parameter to be checked.

Returns

xs:boolean
true if the value can be casted to numeric.

lcm#1

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.

Parameters

numbers as xs:integer
The sequence of one or more positive integers.

Returns

xs:integer
The LCM as integer.

mod#2

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.

Parameters

number as xs:anyAtomicType
The number for which you want to find the remainder.
divisor as xs:anyAtomicType
The number by which you want to divide number. This cannot be zero.

Returns

xs:anyAtomicType
The remainder from division as numeric type.

mround#2

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.

Parameters

number as xs:anyAtomicType
The value to round, castable to numeric type.
multiple as xs:anyAtomicType
The multiple to which you want to round number.

Returns

xs:anyAtomicType
The rounded number up to the desired multiple.

odd#1

declare  function excel:odd($number as xs:anyAtomicType) as xs:integer
Returns number rounded up to the nearest odd integer, away from zero.

Parameters

number as xs:anyAtomicType
The value to round.

Returns

xs:integer
The odd integer.

pi#0

declare  function excel:pi() as xs:decimal
Return the value of PI as decimal with 15 digits.

Parameters

Returns

xs:decimal
The value of PI with 15 digits.

power#2

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.

Parameters

number as xs:anyAtomicType
The base number.
power as xs:integer
The exponent as integer (cannot be floating point like in Excel).

Returns

xs:anyAtomicType
The result as numeric type.

product#1

declare  function excel:product($numbers as xs:anyAtomicType*) as xs:anyAtomicType
Multiplies all the numbers given as arguments and returns the product.

Parameters

numbers as xs:anyAtomicType
The sequence of arguments convertable to numeric types. The sequence can be of any length.

Returns

xs:anyAtomicType
The multiplication result as numeric type.

quotient#2

declare  function excel:quotient($numerator as xs:anyAtomicType, $denominator as xs:anyAtomicType) as xs:integer
Returns the integer portion of a division.

Parameters

numerator as xs:anyAtomicType
The divident.
denominator as xs:anyAtomicType
The divisor. It cannot be zero.

Returns

xs:integer
The result value as numeric type.

radians#1

declare  function excel:radians($degree as xs:integer) as xs:decimal
Converts degrees to radians.

Parameters

degree as xs:integer
An angle in degrees that you want to convert.

Returns

xs:decimal
The value in radians.

roman#1

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.

Parameters

number as xs:integer
A positive integer.

Returns

xs:string
The roman string representation.

round#2

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.

Parameters

number as xs:anyAtomicType
The number to round, castable to a numeric type.
precision as xs:integer
The number of decimal places to keep.

Returns

xs:anyAtomicType
The rounded number as numeric type.

rounddown#2

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.

Parameters

number as xs:anyAtomicType
The number to round, castable to numeric type.
precision as xs:integer
The number of decimal places to keep.

Returns

xs:anyAtomicType
the truncated number toward zero, as numeric type.

roundup#2

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.

Parameters

number as xs:anyAtomicType
The number to round, castable to numeric type.
precision as xs:integer
The number of decimal places to keep.

Returns

xs:anyAtomicType
The truncated number away from zero, as numeric type.

sign#1

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.

Parameters

number as xs:anyAtomicType
The argument castable to numeric type.

Returns

xs:integer
The sign as (-1, 0, 1).

sort-numbers#1

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.

Parameters

numbers as xs:anyAtomicType
The sequence of arguments castable to numeric.

Returns

xs:anyAtomicType*
The sorted sequence as numeric types.

sum#1

declare  function excel:sum($numbers as xs:anyAtomicType*) as xs:anyAtomicType
Adds all the numbers in the sequence.

Parameters

numbers as xs:anyAtomicType
The sequence of arguments castable to numeric types. The sequence can be of any length.

Returns

xs:anyAtomicType
The sum as numeric type.

trunc#1

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.

Parameters

number as xs:anyAtomicType
The argument castable to numeric type.

Returns

xs:integer
The integer value.

trunc#2

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.

Parameters

number as xs:anyAtomicType
The argument castable to numeric type.
precision as xs:integer
The number of decimal places to keep .

Returns

xs:anyAtomicType
The integer value.