ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
Statistical.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 
39 require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
40 
41 
43 define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
44 
46 define('XMININ', 2.23e-308);
47 
49 define('EPS', 2.22e-16);
50 
52 define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
53 
54 
62 class PHPExcel_Calculation_Statistical {
63 
64 
65  private static function _checkTrendArrays(&$array1,&$array2) {
66  if (!is_array($array1)) { $array1 = array($array1); }
67  if (!is_array($array2)) { $array2 = array($array2); }
68 
69  $array1 = PHPExcel_Calculation_Functions::flattenArray($array1);
70  $array2 = PHPExcel_Calculation_Functions::flattenArray($array2);
71  foreach($array1 as $key => $value) {
72  if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
73  unset($array1[$key]);
74  unset($array2[$key]);
75  }
76  }
77  foreach($array2 as $key => $value) {
78  if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
79  unset($array1[$key]);
80  unset($array2[$key]);
81  }
82  }
83  $array1 = array_merge($array1);
84  $array2 = array_merge($array2);
85 
86  return True;
87  } // function _checkTrendArrays()
88 
89 
99  private static function _beta($p, $q) {
100  if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) {
101  return 0.0;
102  } else {
103  return exp(self::_logBeta($p, $q));
104  }
105  } // function _beta()
106 
107 
120  private static function _incompleteBeta($x, $p, $q) {
121  if ($x <= 0.0) {
122  return 0.0;
123  } elseif ($x >= 1.0) {
124  return 1.0;
125  } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
126  return 0.0;
127  }
128  $beta_gam = exp((0 - self::_logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
129  if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
130  return $beta_gam * self::_betaFraction($x, $p, $q) / $p;
131  } else {
132  return 1.0 - ($beta_gam * self::_betaFraction(1 - $x, $q, $p) / $q);
133  }
134  } // function _incompleteBeta()
135 
136 
137  // Function cache for _logBeta function
138  private static $_logBetaCache_p = 0.0;
139  private static $_logBetaCache_q = 0.0;
140  private static $_logBetaCache_result = 0.0;
141 
149  private static function _logBeta($p, $q) {
150  if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
151  self::$_logBetaCache_p = $p;
152  self::$_logBetaCache_q = $q;
153  if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
154  self::$_logBetaCache_result = 0.0;
155  } else {
156  self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
157  }
158  }
159  return self::$_logBetaCache_result;
160  } // function _logBeta()
161 
162 
168  private static function _betaFraction($x, $p, $q) {
169  $c = 1.0;
170  $sum_pq = $p + $q;
171  $p_plus = $p + 1.0;
172  $p_minus = $p - 1.0;
173  $h = 1.0 - $sum_pq * $x / $p_plus;
174  if (abs($h) < XMININ) {
175  $h = XMININ;
176  }
177  $h = 1.0 / $h;
178  $frac = $h;
179  $m = 1;
180  $delta = 0.0;
181  while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
182  $m2 = 2 * $m;
183  // even index for d
184  $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
185  $h = 1.0 + $d * $h;
186  if (abs($h) < XMININ) {
187  $h = XMININ;
188  }
189  $h = 1.0 / $h;
190  $c = 1.0 + $d / $c;
191  if (abs($c) < XMININ) {
192  $c = XMININ;
193  }
194  $frac *= $h * $c;
195  // odd index for d
196  $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
197  $h = 1.0 + $d * $h;
198  if (abs($h) < XMININ) {
199  $h = XMININ;
200  }
201  $h = 1.0 / $h;
202  $c = 1.0 + $d / $c;
203  if (abs($c) < XMININ) {
204  $c = XMININ;
205  }
206  $delta = $h * $c;
207  $frac *= $delta;
208  ++$m;
209  }
210  return $frac;
211  } // function _betaFraction()
212 
213 
257  // Function cache for logGamma
258  private static $_logGammaCache_result = 0.0;
259  private static $_logGammaCache_x = 0.0;
260 
261  private static function _logGamma($x) {
262  // Log Gamma related constants
263  static $lg_d1 = -0.5772156649015328605195174;
264  static $lg_d2 = 0.4227843350984671393993777;
265  static $lg_d4 = 1.791759469228055000094023;
266 
267  static $lg_p1 = array( 4.945235359296727046734888,
268  201.8112620856775083915565,
269  2290.838373831346393026739,
270  11319.67205903380828685045,
271  28557.24635671635335736389,
272  38484.96228443793359990269,
273  26377.48787624195437963534,
274  7225.813979700288197698961 );
275  static $lg_p2 = array( 4.974607845568932035012064,
276  542.4138599891070494101986,
277  15506.93864978364947665077,
278  184793.2904445632425417223,
279  1088204.76946882876749847,
280  3338152.967987029735917223,
281  5106661.678927352456275255,
282  3074109.054850539556250927 );
283  static $lg_p4 = array( 14745.02166059939948905062,
284  2426813.369486704502836312,
285  121475557.4045093227939592,
286  2663432449.630976949898078,
287  29403789566.34553899906876,
288  170266573776.5398868392998,
289  492612579337.743088758812,
290  560625185622.3951465078242 );
291 
292  static $lg_q1 = array( 67.48212550303777196073036,
293  1113.332393857199323513008,
294  7738.757056935398733233834,
295  27639.87074403340708898585,
296  54993.10206226157329794414,
297  61611.22180066002127833352,
298  36351.27591501940507276287,
299  8785.536302431013170870835 );
300  static $lg_q2 = array( 183.0328399370592604055942,
301  7765.049321445005871323047,
302  133190.3827966074194402448,
303  1136705.821321969608938755,
304  5267964.117437946917577538,
305  13467014.54311101692290052,
306  17827365.30353274213975932,
307  9533095.591844353613395747 );
308  static $lg_q4 = array( 2690.530175870899333379843,
309  639388.5654300092398984238,
310  41355999.30241388052042842,
311  1120872109.61614794137657,
312  14886137286.78813811542398,
313  101680358627.2438228077304,
314  341747634550.7377132798597,
315  446315818741.9713286462081 );
316 
317  static $lg_c = array( -0.001910444077728,
318  8.4171387781295e-4,
319  -5.952379913043012e-4,
320  7.93650793500350248e-4,
321  -0.002777777777777681622553,
322  0.08333333333333333331554247,
323  0.0057083835261 );
324 
325  // Rough estimate of the fourth root of logGamma_xBig
326  static $lg_frtbig = 2.25e76;
327  static $pnt68 = 0.6796875;
328 
329 
330  if ($x == self::$_logGammaCache_x) {
331  return self::$_logGammaCache_result;
332  }
333  $y = $x;
334  if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
335  if ($y <= EPS) {
336  $res = -log(y);
337  } elseif ($y <= 1.5) {
338  // ---------------------
339  // EPS .LT. X .LE. 1.5
340  // ---------------------
341  if ($y < $pnt68) {
342  $corr = -log($y);
343  $xm1 = $y;
344  } else {
345  $corr = 0.0;
346  $xm1 = $y - 1.0;
347  }
348  if ($y <= 0.5 || $y >= $pnt68) {
349  $xden = 1.0;
350  $xnum = 0.0;
351  for ($i = 0; $i < 8; ++$i) {
352  $xnum = $xnum * $xm1 + $lg_p1[$i];
353  $xden = $xden * $xm1 + $lg_q1[$i];
354  }
355  $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
356  } else {
357  $xm2 = $y - 1.0;
358  $xden = 1.0;
359  $xnum = 0.0;
360  for ($i = 0; $i < 8; ++$i) {
361  $xnum = $xnum * $xm2 + $lg_p2[$i];
362  $xden = $xden * $xm2 + $lg_q2[$i];
363  }
364  $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
365  }
366  } elseif ($y <= 4.0) {
367  // ---------------------
368  // 1.5 .LT. X .LE. 4.0
369  // ---------------------
370  $xm2 = $y - 2.0;
371  $xden = 1.0;
372  $xnum = 0.0;
373  for ($i = 0; $i < 8; ++$i) {
374  $xnum = $xnum * $xm2 + $lg_p2[$i];
375  $xden = $xden * $xm2 + $lg_q2[$i];
376  }
377  $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
378  } elseif ($y <= 12.0) {
379  // ----------------------
380  // 4.0 .LT. X .LE. 12.0
381  // ----------------------
382  $xm4 = $y - 4.0;
383  $xden = -1.0;
384  $xnum = 0.0;
385  for ($i = 0; $i < 8; ++$i) {
386  $xnum = $xnum * $xm4 + $lg_p4[$i];
387  $xden = $xden * $xm4 + $lg_q4[$i];
388  }
389  $res = $lg_d4 + $xm4 * ($xnum / $xden);
390  } else {
391  // ---------------------------------
392  // Evaluate for argument .GE. 12.0
393  // ---------------------------------
394  $res = 0.0;
395  if ($y <= $lg_frtbig) {
396  $res = $lg_c[6];
397  $ysq = $y * $y;
398  for ($i = 0; $i < 6; ++$i)
399  $res = $res / $ysq + $lg_c[$i];
400  }
401  $res /= $y;
402  $corr = log($y);
403  $res = $res + log(SQRT2PI) - 0.5 * $corr;
404  $res += $y * ($corr - 1.0);
405  }
406  } else {
407  // --------------------------
408  // Return for bad arguments
409  // --------------------------
410  $res = MAX_VALUE;
411  }
412  // ------------------------------
413  // Final adjustments and return
414  // ------------------------------
415  self::$_logGammaCache_x = $x;
416  self::$_logGammaCache_result = $res;
417  return $res;
418  } // function _logGamma()
419 
420 
421  //
422  // Private implementation of the incomplete Gamma function
423  //
424  private static function _incompleteGamma($a,$x) {
425  static $max = 32;
426  $summer = 0;
427  for ($n=0; $n<=$max; ++$n) {
428  $divisor = $a;
429  for ($i=1; $i<=$n; ++$i) {
430  $divisor *= ($a + $i);
431  }
432  $summer += (pow($x,$n) / $divisor);
433  }
434  return pow($x,$a) * exp(0-$x) * $summer;
435  } // function _incompleteGamma()
436 
437 
438  //
439  // Private implementation of the Gamma function
440  //
441  private static function _gamma($data) {
442  if ($data == 0.0) return 0;
443 
444  static $p0 = 1.000000000190015;
445  static $p = array ( 1 => 76.18009172947146,
446  2 => -86.50532032941677,
447  3 => 24.01409824083091,
448  4 => -1.231739572450155,
449  5 => 1.208650973866179e-3,
450  6 => -5.395239384953e-6
451  );
452 
453  $y = $x = $data;
454  $tmp = $x + 5.5;
455  $tmp -= ($x + 0.5) * log($tmp);
456 
457  $summer = $p0;
458  for ($j=1;$j<=6;++$j) {
459  $summer += ($p[$j] / ++$y);
460  }
461  return exp(0 - $tmp + log(SQRT2PI * $summer / $x));
462  } // function _gamma()
463 
464 
465  /***************************************************************************
466  * inverse_ncdf.php
467  * -------------------
468  * begin : Friday, January 16, 2004
469  * copyright : (C) 2004 Michael Nickerson
470  * email : nickersonm@yahoo.com
471  *
472  ***************************************************************************/
473  private static function _inverse_ncdf($p) {
474  // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
475  // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
476  // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
477  // I have not checked the accuracy of this implementation. Be aware that PHP
478  // will truncate the coeficcients to 14 digits.
479 
480  // You have permission to use and distribute this function freely for
481  // whatever purpose you want, but please show common courtesy and give credit
482  // where credit is due.
483 
484  // Input paramater is $p - probability - where 0 < p < 1.
485 
486  // Coefficients in rational approximations
487  static $a = array( 1 => -3.969683028665376e+01,
488  2 => 2.209460984245205e+02,
489  3 => -2.759285104469687e+02,
490  4 => 1.383577518672690e+02,
491  5 => -3.066479806614716e+01,
492  6 => 2.506628277459239e+00
493  );
494 
495  static $b = array( 1 => -5.447609879822406e+01,
496  2 => 1.615858368580409e+02,
497  3 => -1.556989798598866e+02,
498  4 => 6.680131188771972e+01,
499  5 => -1.328068155288572e+01
500  );
501 
502  static $c = array( 1 => -7.784894002430293e-03,
503  2 => -3.223964580411365e-01,
504  3 => -2.400758277161838e+00,
505  4 => -2.549732539343734e+00,
506  5 => 4.374664141464968e+00,
507  6 => 2.938163982698783e+00
508  );
509 
510  static $d = array( 1 => 7.784695709041462e-03,
511  2 => 3.224671290700398e-01,
512  3 => 2.445134137142996e+00,
513  4 => 3.754408661907416e+00
514  );
515 
516  // Define lower and upper region break-points.
517  $p_low = 0.02425; //Use lower region approx. below this
518  $p_high = 1 - $p_low; //Use upper region approx. above this
519 
520  if (0 < $p && $p < $p_low) {
521  // Rational approximation for lower region.
522  $q = sqrt(-2 * log($p));
523  return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
524  (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
525  } elseif ($p_low <= $p && $p <= $p_high) {
526  // Rational approximation for central region.
527  $q = $p - 0.5;
528  $r = $q * $q;
529  return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
530  ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
531  } elseif ($p_high < $p && $p < 1) {
532  // Rational approximation for upper region.
533  $q = sqrt(-2 * log(1 - $p));
534  return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
535  (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
536  }
537  // If 0 < p < 1, return a null value
538  return PHPExcel_Calculation_Functions::NULL();
539  } // function _inverse_ncdf()
540 
541 
542  private static function _inverse_ncdf2($prob) {
543  // Approximation of inverse standard normal CDF developed by
544  // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
545 
546  $a1 = 2.50662823884;
547  $a2 = -18.61500062529;
548  $a3 = 41.39119773534;
549  $a4 = -25.44106049637;
550 
551  $b1 = -8.4735109309;
552  $b2 = 23.08336743743;
553  $b3 = -21.06224101826;
554  $b4 = 3.13082909833;
555 
556  $c1 = 0.337475482272615;
557  $c2 = 0.976169019091719;
558  $c3 = 0.160797971491821;
559  $c4 = 2.76438810333863E-02;
560  $c5 = 3.8405729373609E-03;
561  $c6 = 3.951896511919E-04;
562  $c7 = 3.21767881768E-05;
563  $c8 = 2.888167364E-07;
564  $c9 = 3.960315187E-07;
565 
566  $y = $prob - 0.5;
567  if (abs($y) < 0.42) {
568  $z = ($y * $y);
569  $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
570  } else {
571  if ($y > 0) {
572  $z = log(-log(1 - $prob));
573  } else {
574  $z = log(-log($prob));
575  }
576  $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
577  if ($y < 0) {
578  $z = -$z;
579  }
580  }
581  return $z;
582  } // function _inverse_ncdf2()
583 
584 
585  private static function _inverse_ncdf3($p) {
586  // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
587  // Produces the normal deviate Z corresponding to a given lower
588  // tail area of P; Z is accurate to about 1 part in 10**16.
589  //
590  // This is a PHP version of the original FORTRAN code that can
591  // be found at http://lib.stat.cmu.edu/apstat/
592  $split1 = 0.425;
593  $split2 = 5;
594  $const1 = 0.180625;
595  $const2 = 1.6;
596 
597  // coefficients for p close to 0.5
598  $a0 = 3.3871328727963666080;
599  $a1 = 1.3314166789178437745E+2;
600  $a2 = 1.9715909503065514427E+3;
601  $a3 = 1.3731693765509461125E+4;
602  $a4 = 4.5921953931549871457E+4;
603  $a5 = 6.7265770927008700853E+4;
604  $a6 = 3.3430575583588128105E+4;
605  $a7 = 2.5090809287301226727E+3;
606 
607  $b1 = 4.2313330701600911252E+1;
608  $b2 = 6.8718700749205790830E+2;
609  $b3 = 5.3941960214247511077E+3;
610  $b4 = 2.1213794301586595867E+4;
611  $b5 = 3.9307895800092710610E+4;
612  $b6 = 2.8729085735721942674E+4;
613  $b7 = 5.2264952788528545610E+3;
614 
615  // coefficients for p not close to 0, 0.5 or 1.
616  $c0 = 1.42343711074968357734;
617  $c1 = 4.63033784615654529590;
618  $c2 = 5.76949722146069140550;
619  $c3 = 3.64784832476320460504;
620  $c4 = 1.27045825245236838258;
621  $c5 = 2.41780725177450611770E-1;
622  $c6 = 2.27238449892691845833E-2;
623  $c7 = 7.74545014278341407640E-4;
624 
625  $d1 = 2.05319162663775882187;
626  $d2 = 1.67638483018380384940;
627  $d3 = 6.89767334985100004550E-1;
628  $d4 = 1.48103976427480074590E-1;
629  $d5 = 1.51986665636164571966E-2;
630  $d6 = 5.47593808499534494600E-4;
631  $d7 = 1.05075007164441684324E-9;
632 
633  // coefficients for p near 0 or 1.
634  $e0 = 6.65790464350110377720;
635  $e1 = 5.46378491116411436990;
636  $e2 = 1.78482653991729133580;
637  $e3 = 2.96560571828504891230E-1;
638  $e4 = 2.65321895265761230930E-2;
639  $e5 = 1.24266094738807843860E-3;
640  $e6 = 2.71155556874348757815E-5;
641  $e7 = 2.01033439929228813265E-7;
642 
643  $f1 = 5.99832206555887937690E-1;
644  $f2 = 1.36929880922735805310E-1;
645  $f3 = 1.48753612908506148525E-2;
646  $f4 = 7.86869131145613259100E-4;
647  $f5 = 1.84631831751005468180E-5;
648  $f6 = 1.42151175831644588870E-7;
649  $f7 = 2.04426310338993978564E-15;
650 
651  $q = $p - 0.5;
652 
653  // computation for p close to 0.5
654  if (abs($q) <= split1) {
655  $R = $const1 - $q * $q;
656  $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
657  ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
658  } else {
659  if ($q < 0) {
660  $R = $p;
661  } else {
662  $R = 1 - $p;
663  }
664  $R = pow(-log($R),2);
665 
666  // computation for p not close to 0, 0.5 or 1.
667  If ($R <= $split2) {
668  $R = $R - $const2;
669  $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
670  ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
671  } else {
672  // computation for p near 0 or 1.
673  $R = $R - $split2;
674  $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
675  ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
676  }
677  if ($q < 0) {
678  $z = -$z;
679  }
680  }
681  return $z;
682  } // function _inverse_ncdf3()
683 
684 
699  public static function AVEDEV() {
700  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
701 
702  // Return value
703  $returnValue = null;
704 
705  $aMean = self::AVERAGE($aArgs);
706  if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
707  $aCount = 0;
708  foreach ($aArgs as $k => $arg) {
709  if ((is_bool($arg)) &&
710  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
711  $arg = (integer) $arg;
712  }
713  // Is it a numeric value?
714  if ((is_numeric($arg)) && (!is_string($arg))) {
715  if (is_null($returnValue)) {
716  $returnValue = abs($arg - $aMean);
717  } else {
718  $returnValue += abs($arg - $aMean);
719  }
720  ++$aCount;
721  }
722  }
723 
724  // Return
725  if ($aCount == 0) {
726  return PHPExcel_Calculation_Functions::DIV0();
727  }
728  return $returnValue / $aCount;
729  }
730  return PHPExcel_Calculation_Functions::NaN();
731  } // function AVEDEV()
732 
733 
747  public static function AVERAGE() {
748  $returnValue = $aCount = 0;
749 
750  // Loop through arguments
751  foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
752  if ((is_bool($arg)) &&
753  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
754  $arg = (integer) $arg;
755  }
756  // Is it a numeric value?
757  if ((is_numeric($arg)) && (!is_string($arg))) {
758  if (is_null($returnValue)) {
759  $returnValue = $arg;
760  } else {
761  $returnValue += $arg;
762  }
763  ++$aCount;
764  }
765  }
766 
767  // Return
768  if ($aCount > 0) {
769  return $returnValue / $aCount;
770  } else {
771  return PHPExcel_Calculation_Functions::DIV0();
772  }
773  } // function AVERAGE()
774 
775 
789  public static function AVERAGEA() {
790  // Return value
791  $returnValue = null;
792 
793  $aCount = 0;
794  // Loop through arguments
795  foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
796  if ((is_bool($arg)) &&
797  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
798  } else {
799  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
800  if (is_bool($arg)) {
801  $arg = (integer) $arg;
802  } elseif (is_string($arg)) {
803  $arg = 0;
804  }
805  if (is_null($returnValue)) {
806  $returnValue = $arg;
807  } else {
808  $returnValue += $arg;
809  }
810  ++$aCount;
811  }
812  }
813  }
814 
815  // Return
816  if ($aCount > 0) {
817  return $returnValue / $aCount;
818  } else {
819  return PHPExcel_Calculation_Functions::DIV0();
820  }
821  } // function AVERAGEA()
822 
823 
838  public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
839  // Return value
840  $returnValue = 0;
841 
842  $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
843  $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
844  if (count($averageArgs) == 0) {
845  $averageArgs = $aArgs;
846  }
847  $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
848  // Loop through arguments
849  $aCount = 0;
850  foreach ($aArgs as $key => $arg) {
851  if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
852  $testCondition = '='.$arg.$condition;
853  if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
854  if ((is_null($returnValue)) || ($arg > $returnValue)) {
855  $returnValue += $arg;
856  ++$aCount;
857  }
858  }
859  }
860 
861  // Return
862  if ($aCount > 0) {
863  return $returnValue / $aCount;
864  } else {
865  return PHPExcel_Calculation_Functions::DIV0();
866  }
867  } // function AVERAGEIF()
868 
869 
882  public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
883  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
884  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
885  $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
886  $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
887  $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
888 
889  if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
890  if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
891  return PHPExcel_Calculation_Functions::NaN();
892  }
893  if ($rMin > $rMax) {
894  $tmp = $rMin;
895  $rMin = $rMax;
896  $rMax = $tmp;
897  }
898  $value -= $rMin;
899  $value /= ($rMax - $rMin);
900  return self::_incompleteBeta($value,$alpha,$beta);
901  }
902  return PHPExcel_Calculation_Functions::VALUE();
903  } // function BETADIST()
904 
905 
918  public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
919  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
920  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
921  $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
922  $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
923  $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
924 
925  if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
926  if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
927  return PHPExcel_Calculation_Functions::NaN();
928  }
929  if ($rMin > $rMax) {
930  $tmp = $rMin;
931  $rMin = $rMax;
932  $rMax = $tmp;
933  }
934  $a = 0;
935  $b = 2;
936 
937  $i = 0;
938  while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
939  $guess = ($a + $b) / 2;
940  $result = self::BETADIST($guess, $alpha, $beta);
941  if (($result == $probability) || ($result == 0)) {
942  $b = $a;
943  } elseif ($result > $probability) {
944  $b = $guess;
945  } else {
946  $a = $guess;
947  }
948  }
949  if ($i == MAX_ITERATIONS) {
950  return PHPExcel_Calculation_Functions::NA();
951  }
952  return round($rMin + $guess * ($rMax - $rMin),12);
953  }
954  return PHPExcel_Calculation_Functions::VALUE();
955  } // function BETAINV()
956 
957 
976  public static function BINOMDIST($value, $trials, $probability, $cumulative) {
977  $value = floor(PHPExcel_Calculation_Functions::flattenSingleValue($value));
978  $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
979  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
980 
981  if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
982  if (($value < 0) || ($value > $trials)) {
983  return PHPExcel_Calculation_Functions::NaN();
984  }
985  if (($probability < 0) || ($probability > 1)) {
986  return PHPExcel_Calculation_Functions::NaN();
987  }
988  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
989  if ($cumulative) {
990  $summer = 0;
991  for ($i = 0; $i <= $value; ++$i) {
992  $summer += PHPExcel_Calculation_MathTrig::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
993  }
994  return $summer;
995  } else {
996  return PHPExcel_Calculation_MathTrig::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
997  }
998  }
999  }
1000  return PHPExcel_Calculation_Functions::VALUE();
1001  } // function BINOMDIST()
1002 
1003 
1013  public static function CHIDIST($value, $degrees) {
1014  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1015  $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1016 
1017  if ((is_numeric($value)) && (is_numeric($degrees))) {
1018  if ($degrees < 1) {
1019  return PHPExcel_Calculation_Functions::NaN();
1020  }
1021  if ($value < 0) {
1022  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
1023  return 1;
1024  }
1025  return PHPExcel_Calculation_Functions::NaN();
1026  }
1027  return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
1028  }
1029  return PHPExcel_Calculation_Functions::VALUE();
1030  } // function CHIDIST()
1031 
1032 
1042  public static function CHIINV($probability, $degrees) {
1043  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1044  $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1045 
1046  if ((is_numeric($probability)) && (is_numeric($degrees))) {
1047 
1048  $xLo = 100;
1049  $xHi = 0;
1050 
1051  $x = $xNew = 1;
1052  $dx = 1;
1053  $i = 0;
1054 
1055  while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1056  // Apply Newton-Raphson step
1057  $result = self::CHIDIST($x, $degrees);
1058  $error = $result - $probability;
1059  if ($error == 0.0) {
1060  $dx = 0;
1061  } elseif ($error < 0.0) {
1062  $xLo = $x;
1063  } else {
1064  $xHi = $x;
1065  }
1066  // Avoid division by zero
1067  if ($result != 0.0) {
1068  $dx = $error / $result;
1069  $xNew = $x - $dx;
1070  }
1071  // If the NR fails to converge (which for example may be the
1072  // case if the initial guess is too rough) we apply a bisection
1073  // step to determine a more narrow interval around the root.
1074  if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
1075  $xNew = ($xLo + $xHi) / 2;
1076  $dx = $xNew - $x;
1077  }
1078  $x = $xNew;
1079  }
1080  if ($i == MAX_ITERATIONS) {
1081  return PHPExcel_Calculation_Functions::NA();
1082  }
1083  return round($x,12);
1084  }
1085  return PHPExcel_Calculation_Functions::VALUE();
1086  } // function CHIINV()
1087 
1088 
1100  public static function CONFIDENCE($alpha,$stdDev,$size) {
1101  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1102  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
1103  $size = floor(PHPExcel_Calculation_Functions::flattenSingleValue($size));
1104 
1105  if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
1106  if (($alpha <= 0) || ($alpha >= 1)) {
1107  return PHPExcel_Calculation_Functions::NaN();
1108  }
1109  if (($stdDev <= 0) || ($size < 1)) {
1110  return PHPExcel_Calculation_Functions::NaN();
1111  }
1112  return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
1113  }
1114  return PHPExcel_Calculation_Functions::VALUE();
1115  } // function CONFIDENCE()
1116 
1117 
1127  public static function CORREL($yValues,$xValues=null) {
1128  if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
1129  return PHPExcel_Calculation_Functions::VALUE();
1130  }
1131  if (!self::_checkTrendArrays($yValues,$xValues)) {
1132  return PHPExcel_Calculation_Functions::VALUE();
1133  }
1134  $yValueCount = count($yValues);
1135  $xValueCount = count($xValues);
1136 
1137  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1138  return PHPExcel_Calculation_Functions::NA();
1139  } elseif ($yValueCount == 1) {
1140  return PHPExcel_Calculation_Functions::DIV0();
1141  }
1142 
1143  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1144  return $bestFitLinear->getCorrelation();
1145  } // function CORREL()
1146 
1147 
1161  public static function COUNT() {
1162  // Return value
1163  $returnValue = 0;
1164 
1165  // Loop through arguments
1166  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1167  foreach ($aArgs as $k => $arg) {
1168  if ((is_bool($arg)) &&
1169  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
1170  $arg = (integer) $arg;
1171  }
1172  // Is it a numeric value?
1173  if ((is_numeric($arg)) && (!is_string($arg))) {
1174  ++$returnValue;
1175  }
1176  }
1177 
1178  // Return
1179  return $returnValue;
1180  } // function COUNT()
1181 
1182 
1196  public static function COUNTA() {
1197  // Return value
1198  $returnValue = 0;
1199 
1200  // Loop through arguments
1201  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1202  foreach ($aArgs as $arg) {
1203  // Is it a numeric, boolean or string value?
1204  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1205  ++$returnValue;
1206  }
1207  }
1208 
1209  // Return
1210  return $returnValue;
1211  } // function COUNTA()
1212 
1213 
1227  public static function COUNTBLANK() {
1228  // Return value
1229  $returnValue = 0;
1230 
1231  // Loop through arguments
1232  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1233  foreach ($aArgs as $arg) {
1234  // Is it a blank cell?
1235  if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
1236  ++$returnValue;
1237  }
1238  }
1239 
1240  // Return
1241  return $returnValue;
1242  } // function COUNTBLANK()
1243 
1244 
1259  public static function COUNTIF($aArgs,$condition) {
1260  // Return value
1261  $returnValue = 0;
1262 
1263  $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
1264  $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1265  // Loop through arguments
1266  foreach ($aArgs as $arg) {
1267  if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1268  $testCondition = '='.$arg.$condition;
1269  if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1270  // Is it a value within our criteria
1271  ++$returnValue;
1272  }
1273  }
1274 
1275  // Return
1276  return $returnValue;
1277  } // function COUNTIF()
1278 
1279 
1289  public static function COVAR($yValues,$xValues) {
1290  if (!self::_checkTrendArrays($yValues,$xValues)) {
1291  return PHPExcel_Calculation_Functions::VALUE();
1292  }
1293  $yValueCount = count($yValues);
1294  $xValueCount = count($xValues);
1295 
1296  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1297  return PHPExcel_Calculation_Functions::NA();
1298  } elseif ($yValueCount == 1) {
1299  return PHPExcel_Calculation_Functions::DIV0();
1300  }
1301 
1302  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1303  return $bestFitLinear->getCovariance();
1304  } // function COVAR()
1305 
1306 
1326  public static function CRITBINOM($trials, $probability, $alpha) {
1327  $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
1328  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1329  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1330 
1331  if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
1332  if ($trials < 0) {
1333  return PHPExcel_Calculation_Functions::NaN();
1334  }
1335  if (($probability < 0) || ($probability > 1)) {
1336  return PHPExcel_Calculation_Functions::NaN();
1337  }
1338  if (($alpha < 0) || ($alpha > 1)) {
1339  return PHPExcel_Calculation_Functions::NaN();
1340  }
1341  if ($alpha <= 0.5) {
1342  $t = sqrt(log(1 / ($alpha * $alpha)));
1343  $trialsApprox = 0 - ($t + (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t));
1344  } else {
1345  $t = sqrt(log(1 / pow(1 - $alpha,2)));
1346  $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
1347  }
1348  $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
1349  if ($Guess < 0) {
1350  $Guess = 0;
1351  } elseif ($Guess > $trials) {
1352  $Guess = $trials;
1353  }
1354 
1355  $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
1356  $EssentiallyZero = 10e-12;
1357 
1358  $m = floor($trials * $probability);
1359  ++$TotalUnscaledProbability;
1360  if ($m == $Guess) { ++$UnscaledPGuess; }
1361  if ($m <= $Guess) { ++$UnscaledCumPGuess; }
1362 
1363  $PreviousValue = 1;
1364  $Done = False;
1365  $k = $m + 1;
1366  while ((!$Done) && ($k <= $trials)) {
1367  $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
1368  $TotalUnscaledProbability += $CurrentValue;
1369  if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1370  if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1371  if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1372  $PreviousValue = $CurrentValue;
1373  ++$k;
1374  }
1375 
1376  $PreviousValue = 1;
1377  $Done = False;
1378  $k = $m - 1;
1379  while ((!$Done) && ($k >= 0)) {
1380  $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
1381  $TotalUnscaledProbability += $CurrentValue;
1382  if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1383  if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1384  if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1385  $PreviousValue = $CurrentValue;
1386  --$k;
1387  }
1388 
1389  $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
1390  $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
1391 
1392 // $CumPGuessMinus1 = $CumPGuess - $PGuess;
1393  $CumPGuessMinus1 = $CumPGuess - 1;
1394 
1395  while (True) {
1396  if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
1397  return $Guess;
1398  } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
1399  $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
1400  $CumPGuessMinus1 = $CumPGuess;
1401  $CumPGuess = $CumPGuess + $PGuessPlus1;
1402  $PGuess = $PGuessPlus1;
1403  ++$Guess;
1404  } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
1405  $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
1406  $CumPGuess = $CumPGuessMinus1;
1407  $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
1408  $PGuess = $PGuessMinus1;
1409  --$Guess;
1410  }
1411  }
1412  }
1413  return PHPExcel_Calculation_Functions::VALUE();
1414  } // function CRITBINOM()
1415 
1416 
1430  public static function DEVSQ() {
1431  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1432 
1433  // Return value
1434  $returnValue = null;
1435 
1436  $aMean = self::AVERAGE($aArgs);
1437  if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
1438  $aCount = -1;
1439  foreach ($aArgs as $k => $arg) {
1440  // Is it a numeric value?
1441  if ((is_bool($arg)) &&
1442  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
1443  $arg = (integer) $arg;
1444  }
1445  if ((is_numeric($arg)) && (!is_string($arg))) {
1446  if (is_null($returnValue)) {
1447  $returnValue = pow(($arg - $aMean),2);
1448  } else {
1449  $returnValue += pow(($arg - $aMean),2);
1450  }
1451  ++$aCount;
1452  }
1453  }
1454 
1455  // Return
1456  if (is_null($returnValue)) {
1457  return PHPExcel_Calculation_Functions::NaN();
1458  } else {
1459  return $returnValue;
1460  }
1461  }
1462  return self::NA();
1463  } // function DEVSQ()
1464 
1465 
1478  public static function EXPONDIST($value, $lambda, $cumulative) {
1479  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1480  $lambda = PHPExcel_Calculation_Functions::flattenSingleValue($lambda);
1481  $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
1482 
1483  if ((is_numeric($value)) && (is_numeric($lambda))) {
1484  if (($value < 0) || ($lambda < 0)) {
1485  return PHPExcel_Calculation_Functions::NaN();
1486  }
1487  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1488  if ($cumulative) {
1489  return 1 - exp(0-$value*$lambda);
1490  } else {
1491  return $lambda * exp(0-$value*$lambda);
1492  }
1493  }
1494  }
1495  return PHPExcel_Calculation_Functions::VALUE();
1496  } // function EXPONDIST()
1497 
1498 
1509  public static function FISHER($value) {
1510  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1511 
1512  if (is_numeric($value)) {
1513  if (($value <= -1) || ($value >= 1)) {
1514  return PHPExcel_Calculation_Functions::NaN();
1515  }
1516  return 0.5 * log((1+$value)/(1-$value));
1517  }
1518  return PHPExcel_Calculation_Functions::VALUE();
1519  } // function FISHER()
1520 
1521 
1532  public static function FISHERINV($value) {
1533  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1534 
1535  if (is_numeric($value)) {
1536  return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
1537  }
1538  return PHPExcel_Calculation_Functions::VALUE();
1539  } // function FISHERINV()
1540 
1541 
1552  public static function FORECAST($xValue,$yValues,$xValues) {
1553  $xValue = PHPExcel_Calculation_Functions::flattenSingleValue($xValue);
1554  if (!is_numeric($xValue)) {
1555  return PHPExcel_Calculation_Functions::VALUE();
1556  }
1557 
1558  if (!self::_checkTrendArrays($yValues,$xValues)) {
1559  return PHPExcel_Calculation_Functions::VALUE();
1560  }
1561  $yValueCount = count($yValues);
1562  $xValueCount = count($xValues);
1563 
1564  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1565  return PHPExcel_Calculation_Functions::NA();
1566  } elseif ($yValueCount == 1) {
1567  return PHPExcel_Calculation_Functions::DIV0();
1568  }
1569 
1570  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1571  return $bestFitLinear->getValueOfYForX($xValue);
1572  } // function FORECAST()
1573 
1574 
1587  public static function GAMMADIST($value,$a,$b,$cumulative) {
1588  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1589  $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
1590  $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
1591 
1592  if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
1593  if (($value < 0) || ($a <= 0) || ($b <= 0)) {
1594  return PHPExcel_Calculation_Functions::NaN();
1595  }
1596  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1597  if ($cumulative) {
1598  return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
1599  } else {
1600  return (1 / (pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
1601  }
1602  }
1603  }
1604  return PHPExcel_Calculation_Functions::VALUE();
1605  } // function GAMMADIST()
1606 
1607 
1619  public static function GAMMAINV($probability,$alpha,$beta) {
1620  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1621  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1622  $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
1623 
1624  if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
1625  if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
1626  return PHPExcel_Calculation_Functions::NaN();
1627  }
1628 
1629  $xLo = 0;
1630  $xHi = $alpha * $beta * 5;
1631 
1632  $x = $xNew = 1;
1633  $error = $pdf = 0;
1634  $dx = 1024;
1635  $i = 0;
1636 
1637  while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1638  // Apply Newton-Raphson step
1639  $error = self::GAMMADIST($x, $alpha, $beta, True) - $probability;
1640  if ($error < 0.0) {
1641  $xLo = $x;
1642  } else {
1643  $xHi = $x;
1644  }
1645  $pdf = self::GAMMADIST($x, $alpha, $beta, False);
1646  // Avoid division by zero
1647  if ($pdf != 0.0) {
1648  $dx = $error / $pdf;
1649  $xNew = $x - $dx;
1650  }
1651  // If the NR fails to converge (which for example may be the
1652  // case if the initial guess is too rough) we apply a bisection
1653  // step to determine a more narrow interval around the root.
1654  if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) {
1655  $xNew = ($xLo + $xHi) / 2;
1656  $dx = $xNew - $x;
1657  }
1658  $x = $xNew;
1659  }
1660  if ($i == MAX_ITERATIONS) {
1661  return PHPExcel_Calculation_Functions::NA();
1662  }
1663  return $x;
1664  }
1665  return PHPExcel_Calculation_Functions::VALUE();
1666  } // function GAMMAINV()
1667 
1668 
1677  public static function GAMMALN($value) {
1678  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1679 
1680  if (is_numeric($value)) {
1681  if ($value <= 0) {
1682  return PHPExcel_Calculation_Functions::NaN();
1683  }
1684  return log(self::_gamma($value));
1685  }
1686  return PHPExcel_Calculation_Functions::VALUE();
1687  } // function GAMMALN()
1688 
1689 
1705  public static function GEOMEAN() {
1706  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1707 
1708  $aMean = PHPExcel_Calculation_MathTrig::PRODUCT($aArgs);
1709  if (is_numeric($aMean) && ($aMean > 0)) {
1710  $aCount = self::COUNT($aArgs) ;
1711  if (self::MIN($aArgs) > 0) {
1712  return pow($aMean, (1 / $aCount));
1713  }
1714  }
1715  return PHPExcel_Calculation_Functions::NaN();
1716  } // GEOMEAN()
1717 
1718 
1730  public static function GROWTH($yValues,$xValues=array(),$newValues=array(),$const=True) {
1731  $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
1732  $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
1733  $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
1734  $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
1735 
1736  $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
1737  if (count($newValues) == 0) {
1738  $newValues = $bestFitExponential->getXValues();
1739  }
1740 
1741  $returnArray = array();
1742  foreach($newValues as $xValue) {
1743  $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue);
1744  }
1745 
1746  return $returnArray;
1747  } // function GROWTH()
1748 
1749 
1764  public static function HARMEAN() {
1765  // Return value
1766  $returnValue = PHPExcel_Calculation_Functions::NA();
1767 
1768  // Loop through arguments
1769  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1770  if (self::MIN($aArgs) < 0) {
1771  return PHPExcel_Calculation_Functions::NaN();
1772  }
1773  $aCount = 0;
1774  foreach ($aArgs as $arg) {
1775  // Is it a numeric value?
1776  if ((is_numeric($arg)) && (!is_string($arg))) {
1777  if ($arg <= 0) {
1778  return PHPExcel_Calculation_Functions::NaN();
1779  }
1780  if (is_null($returnValue)) {
1781  $returnValue = (1 / $arg);
1782  } else {
1783  $returnValue += (1 / $arg);
1784  }
1785  ++$aCount;
1786  }
1787  }
1788 
1789  // Return
1790  if ($aCount > 0) {
1791  return 1 / ($returnValue / $aCount);
1792  } else {
1793  return $returnValue;
1794  }
1795  } // function HARMEAN()
1796 
1797 
1811  public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
1812  $sampleSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleSuccesses));
1813  $sampleNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleNumber));
1814  $populationSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationSuccesses));
1815  $populationNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationNumber));
1816 
1817  if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) {
1818  if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
1819  return PHPExcel_Calculation_Functions::NaN();
1820  }
1821  if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
1822  return PHPExcel_Calculation_Functions::NaN();
1823  }
1824  if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
1825  return PHPExcel_Calculation_Functions::NaN();
1826  }
1827  return PHPExcel_Calculation_MathTrig::COMBIN($populationSuccesses,$sampleSuccesses) *
1828  PHPExcel_Calculation_MathTrig::COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
1829  PHPExcel_Calculation_MathTrig::COMBIN($populationNumber,$sampleNumber);
1830  }
1831  return PHPExcel_Calculation_Functions::VALUE();
1832  } // function HYPGEOMDIST()
1833 
1834 
1844  public static function INTERCEPT($yValues,$xValues) {
1845  if (!self::_checkTrendArrays($yValues,$xValues)) {
1846  return PHPExcel_Calculation_Functions::VALUE();
1847  }
1848  $yValueCount = count($yValues);
1849  $xValueCount = count($xValues);
1850 
1851  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1852  return PHPExcel_Calculation_Functions::NA();
1853  } elseif ($yValueCount == 1) {
1854  return PHPExcel_Calculation_Functions::DIV0();
1855  }
1856 
1857  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1858  return $bestFitLinear->getIntersect();
1859  } // function INTERCEPT()
1860 
1861 
1873  public static function KURT() {
1874  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1875  $mean = self::AVERAGE($aArgs);
1876  $stdDev = self::STDEV($aArgs);
1877 
1878  if ($stdDev > 0) {
1879  $count = $summer = 0;
1880  // Loop through arguments
1881  foreach ($aArgs as $k => $arg) {
1882  if ((is_bool($arg)) &&
1883  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
1884  } else {
1885  // Is it a numeric value?
1886  if ((is_numeric($arg)) && (!is_string($arg))) {
1887  $summer += pow((($arg - $mean) / $stdDev),4) ;
1888  ++$count;
1889  }
1890  }
1891  }
1892 
1893  // Return
1894  if ($count > 3) {
1895  return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1,2) / (($count-2) * ($count-3)));
1896  }
1897  }
1898  return PHPExcel_Calculation_Functions::DIV0();
1899  } // function KURT()
1900 
1901 
1918  public static function LARGE() {
1919  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1920 
1921  // Calculate
1922  $entry = floor(array_pop($aArgs));
1923 
1924  if ((is_numeric($entry)) && (!is_string($entry))) {
1925  $mArgs = array();
1926  foreach ($aArgs as $arg) {
1927  // Is it a numeric value?
1928  if ((is_numeric($arg)) && (!is_string($arg))) {
1929  $mArgs[] = $arg;
1930  }
1931  }
1932  $count = self::COUNT($mArgs);
1933  $entry = floor(--$entry);
1934  if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
1935  return PHPExcel_Calculation_Functions::NaN();
1936  }
1937  rsort($mArgs);
1938  return $mArgs[$entry];
1939  }
1940  return PHPExcel_Calculation_Functions::VALUE();
1941  } // function LARGE()
1942 
1943 
1956  public static function LINEST($yValues,$xValues=null,$const=True,$stats=False) {
1957  $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
1958  $stats = (is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
1959  if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
1960 
1961  if (!self::_checkTrendArrays($yValues,$xValues)) {
1962  return PHPExcel_Calculation_Functions::VALUE();
1963  }
1964  $yValueCount = count($yValues);
1965  $xValueCount = count($xValues);
1966 
1967 
1968  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1969  return PHPExcel_Calculation_Functions::NA();
1970  } elseif ($yValueCount == 1) {
1971  return 0;
1972  }
1973 
1974  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
1975  if ($stats) {
1976  return array( array( $bestFitLinear->getSlope(),
1977  $bestFitLinear->getSlopeSE(),
1978  $bestFitLinear->getGoodnessOfFit(),
1979  $bestFitLinear->getF(),
1980  $bestFitLinear->getSSRegression(),
1981  ),
1982  array( $bestFitLinear->getIntersect(),
1983  $bestFitLinear->getIntersectSE(),
1984  $bestFitLinear->getStdevOfResiduals(),
1985  $bestFitLinear->getDFResiduals(),
1986  $bestFitLinear->getSSResiduals()
1987  )
1988  );
1989  } else {
1990  return array( $bestFitLinear->getSlope(),
1991  $bestFitLinear->getIntersect()
1992  );
1993  }
1994  } // function LINEST()
1995 
1996 
2009  public static function LOGEST($yValues,$xValues=null,$const=True,$stats=False) {
2010  $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
2011  $stats = (is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
2012  if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
2013 
2014  if (!self::_checkTrendArrays($yValues,$xValues)) {
2015  return PHPExcel_Calculation_Functions::VALUE();
2016  }
2017  $yValueCount = count($yValues);
2018  $xValueCount = count($xValues);
2019 
2020  foreach($yValues as $value) {
2021  if ($value <= 0.0) {
2022  return PHPExcel_Calculation_Functions::NaN();
2023  }
2024  }
2025 
2026 
2027  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2028  return PHPExcel_Calculation_Functions::NA();
2029  } elseif ($yValueCount == 1) {
2030  return 1;
2031  }
2032 
2033  $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
2034  if ($stats) {
2035  return array( array( $bestFitExponential->getSlope(),
2036  $bestFitExponential->getSlopeSE(),
2037  $bestFitExponential->getGoodnessOfFit(),
2038  $bestFitExponential->getF(),
2039  $bestFitExponential->getSSRegression(),
2040  ),
2041  array( $bestFitExponential->getIntersect(),
2042  $bestFitExponential->getIntersectSE(),
2043  $bestFitExponential->getStdevOfResiduals(),
2044  $bestFitExponential->getDFResiduals(),
2045  $bestFitExponential->getSSResiduals()
2046  )
2047  );
2048  } else {
2049  return array( $bestFitExponential->getSlope(),
2050  $bestFitExponential->getIntersect()
2051  );
2052  }
2053  } // function LOGEST()
2054 
2055 
2068  public static function LOGINV($probability, $mean, $stdDev) {
2069  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2070  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2071  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2072 
2073  if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2074  if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
2075  return PHPExcel_Calculation_Functions::NaN();
2076  }
2077  return exp($mean + $stdDev * self::NORMSINV($probability));
2078  }
2079  return PHPExcel_Calculation_Functions::VALUE();
2080  } // function LOGINV()
2081 
2082 
2092  public static function LOGNORMDIST($value, $mean, $stdDev) {
2093  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2094  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2095  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2096 
2097  if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2098  if (($value <= 0) || ($stdDev <= 0)) {
2099  return PHPExcel_Calculation_Functions::NaN();
2100  }
2101  return self::NORMSDIST((log($value) - $mean) / $stdDev);
2102  }
2103  return PHPExcel_Calculation_Functions::VALUE();
2104  } // function LOGNORMDIST()
2105 
2106 
2121  public static function MAX() {
2122  // Return value
2123  $returnValue = null;
2124 
2125  // Loop through arguments
2126  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2127  foreach ($aArgs as $arg) {
2128  // Is it a numeric value?
2129  if ((is_numeric($arg)) && (!is_string($arg))) {
2130  if ((is_null($returnValue)) || ($arg > $returnValue)) {
2131  $returnValue = $arg;
2132  }
2133  }
2134  }
2135 
2136  // Return
2137  if(is_null($returnValue)) {
2138  return 0;
2139  }
2140  return $returnValue;
2141  } // function MAX()
2142 
2143 
2157  public static function MAXA() {
2158  // Return value
2159  $returnValue = null;
2160 
2161  // Loop through arguments
2162  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2163  foreach ($aArgs as $arg) {
2164  // Is it a numeric value?
2165  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
2166  if (is_bool($arg)) {
2167  $arg = (integer) $arg;
2168  } elseif (is_string($arg)) {
2169  $arg = 0;
2170  }
2171  if ((is_null($returnValue)) || ($arg > $returnValue)) {
2172  $returnValue = $arg;
2173  }
2174  }
2175  }
2176 
2177  // Return
2178  if(is_null($returnValue)) {
2179  return 0;
2180  }
2181  return $returnValue;
2182  } // function MAXA()
2183 
2184 
2199  public static function MAXIF($aArgs,$condition,$sumArgs = array()) {
2200  // Return value
2201  $returnValue = null;
2202 
2203  $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
2204  $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
2205  if (count($sumArgs) == 0) {
2206  $sumArgs = $aArgs;
2207  }
2208  $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2209  // Loop through arguments
2210  foreach ($aArgs as $key => $arg) {
2211  if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
2212  $testCondition = '='.$arg.$condition;
2213  if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
2214  if ((is_null($returnValue)) || ($arg > $returnValue)) {
2215  $returnValue = $arg;
2216  }
2217  }
2218  }
2219 
2220  // Return
2221  return $returnValue;
2222  } // function MAXIF()
2223 
2224 
2238  public static function MEDIAN() {
2239  // Return value
2240  $returnValue = PHPExcel_Calculation_Functions::NaN();
2241 
2242  $mArgs = array();
2243  // Loop through arguments
2244  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2245  foreach ($aArgs as $arg) {
2246  // Is it a numeric value?
2247  if ((is_numeric($arg)) && (!is_string($arg))) {
2248  $mArgs[] = $arg;
2249  }
2250  }
2251 
2252  $mValueCount = count($mArgs);
2253  if ($mValueCount > 0) {
2254  sort($mArgs,SORT_NUMERIC);
2255  $mValueCount = $mValueCount / 2;
2256  if ($mValueCount == floor($mValueCount)) {
2257  $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
2258  } else {
2259  $mValueCount == floor($mValueCount);
2260  $returnValue = $mArgs[$mValueCount];
2261  }
2262  }
2263 
2264  // Return
2265  return $returnValue;
2266  } // function MEDIAN()
2267 
2268 
2283  public static function MIN() {
2284  // Return value
2285  $returnValue = null;
2286 
2287  // Loop through arguments
2288  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2289  foreach ($aArgs as $arg) {
2290  // Is it a numeric value?
2291  if ((is_numeric($arg)) && (!is_string($arg))) {
2292  if ((is_null($returnValue)) || ($arg < $returnValue)) {
2293  $returnValue = $arg;
2294  }
2295  }
2296  }
2297 
2298  // Return
2299  if(is_null($returnValue)) {
2300  return 0;
2301  }
2302  return $returnValue;
2303  } // function MIN()
2304 
2305 
2319  public static function MINA() {
2320  // Return value
2321  $returnValue = null;
2322 
2323  // Loop through arguments
2324  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2325  foreach ($aArgs as $arg) {
2326  // Is it a numeric value?
2327  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
2328  if (is_bool($arg)) {
2329  $arg = (integer) $arg;
2330  } elseif (is_string($arg)) {
2331  $arg = 0;
2332  }
2333  if ((is_null($returnValue)) || ($arg < $returnValue)) {
2334  $returnValue = $arg;
2335  }
2336  }
2337  }
2338 
2339  // Return
2340  if(is_null($returnValue)) {
2341  return 0;
2342  }
2343  return $returnValue;
2344  } // function MINA()
2345 
2346 
2361  public static function MINIF($aArgs,$condition,$sumArgs = array()) {
2362  // Return value
2363  $returnValue = null;
2364 
2365  $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
2366  $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
2367  if (count($sumArgs) == 0) {
2368  $sumArgs = $aArgs;
2369  }
2370  $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2371  // Loop through arguments
2372  foreach ($aArgs as $key => $arg) {
2373  if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
2374  $testCondition = '='.$arg.$condition;
2375  if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
2376  if ((is_null($returnValue)) || ($arg < $returnValue)) {
2377  $returnValue = $arg;
2378  }
2379  }
2380  }
2381 
2382  // Return
2383  return $returnValue;
2384  } // function MINIF()
2385 
2386 
2387  //
2388  // Special variant of array_count_values that isn't limited to strings and integers,
2389  // but can work with floating point numbers as values
2390  //
2391  private static function _modeCalc($data) {
2392  $frequencyArray = array();
2393  foreach($data as $datum) {
2394  $found = False;
2395  foreach($frequencyArray as $key => $value) {
2396  if ((string) $value['value'] == (string) $datum) {
2397  ++$frequencyArray[$key]['frequency'];
2398  $found = True;
2399  break;
2400  }
2401  }
2402  if (!$found) {
2403  $frequencyArray[] = array('value' => $datum,
2404  'frequency' => 1 );
2405  }
2406  }
2407 
2408  foreach($frequencyArray as $key => $value) {
2409  $frequencyList[$key] = $value['frequency'];
2410  $valueList[$key] = $value['value'];
2411  }
2412  array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
2413 
2414  if ($frequencyArray[0]['frequency'] == 1) {
2415  return PHPExcel_Calculation_Functions::NA();
2416  }
2417  return $frequencyArray[0]['value'];
2418  } // function _modeCalc()
2419 
2420 
2434  public static function MODE() {
2435  // Return value
2436  $returnValue = PHPExcel_Calculation_Functions::NA();
2437 
2438  // Loop through arguments
2439  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2440 
2441  $mArgs = array();
2442  foreach ($aArgs as $arg) {
2443  // Is it a numeric value?
2444  if ((is_numeric($arg)) && (!is_string($arg))) {
2445  $mArgs[] = $arg;
2446  }
2447  }
2448 
2449  if (count($mArgs) > 0) {
2450  return self::_modeCalc($mArgs);
2451  }
2452 
2453  // Return
2454  return $returnValue;
2455  } // function MODE()
2456 
2457 
2473  public static function NEGBINOMDIST($failures, $successes, $probability) {
2474  $failures = floor(PHPExcel_Calculation_Functions::flattenSingleValue($failures));
2475  $successes = floor(PHPExcel_Calculation_Functions::flattenSingleValue($successes));
2476  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2477 
2478  if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) {
2479  if (($failures < 0) || ($successes < 1)) {
2480  return PHPExcel_Calculation_Functions::NaN();
2481  }
2482  if (($probability < 0) || ($probability > 1)) {
2483  return PHPExcel_Calculation_Functions::NaN();
2484  }
2485  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
2486  if (($failures + $successes - 1) <= 0) {
2487  return PHPExcel_Calculation_Functions::NaN();
2488  }
2489  }
2490  return (PHPExcel_Calculation_MathTrig::COMBIN($failures + $successes - 1,$successes - 1)) * (pow($probability,$successes)) * (pow(1 - $probability,$failures)) ;
2491  }
2492  return PHPExcel_Calculation_Functions::VALUE();
2493  } // function NEGBINOMDIST()
2494 
2495 
2510  public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
2511  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2512  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2513  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2514 
2515  if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2516  if ($stdDev < 0) {
2517  return PHPExcel_Calculation_Functions::NaN();
2518  }
2519  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
2520  if ($cumulative) {
2521  return 0.5 * (1 + PHPExcel_Calculation_Engineering::_erfVal(($value - $mean) / ($stdDev * sqrt(2))));
2522  } else {
2523  return (1 / (SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean,2) / (2 * ($stdDev * $stdDev))));
2524  }
2525  }
2526  }
2527  return PHPExcel_Calculation_Functions::VALUE();
2528  } // function NORMDIST()
2529 
2530 
2542  public static function NORMINV($probability,$mean,$stdDev) {
2543  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2544  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2545  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2546 
2547  if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2548  if (($probability < 0) || ($probability > 1)) {
2549  return PHPExcel_Calculation_Functions::NaN();
2550  }
2551  if ($stdDev < 0) {
2552  return PHPExcel_Calculation_Functions::NaN();
2553  }
2554  return (self::_inverse_ncdf($probability) * $stdDev) + $mean;
2555  }
2556  return PHPExcel_Calculation_Functions::VALUE();
2557  } // function NORMINV()
2558 
2559 
2570  public static function NORMSDIST($value) {
2571  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2572 
2573  return self::NORMDIST($value, 0, 1, True);
2574  } // function NORMSDIST()
2575 
2576 
2585  public static function NORMSINV($value) {
2586  return self::NORMINV($value, 0, 1);
2587  } // function NORMSINV()
2588 
2589 
2604  public static function PERCENTILE() {
2605  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2606 
2607  // Calculate
2608  $entry = array_pop($aArgs);
2609 
2610  if ((is_numeric($entry)) && (!is_string($entry))) {
2611  if (($entry < 0) || ($entry > 1)) {
2612  return PHPExcel_Calculation_Functions::NaN();
2613  }
2614  $mArgs = array();
2615  foreach ($aArgs as $arg) {
2616  // Is it a numeric value?
2617  if ((is_numeric($arg)) && (!is_string($arg))) {
2618  $mArgs[] = $arg;
2619  }
2620  }
2621  $mValueCount = count($mArgs);
2622  if ($mValueCount > 0) {
2623  sort($mArgs);
2624  $count = self::COUNT($mArgs);
2625  $index = $entry * ($count-1);
2626  $iBase = floor($index);
2627  if ($index == $iBase) {
2628  return $mArgs[$index];
2629  } else {
2630  $iNext = $iBase + 1;
2631  $iProportion = $index - $iBase;
2632  return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
2633  }
2634  }
2635  }
2636  return PHPExcel_Calculation_Functions::VALUE();
2637  } // function PERCENTILE()
2638 
2639 
2650  public static function PERCENTRANK($valueSet,$value,$significance=3) {
2651  $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
2652  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2653  $significance = (is_null($significance)) ? 3 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($significance);
2654 
2655  foreach($valueSet as $key => $valueEntry) {
2656  if (!is_numeric($valueEntry)) {
2657  unset($valueSet[$key]);
2658  }
2659  }
2660  sort($valueSet,SORT_NUMERIC);
2661  $valueCount = count($valueSet);
2662  if ($valueCount == 0) {
2663  return PHPExcel_Calculation_Functions::NaN();
2664  }
2665 
2666  $valueAdjustor = $valueCount - 1;
2667  if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
2668  return PHPExcel_Calculation_Functions::NA();
2669  }
2670 
2671  $pos = array_search($value,$valueSet);
2672  if ($pos === False) {
2673  $pos = 0;
2674  $testValue = $valueSet[0];
2675  while ($testValue < $value) {
2676  $testValue = $valueSet[++$pos];
2677  }
2678  --$pos;
2679  $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
2680  }
2681 
2682  return round($pos / $valueAdjustor,$significance);
2683  } // function PERCENTRANK()
2684 
2685 
2699  public static function PERMUT($numObjs,$numInSet) {
2700  $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs);
2701  $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet);
2702 
2703  if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
2704  $numInSet = floor($numInSet);
2705  if ($numObjs < $numInSet) {
2706  return PHPExcel_Calculation_Functions::NaN();
2707  }
2708  return round(PHPExcel_Calculation_MathTrig::FACT($numObjs) / PHPExcel_Calculation_MathTrig::FACT($numObjs - $numInSet));
2709  }
2710  return PHPExcel_Calculation_Functions::VALUE();
2711  } // function PERMUT()
2712 
2713 
2727  public static function POISSON($value, $mean, $cumulative) {
2728  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2729  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2730 
2731  if ((is_numeric($value)) && (is_numeric($mean))) {
2732  if (($value <= 0) || ($mean <= 0)) {
2733  return PHPExcel_Calculation_Functions::NaN();
2734  }
2735  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
2736  if ($cumulative) {
2737  $summer = 0;
2738  for ($i = 0; $i <= floor($value); ++$i) {
2739  $summer += pow($mean,$i) / PHPExcel_Calculation_MathTrig::FACT($i);
2740  }
2741  return exp(0-$mean) * $summer;
2742  } else {
2743  return (exp(0-$mean) * pow($mean,$value)) / PHPExcel_Calculation_MathTrig::FACT($value);
2744  }
2745  }
2746  }
2747  return PHPExcel_Calculation_Functions::VALUE();
2748  } // function POISSON()
2749 
2750 
2765  public static function QUARTILE() {
2766  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2767 
2768  // Calculate
2769  $entry = floor(array_pop($aArgs));
2770 
2771  if ((is_numeric($entry)) && (!is_string($entry))) {
2772  $entry /= 4;
2773  if (($entry < 0) || ($entry > 1)) {
2774  return PHPExcel_Calculation_Functions::NaN();
2775  }
2776  return self::PERCENTILE($aArgs,$entry);
2777  }
2778  return PHPExcel_Calculation_Functions::VALUE();
2779  } // function QUARTILE()
2780 
2781 
2792  public static function RANK($value,$valueSet,$order=0) {
2793  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2794  $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
2795  $order = (is_null($order)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($order);
2796 
2797  foreach($valueSet as $key => $valueEntry) {
2798  if (!is_numeric($valueEntry)) {
2799  unset($valueSet[$key]);
2800  }
2801  }
2802 
2803  if ($order == 0) {
2804  rsort($valueSet,SORT_NUMERIC);
2805  } else {
2806  sort($valueSet,SORT_NUMERIC);
2807  }
2808  $pos = array_search($value,$valueSet);
2809  if ($pos === False) {
2810  return PHPExcel_Calculation_Functions::NA();
2811  }
2812 
2813  return ++$pos;
2814  } // function RANK()
2815 
2816 
2826  public static function RSQ($yValues,$xValues) {
2827  if (!self::_checkTrendArrays($yValues,$xValues)) {
2828  return PHPExcel_Calculation_Functions::VALUE();
2829  }
2830  $yValueCount = count($yValues);
2831  $xValueCount = count($xValues);
2832 
2833  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2834  return PHPExcel_Calculation_Functions::NA();
2835  } elseif ($yValueCount == 1) {
2836  return PHPExcel_Calculation_Functions::DIV0();
2837  }
2838 
2839  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2840  return $bestFitLinear->getGoodnessOfFit();
2841  } // function RSQ()
2842 
2843 
2855  public static function SKEW() {
2856  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
2857  $mean = self::AVERAGE($aArgs);
2858  $stdDev = self::STDEV($aArgs);
2859 
2860  $count = $summer = 0;
2861  // Loop through arguments
2862  foreach ($aArgs as $k => $arg) {
2863  if ((is_bool($arg)) &&
2864  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
2865  } else {
2866  // Is it a numeric value?
2867  if ((is_numeric($arg)) && (!is_string($arg))) {
2868  $summer += pow((($arg - $mean) / $stdDev),3) ;
2869  ++$count;
2870  }
2871  }
2872  }
2873 
2874  // Return
2875  if ($count > 2) {
2876  return $summer * ($count / (($count-1) * ($count-2)));
2877  }
2878  return PHPExcel_Calculation_Functions::DIV0();
2879  } // function SKEW()
2880 
2881 
2891  public static function SLOPE($yValues,$xValues) {
2892  if (!self::_checkTrendArrays($yValues,$xValues)) {
2893  return PHPExcel_Calculation_Functions::VALUE();
2894  }
2895  $yValueCount = count($yValues);
2896  $xValueCount = count($xValues);
2897 
2898  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2899  return PHPExcel_Calculation_Functions::NA();
2900  } elseif ($yValueCount == 1) {
2901  return PHPExcel_Calculation_Functions::DIV0();
2902  }
2903 
2904  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2905  return $bestFitLinear->getSlope();
2906  } // function SLOPE()
2907 
2908 
2924  public static function SMALL() {
2925  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2926 
2927  // Calculate
2928  $entry = array_pop($aArgs);
2929 
2930  if ((is_numeric($entry)) && (!is_string($entry))) {
2931  $mArgs = array();
2932  foreach ($aArgs as $arg) {
2933  // Is it a numeric value?
2934  if ((is_numeric($arg)) && (!is_string($arg))) {
2935  $mArgs[] = $arg;
2936  }
2937  }
2938  $count = self::COUNT($mArgs);
2939  $entry = floor(--$entry);
2940  if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
2941  return PHPExcel_Calculation_Functions::NaN();
2942  }
2943  sort($mArgs);
2944  return $mArgs[$entry];
2945  }
2946  return PHPExcel_Calculation_Functions::VALUE();
2947  } // function SMALL()
2948 
2949 
2960  public static function STANDARDIZE($value,$mean,$stdDev) {
2961  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2962  $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2963  $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2964 
2965  if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2966  if ($stdDev <= 0) {
2967  return PHPExcel_Calculation_Functions::NaN();
2968  }
2969  return ($value - $mean) / $stdDev ;
2970  }
2971  return PHPExcel_Calculation_Functions::VALUE();
2972  } // function STANDARDIZE()
2973 
2974 
2989  public static function STDEV() {
2990  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
2991 
2992  // Return value
2993  $returnValue = null;
2994 
2995  $aMean = self::AVERAGE($aArgs);
2996  if (!is_null($aMean)) {
2997  $aCount = -1;
2998  foreach ($aArgs as $k => $arg) {
2999  if ((is_bool($arg)) &&
3000  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
3001  $arg = (integer) $arg;
3002  }
3003  // Is it a numeric value?
3004  if ((is_numeric($arg)) && (!is_string($arg))) {
3005  if (is_null($returnValue)) {
3006  $returnValue = pow(($arg - $aMean),2);
3007  } else {
3008  $returnValue += pow(($arg - $aMean),2);
3009  }
3010  ++$aCount;
3011  }
3012  }
3013 
3014  // Return
3015  if (($aCount > 0) && ($returnValue >= 0)) {
3016  return sqrt($returnValue / $aCount);
3017  }
3018  }
3019  return PHPExcel_Calculation_Functions::DIV0();
3020  } // function STDEV()
3021 
3022 
3036  public static function STDEVA() {
3037  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3038 
3039  // Return value
3040  $returnValue = null;
3041 
3042  $aMean = self::AVERAGEA($aArgs);
3043  if (!is_null($aMean)) {
3044  $aCount = -1;
3045  foreach ($aArgs as $k => $arg) {
3046  if ((is_bool($arg)) &&
3047  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3048  } else {
3049  // Is it a numeric value?
3050  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3051  if (is_bool($arg)) {
3052  $arg = (integer) $arg;
3053  } elseif (is_string($arg)) {
3054  $arg = 0;
3055  }
3056  if (is_null($returnValue)) {
3057  $returnValue = pow(($arg - $aMean),2);
3058  } else {
3059  $returnValue += pow(($arg - $aMean),2);
3060  }
3061  ++$aCount;
3062  }
3063  }
3064  }
3065 
3066  // Return
3067  if (($aCount > 0) && ($returnValue >= 0)) {
3068  return sqrt($returnValue / $aCount);
3069  }
3070  }
3071  return PHPExcel_Calculation_Functions::DIV0();
3072  } // function STDEVA()
3073 
3074 
3088  public static function STDEVP() {
3089  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3090 
3091  // Return value
3092  $returnValue = null;
3093 
3094  $aMean = self::AVERAGE($aArgs);
3095  if (!is_null($aMean)) {
3096  $aCount = 0;
3097  foreach ($aArgs as $k => $arg) {
3098  if ((is_bool($arg)) &&
3099  ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
3100  $arg = (integer) $arg;
3101  }
3102  // Is it a numeric value?
3103  if ((is_numeric($arg)) && (!is_string($arg))) {
3104  if (is_null($returnValue)) {
3105  $returnValue = pow(($arg - $aMean),2);
3106  } else {
3107  $returnValue += pow(($arg - $aMean),2);
3108  }
3109  ++$aCount;
3110  }
3111  }
3112 
3113  // Return
3114  if (($aCount > 0) && ($returnValue >= 0)) {
3115  return sqrt($returnValue / $aCount);
3116  }
3117  }
3118  return PHPExcel_Calculation_Functions::DIV0();
3119  } // function STDEVP()
3120 
3121 
3135  public static function STDEVPA() {
3136  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3137 
3138  // Return value
3139  $returnValue = null;
3140 
3141  $aMean = self::AVERAGEA($aArgs);
3142  if (!is_null($aMean)) {
3143  $aCount = 0;
3144  foreach ($aArgs as $k => $arg) {
3145  if ((is_bool($arg)) &&
3146  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3147  } else {
3148  // Is it a numeric value?
3149  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3150  if (is_bool($arg)) {
3151  $arg = (integer) $arg;
3152  } elseif (is_string($arg)) {
3153  $arg = 0;
3154  }
3155  if (is_null($returnValue)) {
3156  $returnValue = pow(($arg - $aMean),2);
3157  } else {
3158  $returnValue += pow(($arg - $aMean),2);
3159  }
3160  ++$aCount;
3161  }
3162  }
3163  }
3164 
3165  // Return
3166  if (($aCount > 0) && ($returnValue >= 0)) {
3167  return sqrt($returnValue / $aCount);
3168  }
3169  }
3170  return PHPExcel_Calculation_Functions::DIV0();
3171  } // function STDEVPA()
3172 
3173 
3183  public static function STEYX($yValues,$xValues) {
3184  if (!self::_checkTrendArrays($yValues,$xValues)) {
3185  return PHPExcel_Calculation_Functions::VALUE();
3186  }
3187  $yValueCount = count($yValues);
3188  $xValueCount = count($xValues);
3189 
3190  if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
3191  return PHPExcel_Calculation_Functions::NA();
3192  } elseif ($yValueCount == 1) {
3193  return PHPExcel_Calculation_Functions::DIV0();
3194  }
3195 
3196  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
3197  return $bestFitLinear->getStdevOfResiduals();
3198  } // function STEYX()
3199 
3200 
3211  public static function TDIST($value, $degrees, $tails) {
3212  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
3213  $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
3214  $tails = floor(PHPExcel_Calculation_Functions::flattenSingleValue($tails));
3215 
3216  if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
3217  if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
3218  return PHPExcel_Calculation_Functions::NaN();
3219  }
3220  // tdist, which finds the probability that corresponds to a given value
3221  // of t with k degrees of freedom. This algorithm is translated from a
3222  // pascal function on p81 of "Statistical Computing in Pascal" by D
3223  // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
3224  // London). The above Pascal algorithm is itself a translation of the
3225  // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
3226  // Laboratory as reported in (among other places) "Applied Statistics
3227  // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
3228  // Horwood Ltd.; W. Sussex, England).
3229  $tterm = $degrees;
3230  $ttheta = atan2($value,sqrt($tterm));
3231  $tc = cos($ttheta);
3232  $ts = sin($ttheta);
3233  $tsum = 0;
3234 
3235  if (($degrees % 2) == 1) {
3236  $ti = 3;
3237  $tterm = $tc;
3238  } else {
3239  $ti = 2;
3240  $tterm = 1;
3241  }
3242 
3243  $tsum = $tterm;
3244  while ($ti < $degrees) {
3245  $tterm *= $tc * $tc * ($ti - 1) / $ti;
3246  $tsum += $tterm;
3247  $ti += 2;
3248  }
3249  $tsum *= $ts;
3250  if (($degrees % 2) == 1) { $tsum = M_2DIVPI * ($tsum + $ttheta); }
3251  $tValue = 0.5 * (1 + $tsum);
3252  if ($tails == 1) {
3253  return 1 - abs($tValue);
3254  } else {
3255  return 1 - abs((1 - $tValue) - $tValue);
3256  }
3257  }
3258  return PHPExcel_Calculation_Functions::VALUE();
3259  } // function TDIST()
3260 
3261 
3271  public static function TINV($probability, $degrees) {
3272  $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
3273  $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
3274 
3275  if ((is_numeric($probability)) && (is_numeric($degrees))) {
3276  $xLo = 100;
3277  $xHi = 0;
3278 
3279  $x = $xNew = 1;
3280  $dx = 1;
3281  $i = 0;
3282 
3283  while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
3284  // Apply Newton-Raphson step
3285  $result = self::TDIST($x, $degrees, 2);
3286  $error = $result - $probability;
3287  if ($error == 0.0) {
3288  $dx = 0;
3289  } elseif ($error < 0.0) {
3290  $xLo = $x;
3291  } else {
3292  $xHi = $x;
3293  }
3294  // Avoid division by zero
3295  if ($result != 0.0) {
3296  $dx = $error / $result;
3297  $xNew = $x - $dx;
3298  }
3299  // If the NR fails to converge (which for example may be the
3300  // case if the initial guess is too rough) we apply a bisection
3301  // step to determine a more narrow interval around the root.
3302  if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
3303  $xNew = ($xLo + $xHi) / 2;
3304  $dx = $xNew - $x;
3305  }
3306  $x = $xNew;
3307  }
3308  if ($i == MAX_ITERATIONS) {
3309  return PHPExcel_Calculation_Functions::NA();
3310  }
3311  return round($x,12);
3312  }
3313  return PHPExcel_Calculation_Functions::VALUE();
3314  } // function TINV()
3315 
3316 
3328  public static function TREND($yValues,$xValues=array(),$newValues=array(),$const=True) {
3329  $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
3330  $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
3331  $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
3332  $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
3333 
3334  $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
3335  if (count($newValues) == 0) {
3336  $newValues = $bestFitLinear->getXValues();
3337  }
3338 
3339  $returnArray = array();
3340  foreach($newValues as $xValue) {
3341  $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue);
3342  }
3343 
3344  return $returnArray;
3345  } // function TREND()
3346 
3347 
3364  public static function TRIMMEAN() {
3365  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3366 
3367  // Calculate
3368  $percent = array_pop($aArgs);
3369 
3370  if ((is_numeric($percent)) && (!is_string($percent))) {
3371  if (($percent < 0) || ($percent > 1)) {
3372  return PHPExcel_Calculation_Functions::NaN();
3373  }
3374  $mArgs = array();
3375  foreach ($aArgs as $arg) {
3376  // Is it a numeric value?
3377  if ((is_numeric($arg)) && (!is_string($arg))) {
3378  $mArgs[] = $arg;
3379  }
3380  }
3381  $discard = floor(self::COUNT($mArgs) * $percent / 2);
3382  sort($mArgs);
3383  for ($i=0; $i < $discard; ++$i) {
3384  array_pop($mArgs);
3385  array_shift($mArgs);
3386  }
3387  return self::AVERAGE($mArgs);
3388  }
3389  return PHPExcel_Calculation_Functions::VALUE();
3390  } // function TRIMMEAN()
3391 
3392 
3406  public static function VARFunc() {
3407  // Return value
3408  $returnValue = PHPExcel_Calculation_Functions::DIV0();
3409 
3410  $summerA = $summerB = 0;
3411 
3412  // Loop through arguments
3413  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3414  $aCount = 0;
3415  foreach ($aArgs as $arg) {
3416  if (is_bool($arg)) { $arg = (integer) $arg; }
3417  // Is it a numeric value?
3418  if ((is_numeric($arg)) && (!is_string($arg))) {
3419  $summerA += ($arg * $arg);
3420  $summerB += $arg;
3421  ++$aCount;
3422  }
3423  }
3424 
3425  // Return
3426  if ($aCount > 1) {
3427  $summerA *= $aCount;
3428  $summerB *= $summerB;
3429  $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3430  }
3431  return $returnValue;
3432  } // function VARFunc()
3433 
3434 
3448  public static function VARA() {
3449  // Return value
3450  $returnValue = PHPExcel_Calculation_Functions::DIV0();
3451 
3452  $summerA = $summerB = 0;
3453 
3454  // Loop through arguments
3455  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3456  $aCount = 0;
3457  foreach ($aArgs as $k => $arg) {
3458  if ((is_string($arg)) &&
3459  (PHPExcel_Calculation_Functions::isValue($k))) {
3460  return PHPExcel_Calculation_Functions::VALUE();
3461  } elseif ((is_string($arg)) &&
3462  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3463  } else {
3464  // Is it a numeric value?
3465  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3466  if (is_bool($arg)) {
3467  $arg = (integer) $arg;
3468  } elseif (is_string($arg)) {
3469  $arg = 0;
3470  }
3471  $summerA += ($arg * $arg);
3472  $summerB += $arg;
3473  ++$aCount;
3474  }
3475  }
3476  }
3477 
3478  // Return
3479  if ($aCount > 1) {
3480  $summerA *= $aCount;
3481  $summerB *= $summerB;
3482  $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3483  }
3484  return $returnValue;
3485  } // function VARA()
3486 
3487 
3501  public static function VARP() {
3502  // Return value
3503  $returnValue = PHPExcel_Calculation_Functions::DIV0();
3504 
3505  $summerA = $summerB = 0;
3506 
3507  // Loop through arguments
3508  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3509  $aCount = 0;
3510  foreach ($aArgs as $arg) {
3511  if (is_bool($arg)) { $arg = (integer) $arg; }
3512  // Is it a numeric value?
3513  if ((is_numeric($arg)) && (!is_string($arg))) {
3514  $summerA += ($arg * $arg);
3515  $summerB += $arg;
3516  ++$aCount;
3517  }
3518  }
3519 
3520  // Return
3521  if ($aCount > 0) {
3522  $summerA *= $aCount;
3523  $summerB *= $summerB;
3524  $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3525  }
3526  return $returnValue;
3527  } // function VARP()
3528 
3529 
3543  public static function VARPA() {
3544  // Return value
3545  $returnValue = PHPExcel_Calculation_Functions::DIV0();
3546 
3547  $summerA = $summerB = 0;
3548 
3549  // Loop through arguments
3550  $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3551  $aCount = 0;
3552  foreach ($aArgs as $k => $arg) {
3553  if ((is_string($arg)) &&
3554  (PHPExcel_Calculation_Functions::isValue($k))) {
3555  return PHPExcel_Calculation_Functions::VALUE();
3556  } elseif ((is_string($arg)) &&
3557  (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3558  } else {
3559  // Is it a numeric value?
3560  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3561  if (is_bool($arg)) {
3562  $arg = (integer) $arg;
3563  } elseif (is_string($arg)) {
3564  $arg = 0;
3565  }
3566  $summerA += ($arg * $arg);
3567  $summerB += $arg;
3568  ++$aCount;
3569  }
3570  }
3571  }
3572 
3573  // Return
3574  if ($aCount > 0) {
3575  $summerA *= $aCount;
3576  $summerB *= $summerB;
3577  $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3578  }
3579  return $returnValue;
3580  } // function VARPA()
3581 
3582 
3596  public static function WEIBULL($value, $alpha, $beta, $cumulative) {
3597  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
3598  $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
3599  $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
3600 
3601  if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
3602  if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
3603  return PHPExcel_Calculation_Functions::NaN();
3604  }
3605  if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
3606  if ($cumulative) {
3607  return 1 - exp(0 - pow($value / $beta,$alpha));
3608  } else {
3609  return ($alpha / pow($beta,$alpha)) * pow($value,$alpha - 1) * exp(0 - pow($value / $beta,$alpha));
3610  }
3611  }
3612  }
3613  return PHPExcel_Calculation_Functions::VALUE();
3614  } // function WEIBULL()
3615 
3616 
3630  public static function ZTEST($dataSet, $m0, $sigma=null) {
3631  $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet);
3632  $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0);
3633  $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma);
3634 
3635  if (is_null($sigma)) {
3636  $sigma = self::STDEV($dataSet);
3637  }
3638  $n = count($dataSet);
3639 
3640  return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0)/($sigma/SQRT($n)));
3641  } // function ZTEST()
3642 
3643 } // class PHPExcel_Calculation_Statistical