Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

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