ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
Statistical.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
38
39require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
40
41
43define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
44
46define('XMININ', 2.23e-308);
47
49define('EPS', 2.22e-16);
50
52define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
53
54
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
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
150 private static function _logBeta($p, $q) {
151 if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
152 self::$_logBetaCache_p = $p;
153 self::$_logBetaCache_q = $q;
154 if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
155 self::$_logBetaCache_result = 0.0;
156 } else {
157 self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
158 }
159 }
161 } // function _logBeta()
162
163
169 private static function _betaFraction($x, $p, $q) {
170 $c = 1.0;
171 $sum_pq = $p + $q;
172 $p_plus = $p + 1.0;
173 $p_minus = $p - 1.0;
174 $h = 1.0 - $sum_pq * $x / $p_plus;
175 if (abs($h) < XMININ) {
176 $h = XMININ;
177 }
178 $h = 1.0 / $h;
179 $frac = $h;
180 $m = 1;
181 $delta = 0.0;
182 while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
183 $m2 = 2 * $m;
184 // even index for d
185 $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
186 $h = 1.0 + $d * $h;
187 if (abs($h) < XMININ) {
188 $h = XMININ;
189 }
190 $h = 1.0 / $h;
191 $c = 1.0 + $d / $c;
192 if (abs($c) < XMININ) {
193 $c = XMININ;
194 }
195 $frac *= $h * $c;
196 // odd index for d
197 $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
198 $h = 1.0 + $d * $h;
199 if (abs($h) < XMININ) {
200 $h = XMININ;
201 }
202 $h = 1.0 / $h;
203 $c = 1.0 + $d / $c;
204 if (abs($c) < XMININ) {
205 $c = XMININ;
206 }
207 $delta = $h * $c;
208 $frac *= $delta;
209 ++$m;
210 }
211 return $frac;
212 } // function _betaFraction()
213
214
258 // Function cache for logGamma
259 private static $_logGammaCache_result = 0.0;
260 private static $_logGammaCache_x = 0.0;
261
262 private static function _logGamma($x) {
263 // Log Gamma related constants
264 static $lg_d1 = -0.5772156649015328605195174;
265 static $lg_d2 = 0.4227843350984671393993777;
266 static $lg_d4 = 1.791759469228055000094023;
267
268 static $lg_p1 = array( 4.945235359296727046734888,
269 201.8112620856775083915565,
270 2290.838373831346393026739,
271 11319.67205903380828685045,
272 28557.24635671635335736389,
273 38484.96228443793359990269,
274 26377.48787624195437963534,
275 7225.813979700288197698961 );
276 static $lg_p2 = array( 4.974607845568932035012064,
277 542.4138599891070494101986,
278 15506.93864978364947665077,
279 184793.2904445632425417223,
280 1088204.76946882876749847,
281 3338152.967987029735917223,
282 5106661.678927352456275255,
283 3074109.054850539556250927 );
284 static $lg_p4 = array( 14745.02166059939948905062,
285 2426813.369486704502836312,
286 121475557.4045093227939592,
287 2663432449.630976949898078,
288 29403789566.34553899906876,
289 170266573776.5398868392998,
290 492612579337.743088758812,
291 560625185622.3951465078242 );
292
293 static $lg_q1 = array( 67.48212550303777196073036,
294 1113.332393857199323513008,
295 7738.757056935398733233834,
296 27639.87074403340708898585,
297 54993.10206226157329794414,
298 61611.22180066002127833352,
299 36351.27591501940507276287,
300 8785.536302431013170870835 );
301 static $lg_q2 = array( 183.0328399370592604055942,
302 7765.049321445005871323047,
303 133190.3827966074194402448,
304 1136705.821321969608938755,
305 5267964.117437946917577538,
306 13467014.54311101692290052,
307 17827365.30353274213975932,
308 9533095.591844353613395747 );
309 static $lg_q4 = array( 2690.530175870899333379843,
310 639388.5654300092398984238,
311 41355999.30241388052042842,
312 1120872109.61614794137657,
313 14886137286.78813811542398,
314 101680358627.2438228077304,
315 341747634550.7377132798597,
316 446315818741.9713286462081 );
317
318 static $lg_c = array( -0.001910444077728,
319 8.4171387781295e-4,
320 -5.952379913043012e-4,
321 7.93650793500350248e-4,
322 -0.002777777777777681622553,
323 0.08333333333333333331554247,
324 0.0057083835261 );
325
326 // Rough estimate of the fourth root of logGamma_xBig
327 static $lg_frtbig = 2.25e76;
328 static $pnt68 = 0.6796875;
329
330
331 if ($x == self::$_logGammaCache_x) {
333 }
334 $y = $x;
335 if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
336 if ($y <= EPS) {
337 $res = -log(y);
338 } elseif ($y <= 1.5) {
339 // ---------------------
340 // EPS .LT. X .LE. 1.5
341 // ---------------------
342 if ($y < $pnt68) {
343 $corr = -log($y);
344 $xm1 = $y;
345 } else {
346 $corr = 0.0;
347 $xm1 = $y - 1.0;
348 }
349 if ($y <= 0.5 || $y >= $pnt68) {
350 $xden = 1.0;
351 $xnum = 0.0;
352 for ($i = 0; $i < 8; ++$i) {
353 $xnum = $xnum * $xm1 + $lg_p1[$i];
354 $xden = $xden * $xm1 + $lg_q1[$i];
355 }
356 $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
357 } else {
358 $xm2 = $y - 1.0;
359 $xden = 1.0;
360 $xnum = 0.0;
361 for ($i = 0; $i < 8; ++$i) {
362 $xnum = $xnum * $xm2 + $lg_p2[$i];
363 $xden = $xden * $xm2 + $lg_q2[$i];
364 }
365 $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
366 }
367 } elseif ($y <= 4.0) {
368 // ---------------------
369 // 1.5 .LT. X .LE. 4.0
370 // ---------------------
371 $xm2 = $y - 2.0;
372 $xden = 1.0;
373 $xnum = 0.0;
374 for ($i = 0; $i < 8; ++$i) {
375 $xnum = $xnum * $xm2 + $lg_p2[$i];
376 $xden = $xden * $xm2 + $lg_q2[$i];
377 }
378 $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
379 } elseif ($y <= 12.0) {
380 // ----------------------
381 // 4.0 .LT. X .LE. 12.0
382 // ----------------------
383 $xm4 = $y - 4.0;
384 $xden = -1.0;
385 $xnum = 0.0;
386 for ($i = 0; $i < 8; ++$i) {
387 $xnum = $xnum * $xm4 + $lg_p4[$i];
388 $xden = $xden * $xm4 + $lg_q4[$i];
389 }
390 $res = $lg_d4 + $xm4 * ($xnum / $xden);
391 } else {
392 // ---------------------------------
393 // Evaluate for argument .GE. 12.0
394 // ---------------------------------
395 $res = 0.0;
396 if ($y <= $lg_frtbig) {
397 $res = $lg_c[6];
398 $ysq = $y * $y;
399 for ($i = 0; $i < 6; ++$i)
400 $res = $res / $ysq + $lg_c[$i];
401 }
402 $res /= $y;
403 $corr = log($y);
404 $res = $res + log(SQRT2PI) - 0.5 * $corr;
405 $res += $y * ($corr - 1.0);
406 }
407 } else {
408 // --------------------------
409 // Return for bad arguments
410 // --------------------------
411 $res = MAX_VALUE;
412 }
413 // ------------------------------
414 // Final adjustments and return
415 // ------------------------------
416 self::$_logGammaCache_x = $x;
417 self::$_logGammaCache_result = $res;
418 return $res;
419 } // function _logGamma()
420
421
422 //
423 // Private implementation of the incomplete Gamma function
424 //
425 private static function _incompleteGamma($a,$x) {
426 static $max = 32;
427 $summer = 0;
428 for ($n=0; $n<=$max; ++$n) {
429 $divisor = $a;
430 for ($i=1; $i<=$n; ++$i) {
431 $divisor *= ($a + $i);
432 }
433 $summer += (pow($x,$n) / $divisor);
434 }
435 return pow($x,$a) * exp(0-$x) * $summer;
436 } // function _incompleteGamma()
437
438
439 //
440 // Private implementation of the Gamma function
441 //
442 private static function _gamma($data) {
443 if ($data == 0.0) return 0;
444
445 static $p0 = 1.000000000190015;
446 static $p = array ( 1 => 76.18009172947146,
447 2 => -86.50532032941677,
448 3 => 24.01409824083091,
449 4 => -1.231739572450155,
450 5 => 1.208650973866179e-3,
451 6 => -5.395239384953e-6
452 );
453
454 $y = $x = $data;
455 $tmp = $x + 5.5;
456 $tmp -= ($x + 0.5) * log($tmp);
457
458 $summer = $p0;
459 for ($j=1;$j<=6;++$j) {
460 $summer += ($p[$j] / ++$y);
461 }
462 return exp(0 - $tmp + log(SQRT2PI * $summer / $x));
463 } // function _gamma()
464
465
466 /***************************************************************************
467 * inverse_ncdf.php
468 * -------------------
469 * begin : Friday, January 16, 2004
470 * copyright : (C) 2004 Michael Nickerson
471 * email : nickersonm@yahoo.com
472 *
473 ***************************************************************************/
474 private static function _inverse_ncdf($p) {
475 // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
476 // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
477 // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
478 // I have not checked the accuracy of this implementation. Be aware that PHP
479 // will truncate the coeficcients to 14 digits.
480
481 // You have permission to use and distribute this function freely for
482 // whatever purpose you want, but please show common courtesy and give credit
483 // where credit is due.
484
485 // Input paramater is $p - probability - where 0 < p < 1.
486
487 // Coefficients in rational approximations
488 static $a = array( 1 => -3.969683028665376e+01,
489 2 => 2.209460984245205e+02,
490 3 => -2.759285104469687e+02,
491 4 => 1.383577518672690e+02,
492 5 => -3.066479806614716e+01,
493 6 => 2.506628277459239e+00
494 );
495
496 static $b = array( 1 => -5.447609879822406e+01,
497 2 => 1.615858368580409e+02,
498 3 => -1.556989798598866e+02,
499 4 => 6.680131188771972e+01,
500 5 => -1.328068155288572e+01
501 );
502
503 static $c = array( 1 => -7.784894002430293e-03,
504 2 => -3.223964580411365e-01,
505 3 => -2.400758277161838e+00,
506 4 => -2.549732539343734e+00,
507 5 => 4.374664141464968e+00,
508 6 => 2.938163982698783e+00
509 );
510
511 static $d = array( 1 => 7.784695709041462e-03,
512 2 => 3.224671290700398e-01,
513 3 => 2.445134137142996e+00,
514 4 => 3.754408661907416e+00
515 );
516
517 // Define lower and upper region break-points.
518 $p_low = 0.02425; //Use lower region approx. below this
519 $p_high = 1 - $p_low; //Use upper region approx. above this
520
521 if (0 < $p && $p < $p_low) {
522 // Rational approximation for lower region.
523 $q = sqrt(-2 * log($p));
524 return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
525 (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
526 } elseif ($p_low <= $p && $p <= $p_high) {
527 // Rational approximation for central region.
528 $q = $p - 0.5;
529 $r = $q * $q;
530 return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
531 ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
532 } elseif ($p_high < $p && $p < 1) {
533 // Rational approximation for upper region.
534 $q = sqrt(-2 * log(1 - $p));
535 return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
536 (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
537 }
538 // If 0 < p < 1, return a null value
540 } // function _inverse_ncdf()
541
542
543 private static function _inverse_ncdf2($prob) {
544 // Approximation of inverse standard normal CDF developed by
545 // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
546
547 $a1 = 2.50662823884;
548 $a2 = -18.61500062529;
549 $a3 = 41.39119773534;
550 $a4 = -25.44106049637;
551
552 $b1 = -8.4735109309;
553 $b2 = 23.08336743743;
554 $b3 = -21.06224101826;
555 $b4 = 3.13082909833;
556
557 $c1 = 0.337475482272615;
558 $c2 = 0.976169019091719;
559 $c3 = 0.160797971491821;
560 $c4 = 2.76438810333863E-02;
561 $c5 = 3.8405729373609E-03;
562 $c6 = 3.951896511919E-04;
563 $c7 = 3.21767881768E-05;
564 $c8 = 2.888167364E-07;
565 $c9 = 3.960315187E-07;
566
567 $y = $prob - 0.5;
568 if (abs($y) < 0.42) {
569 $z = ($y * $y);
570 $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
571 } else {
572 if ($y > 0) {
573 $z = log(-log(1 - $prob));
574 } else {
575 $z = log(-log($prob));
576 }
577 $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
578 if ($y < 0) {
579 $z = -$z;
580 }
581 }
582 return $z;
583 } // function _inverse_ncdf2()
584
585
586 private static function _inverse_ncdf3($p) {
587 // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
588 // Produces the normal deviate Z corresponding to a given lower
589 // tail area of P; Z is accurate to about 1 part in 10**16.
590 //
591 // This is a PHP version of the original FORTRAN code that can
592 // be found at http://lib.stat.cmu.edu/apstat/
593 $split1 = 0.425;
594 $split2 = 5;
595 $const1 = 0.180625;
596 $const2 = 1.6;
597
598 // coefficients for p close to 0.5
599 $a0 = 3.3871328727963666080;
600 $a1 = 1.3314166789178437745E+2;
601 $a2 = 1.9715909503065514427E+3;
602 $a3 = 1.3731693765509461125E+4;
603 $a4 = 4.5921953931549871457E+4;
604 $a5 = 6.7265770927008700853E+4;
605 $a6 = 3.3430575583588128105E+4;
606 $a7 = 2.5090809287301226727E+3;
607
608 $b1 = 4.2313330701600911252E+1;
609 $b2 = 6.8718700749205790830E+2;
610 $b3 = 5.3941960214247511077E+3;
611 $b4 = 2.1213794301586595867E+4;
612 $b5 = 3.9307895800092710610E+4;
613 $b6 = 2.8729085735721942674E+4;
614 $b7 = 5.2264952788528545610E+3;
615
616 // coefficients for p not close to 0, 0.5 or 1.
617 $c0 = 1.42343711074968357734;
618 $c1 = 4.63033784615654529590;
619 $c2 = 5.76949722146069140550;
620 $c3 = 3.64784832476320460504;
621 $c4 = 1.27045825245236838258;
622 $c5 = 2.41780725177450611770E-1;
623 $c6 = 2.27238449892691845833E-2;
624 $c7 = 7.74545014278341407640E-4;
625
626 $d1 = 2.05319162663775882187;
627 $d2 = 1.67638483018380384940;
628 $d3 = 6.89767334985100004550E-1;
629 $d4 = 1.48103976427480074590E-1;
630 $d5 = 1.51986665636164571966E-2;
631 $d6 = 5.47593808499534494600E-4;
632 $d7 = 1.05075007164441684324E-9;
633
634 // coefficients for p near 0 or 1.
635 $e0 = 6.65790464350110377720;
636 $e1 = 5.46378491116411436990;
637 $e2 = 1.78482653991729133580;
638 $e3 = 2.96560571828504891230E-1;
639 $e4 = 2.65321895265761230930E-2;
640 $e5 = 1.24266094738807843860E-3;
641 $e6 = 2.71155556874348757815E-5;
642 $e7 = 2.01033439929228813265E-7;
643
644 $f1 = 5.99832206555887937690E-1;
645 $f2 = 1.36929880922735805310E-1;
646 $f3 = 1.48753612908506148525E-2;
647 $f4 = 7.86869131145613259100E-4;
648 $f5 = 1.84631831751005468180E-5;
649 $f6 = 1.42151175831644588870E-7;
650 $f7 = 2.04426310338993978564E-15;
651
652 $q = $p - 0.5;
653
654 // computation for p close to 0.5
655 if (abs($q) <= split1) {
656 $R = $const1 - $q * $q;
657 $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
658 ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
659 } else {
660 if ($q < 0) {
661 $R = $p;
662 } else {
663 $R = 1 - $p;
664 }
665 $R = pow(-log($R),2);
666
667 // computation for p not close to 0, 0.5 or 1.
668 If ($R <= $split2) {
669 $R = $R - $const2;
670 $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
671 ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
672 } else {
673 // computation for p near 0 or 1.
674 $R = $R - $split2;
675 $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
676 ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
677 }
678 if ($q < 0) {
679 $z = -$z;
680 }
681 }
682 return $z;
683 } // function _inverse_ncdf3()
684
685
700 public static function AVEDEV() {
702
703 // Return value
704 $returnValue = null;
705
706 $aMean = self::AVERAGE($aArgs);
707 if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
708 $aCount = 0;
709 foreach ($aArgs as $k => $arg) {
710 if ((is_bool($arg)) &&
712 $arg = (integer) $arg;
713 }
714 // Is it a numeric value?
715 if ((is_numeric($arg)) && (!is_string($arg))) {
716 if (is_null($returnValue)) {
717 $returnValue = abs($arg - $aMean);
718 } else {
719 $returnValue += abs($arg - $aMean);
720 }
721 ++$aCount;
722 }
723 }
724
725 // Return
726 if ($aCount == 0) {
728 }
729 return $returnValue / $aCount;
730 }
732 } // function AVEDEV()
733
734
748 public static function AVERAGE() {
749 $returnValue = $aCount = 0;
750
751 // Loop through arguments
752 foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
753 if ((is_bool($arg)) &&
755 $arg = (integer) $arg;
756 }
757 // Is it a numeric value?
758 if ((is_numeric($arg)) && (!is_string($arg))) {
759 if (is_null($returnValue)) {
760 $returnValue = $arg;
761 } else {
762 $returnValue += $arg;
763 }
764 ++$aCount;
765 }
766 }
767
768 // Return
769 if ($aCount > 0) {
770 return $returnValue / $aCount;
771 } else {
773 }
774 } // function AVERAGE()
775
776
790 public static function AVERAGEA() {
791 // Return value
792 $returnValue = null;
793
794 $aCount = 0;
795 // Loop through arguments
796 foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
797 if ((is_bool($arg)) &&
799 } else {
800 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
801 if (is_bool($arg)) {
802 $arg = (integer) $arg;
803 } elseif (is_string($arg)) {
804 $arg = 0;
805 }
806 if (is_null($returnValue)) {
807 $returnValue = $arg;
808 } else {
809 $returnValue += $arg;
810 }
811 ++$aCount;
812 }
813 }
814 }
815
816 // Return
817 if ($aCount > 0) {
818 return $returnValue / $aCount;
819 } else {
821 }
822 } // function AVERAGEA()
823
824
840 public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
841 // Return value
842 $returnValue = 0;
843
845 $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
846 if (empty($averageArgs)) {
847 $averageArgs = $aArgs;
848 }
849 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
850 // Loop through arguments
851 $aCount = 0;
852 foreach ($aArgs as $key => $arg) {
853 if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
854 $testCondition = '='.$arg.$condition;
855 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
856 if ((is_null($returnValue)) || ($arg > $returnValue)) {
857 $returnValue += $arg;
858 ++$aCount;
859 }
860 }
861 }
862
863 // Return
864 if ($aCount > 0) {
865 return $returnValue / $aCount;
866 } else {
868 }
869 } // function AVERAGEIF()
870
871
884 public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
890
891 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
892 if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
894 }
895 if ($rMin > $rMax) {
896 $tmp = $rMin;
897 $rMin = $rMax;
898 $rMax = $tmp;
899 }
900 $value -= $rMin;
901 $value /= ($rMax - $rMin);
902 return self::_incompleteBeta($value,$alpha,$beta);
903 }
905 } // function BETADIST()
906
907
922 public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
923 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
928
929 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
930 if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
932 }
933 if ($rMin > $rMax) {
934 $tmp = $rMin;
935 $rMin = $rMax;
936 $rMax = $tmp;
937 }
938 $a = 0;
939 $b = 2;
940
941 $i = 0;
942 while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
943 $guess = ($a + $b) / 2;
944 $result = self::BETADIST($guess, $alpha, $beta);
945 if (($result == $probability) || ($result == 0)) {
946 $b = $a;
947 } elseif ($result > $probability) {
948 $b = $guess;
949 } else {
950 $a = $guess;
951 }
952 }
953 if ($i == MAX_ITERATIONS) {
955 }
956 return round($rMin + $guess * ($rMax - $rMin),12);
957 }
959 } // function BETAINV()
960
961
980 public static function BINOMDIST($value, $trials, $probability, $cumulative) {
983 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
984
985 if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
986 if (($value < 0) || ($value > $trials)) {
988 }
989 if (($probability < 0) || ($probability > 1)) {
991 }
992 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
993 if ($cumulative) {
994 $summer = 0;
995 for ($i = 0; $i <= $value; ++$i) {
996 $summer += PHPExcel_Calculation_MathTrig::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
997 }
998 return $summer;
999 } else {
1000 return PHPExcel_Calculation_MathTrig::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
1001 }
1002 }
1003 }
1005 } // function BINOMDIST()
1006
1007
1017 public static function CHIDIST($value, $degrees) {
1019 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1020
1021 if ((is_numeric($value)) && (is_numeric($degrees))) {
1022 if ($degrees < 1) {
1024 }
1025 if ($value < 0) {
1027 return 1;
1028 }
1030 }
1031 return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
1032 }
1034 } // function CHIDIST()
1035
1036
1046 public static function CHIINV($probability, $degrees) {
1047 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1048 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1049
1050 if ((is_numeric($probability)) && (is_numeric($degrees))) {
1051
1052 $xLo = 100;
1053 $xHi = 0;
1054
1055 $x = $xNew = 1;
1056 $dx = 1;
1057 $i = 0;
1058
1059 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1060 // Apply Newton-Raphson step
1061 $result = self::CHIDIST($x, $degrees);
1062 $error = $result - $probability;
1063 if ($error == 0.0) {
1064 $dx = 0;
1065 } elseif ($error < 0.0) {
1066 $xLo = $x;
1067 } else {
1068 $xHi = $x;
1069 }
1070 // Avoid division by zero
1071 if ($result != 0.0) {
1072 $dx = $error / $result;
1073 $xNew = $x - $dx;
1074 }
1075 // If the NR fails to converge (which for example may be the
1076 // case if the initial guess is too rough) we apply a bisection
1077 // step to determine a more narrow interval around the root.
1078 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
1079 $xNew = ($xLo + $xHi) / 2;
1080 $dx = $xNew - $x;
1081 }
1082 $x = $xNew;
1083 }
1084 if ($i == MAX_ITERATIONS) {
1086 }
1087 return round($x,12);
1088 }
1090 } // function CHIINV()
1091
1092
1104 public static function CONFIDENCE($alpha,$stdDev,$size) {
1108
1109 if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
1110 if (($alpha <= 0) || ($alpha >= 1)) {
1112 }
1113 if (($stdDev <= 0) || ($size < 1)) {
1115 }
1116 return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
1117 }
1119 } // function CONFIDENCE()
1120
1121
1131 public static function CORREL($yValues,$xValues=null) {
1132 if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
1134 }
1135 if (!self::_checkTrendArrays($yValues,$xValues)) {
1137 }
1138 $yValueCount = count($yValues);
1139 $xValueCount = count($xValues);
1140
1141 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1143 } elseif ($yValueCount == 1) {
1145 }
1146
1147 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1148 return $bestFitLinear->getCorrelation();
1149 } // function CORREL()
1150
1151
1165 public static function COUNT() {
1166 // Return value
1167 $returnValue = 0;
1168
1169 // Loop through arguments
1171 foreach ($aArgs as $k => $arg) {
1172 if ((is_bool($arg)) &&
1174 $arg = (integer) $arg;
1175 }
1176 // Is it a numeric value?
1177 if ((is_numeric($arg)) && (!is_string($arg))) {
1178 ++$returnValue;
1179 }
1180 }
1181
1182 // Return
1183 return $returnValue;
1184 } // function COUNT()
1185
1186
1200 public static function COUNTA() {
1201 // Return value
1202 $returnValue = 0;
1203
1204 // Loop through arguments
1205 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1206 foreach ($aArgs as $arg) {
1207 // Is it a numeric, boolean or string value?
1208 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1209 ++$returnValue;
1210 }
1211 }
1212
1213 // Return
1214 return $returnValue;
1215 } // function COUNTA()
1216
1217
1231 public static function COUNTBLANK() {
1232 // Return value
1233 $returnValue = 0;
1234
1235 // Loop through arguments
1236 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1237 foreach ($aArgs as $arg) {
1238 // Is it a blank cell?
1239 if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
1240 ++$returnValue;
1241 }
1242 }
1243
1244 // Return
1245 return $returnValue;
1246 } // function COUNTBLANK()
1247
1248
1263 public static function COUNTIF($aArgs,$condition) {
1264 // Return value
1265 $returnValue = 0;
1266
1268 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1269 // Loop through arguments
1270 foreach ($aArgs as $arg) {
1271 if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1272 $testCondition = '='.$arg.$condition;
1273 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1274 // Is it a value within our criteria
1275 ++$returnValue;
1276 }
1277 }
1278
1279 // Return
1280 return $returnValue;
1281 } // function COUNTIF()
1282
1283
1293 public static function COVAR($yValues,$xValues) {
1294 if (!self::_checkTrendArrays($yValues,$xValues)) {
1296 }
1297 $yValueCount = count($yValues);
1298 $xValueCount = count($xValues);
1299
1300 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1302 } elseif ($yValueCount == 1) {
1304 }
1305
1306 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1307 return $bestFitLinear->getCovariance();
1308 } // function COVAR()
1309
1310
1330 public static function CRITBINOM($trials, $probability, $alpha) {
1332 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1334
1335 if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
1336 if ($trials < 0) {
1338 }
1339 if (($probability < 0) || ($probability > 1)) {
1341 }
1342 if (($alpha < 0) || ($alpha > 1)) {
1344 }
1345 if ($alpha <= 0.5) {
1346 $t = sqrt(log(1 / ($alpha * $alpha)));
1347 $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));
1348 } else {
1349 $t = sqrt(log(1 / pow(1 - $alpha,2)));
1350 $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
1351 }
1352 $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
1353 if ($Guess < 0) {
1354 $Guess = 0;
1355 } elseif ($Guess > $trials) {
1356 $Guess = $trials;
1357 }
1358
1359 $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
1360 $EssentiallyZero = 10e-12;
1361
1362 $m = floor($trials * $probability);
1363 ++$TotalUnscaledProbability;
1364 if ($m == $Guess) { ++$UnscaledPGuess; }
1365 if ($m <= $Guess) { ++$UnscaledCumPGuess; }
1366
1367 $PreviousValue = 1;
1368 $Done = False;
1369 $k = $m + 1;
1370 while ((!$Done) && ($k <= $trials)) {
1371 $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
1372 $TotalUnscaledProbability += $CurrentValue;
1373 if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1374 if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1375 if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1376 $PreviousValue = $CurrentValue;
1377 ++$k;
1378 }
1379
1380 $PreviousValue = 1;
1381 $Done = False;
1382 $k = $m - 1;
1383 while ((!$Done) && ($k >= 0)) {
1384 $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
1385 $TotalUnscaledProbability += $CurrentValue;
1386 if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1387 if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1388 if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1389 $PreviousValue = $CurrentValue;
1390 --$k;
1391 }
1392
1393 $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
1394 $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
1395
1396// $CumPGuessMinus1 = $CumPGuess - $PGuess;
1397 $CumPGuessMinus1 = $CumPGuess - 1;
1398
1399 while (True) {
1400 if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
1401 return $Guess;
1402 } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
1403 $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
1404 $CumPGuessMinus1 = $CumPGuess;
1405 $CumPGuess = $CumPGuess + $PGuessPlus1;
1406 $PGuess = $PGuessPlus1;
1407 ++$Guess;
1408 } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
1409 $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
1410 $CumPGuess = $CumPGuessMinus1;
1411 $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
1412 $PGuess = $PGuessMinus1;
1413 --$Guess;
1414 }
1415 }
1416 }
1418 } // function CRITBINOM()
1419
1420
1434 public static function DEVSQ() {
1436
1437 // Return value
1438 $returnValue = null;
1439
1440 $aMean = self::AVERAGE($aArgs);
1441 if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
1442 $aCount = -1;
1443 foreach ($aArgs as $k => $arg) {
1444 // Is it a numeric value?
1445 if ((is_bool($arg)) &&
1447 $arg = (integer) $arg;
1448 }
1449 if ((is_numeric($arg)) && (!is_string($arg))) {
1450 if (is_null($returnValue)) {
1451 $returnValue = pow(($arg - $aMean),2);
1452 } else {
1453 $returnValue += pow(($arg - $aMean),2);
1454 }
1455 ++$aCount;
1456 }
1457 }
1458
1459 // Return
1460 if (is_null($returnValue)) {
1462 } else {
1463 return $returnValue;
1464 }
1465 }
1466 return self::NA();
1467 } // function DEVSQ()
1468
1469
1482 public static function EXPONDIST($value, $lambda, $cumulative) {
1485 $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
1486
1487 if ((is_numeric($value)) && (is_numeric($lambda))) {
1488 if (($value < 0) || ($lambda < 0)) {
1490 }
1491 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1492 if ($cumulative) {
1493 return 1 - exp(0-$value*$lambda);
1494 } else {
1495 return $lambda * exp(0-$value*$lambda);
1496 }
1497 }
1498 }
1500 } // function EXPONDIST()
1501
1502
1513 public static function FISHER($value) {
1515
1516 if (is_numeric($value)) {
1517 if (($value <= -1) || ($value >= 1)) {
1519 }
1520 return 0.5 * log((1+$value)/(1-$value));
1521 }
1523 } // function FISHER()
1524
1525
1536 public static function FISHERINV($value) {
1538
1539 if (is_numeric($value)) {
1540 return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
1541 }
1543 } // function FISHERINV()
1544
1545
1556 public static function FORECAST($xValue,$yValues,$xValues) {
1558 if (!is_numeric($xValue)) {
1560 }
1561
1562 if (!self::_checkTrendArrays($yValues,$xValues)) {
1564 }
1565 $yValueCount = count($yValues);
1566 $xValueCount = count($xValues);
1567
1568 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1570 } elseif ($yValueCount == 1) {
1572 }
1573
1574 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1575 return $bestFitLinear->getValueOfYForX($xValue);
1576 } // function FORECAST()
1577
1578
1591 public static function GAMMADIST($value,$a,$b,$cumulative) {
1595
1596 if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
1597 if (($value < 0) || ($a <= 0) || ($b <= 0)) {
1599 }
1600 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1601 if ($cumulative) {
1602 return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
1603 } else {
1604 return (1 / (pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
1605 }
1606 }
1607 }
1609 } // function GAMMADIST()
1610
1611
1623 public static function GAMMAINV($probability,$alpha,$beta) {
1624 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1627
1628 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
1629 if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
1631 }
1632
1633 $xLo = 0;
1634 $xHi = $alpha * $beta * 5;
1635
1636 $x = $xNew = 1;
1637 $error = $pdf = 0;
1638 $dx = 1024;
1639 $i = 0;
1640
1641 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1642 // Apply Newton-Raphson step
1643 $error = self::GAMMADIST($x, $alpha, $beta, True) - $probability;
1644 if ($error < 0.0) {
1645 $xLo = $x;
1646 } else {
1647 $xHi = $x;
1648 }
1649 $pdf = self::GAMMADIST($x, $alpha, $beta, False);
1650 // Avoid division by zero
1651 if ($pdf != 0.0) {
1652 $dx = $error / $pdf;
1653 $xNew = $x - $dx;
1654 }
1655 // If the NR fails to converge (which for example may be the
1656 // case if the initial guess is too rough) we apply a bisection
1657 // step to determine a more narrow interval around the root.
1658 if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) {
1659 $xNew = ($xLo + $xHi) / 2;
1660 $dx = $xNew - $x;
1661 }
1662 $x = $xNew;
1663 }
1664 if ($i == MAX_ITERATIONS) {
1666 }
1667 return $x;
1668 }
1670 } // function GAMMAINV()
1671
1672
1681 public static function GAMMALN($value) {
1683
1684 if (is_numeric($value)) {
1685 if ($value <= 0) {
1687 }
1688 return log(self::_gamma($value));
1689 }
1691 } // function GAMMALN()
1692
1693
1709 public static function GEOMEAN() {
1710 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1711
1713 if (is_numeric($aMean) && ($aMean > 0)) {
1714 $aCount = self::COUNT($aArgs) ;
1715 if (self::MIN($aArgs) > 0) {
1716 return pow($aMean, (1 / $aCount));
1717 }
1718 }
1720 } // GEOMEAN()
1721
1722
1734 public static function GROWTH($yValues,$xValues=array(),$newValues=array(),$const=True) {
1737 $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
1738 $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
1739
1740 $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
1741 if (empty($newValues)) {
1742 $newValues = $bestFitExponential->getXValues();
1743 }
1744
1745 $returnArray = array();
1746 foreach($newValues as $xValue) {
1747 $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue);
1748 }
1749
1750 return $returnArray;
1751 } // function GROWTH()
1752
1753
1768 public static function HARMEAN() {
1769 // Return value
1770 $returnValue = PHPExcel_Calculation_Functions::NA();
1771
1772 // Loop through arguments
1773 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1774 if (self::MIN($aArgs) < 0) {
1776 }
1777 $aCount = 0;
1778 foreach ($aArgs as $arg) {
1779 // Is it a numeric value?
1780 if ((is_numeric($arg)) && (!is_string($arg))) {
1781 if ($arg <= 0) {
1783 }
1784 if (is_null($returnValue)) {
1785 $returnValue = (1 / $arg);
1786 } else {
1787 $returnValue += (1 / $arg);
1788 }
1789 ++$aCount;
1790 }
1791 }
1792
1793 // Return
1794 if ($aCount > 0) {
1795 return 1 / ($returnValue / $aCount);
1796 } else {
1797 return $returnValue;
1798 }
1799 } // function HARMEAN()
1800
1801
1815 public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
1816 $sampleSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleSuccesses));
1817 $sampleNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleNumber));
1818 $populationSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationSuccesses));
1819 $populationNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationNumber));
1820
1821 if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) {
1822 if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
1824 }
1825 if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
1827 }
1828 if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
1830 }
1831 return PHPExcel_Calculation_MathTrig::COMBIN($populationSuccesses,$sampleSuccesses) *
1832 PHPExcel_Calculation_MathTrig::COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
1833 PHPExcel_Calculation_MathTrig::COMBIN($populationNumber,$sampleNumber);
1834 }
1836 } // function HYPGEOMDIST()
1837
1838
1848 public static function INTERCEPT($yValues,$xValues) {
1849 if (!self::_checkTrendArrays($yValues,$xValues)) {
1851 }
1852 $yValueCount = count($yValues);
1853 $xValueCount = count($xValues);
1854
1855 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1857 } elseif ($yValueCount == 1) {
1859 }
1860
1861 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1862 return $bestFitLinear->getIntersect();
1863 } // function INTERCEPT()
1864
1865
1877 public static function KURT() {
1879 $mean = self::AVERAGE($aArgs);
1880 $stdDev = self::STDEV($aArgs);
1881
1882 if ($stdDev > 0) {
1883 $count = $summer = 0;
1884 // Loop through arguments
1885 foreach ($aArgs as $k => $arg) {
1886 if ((is_bool($arg)) &&
1888 } else {
1889 // Is it a numeric value?
1890 if ((is_numeric($arg)) && (!is_string($arg))) {
1891 $summer += pow((($arg - $mean) / $stdDev),4) ;
1892 ++$count;
1893 }
1894 }
1895 }
1896
1897 // Return
1898 if ($count > 3) {
1899 return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1,2) / (($count-2) * ($count-3)));
1900 }
1901 }
1903 } // function KURT()
1904
1905
1922 public static function LARGE() {
1923 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1924
1925 // Calculate
1926 $entry = floor(array_pop($aArgs));
1927
1928 if ((is_numeric($entry)) && (!is_string($entry))) {
1929 $mArgs = array();
1930 foreach ($aArgs as $arg) {
1931 // Is it a numeric value?
1932 if ((is_numeric($arg)) && (!is_string($arg))) {
1933 $mArgs[] = $arg;
1934 }
1935 }
1936 $count = self::COUNT($mArgs);
1937 $entry = floor(--$entry);
1938 if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
1940 }
1941 rsort($mArgs);
1942 return $mArgs[$entry];
1943 }
1945 } // function LARGE()
1946
1947
1960 public static function LINEST($yValues, $xValues = NULL, $const = TRUE, $stats = FALSE) {
1961 $const = (is_null($const)) ? TRUE : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
1963 if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
1964
1965 if (!self::_checkTrendArrays($yValues,$xValues)) {
1967 }
1968 $yValueCount = count($yValues);
1969 $xValueCount = count($xValues);
1970
1971
1972 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1974 } elseif ($yValueCount == 1) {
1975 return 0;
1976 }
1977
1978 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
1979 if ($stats) {
1980 return array( array( $bestFitLinear->getSlope(),
1981 $bestFitLinear->getSlopeSE(),
1982 $bestFitLinear->getGoodnessOfFit(),
1983 $bestFitLinear->getF(),
1984 $bestFitLinear->getSSRegression(),
1985 ),
1986 array( $bestFitLinear->getIntersect(),
1987 $bestFitLinear->getIntersectSE(),
1988 $bestFitLinear->getStdevOfResiduals(),
1989 $bestFitLinear->getDFResiduals(),
1990 $bestFitLinear->getSSResiduals()
1991 )
1992 );
1993 } else {
1994 return array( $bestFitLinear->getSlope(),
1995 $bestFitLinear->getIntersect()
1996 );
1997 }
1998 } // function LINEST()
1999
2000
2013 public static function LOGEST($yValues,$xValues=null,$const=True,$stats=False) {
2014 $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
2016 if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
2017
2018 if (!self::_checkTrendArrays($yValues,$xValues)) {
2020 }
2021 $yValueCount = count($yValues);
2022 $xValueCount = count($xValues);
2023
2024 foreach($yValues as $value) {
2025 if ($value <= 0.0) {
2027 }
2028 }
2029
2030
2031 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2033 } elseif ($yValueCount == 1) {
2034 return 1;
2035 }
2036
2037 $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
2038 if ($stats) {
2039 return array( array( $bestFitExponential->getSlope(),
2040 $bestFitExponential->getSlopeSE(),
2041 $bestFitExponential->getGoodnessOfFit(),
2042 $bestFitExponential->getF(),
2043 $bestFitExponential->getSSRegression(),
2044 ),
2045 array( $bestFitExponential->getIntersect(),
2046 $bestFitExponential->getIntersectSE(),
2047 $bestFitExponential->getStdevOfResiduals(),
2048 $bestFitExponential->getDFResiduals(),
2049 $bestFitExponential->getSSResiduals()
2050 )
2051 );
2052 } else {
2053 return array( $bestFitExponential->getSlope(),
2054 $bestFitExponential->getIntersect()
2055 );
2056 }
2057 } // function LOGEST()
2058
2059
2074 public static function LOGINV($probability, $mean, $stdDev) {
2075 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2078
2079 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2080 if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
2082 }
2083 return exp($mean + $stdDev * self::NORMSINV($probability));
2084 }
2086 } // function LOGINV()
2087
2088
2100 public static function LOGNORMDIST($value, $mean, $stdDev) {
2104
2105 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2106 if (($value <= 0) || ($stdDev <= 0)) {
2108 }
2109 return self::NORMSDIST((log($value) - $mean) / $stdDev);
2110 }
2112 } // function LOGNORMDIST()
2113
2114
2129 public static function MAX() {
2130 // Return value
2131 $returnValue = null;
2132
2133 // Loop through arguments
2134 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2135 foreach ($aArgs as $arg) {
2136 // Is it a numeric value?
2137 if ((is_numeric($arg)) && (!is_string($arg))) {
2138 if ((is_null($returnValue)) || ($arg > $returnValue)) {
2139 $returnValue = $arg;
2140 }
2141 }
2142 }
2143
2144 // Return
2145 if(is_null($returnValue)) {
2146 return 0;
2147 }
2148 return $returnValue;
2149 } // function MAX()
2150
2151
2165 public static function MAXA() {
2166 // Return value
2167 $returnValue = null;
2168
2169 // Loop through arguments
2170 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2171 foreach ($aArgs as $arg) {
2172 // Is it a numeric value?
2173 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
2174 if (is_bool($arg)) {
2175 $arg = (integer) $arg;
2176 } elseif (is_string($arg)) {
2177 $arg = 0;
2178 }
2179 if ((is_null($returnValue)) || ($arg > $returnValue)) {
2180 $returnValue = $arg;
2181 }
2182 }
2183 }
2184
2185 // Return
2186 if(is_null($returnValue)) {
2187 return 0;
2188 }
2189 return $returnValue;
2190 } // function MAXA()
2191
2192
2207 public static function MAXIF($aArgs,$condition,$sumArgs = array()) {
2208 // Return value
2209 $returnValue = null;
2210
2213 if (empty($sumArgs)) {
2214 $sumArgs = $aArgs;
2215 }
2216 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2217 // Loop through arguments
2218 foreach ($aArgs as $key => $arg) {
2219 if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
2220 $testCondition = '='.$arg.$condition;
2221 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
2222 if ((is_null($returnValue)) || ($arg > $returnValue)) {
2223 $returnValue = $arg;
2224 }
2225 }
2226 }
2227
2228 // Return
2229 return $returnValue;
2230 } // function MAXIF()
2231
2232
2246 public static function MEDIAN() {
2247 // Return value
2248 $returnValue = PHPExcel_Calculation_Functions::NaN();
2249
2250 $mArgs = array();
2251 // Loop through arguments
2252 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2253 foreach ($aArgs as $arg) {
2254 // Is it a numeric value?
2255 if ((is_numeric($arg)) && (!is_string($arg))) {
2256 $mArgs[] = $arg;
2257 }
2258 }
2259
2260 $mValueCount = count($mArgs);
2261 if ($mValueCount > 0) {
2262 sort($mArgs,SORT_NUMERIC);
2263 $mValueCount = $mValueCount / 2;
2264 if ($mValueCount == floor($mValueCount)) {
2265 $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
2266 } else {
2267 $mValueCount == floor($mValueCount);
2268 $returnValue = $mArgs[$mValueCount];
2269 }
2270 }
2271
2272 // Return
2273 return $returnValue;
2274 } // function MEDIAN()
2275
2276
2291 public static function MIN() {
2292 // Return value
2293 $returnValue = null;
2294
2295 // Loop through arguments
2296 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2297 foreach ($aArgs as $arg) {
2298 // Is it a numeric value?
2299 if ((is_numeric($arg)) && (!is_string($arg))) {
2300 if ((is_null($returnValue)) || ($arg < $returnValue)) {
2301 $returnValue = $arg;
2302 }
2303 }
2304 }
2305
2306 // Return
2307 if(is_null($returnValue)) {
2308 return 0;
2309 }
2310 return $returnValue;
2311 } // function MIN()
2312
2313
2327 public static function MINA() {
2328 // Return value
2329 $returnValue = null;
2330
2331 // Loop through arguments
2332 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2333 foreach ($aArgs as $arg) {
2334 // Is it a numeric value?
2335 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
2336 if (is_bool($arg)) {
2337 $arg = (integer) $arg;
2338 } elseif (is_string($arg)) {
2339 $arg = 0;
2340 }
2341 if ((is_null($returnValue)) || ($arg < $returnValue)) {
2342 $returnValue = $arg;
2343 }
2344 }
2345 }
2346
2347 // Return
2348 if(is_null($returnValue)) {
2349 return 0;
2350 }
2351 return $returnValue;
2352 } // function MINA()
2353
2354
2369 public static function MINIF($aArgs,$condition,$sumArgs = array()) {
2370 // Return value
2371 $returnValue = null;
2372
2375 if (empty($sumArgs)) {
2376 $sumArgs = $aArgs;
2377 }
2378 $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2379 // Loop through arguments
2380 foreach ($aArgs as $key => $arg) {
2381 if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
2382 $testCondition = '='.$arg.$condition;
2383 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
2384 if ((is_null($returnValue)) || ($arg < $returnValue)) {
2385 $returnValue = $arg;
2386 }
2387 }
2388 }
2389
2390 // Return
2391 return $returnValue;
2392 } // function MINIF()
2393
2394
2395 //
2396 // Special variant of array_count_values that isn't limited to strings and integers,
2397 // but can work with floating point numbers as values
2398 //
2399 private static function _modeCalc($data) {
2400 $frequencyArray = array();
2401 foreach($data as $datum) {
2402 $found = False;
2403 foreach($frequencyArray as $key => $value) {
2404 if ((string) $value['value'] == (string) $datum) {
2405 ++$frequencyArray[$key]['frequency'];
2406 $found = True;
2407 break;
2408 }
2409 }
2410 if (!$found) {
2411 $frequencyArray[] = array('value' => $datum,
2412 'frequency' => 1 );
2413 }
2414 }
2415
2416 foreach($frequencyArray as $key => $value) {
2417 $frequencyList[$key] = $value['frequency'];
2418 $valueList[$key] = $value['value'];
2419 }
2420 array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
2421
2422 if ($frequencyArray[0]['frequency'] == 1) {
2424 }
2425 return $frequencyArray[0]['value'];
2426 } // function _modeCalc()
2427
2428
2442 public static function MODE() {
2443 // Return value
2444 $returnValue = PHPExcel_Calculation_Functions::NA();
2445
2446 // Loop through arguments
2447 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2448
2449 $mArgs = array();
2450 foreach ($aArgs as $arg) {
2451 // Is it a numeric value?
2452 if ((is_numeric($arg)) && (!is_string($arg))) {
2453 $mArgs[] = $arg;
2454 }
2455 }
2456
2457 if (!empty($mArgs)) {
2458 return self::_modeCalc($mArgs);
2459 }
2460
2461 // Return
2462 return $returnValue;
2463 } // function MODE()
2464
2465
2481 public static function NEGBINOMDIST($failures, $successes, $probability) {
2482 $failures = floor(PHPExcel_Calculation_Functions::flattenSingleValue($failures));
2483 $successes = floor(PHPExcel_Calculation_Functions::flattenSingleValue($successes));
2484 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2485
2486 if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) {
2487 if (($failures < 0) || ($successes < 1)) {
2489 }
2490 if (($probability < 0) || ($probability > 1)) {
2492 }
2494 if (($failures + $successes - 1) <= 0) {
2496 }
2497 }
2498 return (PHPExcel_Calculation_MathTrig::COMBIN($failures + $successes - 1,$successes - 1)) * (pow($probability,$successes)) * (pow(1 - $probability,$failures)) ;
2499 }
2501 } // function NEGBINOMDIST()
2502
2503
2518 public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
2522
2523 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2524 if ($stdDev < 0) {
2526 }
2527 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
2528 if ($cumulative) {
2529 return 0.5 * (1 + PHPExcel_Calculation_Engineering::_erfVal(($value - $mean) / ($stdDev * sqrt(2))));
2530 } else {
2531 return (1 / (SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean,2) / (2 * ($stdDev * $stdDev))));
2532 }
2533 }
2534 }
2536 } // function NORMDIST()
2537
2538
2550 public static function NORMINV($probability,$mean,$stdDev) {
2551 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2554
2555 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2556 if (($probability < 0) || ($probability > 1)) {
2558 }
2559 if ($stdDev < 0) {
2561 }
2562 return (self::_inverse_ncdf($probability) * $stdDev) + $mean;
2563 }
2565 } // function NORMINV()
2566
2567
2578 public static function NORMSDIST($value) {
2580
2581 return self::NORMDIST($value, 0, 1, True);
2582 } // function NORMSDIST()
2583
2584
2593 public static function NORMSINV($value) {
2594 return self::NORMINV($value, 0, 1);
2595 } // function NORMSINV()
2596
2597
2612 public static function PERCENTILE() {
2613 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2614
2615 // Calculate
2616 $entry = array_pop($aArgs);
2617
2618 if ((is_numeric($entry)) && (!is_string($entry))) {
2619 if (($entry < 0) || ($entry > 1)) {
2621 }
2622 $mArgs = array();
2623 foreach ($aArgs as $arg) {
2624 // Is it a numeric value?
2625 if ((is_numeric($arg)) && (!is_string($arg))) {
2626 $mArgs[] = $arg;
2627 }
2628 }
2629 $mValueCount = count($mArgs);
2630 if ($mValueCount > 0) {
2631 sort($mArgs);
2632 $count = self::COUNT($mArgs);
2633 $index = $entry * ($count-1);
2634 $iBase = floor($index);
2635 if ($index == $iBase) {
2636 return $mArgs[$index];
2637 } else {
2638 $iNext = $iBase + 1;
2639 $iProportion = $index - $iBase;
2640 return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
2641 }
2642 }
2643 }
2645 } // function PERCENTILE()
2646
2647
2658 public static function PERCENTRANK($valueSet,$value,$significance=3) {
2659 $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
2661 $significance = (is_null($significance)) ? 3 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($significance);
2662
2663 foreach($valueSet as $key => $valueEntry) {
2664 if (!is_numeric($valueEntry)) {
2665 unset($valueSet[$key]);
2666 }
2667 }
2668 sort($valueSet,SORT_NUMERIC);
2669 $valueCount = count($valueSet);
2670 if ($valueCount == 0) {
2672 }
2673
2674 $valueAdjustor = $valueCount - 1;
2675 if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
2677 }
2678
2679 $pos = array_search($value,$valueSet);
2680 if ($pos === False) {
2681 $pos = 0;
2682 $testValue = $valueSet[0];
2683 while ($testValue < $value) {
2684 $testValue = $valueSet[++$pos];
2685 }
2686 --$pos;
2687 $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
2688 }
2689
2690 return round($pos / $valueAdjustor,$significance);
2691 } // function PERCENTRANK()
2692
2693
2707 public static function PERMUT($numObjs,$numInSet) {
2710
2711 if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
2712 $numInSet = floor($numInSet);
2713 if ($numObjs < $numInSet) {
2715 }
2716 return round(PHPExcel_Calculation_MathTrig::FACT($numObjs) / PHPExcel_Calculation_MathTrig::FACT($numObjs - $numInSet));
2717 }
2719 } // function PERMUT()
2720
2721
2735 public static function POISSON($value, $mean, $cumulative) {
2738
2739 if ((is_numeric($value)) && (is_numeric($mean))) {
2740 if (($value < 0) || ($mean <= 0)) {
2742 }
2743 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
2744 if ($cumulative) {
2745 $summer = 0;
2746 for ($i = 0; $i <= floor($value); ++$i) {
2747 $summer += pow($mean,$i) / PHPExcel_Calculation_MathTrig::FACT($i);
2748 }
2749 return exp(0-$mean) * $summer;
2750 } else {
2751 return (exp(0-$mean) * pow($mean,$value)) / PHPExcel_Calculation_MathTrig::FACT($value);
2752 }
2753 }
2754 }
2756 } // function POISSON()
2757
2758
2773 public static function QUARTILE() {
2774 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2775
2776 // Calculate
2777 $entry = floor(array_pop($aArgs));
2778
2779 if ((is_numeric($entry)) && (!is_string($entry))) {
2780 $entry /= 4;
2781 if (($entry < 0) || ($entry > 1)) {
2783 }
2784 return self::PERCENTILE($aArgs,$entry);
2785 }
2787 } // function QUARTILE()
2788
2789
2800 public static function RANK($value,$valueSet,$order=0) {
2802 $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
2803 $order = (is_null($order)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($order);
2804
2805 foreach($valueSet as $key => $valueEntry) {
2806 if (!is_numeric($valueEntry)) {
2807 unset($valueSet[$key]);
2808 }
2809 }
2810
2811 if ($order == 0) {
2812 rsort($valueSet,SORT_NUMERIC);
2813 } else {
2814 sort($valueSet,SORT_NUMERIC);
2815 }
2816 $pos = array_search($value,$valueSet);
2817 if ($pos === False) {
2819 }
2820
2821 return ++$pos;
2822 } // function RANK()
2823
2824
2834 public static function RSQ($yValues,$xValues) {
2835 if (!self::_checkTrendArrays($yValues,$xValues)) {
2837 }
2838 $yValueCount = count($yValues);
2839 $xValueCount = count($xValues);
2840
2841 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2843 } elseif ($yValueCount == 1) {
2845 }
2846
2847 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2848 return $bestFitLinear->getGoodnessOfFit();
2849 } // function RSQ()
2850
2851
2863 public static function SKEW() {
2865 $mean = self::AVERAGE($aArgs);
2866 $stdDev = self::STDEV($aArgs);
2867
2868 $count = $summer = 0;
2869 // Loop through arguments
2870 foreach ($aArgs as $k => $arg) {
2871 if ((is_bool($arg)) &&
2873 } else {
2874 // Is it a numeric value?
2875 if ((is_numeric($arg)) && (!is_string($arg))) {
2876 $summer += pow((($arg - $mean) / $stdDev),3) ;
2877 ++$count;
2878 }
2879 }
2880 }
2881
2882 // Return
2883 if ($count > 2) {
2884 return $summer * ($count / (($count-1) * ($count-2)));
2885 }
2887 } // function SKEW()
2888
2889
2899 public static function SLOPE($yValues,$xValues) {
2900 if (!self::_checkTrendArrays($yValues,$xValues)) {
2902 }
2903 $yValueCount = count($yValues);
2904 $xValueCount = count($xValues);
2905
2906 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2908 } elseif ($yValueCount == 1) {
2910 }
2911
2912 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2913 return $bestFitLinear->getSlope();
2914 } // function SLOPE()
2915
2916
2932 public static function SMALL() {
2933 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2934
2935 // Calculate
2936 $entry = array_pop($aArgs);
2937
2938 if ((is_numeric($entry)) && (!is_string($entry))) {
2939 $mArgs = array();
2940 foreach ($aArgs as $arg) {
2941 // Is it a numeric value?
2942 if ((is_numeric($arg)) && (!is_string($arg))) {
2943 $mArgs[] = $arg;
2944 }
2945 }
2946 $count = self::COUNT($mArgs);
2947 $entry = floor(--$entry);
2948 if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
2950 }
2951 sort($mArgs);
2952 return $mArgs[$entry];
2953 }
2955 } // function SMALL()
2956
2957
2968 public static function STANDARDIZE($value,$mean,$stdDev) {
2972
2973 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2974 if ($stdDev <= 0) {
2976 }
2977 return ($value - $mean) / $stdDev ;
2978 }
2980 } // function STANDARDIZE()
2981
2982
2997 public static function STDEV() {
2999
3000 // Return value
3001 $returnValue = null;
3002
3003 $aMean = self::AVERAGE($aArgs);
3004 if (!is_null($aMean)) {
3005 $aCount = -1;
3006 foreach ($aArgs as $k => $arg) {
3007 if ((is_bool($arg)) &&
3009 $arg = (integer) $arg;
3010 }
3011 // Is it a numeric value?
3012 if ((is_numeric($arg)) && (!is_string($arg))) {
3013 if (is_null($returnValue)) {
3014 $returnValue = pow(($arg - $aMean),2);
3015 } else {
3016 $returnValue += pow(($arg - $aMean),2);
3017 }
3018 ++$aCount;
3019 }
3020 }
3021
3022 // Return
3023 if (($aCount > 0) && ($returnValue >= 0)) {
3024 return sqrt($returnValue / $aCount);
3025 }
3026 }
3028 } // function STDEV()
3029
3030
3044 public static function STDEVA() {
3046
3047 // Return value
3048 $returnValue = null;
3049
3050 $aMean = self::AVERAGEA($aArgs);
3051 if (!is_null($aMean)) {
3052 $aCount = -1;
3053 foreach ($aArgs as $k => $arg) {
3054 if ((is_bool($arg)) &&
3056 } else {
3057 // Is it a numeric value?
3058 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3059 if (is_bool($arg)) {
3060 $arg = (integer) $arg;
3061 } elseif (is_string($arg)) {
3062 $arg = 0;
3063 }
3064 if (is_null($returnValue)) {
3065 $returnValue = pow(($arg - $aMean),2);
3066 } else {
3067 $returnValue += pow(($arg - $aMean),2);
3068 }
3069 ++$aCount;
3070 }
3071 }
3072 }
3073
3074 // Return
3075 if (($aCount > 0) && ($returnValue >= 0)) {
3076 return sqrt($returnValue / $aCount);
3077 }
3078 }
3080 } // function STDEVA()
3081
3082
3096 public static function STDEVP() {
3098
3099 // Return value
3100 $returnValue = null;
3101
3102 $aMean = self::AVERAGE($aArgs);
3103 if (!is_null($aMean)) {
3104 $aCount = 0;
3105 foreach ($aArgs as $k => $arg) {
3106 if ((is_bool($arg)) &&
3108 $arg = (integer) $arg;
3109 }
3110 // Is it a numeric value?
3111 if ((is_numeric($arg)) && (!is_string($arg))) {
3112 if (is_null($returnValue)) {
3113 $returnValue = pow(($arg - $aMean),2);
3114 } else {
3115 $returnValue += pow(($arg - $aMean),2);
3116 }
3117 ++$aCount;
3118 }
3119 }
3120
3121 // Return
3122 if (($aCount > 0) && ($returnValue >= 0)) {
3123 return sqrt($returnValue / $aCount);
3124 }
3125 }
3127 } // function STDEVP()
3128
3129
3143 public static function STDEVPA() {
3145
3146 // Return value
3147 $returnValue = null;
3148
3149 $aMean = self::AVERAGEA($aArgs);
3150 if (!is_null($aMean)) {
3151 $aCount = 0;
3152 foreach ($aArgs as $k => $arg) {
3153 if ((is_bool($arg)) &&
3155 } else {
3156 // Is it a numeric value?
3157 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3158 if (is_bool($arg)) {
3159 $arg = (integer) $arg;
3160 } elseif (is_string($arg)) {
3161 $arg = 0;
3162 }
3163 if (is_null($returnValue)) {
3164 $returnValue = pow(($arg - $aMean),2);
3165 } else {
3166 $returnValue += pow(($arg - $aMean),2);
3167 }
3168 ++$aCount;
3169 }
3170 }
3171 }
3172
3173 // Return
3174 if (($aCount > 0) && ($returnValue >= 0)) {
3175 return sqrt($returnValue / $aCount);
3176 }
3177 }
3179 } // function STDEVPA()
3180
3181
3191 public static function STEYX($yValues,$xValues) {
3192 if (!self::_checkTrendArrays($yValues,$xValues)) {
3194 }
3195 $yValueCount = count($yValues);
3196 $xValueCount = count($xValues);
3197
3198 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
3200 } elseif ($yValueCount == 1) {
3202 }
3203
3204 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
3205 return $bestFitLinear->getStdevOfResiduals();
3206 } // function STEYX()
3207
3208
3219 public static function TDIST($value, $degrees, $tails) {
3221 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
3223
3224 if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
3225 if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
3227 }
3228 // tdist, which finds the probability that corresponds to a given value
3229 // of t with k degrees of freedom. This algorithm is translated from a
3230 // pascal function on p81 of "Statistical Computing in Pascal" by D
3231 // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
3232 // London). The above Pascal algorithm is itself a translation of the
3233 // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
3234 // Laboratory as reported in (among other places) "Applied Statistics
3235 // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
3236 // Horwood Ltd.; W. Sussex, England).
3237 $tterm = $degrees;
3238 $ttheta = atan2($value,sqrt($tterm));
3239 $tc = cos($ttheta);
3240 $ts = sin($ttheta);
3241 $tsum = 0;
3242
3243 if (($degrees % 2) == 1) {
3244 $ti = 3;
3245 $tterm = $tc;
3246 } else {
3247 $ti = 2;
3248 $tterm = 1;
3249 }
3250
3251 $tsum = $tterm;
3252 while ($ti < $degrees) {
3253 $tterm *= $tc * $tc * ($ti - 1) / $ti;
3254 $tsum += $tterm;
3255 $ti += 2;
3256 }
3257 $tsum *= $ts;
3258 if (($degrees % 2) == 1) { $tsum = M_2DIVPI * ($tsum + $ttheta); }
3259 $tValue = 0.5 * (1 + $tsum);
3260 if ($tails == 1) {
3261 return 1 - abs($tValue);
3262 } else {
3263 return 1 - abs((1 - $tValue) - $tValue);
3264 }
3265 }
3267 } // function TDIST()
3268
3269
3279 public static function TINV($probability, $degrees) {
3280 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
3281 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
3282
3283 if ((is_numeric($probability)) && (is_numeric($degrees))) {
3284 $xLo = 100;
3285 $xHi = 0;
3286
3287 $x = $xNew = 1;
3288 $dx = 1;
3289 $i = 0;
3290
3291 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
3292 // Apply Newton-Raphson step
3293 $result = self::TDIST($x, $degrees, 2);
3294 $error = $result - $probability;
3295 if ($error == 0.0) {
3296 $dx = 0;
3297 } elseif ($error < 0.0) {
3298 $xLo = $x;
3299 } else {
3300 $xHi = $x;
3301 }
3302 // Avoid division by zero
3303 if ($result != 0.0) {
3304 $dx = $error / $result;
3305 $xNew = $x - $dx;
3306 }
3307 // If the NR fails to converge (which for example may be the
3308 // case if the initial guess is too rough) we apply a bisection
3309 // step to determine a more narrow interval around the root.
3310 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
3311 $xNew = ($xLo + $xHi) / 2;
3312 $dx = $xNew - $x;
3313 }
3314 $x = $xNew;
3315 }
3316 if ($i == MAX_ITERATIONS) {
3318 }
3319 return round($x,12);
3320 }
3322 } // function TINV()
3323
3324
3336 public static function TREND($yValues,$xValues=array(),$newValues=array(),$const=True) {
3339 $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
3340 $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
3341
3342 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
3343 if (empty($newValues)) {
3344 $newValues = $bestFitLinear->getXValues();
3345 }
3346
3347 $returnArray = array();
3348 foreach($newValues as $xValue) {
3349 $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue);
3350 }
3351
3352 return $returnArray;
3353 } // function TREND()
3354
3355
3372 public static function TRIMMEAN() {
3373 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3374
3375 // Calculate
3376 $percent = array_pop($aArgs);
3377
3378 if ((is_numeric($percent)) && (!is_string($percent))) {
3379 if (($percent < 0) || ($percent > 1)) {
3381 }
3382 $mArgs = array();
3383 foreach ($aArgs as $arg) {
3384 // Is it a numeric value?
3385 if ((is_numeric($arg)) && (!is_string($arg))) {
3386 $mArgs[] = $arg;
3387 }
3388 }
3389 $discard = floor(self::COUNT($mArgs) * $percent / 2);
3390 sort($mArgs);
3391 for ($i=0; $i < $discard; ++$i) {
3392 array_pop($mArgs);
3393 array_shift($mArgs);
3394 }
3395 return self::AVERAGE($mArgs);
3396 }
3398 } // function TRIMMEAN()
3399
3400
3414 public static function VARFunc() {
3415 // Return value
3416 $returnValue = PHPExcel_Calculation_Functions::DIV0();
3417
3418 $summerA = $summerB = 0;
3419
3420 // Loop through arguments
3421 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3422 $aCount = 0;
3423 foreach ($aArgs as $arg) {
3424 if (is_bool($arg)) { $arg = (integer) $arg; }
3425 // Is it a numeric value?
3426 if ((is_numeric($arg)) && (!is_string($arg))) {
3427 $summerA += ($arg * $arg);
3428 $summerB += $arg;
3429 ++$aCount;
3430 }
3431 }
3432
3433 // Return
3434 if ($aCount > 1) {
3435 $summerA *= $aCount;
3436 $summerB *= $summerB;
3437 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3438 }
3439 return $returnValue;
3440 } // function VARFunc()
3441
3442
3456 public static function VARA() {
3457 // Return value
3458 $returnValue = PHPExcel_Calculation_Functions::DIV0();
3459
3460 $summerA = $summerB = 0;
3461
3462 // Loop through arguments
3464 $aCount = 0;
3465 foreach ($aArgs as $k => $arg) {
3466 if ((is_string($arg)) &&
3469 } elseif ((is_string($arg)) &&
3471 } else {
3472 // Is it a numeric value?
3473 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3474 if (is_bool($arg)) {
3475 $arg = (integer) $arg;
3476 } elseif (is_string($arg)) {
3477 $arg = 0;
3478 }
3479 $summerA += ($arg * $arg);
3480 $summerB += $arg;
3481 ++$aCount;
3482 }
3483 }
3484 }
3485
3486 // Return
3487 if ($aCount > 1) {
3488 $summerA *= $aCount;
3489 $summerB *= $summerB;
3490 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3491 }
3492 return $returnValue;
3493 } // function VARA()
3494
3495
3509 public static function VARP() {
3510 // Return value
3511 $returnValue = PHPExcel_Calculation_Functions::DIV0();
3512
3513 $summerA = $summerB = 0;
3514
3515 // Loop through arguments
3516 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3517 $aCount = 0;
3518 foreach ($aArgs as $arg) {
3519 if (is_bool($arg)) { $arg = (integer) $arg; }
3520 // Is it a numeric value?
3521 if ((is_numeric($arg)) && (!is_string($arg))) {
3522 $summerA += ($arg * $arg);
3523 $summerB += $arg;
3524 ++$aCount;
3525 }
3526 }
3527
3528 // Return
3529 if ($aCount > 0) {
3530 $summerA *= $aCount;
3531 $summerB *= $summerB;
3532 $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3533 }
3534 return $returnValue;
3535 } // function VARP()
3536
3537
3551 public static function VARPA() {
3552 // Return value
3553 $returnValue = PHPExcel_Calculation_Functions::DIV0();
3554
3555 $summerA = $summerB = 0;
3556
3557 // Loop through arguments
3559 $aCount = 0;
3560 foreach ($aArgs as $k => $arg) {
3561 if ((is_string($arg)) &&
3564 } elseif ((is_string($arg)) &&
3566 } else {
3567 // Is it a numeric value?
3568 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
3569 if (is_bool($arg)) {
3570 $arg = (integer) $arg;
3571 } elseif (is_string($arg)) {
3572 $arg = 0;
3573 }
3574 $summerA += ($arg * $arg);
3575 $summerB += $arg;
3576 ++$aCount;
3577 }
3578 }
3579 }
3580
3581 // Return
3582 if ($aCount > 0) {
3583 $summerA *= $aCount;
3584 $summerB *= $summerB;
3585 $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3586 }
3587 return $returnValue;
3588 } // function VARPA()
3589
3590
3604 public static function WEIBULL($value, $alpha, $beta, $cumulative) {
3608
3609 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
3610 if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
3612 }
3613 if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
3614 if ($cumulative) {
3615 return 1 - exp(0 - pow($value / $beta,$alpha));
3616 } else {
3617 return ($alpha / pow($beta,$alpha)) * pow($value,$alpha - 1) * exp(0 - pow($value / $beta,$alpha));
3618 }
3619 }
3620 }
3622 } // function WEIBULL()
3623
3624
3638 public static function ZTEST($dataSet, $m0, $sigma = NULL) {
3642
3643 if (is_null($sigma)) {
3644 $sigma = self::STDEV($dataSet);
3645 }
3646 $n = count($dataSet);
3647
3648 return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0)/($sigma/SQRT($n)));
3649 } // function ZTEST()
3650
3651} // class PHPExcel_Calculation_Statistical
$result
const MAX_ITERATIONS
MAX_ITERATIONS.
Definition: Functions.php:46
const MAX_VALUE(!defined('PHPEXCEL_ROOT'))
PHPExcel root directory.
Definition: Functions.php:40
const PRECISION
PRECISION.
Definition: Functions.php:49
const M_2DIVPI
2 / PI
Definition: Functions.php:43
$n
Definition: RandomTest.php:85
$size
Definition: RandomTest.php:84
const SQRT2PI
SQRT2PI.
Definition: Statistical.php:52
const LOG_GAMMA_X_MAX_VALUE
LOG_GAMMA_X_MAX_VALUE.
Definition: Statistical.php:43
const EPS
EPS.
Definition: Statistical.php:49
const XMININ
XMININ.
Definition: Statistical.php:46
An exception for terminatinating execution or to throw for unit testing.
static _ifCondition($condition)
Definition: Functions.php:309
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
static flattenArrayIndexed($array)
Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.
Definition: Functions.php:630
static COMBIN($numObjs, $numInSet)
Definition: MathTrig.php:176
static NORMINV($probability, $mean, $stdDev)
NORMINV.
static NEGBINOMDIST($failures, $successes, $probability)
NEGBINOMDIST.
static RSQ($yValues, $xValues)
RSQ.
static NORMDIST($value, $mean, $stdDev, $cumulative)
NORMDIST.
static STANDARDIZE($value, $mean, $stdDev)
STANDARDIZE.
static _checkTrendArrays(&$array1, &$array2)
Definition: Statistical.php:65
static COVAR($yValues, $xValues)
COVAR.
static GROWTH($yValues, $xValues=array(), $newValues=array(), $const=True)
GROWTH.
static ZTEST($dataSet, $m0, $sigma=NULL)
ZTEST.
static HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber)
HYPGEOMDIST.
static FISHER($value)
FISHER.
static EXPONDIST($value, $lambda, $cumulative)
EXPONDIST.
static LOGNORMDIST($value, $mean, $stdDev)
LOGNORMDIST.
static NORMSDIST($value)
NORMSDIST.
static _logBeta($p, $q)
The natural logarithm of the beta function.
static CHIINV($probability, $degrees)
CHIINV.
static LOGEST($yValues, $xValues=null, $const=True, $stats=False)
LOGEST.
static FISHERINV($value)
FISHERINV.
static TDIST($value, $degrees, $tails)
TDIST.
static _beta($p, $q)
Beta function.
Definition: Statistical.php:99
static SLOPE($yValues, $xValues)
SLOPE.
static $_logGammaCache_result
logGamma function
static WEIBULL($value, $alpha, $beta, $cumulative)
WEIBULL.
static PERCENTRANK($valueSet, $value, $significance=3)
PERCENTRANK.
static CHIDIST($value, $degrees)
CHIDIST.
static AVERAGEIF($aArgs, $condition, $averageArgs=array())
static NORMSINV($value)
NORMSINV.
static STEYX($yValues, $xValues)
STEYX.
static RANK($value, $valueSet, $order=0)
RANK.
static GAMMALN($value)
GAMMALN.
static BINOMDIST($value, $trials, $probability, $cumulative)
BINOMDIST.
static LINEST($yValues, $xValues=NULL, $const=TRUE, $stats=FALSE)
LINEST.
static CONFIDENCE($alpha, $stdDev, $size)
CONFIDENCE.
static MAXIF($aArgs, $condition, $sumArgs=array())
static CRITBINOM($trials, $probability, $alpha)
CRITBINOM.
static COUNTIF($aArgs, $condition)
static TINV($probability, $degrees)
TINV.
static INTERCEPT($yValues, $xValues)
INTERCEPT.
static _incompleteBeta($x, $p, $q)
Incomplete beta function.
static FORECAST($xValue, $yValues, $xValues)
FORECAST.
static BETAINV($probability, $alpha, $beta, $rMin=0, $rMax=1)
BETAINV.
static GAMMADIST($value, $a, $b, $cumulative)
GAMMADIST.
static _betaFraction($x, $p, $q)
Evaluates of continued fraction part of incomplete beta function.
static TREND($yValues, $xValues=array(), $newValues=array(), $const=True)
TREND.
static CORREL($yValues, $xValues=null)
CORREL.
static MINIF($aArgs, $condition, $sumArgs=array())
static POISSON($value, $mean, $cumulative)
POISSON.
static LOGINV($probability, $mean, $stdDev)
LOGINV.
static GAMMAINV($probability, $alpha, $beta)
GAMMAINV.
static PERMUT($numObjs, $numInSet)
PERMUT.
static BETADIST($value, $alpha, $beta, $rMin=0, $rMax=1)
BETADIST.
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
static _wrapResult($value)
Wrap string values in quotes.
const TREND_LINEAR
Definition: trendClass.php:45
static calculate($trendType=self::TREND_BEST_FIT, $yValues, $xValues=array(), $const=True)
Definition: trendClass.php:87
const TREND_EXPONENTIAL
Definition: trendClass.php:47
$key
Definition: croninfo.php:18
for( $i=6;$i< 13;$i++) for($i=1; $i< 13; $i++) $d
Definition: date.php:296
$i
Definition: disco.tpl.php:19
$pdf
Definition: example_001.php:31
$y
Definition: example_007.php:83
$x
Definition: example_009.php:98
$h
$r
Definition: example_031.php:79
e($cmd)
Definition: flush.php:14
$error
Definition: Error.php:17
$index
Definition: metadata.php:60
$stats
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
foreach($_POST as $key=> $value) $res