1<?php 2/** 3 * PHPExcel 4 * 5 * Copyright (c) 2006 - 2014 PHPExcel 6 * 7 * This library is free software; you can redistribute it and/or 8 * modify it under the terms of the GNU Lesser General Public 9 * License as published by the Free Software Foundation; either 10 * version 2.1 of the License, or (at your option) any later version. 11 * 12 * This library is distributed in the hope that it will be useful, 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 15 * Lesser General Public License for more details. 16 * 17 * You should have received a copy of the GNU Lesser General Public 18 * License along with this library; if not, write to the Free Software 19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 20 * 21 * @category PHPExcel 22 * @package PHPExcel_Calculation 23 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) 24 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL 25 * @version ##VERSION##, ##DATE## 26 */ 27 28 29/** PHPExcel root directory */ 30if (!defined('PHPEXCEL_ROOT')) { 31 /** 32 * @ignore 33 */ 34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../'); 35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php'); 36} 37 38 39/** 40 * PHPExcel_Calculation_MathTrig 41 * 42 * @category PHPExcel 43 * @package PHPExcel_Calculation 44 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) 45 */ 46class PHPExcel_Calculation_MathTrig { 47 48 // 49 // Private method to return an array of the factors of the input value 50 // 51 private static function _factors($value) { 52 $startVal = floor(sqrt($value)); 53 54 $factorArray = array(); 55 for ($i = $startVal; $i > 1; --$i) { 56 if (($value % $i) == 0) { 57 $factorArray = array_merge($factorArray,self::_factors($value / $i)); 58 $factorArray = array_merge($factorArray,self::_factors($i)); 59 if ($i <= sqrt($value)) { 60 break; 61 } 62 } 63 } 64 if (!empty($factorArray)) { 65 rsort($factorArray); 66 return $factorArray; 67 } else { 68 return array((integer) $value); 69 } 70 } // function _factors() 71 72 73 private static function _romanCut($num, $n) { 74 return ($num - ($num % $n ) ) / $n; 75 } // function _romanCut() 76 77 78 /** 79 * ATAN2 80 * 81 * This function calculates the arc tangent of the two variables x and y. It is similar to 82 * calculating the arc tangent of y ÷ x, except that the signs of both arguments are used 83 * to determine the quadrant of the result. 84 * The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a 85 * point with coordinates (xCoordinate, yCoordinate). The angle is given in radians between 86 * -pi and pi, excluding -pi. 87 * 88 * Note that the Excel ATAN2() function accepts its arguments in the reverse order to the standard 89 * PHP atan2() function, so we need to reverse them here before calling the PHP atan() function. 90 * 91 * Excel Function: 92 * ATAN2(xCoordinate,yCoordinate) 93 * 94 * @access public 95 * @category Mathematical and Trigonometric Functions 96 * @param float $xCoordinate The x-coordinate of the point. 97 * @param float $yCoordinate The y-coordinate of the point. 98 * @return float The inverse tangent of the specified x- and y-coordinates. 99 */ 100 public static function ATAN2($xCoordinate = NULL, $yCoordinate = NULL) { 101 $xCoordinate = PHPExcel_Calculation_Functions::flattenSingleValue($xCoordinate); 102 $yCoordinate = PHPExcel_Calculation_Functions::flattenSingleValue($yCoordinate); 103 104 $xCoordinate = ($xCoordinate !== NULL) ? $xCoordinate : 0.0; 105 $yCoordinate = ($yCoordinate !== NULL) ? $yCoordinate : 0.0; 106 107 if (((is_numeric($xCoordinate)) || (is_bool($xCoordinate))) && 108 ((is_numeric($yCoordinate))) || (is_bool($yCoordinate))) { 109 $xCoordinate = (float) $xCoordinate; 110 $yCoordinate = (float) $yCoordinate; 111 112 if (($xCoordinate == 0) && ($yCoordinate == 0)) { 113 return PHPExcel_Calculation_Functions::DIV0(); 114 } 115 116 return atan2($yCoordinate, $xCoordinate); 117 } 118 return PHPExcel_Calculation_Functions::VALUE(); 119 } // function ATAN2() 120 121 122 /** 123 * CEILING 124 * 125 * Returns number rounded up, away from zero, to the nearest multiple of significance. 126 * For example, if you want to avoid using pennies in your prices and your product is 127 * priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the 128 * nearest nickel. 129 * 130 * Excel Function: 131 * CEILING(number[,significance]) 132 * 133 * @access public 134 * @category Mathematical and Trigonometric Functions 135 * @param float $number The number you want to round. 136 * @param float $significance The multiple to which you want to round. 137 * @return float Rounded Number 138 */ 139 public static function CEILING($number, $significance = NULL) { 140 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 141 $significance = PHPExcel_Calculation_Functions::flattenSingleValue($significance); 142 143 if ((is_null($significance)) && 144 (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC)) { 145 $significance = $number/abs($number); 146 } 147 148 if ((is_numeric($number)) && (is_numeric($significance))) { 149 if ($significance == 0.0) { 150 return 0.0; 151 } elseif (self::SIGN($number) == self::SIGN($significance)) { 152 return ceil($number / $significance) * $significance; 153 } else { 154 return PHPExcel_Calculation_Functions::NaN(); 155 } 156 } 157 return PHPExcel_Calculation_Functions::VALUE(); 158 } // function CEILING() 159 160 161 /** 162 * COMBIN 163 * 164 * Returns the number of combinations for a given number of items. Use COMBIN to 165 * determine the total possible number of groups for a given number of items. 166 * 167 * Excel Function: 168 * COMBIN(numObjs,numInSet) 169 * 170 * @access public 171 * @category Mathematical and Trigonometric Functions 172 * @param int $numObjs Number of different objects 173 * @param int $numInSet Number of objects in each combination 174 * @return int Number of combinations 175 */ 176 public static function COMBIN($numObjs, $numInSet) { 177 $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs); 178 $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet); 179 180 if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { 181 if ($numObjs < $numInSet) { 182 return PHPExcel_Calculation_Functions::NaN(); 183 } elseif ($numInSet < 0) { 184 return PHPExcel_Calculation_Functions::NaN(); 185 } 186 return round(self::FACT($numObjs) / self::FACT($numObjs - $numInSet)) / self::FACT($numInSet); 187 } 188 return PHPExcel_Calculation_Functions::VALUE(); 189 } // function COMBIN() 190 191 192 /** 193 * EVEN 194 * 195 * Returns number rounded up to the nearest even integer. 196 * You can use this function for processing items that come in twos. For example, 197 * a packing crate accepts rows of one or two items. The crate is full when 198 * the number of items, rounded up to the nearest two, matches the crate's 199 * capacity. 200 * 201 * Excel Function: 202 * EVEN(number) 203 * 204 * @access public 205 * @category Mathematical and Trigonometric Functions 206 * @param float $number Number to round 207 * @return int Rounded Number 208 */ 209 public static function EVEN($number) { 210 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 211 212 if (is_null($number)) { 213 return 0; 214 } elseif (is_bool($number)) { 215 $number = (int) $number; 216 } 217 218 if (is_numeric($number)) { 219 $significance = 2 * self::SIGN($number); 220 return (int) self::CEILING($number,$significance); 221 } 222 return PHPExcel_Calculation_Functions::VALUE(); 223 } // function EVEN() 224 225 226 /** 227 * FACT 228 * 229 * Returns the factorial of a number. 230 * The factorial of a number is equal to 1*2*3*...* number. 231 * 232 * Excel Function: 233 * FACT(factVal) 234 * 235 * @access public 236 * @category Mathematical and Trigonometric Functions 237 * @param float $factVal Factorial Value 238 * @return int Factorial 239 */ 240 public static function FACT($factVal) { 241 $factVal = PHPExcel_Calculation_Functions::flattenSingleValue($factVal); 242 243 if (is_numeric($factVal)) { 244 if ($factVal < 0) { 245 return PHPExcel_Calculation_Functions::NaN(); 246 } 247 $factLoop = floor($factVal); 248 if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) { 249 if ($factVal > $factLoop) { 250 return PHPExcel_Calculation_Functions::NaN(); 251 } 252 } 253 254 $factorial = 1; 255 while ($factLoop > 1) { 256 $factorial *= $factLoop--; 257 } 258 return $factorial ; 259 } 260 return PHPExcel_Calculation_Functions::VALUE(); 261 } // function FACT() 262 263 264 /** 265 * FACTDOUBLE 266 * 267 * Returns the double factorial of a number. 268 * 269 * Excel Function: 270 * FACTDOUBLE(factVal) 271 * 272 * @access public 273 * @category Mathematical and Trigonometric Functions 274 * @param float $factVal Factorial Value 275 * @return int Double Factorial 276 */ 277 public static function FACTDOUBLE($factVal) { 278 $factLoop = PHPExcel_Calculation_Functions::flattenSingleValue($factVal); 279 280 if (is_numeric($factLoop)) { 281 $factLoop = floor($factLoop); 282 if ($factVal < 0) { 283 return PHPExcel_Calculation_Functions::NaN(); 284 } 285 $factorial = 1; 286 while ($factLoop > 1) { 287 $factorial *= $factLoop--; 288 --$factLoop; 289 } 290 return $factorial ; 291 } 292 return PHPExcel_Calculation_Functions::VALUE(); 293 } // function FACTDOUBLE() 294 295 296 /** 297 * FLOOR 298 * 299 * Rounds number down, toward zero, to the nearest multiple of significance. 300 * 301 * Excel Function: 302 * FLOOR(number[,significance]) 303 * 304 * @access public 305 * @category Mathematical and Trigonometric Functions 306 * @param float $number Number to round 307 * @param float $significance Significance 308 * @return float Rounded Number 309 */ 310 public static function FLOOR($number, $significance = NULL) { 311 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 312 $significance = PHPExcel_Calculation_Functions::flattenSingleValue($significance); 313 314 if ((is_null($significance)) && (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC)) { 315 $significance = $number/abs($number); 316 } 317 318 if ((is_numeric($number)) && (is_numeric($significance))) { 319 if ((float) $significance == 0.0) { 320 return PHPExcel_Calculation_Functions::DIV0(); 321 } 322 if (self::SIGN($number) == self::SIGN($significance)) { 323 return floor($number / $significance) * $significance; 324 } else { 325 return PHPExcel_Calculation_Functions::NaN(); 326 } 327 } 328 return PHPExcel_Calculation_Functions::VALUE(); 329 } // function FLOOR() 330 331 332 /** 333 * GCD 334 * 335 * Returns the greatest common divisor of a series of numbers. 336 * The greatest common divisor is the largest integer that divides both 337 * number1 and number2 without a remainder. 338 * 339 * Excel Function: 340 * GCD(number1[,number2[, ...]]) 341 * 342 * @access public 343 * @category Mathematical and Trigonometric Functions 344 * @param mixed $arg,... Data values 345 * @return integer Greatest Common Divisor 346 */ 347 public static function GCD() { 348 $returnValue = 1; 349 $allValuesFactors = array(); 350 // Loop through arguments 351 foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) { 352 if (!is_numeric($value)) { 353 return PHPExcel_Calculation_Functions::VALUE(); 354 } elseif ($value == 0) { 355 continue; 356 } elseif($value < 0) { 357 return PHPExcel_Calculation_Functions::NaN(); 358 } 359 $myFactors = self::_factors($value); 360 $myCountedFactors = array_count_values($myFactors); 361 $allValuesFactors[] = $myCountedFactors; 362 } 363 $allValuesCount = count($allValuesFactors); 364 if ($allValuesCount == 0) { 365 return 0; 366 } 367 368 $mergedArray = $allValuesFactors[0]; 369 for ($i=1;$i < $allValuesCount; ++$i) { 370 $mergedArray = array_intersect_key($mergedArray,$allValuesFactors[$i]); 371 } 372 $mergedArrayValues = count($mergedArray); 373 if ($mergedArrayValues == 0) { 374 return $returnValue; 375 } elseif ($mergedArrayValues > 1) { 376 foreach($mergedArray as $mergedKey => $mergedValue) { 377 foreach($allValuesFactors as $highestPowerTest) { 378 foreach($highestPowerTest as $testKey => $testValue) { 379 if (($testKey == $mergedKey) && ($testValue < $mergedValue)) { 380 $mergedArray[$mergedKey] = $testValue; 381 $mergedValue = $testValue; 382 } 383 } 384 } 385 } 386 387 $returnValue = 1; 388 foreach($mergedArray as $key => $value) { 389 $returnValue *= pow($key,$value); 390 } 391 return $returnValue; 392 } else { 393 $keys = array_keys($mergedArray); 394 $key = $keys[0]; 395 $value = $mergedArray[$key]; 396 foreach($allValuesFactors as $testValue) { 397 foreach($testValue as $mergedKey => $mergedValue) { 398 if (($mergedKey == $key) && ($mergedValue < $value)) { 399 $value = $mergedValue; 400 } 401 } 402 } 403 return pow($key,$value); 404 } 405 } // function GCD() 406 407 408 /** 409 * INT 410 * 411 * Casts a floating point value to an integer 412 * 413 * Excel Function: 414 * INT(number) 415 * 416 * @access public 417 * @category Mathematical and Trigonometric Functions 418 * @param float $number Number to cast to an integer 419 * @return integer Integer value 420 */ 421 public static function INT($number) { 422 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 423 424 if (is_null($number)) { 425 return 0; 426 } elseif (is_bool($number)) { 427 return (int) $number; 428 } 429 if (is_numeric($number)) { 430 return (int) floor($number); 431 } 432 return PHPExcel_Calculation_Functions::VALUE(); 433 } // function INT() 434 435 436 /** 437 * LCM 438 * 439 * Returns the lowest common multiplier of a series of numbers 440 * The least common multiple is the smallest positive integer that is a multiple 441 * of all integer arguments number1, number2, and so on. Use LCM to add fractions 442 * with different denominators. 443 * 444 * Excel Function: 445 * LCM(number1[,number2[, ...]]) 446 * 447 * @access public 448 * @category Mathematical and Trigonometric Functions 449 * @param mixed $arg,... Data values 450 * @return int Lowest Common Multiplier 451 */ 452 public static function LCM() { 453 $returnValue = 1; 454 $allPoweredFactors = array(); 455 // Loop through arguments 456 foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) { 457 if (!is_numeric($value)) { 458 return PHPExcel_Calculation_Functions::VALUE(); 459 } 460 if ($value == 0) { 461 return 0; 462 } elseif ($value < 0) { 463 return PHPExcel_Calculation_Functions::NaN(); 464 } 465 $myFactors = self::_factors(floor($value)); 466 $myCountedFactors = array_count_values($myFactors); 467 $myPoweredFactors = array(); 468 foreach($myCountedFactors as $myCountedFactor => $myCountedPower) { 469 $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor,$myCountedPower); 470 } 471 foreach($myPoweredFactors as $myPoweredValue => $myPoweredFactor) { 472 if (array_key_exists($myPoweredValue,$allPoweredFactors)) { 473 if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) { 474 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; 475 } 476 } else { 477 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; 478 } 479 } 480 } 481 foreach($allPoweredFactors as $allPoweredFactor) { 482 $returnValue *= (integer) $allPoweredFactor; 483 } 484 return $returnValue; 485 } // function LCM() 486 487 488 /** 489 * LOG_BASE 490 * 491 * Returns the logarithm of a number to a specified base. The default base is 10. 492 * 493 * Excel Function: 494 * LOG(number[,base]) 495 * 496 * @access public 497 * @category Mathematical and Trigonometric Functions 498 * @param float $number The positive real number for which you want the logarithm 499 * @param float $base The base of the logarithm. If base is omitted, it is assumed to be 10. 500 * @return float 501 */ 502 public static function LOG_BASE($number = NULL, $base = 10) { 503 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 504 $base = (is_null($base)) ? 10 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($base); 505 506 if ((!is_numeric($base)) || (!is_numeric($number))) 507 return PHPExcel_Calculation_Functions::VALUE(); 508 if (($base <= 0) || ($number <= 0)) 509 return PHPExcel_Calculation_Functions::NaN(); 510 return log($number, $base); 511 } // function LOG_BASE() 512 513 514 /** 515 * MDETERM 516 * 517 * Returns the matrix determinant of an array. 518 * 519 * Excel Function: 520 * MDETERM(array) 521 * 522 * @access public 523 * @category Mathematical and Trigonometric Functions 524 * @param array $matrixValues A matrix of values 525 * @return float 526 */ 527 public static function MDETERM($matrixValues) { 528 $matrixData = array(); 529 if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); } 530 531 $row = $maxColumn = 0; 532 foreach($matrixValues as $matrixRow) { 533 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); } 534 $column = 0; 535 foreach($matrixRow as $matrixCell) { 536 if ((is_string($matrixCell)) || ($matrixCell === null)) { 537 return PHPExcel_Calculation_Functions::VALUE(); 538 } 539 $matrixData[$column][$row] = $matrixCell; 540 ++$column; 541 } 542 if ($column > $maxColumn) { $maxColumn = $column; } 543 ++$row; 544 } 545 if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); } 546 547 try { 548 $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData); 549 return $matrix->det(); 550 } catch (PHPExcel_Exception $ex) { 551 return PHPExcel_Calculation_Functions::VALUE(); 552 } 553 } // function MDETERM() 554 555 556 /** 557 * MINVERSE 558 * 559 * Returns the inverse matrix for the matrix stored in an array. 560 * 561 * Excel Function: 562 * MINVERSE(array) 563 * 564 * @access public 565 * @category Mathematical and Trigonometric Functions 566 * @param array $matrixValues A matrix of values 567 * @return array 568 */ 569 public static function MINVERSE($matrixValues) { 570 $matrixData = array(); 571 if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); } 572 573 $row = $maxColumn = 0; 574 foreach($matrixValues as $matrixRow) { 575 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); } 576 $column = 0; 577 foreach($matrixRow as $matrixCell) { 578 if ((is_string($matrixCell)) || ($matrixCell === null)) { 579 return PHPExcel_Calculation_Functions::VALUE(); 580 } 581 $matrixData[$column][$row] = $matrixCell; 582 ++$column; 583 } 584 if ($column > $maxColumn) { $maxColumn = $column; } 585 ++$row; 586 } 587 if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); } 588 589 try { 590 $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData); 591 return $matrix->inverse()->getArray(); 592 } catch (PHPExcel_Exception $ex) { 593 return PHPExcel_Calculation_Functions::VALUE(); 594 } 595 } // function MINVERSE() 596 597 598 /** 599 * MMULT 600 * 601 * @param array $matrixData1 A matrix of values 602 * @param array $matrixData2 A matrix of values 603 * @return array 604 */ 605 public static function MMULT($matrixData1,$matrixData2) { 606 $matrixAData = $matrixBData = array(); 607 if (!is_array($matrixData1)) { $matrixData1 = array(array($matrixData1)); } 608 if (!is_array($matrixData2)) { $matrixData2 = array(array($matrixData2)); } 609 610 $rowA = 0; 611 foreach($matrixData1 as $matrixRow) { 612 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); } 613 $columnA = 0; 614 foreach($matrixRow as $matrixCell) { 615 if ((is_string($matrixCell)) || ($matrixCell === null)) { 616 return PHPExcel_Calculation_Functions::VALUE(); 617 } 618 $matrixAData[$rowA][$columnA] = $matrixCell; 619 ++$columnA; 620 } 621 ++$rowA; 622 } 623 try { 624 $matrixA = new PHPExcel_Shared_JAMA_Matrix($matrixAData); 625 $rowB = 0; 626 foreach($matrixData2 as $matrixRow) { 627 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); } 628 $columnB = 0; 629 foreach($matrixRow as $matrixCell) { 630 if ((is_string($matrixCell)) || ($matrixCell === null)) { 631 return PHPExcel_Calculation_Functions::VALUE(); 632 } 633 $matrixBData[$rowB][$columnB] = $matrixCell; 634 ++$columnB; 635 } 636 ++$rowB; 637 } 638 $matrixB = new PHPExcel_Shared_JAMA_Matrix($matrixBData); 639 640 if (($rowA != $columnB) || ($rowB != $columnA)) { 641 return PHPExcel_Calculation_Functions::VALUE(); 642 } 643 644 return $matrixA->times($matrixB)->getArray(); 645 } catch (PHPExcel_Exception $ex) { 646 return PHPExcel_Calculation_Functions::VALUE(); 647 } 648 } // function MMULT() 649 650 651 /** 652 * MOD 653 * 654 * @param int $a Dividend 655 * @param int $b Divisor 656 * @return int Remainder 657 */ 658 public static function MOD($a = 1, $b = 1) { 659 $a = PHPExcel_Calculation_Functions::flattenSingleValue($a); 660 $b = PHPExcel_Calculation_Functions::flattenSingleValue($b); 661 662 if ($b == 0.0) { 663 return PHPExcel_Calculation_Functions::DIV0(); 664 } elseif (($a < 0.0) && ($b > 0.0)) { 665 return $b - fmod(abs($a),$b); 666 } elseif (($a > 0.0) && ($b < 0.0)) { 667 return $b + fmod($a,abs($b)); 668 } 669 670 return fmod($a,$b); 671 } // function MOD() 672 673 674 /** 675 * MROUND 676 * 677 * Rounds a number to the nearest multiple of a specified value 678 * 679 * @param float $number Number to round 680 * @param int $multiple Multiple to which you want to round $number 681 * @return float Rounded Number 682 */ 683 public static function MROUND($number,$multiple) { 684 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 685 $multiple = PHPExcel_Calculation_Functions::flattenSingleValue($multiple); 686 687 if ((is_numeric($number)) && (is_numeric($multiple))) { 688 if ($multiple == 0) { 689 return 0; 690 } 691 if ((self::SIGN($number)) == (self::SIGN($multiple))) { 692 $multiplier = 1 / $multiple; 693 return round($number * $multiplier) / $multiplier; 694 } 695 return PHPExcel_Calculation_Functions::NaN(); 696 } 697 return PHPExcel_Calculation_Functions::VALUE(); 698 } // function MROUND() 699 700 701 /** 702 * MULTINOMIAL 703 * 704 * Returns the ratio of the factorial of a sum of values to the product of factorials. 705 * 706 * @param array of mixed Data Series 707 * @return float 708 */ 709 public static function MULTINOMIAL() { 710 $summer = 0; 711 $divisor = 1; 712 // Loop through arguments 713 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) { 714 // Is it a numeric value? 715 if (is_numeric($arg)) { 716 if ($arg < 1) { 717 return PHPExcel_Calculation_Functions::NaN(); 718 } 719 $summer += floor($arg); 720 $divisor *= self::FACT($arg); 721 } else { 722 return PHPExcel_Calculation_Functions::VALUE(); 723 } 724 } 725 726 // Return 727 if ($summer > 0) { 728 $summer = self::FACT($summer); 729 return $summer / $divisor; 730 } 731 return 0; 732 } // function MULTINOMIAL() 733 734 735 /** 736 * ODD 737 * 738 * Returns number rounded up to the nearest odd integer. 739 * 740 * @param float $number Number to round 741 * @return int Rounded Number 742 */ 743 public static function ODD($number) { 744 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 745 746 if (is_null($number)) { 747 return 1; 748 } elseif (is_bool($number)) { 749 $number = (int) $number; 750 } 751 752 if (is_numeric($number)) { 753 $significance = self::SIGN($number); 754 if ($significance == 0) { 755 return 1; 756 } 757 758 $result = self::CEILING($number,$significance); 759 if ($result == self::EVEN($result)) { 760 $result += $significance; 761 } 762 763 return (int) $result; 764 } 765 return PHPExcel_Calculation_Functions::VALUE(); 766 } // function ODD() 767 768 769 /** 770 * POWER 771 * 772 * Computes x raised to the power y. 773 * 774 * @param float $x 775 * @param float $y 776 * @return float 777 */ 778 public static function POWER($x = 0, $y = 2) { 779 $x = PHPExcel_Calculation_Functions::flattenSingleValue($x); 780 $y = PHPExcel_Calculation_Functions::flattenSingleValue($y); 781 782 // Validate parameters 783 if ($x == 0.0 && $y == 0.0) { 784 return PHPExcel_Calculation_Functions::NaN(); 785 } elseif ($x == 0.0 && $y < 0.0) { 786 return PHPExcel_Calculation_Functions::DIV0(); 787 } 788 789 // Return 790 $result = pow($x, $y); 791 return (!is_nan($result) && !is_infinite($result)) ? $result : PHPExcel_Calculation_Functions::NaN(); 792 } // function POWER() 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 * @access public 804 * @category Mathematical and Trigonometric Functions 805 * @param mixed $arg,... Data values 806 * @return float 807 */ 808 public static function PRODUCT() { 809 // Return value 810 $returnValue = null; 811 812 // Loop through arguments 813 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) { 814 // Is it a numeric value? 815 if ((is_numeric($arg)) && (!is_string($arg))) { 816 if (is_null($returnValue)) { 817 $returnValue = $arg; 818 } else { 819 $returnValue *= $arg; 820 } 821 } 822 } 823 824 // Return 825 if (is_null($returnValue)) { 826 return 0; 827 } 828 return $returnValue; 829 } // function PRODUCT() 830 831 832 /** 833 * QUOTIENT 834 * 835 * QUOTIENT function returns the integer portion of a division. Numerator is the divided number 836 * and denominator is the divisor. 837 * 838 * Excel Function: 839 * QUOTIENT(value1[,value2[, ...]]) 840 * 841 * @access public 842 * @category Mathematical and Trigonometric Functions 843 * @param mixed $arg,... Data values 844 * @return float 845 */ 846 public static function QUOTIENT() { 847 // Return value 848 $returnValue = null; 849 850 // Loop through arguments 851 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) { 852 // Is it a numeric value? 853 if ((is_numeric($arg)) && (!is_string($arg))) { 854 if (is_null($returnValue)) { 855 $returnValue = ($arg == 0) ? 0 : $arg; 856 } else { 857 if (($returnValue == 0) || ($arg == 0)) { 858 $returnValue = 0; 859 } else { 860 $returnValue /= $arg; 861 } 862 } 863 } 864 } 865 866 // Return 867 return intval($returnValue); 868 } // function QUOTIENT() 869 870 871 /** 872 * RAND 873 * 874 * @param int $min Minimal value 875 * @param int $max Maximal value 876 * @return int Random number 877 */ 878 public static function RAND($min = 0, $max = 0) { 879 $min = PHPExcel_Calculation_Functions::flattenSingleValue($min); 880 $max = PHPExcel_Calculation_Functions::flattenSingleValue($max); 881 882 if ($min == 0 && $max == 0) { 883 return (rand(0,10000000)) / 10000000; 884 } else { 885 return rand($min, $max); 886 } 887 } // function RAND() 888 889 890 public static function ROMAN($aValue, $style=0) { 891 $aValue = PHPExcel_Calculation_Functions::flattenSingleValue($aValue); 892 $style = (is_null($style)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($style); 893 if ((!is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) { 894 return PHPExcel_Calculation_Functions::VALUE(); 895 } 896 $aValue = (integer) $aValue; 897 if ($aValue == 0) { 898 return ''; 899 } 900 901 $mill = Array('', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM'); 902 $cent = Array('', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM'); 903 $tens = Array('', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC'); 904 $ones = Array('', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX'); 905 906 $roman = ''; 907 while ($aValue > 5999) { 908 $roman .= 'M'; 909 $aValue -= 1000; 910 } 911 $m = self::_romanCut($aValue, 1000); $aValue %= 1000; 912 $c = self::_romanCut($aValue, 100); $aValue %= 100; 913 $t = self::_romanCut($aValue, 10); $aValue %= 10; 914 915 return $roman.$mill[$m].$cent[$c].$tens[$t].$ones[$aValue]; 916 } // function ROMAN() 917 918 919 /** 920 * ROUNDUP 921 * 922 * Rounds a number up to a specified number of decimal places 923 * 924 * @param float $number Number to round 925 * @param int $digits Number of digits to which you want to round $number 926 * @return float Rounded Number 927 */ 928 public static function ROUNDUP($number,$digits) { 929 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 930 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits); 931 932 if ((is_numeric($number)) && (is_numeric($digits))) { 933 $significance = pow(10,(int) $digits); 934 if ($number < 0.0) { 935 return floor($number * $significance) / $significance; 936 } else { 937 return ceil($number * $significance) / $significance; 938 } 939 } 940 return PHPExcel_Calculation_Functions::VALUE(); 941 } // function ROUNDUP() 942 943 944 /** 945 * ROUNDDOWN 946 * 947 * Rounds a number down to a specified number of decimal places 948 * 949 * @param float $number Number to round 950 * @param int $digits Number of digits to which you want to round $number 951 * @return float Rounded Number 952 */ 953 public static function ROUNDDOWN($number,$digits) { 954 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 955 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits); 956 957 if ((is_numeric($number)) && (is_numeric($digits))) { 958 $significance = pow(10,(int) $digits); 959 if ($number < 0.0) { 960 return ceil($number * $significance) / $significance; 961 } else { 962 return floor($number * $significance) / $significance; 963 } 964 } 965 return PHPExcel_Calculation_Functions::VALUE(); 966 } // function ROUNDDOWN() 967 968 969 /** 970 * SERIESSUM 971 * 972 * Returns the sum of a power series 973 * 974 * @param float $x Input value to the power series 975 * @param float $n Initial power to which you want to raise $x 976 * @param float $m Step by which to increase $n for each term in the series 977 * @param array of mixed Data Series 978 * @return float 979 */ 980 public static function SERIESSUM() { 981 // Return value 982 $returnValue = 0; 983 984 // Loop through arguments 985 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 986 987 $x = array_shift($aArgs); 988 $n = array_shift($aArgs); 989 $m = array_shift($aArgs); 990 991 if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) { 992 // Calculate 993 $i = 0; 994 foreach($aArgs as $arg) { 995 // Is it a numeric value? 996 if ((is_numeric($arg)) && (!is_string($arg))) { 997 $returnValue += $arg * pow($x,$n + ($m * $i++)); 998 } else { 999 return PHPExcel_Calculation_Functions::VALUE(); 1000 } 1001 } 1002 // Return 1003 return $returnValue; 1004 } 1005 return PHPExcel_Calculation_Functions::VALUE(); 1006 } // function SERIESSUM() 1007 1008 1009 /** 1010 * SIGN 1011 * 1012 * Determines the sign of a number. Returns 1 if the number is positive, zero (0) 1013 * if the number is 0, and -1 if the number is negative. 1014 * 1015 * @param float $number Number to round 1016 * @return int sign value 1017 */ 1018 public static function SIGN($number) { 1019 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 1020 1021 if (is_bool($number)) 1022 return (int) $number; 1023 if (is_numeric($number)) { 1024 if ($number == 0.0) { 1025 return 0; 1026 } 1027 return $number / abs($number); 1028 } 1029 return PHPExcel_Calculation_Functions::VALUE(); 1030 } // function SIGN() 1031 1032 1033 /** 1034 * SQRTPI 1035 * 1036 * Returns the square root of (number * pi). 1037 * 1038 * @param float $number Number 1039 * @return float Square Root of Number * Pi 1040 */ 1041 public static function SQRTPI($number) { 1042 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number); 1043 1044 if (is_numeric($number)) { 1045 if ($number < 0) { 1046 return PHPExcel_Calculation_Functions::NaN(); 1047 } 1048 return sqrt($number * M_PI) ; 1049 } 1050 return PHPExcel_Calculation_Functions::VALUE(); 1051 } // function SQRTPI() 1052 1053 1054 /** 1055 * SUBTOTAL 1056 * 1057 * Returns a subtotal in a list or database. 1058 * 1059 * @param int the number 1 to 11 that specifies which function to 1060 * use in calculating subtotals within a list. 1061 * @param array of mixed Data Series 1062 * @return float 1063 */ 1064 public static function SUBTOTAL() { 1065 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 1066 1067 // Calculate 1068 $subtotal = array_shift($aArgs); 1069 1070 if ((is_numeric($subtotal)) && (!is_string($subtotal))) { 1071 switch($subtotal) { 1072 case 1 : 1073 return PHPExcel_Calculation_Statistical::AVERAGE($aArgs); 1074 break; 1075 case 2 : 1076 return PHPExcel_Calculation_Statistical::COUNT($aArgs); 1077 break; 1078 case 3 : 1079 return PHPExcel_Calculation_Statistical::COUNTA($aArgs); 1080 break; 1081 case 4 : 1082 return PHPExcel_Calculation_Statistical::MAX($aArgs); 1083 break; 1084 case 5 : 1085 return PHPExcel_Calculation_Statistical::MIN($aArgs); 1086 break; 1087 case 6 : 1088 return self::PRODUCT($aArgs); 1089 break; 1090 case 7 : 1091 return PHPExcel_Calculation_Statistical::STDEV($aArgs); 1092 break; 1093 case 8 : 1094 return PHPExcel_Calculation_Statistical::STDEVP($aArgs); 1095 break; 1096 case 9 : 1097 return self::SUM($aArgs); 1098 break; 1099 case 10 : 1100 return PHPExcel_Calculation_Statistical::VARFunc($aArgs); 1101 break; 1102 case 11 : 1103 return PHPExcel_Calculation_Statistical::VARP($aArgs); 1104 break; 1105 } 1106 } 1107 return PHPExcel_Calculation_Functions::VALUE(); 1108 } // function SUBTOTAL() 1109 1110 1111 /** 1112 * SUM 1113 * 1114 * SUM computes the sum of all the values and cells referenced in the argument list. 1115 * 1116 * Excel Function: 1117 * SUM(value1[,value2[, ...]]) 1118 * 1119 * @access public 1120 * @category Mathematical and Trigonometric Functions 1121 * @param mixed $arg,... Data values 1122 * @return float 1123 */ 1124 public static function SUM() { 1125 // Return value 1126 $returnValue = 0; 1127 1128 // Loop through the arguments 1129 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) { 1130 // Is it a numeric value? 1131 if ((is_numeric($arg)) && (!is_string($arg))) { 1132 $returnValue += $arg; 1133 } 1134 } 1135 1136 // Return 1137 return $returnValue; 1138 } // function SUM() 1139 1140 1141 /** 1142 * SUMIF 1143 * 1144 * Counts the number of cells that contain numbers within the list of arguments 1145 * 1146 * Excel Function: 1147 * SUMIF(value1[,value2[, ...]],condition) 1148 * 1149 * @access public 1150 * @category Mathematical and Trigonometric Functions 1151 * @param mixed $arg,... Data values 1152 * @param string $condition The criteria that defines which cells will be summed. 1153 * @return float 1154 */ 1155 public static function SUMIF($aArgs,$condition,$sumArgs = array()) { 1156 // Return value 1157 $returnValue = 0; 1158 1159 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 1160 $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); 1161 if (empty($sumArgs)) { 1162 $sumArgs = $aArgs; 1163 } 1164 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition); 1165 // Loop through arguments 1166 foreach ($aArgs as $key => $arg) { 1167 if (!is_numeric($arg)) { 1168 $arg = str_replace('"', '""', $arg); 1169 $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); 1170 } 1171 1172 $testCondition = '='.$arg.$condition; 1173 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1174 // Is it a value within our criteria 1175 $returnValue += $sumArgs[$key]; 1176 } 1177 } 1178 1179 // Return 1180 return $returnValue; 1181 } // function SUMIF() 1182 1183 1184 /** 1185 * SUMPRODUCT 1186 * 1187 * Excel Function: 1188 * SUMPRODUCT(value1[,value2[, ...]]) 1189 * 1190 * @access public 1191 * @category Mathematical and Trigonometric Functions 1192 * @param mixed $arg,... Data values 1193 * @return float 1194 */ 1195 public static function SUMPRODUCT() { 1196 $arrayList = func_get_args(); 1197 1198 $wrkArray = PHPExcel_Calculation_Functions::flattenArray(array_shift($arrayList)); 1199 $wrkCellCount = count($wrkArray); 1200 1201 for ($i=0; $i< $wrkCellCount; ++$i) { 1202 if ((!is_numeric($wrkArray[$i])) || (is_string($wrkArray[$i]))) { 1203 $wrkArray[$i] = 0; 1204 } 1205 } 1206 1207 foreach($arrayList as $matrixData) { 1208 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData); 1209 $count = count($array2); 1210 if ($wrkCellCount != $count) { 1211 return PHPExcel_Calculation_Functions::VALUE(); 1212 } 1213 1214 foreach ($array2 as $i => $val) { 1215 if ((!is_numeric($val)) || (is_string($val))) { 1216 $val = 0; 1217 } 1218 $wrkArray[$i] *= $val; 1219 } 1220 } 1221 1222 return array_sum($wrkArray); 1223 } // function SUMPRODUCT() 1224 1225 1226 /** 1227 * SUMSQ 1228 * 1229 * SUMSQ returns the sum of the squares of the arguments 1230 * 1231 * Excel Function: 1232 * SUMSQ(value1[,value2[, ...]]) 1233 * 1234 * @access public 1235 * @category Mathematical and Trigonometric Functions 1236 * @param mixed $arg,... Data values 1237 * @return float 1238 */ 1239 public static function SUMSQ() { 1240 // Return value 1241 $returnValue = 0; 1242 1243 // Loop through arguments 1244 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) { 1245 // Is it a numeric value? 1246 if ((is_numeric($arg)) && (!is_string($arg))) { 1247 $returnValue += ($arg * $arg); 1248 } 1249 } 1250 1251 // Return 1252 return $returnValue; 1253 } // function SUMSQ() 1254 1255 1256 /** 1257 * SUMX2MY2 1258 * 1259 * @param mixed[] $matrixData1 Matrix #1 1260 * @param mixed[] $matrixData2 Matrix #2 1261 * @return float 1262 */ 1263 public static function SUMX2MY2($matrixData1,$matrixData2) { 1264 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1); 1265 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2); 1266 $count1 = count($array1); 1267 $count2 = count($array2); 1268 if ($count1 < $count2) { 1269 $count = $count1; 1270 } else { 1271 $count = $count2; 1272 } 1273 1274 $result = 0; 1275 for ($i = 0; $i < $count; ++$i) { 1276 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1277 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1278 $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]); 1279 } 1280 } 1281 1282 return $result; 1283 } // function SUMX2MY2() 1284 1285 1286 /** 1287 * SUMX2PY2 1288 * 1289 * @param mixed[] $matrixData1 Matrix #1 1290 * @param mixed[] $matrixData2 Matrix #2 1291 * @return float 1292 */ 1293 public static function SUMX2PY2($matrixData1,$matrixData2) { 1294 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1); 1295 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2); 1296 $count1 = count($array1); 1297 $count2 = count($array2); 1298 if ($count1 < $count2) { 1299 $count = $count1; 1300 } else { 1301 $count = $count2; 1302 } 1303 1304 $result = 0; 1305 for ($i = 0; $i < $count; ++$i) { 1306 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1307 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1308 $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]); 1309 } 1310 } 1311 1312 return $result; 1313 } // function SUMX2PY2() 1314 1315 1316 /** 1317 * SUMXMY2 1318 * 1319 * @param mixed[] $matrixData1 Matrix #1 1320 * @param mixed[] $matrixData2 Matrix #2 1321 * @return float 1322 */ 1323 public static function SUMXMY2($matrixData1,$matrixData2) { 1324 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1); 1325 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2); 1326 $count1 = count($array1); 1327 $count2 = count($array2); 1328 if ($count1 < $count2) { 1329 $count = $count1; 1330 } else { 1331 $count = $count2; 1332 } 1333 1334 $result = 0; 1335 for ($i = 0; $i < $count; ++$i) { 1336 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && 1337 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { 1338 $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]); 1339 } 1340 } 1341 1342 return $result; 1343 } // function SUMXMY2() 1344 1345 1346 /** 1347 * TRUNC 1348 * 1349 * Truncates value to the number of fractional digits by number_digits. 1350 * 1351 * @param float $value 1352 * @param int $digits 1353 * @return float Truncated value 1354 */ 1355 public static function TRUNC($value = 0, $digits = 0) { 1356 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1357 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits); 1358 1359 // Validate parameters 1360 if ((!is_numeric($value)) || (!is_numeric($digits))) 1361 return PHPExcel_Calculation_Functions::VALUE(); 1362 $digits = floor($digits); 1363 1364 // Truncate 1365 $adjust = pow(10, $digits); 1366 1367 if (($digits > 0) && (rtrim(intval((abs($value) - abs(intval($value))) * $adjust),'0') < $adjust/10)) 1368 return $value; 1369 1370 return (intval($value * $adjust)) / $adjust; 1371 } // function TRUNC() 1372 1373} // class PHPExcel_Calculation_MathTrig 1374