2
: Copyright 2006-2009 The FLWOR Foundation.
4
: Licensed under the Apache License, Version 2.0 (the "License");
5
: you may not use this file except in compliance with the License.
6
: You may obtain a copy of the License at
8
: http://www.apache.org/licenses/LICENSE-2.0
10
: Unless required by applicable law or agreed to in writing, software
11
: distributed under the License is distributed on an "AS IS" BASIS,
12
: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
: See the License for the specific language governing permissions and
14
: limitations under the License.
18
: This is a library module offering a part of the set of functions
19
: defined by Microsoft Excel 2003.
21
: @see <a href="http://office.microsoft.com/en-us/excel/CH062528291033.aspx"
22
: target="_blank">Excel 2003 Documentation: Math Functions</a>
24
: @spec XQuery Specification: January 2007
25
: @author Daniel Turcanu
28
module namespace excel = "http://www.zorba-xquery.com/modules/excel/math" ;
31
: Use excel-err module functions for throwing errors.
33
import module namespace excel-err="http://www.zorba-xquery.com/modules/excel/errors";
36
: Checks if the xs:anyAtomicType argument is actually a numeric type
37
: or can be converted to numeric.
39
: @param $value Parameter to be checked.
40
: @return true if the value can be casted to numeric.
42
declare function excel:is-a-number($value as xs:anyAtomicType) as xs:boolean
44
fn:string(fn:number($value)) ne 'NaN'
48
: Cast the xs:anyAtomicType to a numeric type.
49
: If the value is already of a numeric type then nothing is changed.
50
: Otherwise the value is casted to the numeric type that is most appropriate.
52
: @param $number The parameter can be a number, string, boolean value.
53
: @return The casted value.
54
: @error XQP0021(errValue) if the value cannot be casted to numeric type.
56
declare function excel:cast-as-numeric($number as xs:anyAtomicType) as xs:anyAtomicType
59
case xs:double return $number
60
case xs:decimal return $number
61
case xs:double return $number
62
case xs:float return $number
64
if ($number castable as xs:integer) then
66
else if ($number castable as xs:decimal) then
68
else if ($number castable as xs:double) then
71
fn:error($excel-err:errValue, "Provided value is not a number", $number)
74
(: ---------------- Excel Math functions ----------------------- :)
77
: Compute the abs of a numeric value.
78
: The value can also be a string and it will be casted to the appropriate numeric first.
80
: @see http://office.microsoft.com/en-us/excel/HP052089781033.aspx
81
: @param $arg The parameter can be a number, string, boolean value.
82
: @return The abs value as a numeric type.
83
: @error XQP0021(errValue) if arg cannot be casted to numeric type.
84
: @example rbkt/Queries/zorba/excel/math/abs1.xq
85
: @example rbkt/Queries/zorba/excel/math/abs2.xq
86
: @example rbkt/Queries/zorba/excel/math/abs3.xq
87
: @example rbkt/Queries/zorba/excel/math/abs4.xq
88
: @example rbkt/Queries/zorba/excel/math/abs5.xq
89
: @example rbkt/Queries/zorba/excel/math/abs6.xq
91
declare function excel:abs($arg as xs:anyAtomicType) as xs:anyAtomicType
93
fn:abs(excel:cast-as-numeric($arg))
97
: Returns number rounded up, away from zero, to the nearest multiple of significance.
98
: Significance must have the same sign as number.
99
: Number and significance must be of a numeric type or castable to numeric.
100
: Significance must not be zero.
102
: @see http://office.microsoft.com/en-us/excel/HP052090071033.aspx
103
: @param $number The value you want to round.
104
: @param $significance The multiple to which you want to round.
105
: @return The rounded value.
106
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
107
: @error XQP0021(errNum) if significance is zero or it doesn't have the same sign as number.
108
: @example rbkt/Queries/zorba/excel/math/ceiling1.xq
109
: @example rbkt/Queries/zorba/excel/math/ceiling2.xq
110
: @example rbkt/Queries/zorba/excel/math/ceiling3.xq
111
: @example rbkt/Queries/zorba/excel/math/ceiling4.xq
112
: @example rbkt/Queries/zorba/excel/math/ceiling5.xq
113
: @example rbkt/Queries/zorba/excel/math/ceiling6.xq
114
: @example rbkt/Queries/zorba/excel/math/ceiling7.xq
116
declare function excel:ceiling(
117
$number as xs:anyAtomicType,
118
$significance as xs:anyAtomicType) as xs:anyAtomicType
120
let $num := excel:cast-as-numeric($number)
121
let $sig := excel:cast-as-numeric($significance)
124
fn:error($excel-err:errNum, "Ceiling function does not accept significance 0")
125
else if ($num * $sig ge 0) then
126
fn:ceiling($num div $sig) * $sig
128
fn:error($excel-err:errNum, "Ceiling function: both arguments must have the same sign")
132
: Returns number rounded up to the nearest even integer.
133
: Regardless of the sign of number, a value is rounded up when adjusted away from zero.
135
: @see http://office.microsoft.com/en-us/excel/HP052090801033.aspx
136
: @param $number The value to round.
137
: @return The rounded value casted as numeric type.
138
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
139
: @example rbkt/Queries/zorba/excel/math/even1.xq
140
: @example rbkt/Queries/zorba/excel/math/even2.xq
141
: @example rbkt/Queries/zorba/excel/math/even3.xq
142
: @example rbkt/Queries/zorba/excel/math/even4.xq
143
: @example rbkt/Queries/zorba/excel/math/even5.xq
144
: @example rbkt/Queries/zorba/excel/math/even6.xq
146
declare function excel:even($number as xs:anyAtomicType) as xs:anyAtomicType
148
let $num := excel:cast-as-numeric($number)
153
let $intnum := excel:ceiling($num, excel:sign($num))
155
if ($intnum mod 2 ne 0) then
156
if ($intnum gt 0) then
165
: Function for computing factorial.
166
: This function should not be used outside this module.
167
: This recursive function computes: number * fact(number-1)
169
: @param $intnum A positive integer.
170
: @return The factorial of intnum.
172
declare %private function excel:fact-integer($intnum as xs:integer) as xs:integer
174
if ($intnum = 1) then
177
$intnum * excel:fact-integer($intnum - 1)
181
: Returns the factorial of a number.
183
: @see http://office.microsoft.com/en-us/excel/HP052090841033.aspx
184
: @param $number The nonnegative number you want the factorial of.
185
: If number is not an integer, it is truncated.
186
: @return Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
187
: @error XQP0021(errNum) if the number is smaller than zero
188
: @example rbkt/Queries/zorba/excel/math/fact1.xq
189
: @example rbkt/Queries/zorba/excel/math/fact2.xq
190
: @example rbkt/Queries/zorba/excel/math/fact3.xq
191
: @example rbkt/Queries/zorba/excel/math/fact4.xq
192
: @example rbkt/Queries/zorba/excel/math/fact5.xq
194
declare function excel:fact($number as xs:anyAtomicType) as xs:integer
196
let $num := excel:cast-as-numeric($number) return
201
fn:error($excel-err:errNum, "Fact function does not accept numbers less than zero")
203
excel:fact-integer(xs:integer($num))
207
: Rounds number down, toward zero, to the nearest multiple of significance.
208
: Significance must have the same sign as number.
210
: @see http://office.microsoft.com/en-us/excel/HP052090941033.aspx
211
: @param $number The value you want to round. The value is casted to numeric.
212
: @param $significance The multiple to which you want to round.
213
: @return The rounded value as numeric type.
214
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
215
: @error XQP0021(errNum) if significance is zero or it doesn't have the same sign as number.
216
: @example rbkt/Queries/zorba/excel/math/floor1.xq
217
: @example rbkt/Queries/zorba/excel/math/floor2.xq
218
: @example rbkt/Queries/zorba/excel/math/floor3.xq
219
: @example rbkt/Queries/zorba/excel/math/floor4.xq
220
: @example rbkt/Queries/zorba/excel/math/floor5.xq
222
declare function excel:floor(
223
$number as xs:anyAtomicType,
224
$significance as xs:anyAtomicType) as xs:anyAtomicType
226
let $num := excel:cast-as-numeric($number)
227
let $sig := excel:cast-as-numeric($significance)
230
fn:error($excel-err:errNum, "Floor function does not accept significance 0")
231
else if ($num * $sig ge 0) then
232
fn:floor($num div $sig) * $sig
234
fn:error($excel-err:errNum, "Floor function: both arguments must have the same sign")
238
: Rounds a number down to the nearest integer.
239
: Positive numbers are rounded toward zero, negative numbers are rounded away from zero.
241
: @see http://office.microsoft.com/en-us/excel/HP052091421033.aspx
242
: @param $number The value to be rounded.
243
: @return The rounded integer.
244
: @error XQP0021(errValue) if parameter cannot be casted to numeric type
245
: @example rbkt/Queries/zorba/excel/math/int1.xq
246
: @example rbkt/Queries/zorba/excel/math/int2.xq
247
: @example rbkt/Queries/zorba/excel/math/int3.xq
248
: @example rbkt/Queries/zorba/excel/math/int4.xq
250
declare function excel:int($number as xs:anyAtomicType) as xs:integer
252
xs:integer(fn:floor(excel:cast-as-numeric($number)))
256
: Returns the remainder after number is divided by divisor.
257
: The result has the same sign as divisor.
259
: @see http://office.microsoft.com/en-us/excel/HP052091821033.aspx
260
: @param $number The number for which you want to find the remainder.
261
: @param $divisor The number by which you want to divide number.
262
: This cannot be zero.
263
: @return The remainder from division as numeric type.
264
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
265
: @error XQP0021(errDiv0) if divisor is zero after casting to numeric.
266
: @example rbkt/Queries/zorba/excel/math/mod1.xq
267
: @example rbkt/Queries/zorba/excel/math/mod2.xq
268
: @example rbkt/Queries/zorba/excel/math/mod3.xq
269
: @example rbkt/Queries/zorba/excel/math/mod4.xq
271
declare function excel:mod(
272
$number as xs:anyAtomicType,
273
$divisor as xs:anyAtomicType) as xs:anyAtomicType
275
let $num := excel:cast-as-numeric($number)
276
let $div := excel:cast-as-numeric($divisor) return
278
fn:error($excel-err:errDiv0, "Mod operator: divide by 0")
280
let $result := $num mod $div
282
if ($result * $div lt 0) then
289
: Returns number rounded up to the nearest odd integer, away from zero.
291
: @see http://office.microsoft.com/en-us/excel/HP052092031033.aspx
292
: @param $number The value to round.
293
: @return The odd integer.
294
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
295
: @example rbkt/Queries/zorba/excel/math/odd1.xq
296
: @example rbkt/Queries/zorba/excel/math/odd2.xq
297
: @example rbkt/Queries/zorba/excel/math/odd3.xq
298
: @example rbkt/Queries/zorba/excel/math/odd4.xq
299
: @example rbkt/Queries/zorba/excel/math/odd5.xq
300
: @example rbkt/Queries/zorba/excel/math/odd6.xq
302
declare function excel:odd($number as xs:anyAtomicType) as xs:integer
304
let $num := excel:cast-as-numeric($number) return
308
let $intnum := excel:ceiling($num, excel:sign($num))
310
if ($intnum mod 2 eq 0) then
311
if ($intnum ge 0) then
312
($intnum + 1) cast as xs:integer
314
($intnum - 1) cast as xs:integer
316
$intnum cast as xs:integer
320
: Return the value of PI as decimal with 15 digits.
322
: @see http://office.microsoft.com/en-us/excel/HP052092141033.aspx
323
: @return The value of PI with 15 digits.
325
declare function excel:pi() as xs:decimal
331
: Returns the result of a number raised to a power.
332
: The result is computed through successive multiplications.
334
: @see http://office.microsoft.com/en-us/excel/HP052092171033.aspx
335
: @param $number The base number.
336
: @param $power The exponent as integer (cannot be floating point like in Excel).
337
: @return The result as numeric type.
338
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
339
: @error XQP0021(errValue) if power is smaller than zero.
340
: @example rbkt/Queries/zorba/excel/math/power1.xq
341
: @example rbkt/Queries/zorba/excel/math/power3.xq
342
: @example rbkt/Queries/zorba/excel/math/power4.xq
343
: @example rbkt/Queries/zorba/excel/math/power5.xq
345
declare function excel:power(
346
$number as xs:anyAtomicType,
347
$power as xs:integer) as xs:anyAtomicType
349
let $num := excel:cast-as-numeric($number)
351
if ($power lt 0) then
352
fn:error($excel-err:errValue, "Power function: power must be greater or equal than zero")
353
else if ($power eq 0) then
355
else if ($power = 1) then
358
let $retval := excel:power($num, $power idiv 2)
360
$retval * $retval * excel:power($num, $power mod 2)
364
: Function for product.
365
: This function should not be used outside this module.
366
: Multiplies all numbers in the sequence.
368
: @param $numbers The list of arguments to be casted to numeric and multiplied.
369
: @return The multiplication result as numeric type.
370
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
372
declare %private function excel:product-internal($numbers as xs:anyAtomicType*) as xs:anyAtomicType
374
if (fn:empty($numbers)) then
377
let $x := excel:cast-as-numeric($numbers[1])
379
$x * excel:product-internal(fn:subsequence($numbers, 2))
383
: Multiplies all the numbers given as arguments and returns the product.
385
: @see http://office.microsoft.com/en-us/excel/HP052092231033.aspx
386
: @param $numbers The sequence of arguments convertable to numeric types.
387
: The sequence can be of any length.
388
: @return The multiplication result as numeric type.
389
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
390
: @example rbkt/Queries/zorba/excel/math/product1.xq
391
: @example rbkt/Queries/zorba/excel/math/product2.xq
392
: @example rbkt/Queries/zorba/excel/math/product3.xq
393
: @example rbkt/Queries/zorba/excel/math/product4.xq
394
: @example rbkt/Queries/zorba/excel/math/product5.xq
396
declare function excel:product($numbers as xs:anyAtomicType*) as xs:anyAtomicType
398
if (fn:empty($numbers)) then
401
excel:product-internal($numbers)
405
: Returns the integer portion of a division.
407
: @see http://office.microsoft.com/en-us/excel/HP052092271033.aspx
408
: @param $numerator The divident.
409
: @param $denominator The divisor. It cannot be zero.
410
: @return The result value as numeric type.
411
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
412
: @error XQP0021(div0) if denominator casted as numeric type has value zero.
413
: @example rbkt/Queries/zorba/excel/math/quotient1.xq
414
: @example rbkt/Queries/zorba/excel/math/quotient2.xq
415
: @example rbkt/Queries/zorba/excel/math/quotient3.xq
416
: @example rbkt/Queries/zorba/excel/math/quotient4.xq
418
declare function excel:quotient(
419
$numerator as xs:anyAtomicType,
420
$denominator as xs:anyAtomicType) as xs:integer
422
let $numer := excel:cast-as-numeric($numerator)
423
let $denom := excel:cast-as-numeric($denominator)
425
if ($denom eq 0) then
426
fn:error($excel-err:errDiv0, "Quotient function: divide by 0")
428
xs:integer($numer div $denom)
432
: Rounds a number to a specified number of digits.
433
: If precision is greater than 0 (zero), then number is rounded
434
: to the specified number of decimal places.
435
: If num_digits is 0, then number is rounded to the nearest integer.
436
: If num_digits is less than 0, then number is rounded to the left of the decimal point.
437
: The 0.5 is rounded away from zero.
439
: @see http://office.microsoft.com/en-us/excel/HP052092391033.aspx
440
: @param $number The number to round, castable to a numeric type.
441
: @param $precision The number of decimal places to keep.
442
: @return The rounded number as numeric type.
443
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
444
: @example rbkt/Queries/zorba/excel/math/round1.xq
445
: @example rbkt/Queries/zorba/excel/math/round2.xq
446
: @example rbkt/Queries/zorba/excel/math/round3.xq
447
: @example rbkt/Queries/zorba/excel/math/round4.xq
449
declare function excel:round(
450
$number as xs:anyAtomicType,
451
$precision as xs:integer) as xs:anyAtomicType
453
let $num := excel:cast-as-numeric($number)
455
if ($precision ge 0) then
456
let $exp_prec := excel:power(10, $precision)
459
fn:floor($num * $exp_prec + 0.5) div $exp_prec
461
-fn:floor(-$num * $exp_prec + 0.5) div $exp_prec
463
let $exp_prec := excel:power(10, -$precision)
466
fn:floor($num div $exp_prec + 0.5) * $exp_prec
468
-fn:floor(-$num div $exp_prec + 0.5) * $exp_prec
472
: Rounds a number down, toward zero.
473
: If num_digits is greater than 0 (zero), then number is rounded down
474
: to the specified number of decimal places.
475
: If num_digits is 0, then number is rounded down to the nearest integer.
476
: If num_digits is less than 0, then number is rounded down to the left of the decimal point.
478
: @see http://office.microsoft.com/en-us/excel/HP052092411033.aspx
479
: @param $number The number to round, castable to numeric type.
480
: @param $precision The number of decimal places to keep.
481
: @return the truncated number toward zero, as numeric type.
482
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
483
: @example rbkt/Queries/zorba/excel/math/rounddown1.xq
484
: @example rbkt/Queries/zorba/excel/math/rounddown2.xq
485
: @example rbkt/Queries/zorba/excel/math/rounddown3.xq
486
: @example rbkt/Queries/zorba/excel/math/rounddown4.xq
487
: @example rbkt/Queries/zorba/excel/math/rounddown5.xq
489
declare function excel:rounddown(
490
$number as xs:anyAtomicType,
491
$precision as xs:integer) as xs:anyAtomicType
493
let $num := excel:cast-as-numeric($number)
495
if ($precision ge 0) then
496
let $exp_prec := excel:power(10, $precision)
499
fn:floor($num * $exp_prec) div $exp_prec
501
-fn:floor(-$num * $exp_prec) div $exp_prec
503
let $exp_prec := excel:power(10, -$precision)
506
fn:floor($num div $exp_prec) * $exp_prec
508
-fn:floor(-$num div $exp_prec) * $exp_prec
512
: Rounds a number up, away from 0 (zero).
513
: If num_digits is greater than 0 (zero), then number is rounded down
514
: to the specified number of decimal places.
515
: If num_digits is 0, then number is rounded down to the nearest integer.
516
: If num_digits is less than 0, then number is rounded down to the left of the decimal point.
518
: @see http://office.microsoft.com/en-us/excel/HP052092421033.aspx
519
: @param $number The number to round, castable to numeric type.
520
: @param $precision The number of decimal places to keep.
521
: @return The truncated number away from zero, as numeric type.
522
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
523
: @example rbkt/Queries/zorba/excel/math/roundup1.xq
524
: @example rbkt/Queries/zorba/excel/math/roundup2.xq
525
: @example rbkt/Queries/zorba/excel/math/roundup3.xq
526
: @example rbkt/Queries/zorba/excel/math/roundup4.xq
527
: @example rbkt/Queries/zorba/excel/math/roundup5.xq
529
declare function excel:roundup(
530
$number as xs:anyAtomicType,
531
$precision as xs:integer) as xs:anyAtomicType
533
let $num := excel:cast-as-numeric($number)
535
if ($precision ge 0) then
536
let $exp_prec := excel:power(10, $precision)
539
fn:ceiling($num * $exp_prec) div $exp_prec
541
-fn:ceiling(-$num * $exp_prec) div $exp_prec
543
let $exp_prec := excel:power(10, -$precision)
546
fn:ceiling($num div $exp_prec) * $exp_prec
548
-fn:ceiling(-$num div $exp_prec) * $exp_prec
552
: Determines the sign of a number.
553
: Returns 1 if the number is positive, zero (0) if the number is 0,
554
: and -1 if the number is negative.
556
: @see http://office.microsoft.com/en-us/excel/HP052092551033.aspx
557
: @param $number The argument castable to numeric type.
558
: @return The sign as (-1, 0, 1).
559
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
560
: @example rbkt/Queries/zorba/excel/math/sign1.xq
561
: @example rbkt/Queries/zorba/excel/math/sign2.xq
562
: @example rbkt/Queries/zorba/excel/math/sign3.xq
564
declare function excel:sign($number as xs:anyAtomicType) as xs:integer
566
let $num := excel:cast-as-numeric($number)
570
else if ($num gt 0) then
577
: Adds all the numbers in the sequence.
579
: @see http://office.microsoft.com/en-us/excel/HP052092901033.aspx
580
: @param $numbers The sequence of arguments castable to numeric types.
581
: The sequence can be of any length.
582
: @return The sum as numeric type.
583
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
584
: @example rbkt/Queries/zorba/excel/math/sum1.xq
585
: @example rbkt/Queries/zorba/excel/math/sum2.xq
586
: @example rbkt/Queries/zorba/excel/math/sum3.xq
588
declare function excel:sum($numbers as xs:anyAtomicType*) as xs:anyAtomicType
590
if (fn:empty($numbers)) then
593
let $x := excel:cast-as-numeric($numbers[1])
595
$x + excel:sum(fn:subsequence($numbers,2))
599
: Truncates a number to an integer by removing the fractional part of the number.
601
: @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx
602
: @param $number The argument castable to numeric type.
603
: @return The integer value.
604
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
605
: @example rbkt/Queries/zorba/excel/math/trunc1.xq
606
: @example rbkt/Queries/zorba/excel/math/trunc2.xq
608
declare function excel:trunc($number as xs:anyAtomicType ) as xs:integer
610
xs:integer(excel:cast-as-numeric($number))
614
: Truncates a number down to precision.
615
: This behaves exactly like rounddown.
617
: @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx
618
: @param $number The argument castable to numeric type.
619
: @param $precision The number of decimal places to keep .
620
: @return The integer value.
621
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
622
: @example rbkt/Queries/zorba/excel/math/trunc3.xq
624
declare function excel:trunc(
625
$number as xs:anyAtomicType,
626
$precision as xs:integer) as xs:anyAtomicType
628
excel:rounddown(excel:cast-as-numeric($number), $precision)
632
: Helper function.<br/>
633
: Sorts a sequence of numbers or arguments castable to numeric.
634
: It first casts all arguments to numeric and then sorts ascending.
636
: @param $numbers The sequence of arguments castable to numeric.
637
: @return The sorted sequence as numeric types.
638
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
640
declare function excel:sort-numbers($numbers as xs:anyAtomicType*) as xs:anyAtomicType*
642
let $sorted-numbers :=
644
for $number in $numbers
645
let $num := excel:cast-as-numeric($number)
649
return $sorted-numbers
653
: Converts radians into degrees.
655
: @see http://office.microsoft.com/en-us/excel/HP052090561033.aspx
656
: @param $radian The value in radians.
657
: @return The value in degrees 0 .. 360 or 0 .. -360.
659
declare function excel:degrees($radian as xs:double) as xs:integer
661
xs:integer(($radian * 180 div excel:pi()) mod 360)
665
: Returns the double factorial of a number.
666
: Computes the double factorial of n as n(n-2)(n-4)...
668
: @see http://office.microsoft.com/en-us/excel/HP052090851033.aspx
669
: @param $number The positive integer value.
670
: @return The result as integer.
671
: @error XQP0021(errNum) if the number is negative.
672
: @example rbkt/Queries/zorba/excel/math/priority1/factdouble1.xq
673
: @example rbkt/Queries/zorba/excel/math/priority1/factdouble2.xq
675
declare function excel:factdouble($number as xs:integer) as xs:integer
677
if ($number lt 0) then
678
fn:error($excel-err:errNum, "Factdouble function: number should be greater than zero or equal")
679
else if ($number eq 1) then
681
else if ($number eq 2) then
684
$number * excel:factdouble($number - 2)
688
: Function for computing GCD.
689
: This function should not be used outside this module.
690
: It calculates the minimum value from a sequence of positive integers,
691
: not taking into account the zero value.
693
: @param $numbers The sequence of positive integers.
694
: @return The minimum value. If the sequence contains only zero values, then zero is returned.
696
declare %private function excel:min-without-zero($numbers as xs:integer+) as xs:integer
698
if (fn:count($numbers) eq 1) then
701
let $min-other := excel:min-without-zero(fn:subsequence($numbers, 2))
703
if ($numbers[1] eq 0) then
705
else if ($min-other eq 0) then
707
else if ($numbers[1] lt $min-other) then
714
: Function for computing GCD.
715
: This function should not be used outside this module.
716
: Checks if all integer numbers from a sequence divide exactly to a divident.
718
: @param $numbers The positive integers.
719
: @param $divident The divident to be tried.
720
: @return true if the numbers divide exactly.
722
declare %private function excel:try-exact-divide(
723
$numbers as xs:integer*,
724
$divident as xs:integer) as xs:boolean
726
if (fn:empty($numbers)) then
729
if ($numbers[1] mod $divident ne 0) then
732
excel:try-exact-divide(fn:subsequence($numbers, 2), $divident)
736
: Function for computing GCD.
737
: This function should not be used outside this module.
738
: This function iterates through possible divisors and checks if the sequence
739
: divides exactly to any of those. It starts from the minimum value from the
740
: sequence and searches downwards.
742
: @param $numbers The sequence of positive integers.
743
: @param $min-nonzero The minimum value of numbers sequence, excluding the zero value.
744
: @param $iteration Which iteration is it. It starts from 1 and continues
746
: @return The greatest common divisor if found, or 1 if not found.
748
declare %private function excel:iterate-all-gcd(
749
$numbers as xs:integer*,
750
$min-nonzero as xs:integer,
751
$iteration as xs:integer) as xs:integer
753
if ($min-nonzero mod $iteration eq 0) then
754
if (excel:try-exact-divide($numbers, $min-nonzero idiv $iteration)) then
755
$min-nonzero idiv $iteration
757
excel:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1)
759
if ($iteration > $min-nonzero idiv 2) then
762
excel:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1)
766
: Returns the greatest common divisor GCD of a sequence of integers.
767
: The sequence can have one or more positive integers.
769
: @see http://office.microsoft.com/en-us/excel/HP052091041033.aspx
770
: @param $numbers The sequence of positive integers.
771
: @return The GCD as integer.
772
: @error XQP0021(errNum) if any number is smaller than zero.
773
: @example rbkt/Queries/zorba/excel/math/priority1/gcd1.xq
774
: @example rbkt/Queries/zorba/excel/math/priority1/gcd2.xq
775
: @example rbkt/Queries/zorba/excel/math/priority1/gcd3.xq
776
: @example rbkt/Queries/zorba/excel/math/priority1/gcd4.xq
777
: @example rbkt/Queries/zorba/excel/math/priority1/gcd5.xq
778
: @example rbkt/Queries/zorba/excel/math/priority1/gcd6.xq
779
: @example rbkt/Queries/zorba/excel/math/priority1/gcd7.xq
780
: @example rbkt/Queries/zorba/excel/math/priority1/gcd8.xq
782
declare function excel:gcd($numbers as xs:integer+) as xs:integer
784
if (fn:count($numbers) = 1) then
787
let $minval := excel:min-without-zero($numbers)
789
if ($minval lt 0) then
790
fn:error($excel-err:errNum, "gcd function: numbers should be greater than zero or equal")
791
else if ($minval eq 0) then
794
excel:iterate-all-gcd($numbers, $minval, 1)
798
: Returns the least common multiple of integers.<br/>
799
: LCM for two numbers is computed by multiplying them and dividing with GCD. <br/>
800
: The function is applied recursively replacing the first two numbers in the sequence with their LCM.
802
: @see http://office.microsoft.com/en-us/excel/HP052091521033.aspx
803
: @param $numbers The sequence of one or more positive integers.
804
: @return The LCM as integer.
805
: @error XQP0021(errNum) if any number is smaller than zero.
806
: @example rbkt/Queries/zorba/excel/math/priority1/lcm1.xq
807
: @example rbkt/Queries/zorba/excel/math/priority1/lcm2.xq
808
: @example rbkt/Queries/zorba/excel/math/priority1/lcm3.xq
809
: @example rbkt/Queries/zorba/excel/math/priority1/lcm4.xq
810
: @example rbkt/Queries/zorba/excel/math/priority1/lcm5.xq
812
declare function excel:lcm($numbers as xs:integer+) as xs:integer
814
if(count($numbers) eq 1) then
817
if(count($numbers) eq 2) then
818
let $product := excel:product(fn:distinct-values($numbers))
820
if ($product eq 0) then
823
$product idiv excel:gcd($numbers)
825
excel:lcm((excel:lcm(($numbers[1], $numbers[2])), subsequence($numbers, 3)))
830
: Returns a number rounded to the desired multiple.
831
: MROUND rounds up, away from zero, if the remainder of dividing number by multiple
832
: is greater than or equal to half the value of multiple.
833
: MROUND is computed through floor function.
835
: @see http://office.microsoft.com/en-us/excel/HP052091851033.aspx
836
: @param $number The value to round, castable to numeric type.
837
: @param $multiple The multiple to which you want to round number.
838
: @return The rounded number up to the desired multiple.
839
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
840
: @example rbkt/Queries/zorba/excel/math/priority1/mround1.xq
841
: @example rbkt/Queries/zorba/excel/math/priority1/mround2.xq
842
: @example rbkt/Queries/zorba/excel/math/priority1/mround3.xq
844
declare function excel:mround(
845
$number as xs:anyAtomicType,
846
$multiple as xs:anyAtomicType) as xs:anyAtomicType
848
let $num := excel:cast-as-numeric($number)
849
let $mul := excel:cast-as-numeric($multiple)
850
let $floor := excel:floor($num, $mul) return
852
if (($num - $floor) ge ($mul div 2)) then
857
if ((-$num + $floor) ge (-$mul div 2)) then
864
: Converts degrees to radians.
866
: @see http://office.microsoft.com/en-us/excel/HP052092281033.aspx
867
: @param $degree An angle in degrees that you want to convert.
868
: @return The value in radians.
869
: @example rbkt/Queries/zorba/excel/math/priority1/radians1.xq
870
: @example rbkt/Queries/zorba/excel/math/priority1/radians2.xq
871
: @example rbkt/Queries/zorba/excel/math/priority1/radians3.xq
873
declare function excel:radians($degree as xs:integer) as xs:decimal
875
($degree mod 360) div 180.0 * excel:pi()
879
: Converts an arabic numeral to roman, as text.
880
: Only the clasic format is supported (out of all formats Excel requires).<br/>
881
: M is the largest digit, it represents 1000.
882
: Numbers bigger than 2000 will be represented by a sequence of "M".<br/>
883
: D = 500, C = 100, L = 50, X = 10, V = 5, I = 1.
885
: @see http://office.microsoft.com/en-us/excel/HP052092381033.aspx
886
: @param $number A positive integer.
887
: @return The roman string representation.
888
: @error XQP0021(errNum) if the input integer is negative
889
: @example rbkt/Queries/zorba/excel/math/priority1/roman1.xq
890
: @example rbkt/Queries/zorba/excel/math/priority1/roman2.xq
891
: @example rbkt/Queries/zorba/excel/math/priority1/roman3.xq
893
declare function excel:roman($number as xs:integer) as xs:string
895
if ($number lt 0) then
896
fn:error($excel-err:errNum, "Roman function: number should be greater than zero or equal")
897
else if ($number ge 1000) then
898
fn:concat("M", excel:roman($number - 1000))
899
else if ($number ge 900) then
900
fn:concat("CM", excel:roman($number - 900))
901
else if ($number ge 800) then
902
fn:concat("DCCC", excel:roman($number - 800))
903
else if ($number ge 700) then
904
fn:concat("DCC", excel:roman($number - 700))
905
else if ($number ge 600) then
906
fn:concat("DC", excel:roman($number - 600))
907
else if ($number ge 500) then
908
fn:concat("D", excel:roman($number - 500))
909
else if ($number ge 400) then
910
fn:concat("CD", excel:roman($number - 400))
911
else if ($number ge 300) then
912
fn:concat("CCC", excel:roman($number - 300))
913
else if ($number ge 200) then
914
fn:concat("CC", excel:roman($number - 200))
915
else if ($number ge 100) then
916
fn:concat("C", excel:roman($number - 100))
917
else if ($number ge 90) then
918
fn:concat("XC", excel:roman($number - 90))
919
else if ($number ge 80) then
920
fn:concat("LXXX", excel:roman($number - 80))
921
else if ($number ge 70) then
922
fn:concat("LXX", excel:roman($number - 70))
923
else if ($number ge 60) then
924
fn:concat("LX", excel:roman($number - 60))
925
else if ($number ge 50) then
926
fn:concat("L", excel:roman($number - 50))
927
else if ($number ge 40) then
928
fn:concat("XL", excel:roman($number - 40))
929
else if ($number ge 30) then
930
fn:concat("XXX", excel:roman($number - 30))
931
else if ($number ge 20) then
932
fn:concat("XX", excel:roman($number - 20))
933
else if ($number ge 10) then
934
fn:concat("X", excel:roman($number - 10))
935
else if ($number eq 9) then
937
else if ($number eq 8) then
939
else if ($number eq 7) then
941
else if ($number eq 6) then
943
else if ($number eq 5) then
945
else if ($number eq 4) then
947
else if ($number eq 3) then
949
else if ($number eq 2) then
951
else if ($number eq 1) then
2
: Copyright 2006-2009 The FLWOR Foundation.
4
: Licensed under the Apache License, Version 2.0 (the "License");
5
: you may not use this file except in compliance with the License.
6
: You may obtain a copy of the License at
8
: http://www.apache.org/licenses/LICENSE-2.0
10
: Unless required by applicable law or agreed to in writing, software
11
: distributed under the License is distributed on an "AS IS" BASIS,
12
: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
: See the License for the specific language governing permissions and
14
: limitations under the License.
18
: This is a library module offering a part of the set of functions
19
: defined by Microsoft Excel 2003.
21
: @see <a href="http://office.microsoft.com/en-us/excel/CH062528291033.aspx"
22
: target="_blank">Excel 2003 Documentation: Math Functions</a>
24
: @spec XQuery Specification: January 2007
25
: @author Daniel Turcanu
28
module namespace excel = "http://www.zorba-xquery.com/modules/excel/math" ;
31
: Use excel-err module functions for throwing errors.
33
import module namespace excel-err="http://www.zorba-xquery.com/modules/excel/errors";
36
: Checks if the xs:anyAtomicType argument is actually a numeric type
37
: or can be converted to numeric.
39
: @param $value Parameter to be checked.
40
: @return true if the value can be casted to numeric.
42
declare function excel:is-a-number($value as xs:anyAtomicType) as xs:boolean
44
fn:string(fn:number($value)) ne 'NaN'
48
: Cast the xs:anyAtomicType to a numeric type.
49
: If the value is already of a numeric type then nothing is changed.
50
: Otherwise the value is casted to the numeric type that is most appropriate.
52
: @param $number The parameter can be a number, string, boolean value.
53
: @return The casted value.
54
: @error XQP0021(errValue) if the value cannot be casted to numeric type.
56
declare function excel:cast-as-numeric($number as xs:anyAtomicType) as xs:anyAtomicType
59
case xs:double return $number
60
case xs:decimal return $number
61
case xs:double return $number
62
case xs:float return $number
64
if ($number castable as xs:integer) then
66
else if ($number castable as xs:decimal) then
68
else if ($number castable as xs:double) then
71
fn:error($excel-err:errValue, "Provided value is not a number", $number)
74
(: ---------------- Excel Math functions ----------------------- :)
77
: Compute the abs of a numeric value.
78
: The value can also be a string and it will be casted to the appropriate numeric first.
80
: @see http://office.microsoft.com/en-us/excel/HP052089781033.aspx
81
: @param $arg The parameter can be a number, string, boolean value.
82
: @return The abs value as a numeric type.
83
: @error XQP0021(errValue) if arg cannot be casted to numeric type.
84
: @example rbkt/Queries/zorba/excel/math/abs1.xq
85
: @example rbkt/Queries/zorba/excel/math/abs2.xq
86
: @example rbkt/Queries/zorba/excel/math/abs3.xq
87
: @example rbkt/Queries/zorba/excel/math/abs4.xq
88
: @example rbkt/Queries/zorba/excel/math/abs5.xq
89
: @example rbkt/Queries/zorba/excel/math/abs6.xq
91
declare function excel:abs($arg as xs:anyAtomicType) as xs:anyAtomicType
93
fn:abs(excel:cast-as-numeric($arg))
97
: Returns number rounded up, away from zero, to the nearest multiple of significance.
98
: Significance must have the same sign as number.
99
: Number and significance must be of a numeric type or castable to numeric.
100
: Significance must not be zero.
102
: @see http://office.microsoft.com/en-us/excel/HP052090071033.aspx
103
: @param $number The value you want to round.
104
: @param $significance The multiple to which you want to round.
105
: @return The rounded value.
106
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
107
: @error XQP0021(errNum) if significance is zero or it doesn't have the same sign as number.
108
: @example rbkt/Queries/zorba/excel/math/ceiling1.xq
109
: @example rbkt/Queries/zorba/excel/math/ceiling2.xq
110
: @example rbkt/Queries/zorba/excel/math/ceiling3.xq
111
: @example rbkt/Queries/zorba/excel/math/ceiling4.xq
112
: @example rbkt/Queries/zorba/excel/math/ceiling5.xq
113
: @example rbkt/Queries/zorba/excel/math/ceiling6.xq
114
: @example rbkt/Queries/zorba/excel/math/ceiling7.xq
116
declare function excel:ceiling(
117
$number as xs:anyAtomicType,
118
$significance as xs:anyAtomicType) as xs:anyAtomicType
120
let $num := excel:cast-as-numeric($number)
121
let $sig := excel:cast-as-numeric($significance)
124
fn:error($excel-err:errNum, "Ceiling function does not accept significance 0")
125
else if ($num * $sig ge 0) then
126
fn:ceiling($num div $sig) * $sig
128
fn:error($excel-err:errNum, "Ceiling function: both arguments must have the same sign")
132
: Returns number rounded up to the nearest even integer.
133
: Regardless of the sign of number, a value is rounded up when adjusted away from zero.
135
: @see http://office.microsoft.com/en-us/excel/HP052090801033.aspx
136
: @param $number The value to round.
137
: @return The rounded value casted as numeric type.
138
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
139
: @example rbkt/Queries/zorba/excel/math/even1.xq
140
: @example rbkt/Queries/zorba/excel/math/even2.xq
141
: @example rbkt/Queries/zorba/excel/math/even3.xq
142
: @example rbkt/Queries/zorba/excel/math/even4.xq
143
: @example rbkt/Queries/zorba/excel/math/even5.xq
144
: @example rbkt/Queries/zorba/excel/math/even6.xq
146
declare function excel:even($number as xs:anyAtomicType) as xs:anyAtomicType
148
let $num := excel:cast-as-numeric($number)
153
let $intnum := excel:ceiling($num, excel:sign($num))
155
if ($intnum mod 2 ne 0) then
156
if ($intnum gt 0) then
165
: Function for computing factorial.
166
: This function should not be used outside this module.
167
: This recursive function computes: number * fact(number-1)
169
: @param $intnum A positive integer.
170
: @return The factorial of intnum.
172
declare %private function excel:fact-integer($intnum as xs:integer) as xs:integer
174
if ($intnum = 1) then
177
$intnum * excel:fact-integer($intnum - 1)
181
: Returns the factorial of a number.
183
: @see http://office.microsoft.com/en-us/excel/HP052090841033.aspx
184
: @param $number The nonnegative number you want the factorial of.
185
: If number is not an integer, it is truncated.
186
: @return Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
187
: @error XQP0021(errNum) if the number is smaller than zero
188
: @example rbkt/Queries/zorba/excel/math/fact1.xq
189
: @example rbkt/Queries/zorba/excel/math/fact2.xq
190
: @example rbkt/Queries/zorba/excel/math/fact3.xq
191
: @example rbkt/Queries/zorba/excel/math/fact4.xq
192
: @example rbkt/Queries/zorba/excel/math/fact5.xq
194
declare function excel:fact($number as xs:anyAtomicType) as xs:integer
196
let $num := excel:cast-as-numeric($number) return
201
fn:error($excel-err:errNum, "Fact function does not accept numbers less than zero")
203
excel:fact-integer(xs:integer($num))
207
: Rounds number down, toward zero, to the nearest multiple of significance.
208
: Significance must have the same sign as number.
210
: @see http://office.microsoft.com/en-us/excel/HP052090941033.aspx
211
: @param $number The value you want to round. The value is casted to numeric.
212
: @param $significance The multiple to which you want to round.
213
: @return The rounded value as numeric type.
214
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
215
: @error XQP0021(errNum) if significance is zero or it doesn't have the same sign as number.
216
: @example rbkt/Queries/zorba/excel/math/floor1.xq
217
: @example rbkt/Queries/zorba/excel/math/floor2.xq
218
: @example rbkt/Queries/zorba/excel/math/floor3.xq
219
: @example rbkt/Queries/zorba/excel/math/floor4.xq
220
: @example rbkt/Queries/zorba/excel/math/floor5.xq
222
declare function excel:floor(
223
$number as xs:anyAtomicType,
224
$significance as xs:anyAtomicType) as xs:anyAtomicType
226
let $num := excel:cast-as-numeric($number)
227
let $sig := excel:cast-as-numeric($significance)
230
fn:error($excel-err:errNum, "Floor function does not accept significance 0")
231
else if ($num * $sig ge 0) then
232
fn:floor($num div $sig) * $sig
234
fn:error($excel-err:errNum, "Floor function: both arguments must have the same sign")
238
: Rounds a number down to the nearest integer.
239
: Positive numbers are rounded toward zero, negative numbers are rounded away from zero.
241
: @see http://office.microsoft.com/en-us/excel/HP052091421033.aspx
242
: @param $number The value to be rounded.
243
: @return The rounded integer.
244
: @error XQP0021(errValue) if parameter cannot be casted to numeric type
245
: @example rbkt/Queries/zorba/excel/math/int1.xq
246
: @example rbkt/Queries/zorba/excel/math/int2.xq
247
: @example rbkt/Queries/zorba/excel/math/int3.xq
248
: @example rbkt/Queries/zorba/excel/math/int4.xq
250
declare function excel:int($number as xs:anyAtomicType) as xs:integer
252
xs:integer(fn:floor(excel:cast-as-numeric($number)))
256
: Returns the remainder after number is divided by divisor.
257
: The result has the same sign as divisor.
259
: @see http://office.microsoft.com/en-us/excel/HP052091821033.aspx
260
: @param $number The number for which you want to find the remainder.
261
: @param $divisor The number by which you want to divide number.
262
: This cannot be zero.
263
: @return The remainder from division as numeric type.
264
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
265
: @error XQP0021(errDiv0) if divisor is zero after casting to numeric.
266
: @example rbkt/Queries/zorba/excel/math/mod1.xq
267
: @example rbkt/Queries/zorba/excel/math/mod2.xq
268
: @example rbkt/Queries/zorba/excel/math/mod3.xq
269
: @example rbkt/Queries/zorba/excel/math/mod4.xq
271
declare function excel:mod(
272
$number as xs:anyAtomicType,
273
$divisor as xs:anyAtomicType) as xs:anyAtomicType
275
let $num := excel:cast-as-numeric($number)
276
let $div := excel:cast-as-numeric($divisor) return
278
fn:error($excel-err:errDiv0, "Mod operator: divide by 0")
280
let $result := $num mod $div
282
if ($result * $div lt 0) then
289
: Returns number rounded up to the nearest odd integer, away from zero.
291
: @see http://office.microsoft.com/en-us/excel/HP052092031033.aspx
292
: @param $number The value to round.
293
: @return The odd integer.
294
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
295
: @example rbkt/Queries/zorba/excel/math/odd1.xq
296
: @example rbkt/Queries/zorba/excel/math/odd2.xq
297
: @example rbkt/Queries/zorba/excel/math/odd3.xq
298
: @example rbkt/Queries/zorba/excel/math/odd4.xq
299
: @example rbkt/Queries/zorba/excel/math/odd5.xq
300
: @example rbkt/Queries/zorba/excel/math/odd6.xq
302
declare function excel:odd($number as xs:anyAtomicType) as xs:integer
304
let $num := excel:cast-as-numeric($number) return
308
let $intnum := excel:ceiling($num, excel:sign($num))
310
if ($intnum mod 2 eq 0) then
311
if ($intnum ge 0) then
312
($intnum + 1) cast as xs:integer
314
($intnum - 1) cast as xs:integer
316
$intnum cast as xs:integer
320
: Return the value of PI as decimal with 15 digits.
322
: @see http://office.microsoft.com/en-us/excel/HP052092141033.aspx
323
: @return The value of PI with 15 digits.
325
declare function excel:pi() as xs:decimal
331
: Returns the result of a number raised to a power.
332
: The result is computed through successive multiplications.
334
: @see http://office.microsoft.com/en-us/excel/HP052092171033.aspx
335
: @param $number The base number.
336
: @param $power The exponent as integer (cannot be floating point like in Excel).
337
: @return The result as numeric type.
338
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
339
: @error XQP0021(errValue) if power is smaller than zero.
340
: @example rbkt/Queries/zorba/excel/math/power1.xq
341
: @example rbkt/Queries/zorba/excel/math/power3.xq
342
: @example rbkt/Queries/zorba/excel/math/power4.xq
343
: @example rbkt/Queries/zorba/excel/math/power5.xq
345
declare function excel:power(
346
$number as xs:anyAtomicType,
347
$power as xs:integer) as xs:anyAtomicType
349
let $num := excel:cast-as-numeric($number)
351
if ($power lt 0) then
352
fn:error($excel-err:errValue, "Power function: power must be greater or equal than zero")
353
else if ($power eq 0) then
355
else if ($power = 1) then
358
let $retval := excel:power($num, $power idiv 2)
360
$retval * $retval * excel:power($num, $power mod 2)
364
: Function for product.
365
: This function should not be used outside this module.
366
: Multiplies all numbers in the sequence.
368
: @param $numbers The list of arguments to be casted to numeric and multiplied.
369
: @return The multiplication result as numeric type.
370
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
372
declare %private function excel:product-internal($numbers as xs:anyAtomicType*) as xs:anyAtomicType
374
if (fn:empty($numbers)) then
377
let $x := excel:cast-as-numeric($numbers[1])
379
$x * excel:product-internal(fn:subsequence($numbers, 2))
383
: Multiplies all the numbers given as arguments and returns the product.
385
: @see http://office.microsoft.com/en-us/excel/HP052092231033.aspx
386
: @param $numbers The sequence of arguments convertable to numeric types.
387
: The sequence can be of any length.
388
: @return The multiplication result as numeric type.
389
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
390
: @example rbkt/Queries/zorba/excel/math/product1.xq
391
: @example rbkt/Queries/zorba/excel/math/product2.xq
392
: @example rbkt/Queries/zorba/excel/math/product3.xq
393
: @example rbkt/Queries/zorba/excel/math/product4.xq
394
: @example rbkt/Queries/zorba/excel/math/product5.xq
396
declare function excel:product($numbers as xs:anyAtomicType*) as xs:anyAtomicType
398
if (fn:empty($numbers)) then
401
excel:product-internal($numbers)
405
: Returns the integer portion of a division.
407
: @see http://office.microsoft.com/en-us/excel/HP052092271033.aspx
408
: @param $numerator The divident.
409
: @param $denominator The divisor. It cannot be zero.
410
: @return The result value as numeric type.
411
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
412
: @error XQP0021(div0) if denominator casted as numeric type has value zero.
413
: @example rbkt/Queries/zorba/excel/math/quotient1.xq
414
: @example rbkt/Queries/zorba/excel/math/quotient2.xq
415
: @example rbkt/Queries/zorba/excel/math/quotient3.xq
416
: @example rbkt/Queries/zorba/excel/math/quotient4.xq
418
declare function excel:quotient(
419
$numerator as xs:anyAtomicType,
420
$denominator as xs:anyAtomicType) as xs:integer
422
let $numer := excel:cast-as-numeric($numerator)
423
let $denom := excel:cast-as-numeric($denominator)
425
if ($denom eq 0) then
426
fn:error($excel-err:errDiv0, "Quotient function: divide by 0")
428
xs:integer($numer div $denom)
432
: Rounds a number to a specified number of digits.
433
: If precision is greater than 0 (zero), then number is rounded
434
: to the specified number of decimal places.
435
: If num_digits is 0, then number is rounded to the nearest integer.
436
: If num_digits is less than 0, then number is rounded to the left of the decimal point.
437
: The 0.5 is rounded away from zero.
439
: @see http://office.microsoft.com/en-us/excel/HP052092391033.aspx
440
: @param $number The number to round, castable to a numeric type.
441
: @param $precision The number of decimal places to keep.
442
: @return The rounded number as numeric type.
443
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
444
: @example rbkt/Queries/zorba/excel/math/round1.xq
445
: @example rbkt/Queries/zorba/excel/math/round2.xq
446
: @example rbkt/Queries/zorba/excel/math/round3.xq
447
: @example rbkt/Queries/zorba/excel/math/round4.xq
449
declare function excel:round(
450
$number as xs:anyAtomicType,
451
$precision as xs:integer) as xs:anyAtomicType
453
let $num := excel:cast-as-numeric($number)
455
if ($precision ge 0) then
456
let $exp_prec := excel:power(10, $precision)
459
fn:floor($num * $exp_prec + 0.5) div $exp_prec
461
-fn:floor(-$num * $exp_prec + 0.5) div $exp_prec
463
let $exp_prec := excel:power(10, -$precision)
466
fn:floor($num div $exp_prec + 0.5) * $exp_prec
468
-fn:floor(-$num div $exp_prec + 0.5) * $exp_prec
472
: Rounds a number down, toward zero.
473
: If num_digits is greater than 0 (zero), then number is rounded down
474
: to the specified number of decimal places.
475
: If num_digits is 0, then number is rounded down to the nearest integer.
476
: If num_digits is less than 0, then number is rounded down to the left of the decimal point.
478
: @see http://office.microsoft.com/en-us/excel/HP052092411033.aspx
479
: @param $number The number to round, castable to numeric type.
480
: @param $precision The number of decimal places to keep.
481
: @return the truncated number toward zero, as numeric type.
482
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
483
: @example rbkt/Queries/zorba/excel/math/rounddown1.xq
484
: @example rbkt/Queries/zorba/excel/math/rounddown2.xq
485
: @example rbkt/Queries/zorba/excel/math/rounddown3.xq
486
: @example rbkt/Queries/zorba/excel/math/rounddown4.xq
487
: @example rbkt/Queries/zorba/excel/math/rounddown5.xq
489
declare function excel:rounddown(
490
$number as xs:anyAtomicType,
491
$precision as xs:integer) as xs:anyAtomicType
493
let $num := excel:cast-as-numeric($number)
495
if ($precision ge 0) then
496
let $exp_prec := excel:power(10, $precision)
499
fn:floor($num * $exp_prec) div $exp_prec
501
-fn:floor(-$num * $exp_prec) div $exp_prec
503
let $exp_prec := excel:power(10, -$precision)
506
fn:floor($num div $exp_prec) * $exp_prec
508
-fn:floor(-$num div $exp_prec) * $exp_prec
512
: Rounds a number up, away from 0 (zero).
513
: If num_digits is greater than 0 (zero), then number is rounded down
514
: to the specified number of decimal places.
515
: If num_digits is 0, then number is rounded down to the nearest integer.
516
: If num_digits is less than 0, then number is rounded down to the left of the decimal point.
518
: @see http://office.microsoft.com/en-us/excel/HP052092421033.aspx
519
: @param $number The number to round, castable to numeric type.
520
: @param $precision The number of decimal places to keep.
521
: @return The truncated number away from zero, as numeric type.
522
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
523
: @example rbkt/Queries/zorba/excel/math/roundup1.xq
524
: @example rbkt/Queries/zorba/excel/math/roundup2.xq
525
: @example rbkt/Queries/zorba/excel/math/roundup3.xq
526
: @example rbkt/Queries/zorba/excel/math/roundup4.xq
527
: @example rbkt/Queries/zorba/excel/math/roundup5.xq
529
declare function excel:roundup(
530
$number as xs:anyAtomicType,
531
$precision as xs:integer) as xs:anyAtomicType
533
let $num := excel:cast-as-numeric($number)
535
if ($precision ge 0) then
536
let $exp_prec := excel:power(10, $precision)
539
fn:ceiling($num * $exp_prec) div $exp_prec
541
-fn:ceiling(-$num * $exp_prec) div $exp_prec
543
let $exp_prec := excel:power(10, -$precision)
546
fn:ceiling($num div $exp_prec) * $exp_prec
548
-fn:ceiling(-$num div $exp_prec) * $exp_prec
552
: Determines the sign of a number.
553
: Returns 1 if the number is positive, zero (0) if the number is 0,
554
: and -1 if the number is negative.
556
: @see http://office.microsoft.com/en-us/excel/HP052092551033.aspx
557
: @param $number The argument castable to numeric type.
558
: @return The sign as (-1, 0, 1).
559
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
560
: @example rbkt/Queries/zorba/excel/math/sign1.xq
561
: @example rbkt/Queries/zorba/excel/math/sign2.xq
562
: @example rbkt/Queries/zorba/excel/math/sign3.xq
564
declare function excel:sign($number as xs:anyAtomicType) as xs:integer
566
let $num := excel:cast-as-numeric($number)
570
else if ($num gt 0) then
577
: Adds all the numbers in the sequence.
579
: @see http://office.microsoft.com/en-us/excel/HP052092901033.aspx
580
: @param $numbers The sequence of arguments castable to numeric types.
581
: The sequence can be of any length.
582
: @return The sum as numeric type.
583
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
584
: @example rbkt/Queries/zorba/excel/math/sum1.xq
585
: @example rbkt/Queries/zorba/excel/math/sum2.xq
586
: @example rbkt/Queries/zorba/excel/math/sum3.xq
588
declare function excel:sum($numbers as xs:anyAtomicType*) as xs:anyAtomicType
590
if (fn:empty($numbers)) then
593
let $x := excel:cast-as-numeric($numbers[1])
595
$x + excel:sum(fn:subsequence($numbers,2))
599
: Truncates a number to an integer by removing the fractional part of the number.
601
: @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx
602
: @param $number The argument castable to numeric type.
603
: @return The integer value.
604
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
605
: @example rbkt/Queries/zorba/excel/math/trunc1.xq
606
: @example rbkt/Queries/zorba/excel/math/trunc2.xq
608
declare function excel:trunc($number as xs:anyAtomicType ) as xs:integer
610
xs:integer(excel:cast-as-numeric($number))
614
: Truncates a number down to precision.
615
: This behaves exactly like rounddown.
617
: @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx
618
: @param $number The argument castable to numeric type.
619
: @param $precision The number of decimal places to keep .
620
: @return The integer value.
621
: @error XQP0021(errValue) if parameter cannot be casted to numeric type.
622
: @example rbkt/Queries/zorba/excel/math/trunc3.xq
624
declare function excel:trunc(
625
$number as xs:anyAtomicType,
626
$precision as xs:integer) as xs:anyAtomicType
628
excel:rounddown(excel:cast-as-numeric($number), $precision)
632
: Helper function.<br/>
633
: Sorts a sequence of numbers or arguments castable to numeric.
634
: It first casts all arguments to numeric and then sorts ascending.
636
: @param $numbers The sequence of arguments castable to numeric.
637
: @return The sorted sequence as numeric types.
638
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
640
declare function excel:sort-numbers($numbers as xs:anyAtomicType*) as xs:anyAtomicType*
642
let $sorted-numbers :=
644
for $number in $numbers
645
let $num := excel:cast-as-numeric($number)
649
return $sorted-numbers
653
: Converts radians into degrees.
655
: @see http://office.microsoft.com/en-us/excel/HP052090561033.aspx
656
: @param $radian The value in radians.
657
: @return The value in degrees 0 .. 360 or 0 .. -360.
659
declare function excel:degrees($radian as xs:double) as xs:integer
661
xs:integer(($radian * 180 div excel:pi()) mod 360)
665
: Returns the double factorial of a number.
666
: Computes the double factorial of n as n(n-2)(n-4)...
668
: @see http://office.microsoft.com/en-us/excel/HP052090851033.aspx
669
: @param $number The positive integer value.
670
: @return The result as integer.
671
: @error XQP0021(errNum) if the number is negative.
672
: @example rbkt/Queries/zorba/excel/math/priority1/factdouble1.xq
673
: @example rbkt/Queries/zorba/excel/math/priority1/factdouble2.xq
675
declare function excel:factdouble($number as xs:integer) as xs:integer
677
if ($number lt 0) then
678
fn:error($excel-err:errNum, "Factdouble function: number should be greater than zero or equal")
679
else if ($number eq 1) then
681
else if ($number eq 2) then
684
$number * excel:factdouble($number - 2)
688
: Function for computing GCD.
689
: This function should not be used outside this module.
690
: It calculates the minimum value from a sequence of positive integers,
691
: not taking into account the zero value.
693
: @param $numbers The sequence of positive integers.
694
: @return The minimum value. If the sequence contains only zero values, then zero is returned.
696
declare %private function excel:min-without-zero($numbers as xs:integer+) as xs:integer
698
if (fn:count($numbers) eq 1) then
701
let $min-other := excel:min-without-zero(fn:subsequence($numbers, 2))
703
if ($numbers[1] eq 0) then
705
else if ($min-other eq 0) then
707
else if ($numbers[1] lt $min-other) then
714
: Function for computing GCD.
715
: This function should not be used outside this module.
716
: Checks if all integer numbers from a sequence divide exactly to a divident.
718
: @param $numbers The positive integers.
719
: @param $divident The divident to be tried.
720
: @return true if the numbers divide exactly.
722
declare %private function excel:try-exact-divide(
723
$numbers as xs:integer*,
724
$divident as xs:integer) as xs:boolean
726
if (fn:empty($numbers)) then
729
if ($numbers[1] mod $divident ne 0) then
732
excel:try-exact-divide(fn:subsequence($numbers, 2), $divident)
736
: Function for computing GCD.
737
: This function should not be used outside this module.
738
: This function iterates through possible divisors and checks if the sequence
739
: divides exactly to any of those. It starts from the minimum value from the
740
: sequence and searches downwards.
742
: @param $numbers The sequence of positive integers.
743
: @param $min-nonzero The minimum value of numbers sequence, excluding the zero value.
744
: @param $iteration Which iteration is it. It starts from 1 and continues
746
: @return The greatest common divisor if found, or 1 if not found.
748
declare %private function excel:iterate-all-gcd(
749
$numbers as xs:integer*,
750
$min-nonzero as xs:integer,
751
$iteration as xs:integer) as xs:integer
753
if ($min-nonzero mod $iteration eq 0) then
754
if (excel:try-exact-divide($numbers, $min-nonzero idiv $iteration)) then
755
$min-nonzero idiv $iteration
757
excel:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1)
759
if ($iteration > $min-nonzero idiv 2) then
762
excel:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1)
766
: Returns the greatest common divisor GCD of a sequence of integers.
767
: The sequence can have one or more positive integers.
769
: @see http://office.microsoft.com/en-us/excel/HP052091041033.aspx
770
: @param $numbers The sequence of positive integers.
771
: @return The GCD as integer.
772
: @error XQP0021(errNum) if any number is smaller than zero.
773
: @example rbkt/Queries/zorba/excel/math/priority1/gcd1.xq
774
: @example rbkt/Queries/zorba/excel/math/priority1/gcd2.xq
775
: @example rbkt/Queries/zorba/excel/math/priority1/gcd3.xq
776
: @example rbkt/Queries/zorba/excel/math/priority1/gcd4.xq
777
: @example rbkt/Queries/zorba/excel/math/priority1/gcd5.xq
778
: @example rbkt/Queries/zorba/excel/math/priority1/gcd6.xq
779
: @example rbkt/Queries/zorba/excel/math/priority1/gcd7.xq
780
: @example rbkt/Queries/zorba/excel/math/priority1/gcd8.xq
782
declare function excel:gcd($numbers as xs:integer+) as xs:integer
784
if (fn:count($numbers) = 1) then
787
let $minval := excel:min-without-zero($numbers)
789
if ($minval lt 0) then
790
fn:error($excel-err:errNum, "gcd function: numbers should be greater than zero or equal")
791
else if ($minval eq 0) then
794
excel:iterate-all-gcd($numbers, $minval, 1)
798
: Returns the least common multiple of integers.<br/>
799
: LCM for two numbers is computed by multiplying them and dividing with GCD. <br/>
800
: The function is applied recursively replacing the first two numbers in the sequence with their LCM.
802
: @see http://office.microsoft.com/en-us/excel/HP052091521033.aspx
803
: @param $numbers The sequence of one or more positive integers.
804
: @return The LCM as integer.
805
: @error XQP0021(errNum) if any number is smaller than zero.
806
: @example rbkt/Queries/zorba/excel/math/priority1/lcm1.xq
807
: @example rbkt/Queries/zorba/excel/math/priority1/lcm2.xq
808
: @example rbkt/Queries/zorba/excel/math/priority1/lcm3.xq
809
: @example rbkt/Queries/zorba/excel/math/priority1/lcm4.xq
810
: @example rbkt/Queries/zorba/excel/math/priority1/lcm5.xq
812
declare function excel:lcm($numbers as xs:integer+) as xs:integer
814
if(count($numbers) eq 1) then
817
if(count($numbers) eq 2) then
818
let $product := excel:product(fn:distinct-values($numbers))
820
if ($product eq 0) then
823
$product idiv excel:gcd($numbers)
825
excel:lcm((excel:lcm(($numbers[1], $numbers[2])), subsequence($numbers, 3)))
830
: Returns a number rounded to the desired multiple.
831
: MROUND rounds up, away from zero, if the remainder of dividing number by multiple
832
: is greater than or equal to half the value of multiple.
833
: MROUND is computed through floor function.
835
: @see http://office.microsoft.com/en-us/excel/HP052091851033.aspx
836
: @param $number The value to round, castable to numeric type.
837
: @param $multiple The multiple to which you want to round number.
838
: @return The rounded number up to the desired multiple.
839
: @error XQP0021(errValue) if parameters cannot be casted to numeric type.
840
: @example rbkt/Queries/zorba/excel/math/priority1/mround1.xq
841
: @example rbkt/Queries/zorba/excel/math/priority1/mround2.xq
842
: @example rbkt/Queries/zorba/excel/math/priority1/mround3.xq
844
declare function excel:mround(
845
$number as xs:anyAtomicType,
846
$multiple as xs:anyAtomicType) as xs:anyAtomicType
848
let $num := excel:cast-as-numeric($number)
849
let $mul := excel:cast-as-numeric($multiple)
850
let $floor := excel:floor($num, $mul) return
852
if (($num - $floor) ge ($mul div 2)) then
857
if ((-$num + $floor) ge (-$mul div 2)) then
864
: Converts degrees to radians.
866
: @see http://office.microsoft.com/en-us/excel/HP052092281033.aspx
867
: @param $degree An angle in degrees that you want to convert.
868
: @return The value in radians.
869
: @example rbkt/Queries/zorba/excel/math/priority1/radians1.xq
870
: @example rbkt/Queries/zorba/excel/math/priority1/radians2.xq
871
: @example rbkt/Queries/zorba/excel/math/priority1/radians3.xq
873
declare function excel:radians($degree as xs:integer) as xs:decimal
875
($degree mod 360) div 180.0 * excel:pi()
879
: Converts an arabic numeral to roman, as text.
880
: Only the clasic format is supported (out of all formats Excel requires).<br/>
881
: M is the largest digit, it represents 1000.
882
: Numbers bigger than 2000 will be represented by a sequence of "M".<br/>
883
: D = 500, C = 100, L = 50, X = 10, V = 5, I = 1.
885
: @see http://office.microsoft.com/en-us/excel/HP052092381033.aspx
886
: @param $number A positive integer.
887
: @return The roman string representation.
888
: @error XQP0021(errNum) if the input integer is negative
889
: @example rbkt/Queries/zorba/excel/math/priority1/roman1.xq
890
: @example rbkt/Queries/zorba/excel/math/priority1/roman2.xq
891
: @example rbkt/Queries/zorba/excel/math/priority1/roman3.xq
893
declare function excel:roman($number as xs:integer) as xs:string
895
if ($number lt 0) then
896
fn:error($excel-err:errNum, "Roman function: number should be greater than zero or equal")
897
else if ($number ge 1000) then
898
fn:concat("M", excel:roman($number - 1000))
899
else if ($number ge 900) then
900
fn:concat("CM", excel:roman($number - 900))
901
else if ($number ge 800) then
902
fn:concat("DCCC", excel:roman($number - 800))
903
else if ($number ge 700) then
904
fn:concat("DCC", excel:roman($number - 700))
905
else if ($number ge 600) then
906
fn:concat("DC", excel:roman($number - 600))
907
else if ($number ge 500) then
908
fn:concat("D", excel:roman($number - 500))
909
else if ($number ge 400) then
910
fn:concat("CD", excel:roman($number - 400))
911
else if ($number ge 300) then
912
fn:concat("CCC", excel:roman($number - 300))
913
else if ($number ge 200) then
914
fn:concat("CC", excel:roman($number - 200))
915
else if ($number ge 100) then
916
fn:concat("C", excel:roman($number - 100))
917
else if ($number ge 90) then
918
fn:concat("XC", excel:roman($number - 90))
919
else if ($number ge 80) then
920
fn:concat("LXXX", excel:roman($number - 80))
921
else if ($number ge 70) then
922
fn:concat("LXX", excel:roman($number - 70))
923
else if ($number ge 60) then
924
fn:concat("LX", excel:roman($number - 60))
925
else if ($number ge 50) then
926
fn:concat("L", excel:roman($number - 50))
927
else if ($number ge 40) then
928
fn:concat("XL", excel:roman($number - 40))
929
else if ($number ge 30) then
930
fn:concat("XXX", excel:roman($number - 30))
931
else if ($number ge 20) then
932
fn:concat("XX", excel:roman($number - 20))
933
else if ($number ge 10) then
934
fn:concat("X", excel:roman($number - 10))
935
else if ($number eq 9) then
937
else if ($number eq 8) then
939
else if ($number eq 7) then
941
else if ($number eq 6) then
943
else if ($number eq 5) then
945
else if ($number eq 4) then
947
else if ($number eq 3) then
949
else if ($number eq 2) then
951
else if ($number eq 1) then