ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
MathTrig.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
38
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
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
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 (($number == 0.0 ) || ($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
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
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
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
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
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 ($significance == 0.0) {
320 return PHPExcel_Calculation_Functions::DIV0();
321 } elseif ($number == 0.0) {
322 return 0.0;
323 } elseif (self::SIGN($number) == self::SIGN($significance)) {
324 return floor($number / $significance) * $significance;
325 } else {
326 return PHPExcel_Calculation_Functions::NaN();
327 }
328 } else
329
330 return PHPExcel_Calculation_Functions::VALUE();
331 } // function FLOOR()
332
333
349 public static function GCD() {
350 $returnValue = 1;
351 $allValuesFactors = array();
352 // Loop through arguments
353 foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) {
354 if (!is_numeric($value)) {
355 return PHPExcel_Calculation_Functions::VALUE();
356 } elseif ($value == 0) {
357 continue;
358 } elseif($value < 0) {
359 return PHPExcel_Calculation_Functions::NaN();
360 }
361 $myFactors = self::_factors($value);
362 $myCountedFactors = array_count_values($myFactors);
363 $allValuesFactors[] = $myCountedFactors;
364 }
365 $allValuesCount = count($allValuesFactors);
366 if ($allValuesCount == 0) {
367 return 0;
368 }
369
370 $mergedArray = $allValuesFactors[0];
371 for ($i=1;$i < $allValuesCount; ++$i) {
372 $mergedArray = array_intersect_key($mergedArray,$allValuesFactors[$i]);
373 }
374 $mergedArrayValues = count($mergedArray);
375 if ($mergedArrayValues == 0) {
376 return $returnValue;
377 } elseif ($mergedArrayValues > 1) {
378 foreach($mergedArray as $mergedKey => $mergedValue) {
379 foreach($allValuesFactors as $highestPowerTest) {
380 foreach($highestPowerTest as $testKey => $testValue) {
381 if (($testKey == $mergedKey) && ($testValue < $mergedValue)) {
382 $mergedArray[$mergedKey] = $testValue;
383 $mergedValue = $testValue;
384 }
385 }
386 }
387 }
388
389 $returnValue = 1;
390 foreach($mergedArray as $key => $value) {
391 $returnValue *= pow($key,$value);
392 }
393 return $returnValue;
394 } else {
395 $keys = array_keys($mergedArray);
396 $key = $keys[0];
397 $value = $mergedArray[$key];
398 foreach($allValuesFactors as $testValue) {
399 foreach($testValue as $mergedKey => $mergedValue) {
400 if (($mergedKey == $key) && ($mergedValue < $value)) {
401 $value = $mergedValue;
402 }
403 }
404 }
405 return pow($key,$value);
406 }
407 } // function GCD()
408
409
423 public static function INT($number) {
424 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
425
426 if (is_null($number)) {
427 return 0;
428 } elseif (is_bool($number)) {
429 return (int) $number;
430 }
431 if (is_numeric($number)) {
432 return (int) floor($number);
433 }
434 return PHPExcel_Calculation_Functions::VALUE();
435 } // function INT()
436
437
454 public static function LCM() {
455 $returnValue = 1;
456 $allPoweredFactors = array();
457 // Loop through arguments
458 foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) {
459 if (!is_numeric($value)) {
460 return PHPExcel_Calculation_Functions::VALUE();
461 }
462 if ($value == 0) {
463 return 0;
464 } elseif ($value < 0) {
465 return PHPExcel_Calculation_Functions::NaN();
466 }
467 $myFactors = self::_factors(floor($value));
468 $myCountedFactors = array_count_values($myFactors);
469 $myPoweredFactors = array();
470 foreach($myCountedFactors as $myCountedFactor => $myCountedPower) {
471 $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor,$myCountedPower);
472 }
473 foreach($myPoweredFactors as $myPoweredValue => $myPoweredFactor) {
474 if (array_key_exists($myPoweredValue,$allPoweredFactors)) {
475 if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) {
476 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
477 }
478 } else {
479 $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
480 }
481 }
482 }
483 foreach($allPoweredFactors as $allPoweredFactor) {
484 $returnValue *= (integer) $allPoweredFactor;
485 }
486 return $returnValue;
487 } // function LCM()
488
489
504 public static function LOG_BASE($number = NULL, $base = 10) {
505 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
506 $base = (is_null($base)) ? 10 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($base);
507
508 if ((!is_numeric($base)) || (!is_numeric($number)))
509 return PHPExcel_Calculation_Functions::VALUE();
510 if (($base <= 0) || ($number <= 0))
511 return PHPExcel_Calculation_Functions::NaN();
512 return log($number, $base);
513 } // function LOG_BASE()
514
515
529 public static function MDETERM($matrixValues) {
530 $matrixData = array();
531 if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
532
533 $row = $maxColumn = 0;
534 foreach($matrixValues as $matrixRow) {
535 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); }
536 $column = 0;
537 foreach($matrixRow as $matrixCell) {
538 if ((is_string($matrixCell)) || ($matrixCell === null)) {
539 return PHPExcel_Calculation_Functions::VALUE();
540 }
541 $matrixData[$column][$row] = $matrixCell;
542 ++$column;
543 }
544 if ($column > $maxColumn) { $maxColumn = $column; }
545 ++$row;
546 }
547 if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); }
548
549 try {
550 $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
551 return $matrix->det();
552 } catch (PHPExcel_Exception $ex) {
553 return PHPExcel_Calculation_Functions::VALUE();
554 }
555 } // function MDETERM()
556
557
571 public static function MINVERSE($matrixValues) {
572 $matrixData = array();
573 if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
574
575 $row = $maxColumn = 0;
576 foreach($matrixValues as $matrixRow) {
577 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); }
578 $column = 0;
579 foreach($matrixRow as $matrixCell) {
580 if ((is_string($matrixCell)) || ($matrixCell === null)) {
581 return PHPExcel_Calculation_Functions::VALUE();
582 }
583 $matrixData[$column][$row] = $matrixCell;
584 ++$column;
585 }
586 if ($column > $maxColumn) { $maxColumn = $column; }
587 ++$row;
588 }
589 if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); }
590
591 try {
592 $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
593 return $matrix->inverse()->getArray();
594 } catch (PHPExcel_Exception $ex) {
595 return PHPExcel_Calculation_Functions::VALUE();
596 }
597 } // function MINVERSE()
598
599
607 public static function MMULT($matrixData1,$matrixData2) {
608 $matrixAData = $matrixBData = array();
609 if (!is_array($matrixData1)) { $matrixData1 = array(array($matrixData1)); }
610 if (!is_array($matrixData2)) { $matrixData2 = array(array($matrixData2)); }
611
612 try {
613 $rowA = 0;
614 foreach($matrixData1 as $matrixRow) {
615 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); }
616 $columnA = 0;
617 foreach($matrixRow as $matrixCell) {
618 if ((!is_numeric($matrixCell)) || ($matrixCell === null)) {
619 return PHPExcel_Calculation_Functions::VALUE();
620 }
621 $matrixAData[$rowA][$columnA] = $matrixCell;
622 ++$columnA;
623 }
624 ++$rowA;
625 }
626 $matrixA = new PHPExcel_Shared_JAMA_Matrix($matrixAData);
627 $rowB = 0;
628 foreach($matrixData2 as $matrixRow) {
629 if (!is_array($matrixRow)) { $matrixRow = array($matrixRow); }
630 $columnB = 0;
631 foreach($matrixRow as $matrixCell) {
632 if ((!is_numeric($matrixCell)) || ($matrixCell === null)) {
633 return PHPExcel_Calculation_Functions::VALUE();
634 }
635 $matrixBData[$rowB][$columnB] = $matrixCell;
636 ++$columnB;
637 }
638 ++$rowB;
639 }
640 $matrixB = new PHPExcel_Shared_JAMA_Matrix($matrixBData);
641
642 if ($columnA != $rowB) {
643 return PHPExcel_Calculation_Functions::VALUE();
644 }
645
646 return $matrixA->times($matrixB)->getArray();
647 } catch (PHPExcel_Exception $ex) {
648 var_dump($ex->getMessage());
649 return PHPExcel_Calculation_Functions::VALUE();
650 }
651 } // function MMULT()
652
653
661 public static function MOD($a = 1, $b = 1) {
662 $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
663 $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
664
665 if ($b == 0.0) {
666 return PHPExcel_Calculation_Functions::DIV0();
667 } elseif (($a < 0.0) && ($b > 0.0)) {
668 return $b - fmod(abs($a),$b);
669 } elseif (($a > 0.0) && ($b < 0.0)) {
670 return $b + fmod($a,abs($b));
671 }
672
673 return fmod($a,$b);
674 } // function MOD()
675
676
686 public static function MROUND($number,$multiple) {
687 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
688 $multiple = PHPExcel_Calculation_Functions::flattenSingleValue($multiple);
689
690 if ((is_numeric($number)) && (is_numeric($multiple))) {
691 if ($multiple == 0) {
692 return 0;
693 }
694 if ((self::SIGN($number)) == (self::SIGN($multiple))) {
695 $multiplier = 1 / $multiple;
696 return round($number * $multiplier) / $multiplier;
697 }
698 return PHPExcel_Calculation_Functions::NaN();
699 }
700 return PHPExcel_Calculation_Functions::VALUE();
701 } // function MROUND()
702
703
712 public static function MULTINOMIAL() {
713 $summer = 0;
714 $divisor = 1;
715 // Loop through arguments
716 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
717 // Is it a numeric value?
718 if (is_numeric($arg)) {
719 if ($arg < 1) {
720 return PHPExcel_Calculation_Functions::NaN();
721 }
722 $summer += floor($arg);
723 $divisor *= self::FACT($arg);
724 } else {
725 return PHPExcel_Calculation_Functions::VALUE();
726 }
727 }
728
729 // Return
730 if ($summer > 0) {
731 $summer = self::FACT($summer);
732 return $summer / $divisor;
733 }
734 return 0;
735 } // function MULTINOMIAL()
736
737
746 public static function ODD($number) {
747 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
748
749 if (is_null($number)) {
750 return 1;
751 } elseif (is_bool($number)) {
752 $number = (int) $number;
753 }
754
755 if (is_numeric($number)) {
756 $significance = self::SIGN($number);
757 if ($significance == 0) {
758 return 1;
759 }
760
761 $result = self::CEILING($number,$significance);
762 if ($result == self::EVEN($result)) {
763 $result += $significance;
764 }
765
766 return (int) $result;
767 }
768 return PHPExcel_Calculation_Functions::VALUE();
769 } // function ODD()
770
771
781 public static function POWER($x = 0, $y = 2) {
782 $x = PHPExcel_Calculation_Functions::flattenSingleValue($x);
783 $y = PHPExcel_Calculation_Functions::flattenSingleValue($y);
784
785 // Validate parameters
786 if ($x == 0.0 && $y == 0.0) {
787 return PHPExcel_Calculation_Functions::NaN();
788 } elseif ($x == 0.0 && $y < 0.0) {
789 return PHPExcel_Calculation_Functions::DIV0();
790 }
791
792 // Return
793 $result = pow($x, $y);
794 return (!is_nan($result) && !is_infinite($result)) ? $result : PHPExcel_Calculation_Functions::NaN();
795 } // function POWER()
796
797
811 public static function PRODUCT() {
812 // Return value
813 $returnValue = null;
814
815 // Loop through arguments
816 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
817 // Is it a numeric value?
818 if ((is_numeric($arg)) && (!is_string($arg))) {
819 if (is_null($returnValue)) {
820 $returnValue = $arg;
821 } else {
822 $returnValue *= $arg;
823 }
824 }
825 }
826
827 // Return
828 if (is_null($returnValue)) {
829 return 0;
830 }
831 return $returnValue;
832 } // function PRODUCT()
833
834
849 public static function QUOTIENT() {
850 // Return value
851 $returnValue = null;
852
853 // Loop through arguments
854 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
855 // Is it a numeric value?
856 if ((is_numeric($arg)) && (!is_string($arg))) {
857 if (is_null($returnValue)) {
858 $returnValue = ($arg == 0) ? 0 : $arg;
859 } else {
860 if (($returnValue == 0) || ($arg == 0)) {
861 $returnValue = 0;
862 } else {
863 $returnValue /= $arg;
864 }
865 }
866 }
867 }
868
869 // Return
870 return intval($returnValue);
871 } // function QUOTIENT()
872
873
881 public static function RAND($min = 0, $max = 0) {
882 $min = PHPExcel_Calculation_Functions::flattenSingleValue($min);
883 $max = PHPExcel_Calculation_Functions::flattenSingleValue($max);
884
885 if ($min == 0 && $max == 0) {
886 return (mt_rand(0,10000000)) / 10000000;
887 } else {
888 return mt_rand($min, $max);
889 }
890 } // function RAND()
891
892
893 public static function ROMAN($aValue, $style=0) {
894 $aValue = PHPExcel_Calculation_Functions::flattenSingleValue($aValue);
895 $style = (is_null($style)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($style);
896 if ((!is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) {
897 return PHPExcel_Calculation_Functions::VALUE();
898 }
899 $aValue = (integer) $aValue;
900 if ($aValue == 0) {
901 return '';
902 }
903
904 $mill = Array('', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM');
905 $cent = Array('', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM');
906 $tens = Array('', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC');
907 $ones = Array('', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX');
908
909 $roman = '';
910 while ($aValue > 5999) {
911 $roman .= 'M';
912 $aValue -= 1000;
913 }
914 $m = self::_romanCut($aValue, 1000); $aValue %= 1000;
915 $c = self::_romanCut($aValue, 100); $aValue %= 100;
916 $t = self::_romanCut($aValue, 10); $aValue %= 10;
917
918 return $roman.$mill[$m].$cent[$c].$tens[$t].$ones[$aValue];
919 } // function ROMAN()
920
921
931 public static function ROUNDUP($number,$digits) {
932 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
933 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
934
935 if ((is_numeric($number)) && (is_numeric($digits))) {
936 $significance = pow(10,(int) $digits);
937 if ($number < 0.0) {
938 return floor($number * $significance) / $significance;
939 } else {
940 return ceil($number * $significance) / $significance;
941 }
942 }
943 return PHPExcel_Calculation_Functions::VALUE();
944 } // function ROUNDUP()
945
946
956 public static function ROUNDDOWN($number,$digits) {
957 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
958 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
959
960 if ((is_numeric($number)) && (is_numeric($digits))) {
961 $significance = pow(10,(int) $digits);
962 if ($number < 0.0) {
963 return ceil($number * $significance) / $significance;
964 } else {
965 return floor($number * $significance) / $significance;
966 }
967 }
968 return PHPExcel_Calculation_Functions::VALUE();
969 } // function ROUNDDOWN()
970
971
983 public static function SERIESSUM() {
984 // Return value
985 $returnValue = 0;
986
987 // Loop through arguments
988 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
989
990 $x = array_shift($aArgs);
991 $n = array_shift($aArgs);
992 $m = array_shift($aArgs);
993
994 if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) {
995 // Calculate
996 $i = 0;
997 foreach($aArgs as $arg) {
998 // Is it a numeric value?
999 if ((is_numeric($arg)) && (!is_string($arg))) {
1000 $returnValue += $arg * pow($x,$n + ($m * $i++));
1001 } else {
1002 return PHPExcel_Calculation_Functions::VALUE();
1003 }
1004 }
1005 // Return
1006 return $returnValue;
1007 }
1008 return PHPExcel_Calculation_Functions::VALUE();
1009 } // function SERIESSUM()
1010
1011
1021 public static function SIGN($number) {
1022 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
1023
1024 if (is_bool($number))
1025 return (int) $number;
1026 if (is_numeric($number)) {
1027 if ($number == 0.0) {
1028 return 0;
1029 }
1030 return $number / abs($number);
1031 }
1032 return PHPExcel_Calculation_Functions::VALUE();
1033 } // function SIGN()
1034
1035
1044 public static function SQRTPI($number) {
1045 $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
1046
1047 if (is_numeric($number)) {
1048 if ($number < 0) {
1049 return PHPExcel_Calculation_Functions::NaN();
1050 }
1051 return sqrt($number * M_PI) ;
1052 }
1053 return PHPExcel_Calculation_Functions::VALUE();
1054 } // function SQRTPI()
1055
1056
1067 public static function SUBTOTAL() {
1068 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1069
1070 // Calculate
1071 $subtotal = array_shift($aArgs);
1072
1073 if ((is_numeric($subtotal)) && (!is_string($subtotal))) {
1074 switch($subtotal) {
1075 case 1 :
1076 return PHPExcel_Calculation_Statistical::AVERAGE($aArgs);
1077 break;
1078 case 2 :
1079 return PHPExcel_Calculation_Statistical::COUNT($aArgs);
1080 break;
1081 case 3 :
1082 return PHPExcel_Calculation_Statistical::COUNTA($aArgs);
1083 break;
1084 case 4 :
1085 return PHPExcel_Calculation_Statistical::MAX($aArgs);
1086 break;
1087 case 5 :
1088 return PHPExcel_Calculation_Statistical::MIN($aArgs);
1089 break;
1090 case 6 :
1091 return self::PRODUCT($aArgs);
1092 break;
1093 case 7 :
1094 return PHPExcel_Calculation_Statistical::STDEV($aArgs);
1095 break;
1096 case 8 :
1097 return PHPExcel_Calculation_Statistical::STDEVP($aArgs);
1098 break;
1099 case 9 :
1100 return self::SUM($aArgs);
1101 break;
1102 case 10 :
1103 return PHPExcel_Calculation_Statistical::VARFunc($aArgs);
1104 break;
1105 case 11 :
1106 return PHPExcel_Calculation_Statistical::VARP($aArgs);
1107 break;
1108 }
1109 }
1110 return PHPExcel_Calculation_Functions::VALUE();
1111 } // function SUBTOTAL()
1112
1113
1127 public static function SUM() {
1128 // Return value
1129 $returnValue = 0;
1130
1131 // Loop through the arguments
1132 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1133 // Is it a numeric value?
1134 if ((is_numeric($arg)) && (!is_string($arg))) {
1135 $returnValue += $arg;
1136 }
1137 }
1138
1139 // Return
1140 return $returnValue;
1141 } // function SUM()
1142
1143
1158 public static function SUMIF($aArgs,$condition,$sumArgs = array()) {
1159 // Return value
1160 $returnValue = 0;
1161
1162 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
1163 $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
1164 if (empty($sumArgs)) {
1165 $sumArgs = $aArgs;
1166 }
1167 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1168 // Loop through arguments
1169 foreach ($aArgs as $key => $arg) {
1170 if (!is_numeric($arg)) {
1171 $arg = str_replace('"', '""', $arg);
1172 $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
1173 }
1174
1175 $testCondition = '='.$arg.$condition;
1176 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1177 // Is it a value within our criteria
1178 $returnValue += $sumArgs[$key];
1179 }
1180 }
1181
1182 // Return
1183 return $returnValue;
1184 } // function SUMIF()
1185
1186
1198 public static function SUMPRODUCT() {
1199 $arrayList = func_get_args();
1200
1201 $wrkArray = PHPExcel_Calculation_Functions::flattenArray(array_shift($arrayList));
1202 $wrkCellCount = count($wrkArray);
1203
1204 for ($i=0; $i< $wrkCellCount; ++$i) {
1205 if ((!is_numeric($wrkArray[$i])) || (is_string($wrkArray[$i]))) {
1206 $wrkArray[$i] = 0;
1207 }
1208 }
1209
1210 foreach($arrayList as $matrixData) {
1211 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData);
1212 $count = count($array2);
1213 if ($wrkCellCount != $count) {
1214 return PHPExcel_Calculation_Functions::VALUE();
1215 }
1216
1217 foreach ($array2 as $i => $val) {
1218 if ((!is_numeric($val)) || (is_string($val))) {
1219 $val = 0;
1220 }
1221 $wrkArray[$i] *= $val;
1222 }
1223 }
1224
1225 return array_sum($wrkArray);
1226 } // function SUMPRODUCT()
1227
1228
1242 public static function SUMSQ() {
1243 // Return value
1244 $returnValue = 0;
1245
1246 // Loop through arguments
1247 foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1248 // Is it a numeric value?
1249 if ((is_numeric($arg)) && (!is_string($arg))) {
1250 $returnValue += ($arg * $arg);
1251 }
1252 }
1253
1254 // Return
1255 return $returnValue;
1256 } // function SUMSQ()
1257
1258
1266 public static function SUMX2MY2($matrixData1,$matrixData2) {
1267 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1268 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1269 $count1 = count($array1);
1270 $count2 = count($array2);
1271 if ($count1 < $count2) {
1272 $count = $count1;
1273 } else {
1274 $count = $count2;
1275 }
1276
1277 $result = 0;
1278 for ($i = 0; $i < $count; ++$i) {
1279 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1280 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1281 $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]);
1282 }
1283 }
1284
1285 return $result;
1286 } // function SUMX2MY2()
1287
1288
1296 public static function SUMX2PY2($matrixData1,$matrixData2) {
1297 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1298 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1299 $count1 = count($array1);
1300 $count2 = count($array2);
1301 if ($count1 < $count2) {
1302 $count = $count1;
1303 } else {
1304 $count = $count2;
1305 }
1306
1307 $result = 0;
1308 for ($i = 0; $i < $count; ++$i) {
1309 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1310 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1311 $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]);
1312 }
1313 }
1314
1315 return $result;
1316 } // function SUMX2PY2()
1317
1318
1326 public static function SUMXMY2($matrixData1,$matrixData2) {
1327 $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1328 $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1329 $count1 = count($array1);
1330 $count2 = count($array2);
1331 if ($count1 < $count2) {
1332 $count = $count1;
1333 } else {
1334 $count = $count2;
1335 }
1336
1337 $result = 0;
1338 for ($i = 0; $i < $count; ++$i) {
1339 if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1340 ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1341 $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]);
1342 }
1343 }
1344
1345 return $result;
1346 } // function SUMXMY2()
1347
1348
1358 public static function TRUNC($value = 0, $digits = 0) {
1359 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1360 $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
1361
1362 // Validate parameters
1363 if ((!is_numeric($value)) || (!is_numeric($digits)))
1364 return PHPExcel_Calculation_Functions::VALUE();
1365 $digits = floor($digits);
1366
1367 // Truncate
1368 $adjust = pow(10, $digits);
1369
1370 if (($digits > 0) && (rtrim(intval((abs($value) - abs(intval($value))) * $adjust),'0') < $adjust/10))
1371 return $value;
1372
1373 return (intval($value * $adjust)) / $adjust;
1374 } // function TRUNC()
1375
1376} // class PHPExcel_Calculation_MathTrig
$column
$column
Definition: 39dropdown.php:62
$result
$result
Definition: CleanUpTest.php:463
$n
$n
Definition: RandomTest.php:85
php
An exception for terminatinating execution or to throw for unit testing.
PHPExcel_Calculation_Functions\getCompatibilityMode
static getCompatibilityMode()
Definition: Functions.php:138
PHPExcel_Calculation_Functions\_ifCondition
static _ifCondition($condition)
Definition: Functions.php:309
PHPExcel_Calculation_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
PHPExcel_Calculation_Functions\COMPATIBILITY_GNUMERIC
const COMPATIBILITY_GNUMERIC
Definition: Functions.php:63
PHPExcel_Calculation_Functions\flattenArray
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
PHPExcel_Calculation_Functions\DIV0
static DIV0()
Definition: Functions.php:201
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289
PHPExcel_Calculation_Functions\NaN
static NaN()
Definition: Functions.php:233
PHPExcel_Calculation_MathTrig
Definition: MathTrig.php:46
PHPExcel_Calculation_MathTrig\SUM
static SUM()
Definition: MathTrig.php:1127
PHPExcel_Calculation_MathTrig\_factors
static _factors($value)
Definition: MathTrig.php:51
PHPExcel_Calculation_MathTrig\MOD
static MOD($a=1, $b=1)
MOD.
Definition: MathTrig.php:661
PHPExcel_Calculation_MathTrig\TRUNC
static TRUNC($value=0, $digits=0)
TRUNC.
Definition: MathTrig.php:1358
PHPExcel_Calculation_MathTrig\_romanCut
static _romanCut($num, $n)
Definition: MathTrig.php:73
PHPExcel_Calculation_MathTrig\ATAN2
static ATAN2($xCoordinate=NULL, $yCoordinate=NULL)
Definition: MathTrig.php:100
PHPExcel_Calculation_MathTrig\POWER
static POWER($x=0, $y=2)
POWER.
Definition: MathTrig.php:781
PHPExcel_Calculation_MathTrig\LOG_BASE
static LOG_BASE($number=NULL, $base=10)
Definition: MathTrig.php:504
PHPExcel_Calculation_MathTrig\MDETERM
static MDETERM($matrixValues)
Definition: MathTrig.php:529
PHPExcel_Calculation_MathTrig\SUMX2MY2
static SUMX2MY2($matrixData1, $matrixData2)
SUMX2MY2.
Definition: MathTrig.php:1266
PHPExcel_Calculation_MathTrig\MMULT
static MMULT($matrixData1, $matrixData2)
MMULT.
Definition: MathTrig.php:607
PHPExcel_Calculation_MathTrig\SUBTOTAL
static SUBTOTAL()
SUBTOTAL.
Definition: MathTrig.php:1067
PHPExcel_Calculation_MathTrig\SERIESSUM
static SERIESSUM()
SERIESSUM.
Definition: MathTrig.php:983
PHPExcel_Calculation_MathTrig\FACT
static FACT($factVal)
Definition: MathTrig.php:240
PHPExcel_Calculation_MathTrig\ROUNDDOWN
static ROUNDDOWN($number, $digits)
ROUNDDOWN.
Definition: MathTrig.php:956
PHPExcel_Calculation_MathTrig\MROUND
static MROUND($number, $multiple)
MROUND.
Definition: MathTrig.php:686
PHPExcel_Calculation_MathTrig\EVEN
static EVEN($number)
Definition: MathTrig.php:209
PHPExcel_Calculation_MathTrig\SUMPRODUCT
static SUMPRODUCT()
Definition: MathTrig.php:1198
PHPExcel_Calculation_MathTrig\SUMIF
static SUMIF($aArgs, $condition, $sumArgs=array())
Definition: MathTrig.php:1158
PHPExcel_Calculation_MathTrig\SQRTPI
static SQRTPI($number)
SQRTPI.
Definition: MathTrig.php:1044
PHPExcel_Calculation_MathTrig\PRODUCT
static PRODUCT()
Definition: MathTrig.php:811
PHPExcel_Calculation_MathTrig\LCM
static LCM()
Definition: MathTrig.php:454
PHPExcel_Calculation_MathTrig\FLOOR
static FLOOR($number, $significance=NULL)
Definition: MathTrig.php:310
PHPExcel_Calculation_MathTrig\INT
static INT($number)
Definition: MathTrig.php:423
PHPExcel_Calculation_MathTrig\SUMX2PY2
static SUMX2PY2($matrixData1, $matrixData2)
SUMX2PY2.
Definition: MathTrig.php:1296
PHPExcel_Calculation_MathTrig\CEILING
static CEILING($number, $significance=NULL)
Definition: MathTrig.php:139
PHPExcel_Calculation_MathTrig\MINVERSE
static MINVERSE($matrixValues)
Definition: MathTrig.php:571
PHPExcel_Calculation_MathTrig\FACTDOUBLE
static FACTDOUBLE($factVal)
Definition: MathTrig.php:277
PHPExcel_Calculation_MathTrig\RAND
static RAND($min=0, $max=0)
RAND.
Definition: MathTrig.php:881
PHPExcel_Calculation_MathTrig\COMBIN
static COMBIN($numObjs, $numInSet)
Definition: MathTrig.php:176
PHPExcel_Calculation_MathTrig\GCD
static GCD()
Definition: MathTrig.php:349
PHPExcel_Calculation_MathTrig\ODD
static ODD($number)
ODD.
Definition: MathTrig.php:746
PHPExcel_Calculation_MathTrig\MULTINOMIAL
static MULTINOMIAL()
MULTINOMIAL.
Definition: MathTrig.php:712
PHPExcel_Calculation_MathTrig\SUMXMY2
static SUMXMY2($matrixData1, $matrixData2)
SUMXMY2.
Definition: MathTrig.php:1326
PHPExcel_Calculation_MathTrig\ROMAN
static ROMAN($aValue, $style=0)
Definition: MathTrig.php:893
PHPExcel_Calculation_MathTrig\QUOTIENT
static QUOTIENT()
Definition: MathTrig.php:849
PHPExcel_Calculation_MathTrig\ROUNDUP
static ROUNDUP($number, $digits)
ROUNDUP.
Definition: MathTrig.php:931
PHPExcel_Calculation_MathTrig\SIGN
static SIGN($number)
SIGN.
Definition: MathTrig.php:1021
PHPExcel_Calculation_MathTrig\SUMSQ
static SUMSQ()
Definition: MathTrig.php:1242
PHPExcel_Calculation_Statistical\STDEVP
static STDEVP()
Definition: Statistical.php:3096
PHPExcel_Calculation_Statistical\COUNTA
static COUNTA()
Definition: Statistical.php:1200
PHPExcel_Calculation_Statistical\MIN
static MIN()
Definition: Statistical.php:2291
PHPExcel_Calculation_Statistical\COUNT
static COUNT()
Definition: Statistical.php:1165
PHPExcel_Calculation_Statistical\STDEV
static STDEV()
Definition: Statistical.php:2997
PHPExcel_Calculation_Statistical\VARP
static VARP()
Definition: Statistical.php:3509
PHPExcel_Calculation_Statistical\MAX
static MAX()
Definition: Statistical.php:2129
PHPExcel_Calculation_Statistical\AVERAGE
static AVERAGE()
Definition: Statistical.php:748
PHPExcel_Calculation_Statistical\VARFunc
static VARFunc()
Definition: Statistical.php:3414
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
PHPExcel_Calculation\_wrapResult
static _wrapResult($value)
Wrap string values in quotes.
Definition: Calculation.php:2163
PHPExcel_Exception
Definition: Exception.php:36
PHPExcel_Shared_JAMA_Matrix
PHPExcel root directory.
Definition: Matrix.php:27
$key
$key
Definition: croninfo.php:18
$i
$i
Definition: disco.tpl.php:19
$y
$y
Definition: example_007.php:83
$x
$x
Definition: example_009.php:98
$style
$style
Definition: example_012.php:70
$base
$base
Definition: index.php:4
$keys
$keys
Definition: memcacheSync.php:38
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$m
$m
Definition: show_metadata.php:25