ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
MathTrig.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
38 
46 class 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 (count($factorArray) > 0) {
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, $yCoordinate) {
101  $xCoordinate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($xCoordinate);
102  $yCoordinate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($yCoordinate);
103 
104  if (($xCoordinate == 0) && ($yCoordinate == 0)) {
105  return PHPExcel_Calculation_Functions::DIV0();
106  }
107 
108  return atan2($yCoordinate, $xCoordinate);
109  } // function REVERSE_ATAN2()
110 
111 
121  public static function CEILING($number,$significance=null) {
122  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
123  $significance = PHPExcel_Calculation_Functions::flattenSingleValue($significance);
124 
125  if ((is_null($significance)) && (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC)) {
126  $significance = $number/abs($number);
127  }
128 
129  if ((is_numeric($number)) && (is_numeric($significance))) {
130  if (self::SIGN($number) == self::SIGN($significance)) {
131  if ($significance == 0.0) {
132  return 0;
133  }
134  return ceil($number / $significance) * $significance;
135  } else {
136  return PHPExcel_Calculation_Functions::NaN();
137  }
138  }
139  return PHPExcel_Calculation_Functions::VALUE();
140  } // function CEILING()
141 
142 
153  public static function COMBIN($numObjs,$numInSet) {
154  $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs);
155  $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet);
156 
157  if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
158  if ($numObjs < $numInSet) {
159  return PHPExcel_Calculation_Functions::NaN();
160  } elseif ($numInSet < 0) {
161  return PHPExcel_Calculation_Functions::NaN();
162  }
163  return round(self::FACT($numObjs) / self::FACT($numObjs - $numInSet)) / self::FACT($numInSet);
164  }
165  return PHPExcel_Calculation_Functions::VALUE();
166  } // function COMBIN()
167 
168 
177  public static function EVEN($number) {
178  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
179 
180  if (is_null($number)) {
181  return 0;
182  } elseif (is_numeric($number)) {
183  $significance = 2 * self::SIGN($number);
184  return (int) self::CEILING($number,$significance);
185  }
186  return PHPExcel_Calculation_Functions::VALUE();
187  } // function EVEN()
188 
189 
198  public static function FACT($factVal) {
199  $factVal = PHPExcel_Calculation_Functions::flattenSingleValue($factVal);
200 
201  if (is_numeric($factVal)) {
202  if ($factVal < 0) {
203  return PHPExcel_Calculation_Functions::NaN();
204  }
205  $factLoop = floor($factVal);
206  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
207  if ($factVal > $factLoop) {
208  return PHPExcel_Calculation_Functions::NaN();
209  }
210  }
211 
212  $factorial = 1;
213  while ($factLoop > 1) {
214  $factorial *= $factLoop--;
215  }
216  return $factorial ;
217  }
218  return PHPExcel_Calculation_Functions::VALUE();
219  } // function FACT()
220 
221 
230  public static function FACTDOUBLE($factVal) {
231  $factLoop = floor(PHPExcel_Calculation_Functions::flattenSingleValue($factVal));
232 
233  if (is_numeric($factLoop)) {
234  if ($factVal < 0) {
235  return PHPExcel_Calculation_Functions::NaN();
236  }
237  $factorial = 1;
238  while ($factLoop > 1) {
239  $factorial *= $factLoop--;
240  --$factLoop;
241  }
242  return $factorial ;
243  }
244  return PHPExcel_Calculation_Functions::VALUE();
245  } // function FACTDOUBLE()
246 
247 
257  public static function FLOOR($number,$significance=null) {
258  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
259  $significance = PHPExcel_Calculation_Functions::flattenSingleValue($significance);
260 
261  if ((is_null($significance)) && (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC)) {
262  $significance = $number/abs($number);
263  }
264 
265  if ((is_numeric($number)) && (is_numeric($significance))) {
266  if ((float) $significance == 0.0) {
267  return PHPExcel_Calculation_Functions::DIV0();
268  }
269  if (self::SIGN($number) == self::SIGN($significance)) {
270  return floor($number / $significance) * $significance;
271  } else {
272  return PHPExcel_Calculation_Functions::NaN();
273  }
274  }
275  return PHPExcel_Calculation_Functions::VALUE();
276  } // function FLOOR()
277 
278 
287  public static function GCD() {
288  $returnValue = 1;
289  $allPoweredFactors = array();
290  // Loop through arguments
291  foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) {
292  if ($value == 0) {
293  break;
294  }
295  $myFactors = self::_factors($value);
296  $myCountedFactors = array_count_values($myFactors);
297  $allValuesFactors[] = $myCountedFactors;
298  }
299  $allValuesCount = count($allValuesFactors);
300  $mergedArray = $allValuesFactors[0];
301  for ($i=1;$i < $allValuesCount; ++$i) {
302  $mergedArray = array_intersect_key($mergedArray,$allValuesFactors[$i]);
303  }
304  $mergedArrayValues = count($mergedArray);
305  if ($mergedArrayValues == 0) {
306  return $returnValue;
307  } elseif ($mergedArrayValues > 1) {
308  foreach($mergedArray as $mergedKey => $mergedValue) {
309  foreach($allValuesFactors as $highestPowerTest) {
310  foreach($highestPowerTest as $testKey => $testValue) {
311  if (($testKey == $mergedKey) && ($testValue < $mergedValue)) {
312  $mergedArray[$mergedKey] = $testValue;
313  $mergedValue = $testValue;
314  }
315  }
316  }
317  }
318 
319  $returnValue = 1;
320  foreach($mergedArray as $key => $value) {
321  $returnValue *= pow($key,$value);
322  }
323  return $returnValue;
324  } else {
325  $keys = array_keys($mergedArray);
326  $key = $keys[0];
327  $value = $mergedArray[$key];
328  foreach($allValuesFactors as $testValue) {
329  foreach($testValue as $mergedKey => $mergedValue) {
330  if (($mergedKey == $key) && ($mergedValue < $value)) {
331  $value = $mergedValue;
332  }
333  }
334  }
335  return pow($key,$value);
336  }
337  } // function GCD()
338 
339 
348  public static function INT($number) {
349  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
350 
351  if (is_numeric($number)) {
352  return (int) floor($number);
353  }
354  return PHPExcel_Calculation_Functions::VALUE();
355  } // function INT()
356 
357 
366  public static function LCM() {
367  $returnValue = 1;
368  $allPoweredFactors = array();
369  // Loop through arguments
370  foreach(PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $value) {
371  if (!is_numeric($value)) {
372  return PHPExcel_Calculation_Functions::VALUE();
373  }
374  if ($value == 0) {
375  return 0;
376  } elseif ($value < 0) {
377  return PHPExcel_Calculation_Functions::NaN();
378  }
379  $myFactors = self::_factors(floor($value));
380  $myCountedFactors = array_count_values($myFactors);
381  $myPoweredFactors = array();
382  foreach($myCountedFactors as $myCountedFactor => $myCountedPower) {
383  $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor,$myCountedPower);
384  }
385  foreach($myPoweredFactors as $myPoweredValue => $myPoweredFactor) {
386  if (array_key_exists($myPoweredValue,$allPoweredFactors)) {
387  if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) {
388  $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
389  }
390  } else {
391  $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
392  }
393  }
394  }
395  foreach($allPoweredFactors as $allPoweredFactor) {
396  $returnValue *= (integer) $allPoweredFactor;
397  }
398  return $returnValue;
399  } // function LCM()
400 
401 
416  public static function LOG_BASE($number, $base=10) {
417  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
418  $base = (is_null($base)) ? 10 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($base);
419 
420  return log($number, $base);
421  } // function LOG_BASE()
422 
423 
430  public static function MDETERM($matrixValues) {
431  $matrixData = array();
432  if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
433 
434  $row = $maxColumn = 0;
435  foreach($matrixValues as $matrixRow) {
436  $column = 0;
437  foreach($matrixRow as $matrixCell) {
438  if ((is_string($matrixCell)) || ($matrixCell === null)) {
439  return PHPExcel_Calculation_Functions::VALUE();
440  }
441  $matrixData[$column][$row] = $matrixCell;
442  ++$column;
443  }
444  if ($column > $maxColumn) { $maxColumn = $column; }
445  ++$row;
446  }
447  if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); }
448 
449  try {
450  $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
451  return $matrix->det();
452  } catch (Exception $ex) {
453  return PHPExcel_Calculation_Functions::VALUE();
454  }
455  } // function MDETERM()
456 
457 
464  public static function MINVERSE($matrixValues) {
465  $matrixData = array();
466  if (!is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
467 
468  $row = $maxColumn = 0;
469  foreach($matrixValues as $matrixRow) {
470  $column = 0;
471  foreach($matrixRow as $matrixCell) {
472  if ((is_string($matrixCell)) || ($matrixCell === null)) {
473  return PHPExcel_Calculation_Functions::VALUE();
474  }
475  $matrixData[$column][$row] = $matrixCell;
476  ++$column;
477  }
478  if ($column > $maxColumn) { $maxColumn = $column; }
479  ++$row;
480  }
481  if ($row != $maxColumn) { return PHPExcel_Calculation_Functions::VALUE(); }
482 
483  try {
484  $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
485  return $matrix->inverse()->getArray();
486  } catch (Exception $ex) {
487  return PHPExcel_Calculation_Functions::VALUE();
488  }
489  } // function MINVERSE()
490 
491 
499  public static function MMULT($matrixData1,$matrixData2) {
500  $matrixAData = $matrixBData = array();
501  if (!is_array($matrixData1)) { $matrixData1 = array(array($matrixData1)); }
502  if (!is_array($matrixData2)) { $matrixData2 = array(array($matrixData2)); }
503 
504  $rowA = 0;
505  foreach($matrixData1 as $matrixRow) {
506  $columnA = 0;
507  foreach($matrixRow as $matrixCell) {
508  if ((is_string($matrixCell)) || ($matrixCell === null)) {
509  return PHPExcel_Calculation_Functions::VALUE();
510  }
511  $matrixAData[$rowA][$columnA] = $matrixCell;
512  ++$columnA;
513  }
514  ++$rowA;
515  }
516  try {
517  $matrixA = new PHPExcel_Shared_JAMA_Matrix($matrixAData);
518  $rowB = 0;
519  foreach($matrixData2 as $matrixRow) {
520  $columnB = 0;
521  foreach($matrixRow as $matrixCell) {
522  if ((is_string($matrixCell)) || ($matrixCell === null)) {
523  return PHPExcel_Calculation_Functions::VALUE();
524  }
525  $matrixBData[$rowB][$columnB] = $matrixCell;
526  ++$columnB;
527  }
528  ++$rowB;
529  }
530  $matrixB = new PHPExcel_Shared_JAMA_Matrix($matrixBData);
531 
532  if (($rowA != $columnB) || ($rowB != $columnA)) {
533  return PHPExcel_Calculation_Functions::VALUE();
534  }
535 
536  return $matrixA->times($matrixB)->getArray();
537  } catch (Exception $ex) {
538  return PHPExcel_Calculation_Functions::VALUE();
539  }
540  } // function MMULT()
541 
542 
550  public static function MOD($a = 1, $b = 1) {
551  $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
552  $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
553 
554  if ($b == 0.0) {
555  return PHPExcel_Calculation_Functions::DIV0();
556  } elseif (($a < 0.0) && ($b > 0.0)) {
557  return $b - fmod(abs($a),$b);
558  } elseif (($a > 0.0) && ($b < 0.0)) {
559  return $b + fmod($a,abs($b));
560  }
561 
562  return fmod($a,$b);
563  } // function MOD()
564 
565 
575  public static function MROUND($number,$multiple) {
576  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
577  $multiple = PHPExcel_Calculation_Functions::flattenSingleValue($multiple);
578 
579  if ((is_numeric($number)) && (is_numeric($multiple))) {
580  if ($multiple == 0) {
581  return 0;
582  }
583  if ((self::SIGN($number)) == (self::SIGN($multiple))) {
584  $multiplier = 1 / $multiple;
585  return round($number * $multiplier) / $multiplier;
586  }
587  return PHPExcel_Calculation_Functions::NaN();
588  }
589  return PHPExcel_Calculation_Functions::VALUE();
590  } // function MROUND()
591 
592 
601  public static function MULTINOMIAL() {
602  $summer = 0;
603  $divisor = 1;
604  // Loop through arguments
605  foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
606  // Is it a numeric value?
607  if (is_numeric($arg)) {
608  if ($arg < 1) {
609  return PHPExcel_Calculation_Functions::NaN();
610  }
611  $summer += floor($arg);
612  $divisor *= self::FACT($arg);
613  } else {
614  return PHPExcel_Calculation_Functions::VALUE();
615  }
616  }
617 
618  // Return
619  if ($summer > 0) {
620  $summer = self::FACT($summer);
621  return $summer / $divisor;
622  }
623  return 0;
624  } // function MULTINOMIAL()
625 
626 
635  public static function ODD($number) {
636  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
637 
638  if (is_null($number)) {
639  return 1;
640  } elseif (is_numeric($number)) {
641  $significance = self::SIGN($number);
642  if ($significance == 0) {
643  return 1;
644  }
645 
646  $result = self::CEILING($number,$significance);
647  if ($result == self::EVEN($result)) {
648  $result += $significance;
649  }
650 
651  return (int) $result;
652  }
653  return PHPExcel_Calculation_Functions::VALUE();
654  } // function ODD()
655 
656 
666  public static function POWER($x = 0, $y = 2) {
667  $x = PHPExcel_Calculation_Functions::flattenSingleValue($x);
668  $y = PHPExcel_Calculation_Functions::flattenSingleValue($y);
669 
670  // Validate parameters
671  if ($x == 0 && $y <= 0) {
672  return PHPExcel_Calculation_Functions::DIV0();
673  }
674 
675  // Return
676  return pow($x, $y);
677  } // function POWER()
678 
679 
693  public static function PRODUCT() {
694  // Return value
695  $returnValue = null;
696 
697  // Loop through arguments
698  foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
699  // Is it a numeric value?
700  if ((is_numeric($arg)) && (!is_string($arg))) {
701  if (is_null($returnValue)) {
702  $returnValue = $arg;
703  } else {
704  $returnValue *= $arg;
705  }
706  }
707  }
708 
709  // Return
710  if (is_null($returnValue)) {
711  return 0;
712  }
713  return $returnValue;
714  } // function PRODUCT()
715 
716 
731  public static function QUOTIENT() {
732  // Return value
733  $returnValue = null;
734 
735  // Loop through arguments
736  foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
737  // Is it a numeric value?
738  if ((is_numeric($arg)) && (!is_string($arg))) {
739  if (is_null($returnValue)) {
740  $returnValue = ($arg == 0) ? 0 : $arg;
741  } else {
742  if (($returnValue == 0) || ($arg == 0)) {
743  $returnValue = 0;
744  } else {
745  $returnValue /= $arg;
746  }
747  }
748  }
749  }
750 
751  // Return
752  return intval($returnValue);
753  } // function QUOTIENT()
754 
755 
763  public static function RAND($min = 0, $max = 0) {
764  $min = PHPExcel_Calculation_Functions::flattenSingleValue($min);
765  $max = PHPExcel_Calculation_Functions::flattenSingleValue($max);
766 
767  if ($min == 0 && $max == 0) {
768  return (rand(0,10000000)) / 10000000;
769  } else {
770  return rand($min, $max);
771  }
772  } // function RAND()
773 
774 
775  public static function ROMAN($aValue, $style=0) {
776  $aValue = (integer) PHPExcel_Calculation_Functions::flattenSingleValue($aValue);
777  $style = (is_null($style)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($style);
778  if ((!is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) {
779  return PHPExcel_Calculation_Functions::VALUE();
780  }
781  if ($aValue == 0) {
782  return '';
783  }
784 
785  $mill = Array('', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM');
786  $cent = Array('', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM');
787  $tens = Array('', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC');
788  $ones = Array('', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX');
789 
790  $roman = '';
791  while ($aValue > 5999) {
792  $roman .= 'M';
793  $aValue -= 1000;
794  }
795  $m = self::_romanCut($aValue, 1000); $aValue %= 1000;
796  $c = self::_romanCut($aValue, 100); $aValue %= 100;
797  $t = self::_romanCut($aValue, 10); $aValue %= 10;
798 
799  return $roman.$mill[$m].$cent[$c].$tens[$t].$ones[$aValue];
800  } // function ROMAN()
801 
802 
812  public static function ROUNDUP($number,$digits) {
813  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
814  $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
815 
816  if ((is_numeric($number)) && (is_numeric($digits))) {
817  $significance = pow(10,$digits);
818  if ($number < 0.0) {
819  return floor($number * $significance) / $significance;
820  } else {
821  return ceil($number * $significance) / $significance;
822  }
823  }
824  return PHPExcel_Calculation_Functions::VALUE();
825  } // function ROUNDUP()
826 
827 
837  public static function ROUNDDOWN($number,$digits) {
838  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
839  $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
840 
841  if ((is_numeric($number)) && (is_numeric($digits))) {
842  $significance = pow(10,$digits);
843  if ($number < 0.0) {
844  return ceil($number * $significance) / $significance;
845  } else {
846  return floor($number * $significance) / $significance;
847  }
848  }
849  return PHPExcel_Calculation_Functions::VALUE();
850  } // function ROUNDDOWN()
851 
852 
864  public static function SERIESSUM() {
865  // Return value
866  $returnValue = 0;
867 
868  // Loop through arguments
869  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
870 
871  $x = array_shift($aArgs);
872  $n = array_shift($aArgs);
873  $m = array_shift($aArgs);
874 
875  if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) {
876  // Calculate
877  $i = 0;
878  foreach($aArgs as $arg) {
879  // Is it a numeric value?
880  if ((is_numeric($arg)) && (!is_string($arg))) {
881  $returnValue += $arg * pow($x,$n + ($m * $i++));
882  } else {
883  return PHPExcel_Calculation_Functions::VALUE();
884  }
885  }
886  // Return
887  return $returnValue;
888  }
889  return PHPExcel_Calculation_Functions::VALUE();
890  } // function SERIESSUM()
891 
892 
902  public static function SIGN($number) {
903  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
904 
905  if (is_numeric($number)) {
906  if ($number == 0.0) {
907  return 0;
908  }
909  return $number / abs($number);
910  }
911  return PHPExcel_Calculation_Functions::VALUE();
912  } // function SIGN()
913 
914 
923  public static function SQRTPI($number) {
924  $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
925 
926  if (is_numeric($number)) {
927  if ($number < 0) {
928  return PHPExcel_Calculation_Functions::NaN();
929  }
930  return sqrt($number * M_PI) ;
931  }
932  return PHPExcel_Calculation_Functions::VALUE();
933  } // function SQRTPI()
934 
935 
946  public static function SUBTOTAL() {
947  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
948 
949  // Calculate
950  $subtotal = array_shift($aArgs);
951 
952  if ((is_numeric($subtotal)) && (!is_string($subtotal))) {
953  switch($subtotal) {
954  case 1 :
955  return PHPExcel_Calculation_Statistical::AVERAGE($aArgs);
956  break;
957  case 2 :
958  return PHPExcel_Calculation_Statistical::COUNT($aArgs);
959  break;
960  case 3 :
961  return PHPExcel_Calculation_Statistical::COUNTA($aArgs);
962  break;
963  case 4 :
964  return PHPExcel_Calculation_Statistical::MAX($aArgs);
965  break;
966  case 5 :
967  return PHPExcel_Calculation_Statistical::MIN($aArgs);
968  break;
969  case 6 :
970  return self::PRODUCT($aArgs);
971  break;
972  case 7 :
973  return PHPExcel_Calculation_Statistical::STDEV($aArgs);
974  break;
975  case 8 :
976  return PHPExcel_Calculation_Statistical::STDEVP($aArgs);
977  break;
978  case 9 :
979  return self::SUM($aArgs);
980  break;
981  case 10 :
982  return PHPExcel_Calculation_Statistical::VARFunc($aArgs);
983  break;
984  case 11 :
985  return PHPExcel_Calculation_Statistical::VARP($aArgs);
986  break;
987  }
988  }
989  return PHPExcel_Calculation_Functions::VALUE();
990  } // function SUBTOTAL()
991 
992 
1006  public static function SUM() {
1007  // Return value
1008  $returnValue = 0;
1009 
1010  // Loop through the arguments
1011  foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1012  // Is it a numeric value?
1013  if ((is_numeric($arg)) && (!is_string($arg))) {
1014  $returnValue += $arg;
1015  }
1016  }
1017 
1018  // Return
1019  return $returnValue;
1020  } // function SUM()
1021 
1022 
1037  public static function SUMIF($aArgs,$condition,$sumArgs = array()) {
1038  // Return value
1039  $returnValue = 0;
1040 
1041  $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
1042  $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
1043  if (count($sumArgs) == 0) {
1044  $sumArgs = $aArgs;
1045  }
1046  $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1047  // Loop through arguments
1048  foreach ($aArgs as $key => $arg) {
1049  if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1050  $testCondition = '='.$arg.$condition;
1051  if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1052  // Is it a value within our criteria
1053  $returnValue += $sumArgs[$key];
1054  }
1055  }
1056 
1057  // Return
1058  return $returnValue;
1059  } // function SUMIF()
1060 
1061 
1068  public static function SUMPRODUCT() {
1069  $arrayList = func_get_args();
1070 
1071  $wrkArray = PHPExcel_Calculation_Functions::flattenArray(array_shift($arrayList));
1072  $wrkCellCount = count($wrkArray);
1073 
1074  foreach($arrayList as $matrixData) {
1075  $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData);
1076  $count = count($array2);
1077  if ($wrkCellCount != $count) {
1078  return PHPExcel_Calculation_Functions::VALUE();
1079  }
1080 
1081  foreach ($array2 as $i => $val) {
1082  if (((is_numeric($wrkArray[$i])) && (!is_string($wrkArray[$i]))) &&
1083  ((is_numeric($val)) && (!is_string($val)))) {
1084  $wrkArray[$i] *= $val;
1085  }
1086  }
1087  }
1088 
1089  return array_sum($wrkArray);
1090  } // function SUMPRODUCT()
1091 
1092 
1106  public static function SUMSQ() {
1107  // Return value
1108  $returnValue = 0;
1109 
1110  // Loop through arguments
1111  foreach (PHPExcel_Calculation_Functions::flattenArray(func_get_args()) as $arg) {
1112  // Is it a numeric value?
1113  if ((is_numeric($arg)) && (!is_string($arg))) {
1114  $returnValue += ($arg * $arg);
1115  }
1116  }
1117 
1118  // Return
1119  return $returnValue;
1120  } // function SUMSQ()
1121 
1122 
1129  public static function SUMX2MY2($matrixData1,$matrixData2) {
1130  $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1131  $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1132  $count1 = count($array1);
1133  $count2 = count($array2);
1134  if ($count1 < $count2) {
1135  $count = $count1;
1136  } else {
1137  $count = $count2;
1138  }
1139 
1140  $result = 0;
1141  for ($i = 0; $i < $count; ++$i) {
1142  if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1143  ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1144  $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]);
1145  }
1146  }
1147 
1148  return $result;
1149  } // function SUMX2MY2()
1150 
1151 
1158  public static function SUMX2PY2($matrixData1,$matrixData2) {
1159  $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1160  $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1161  $count1 = count($array1);
1162  $count2 = count($array2);
1163  if ($count1 < $count2) {
1164  $count = $count1;
1165  } else {
1166  $count = $count2;
1167  }
1168 
1169  $result = 0;
1170  for ($i = 0; $i < $count; ++$i) {
1171  if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1172  ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1173  $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]);
1174  }
1175  }
1176 
1177  return $result;
1178  } // function SUMX2PY2()
1179 
1180 
1187  public static function SUMXMY2($matrixData1,$matrixData2) {
1188  $array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
1189  $array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
1190  $count1 = count($array1);
1191  $count2 = count($array2);
1192  if ($count1 < $count2) {
1193  $count = $count1;
1194  } else {
1195  $count = $count2;
1196  }
1197 
1198  $result = 0;
1199  for ($i = 0; $i < $count; ++$i) {
1200  if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) &&
1201  ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) {
1202  $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]);
1203  }
1204  }
1205 
1206  return $result;
1207  } // function SUMXMY2()
1208 
1209 
1219  public static function TRUNC($value = 0, $number_digits = 0) {
1220  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1221  $number_digits = PHPExcel_Calculation_Functions::flattenSingleValue($number_digits);
1222 
1223  // Validate parameters
1224  if ($number_digits < 0) {
1225  return PHPExcel_Calculation_Functions::VALUE();
1226  }
1227 
1228  // Truncate
1229  if ($number_digits > 0) {
1230  $value = $value * pow(10, $number_digits);
1231  }
1232  $value = intval($value);
1233  if ($number_digits > 0) {
1234  $value = $value / pow(10, $number_digits);
1235  }
1236 
1237  // Return
1238  return $value;
1239  } // function TRUNC()
1240 
1241 } // class PHPExcel_Calculation_MathTrig