1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29:
30: if (!defined('PHPEXCEL_ROOT')) {
31: 32: 33:
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
40:
41:
42:
43: define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
44:
45:
46: define('XMININ', 2.23e-308);
47:
48:
49: define('EPS', 2.22e-16);
50:
51:
52: define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
53:
54:
55: 56: 57: 58: 59: 60: 61:
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: }
88:
89:
90: 91: 92: 93: 94: 95: 96: 97: 98:
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: }
106:
107:
108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119:
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: }
135:
136:
137:
138: private static $_logBetaCache_p = 0.0;
139: private static $_logBetaCache_q = 0.0;
140: private static $_logBetaCache_result = 0.0;
141:
142: 143: 144: 145: 146: 147: 148: 149:
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: }
160: return self::$_logBetaCache_result;
161: }
162:
163:
164: 165: 166: 167: 168:
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:
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:
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: }
213:
214:
215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256:
257:
258:
259: private static $_logGammaCache_result = 0.0;
260: private static $_logGammaCache_x = 0.0;
261:
262: private static function _logGamma($x) {
263:
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:
327: static $lg_frtbig = 2.25e76;
328: static $pnt68 = 0.6796875;
329:
330:
331: if ($x == self::$_logGammaCache_x) {
332: return self::$_logGammaCache_result;
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:
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:
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:
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:
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:
410:
411: $res = MAX_VALUE;
412: }
413:
414:
415:
416: self::$_logGammaCache_x = $x;
417: self::$_logGammaCache_result = $res;
418: return $res;
419: }
420:
421:
422:
423:
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: }
437:
438:
439:
440:
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: }
464:
465:
466: 467: 468: 469: 470: 471: 472: 473:
474: private static function _inverse_ncdf($p) {
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
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:
518: $p_low = 0.02425;
519: $p_high = 1 - $p_low;
520:
521: if (0 < $p && $p < $p_low) {
522:
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:
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:
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:
539: return PHPExcel_Calculation_Functions::NULL();
540: }
541:
542:
543: private static function _inverse_ncdf2($prob) {
544:
545:
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: }
584:
585:
586: private static function _inverse_ncdf3($p) {
587:
588:
589:
590:
591:
592:
593: $split1 = 0.425;
594: $split2 = 5;
595: $const1 = 0.180625;
596: $const2 = 1.6;
597:
598:
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:
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:
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:
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:
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:
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: }
684:
685:
686: 687: 688: 689: 690: 691: 692: 693: 694: 695: 696: 697: 698: 699:
700: public static function AVEDEV() {
701: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
702:
703:
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)) &&
711: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
712: $arg = (integer) $arg;
713: }
714:
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:
726: if ($aCount == 0) {
727: return PHPExcel_Calculation_Functions::DIV0();
728: }
729: return $returnValue / $aCount;
730: }
731: return PHPExcel_Calculation_Functions::NaN();
732: }
733:
734:
735: 736: 737: 738: 739: 740: 741: 742: 743: 744: 745: 746: 747:
748: public static function AVERAGE() {
749: $returnValue = $aCount = 0;
750:
751:
752: foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
753: if ((is_bool($arg)) &&
754: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
755: $arg = (integer) $arg;
756: }
757:
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:
769: if ($aCount > 0) {
770: return $returnValue / $aCount;
771: } else {
772: return PHPExcel_Calculation_Functions::DIV0();
773: }
774: }
775:
776:
777: 778: 779: 780: 781: 782: 783: 784: 785: 786: 787: 788: 789:
790: public static function AVERAGEA() {
791:
792: $returnValue = null;
793:
794: $aCount = 0;
795:
796: foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
797: if ((is_bool($arg)) &&
798: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
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:
817: if ($aCount > 0) {
818: return $returnValue / $aCount;
819: } else {
820: return PHPExcel_Calculation_Functions::DIV0();
821: }
822: }
823:
824:
825: 826: 827: 828: 829: 830: 831: 832: 833: 834: 835: 836: 837: 838: 839:
840: public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
841:
842: $returnValue = 0;
843:
844: $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
845: $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
846: if (empty($averageArgs)) {
847: $averageArgs = $aArgs;
848: }
849: $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
850:
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:
864: if ($aCount > 0) {
865: return $returnValue / $aCount;
866: } else {
867: return PHPExcel_Calculation_Functions::DIV0();
868: }
869: }
870:
871:
872: 873: 874: 875: 876: 877: 878: 879: 880: 881: 882: 883:
884: public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
885: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
886: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
887: $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
888: $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
889: $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
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)) {
893: return PHPExcel_Calculation_Functions::NaN();
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: }
904: return PHPExcel_Calculation_Functions::VALUE();
905: }
906:
907:
908: 909: 910: 911: 912: 913: 914: 915: 916: 917: 918: 919: 920: 921:
922: public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
923: $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
924: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
925: $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
926: $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
927: $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
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)) {
931: return PHPExcel_Calculation_Functions::NaN();
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) {
954: return PHPExcel_Calculation_Functions::NA();
955: }
956: return round($rMin + $guess * ($rMax - $rMin),12);
957: }
958: return PHPExcel_Calculation_Functions::VALUE();
959: }
960:
961:
962: 963: 964: 965: 966: 967: 968: 969: 970: 971: 972: 973: 974: 975: 976: 977: 978: 979:
980: public static function BINOMDIST($value, $trials, $probability, $cumulative) {
981: $value = floor(PHPExcel_Calculation_Functions::flattenSingleValue($value));
982: $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
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)) {
987: return PHPExcel_Calculation_Functions::NaN();
988: }
989: if (($probability < 0) || ($probability > 1)) {
990: return PHPExcel_Calculation_Functions::NaN();
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: }
1004: return PHPExcel_Calculation_Functions::VALUE();
1005: }
1006:
1007:
1008: 1009: 1010: 1011: 1012: 1013: 1014: 1015: 1016:
1017: public static function CHIDIST($value, $degrees) {
1018: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1019: $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1020:
1021: if ((is_numeric($value)) && (is_numeric($degrees))) {
1022: if ($degrees < 1) {
1023: return PHPExcel_Calculation_Functions::NaN();
1024: }
1025: if ($value < 0) {
1026: if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
1027: return 1;
1028: }
1029: return PHPExcel_Calculation_Functions::NaN();
1030: }
1031: return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
1032: }
1033: return PHPExcel_Calculation_Functions::VALUE();
1034: }
1035:
1036:
1037: 1038: 1039: 1040: 1041: 1042: 1043: 1044: 1045:
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:
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:
1071: if ($result != 0.0) {
1072: $dx = $error / $result;
1073: $xNew = $x - $dx;
1074: }
1075:
1076:
1077:
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) {
1085: return PHPExcel_Calculation_Functions::NA();
1086: }
1087: return round($x,12);
1088: }
1089: return PHPExcel_Calculation_Functions::VALUE();
1090: }
1091:
1092:
1093: 1094: 1095: 1096: 1097: 1098: 1099: 1100: 1101: 1102: 1103:
1104: public static function CONFIDENCE($alpha,$stdDev,$size) {
1105: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1106: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
1107: $size = floor(PHPExcel_Calculation_Functions::flattenSingleValue($size));
1108:
1109: if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
1110: if (($alpha <= 0) || ($alpha >= 1)) {
1111: return PHPExcel_Calculation_Functions::NaN();
1112: }
1113: if (($stdDev <= 0) || ($size < 1)) {
1114: return PHPExcel_Calculation_Functions::NaN();
1115: }
1116: return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
1117: }
1118: return PHPExcel_Calculation_Functions::VALUE();
1119: }
1120:
1121:
1122: 1123: 1124: 1125: 1126: 1127: 1128: 1129: 1130:
1131: public static function CORREL($yValues,$xValues=null) {
1132: if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
1133: return PHPExcel_Calculation_Functions::VALUE();
1134: }
1135: if (!self::_checkTrendArrays($yValues,$xValues)) {
1136: return PHPExcel_Calculation_Functions::VALUE();
1137: }
1138: $yValueCount = count($yValues);
1139: $xValueCount = count($xValues);
1140:
1141: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1142: return PHPExcel_Calculation_Functions::NA();
1143: } elseif ($yValueCount == 1) {
1144: return PHPExcel_Calculation_Functions::DIV0();
1145: }
1146:
1147: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1148: return $bestFitLinear->getCorrelation();
1149: }
1150:
1151:
1152: 1153: 1154: 1155: 1156: 1157: 1158: 1159: 1160: 1161: 1162: 1163: 1164:
1165: public static function COUNT() {
1166:
1167: $returnValue = 0;
1168:
1169:
1170: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1171: foreach ($aArgs as $k => $arg) {
1172: if ((is_bool($arg)) &&
1173: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
1174: $arg = (integer) $arg;
1175: }
1176:
1177: if ((is_numeric($arg)) && (!is_string($arg))) {
1178: ++$returnValue;
1179: }
1180: }
1181:
1182:
1183: return $returnValue;
1184: }
1185:
1186:
1187: 1188: 1189: 1190: 1191: 1192: 1193: 1194: 1195: 1196: 1197: 1198: 1199:
1200: public static function COUNTA() {
1201:
1202: $returnValue = 0;
1203:
1204:
1205: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1206: foreach ($aArgs as $arg) {
1207:
1208: if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1209: ++$returnValue;
1210: }
1211: }
1212:
1213:
1214: return $returnValue;
1215: }
1216:
1217:
1218: 1219: 1220: 1221: 1222: 1223: 1224: 1225: 1226: 1227: 1228: 1229: 1230:
1231: public static function COUNTBLANK() {
1232:
1233: $returnValue = 0;
1234:
1235:
1236: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1237: foreach ($aArgs as $arg) {
1238:
1239: if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
1240: ++$returnValue;
1241: }
1242: }
1243:
1244:
1245: return $returnValue;
1246: }
1247:
1248:
1249: 1250: 1251: 1252: 1253: 1254: 1255: 1256: 1257: 1258: 1259: 1260: 1261: 1262:
1263: public static function COUNTIF($aArgs,$condition) {
1264:
1265: $returnValue = 0;
1266:
1267: $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
1268: $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1269:
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:
1275: ++$returnValue;
1276: }
1277: }
1278:
1279:
1280: return $returnValue;
1281: }
1282:
1283:
1284: 1285: 1286: 1287: 1288: 1289: 1290: 1291: 1292:
1293: public static function COVAR($yValues,$xValues) {
1294: if (!self::_checkTrendArrays($yValues,$xValues)) {
1295: return PHPExcel_Calculation_Functions::VALUE();
1296: }
1297: $yValueCount = count($yValues);
1298: $xValueCount = count($xValues);
1299:
1300: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1301: return PHPExcel_Calculation_Functions::NA();
1302: } elseif ($yValueCount == 1) {
1303: return PHPExcel_Calculation_Functions::DIV0();
1304: }
1305:
1306: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1307: return $bestFitLinear->getCovariance();
1308: }
1309:
1310:
1311: 1312: 1313: 1314: 1315: 1316: 1317: 1318: 1319: 1320: 1321: 1322: 1323: 1324: 1325: 1326: 1327: 1328: 1329:
1330: public static function CRITBINOM($trials, $probability, $alpha) {
1331: $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
1332: $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1333: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1334:
1335: if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
1336: if ($trials < 0) {
1337: return PHPExcel_Calculation_Functions::NaN();
1338: }
1339: if (($probability < 0) || ($probability > 1)) {
1340: return PHPExcel_Calculation_Functions::NaN();
1341: }
1342: if (($alpha < 0) || ($alpha > 1)) {
1343: return PHPExcel_Calculation_Functions::NaN();
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:
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: }
1417: return PHPExcel_Calculation_Functions::VALUE();
1418: }
1419:
1420:
1421: 1422: 1423: 1424: 1425: 1426: 1427: 1428: 1429: 1430: 1431: 1432: 1433:
1434: public static function DEVSQ() {
1435: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1436:
1437:
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:
1445: if ((is_bool($arg)) &&
1446: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
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:
1460: if (is_null($returnValue)) {
1461: return PHPExcel_Calculation_Functions::NaN();
1462: } else {
1463: return $returnValue;
1464: }
1465: }
1466: return self::NA();
1467: }
1468:
1469:
1470: 1471: 1472: 1473: 1474: 1475: 1476: 1477: 1478: 1479: 1480: 1481:
1482: public static function EXPONDIST($value, $lambda, $cumulative) {
1483: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1484: $lambda = PHPExcel_Calculation_Functions::flattenSingleValue($lambda);
1485: $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
1486:
1487: if ((is_numeric($value)) && (is_numeric($lambda))) {
1488: if (($value < 0) || ($lambda < 0)) {
1489: return PHPExcel_Calculation_Functions::NaN();
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: }
1499: return PHPExcel_Calculation_Functions::VALUE();
1500: }
1501:
1502:
1503: 1504: 1505: 1506: 1507: 1508: 1509: 1510: 1511: 1512:
1513: public static function FISHER($value) {
1514: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1515:
1516: if (is_numeric($value)) {
1517: if (($value <= -1) || ($value >= 1)) {
1518: return PHPExcel_Calculation_Functions::NaN();
1519: }
1520: return 0.5 * log((1+$value)/(1-$value));
1521: }
1522: return PHPExcel_Calculation_Functions::VALUE();
1523: }
1524:
1525:
1526: 1527: 1528: 1529: 1530: 1531: 1532: 1533: 1534: 1535:
1536: public static function FISHERINV($value) {
1537: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1538:
1539: if (is_numeric($value)) {
1540: return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
1541: }
1542: return PHPExcel_Calculation_Functions::VALUE();
1543: }
1544:
1545:
1546: 1547: 1548: 1549: 1550: 1551: 1552: 1553: 1554: 1555:
1556: public static function FORECAST($xValue,$yValues,$xValues) {
1557: $xValue = PHPExcel_Calculation_Functions::flattenSingleValue($xValue);
1558: if (!is_numeric($xValue)) {
1559: return PHPExcel_Calculation_Functions::VALUE();
1560: }
1561:
1562: if (!self::_checkTrendArrays($yValues,$xValues)) {
1563: return PHPExcel_Calculation_Functions::VALUE();
1564: }
1565: $yValueCount = count($yValues);
1566: $xValueCount = count($xValues);
1567:
1568: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1569: return PHPExcel_Calculation_Functions::NA();
1570: } elseif ($yValueCount == 1) {
1571: return PHPExcel_Calculation_Functions::DIV0();
1572: }
1573:
1574: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1575: return $bestFitLinear->getValueOfYForX($xValue);
1576: }
1577:
1578:
1579: 1580: 1581: 1582: 1583: 1584: 1585: 1586: 1587: 1588: 1589: 1590:
1591: public static function GAMMADIST($value,$a,$b,$cumulative) {
1592: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1593: $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
1594: $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
1595:
1596: if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
1597: if (($value < 0) || ($a <= 0) || ($b <= 0)) {
1598: return PHPExcel_Calculation_Functions::NaN();
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: }
1608: return PHPExcel_Calculation_Functions::VALUE();
1609: }
1610:
1611:
1612: 1613: 1614: 1615: 1616: 1617: 1618: 1619: 1620: 1621: 1622:
1623: public static function GAMMAINV($probability,$alpha,$beta) {
1624: $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1625: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1626: $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
1627:
1628: if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
1629: if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
1630: return PHPExcel_Calculation_Functions::NaN();
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:
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:
1651: if ($pdf != 0.0) {
1652: $dx = $error / $pdf;
1653: $xNew = $x - $dx;
1654: }
1655:
1656:
1657:
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) {
1665: return PHPExcel_Calculation_Functions::NA();
1666: }
1667: return $x;
1668: }
1669: return PHPExcel_Calculation_Functions::VALUE();
1670: }
1671:
1672:
1673: 1674: 1675: 1676: 1677: 1678: 1679: 1680:
1681: public static function GAMMALN($value) {
1682: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
1683:
1684: if (is_numeric($value)) {
1685: if ($value <= 0) {
1686: return PHPExcel_Calculation_Functions::NaN();
1687: }
1688: return log(self::_gamma($value));
1689: }
1690: return PHPExcel_Calculation_Functions::VALUE();
1691: }
1692:
1693:
1694: 1695: 1696: 1697: 1698: 1699: 1700: 1701: 1702: 1703: 1704: 1705: 1706: 1707: 1708:
1709: public static function GEOMEAN() {
1710: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1711:
1712: $aMean = PHPExcel_Calculation_MathTrig::PRODUCT($aArgs);
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: }
1719: return PHPExcel_Calculation_Functions::NaN();
1720: }
1721:
1722:
1723: 1724: 1725: 1726: 1727: 1728: 1729: 1730: 1731: 1732: 1733:
1734: public static function GROWTH($yValues,$xValues=array(),$newValues=array(),$const=True) {
1735: $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
1736: $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
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: }
1752:
1753:
1754: 1755: 1756: 1757: 1758: 1759: 1760: 1761: 1762: 1763: 1764: 1765: 1766: 1767:
1768: public static function HARMEAN() {
1769:
1770: $returnValue = PHPExcel_Calculation_Functions::NA();
1771:
1772:
1773: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1774: if (self::MIN($aArgs) < 0) {
1775: return PHPExcel_Calculation_Functions::NaN();
1776: }
1777: $aCount = 0;
1778: foreach ($aArgs as $arg) {
1779:
1780: if ((is_numeric($arg)) && (!is_string($arg))) {
1781: if ($arg <= 0) {
1782: return PHPExcel_Calculation_Functions::NaN();
1783: }
1784: if (is_null($returnValue)) {
1785: $returnValue = (1 / $arg);
1786: } else {
1787: $returnValue += (1 / $arg);
1788: }
1789: ++$aCount;
1790: }
1791: }
1792:
1793:
1794: if ($aCount > 0) {
1795: return 1 / ($returnValue / $aCount);
1796: } else {
1797: return $returnValue;
1798: }
1799: }
1800:
1801:
1802: 1803: 1804: 1805: 1806: 1807: 1808: 1809: 1810: 1811: 1812: 1813: 1814:
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)) {
1823: return PHPExcel_Calculation_Functions::NaN();
1824: }
1825: if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
1826: return PHPExcel_Calculation_Functions::NaN();
1827: }
1828: if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
1829: return PHPExcel_Calculation_Functions::NaN();
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: }
1835: return PHPExcel_Calculation_Functions::VALUE();
1836: }
1837:
1838:
1839: 1840: 1841: 1842: 1843: 1844: 1845: 1846: 1847:
1848: public static function INTERCEPT($yValues,$xValues) {
1849: if (!self::_checkTrendArrays($yValues,$xValues)) {
1850: return PHPExcel_Calculation_Functions::VALUE();
1851: }
1852: $yValueCount = count($yValues);
1853: $xValueCount = count($xValues);
1854:
1855: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1856: return PHPExcel_Calculation_Functions::NA();
1857: } elseif ($yValueCount == 1) {
1858: return PHPExcel_Calculation_Functions::DIV0();
1859: }
1860:
1861: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1862: return $bestFitLinear->getIntersect();
1863: }
1864:
1865:
1866: 1867: 1868: 1869: 1870: 1871: 1872: 1873: 1874: 1875: 1876:
1877: public static function KURT() {
1878: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1879: $mean = self::AVERAGE($aArgs);
1880: $stdDev = self::STDEV($aArgs);
1881:
1882: if ($stdDev > 0) {
1883: $count = $summer = 0;
1884:
1885: foreach ($aArgs as $k => $arg) {
1886: if ((is_bool($arg)) &&
1887: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
1888: } else {
1889:
1890: if ((is_numeric($arg)) && (!is_string($arg))) {
1891: $summer += pow((($arg - $mean) / $stdDev),4) ;
1892: ++$count;
1893: }
1894: }
1895: }
1896:
1897:
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: }
1902: return PHPExcel_Calculation_Functions::DIV0();
1903: }
1904:
1905:
1906: 1907: 1908: 1909: 1910: 1911: 1912: 1913: 1914: 1915: 1916: 1917: 1918: 1919: 1920: 1921:
1922: public static function LARGE() {
1923: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1924:
1925:
1926: $entry = floor(array_pop($aArgs));
1927:
1928: if ((is_numeric($entry)) && (!is_string($entry))) {
1929: $mArgs = array();
1930: foreach ($aArgs as $arg) {
1931:
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)) {
1939: return PHPExcel_Calculation_Functions::NaN();
1940: }
1941: rsort($mArgs);
1942: return $mArgs[$entry];
1943: }
1944: return PHPExcel_Calculation_Functions::VALUE();
1945: }
1946:
1947:
1948: 1949: 1950: 1951: 1952: 1953: 1954: 1955: 1956: 1957: 1958: 1959:
1960: public static function LINEST($yValues, $xValues = NULL, $const = TRUE, $stats = FALSE) {
1961: $const = (is_null($const)) ? TRUE : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
1962: $stats = (is_null($stats)) ? FALSE : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
1963: if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
1964:
1965: if (!self::_checkTrendArrays($yValues,$xValues)) {
1966: return PHPExcel_Calculation_Functions::VALUE();
1967: }
1968: $yValueCount = count($yValues);
1969: $xValueCount = count($xValues);
1970:
1971:
1972: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1973: return PHPExcel_Calculation_Functions::NA();
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: }
1999:
2000:
2001: 2002: 2003: 2004: 2005: 2006: 2007: 2008: 2009: 2010: 2011: 2012:
2013: public static function LOGEST($yValues,$xValues=null,$const=True,$stats=False) {
2014: $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
2015: $stats = (is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
2016: if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
2017:
2018: if (!self::_checkTrendArrays($yValues,$xValues)) {
2019: return PHPExcel_Calculation_Functions::VALUE();
2020: }
2021: $yValueCount = count($yValues);
2022: $xValueCount = count($xValues);
2023:
2024: foreach($yValues as $value) {
2025: if ($value <= 0.0) {
2026: return PHPExcel_Calculation_Functions::NaN();
2027: }
2028: }
2029:
2030:
2031: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2032: return PHPExcel_Calculation_Functions::NA();
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: }
2058:
2059:
2060: 2061: 2062: 2063: 2064: 2065: 2066: 2067: 2068: 2069: 2070: 2071: 2072: 2073:
2074: public static function LOGINV($probability, $mean, $stdDev) {
2075: $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2076: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2077: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2078:
2079: if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2080: if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
2081: return PHPExcel_Calculation_Functions::NaN();
2082: }
2083: return exp($mean + $stdDev * self::NORMSINV($probability));
2084: }
2085: return PHPExcel_Calculation_Functions::VALUE();
2086: }
2087:
2088:
2089: 2090: 2091: 2092: 2093: 2094: 2095: 2096: 2097: 2098: 2099:
2100: public static function LOGNORMDIST($value, $mean, $stdDev) {
2101: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2102: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2103: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2104:
2105: if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2106: if (($value <= 0) || ($stdDev <= 0)) {
2107: return PHPExcel_Calculation_Functions::NaN();
2108: }
2109: return self::NORMSDIST((log($value) - $mean) / $stdDev);
2110: }
2111: return PHPExcel_Calculation_Functions::VALUE();
2112: }
2113:
2114:
2115: 2116: 2117: 2118: 2119: 2120: 2121: 2122: 2123: 2124: 2125: 2126: 2127: 2128:
2129: public static function MAX() {
2130:
2131: $returnValue = null;
2132:
2133:
2134: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2135: foreach ($aArgs as $arg) {
2136:
2137: if ((is_numeric($arg)) && (!is_string($arg))) {
2138: if ((is_null($returnValue)) || ($arg > $returnValue)) {
2139: $returnValue = $arg;
2140: }
2141: }
2142: }
2143:
2144:
2145: if(is_null($returnValue)) {
2146: return 0;
2147: }
2148: return $returnValue;
2149: }
2150:
2151:
2152: 2153: 2154: 2155: 2156: 2157: 2158: 2159: 2160: 2161: 2162: 2163: 2164:
2165: public static function MAXA() {
2166:
2167: $returnValue = null;
2168:
2169:
2170: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2171: foreach ($aArgs as $arg) {
2172:
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:
2186: if(is_null($returnValue)) {
2187: return 0;
2188: }
2189: return $returnValue;
2190: }
2191:
2192:
2193: 2194: 2195: 2196: 2197: 2198: 2199: 2200: 2201: 2202: 2203: 2204: 2205: 2206:
2207: public static function MAXIF($aArgs,$condition,$sumArgs = array()) {
2208:
2209: $returnValue = null;
2210:
2211: $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
2212: $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
2213: if (empty($sumArgs)) {
2214: $sumArgs = $aArgs;
2215: }
2216: $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2217:
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:
2229: return $returnValue;
2230: }
2231:
2232:
2233: 2234: 2235: 2236: 2237: 2238: 2239: 2240: 2241: 2242: 2243: 2244: 2245:
2246: public static function MEDIAN() {
2247:
2248: $returnValue = PHPExcel_Calculation_Functions::NaN();
2249:
2250: $mArgs = array();
2251:
2252: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2253: foreach ($aArgs as $arg) {
2254:
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:
2273: return $returnValue;
2274: }
2275:
2276:
2277: 2278: 2279: 2280: 2281: 2282: 2283: 2284: 2285: 2286: 2287: 2288: 2289: 2290:
2291: public static function MIN() {
2292:
2293: $returnValue = null;
2294:
2295:
2296: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2297: foreach ($aArgs as $arg) {
2298:
2299: if ((is_numeric($arg)) && (!is_string($arg))) {
2300: if ((is_null($returnValue)) || ($arg < $returnValue)) {
2301: $returnValue = $arg;
2302: }
2303: }
2304: }
2305:
2306:
2307: if(is_null($returnValue)) {
2308: return 0;
2309: }
2310: return $returnValue;
2311: }
2312:
2313:
2314: 2315: 2316: 2317: 2318: 2319: 2320: 2321: 2322: 2323: 2324: 2325: 2326:
2327: public static function MINA() {
2328:
2329: $returnValue = null;
2330:
2331:
2332: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2333: foreach ($aArgs as $arg) {
2334:
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:
2348: if(is_null($returnValue)) {
2349: return 0;
2350: }
2351: return $returnValue;
2352: }
2353:
2354:
2355: 2356: 2357: 2358: 2359: 2360: 2361: 2362: 2363: 2364: 2365: 2366: 2367: 2368:
2369: public static function MINIF($aArgs,$condition,$sumArgs = array()) {
2370:
2371: $returnValue = null;
2372:
2373: $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
2374: $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
2375: if (empty($sumArgs)) {
2376: $sumArgs = $aArgs;
2377: }
2378: $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2379:
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:
2391: return $returnValue;
2392: }
2393:
2394:
2395:
2396:
2397:
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) {
2423: return PHPExcel_Calculation_Functions::NA();
2424: }
2425: return $frequencyArray[0]['value'];
2426: }
2427:
2428:
2429: 2430: 2431: 2432: 2433: 2434: 2435: 2436: 2437: 2438: 2439: 2440: 2441:
2442: public static function MODE() {
2443:
2444: $returnValue = PHPExcel_Calculation_Functions::NA();
2445:
2446:
2447: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2448:
2449: $mArgs = array();
2450: foreach ($aArgs as $arg) {
2451:
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:
2462: return $returnValue;
2463: }
2464:
2465:
2466: 2467: 2468: 2469: 2470: 2471: 2472: 2473: 2474: 2475: 2476: 2477: 2478: 2479: 2480:
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)) {
2488: return PHPExcel_Calculation_Functions::NaN();
2489: }
2490: if (($probability < 0) || ($probability > 1)) {
2491: return PHPExcel_Calculation_Functions::NaN();
2492: }
2493: if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
2494: if (($failures + $successes - 1) <= 0) {
2495: return PHPExcel_Calculation_Functions::NaN();
2496: }
2497: }
2498: return (PHPExcel_Calculation_MathTrig::COMBIN($failures + $successes - 1,$successes - 1)) * (pow($probability,$successes)) * (pow(1 - $probability,$failures)) ;
2499: }
2500: return PHPExcel_Calculation_Functions::VALUE();
2501: }
2502:
2503:
2504: 2505: 2506: 2507: 2508: 2509: 2510: 2511: 2512: 2513: 2514: 2515: 2516: 2517:
2518: public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
2519: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2520: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2521: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2522:
2523: if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2524: if ($stdDev < 0) {
2525: return PHPExcel_Calculation_Functions::NaN();
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: }
2535: return PHPExcel_Calculation_Functions::VALUE();
2536: }
2537:
2538:
2539: 2540: 2541: 2542: 2543: 2544: 2545: 2546: 2547: 2548: 2549:
2550: public static function NORMINV($probability,$mean,$stdDev) {
2551: $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
2552: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2553: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2554:
2555: if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2556: if (($probability < 0) || ($probability > 1)) {
2557: return PHPExcel_Calculation_Functions::NaN();
2558: }
2559: if ($stdDev < 0) {
2560: return PHPExcel_Calculation_Functions::NaN();
2561: }
2562: return (self::_inverse_ncdf($probability) * $stdDev) + $mean;
2563: }
2564: return PHPExcel_Calculation_Functions::VALUE();
2565: }
2566:
2567:
2568: 2569: 2570: 2571: 2572: 2573: 2574: 2575: 2576: 2577:
2578: public static function NORMSDIST($value) {
2579: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2580:
2581: return self::NORMDIST($value, 0, 1, True);
2582: }
2583:
2584:
2585: 2586: 2587: 2588: 2589: 2590: 2591: 2592:
2593: public static function NORMSINV($value) {
2594: return self::NORMINV($value, 0, 1);
2595: }
2596:
2597:
2598: 2599: 2600: 2601: 2602: 2603: 2604: 2605: 2606: 2607: 2608: 2609: 2610: 2611:
2612: public static function PERCENTILE() {
2613: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2614:
2615:
2616: $entry = array_pop($aArgs);
2617:
2618: if ((is_numeric($entry)) && (!is_string($entry))) {
2619: if (($entry < 0) || ($entry > 1)) {
2620: return PHPExcel_Calculation_Functions::NaN();
2621: }
2622: $mArgs = array();
2623: foreach ($aArgs as $arg) {
2624:
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: }
2644: return PHPExcel_Calculation_Functions::VALUE();
2645: }
2646:
2647:
2648: 2649: 2650: 2651: 2652: 2653: 2654: 2655: 2656: 2657:
2658: public static function PERCENTRANK($valueSet,$value,$significance=3) {
2659: $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
2660: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
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) {
2671: return PHPExcel_Calculation_Functions::NaN();
2672: }
2673:
2674: $valueAdjustor = $valueCount - 1;
2675: if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
2676: return PHPExcel_Calculation_Functions::NA();
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: }
2692:
2693:
2694: 2695: 2696: 2697: 2698: 2699: 2700: 2701: 2702: 2703: 2704: 2705: 2706:
2707: public static function PERMUT($numObjs,$numInSet) {
2708: $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs);
2709: $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet);
2710:
2711: if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
2712: $numInSet = floor($numInSet);
2713: if ($numObjs < $numInSet) {
2714: return PHPExcel_Calculation_Functions::NaN();
2715: }
2716: return round(PHPExcel_Calculation_MathTrig::FACT($numObjs) / PHPExcel_Calculation_MathTrig::FACT($numObjs - $numInSet));
2717: }
2718: return PHPExcel_Calculation_Functions::VALUE();
2719: }
2720:
2721:
2722: 2723: 2724: 2725: 2726: 2727: 2728: 2729: 2730: 2731: 2732: 2733: 2734:
2735: public static function POISSON($value, $mean, $cumulative) {
2736: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2737: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2738:
2739: if ((is_numeric($value)) && (is_numeric($mean))) {
2740: if (($value <= 0) || ($mean <= 0)) {
2741: return PHPExcel_Calculation_Functions::NaN();
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: }
2755: return PHPExcel_Calculation_Functions::VALUE();
2756: }
2757:
2758:
2759: 2760: 2761: 2762: 2763: 2764: 2765: 2766: 2767: 2768: 2769: 2770: 2771: 2772:
2773: public static function QUARTILE() {
2774: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2775:
2776:
2777: $entry = floor(array_pop($aArgs));
2778:
2779: if ((is_numeric($entry)) && (!is_string($entry))) {
2780: $entry /= 4;
2781: if (($entry < 0) || ($entry > 1)) {
2782: return PHPExcel_Calculation_Functions::NaN();
2783: }
2784: return self::PERCENTILE($aArgs,$entry);
2785: }
2786: return PHPExcel_Calculation_Functions::VALUE();
2787: }
2788:
2789:
2790: 2791: 2792: 2793: 2794: 2795: 2796: 2797: 2798: 2799:
2800: public static function RANK($value,$valueSet,$order=0) {
2801: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
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) {
2818: return PHPExcel_Calculation_Functions::NA();
2819: }
2820:
2821: return ++$pos;
2822: }
2823:
2824:
2825: 2826: 2827: 2828: 2829: 2830: 2831: 2832: 2833:
2834: public static function RSQ($yValues,$xValues) {
2835: if (!self::_checkTrendArrays($yValues,$xValues)) {
2836: return PHPExcel_Calculation_Functions::VALUE();
2837: }
2838: $yValueCount = count($yValues);
2839: $xValueCount = count($xValues);
2840:
2841: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2842: return PHPExcel_Calculation_Functions::NA();
2843: } elseif ($yValueCount == 1) {
2844: return PHPExcel_Calculation_Functions::DIV0();
2845: }
2846:
2847: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2848: return $bestFitLinear->getGoodnessOfFit();
2849: }
2850:
2851:
2852: 2853: 2854: 2855: 2856: 2857: 2858: 2859: 2860: 2861: 2862:
2863: public static function SKEW() {
2864: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
2865: $mean = self::AVERAGE($aArgs);
2866: $stdDev = self::STDEV($aArgs);
2867:
2868: $count = $summer = 0;
2869:
2870: foreach ($aArgs as $k => $arg) {
2871: if ((is_bool($arg)) &&
2872: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
2873: } else {
2874:
2875: if ((is_numeric($arg)) && (!is_string($arg))) {
2876: $summer += pow((($arg - $mean) / $stdDev),3) ;
2877: ++$count;
2878: }
2879: }
2880: }
2881:
2882:
2883: if ($count > 2) {
2884: return $summer * ($count / (($count-1) * ($count-2)));
2885: }
2886: return PHPExcel_Calculation_Functions::DIV0();
2887: }
2888:
2889:
2890: 2891: 2892: 2893: 2894: 2895: 2896: 2897: 2898:
2899: public static function SLOPE($yValues,$xValues) {
2900: if (!self::_checkTrendArrays($yValues,$xValues)) {
2901: return PHPExcel_Calculation_Functions::VALUE();
2902: }
2903: $yValueCount = count($yValues);
2904: $xValueCount = count($xValues);
2905:
2906: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2907: return PHPExcel_Calculation_Functions::NA();
2908: } elseif ($yValueCount == 1) {
2909: return PHPExcel_Calculation_Functions::DIV0();
2910: }
2911:
2912: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
2913: return $bestFitLinear->getSlope();
2914: }
2915:
2916:
2917: 2918: 2919: 2920: 2921: 2922: 2923: 2924: 2925: 2926: 2927: 2928: 2929: 2930: 2931:
2932: public static function SMALL() {
2933: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
2934:
2935:
2936: $entry = array_pop($aArgs);
2937:
2938: if ((is_numeric($entry)) && (!is_string($entry))) {
2939: $mArgs = array();
2940: foreach ($aArgs as $arg) {
2941:
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)) {
2949: return PHPExcel_Calculation_Functions::NaN();
2950: }
2951: sort($mArgs);
2952: return $mArgs[$entry];
2953: }
2954: return PHPExcel_Calculation_Functions::VALUE();
2955: }
2956:
2957:
2958: 2959: 2960: 2961: 2962: 2963: 2964: 2965: 2966: 2967:
2968: public static function STANDARDIZE($value,$mean,$stdDev) {
2969: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
2970: $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
2971: $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
2972:
2973: if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
2974: if ($stdDev <= 0) {
2975: return PHPExcel_Calculation_Functions::NaN();
2976: }
2977: return ($value - $mean) / $stdDev ;
2978: }
2979: return PHPExcel_Calculation_Functions::VALUE();
2980: }
2981:
2982:
2983: 2984: 2985: 2986: 2987: 2988: 2989: 2990: 2991: 2992: 2993: 2994: 2995: 2996:
2997: public static function STDEV() {
2998: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
2999:
3000:
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)) &&
3008: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
3009: $arg = (integer) $arg;
3010: }
3011:
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:
3023: if (($aCount > 0) && ($returnValue >= 0)) {
3024: return sqrt($returnValue / $aCount);
3025: }
3026: }
3027: return PHPExcel_Calculation_Functions::DIV0();
3028: }
3029:
3030:
3031: 3032: 3033: 3034: 3035: 3036: 3037: 3038: 3039: 3040: 3041: 3042: 3043:
3044: public static function STDEVA() {
3045: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3046:
3047:
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)) &&
3055: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3056: } else {
3057:
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:
3075: if (($aCount > 0) && ($returnValue >= 0)) {
3076: return sqrt($returnValue / $aCount);
3077: }
3078: }
3079: return PHPExcel_Calculation_Functions::DIV0();
3080: }
3081:
3082:
3083: 3084: 3085: 3086: 3087: 3088: 3089: 3090: 3091: 3092: 3093: 3094: 3095:
3096: public static function STDEVP() {
3097: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3098:
3099:
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)) &&
3107: ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
3108: $arg = (integer) $arg;
3109: }
3110:
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:
3122: if (($aCount > 0) && ($returnValue >= 0)) {
3123: return sqrt($returnValue / $aCount);
3124: }
3125: }
3126: return PHPExcel_Calculation_Functions::DIV0();
3127: }
3128:
3129:
3130: 3131: 3132: 3133: 3134: 3135: 3136: 3137: 3138: 3139: 3140: 3141: 3142:
3143: public static function STDEVPA() {
3144: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3145:
3146:
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)) &&
3154: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3155: } else {
3156:
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:
3174: if (($aCount > 0) && ($returnValue >= 0)) {
3175: return sqrt($returnValue / $aCount);
3176: }
3177: }
3178: return PHPExcel_Calculation_Functions::DIV0();
3179: }
3180:
3181:
3182: 3183: 3184: 3185: 3186: 3187: 3188: 3189: 3190:
3191: public static function STEYX($yValues,$xValues) {
3192: if (!self::_checkTrendArrays($yValues,$xValues)) {
3193: return PHPExcel_Calculation_Functions::VALUE();
3194: }
3195: $yValueCount = count($yValues);
3196: $xValueCount = count($xValues);
3197:
3198: if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
3199: return PHPExcel_Calculation_Functions::NA();
3200: } elseif ($yValueCount == 1) {
3201: return PHPExcel_Calculation_Functions::DIV0();
3202: }
3203:
3204: $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
3205: return $bestFitLinear->getStdevOfResiduals();
3206: }
3207:
3208:
3209: 3210: 3211: 3212: 3213: 3214: 3215: 3216: 3217: 3218:
3219: public static function TDIST($value, $degrees, $tails) {
3220: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
3221: $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
3222: $tails = floor(PHPExcel_Calculation_Functions::flattenSingleValue($tails));
3223:
3224: if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
3225: if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
3226: return PHPExcel_Calculation_Functions::NaN();
3227: }
3228:
3229:
3230:
3231:
3232:
3233:
3234:
3235:
3236:
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: }
3266: return PHPExcel_Calculation_Functions::VALUE();
3267: }
3268:
3269:
3270: 3271: 3272: 3273: 3274: 3275: 3276: 3277: 3278:
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:
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:
3303: if ($result != 0.0) {
3304: $dx = $error / $result;
3305: $xNew = $x - $dx;
3306: }
3307:
3308:
3309:
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) {
3317: return PHPExcel_Calculation_Functions::NA();
3318: }
3319: return round($x,12);
3320: }
3321: return PHPExcel_Calculation_Functions::VALUE();
3322: }
3323:
3324:
3325: 3326: 3327: 3328: 3329: 3330: 3331: 3332: 3333: 3334: 3335:
3336: public static function TREND($yValues,$xValues=array(),$newValues=array(),$const=True) {
3337: $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
3338: $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
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: }
3354:
3355:
3356: 3357: 3358: 3359: 3360: 3361: 3362: 3363: 3364: 3365: 3366: 3367: 3368: 3369: 3370: 3371:
3372: public static function TRIMMEAN() {
3373: $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
3374:
3375:
3376: $percent = array_pop($aArgs);
3377:
3378: if ((is_numeric($percent)) && (!is_string($percent))) {
3379: if (($percent < 0) || ($percent > 1)) {
3380: return PHPExcel_Calculation_Functions::NaN();
3381: }
3382: $mArgs = array();
3383: foreach ($aArgs as $arg) {
3384:
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: }
3397: return PHPExcel_Calculation_Functions::VALUE();
3398: }
3399:
3400:
3401: 3402: 3403: 3404: 3405: 3406: 3407: 3408: 3409: 3410: 3411: 3412: 3413:
3414: public static function VARFunc() {
3415:
3416: $returnValue = PHPExcel_Calculation_Functions::DIV0();
3417:
3418: $summerA = $summerB = 0;
3419:
3420:
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:
3426: if ((is_numeric($arg)) && (!is_string($arg))) {
3427: $summerA += ($arg * $arg);
3428: $summerB += $arg;
3429: ++$aCount;
3430: }
3431: }
3432:
3433:
3434: if ($aCount > 1) {
3435: $summerA *= $aCount;
3436: $summerB *= $summerB;
3437: $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3438: }
3439: return $returnValue;
3440: }
3441:
3442:
3443: 3444: 3445: 3446: 3447: 3448: 3449: 3450: 3451: 3452: 3453: 3454: 3455:
3456: public static function VARA() {
3457:
3458: $returnValue = PHPExcel_Calculation_Functions::DIV0();
3459:
3460: $summerA = $summerB = 0;
3461:
3462:
3463: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3464: $aCount = 0;
3465: foreach ($aArgs as $k => $arg) {
3466: if ((is_string($arg)) &&
3467: (PHPExcel_Calculation_Functions::isValue($k))) {
3468: return PHPExcel_Calculation_Functions::VALUE();
3469: } elseif ((is_string($arg)) &&
3470: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3471: } else {
3472:
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:
3487: if ($aCount > 1) {
3488: $summerA *= $aCount;
3489: $summerB *= $summerB;
3490: $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3491: }
3492: return $returnValue;
3493: }
3494:
3495:
3496: 3497: 3498: 3499: 3500: 3501: 3502: 3503: 3504: 3505: 3506: 3507: 3508:
3509: public static function VARP() {
3510:
3511: $returnValue = PHPExcel_Calculation_Functions::DIV0();
3512:
3513: $summerA = $summerB = 0;
3514:
3515:
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:
3521: if ((is_numeric($arg)) && (!is_string($arg))) {
3522: $summerA += ($arg * $arg);
3523: $summerB += $arg;
3524: ++$aCount;
3525: }
3526: }
3527:
3528:
3529: if ($aCount > 0) {
3530: $summerA *= $aCount;
3531: $summerB *= $summerB;
3532: $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3533: }
3534: return $returnValue;
3535: }
3536:
3537:
3538: 3539: 3540: 3541: 3542: 3543: 3544: 3545: 3546: 3547: 3548: 3549: 3550:
3551: public static function VARPA() {
3552:
3553: $returnValue = PHPExcel_Calculation_Functions::DIV0();
3554:
3555: $summerA = $summerB = 0;
3556:
3557:
3558: $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
3559: $aCount = 0;
3560: foreach ($aArgs as $k => $arg) {
3561: if ((is_string($arg)) &&
3562: (PHPExcel_Calculation_Functions::isValue($k))) {
3563: return PHPExcel_Calculation_Functions::VALUE();
3564: } elseif ((is_string($arg)) &&
3565: (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
3566: } else {
3567:
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:
3582: if ($aCount > 0) {
3583: $summerA *= $aCount;
3584: $summerB *= $summerB;
3585: $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3586: }
3587: return $returnValue;
3588: }
3589:
3590:
3591: 3592: 3593: 3594: 3595: 3596: 3597: 3598: 3599: 3600: 3601: 3602: 3603:
3604: public static function WEIBULL($value, $alpha, $beta, $cumulative) {
3605: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
3606: $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
3607: $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
3608:
3609: if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
3610: if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
3611: return PHPExcel_Calculation_Functions::NaN();
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: }
3621: return PHPExcel_Calculation_Functions::VALUE();
3622: }
3623:
3624:
3625: 3626: 3627: 3628: 3629: 3630: 3631: 3632: 3633: 3634: 3635: 3636: 3637:
3638: public static function ZTEST($dataSet, $m0, $sigma = NULL) {
3639: $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet);
3640: $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0);
3641: $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma);
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: }
3650:
3651: }
3652: