1<?php 2/** 3 * PHPExcel 4 * 5 * Copyright (c) 2006 - 2014 PHPExcel 6 * 7 * This library is free software; you can redistribute it and/or 8 * modify it under the terms of the GNU Lesser General Public 9 * License as published by the Free Software Foundation; either 10 * version 2.1 of the License, or (at your option) any later version. 11 * 12 * This library is distributed in the hope that it will be useful, 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 15 * Lesser General Public License for more details. 16 * 17 * You should have received a copy of the GNU Lesser General Public 18 * License along with this library; if not, write to the Free Software 19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 20 * 21 * @category PHPExcel 22 * @package PHPExcel_Calculation 23 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) 24 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL 25 * @version ##VERSION##, ##DATE## 26 */ 27 28 29/** PHPExcel root directory */ 30if (!defined('PHPEXCEL_ROOT')) { 31 /** 32 * @ignore 33 */ 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 42/** LOG_GAMMA_X_MAX_VALUE */ 43define('LOG_GAMMA_X_MAX_VALUE', 2.55e305); 44 45/** XMININ */ 46define('XMININ', 2.23e-308); 47 48/** EPS */ 49define('EPS', 2.22e-16); 50 51/** SQRT2PI */ 52define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099); 53 54 55/** 56 * PHPExcel_Calculation_Statistical 57 * 58 * @category PHPExcel 59 * @package PHPExcel_Calculation 60 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) 61 */ 62class PHPExcel_Calculation_Statistical { 63 64 65 private static function _checkTrendArrays(&$array1,&$array2) { 66 if (!is_array($array1)) { $array1 = array($array1); } 67 if (!is_array($array2)) { $array2 = array($array2); } 68 69 $array1 = PHPExcel_Calculation_Functions::flattenArray($array1); 70 $array2 = PHPExcel_Calculation_Functions::flattenArray($array2); 71 foreach($array1 as $key => $value) { 72 if ((is_bool($value)) || (is_string($value)) || (is_null($value))) { 73 unset($array1[$key]); 74 unset($array2[$key]); 75 } 76 } 77 foreach($array2 as $key => $value) { 78 if ((is_bool($value)) || (is_string($value)) || (is_null($value))) { 79 unset($array1[$key]); 80 unset($array2[$key]); 81 } 82 } 83 $array1 = array_merge($array1); 84 $array2 = array_merge($array2); 85 86 return True; 87 } // function _checkTrendArrays() 88 89 90 /** 91 * Beta function. 92 * 93 * @author Jaco van Kooten 94 * 95 * @param p require p>0 96 * @param q require q>0 97 * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow 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 } // function _beta() 106 107 108 /** 109 * Incomplete beta function 110 * 111 * @author Jaco van Kooten 112 * @author Paul Meagher 113 * 114 * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992). 115 * @param x require 0<=x<=1 116 * @param p require p>0 117 * @param q require q>0 118 * @return 0 if x<0, p<=0, q<=0 or p+q>2.55E305 and 1 if x>1 to avoid errors and over/underflow 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 } // 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 142 /** 143 * The natural logarithm of the beta function. 144 * 145 * @param p require p>0 146 * @param q require q>0 147 * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow 148 * @author Jaco van Kooten 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 } // function _logBeta() 162 163 164 /** 165 * Evaluates of continued fraction part of incomplete beta function. 166 * Based on an idea from Numerical Recipes (W.H. Press et al, 1992). 167 * @author Jaco van Kooten 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 // 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 215 /** 216 * logGamma function 217 * 218 * @version 1.1 219 * @author Jaco van Kooten 220 * 221 * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher. 222 * 223 * The natural logarithm of the gamma function. <br /> 224 * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br /> 225 * Applied Mathematics Division <br /> 226 * Argonne National Laboratory <br /> 227 * Argonne, IL 60439 <br /> 228 * <p> 229 * References: 230 * <ol> 231 * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural 232 * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li> 233 * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li> 234 * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li> 235 * </ol> 236 * </p> 237 * <p> 238 * From the original documentation: 239 * </p> 240 * <p> 241 * This routine calculates the LOG(GAMMA) function for a positive real argument X. 242 * Computation is based on an algorithm outlined in references 1 and 2. 243 * The program uses rational functions that theoretically approximate LOG(GAMMA) 244 * to at least 18 significant decimal digits. The approximation for X > 12 is from 245 * reference 3, while approximations for X < 12.0 are similar to those in reference 246 * 1, but are unpublished. The accuracy achieved depends on the arithmetic system, 247 * the compiler, the intrinsic functions, and proper selection of the 248 * machine-dependent constants. 249 * </p> 250 * <p> 251 * Error returns: <br /> 252 * The program returns the value XINF for X .LE. 0.0 or when overflow would occur. 253 * The computation is believed to be free of underflow and overflow. 254 * </p> 255 * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305 256 */ 257 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) { 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 // 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 539 return PHPExcel_Calculation_Functions::NULL(); 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 686 /** 687 * AVEDEV 688 * 689 * Returns the average of the absolute deviations of data points from their mean. 690 * AVEDEV is a measure of the variability in a data set. 691 * 692 * Excel Function: 693 * AVEDEV(value1[,value2[, ...]]) 694 * 695 * @access public 696 * @category Statistical Functions 697 * @param mixed $arg,... Data values 698 * @return float 699 */ 700 public static function AVEDEV() { 701 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 711 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 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) { 727 return PHPExcel_Calculation_Functions::DIV0(); 728 } 729 return $returnValue / $aCount; 730 } 731 return PHPExcel_Calculation_Functions::NaN(); 732 } // function AVEDEV() 733 734 735 /** 736 * AVERAGE 737 * 738 * Returns the average (arithmetic mean) of the arguments 739 * 740 * Excel Function: 741 * AVERAGE(value1[,value2[, ...]]) 742 * 743 * @access public 744 * @category Statistical Functions 745 * @param mixed $arg,... Data values 746 * @return float 747 */ 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)) && 754 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 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 { 772 return PHPExcel_Calculation_Functions::DIV0(); 773 } 774 } // function AVERAGE() 775 776 777 /** 778 * AVERAGEA 779 * 780 * Returns the average of its arguments, including numbers, text, and logical values 781 * 782 * Excel Function: 783 * AVERAGEA(value1[,value2[, ...]]) 784 * 785 * @access public 786 * @category Statistical Functions 787 * @param mixed $arg,... Data values 788 * @return float 789 */ 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)) && 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 // Return 817 if ($aCount > 0) { 818 return $returnValue / $aCount; 819 } else { 820 return PHPExcel_Calculation_Functions::DIV0(); 821 } 822 } // function AVERAGEA() 823 824 825 /** 826 * AVERAGEIF 827 * 828 * Returns the average value from a range of cells that contain numbers within the list of arguments 829 * 830 * Excel Function: 831 * AVERAGEIF(value1[,value2[, ...]],condition) 832 * 833 * @access public 834 * @category Mathematical and Trigonometric Functions 835 * @param mixed $arg,... Data values 836 * @param string $condition The criteria that defines which cells will be checked. 837 * @param mixed[] $averageArgs Data values 838 * @return float 839 */ 840 public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) { 841 // Return value 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 // 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 { 867 return PHPExcel_Calculation_Functions::DIV0(); 868 } 869 } // function AVERAGEIF() 870 871 872 /** 873 * BETADIST 874 * 875 * Returns the beta distribution. 876 * 877 * @param float $value Value at which you want to evaluate the distribution 878 * @param float $alpha Parameter to the distribution 879 * @param float $beta Parameter to the distribution 880 * @param boolean $cumulative 881 * @return float 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 } // function BETADIST() 906 907 908 /** 909 * BETAINV 910 * 911 * Returns the inverse of the beta distribution. 912 * 913 * @param float $probability Probability at which you want to evaluate the distribution 914 * @param float $alpha Parameter to the distribution 915 * @param float $beta Parameter to the distribution 916 * @param float $rMin Minimum value 917 * @param float $rMax Maximum value 918 * @param boolean $cumulative 919 * @return float 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 } // function BETAINV() 960 961 962 /** 963 * BINOMDIST 964 * 965 * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with 966 * a fixed number of tests or trials, when the outcomes of any trial are only success or failure, 967 * when trials are independent, and when the probability of success is constant throughout the 968 * experiment. For example, BINOMDIST can calculate the probability that two of the next three 969 * babies born are male. 970 * 971 * @param float $value Number of successes in trials 972 * @param float $trials Number of trials 973 * @param float $probability Probability of success on each trial 974 * @param boolean $cumulative 975 * @return float 976 * 977 * @todo Cumulative distribution function 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 } // function BINOMDIST() 1006 1007 1008 /** 1009 * CHIDIST 1010 * 1011 * Returns the one-tailed probability of the chi-squared distribution. 1012 * 1013 * @param float $value Value for the function 1014 * @param float $degrees degrees of freedom 1015 * @return float 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 } // function CHIDIST() 1035 1036 1037 /** 1038 * CHIINV 1039 * 1040 * Returns the one-tailed probability of the chi-squared distribution. 1041 * 1042 * @param float $probability Probability for the function 1043 * @param float $degrees degrees of freedom 1044 * @return float 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 // 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) { 1085 return PHPExcel_Calculation_Functions::NA(); 1086 } 1087 return round($x,12); 1088 } 1089 return PHPExcel_Calculation_Functions::VALUE(); 1090 } // function CHIINV() 1091 1092 1093 /** 1094 * CONFIDENCE 1095 * 1096 * Returns the confidence interval for a population mean 1097 * 1098 * @param float $alpha 1099 * @param float $stdDev Standard Deviation 1100 * @param float $size 1101 * @return float 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 } // function CONFIDENCE() 1120 1121 1122 /** 1123 * CORREL 1124 * 1125 * Returns covariance, the average of the products of deviations for each data point pair. 1126 * 1127 * @param array of mixed Data Series Y 1128 * @param array of mixed Data Series X 1129 * @return float 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 } // function CORREL() 1150 1151 1152 /** 1153 * COUNT 1154 * 1155 * Counts the number of cells that contain numbers within the list of arguments 1156 * 1157 * Excel Function: 1158 * COUNT(value1[,value2[, ...]]) 1159 * 1160 * @access public 1161 * @category Statistical Functions 1162 * @param mixed $arg,... Data values 1163 * @return int 1164 */ 1165 public static function COUNT() { 1166 // Return value 1167 $returnValue = 0; 1168 1169 // Loop through arguments 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 // 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 1187 /** 1188 * COUNTA 1189 * 1190 * Counts the number of cells that are not empty within the list of arguments 1191 * 1192 * Excel Function: 1193 * COUNTA(value1[,value2[, ...]]) 1194 * 1195 * @access public 1196 * @category Statistical Functions 1197 * @param mixed $arg,... Data values 1198 * @return int 1199 */ 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 1218 /** 1219 * COUNTBLANK 1220 * 1221 * Counts the number of empty cells within the list of arguments 1222 * 1223 * Excel Function: 1224 * COUNTBLANK(value1[,value2[, ...]]) 1225 * 1226 * @access public 1227 * @category Statistical Functions 1228 * @param mixed $arg,... Data values 1229 * @return int 1230 */ 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 1249 /** 1250 * COUNTIF 1251 * 1252 * Counts the number of cells that contain numbers within the list of arguments 1253 * 1254 * Excel Function: 1255 * COUNTIF(value1[,value2[, ...]],condition) 1256 * 1257 * @access public 1258 * @category Statistical Functions 1259 * @param mixed $arg,... Data values 1260 * @param string $condition The criteria that defines which cells will be counted. 1261 * @return int 1262 */ 1263 public static function COUNTIF($aArgs,$condition) { 1264 // Return value 1265 $returnValue = 0; 1266 1267 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 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 1284 /** 1285 * COVAR 1286 * 1287 * Returns covariance, the average of the products of deviations for each data point pair. 1288 * 1289 * @param array of mixed Data Series Y 1290 * @param array of mixed Data Series X 1291 * @return float 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 } // function COVAR() 1309 1310 1311 /** 1312 * CRITBINOM 1313 * 1314 * Returns the smallest value for which the cumulative binomial distribution is greater 1315 * than or equal to a criterion value 1316 * 1317 * See http://support.microsoft.com/kb/828117/ for details of the algorithm used 1318 * 1319 * @param float $trials number of Bernoulli trials 1320 * @param float $probability probability of a success on each trial 1321 * @param float $alpha criterion value 1322 * @return int 1323 * 1324 * @todo Warning. This implementation differs from the algorithm detailed on the MS 1325 * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess 1326 * This eliminates a potential endless loop error, but may have an adverse affect on the 1327 * accuracy of the function (although all my tests have so far returned correct results). 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// $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 } 1417 return PHPExcel_Calculation_Functions::VALUE(); 1418 } // function CRITBINOM() 1419 1420 1421 /** 1422 * DEVSQ 1423 * 1424 * Returns the sum of squares of deviations of data points from their sample mean. 1425 * 1426 * Excel Function: 1427 * DEVSQ(value1[,value2[, ...]]) 1428 * 1429 * @access public 1430 * @category Statistical Functions 1431 * @param mixed $arg,... Data values 1432 * @return float 1433 */ 1434 public static function DEVSQ() { 1435 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 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 // Return 1460 if (is_null($returnValue)) { 1461 return PHPExcel_Calculation_Functions::NaN(); 1462 } else { 1463 return $returnValue; 1464 } 1465 } 1466 return self::NA(); 1467 } // function DEVSQ() 1468 1469 1470 /** 1471 * EXPONDIST 1472 * 1473 * Returns the exponential distribution. Use EXPONDIST to model the time between events, 1474 * such as how long an automated bank teller takes to deliver cash. For example, you can 1475 * use EXPONDIST to determine the probability that the process takes at most 1 minute. 1476 * 1477 * @param float $value Value of the function 1478 * @param float $lambda The parameter value 1479 * @param boolean $cumulative 1480 * @return float 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 } // function EXPONDIST() 1501 1502 1503 /** 1504 * FISHER 1505 * 1506 * Returns the Fisher transformation at x. This transformation produces a function that 1507 * is normally distributed rather than skewed. Use this function to perform hypothesis 1508 * testing on the correlation coefficient. 1509 * 1510 * @param float $value 1511 * @return float 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 } // function FISHER() 1524 1525 1526 /** 1527 * FISHERINV 1528 * 1529 * Returns the inverse of the Fisher transformation. Use this transformation when 1530 * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then 1531 * FISHERINV(y) = x. 1532 * 1533 * @param float $value 1534 * @return float 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 } // function FISHERINV() 1544 1545 1546 /** 1547 * FORECAST 1548 * 1549 * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. 1550 * 1551 * @param float Value of X for which we want to find Y 1552 * @param array of mixed Data Series Y 1553 * @param array of mixed Data Series X 1554 * @return float 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 } // function FORECAST() 1577 1578 1579 /** 1580 * GAMMADIST 1581 * 1582 * Returns the gamma distribution. 1583 * 1584 * @param float $value Value at which you want to evaluate the distribution 1585 * @param float $a Parameter to the distribution 1586 * @param float $b Parameter to the distribution 1587 * @param boolean $cumulative 1588 * @return float 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 } // function GAMMADIST() 1610 1611 1612 /** 1613 * GAMMAINV 1614 * 1615 * Returns the inverse of the beta distribution. 1616 * 1617 * @param float $probability Probability at which you want to evaluate the distribution 1618 * @param float $alpha Parameter to the distribution 1619 * @param float $beta Parameter to the distribution 1620 * @return float 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 // 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) { 1665 return PHPExcel_Calculation_Functions::NA(); 1666 } 1667 return $x; 1668 } 1669 return PHPExcel_Calculation_Functions::VALUE(); 1670 } // function GAMMAINV() 1671 1672 1673 /** 1674 * GAMMALN 1675 * 1676 * Returns the natural logarithm of the gamma function. 1677 * 1678 * @param float $value 1679 * @return float 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 } // function GAMMALN() 1692 1693 1694 /** 1695 * GEOMEAN 1696 * 1697 * Returns the geometric mean of an array or range of positive data. For example, you 1698 * can use GEOMEAN to calculate average growth rate given compound interest with 1699 * variable rates. 1700 * 1701 * Excel Function: 1702 * GEOMEAN(value1[,value2[, ...]]) 1703 * 1704 * @access public 1705 * @category Statistical Functions 1706 * @param mixed $arg,... Data values 1707 * @return float 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 } // GEOMEAN() 1721 1722 1723 /** 1724 * GROWTH 1725 * 1726 * Returns values along a predicted emponential trend 1727 * 1728 * @param array of mixed Data Series Y 1729 * @param array of mixed Data Series X 1730 * @param array of mixed Values of X for which we want to find Y 1731 * @param boolean A logical value specifying whether to force the intersect to equal 0. 1732 * @return array of float 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 } // function GROWTH() 1752 1753 1754 /** 1755 * HARMEAN 1756 * 1757 * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the 1758 * arithmetic mean of reciprocals. 1759 * 1760 * Excel Function: 1761 * HARMEAN(value1[,value2[, ...]]) 1762 * 1763 * @access public 1764 * @category Statistical Functions 1765 * @param mixed $arg,... Data values 1766 * @return float 1767 */ 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) { 1775 return PHPExcel_Calculation_Functions::NaN(); 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) { 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 // Return 1794 if ($aCount > 0) { 1795 return 1 / ($returnValue / $aCount); 1796 } else { 1797 return $returnValue; 1798 } 1799 } // function HARMEAN() 1800 1801 1802 /** 1803 * HYPGEOMDIST 1804 * 1805 * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of 1806 * sample successes, given the sample size, population successes, and population size. 1807 * 1808 * @param float $sampleSuccesses Number of successes in the sample 1809 * @param float $sampleNumber Size of the sample 1810 * @param float $populationSuccesses Number of successes in the population 1811 * @param float $populationNumber Population size 1812 * @return float 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 } // function HYPGEOMDIST() 1837 1838 1839 /** 1840 * INTERCEPT 1841 * 1842 * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. 1843 * 1844 * @param array of mixed Data Series Y 1845 * @param array of mixed Data Series X 1846 * @return float 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 } // function INTERCEPT() 1864 1865 1866 /** 1867 * KURT 1868 * 1869 * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness 1870 * or flatness of a distribution compared with the normal distribution. Positive 1871 * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a 1872 * relatively flat distribution. 1873 * 1874 * @param array Data Series 1875 * @return float 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 // Loop through arguments 1885 foreach ($aArgs as $k => $arg) { 1886 if ((is_bool($arg)) && 1887 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 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 } 1902 return PHPExcel_Calculation_Functions::DIV0(); 1903 } // function KURT() 1904 1905 1906 /** 1907 * LARGE 1908 * 1909 * Returns the nth largest value in a data set. You can use this function to 1910 * select a value based on its relative standing. 1911 * 1912 * Excel Function: 1913 * LARGE(value1[,value2[, ...]],entry) 1914 * 1915 * @access public 1916 * @category Statistical Functions 1917 * @param mixed $arg,... Data values 1918 * @param int $entry Position (ordered from the largest) in the array or range of data to return 1919 * @return float 1920 * 1921 */ 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)) { 1939 return PHPExcel_Calculation_Functions::NaN(); 1940 } 1941 rsort($mArgs); 1942 return $mArgs[$entry]; 1943 } 1944 return PHPExcel_Calculation_Functions::VALUE(); 1945 } // function LARGE() 1946 1947 1948 /** 1949 * LINEST 1950 * 1951 * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, 1952 * and then returns an array that describes the line. 1953 * 1954 * @param array of mixed Data Series Y 1955 * @param array of mixed Data Series X 1956 * @param boolean A logical value specifying whether to force the intersect to equal 0. 1957 * @param boolean A logical value specifying whether to return additional regression statistics. 1958 * @return array 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 } // function LINEST() 1999 2000 2001 /** 2002 * LOGEST 2003 * 2004 * Calculates an exponential curve that best fits the X and Y data series, 2005 * and then returns an array that describes the line. 2006 * 2007 * @param array of mixed Data Series Y 2008 * @param array of mixed Data Series X 2009 * @param boolean A logical value specifying whether to force the intersect to equal 0. 2010 * @param boolean A logical value specifying whether to return additional regression statistics. 2011 * @return array 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 } // function LOGEST() 2058 2059 2060 /** 2061 * LOGINV 2062 * 2063 * Returns the inverse of the normal cumulative distribution 2064 * 2065 * @param float $probability 2066 * @param float $mean 2067 * @param float $stdDev 2068 * @return float 2069 * 2070 * @todo Try implementing P J Acklam's refinement algorithm for greater 2071 * accuracy if I can get my head round the mathematics 2072 * (as described at) http://home.online.no/~pjacklam/notes/invnorm/ 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 } // function LOGINV() 2087 2088 2089 /** 2090 * LOGNORMDIST 2091 * 2092 * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed 2093 * with parameters mean and standard_dev. 2094 * 2095 * @param float $value 2096 * @param float $mean 2097 * @param float $stdDev 2098 * @return float 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 } // function LOGNORMDIST() 2113 2114 2115 /** 2116 * MAX 2117 * 2118 * MAX returns the value of the element of the values passed that has the highest value, 2119 * with negative numbers considered smaller than positive numbers. 2120 * 2121 * Excel Function: 2122 * MAX(value1[,value2[, ...]]) 2123 * 2124 * @access public 2125 * @category Statistical Functions 2126 * @param mixed $arg,... Data values 2127 * @return float 2128 */ 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 2152 /** 2153 * MAXA 2154 * 2155 * Returns the greatest value in a list of arguments, including numbers, text, and logical values 2156 * 2157 * Excel Function: 2158 * MAXA(value1[,value2[, ...]]) 2159 * 2160 * @access public 2161 * @category Statistical Functions 2162 * @param mixed $arg,... Data values 2163 * @return float 2164 */ 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 2193 /** 2194 * MAXIF 2195 * 2196 * Counts the maximum value within a range of cells that contain numbers within the list of arguments 2197 * 2198 * Excel Function: 2199 * MAXIF(value1[,value2[, ...]],condition) 2200 * 2201 * @access public 2202 * @category Mathematical and Trigonometric Functions 2203 * @param mixed $arg,... Data values 2204 * @param string $condition The criteria that defines which cells will be checked. 2205 * @return float 2206 */ 2207 public static function MAXIF($aArgs,$condition,$sumArgs = array()) { 2208 // Return value 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 // 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 2233 /** 2234 * MEDIAN 2235 * 2236 * Returns the median of the given numbers. The median is the number in the middle of a set of numbers. 2237 * 2238 * Excel Function: 2239 * MEDIAN(value1[,value2[, ...]]) 2240 * 2241 * @access public 2242 * @category Statistical Functions 2243 * @param mixed $arg,... Data values 2244 * @return float 2245 */ 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 2277 /** 2278 * MIN 2279 * 2280 * MIN returns the value of the element of the values passed that has the smallest value, 2281 * with negative numbers considered smaller than positive numbers. 2282 * 2283 * Excel Function: 2284 * MIN(value1[,value2[, ...]]) 2285 * 2286 * @access public 2287 * @category Statistical Functions 2288 * @param mixed $arg,... Data values 2289 * @return float 2290 */ 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 2314 /** 2315 * MINA 2316 * 2317 * Returns the smallest value in a list of arguments, including numbers, text, and logical values 2318 * 2319 * Excel Function: 2320 * MINA(value1[,value2[, ...]]) 2321 * 2322 * @access public 2323 * @category Statistical Functions 2324 * @param mixed $arg,... Data values 2325 * @return float 2326 */ 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 2355 /** 2356 * MINIF 2357 * 2358 * Returns the minimum value within a range of cells that contain numbers within the list of arguments 2359 * 2360 * Excel Function: 2361 * MINIF(value1[,value2[, ...]],condition) 2362 * 2363 * @access public 2364 * @category Mathematical and Trigonometric Functions 2365 * @param mixed $arg,... Data values 2366 * @param string $condition The criteria that defines which cells will be checked. 2367 * @return float 2368 */ 2369 public static function MINIF($aArgs,$condition,$sumArgs = array()) { 2370 // Return value 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 // 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) { 2423 return PHPExcel_Calculation_Functions::NA(); 2424 } 2425 return $frequencyArray[0]['value']; 2426 } // function _modeCalc() 2427 2428 2429 /** 2430 * MODE 2431 * 2432 * Returns the most frequently occurring, or repetitive, value in an array or range of data 2433 * 2434 * Excel Function: 2435 * MODE(value1[,value2[, ...]]) 2436 * 2437 * @access public 2438 * @category Statistical Functions 2439 * @param mixed $arg,... Data values 2440 * @return float 2441 */ 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 2466 /** 2467 * NEGBINOMDIST 2468 * 2469 * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that 2470 * there will be number_f failures before the number_s-th success, when the constant 2471 * probability of a success is probability_s. This function is similar to the binomial 2472 * distribution, except that the number of successes is fixed, and the number of trials is 2473 * variable. Like the binomial, trials are assumed to be independent. 2474 * 2475 * @param float $failures Number of Failures 2476 * @param float $successes Threshold number of Successes 2477 * @param float $probability Probability of success on each trial 2478 * @return float 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 } // function NEGBINOMDIST() 2502 2503 2504 /** 2505 * NORMDIST 2506 * 2507 * Returns the normal distribution for the specified mean and standard deviation. This 2508 * function has a very wide range of applications in statistics, including hypothesis 2509 * testing. 2510 * 2511 * @param float $value 2512 * @param float $mean Mean Value 2513 * @param float $stdDev Standard Deviation 2514 * @param boolean $cumulative 2515 * @return float 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 } // function NORMDIST() 2537 2538 2539 /** 2540 * NORMINV 2541 * 2542 * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. 2543 * 2544 * @param float $value 2545 * @param float $mean Mean Value 2546 * @param float $stdDev Standard Deviation 2547 * @return float 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 } // function NORMINV() 2566 2567 2568 /** 2569 * NORMSDIST 2570 * 2571 * Returns the standard normal cumulative distribution function. The distribution has 2572 * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a 2573 * table of standard normal curve areas. 2574 * 2575 * @param float $value 2576 * @return float 2577 */ 2578 public static function NORMSDIST($value) { 2579 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2580 2581 return self::NORMDIST($value, 0, 1, True); 2582 } // function NORMSDIST() 2583 2584 2585 /** 2586 * NORMSINV 2587 * 2588 * Returns the inverse of the standard normal cumulative distribution 2589 * 2590 * @param float $value 2591 * @return float 2592 */ 2593 public static function NORMSINV($value) { 2594 return self::NORMINV($value, 0, 1); 2595 } // function NORMSINV() 2596 2597 2598 /** 2599 * PERCENTILE 2600 * 2601 * Returns the nth percentile of values in a range.. 2602 * 2603 * Excel Function: 2604 * PERCENTILE(value1[,value2[, ...]],entry) 2605 * 2606 * @access public 2607 * @category Statistical Functions 2608 * @param mixed $arg,... Data values 2609 * @param float $entry Percentile value in the range 0..1, inclusive. 2610 * @return float 2611 */ 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)) { 2620 return PHPExcel_Calculation_Functions::NaN(); 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 } 2644 return PHPExcel_Calculation_Functions::VALUE(); 2645 } // function PERCENTILE() 2646 2647 2648 /** 2649 * PERCENTRANK 2650 * 2651 * Returns the rank of a value in a data set as a percentage of the data set. 2652 * 2653 * @param array of number An array of, or a reference to, a list of numbers. 2654 * @param number The number whose rank you want to find. 2655 * @param number The number of significant digits for the returned percentage value. 2656 * @return float 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 } // function PERCENTRANK() 2692 2693 2694 /** 2695 * PERMUT 2696 * 2697 * Returns the number of permutations for a given number of objects that can be 2698 * selected from number objects. A permutation is any set or subset of objects or 2699 * events where internal order is significant. Permutations are different from 2700 * combinations, for which the internal order is not significant. Use this function 2701 * for lottery-style probability calculations. 2702 * 2703 * @param int $numObjs Number of different objects 2704 * @param int $numInSet Number of objects in each permutation 2705 * @return int Number of permutations 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 } // function PERMUT() 2720 2721 2722 /** 2723 * POISSON 2724 * 2725 * Returns the Poisson distribution. A common application of the Poisson distribution 2726 * is predicting the number of events over a specific time, such as the number of 2727 * cars arriving at a toll plaza in 1 minute. 2728 * 2729 * @param float $value 2730 * @param float $mean Mean Value 2731 * @param boolean $cumulative 2732 * @return float 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 } // function POISSON() 2757 2758 2759 /** 2760 * QUARTILE 2761 * 2762 * Returns the quartile of a data set. 2763 * 2764 * Excel Function: 2765 * QUARTILE(value1[,value2[, ...]],entry) 2766 * 2767 * @access public 2768 * @category Statistical Functions 2769 * @param mixed $arg,... Data values 2770 * @param int $entry Quartile value in the range 1..3, inclusive. 2771 * @return float 2772 */ 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)) { 2782 return PHPExcel_Calculation_Functions::NaN(); 2783 } 2784 return self::PERCENTILE($aArgs,$entry); 2785 } 2786 return PHPExcel_Calculation_Functions::VALUE(); 2787 } // function QUARTILE() 2788 2789 2790 /** 2791 * RANK 2792 * 2793 * Returns the rank of a number in a list of numbers. 2794 * 2795 * @param number The number whose rank you want to find. 2796 * @param array of number An array of, or a reference to, a list of numbers. 2797 * @param mixed Order to sort the values in the value set 2798 * @return float 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 } // function RANK() 2823 2824 2825 /** 2826 * RSQ 2827 * 2828 * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. 2829 * 2830 * @param array of mixed Data Series Y 2831 * @param array of mixed Data Series X 2832 * @return float 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 } // function RSQ() 2850 2851 2852 /** 2853 * SKEW 2854 * 2855 * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry 2856 * of a distribution around its mean. Positive skewness indicates a distribution with an 2857 * asymmetric tail extending toward more positive values. Negative skewness indicates a 2858 * distribution with an asymmetric tail extending toward more negative values. 2859 * 2860 * @param array Data Series 2861 * @return float 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 // Loop through arguments 2870 foreach ($aArgs as $k => $arg) { 2871 if ((is_bool($arg)) && 2872 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 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 } 2886 return PHPExcel_Calculation_Functions::DIV0(); 2887 } // function SKEW() 2888 2889 2890 /** 2891 * SLOPE 2892 * 2893 * Returns the slope of the linear regression line through data points in known_y's and known_x's. 2894 * 2895 * @param array of mixed Data Series Y 2896 * @param array of mixed Data Series X 2897 * @return float 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 } // function SLOPE() 2915 2916 2917 /** 2918 * SMALL 2919 * 2920 * Returns the nth smallest value in a data set. You can use this function to 2921 * select a value based on its relative standing. 2922 * 2923 * Excel Function: 2924 * SMALL(value1[,value2[, ...]],entry) 2925 * 2926 * @access public 2927 * @category Statistical Functions 2928 * @param mixed $arg,... Data values 2929 * @param int $entry Position (ordered from the smallest) in the array or range of data to return 2930 * @return float 2931 */ 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)) { 2949 return PHPExcel_Calculation_Functions::NaN(); 2950 } 2951 sort($mArgs); 2952 return $mArgs[$entry]; 2953 } 2954 return PHPExcel_Calculation_Functions::VALUE(); 2955 } // function SMALL() 2956 2957 2958 /** 2959 * STANDARDIZE 2960 * 2961 * Returns a normalized value from a distribution characterized by mean and standard_dev. 2962 * 2963 * @param float $value Value to normalize 2964 * @param float $mean Mean Value 2965 * @param float $stdDev Standard Deviation 2966 * @return float Standardized value 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 } // function STANDARDIZE() 2981 2982 2983 /** 2984 * STDEV 2985 * 2986 * Estimates standard deviation based on a sample. The standard deviation is a measure of how 2987 * widely values are dispersed from the average value (the mean). 2988 * 2989 * Excel Function: 2990 * STDEV(value1[,value2[, ...]]) 2991 * 2992 * @access public 2993 * @category Statistical Functions 2994 * @param mixed $arg,... Data values 2995 * @return float 2996 */ 2997 public static function STDEV() { 2998 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 3008 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 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 } 3027 return PHPExcel_Calculation_Functions::DIV0(); 3028 } // function STDEV() 3029 3030 3031 /** 3032 * STDEVA 3033 * 3034 * Estimates standard deviation based on a sample, including numbers, text, and logical values 3035 * 3036 * Excel Function: 3037 * STDEVA(value1[,value2[, ...]]) 3038 * 3039 * @access public 3040 * @category Statistical Functions 3041 * @param mixed $arg,... Data values 3042 * @return float 3043 */ 3044 public static function STDEVA() { 3045 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 3055 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 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 } 3079 return PHPExcel_Calculation_Functions::DIV0(); 3080 } // function STDEVA() 3081 3082 3083 /** 3084 * STDEVP 3085 * 3086 * Calculates standard deviation based on the entire population 3087 * 3088 * Excel Function: 3089 * STDEVP(value1[,value2[, ...]]) 3090 * 3091 * @access public 3092 * @category Statistical Functions 3093 * @param mixed $arg,... Data values 3094 * @return float 3095 */ 3096 public static function STDEVP() { 3097 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 3107 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 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 } 3126 return PHPExcel_Calculation_Functions::DIV0(); 3127 } // function STDEVP() 3128 3129 3130 /** 3131 * STDEVPA 3132 * 3133 * Calculates standard deviation based on the entire population, including numbers, text, and logical values 3134 * 3135 * Excel Function: 3136 * STDEVPA(value1[,value2[, ...]]) 3137 * 3138 * @access public 3139 * @category Statistical Functions 3140 * @param mixed $arg,... Data values 3141 * @return float 3142 */ 3143 public static function STDEVPA() { 3144 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 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)) && 3154 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 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 } 3178 return PHPExcel_Calculation_Functions::DIV0(); 3179 } // function STDEVPA() 3180 3181 3182 /** 3183 * STEYX 3184 * 3185 * Returns the standard error of the predicted y-value for each x in the regression. 3186 * 3187 * @param array of mixed Data Series Y 3188 * @param array of mixed Data Series X 3189 * @return float 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 } // function STEYX() 3207 3208 3209 /** 3210 * TDIST 3211 * 3212 * Returns the probability of Student's T distribution. 3213 * 3214 * @param float $value Value for the function 3215 * @param float $degrees degrees of freedom 3216 * @param float $tails number of tails (1 or 2) 3217 * @return float 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 // 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 } 3266 return PHPExcel_Calculation_Functions::VALUE(); 3267 } // function TDIST() 3268 3269 3270 /** 3271 * TINV 3272 * 3273 * Returns the one-tailed probability of the chi-squared distribution. 3274 * 3275 * @param float $probability Probability for the function 3276 * @param float $degrees degrees of freedom 3277 * @return float 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 // 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) { 3317 return PHPExcel_Calculation_Functions::NA(); 3318 } 3319 return round($x,12); 3320 } 3321 return PHPExcel_Calculation_Functions::VALUE(); 3322 } // function TINV() 3323 3324 3325 /** 3326 * TREND 3327 * 3328 * Returns values along a linear trend 3329 * 3330 * @param array of mixed Data Series Y 3331 * @param array of mixed Data Series X 3332 * @param array of mixed Values of X for which we want to find Y 3333 * @param boolean A logical value specifying whether to force the intersect to equal 0. 3334 * @return array of float 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 } // function TREND() 3354 3355 3356 /** 3357 * TRIMMEAN 3358 * 3359 * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean 3360 * taken by excluding a percentage of data points from the top and bottom tails 3361 * of a data set. 3362 * 3363 * Excel Function: 3364 * TRIMEAN(value1[,value2[, ...]],$discard) 3365 * 3366 * @access public 3367 * @category Statistical Functions 3368 * @param mixed $arg,... Data values 3369 * @param float $discard Percentage to discard 3370 * @return float 3371 */ 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)) { 3380 return PHPExcel_Calculation_Functions::NaN(); 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 } 3397 return PHPExcel_Calculation_Functions::VALUE(); 3398 } // function TRIMMEAN() 3399 3400 3401 /** 3402 * VARFunc 3403 * 3404 * Estimates variance based on a sample. 3405 * 3406 * Excel Function: 3407 * VAR(value1[,value2[, ...]]) 3408 * 3409 * @access public 3410 * @category Statistical Functions 3411 * @param mixed $arg,... Data values 3412 * @return float 3413 */ 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 3443 /** 3444 * VARA 3445 * 3446 * Estimates variance based on a sample, including numbers, text, and logical values 3447 * 3448 * Excel Function: 3449 * VARA(value1[,value2[, ...]]) 3450 * 3451 * @access public 3452 * @category Statistical Functions 3453 * @param mixed $arg,... Data values 3454 * @return float 3455 */ 3456 public static function VARA() { 3457 // Return value 3458 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3459 3460 $summerA = $summerB = 0; 3461 3462 // Loop through arguments 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 // 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 3496 /** 3497 * VARP 3498 * 3499 * Calculates variance based on the entire population 3500 * 3501 * Excel Function: 3502 * VARP(value1[,value2[, ...]]) 3503 * 3504 * @access public 3505 * @category Statistical Functions 3506 * @param mixed $arg,... Data values 3507 * @return float 3508 */ 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 3538 /** 3539 * VARPA 3540 * 3541 * Calculates variance based on the entire population, including numbers, text, and logical values 3542 * 3543 * Excel Function: 3544 * VARPA(value1[,value2[, ...]]) 3545 * 3546 * @access public 3547 * @category Statistical Functions 3548 * @param mixed $arg,... Data values 3549 * @return float 3550 */ 3551 public static function VARPA() { 3552 // Return value 3553 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3554 3555 $summerA = $summerB = 0; 3556 3557 // Loop through arguments 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 // 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 3591 /** 3592 * WEIBULL 3593 * 3594 * Returns the Weibull distribution. Use this distribution in reliability 3595 * analysis, such as calculating a device's mean time to failure. 3596 * 3597 * @param float $value 3598 * @param float $alpha Alpha Parameter 3599 * @param float $beta Beta Parameter 3600 * @param boolean $cumulative 3601 * @return float 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 } // function WEIBULL() 3623 3624 3625 /** 3626 * ZTEST 3627 * 3628 * Returns the Weibull distribution. Use this distribution in reliability 3629 * analysis, such as calculating a device's mean time to failure. 3630 * 3631 * @param float $dataSet 3632 * @param float $m0 Alpha Parameter 3633 * @param float $sigma Beta Parameter 3634 * @param boolean $cumulative 3635 * @return float 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 } // function ZTEST() 3650 3651} // class PHPExcel_Calculation_Statistical 3652