Search moodle.org's
Developer Documentation
Developer Documentation
See Release Notes
Long Term Support Release
- Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
- Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
- PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5 use Matrix\Exception as MatrixException; 6 use Matrix\Matrix; 7 8 class MathTrig 9 { 10 // 11 // Private method to return an array of the factors of the input value 12 // 13 private static function factors($value) 14 { 15 $startVal = floor(sqrt($value)); 16 17 $factorArray = []; 18 for ($i = $startVal; $i > 1; --$i) { 19 if (($value % $i) == 0) { 20 $factorArray = array_merge($factorArray, self::factors($value / $i)); 21 $factorArray = array_merge($factorArray, self::factors($i)); 22 if ($i <= sqrt($value)) { 23 break; 24 } 25 } 26 } 27 if (!empty($factorArray)) { 28 rsort($factorArray); 29 30 return $factorArray; 31 } 32 33 return [(int) $value]; 34 } 35 36 private static function romanCut($num, $n) 37 { 38 return ($num - ($num % $n)) / $n; 39 } 40 41 /** 42 * ATAN2. 43 * 44 * This function calculates the arc tangent of the two variables x and y. It is similar to 45 * calculating the arc tangent of y รท x, except that the signs of both arguments are used 46 * to determine the quadrant of the result. 47 * The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a 48 * point with coordinates (xCoordinate, yCoordinate). The angle is given in radians between 49 * -pi and pi, excluding -pi. 50 * 51 * Note that the Excel ATAN2() function accepts its arguments in the reverse order to the standard 52 * PHP atan2() function, so we need to reverse them here before calling the PHP atan() function. 53 * 54 * Excel Function: 55 * ATAN2(xCoordinate,yCoordinate) 56 * 57 * @category Mathematical and Trigonometric Functions 58 * 59 * @param float $xCoordinate the x-coordinate of the point 60 * @param float $yCoordinate the y-coordinate of the point 61 * 62 * @return float the inverse tangent of the specified x- and y-coordinates 63 */ 64 public static function ATAN2($xCoordinate = null, $yCoordinate = null) 65 { 66 $xCoordinate = Functions::flattenSingleValue($xCoordinate); 67 $yCoordinate = Functions::flattenSingleValue($yCoordinate); 68 69 $xCoordinate = ($xCoordinate !== null) ? $xCoordinate : 0.0; 70 $yCoordinate = ($yCoordinate !== null) ? $yCoordinate : 0.0; 71 72 if (((is_numeric($xCoordinate)) || (is_bool($xCoordinate))) && 73 ((is_numeric($yCoordinate))) || (is_bool($yCoordinate))) { 74 $xCoordinate = (float) $xCoordinate; 75 $yCoordinate = (float) $yCoordinate; 76 77 if (($xCoordinate == 0) && ($yCoordinate == 0)) { 78 return Functions::DIV0(); 79 } 80 81 return atan2($yCoordinate, $xCoordinate); 82 } 83 84 return Functions::VALUE(); 85 } 86 87 /** 88 * CEILING. 89 * 90 * Returns number rounded up, away from zero, to the nearest multiple of significance. 91 * For example, if you want to avoid using pennies in your prices and your product is 92 * priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the 93 * nearest nickel. 94 * 95 * Excel Function: 96 * CEILING(number[,significance]) 97 * 98 * @category Mathematical and Trigonometric Functions 99 * 100 * @param float $number the number you want to round 101 * @param float $significance the multiple to which you want to round 102 * 103 * @return float Rounded Number 104 */ 105 public static function CEILING($number, $significance = null) 106 { 107 $number = Functions::flattenSingleValue($number); 108 $significance = Functions::flattenSingleValue($significance); 109 110 if (($significance === null) && 111 (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC)) { 112 $significance = $number / abs($number); 113 } 114 115 if ((is_numeric($number)) && (is_numeric($significance))) { 116 if (($number == 0.0) || ($significance == 0.0)) { 117 return 0.0; 118 } elseif (self::SIGN($number) == self::SIGN($significance)) { 119 return ceil($number / $significance) * $significance; 120 } 121 122 return Functions::NAN(); 123 } 124 125 return Functions::VALUE(); 126 } 127 128 /** 129 * COMBIN. 130 * 131 * Returns the number of combinations for a given number of items. Use COMBIN to 132 * determine the total possible number of groups for a given number of items. 133 * 134 * Excel Function: 135 * COMBIN(numObjs,numInSet) 136 * 137 * @category Mathematical and Trigonometric Functions 138 * 139 * @param int $numObjs Number of different objects 140 * @param int $numInSet Number of objects in each combination 141 * 142 * @return int Number of combinations 143 */ 144 public static function COMBIN($numObjs, $numInSet) 145 { 146 $numObjs = Functions::flattenSingleValue($numObjs); 147 $numInSet = Functions::flattenSingleValue($numInSet); 148 149 if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { 150 if ($numObjs < $numInSet) { 151 return Functions::NAN(); 152 } elseif ($numInSet < 0) { 153 return Functions::NAN(); 154 } 155 156 return round(self::FACT($numObjs) / self::FACT($numObjs - $numInSet)) / self::FACT($numInSet); 157 } 158 159 return Functions::VALUE(); 160 } 161 162 /** 163 * EVEN. 164 * 165 * Returns number rounded up to the nearest even integer. 166 * You can use this function for processing items that come in twos. For example, 167 * a packing crate accepts rows of one or two items. The crate is full when 168 * the number of items, rounded up to the nearest two, matches the crate's 169 * capacity. 170 * 171 * Excel Function: 172 * EVEN(number) 173 * 174 * @category Mathematical and Trigonometric Functions 175 * 176 * @param float $number Number to round 177 * 178 * @return int Rounded Number 179 */ 180 public static function EVEN($number) 181 { 182 $number = Functions::flattenSingleValue($number); 183 184 if ($number === null) { 185 return 0; 186 } elseif (is_bool($number)) { 187 $number = (int) $number; 188 } 189 190 if (is_numeric($number)) { 191 $significance = 2 * self::SIGN($number); 192 193 return (int) self::CEILING($number, $significance); 194 } 195 196 return Functions::VALUE(); 197 } 198 199 /** 200 * FACT. 201 * 202 * Returns the factorial of a number. 203 * The factorial of a number is equal to 1*2*3*...* number. 204 * 205 * Excel Function: 206 * FACT(factVal) 207 * 208 * @category Mathematical and Trigonometric Functions 209 * 210 * @param float $factVal Factorial Value 211 * 212 * @return int Factorial 213 */ 214 public static function FACT($factVal) 215 { 216 $factVal = Functions::flattenSingleValue($factVal); 217 218 if (is_numeric($factVal)) { 219 if ($factVal < 0) { 220 return Functions::NAN(); 221 } 222 $factLoop = floor($factVal); 223 if ((Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) && 224 ($factVal > $factLoop)) { 225 return Functions::NAN(); 226 } 227 228 $factorial = 1; 229 while ($factLoop > 1) { 230 $factorial *= $factLoop--; 231 } 232 233 return $factorial; 234 } 235 236 return Functions::VALUE(); 237 } 238 239 /** 240 * FACTDOUBLE. 241 * 242 * Returns the double factorial of a number. 243 * 244 * Excel Function: 245 * FACTDOUBLE(factVal) 246 * 247 * @category Mathematical and Trigonometric Functions 248 * 249 * @param float $factVal Factorial Value 250 * 251 * @return int Double Factorial 252 */ 253 public static function FACTDOUBLE($factVal) 254 { 255 $factLoop = Functions::flattenSingleValue($factVal); 256 257 if (is_numeric($factLoop)) { 258 $factLoop = floor($factLoop); 259 if ($factVal < 0) { 260 return Functions::NAN(); 261 } 262 $factorial = 1; 263 while ($factLoop > 1) { 264 $factorial *= $factLoop--; 265 --$factLoop; 266 } 267 268 return $factorial; 269 } 270 271 return Functions::VALUE(); 272 } 273 274 /** 275 * FLOOR. 276 * 277 * Rounds number down, toward zero, to the nearest multiple of significance. 278 * 279 * Excel Function: 280 * FLOOR(number[,significance]) 281 * 282 * @category Mathematical and Trigonometric Functions 283 * 284 * @param float $number Number to round 285 * @param float $significance Significance 286 * 287 * @return float Rounded Number 288 */ 289 public static function FLOOR($number, $significance = null) 290 { 291 $number = Functions::flattenSingleValue($number); 292 $significance = Functions::flattenSingleValue($significance); 293 294 if (($significance === null) && 295 (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC)) { 296 $significance = $number / abs($number); 297 } 298 299 if ((is_numeric($number)) && (is_numeric($significance))) { 300 if ($significance == 0.0) { 301 return Functions::DIV0(); 302 } elseif ($number == 0.0) { 303 return 0.0; 304 } elseif (self::SIGN($significance) == 1) { 305 return floor($number / $significance) * $significance; 306 } elseif (self::SIGN($number) == -1 && self::SIGN($significance) == -1) { 307 return floor($number / $significance) * $significance; 308 } 309 310 return Functions::NAN(); 311 } 312 313 return Functions::VALUE(); 314 } 315 316 private static function evaluateGCD($a, $b) 317 { 318 return $b ? self::evaluateGCD($b, $a % $b) : $a; 319 } 320 321 /** 322 * GCD. 323 * 324 * Returns the greatest common divisor of a series of numbers. 325 * The greatest common divisor is the largest integer that divides both 326 * number1 and number2 without a remainder. 327 * 328 * Excel Function: 329 * GCD(number1[,number2[, ...]]) 330 * 331 * @category Mathematical and Trigonometric Functions 332 * 333 * @param mixed ...$args Data values 334 * 335 * @return int Greatest Common Divisor 336 */ 337 public static function GCD(...$args) 338 { 339 $args = Functions::flattenArray($args); 340 // Loop through arguments 341 foreach (Functions::flattenArray($args) as $value) { 342 if (!is_numeric($value)) { 343 return Functions::VALUE(); 344 } elseif ($value < 0) { 345 return Functions::NAN(); 346 } 347 } 348 349 $gcd = (int) array_pop($args); 350 do { 351 $gcd = self::evaluateGCD($gcd, (int) array_pop($args)); 352 } while (!empty($args)); 353 354 return $gcd; 355 } 356 357 /** 358 * INT. 359 * 360 * Casts a floating point value to an integer 361 * 362 * Excel Function: 363 * INT(number) 364 * 365 * @category Mathematical and Trigonometric Functions 366 * 367 * @param float $number Number to cast to an integer 368 * 369 * @return int Integer value 370 */ 371 public static function INT($number) 372 { 373 $number = Functions::flattenSingleValue($number); 374 375 if ($number === null) { 376 return 0; 377 } elseif (is_bool($number)) { 378 return (int) $number; 379 } 380 if (is_numeric($number)) { 381 return (int) floor($number); 382 } 383 384 return Functions::VALUE(); 385 } 386 387 /** 388 * LCM. 389 * 390 * Returns the lowest common multiplier of a series of numbers 391 * The least common multiple is the smallest positive integer that is a multiple 392 * of all integer arguments number1, number2, and so on. Use LCM to add fractions 393 * with different denominators. 394 * 395 * Excel Function: 396 * LCM(number1[,number2[, ...]]) 397 * 398 * @category Mathematical and Trigonometric Functions 399 * 400 * @param mixed ...$args Data values 401 * 402 * @return int Lowest Common Multiplier 403 */ 404 public static function LCM(...$args) 405 { 406 $returnValue = 1; 407 $allPoweredFactors = []; 408 // Loop through arguments 409 foreach (Functions::flattenArray($args) as $value) { 410 if (!is_numeric($value)) { 411 return Functions::VALUE(); 412 } 413 if ($value == 0) { 414 return 0; 415 } elseif ($value < 0) { 416 return Functions::NAN(); 417 } 418 $myFactors = self::factors(floor($value)); 419 $myCountedFactors = array_count_values($myFactors); 420 $myPoweredFactors = []; 421 foreach ($myCountedFactors as $myCountedFactor => $myCountedPower) { 422 $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor, $myCountedPower); 423 } 424 foreach ($myPoweredFactors as $myPoweredValue => $myPoweredFactor) { 425 if (isset($allPoweredFactors[$myPoweredValue])) { 426 if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) { 427 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; 428 } 429 } else { 430 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; 431 } 432 } 433 } 434 foreach ($allPoweredFactors as $allPoweredFactor) { 435 $returnValue *= (int) $allPoweredFactor; 436 } 437 438 return $returnValue; 439 } 440 441 /** 442 * LOG_BASE. 443 * 444 * Returns the logarithm of a number to a specified base. The default base is 10. 445 * 446 * Excel Function: 447 * LOG(number[,base]) 448 * 449 * @category Mathematical and Trigonometric Functions 450 * 451 * @param float $number The positive real number for which you want the logarithm 452 * @param float $base The base of the logarithm. If base is omitted, it is assumed to be 10. 453 * 454 * @return float 455 */ 456 public static function logBase($number = null, $base = 10) 457 { 458 $number = Functions::flattenSingleValue($number); 459 $base = ($base === null) ? 10 : (float) Functions::flattenSingleValue($base); 460 461 if ((!is_numeric($base)) || (!is_numeric($number))) { 462 return Functions::VALUE(); 463 } 464 if (($base <= 0) || ($number <= 0)) { 465 return Functions::NAN(); 466 } 467 468 return log($number, $base); 469 } 470 471 /** 472 * MDETERM. 473 * 474 * Returns the matrix determinant of an array. 475 * 476 * Excel Function: 477 * MDETERM(array) 478 * 479 * @category Mathematical and Trigonometric Functions 480 * 481 * @param array $matrixValues A matrix of values 482 * 483 * @return float 484 */ 485 public static function MDETERM($matrixValues) 486 { 487 $matrixData = []; 488 if (!is_array($matrixValues)) { 489 $matrixValues = [[$matrixValues]]; 490 } 491 492 $row = $maxColumn = 0; 493 foreach ($matrixValues as $matrixRow) { 494 if (!is_array($matrixRow)) { 495 $matrixRow = [$matrixRow]; 496 } 497 $column = 0; 498 foreach ($matrixRow as $matrixCell) { 499 if ((is_string($matrixCell)) || ($matrixCell === null)) { 500 return Functions::VALUE(); 501 } 502 $matrixData[$row][$column] = $matrixCell; 503 ++$column; 504 } 505 if ($column > $maxColumn) { 506 $maxColumn = $column; 507 } 508 ++$row; 509 } 510 511 $matrix = new Matrix($matrixData); 512 if (!$matrix->isSquare()) { 513 return Functions::VALUE(); 514 } 515 516 try { 517 return $matrix->determinant(); 518 } catch (MatrixException $ex) { 519 return Functions::VALUE(); 520 } 521 } 522 523 /** 524 * MINVERSE. 525 * 526 * Returns the inverse matrix for the matrix stored in an array. 527 * 528 * Excel Function: 529 * MINVERSE(array) 530 * 531 * @category Mathematical and Trigonometric Functions 532 * 533 * @param array $matrixValues A matrix of values 534 * 535 * @return array 536 */ 537 public static function MINVERSE($matrixValues) 538 { 539 $matrixData = []; 540 if (!is_array($matrixValues)) { 541 $matrixValues = [[$matrixValues]]; 542 } 543 544 $row = $maxColumn = 0; 545 foreach ($matrixValues as $matrixRow) { 546 if (!is_array($matrixRow)) { 547 $matrixRow = [$matrixRow]; 548 } 549 $column = 0; 550 foreach ($matrixRow as $matrixCell) { 551 if ((is_string($matrixCell)) || ($matrixCell === null)) { 552 return Functions::VALUE(); 553 } 554 $matrixData[$row][$column] = $matrixCell; 555 ++$column; 556 } 557 if ($column > $maxColumn) { 558 $maxColumn = $column; 559 } 560 ++$row; 561 } 562 563 $matrix = new Matrix($matrixData); 564 if (!$matrix->isSquare()) { 565 return Functions::VALUE(); 566 } 567 568 if ($matrix->determinant() == 0.0) { 569 return Functions::NAN(); 570 } 571 572 try { 573 return $matrix->inverse()->toArray(); 574 } catch (MatrixException $ex) { 575 return Functions::VALUE(); 576 } 577 } 578 579 /** 580 * MMULT. 581 * 582 * @param array $matrixData1 A matrix of values 583 * @param array $matrixData2 A matrix of values 584 * 585 * @return array 586 */ 587 public static function MMULT($matrixData1, $matrixData2) 588 { 589 $matrixAData = $matrixBData = []; 590 if (!is_array($matrixData1)) { 591 $matrixData1 = [[$matrixData1]]; 592 } 593 if (!is_array($matrixData2)) { 594 $matrixData2 = [[$matrixData2]]; 595 } 596 597 try { 598 $rowA = 0; 599 foreach ($matrixData1 as $matrixRow) { 600 if (!is_array($matrixRow)) { 601 $matrixRow = [$matrixRow]; 602 } 603 $columnA = 0; 604 foreach ($matrixRow as $matrixCell) { 605 if ((!is_numeric($matrixCell)) || ($matrixCell === null)) { 606 return Functions::VALUE(); 607 } 608 $matrixAData[$rowA][$columnA] = $matrixCell; 609 ++$columnA; 610 } 611 ++$rowA; 612 } 613 $matrixA = new Matrix($matrixAData); 614 $rowB = 0; 615 foreach ($matrixData2 as $matrixRow) { 616 if (!is_array($matrixRow)) { 617 $matrixRow = [$matrixRow]; 618 } 619 $columnB = 0; 620 foreach ($matrixRow as $matrixCell) { 621 if ((!is_numeric($matrixCell)) || ($matrixCell === null)) { 622 return Functions::VALUE(); 623 } 624 $matrixBData[$rowB][$columnB] = $matrixCell; 625 ++$columnB; 626 } 627 ++$rowB; 628 } 629 $matrixB = new Matrix($matrixBData); 630 631 if ($columnA != $rowB) { 632 return Functions::VALUE(); 633 } 634 635 return $matrixA->multiply($matrixB)->toArray(); 636 } catch (MatrixException $ex) { 637 return Functions::VALUE(); 638 } 639 } 640 641 /** 642 * MOD. 643 * 644 * @param int $a Dividend 645 * @param int $b Divisor 646 * 647 * @return int Remainder 648 */ 649 public static function MOD($a = 1, $b = 1) 650 { 651 $a = (float) Functions::flattenSingleValue($a); 652 $b = (float) Functions::flattenSingleValue($b); 653 654 if ($b == 0.0) { 655 return Functions::DIV0(); 656 } elseif (($a < 0.0) && ($b > 0.0)) { 657 return $b - fmod(abs($a), $b); 658 } elseif (($a > 0.0) && ($b < 0.0)) { 659 return $b + fmod($a, abs($b)); 660 } 661 662 return fmod($a, $b); 663 } 664 665 /** 666 * MROUND. 667 * 668 * Rounds a number to the nearest multiple of a specified value 669 * 670 * @param float $number Number to round 671 * @param int $multiple Multiple to which you want to round $number 672 * 673 * @return float Rounded Number 674 */ 675 public static function MROUND($number, $multiple) 676 { 677 $number = Functions::flattenSingleValue($number); 678 $multiple = Functions::flattenSingleValue($multiple); 679 680 if ((is_numeric($number)) && (is_numeric($multiple))) { 681 if ($multiple == 0) { 682 return 0; 683 } 684 if ((self::SIGN($number)) == (self::SIGN($multiple))) { 685 $multiplier = 1 / $multiple; 686 687 return round($number * $multiplier) / $multiplier; 688 } 689 690 return Functions::NAN(); 691 } 692 693 return Functions::VALUE(); 694 } 695 696 /** 697 * MULTINOMIAL. 698 * 699 * Returns the ratio of the factorial of a sum of values to the product of factorials. 700 * 701 * @param array of mixed Data Series 702 * 703 * @return float 704 */ 705 public static function MULTINOMIAL(...$args) 706 { 707 $summer = 0; 708 $divisor = 1; 709 // Loop through arguments 710 foreach (Functions::flattenArray($args) as $arg) { 711 // Is it a numeric value? 712 if (is_numeric($arg)) { 713 if ($arg < 1) { 714 return Functions::NAN(); 715 } 716 $summer += floor($arg); 717 $divisor *= self::FACT($arg); 718 } else { 719 return Functions::VALUE(); 720 } 721 } 722 723 // Return 724 if ($summer > 0) { 725 $summer = self::FACT($summer); 726 727 return $summer / $divisor; 728 } 729 730 return 0; 731 } 732 733 /** 734 * ODD. 735 * 736 * Returns number rounded up to the nearest odd integer. 737 * 738 * @param float $number Number to round 739 * 740 * @return int Rounded Number 741 */ 742 public static function ODD($number) 743 { 744 $number = Functions::flattenSingleValue($number); 745 746 if ($number === null) { 747 return 1; 748 } elseif (is_bool($number)) { 749 return 1; 750 } elseif (is_numeric($number)) { 751 $significance = self::SIGN($number); 752 if ($significance == 0) { 753 return 1; 754 } 755 756 $result = self::CEILING($number, $significance); 757 if ($result == self::EVEN($result)) { 758 $result += $significance; 759 } 760 761 return (int) $result; 762 } 763 764 return Functions::VALUE(); 765 } 766 767 /** 768 * POWER. 769 * 770 * Computes x raised to the power y. 771 * 772 * @param float $x 773 * @param float $y 774 * 775 * @return float 776 */ 777 public static function POWER($x = 0, $y = 2) 778 { 779 $x = Functions::flattenSingleValue($x); 780 $y = Functions::flattenSingleValue($y); 781 782 // Validate parameters 783 if ($x == 0.0 && $y == 0.0) { 784 return Functions::NAN(); 785 } elseif ($x == 0.0 && $y < 0.0) { 786 return Functions::DIV0(); 787 } 788 789 // Return 790 $result = pow($x, $y); 791 792 return (!is_nan($result) && !is_infinite($result)) ? $result : Functions::NAN(); 793 } 794 795 /** 796 * PRODUCT. 797 * 798 * PRODUCT returns the product of all the values and cells referenced in the argument list. 799 * 800 * Excel Function: 801 * PRODUCT(value1[,value2[, ...]]) 802 * 803 * @category Mathematical and Trigonometric Functions 804 * 805 * @param mixed ...$args Data values 806 * 807 * @return float 808 */ 809 public static function PRODUCT(...$args) 810 { 811 // Return value 812 $returnValue = null; 813 814 // Loop through arguments 815 foreach (Functions::flattenArray($args) as $arg) { 816 // Is it a numeric value? 817 if ((is_numeric($arg)) && (!is_string($arg))) { 818 if ($returnValue === null) { 819 $returnValue = $arg; 820 } else { 821 $returnValue *= $arg; 822 } 823 } 824 } 825 826 // Return 827 if ($returnValue === null) { 828 return 0; 829 } 830 831 return $returnValue; 832 } 833 834 /** 835 * QUOTIENT. 836 * 837 * QUOTIENT function returns the integer portion of a division. Numerator is the divided number 838 * and denominator is the divisor. 839 * 840 * Excel Function: 841 * QUOTIENT(value1[,value2[, ...]]) 842 * 843 * @category Mathematical and Trigonometric Functions 844 * 845 * @param mixed ...$args Data values 846 * 847 * @return float 848 */ 849 public static function QUOTIENT(...$args) 850 { 851 // Return value 852 $returnValue = null; 853 854 // Loop through arguments 855 foreach (Functions::flattenArray($args) as $arg) { 856 // Is it a numeric value? 857 if ((is_numeric($arg)) && (!is_string($arg))) { 858 if ($returnValue === null) { 859 $returnValue = ($arg == 0) ? 0 : $arg; 860 } else { 861 if (($returnValue == 0) || ($arg == 0)) { 862 $returnValue = 0; 863 } else { 864 $returnValue /= $arg; 865 } 866 } 867 } 868 } 869 870 // Return 871 return (int) $returnValue; 872 } 873 874 /** 875 * RAND. 876 * 877 * @param int $min Minimal value 878 * @param int $max Maximal value 879 * 880 * @return int Random number 881 */ 882 public static function RAND($min = 0, $max = 0) 883 { 884 $min = Functions::flattenSingleValue($min); 885 $max = Functions::flattenSingleValue($max); 886 887 if ($min == 0 && $max == 0) { 888 return (mt_rand(0, 10000000)) / 10000000; 889 } 890 891 return mt_rand($min, $max); 892 } 893 894 public static function ROMAN($aValue, $style = 0) 895 { 896 $aValue = Functions::flattenSingleValue($aValue); 897 $style = ($style === null) ? 0 : (int) Functions::flattenSingleValue($style); 898 if ((!is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) { 899 return Functions::VALUE(); 900 } 901 $aValue = (int) $aValue; 902 if ($aValue == 0) { 903 return ''; 904 } 905 906 $mill = ['', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM']; 907 $cent = ['', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM']; 908 $tens = ['', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC']; 909 $ones = ['', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX']; 910 911 $roman = ''; 912 while ($aValue > 5999) { 913 $roman .= 'M'; 914 $aValue -= 1000; 915 } 916 $m = self::romanCut($aValue, 1000); 917 $aValue %= 1000; 918 $c = self::romanCut($aValue, 100); 919 $aValue %= 100; 920 $t = self::romanCut($aValue, 10); 921 $aValue %= 10; 922 923 return $roman . $mill[$m] . $cent[$c] . $tens[$t] . $ones[$aValue]; 924 } 925 926 /** 927 * ROUNDUP. 928 * 929 * Rounds a number up to a specified number of decimal places 930 * 931 * @param float $number Number to round 932 * @param int $digits Number of digits to which you want to round $number 933 * 934 * @return float Rounded Number 935 */ 936 public static function ROUNDUP($number, $digits) 937 { 938 $number = Functions::flattenSingleValue($number); 939 $digits = Functions::flattenSingleValue($digits); 940 941 if ((is_numeric($number)) && (is_numeric($digits))) { 942 $significance = pow(10, (int) $digits); 943 if ($number < 0.0) { 944 return floor($number * $significance) / $significance; 945 } 946 947 return ceil($number * $significance) / $significance; 948 } 949 950 return Functions::VALUE(); 951 } 952 953 /** 954 * ROUNDDOWN. 955 * 956 * Rounds a number down to a specified number of decimal places 957 * 958 * @param float $number Number to round 959 * @param int $digits Number of digits to which you want to round $number 960 * 961 * @return float Rounded Number 962 */ 963 public static function ROUNDDOWN($number, $digits) 964 { 965 $number = Functions::flattenSingleValue($number); 966 $digits = Functions::flattenSingleValue($digits); 967 968 if ((is_numeric($number)) && (is_numeric($digits))) { 969 $significance = pow(10, (int) $digits); 970 if ($number < 0.0) { 971 return ceil($number * $significance) / $significance; 972 } 973 974 return floor($number * $significance) / $significance; 975 } 976 977 return Functions::VALUE(); 978 } 979 980 /** 981 * SERIESSUM. 982 * 983 * Returns the sum of a power series 984 * 985 * @param float $x Input value to the power series 986 * @param float $n Initial power to which you want to raise $x 987 * @param float $m Step by which to increase $n for each term in the series 988 * @param array of mixed Data Series 989 * 990 * @return float 991 */ 992 public static function SERIESSUM(...$args) 993 { 994 $returnValue = 0; 995 996 // Loop through arguments 997 $aArgs = Functions::flattenArray($args); 998 999 $x = array_shift($aArgs); 1000 $n = array_shift($aArgs); 1001 $m = array_shift($aArgs); 1002 1003 if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) { 1004 // Calculate 1005 $i = 0; 1006 foreach ($aArgs as $arg) { 1007 // Is it a numeric value? 1008 if ((is_numeric($arg)) && (!is_string($arg))) { 1009 $returnValue += $arg * pow($x, $n + ($m * $i++)); 1010 } else { 1011 return Functions::VALUE(); 1012 } 1013 } 1014 1015 return $returnValue; 1016 } 1017 1018 return Functions::VALUE(); 1019 } 1020 1021 /** 1022 * SIGN. 1023 * 1024 * Determines the sign of a number. Returns 1 if the number is positive, zero (0) 1025 * if the number is 0, and -1 if the number is negative. 1026 * 1027 * @param float $number Number to round 1028 * 1029 * @return int sign value 1030 */ 1031 public static function SIGN($number) 1032 { 1033 $number = Functions::flattenSingleValue($number); 1034 1035 if (is_bool($number)) { 1036 return (int) $number; 1037 } 1038 if (is_numeric($number)) { 1039 if ($number == 0.0) { 1040 return 0; 1041 } 1042 1043 return $number / abs($number); 1044 } 1045 1046 return Functions::VALUE(); 1047 } 1048 1049 /** 1050 * SQRTPI. 1051 * 1052 * Returns the square root of (number * pi). 1053 * 1054 * @param float $number Number 1055 * 1056 * @return float Square Root of Number * Pi 1057 */ 1058 public static function SQRTPI($number) 1059 { 1060 $number = Functions::flattenSingleValue($number); 1061 1062 if (is_numeric($number)) { 1063 if ($number < 0) { 1064 return Functions::NAN(); 1065 } 1066 1067 return sqrt($number * M_PI); 1068 } 1069 1070 return Functions::VALUE(); 1071 } 1072 1073 protected static function filterHiddenArgs($cellReference, $args) 1074 { 1075 return array_filter( 1076 $args, 1077 function ($index) use ($cellReference) { 1078 [, $row, $column] = explode('.', $index); 1079 1080 return $cellReference->getWorksheet()->getRowDimension($row)->getVisible() && 1081 $cellReference->getWorksheet()->getColumnDimension($column)->getVisible(); 1082 }, 1083 ARRAY_FILTER_USE_KEY 1084 ); 1085 } 1086 1087 protected static function filterFormulaArgs($cellReference, $args) 1088 { 1089 return array_filter( 1090 $args, 1091 function ($index) use ($cellReference) { 1092 [, $row, $column] = explode('.', $index); 1093 if ($cellReference->getWorksheet()->cellExists($column . $row)) { 1094 //take this cell out if it contains the SUBTOTAL or AGGREGATE functions in a formula 1095 $isFormula = $cellReference->getWorksheet()->getCell($column . $row)->isFormula(); 1096 $cellFormula = !preg_match('/^=.*\b(SUBTOTAL|AGGREGATE)\s*\(/i', $cellReference->getWorksheet()->getCell($column . $row)->getValue()); 1097 1098 return !$isFormula || $cellFormula; 1099 } 1100 1101 return true; 1102 }, 1103 ARRAY_FILTER_USE_KEY 1104 ); 1105 } 1106 1107 /** 1108 * SUBTOTAL. 1109 * 1110 * Returns a subtotal in a list or database. 1111 * 1112 * @param int the number 1 to 11 that specifies which function to 1113 * use in calculating subtotals within a range 1114 * list 1115 * Numbers 101 to 111 shadow the functions of 1 to 11 1116 * but ignore any values in the range that are 1117 * in hidden rows or columns 1118 * @param array of mixed Data Series 1119 * 1120 * @return float|string 1121 */ 1122 public static function SUBTOTAL(...$args) 1123 { 1124 $cellReference = array_pop($args); 1125 $aArgs = Functions::flattenArrayIndexed($args); 1126 $subtotal = array_shift($aArgs); 1127 1128 // Calculate 1129 if ((is_numeric($subtotal)) && (!is_string($subtotal))) { 1130 if ($subtotal > 100) { 1131 $aArgs = self::filterHiddenArgs($cellReference, $aArgs); 1132 $subtotal -= 100; 1133 } 1134 1135 $aArgs = self::filterFormulaArgs($cellReference, $aArgs); 1136 switch ($subtotal) { 1137 case 1: 1138 return Statistical::AVERAGE($aArgs); 1139 case 2: 1140 return Statistical::COUNT($aArgs); 1141 case 3: 1142 return Statistical::COUNTA($aArgs); 1143 case 4: 1144 return Statistical::MAX($aArgs); 1145 case 5: 1146 return Statistical::MIN($aArgs); 1147 case 6: 1148 return self::PRODUCT($aArgs); 1149 case 7: 1150 return Statistical::STDEV($aArgs); 1151 case 8: 1152 return Statistical::STDEVP($aArgs); 1153 case 9: 1154 return self::SUM($aArgs); 1155 case 10: 1156 return Statistical::VARFunc($aArgs); 1157 case 11: 1158 return Statistical::VARP($aArgs); 1159 } 1160 } 1161 1162 return Functions::VALUE(); 1163 } 1164 1165 /** 1166 * SUM. 1167 * 1168 * SUM computes the sum of all the values and cells referenced in the argument list. 1169 * 1170 * Excel Function: 1171 * SUM(value1[,value2[, ...]]) 1172 * 1173 * @category Mathematical and Trigonometric Functions 1174 * 1175 * @param mixed ...$args Data values 1176 * 1177 * @return float 1178 */ 1179 public static function SUM(...$args) 1180 { 1181 $returnValue = 0; 1182 1183 // Loop through the arguments 1184 foreach (Functions::flattenArray($args) as $arg) { 1185 // Is it a numeric value? 1186 if ((is_numeric($arg)) && (!is_string($arg))) { 1187 $returnValue += $arg; 1188 } 1189 } 1190 1191 return $returnValue; 1192 } 1193 1194 /** 1195 * SUMIF. 1196 * 1197 * Counts the number of cells that contain numbers within the list of arguments 1198 * 1199 * Excel Function: 1200 * SUMIF(value1[,value2[, ...]],condition) 1201 * 1202 * @category Mathematical and Trigonometric Functions 1203 * 1204 * @param mixed $aArgs Data values 1205 * @param string $condition the criteria that defines which cells will be summed 1206 * @param mixed $sumArgs 1207 * 1208 * @return float 1209 */ 1210 public static function SUMIF($aArgs, $condition, $sumArgs = []) 1211 { 1212 $returnValue = 0; 1213 1214 $aArgs = Functions::flattenArray($aArgs); 1215 $sumArgs = Functions::flattenArray($sumArgs); 1216 if (empty($sumArgs)) { 1217 $sumArgs = $aArgs; 1218 } 1219 $condition = Functions::ifCondition($condition); 1220 // Loop through arguments 1221 foreach ($aArgs as $key => $arg) { 1222 if (!is_numeric($arg)) { 1223 $arg = str_replace('"', '""', $arg); 1224 $arg = Calculation::wrapResult(strtoupper($arg)); 1225 } 1226 1227 $testCondition = '=' . $arg . $condition; 1228 $sumValue = array_key_exists($key, $sumArgs) ? $sumArgs[$key] : 0; 1229 1230 if (is_numeric($sumValue) && 1231 Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1232 // Is it a value within our criteria and only numeric can be added to the result 1233 $returnValue += $sumValue; 1234 } 1235 } 1236 1237 return $returnValue; 1238 } 1239 1240 /** 1241 * SUMIFS. 1242 * 1243 * Counts the number of cells that contain numbers within the list of arguments 1244 * 1245 * Excel Function: 1246 * SUMIFS(value1[,value2[, ...]],condition) 1247 * 1248 * @category Mathematical and Trigonometric Functions 1249 * 1250 * @param mixed $args Data values 1251 * @param string $condition the criteria that defines which cells will be summed 1252 * 1253 * @return float 1254 */ 1255 public static function SUMIFS(...$args) 1256 { 1257 $arrayList = $args; 1258 1259 // Return value 1260 $returnValue = 0; 1261 1262 $sumArgs = Functions::flattenArray(array_shift($arrayList)); 1263 $aArgsArray = []; 1264 $conditions = []; 1265 1266 while (count($arrayList) > 0) { 1267 $aArgsArray[] = Functions::flattenArray(array_shift($arrayList)); 1268 $conditions[] = Functions::ifCondition(array_shift($arrayList)); 1269 } 1270 1271 // Loop through each sum and see if arguments and conditions are true 1272 foreach ($sumArgs as $index => $value) { 1273 $valid = true; 1274 1275 foreach ($conditions as $cidx => $condition) { 1276 $arg = $aArgsArray[$cidx][$index]; 1277 1278 // Loop through arguments 1279 if (!is_numeric($arg)) { 1280 $arg = Calculation::wrapResult(strtoupper($arg)); 1281 } 1282 $testCondition = '=' . $arg . $condition; 1283 if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1284 // Is not a value within our criteria 1285 $valid = false; 1286 1287 break; // if false found, don't need to check other conditions 1288 } 1289 } 1290 1291 if ($valid) { 1292 $returnValue += $value; 1293 } 1294 } 1295 1296 // Return 1297 return $returnValue; 1298 } 1299 1300 /** 1301 * SUMPRODUCT. 1302 * 1303 * Excel Function: 1304 * SUMPRODUCT(value1[,value2[, ...]]) 1305 * 1306 * @category Mathematical and Trigonometric Functions 1307 * 1308 * @param mixed ...$args Data values 1309 * 1310 * @return float 1311 */ 1312 public static function SUMPRODUCT(...$args) 1313 { 1314 $arrayList = $args; 1315 1316 $wrkArray = Functions::flattenArray(array_shift($arrayList)); 1317 $wrkCellCount = count($wrkArray); 1318 1319 for ($i = 0; $i < $wrkCellCount; ++$i) { 1320 if ((!is_numeric($wrkArray[$i])) || (is_string($wrkArray[$i]))) { 1321 $wrkArray[$i] = 0; 1322 } 1323 } 1324 1325 foreach ($arrayList as $matrixData) { 1326 $array2 = Functions::flattenArray($matrixData); 1327 $count = count($array2); 1328 if ($wrkCellCount != $count) { 1329 return Functions::VALUE(); 1330 } 1331 1332 foreach ($array2 as $i => $val) { 1333 if ((!is_numeric($val)) || (is_string($val))) { 1334 $val = 0; 1335 } 1336 $wrkArray[$i] *= $val; 1337 } 1338 } 1339 1340 return array_sum($wrkArray); 1341 } 1342 1343 /** 1344 * SUMSQ. 1345 * 1346 * SUMSQ returns the sum of the squares of the arguments 1347 * 1348 * Excel Function: 1349 * SUMSQ(value1[,value2[, ...]]) 1350 * 1351 * @category Mathematical and Trigonometric Functions 1352 * 1353 * @param mixed ...$args Data values 1354 * 1355 * @return float 1356 */ 1357 public static function SUMSQ(...$args) 1358 { 1359 $returnValue = 0; 1360 1361 // Loop through arguments 1362 foreach (Functions::flattenArray($args) as $arg) { 1363 // Is it a numeric value? 1364 if ((is_numeric($arg)) && (!is_string($arg))) { 1365 $returnValue += ($arg * $arg); 1366 } 1367 } 1368 1369 return $returnValue; 1370 } 1371 1372 /** 1373 * SUMX2MY2. 1374 * 1375 * @param mixed[] $matrixData1 Matrix #1 1376 * @param mixed[] $matrixData2 Matrix #2 1377 * 1378 * @return float 1379 */ 1380 public static function SUMX2MY2($matrixData1, $matrixData2) 1381 { 1382 $array1 = Functions::flattenArray($matrixData1); 1383 $array2 = Functions::flattenArray($matrixData2); 1384 $count = min(count($array1), count($array2)); 1385 1386 $result = 0; 1387 for ($i = 0; $i < $count; ++$i) { 1388 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1389 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1390 $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]); 1391 } 1392 } 1393 1394 return $result; 1395 } 1396 1397 /** 1398 * SUMX2PY2. 1399 * 1400 * @param mixed[] $matrixData1 Matrix #1 1401 * @param mixed[] $matrixData2 Matrix #2 1402 * 1403 * @return float 1404 */ 1405 public static function SUMX2PY2($matrixData1, $matrixData2) 1406 { 1407 $array1 = Functions::flattenArray($matrixData1); 1408 $array2 = Functions::flattenArray($matrixData2); 1409 $count = min(count($array1), count($array2)); 1410 1411 $result = 0; 1412 for ($i = 0; $i < $count; ++$i) { 1413 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1414 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1415 $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]); 1416 } 1417 } 1418 1419 return $result; 1420 } 1421 1422 /** 1423 * SUMXMY2. 1424 * 1425 * @param mixed[] $matrixData1 Matrix #1 1426 * @param mixed[] $matrixData2 Matrix #2 1427 * 1428 * @return float 1429 */ 1430 public static function SUMXMY2($matrixData1, $matrixData2) 1431 { 1432 $array1 = Functions::flattenArray($matrixData1); 1433 $array2 = Functions::flattenArray($matrixData2); 1434 $count = min(count($array1), count($array2)); 1435 1436 $result = 0; 1437 for ($i = 0; $i < $count; ++$i) { 1438 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1439 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1440 $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]); 1441 } 1442 } 1443 1444 return $result; 1445 } 1446 1447 /** 1448 * TRUNC. 1449 * 1450 * Truncates value to the number of fractional digits by number_digits. 1451 * 1452 * @param float $value 1453 * @param int $digits 1454 * 1455 * @return float Truncated value 1456 */ 1457 public static function TRUNC($value = 0, $digits = 0) 1458 { 1459 $value = Functions::flattenSingleValue($value); 1460 $digits = Functions::flattenSingleValue($digits); 1461 1462 // Validate parameters 1463 if ((!is_numeric($value)) || (!is_numeric($digits))) { 1464 return Functions::VALUE(); 1465 } 1466 $digits = floor($digits); 1467 1468 // Truncate 1469 $adjust = pow(10, $digits); 1470 1471 if (($digits > 0) && (rtrim((int) ((abs($value) - abs((int) $value)) * $adjust), '0') < $adjust / 10)) { 1472 return $value; 1473 } 1474 1475 return ((int) ($value * $adjust)) / $adjust; 1476 } 1477 1478 /** 1479 * SEC. 1480 * 1481 * Returns the secant of an angle. 1482 * 1483 * @param float $angle Number 1484 * 1485 * @return float|string The secant of the angle 1486 */ 1487 public static function SEC($angle) 1488 { 1489 $angle = Functions::flattenSingleValue($angle); 1490 1491 if (!is_numeric($angle)) { 1492 return Functions::VALUE(); 1493 } 1494 1495 $result = cos($angle); 1496 1497 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1498 } 1499 1500 /** 1501 * SECH. 1502 * 1503 * Returns the hyperbolic secant of an angle. 1504 * 1505 * @param float $angle Number 1506 * 1507 * @return float|string The hyperbolic secant of the angle 1508 */ 1509 public static function SECH($angle) 1510 { 1511 $angle = Functions::flattenSingleValue($angle); 1512 1513 if (!is_numeric($angle)) { 1514 return Functions::VALUE(); 1515 } 1516 1517 $result = cosh($angle); 1518 1519 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1520 } 1521 1522 /** 1523 * CSC. 1524 * 1525 * Returns the cosecant of an angle. 1526 * 1527 * @param float $angle Number 1528 * 1529 * @return float|string The cosecant of the angle 1530 */ 1531 public static function CSC($angle) 1532 { 1533 $angle = Functions::flattenSingleValue($angle); 1534 1535 if (!is_numeric($angle)) { 1536 return Functions::VALUE(); 1537 } 1538 1539 $result = sin($angle); 1540 1541 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1542 } 1543 1544 /** 1545 * CSCH. 1546 * 1547 * Returns the hyperbolic cosecant of an angle. 1548 * 1549 * @param float $angle Number 1550 * 1551 * @return float|string The hyperbolic cosecant of the angle 1552 */ 1553 public static function CSCH($angle) 1554 { 1555 $angle = Functions::flattenSingleValue($angle); 1556 1557 if (!is_numeric($angle)) { 1558 return Functions::VALUE(); 1559 } 1560 1561 $result = sinh($angle); 1562 1563 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1564 } 1565 1566 /** 1567 * COT. 1568 * 1569 * Returns the cotangent of an angle. 1570 * 1571 * @param float $angle Number 1572 * 1573 * @return float|string The cotangent of the angle 1574 */ 1575 public static function COT($angle) 1576 { 1577 $angle = Functions::flattenSingleValue($angle); 1578 1579 if (!is_numeric($angle)) { 1580 return Functions::VALUE(); 1581 } 1582 1583 $result = tan($angle); 1584 1585 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1586 } 1587 1588 /** 1589 * COTH. 1590 * 1591 * Returns the hyperbolic cotangent of an angle. 1592 * 1593 * @param float $angle Number 1594 * 1595 * @return float|string The hyperbolic cotangent of the angle 1596 */ 1597 public static function COTH($angle) 1598 { 1599 $angle = Functions::flattenSingleValue($angle); 1600 1601 if (!is_numeric($angle)) { 1602 return Functions::VALUE(); 1603 } 1604 1605 $result = tanh($angle); 1606 1607 return ($result == 0.0) ? Functions::DIV0() : 1 / $result; 1608 } 1609 1610 /** 1611 * ACOT. 1612 * 1613 * Returns the arccotangent of a number. 1614 * 1615 * @param float $number Number 1616 * 1617 * @return float|string The arccotangent of the number 1618 */ 1619 public static function ACOT($number) 1620 { 1621 $number = Functions::flattenSingleValue($number); 1622 1623 if (!is_numeric($number)) { 1624 return Functions::VALUE(); 1625 } 1626 1627 return (M_PI / 2) - atan($number); 1628 } 1629 1630 /** 1631 * ACOTH. 1632 * 1633 * Returns the hyperbolic arccotangent of a number. 1634 * 1635 * @param float $number Number 1636 * 1637 * @return float|string The hyperbolic arccotangent of the number 1638 */ 1639 public static function ACOTH($number) 1640 { 1641 $number = Functions::flattenSingleValue($number); 1642 1643 if (!is_numeric($number)) { 1644 return Functions::VALUE(); 1645 } 1646 1647 $result = log(($number + 1) / ($number - 1)) / 2; 1648 1649 return is_nan($result) ? Functions::NAN() : $result; 1650 } 1651 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
