1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29:
30: if (!defined('PHPEXCEL_ROOT')) {
31: 32: 33:
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: 40: 41: 42: 43: 44: 45:
46: class PHPExcel_Calculation_MathTrig {
47:
48:
49:
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: }
71:
72:
73: private static function _romanCut($num, $n) {
74: return ($num - ($num % $n ) ) / $n;
75: }
76:
77:
78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 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: }
120:
121:
122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 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: }
159:
160:
161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 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: }
190:
191:
192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 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: }
224:
225:
226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 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: }
262:
263:
264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 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: }
294:
295:
296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 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: }
330:
331:
332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346:
347: public static function GCD() {
348: $returnValue = 1;
349: $allValuesFactors = array();
350:
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: }
406:
407:
408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 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: }
434:
435:
436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451:
452: public static function LCM() {
453: $returnValue = 1;
454: $allPoweredFactors = array();
455:
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: }
486:
487:
488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 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: }
512:
513:
514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 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: }
554:
555:
556: 557: 558: 559: 560: 561: 562: 563: 564: 565: 566: 567: 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: }
596:
597:
598: 599: 600: 601: 602: 603: 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: }
649:
650:
651: 652: 653: 654: 655: 656: 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: }
672:
673:
674: 675: 676: 677: 678: 679: 680: 681: 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: }
699:
700:
701: 702: 703: 704: 705: 706: 707: 708:
709: public static function MULTINOMIAL() {
710: $summer = 0;
711: $divisor = 1;
712:
713: foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
714:
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:
727: if ($summer > 0) {
728: $summer = self::FACT($summer);
729: return $summer / $divisor;
730: }
731: return 0;
732: }
733:
734:
735: 736: 737: 738: 739: 740: 741: 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: }
767:
768:
769: 770: 771: 772: 773: 774: 775: 776: 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:
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:
790: $result = pow($x, $y);
791: return (!is_nan($result) && !is_infinite($result)) ? $result : PHPExcel_Calculation_Functions::NaN();
792: }
793:
794:
795: 796: 797: 798: 799: 800: 801: 802: 803: 804: 805: 806: 807:
808: public static function PRODUCT() {
809:
810: $returnValue = null;
811:
812:
813: foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
814:
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:
825: if (is_null($returnValue)) {
826: return 0;
827: }
828: return $returnValue;
829: }
830:
831:
832: 833: 834: 835: 836: 837: 838: 839: 840: 841: 842: 843: 844: 845:
846: public static function QUOTIENT() {
847:
848: $returnValue = null;
849:
850:
851: foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
852:
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:
867: return intval($returnValue);
868: }
869:
870:
871: 872: 873: 874: 875: 876: 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: }
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: }
917:
918:
919: 920: 921: 922: 923: 924: 925: 926: 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: }
942:
943:
944: 945: 946: 947: 948: 949: 950: 951: 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: }
967:
968:
969: 970: 971: 972: 973: 974: 975: 976: 977: 978: 979:
980: public static function SERIESSUM() {
981:
982: $returnValue = 0;
983:
984:
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:
993: $i = 0;
994: foreach($aArgs as $arg) {
995:
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:
1003: return $returnValue;
1004: }
1005: return PHPExcel_Calculation_Functions::VALUE();
1006: }
1007:
1008:
1009: 1010: 1011: 1012: 1013: 1014: 1015: 1016: 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: }
1031:
1032:
1033: 1034: 1035: 1036: 1037: 1038: 1039: 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: }
1052:
1053:
1054: 1055: 1056: 1057: 1058: 1059: 1060: 1061: 1062: 1063:
1064: public static function SUBTOTAL() {
1065: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1066:
1067:
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: }
1109:
1110:
1111: 1112: 1113: 1114: 1115: 1116: 1117: 1118: 1119: 1120: 1121: 1122: 1123:
1124: public static function SUM() {
1125:
1126: $returnValue = 0;
1127:
1128:
1129: foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1130:
1131: if ((is_numeric($arg)) && (!is_string($arg))) {
1132: $returnValue += $arg;
1133: }
1134: }
1135:
1136:
1137: return $returnValue;
1138: }
1139:
1140:
1141: 1142: 1143: 1144: 1145: 1146: 1147: 1148: 1149: 1150: 1151: 1152: 1153: 1154:
1155: public static function SUMIF($aArgs,$condition,$sumArgs = array()) {
1156:
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:
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:
1175: $returnValue += $sumArgs[$key];
1176: }
1177: }
1178:
1179:
1180: return $returnValue;
1181: }
1182:
1183:
1184: 1185: 1186: 1187: 1188: 1189: 1190: 1191: 1192: 1193: 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: }
1224:
1225:
1226: 1227: 1228: 1229: 1230: 1231: 1232: 1233: 1234: 1235: 1236: 1237: 1238:
1239: public static function SUMSQ() {
1240:
1241: $returnValue = 0;
1242:
1243:
1244: foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1245:
1246: if ((is_numeric($arg)) && (!is_string($arg))) {
1247: $returnValue += ($arg * $arg);
1248: }
1249: }
1250:
1251:
1252: return $returnValue;
1253: }
1254:
1255:
1256: 1257: 1258: 1259: 1260: 1261: 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: }
1284:
1285:
1286: 1287: 1288: 1289: 1290: 1291: 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: }
1314:
1315:
1316: 1317: 1318: 1319: 1320: 1321: 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: }
1344:
1345:
1346: 1347: 1348: 1349: 1350: 1351: 1352: 1353: 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:
1360: if ((!is_numeric($value)) || (!is_numeric($digits)))
1361: return PHPExcel_Calculation_Functions::VALUE();
1362: $digits = floor($digits);
1363:
1364:
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: }
1372:
1373: }
1374: