| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
1 <?php 2 3 /** PHPExcel root directory */ 4 if (!defined('PHPEXCEL_ROOT')) { 5 /** 6 * @ignore 7 */ 8 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../'); 9 require (PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php'); 10 } 11 12 13 require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php'; 14 15 16 /** LOG_GAMMA_X_MAX_VALUE */ 17 define('LOG_GAMMA_X_MAX_VALUE', 2.55e305); 18 19 /** XMININ */ 20 define('XMININ', 2.23e-308); 21 22 /** EPS */ 23 define('EPS', 2.22e-16); 24 25 /** SQRT2PI */ 26 define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099); 27 28 /** 29 * PHPExcel_Calculation_Statistical 30 * 31 * Copyright (c) 2006 - 2015 PHPExcel 32 * 33 * This library is free software; you can redistribute it and/or 34 * modify it under the terms of the GNU Lesser General Public 35 * License as published by the Free Software Foundation; either 36 * version 2.1 of the License, or (at your option) any later version. 37 * 38 * This library is distributed in the hope that it will be useful, 39 * but WITHOUT ANY WARRANTY; without even the implied warranty of 40 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 41 * Lesser General Public License for more details. 42 * 43 * You should have received a copy of the GNU Lesser General Public 44 * License along with this library; if not, write to the Free Software 45 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 46 * 47 * @category PHPExcel 48 * @package PHPExcel_Calculation 49 * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) 50 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL 51 * @version ##VERSION##, ##DATE## 52 */ 53 class PHPExcel_Calculation_Statistical 54 { 55 private static function checkTrendArrays(&$array1, &$array2) 56 { 57 if (!is_array($array1)) { 58 $array1 = array($array1); 59 } 60 if (!is_array($array2)) { 61 $array2 = array($array2); 62 } 63 64 $array1 = PHPExcel_Calculation_Functions::flattenArray($array1); 65 $array2 = PHPExcel_Calculation_Functions::flattenArray($array2); 66 foreach ($array1 as $key => $value) { 67 if ((is_bool($value)) || (is_string($value)) || (is_null($value))) { 68 unset($array1[$key]); 69 unset($array2[$key]); 70 } 71 } 72 foreach ($array2 as $key => $value) { 73 if ((is_bool($value)) || (is_string($value)) || (is_null($value))) { 74 unset($array1[$key]); 75 unset($array2[$key]); 76 } 77 } 78 $array1 = array_merge($array1); 79 $array2 = array_merge($array2); 80 81 return true; 82 } 83 84 85 /** 86 * Beta function. 87 * 88 * @author Jaco van Kooten 89 * 90 * @param p require p>0 91 * @param q require q>0 92 * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow 93 */ 94 private static function beta($p, $q) 95 { 96 if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) { 97 return 0.0; 98 } else { 99 return exp(self::logBeta($p, $q)); 100 } 101 } 102 103 104 /** 105 * Incomplete beta function 106 * 107 * @author Jaco van Kooten 108 * @author Paul Meagher 109 * 110 * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992). 111 * @param x require 0<=x<=1 112 * @param p require p>0 113 * @param q require q>0 114 * @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 115 */ 116 private static function incompleteBeta($x, $p, $q) 117 { 118 if ($x <= 0.0) { 119 return 0.0; 120 } elseif ($x >= 1.0) { 121 return 1.0; 122 } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) { 123 return 0.0; 124 } 125 $beta_gam = exp((0 - self::logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x)); 126 if ($x < ($p + 1.0) / ($p + $q + 2.0)) { 127 return $beta_gam * self::betaFraction($x, $p, $q) / $p; 128 } else { 129 return 1.0 - ($beta_gam * self::betaFraction(1 - $x, $q, $p) / $q); 130 } 131 } 132 133 134 // Function cache for logBeta function 135 private static $logBetaCacheP = 0.0; 136 private static $logBetaCacheQ = 0.0; 137 private static $logBetaCacheResult = 0.0; 138 139 /** 140 * The natural logarithm of the beta function. 141 * 142 * @param p require p>0 143 * @param q require q>0 144 * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow 145 * @author Jaco van Kooten 146 */ 147 private static function logBeta($p, $q) 148 { 149 if ($p != self::$logBetaCacheP || $q != self::$logBetaCacheQ) { 150 self::$logBetaCacheP = $p; 151 self::$logBetaCacheQ = $q; 152 if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) { 153 self::$logBetaCacheResult = 0.0; 154 } else { 155 self::$logBetaCacheResult = self::logGamma($p) + self::logGamma($q) - self::logGamma($p + $q); 156 } 157 } 158 return self::$logBetaCacheResult; 159 } 160 161 162 /** 163 * Evaluates of continued fraction part of incomplete beta function. 164 * Based on an idea from Numerical Recipes (W.H. Press et al, 1992). 165 * @author Jaco van Kooten 166 */ 167 private static function betaFraction($x, $p, $q) 168 { 169 $c = 1.0; 170 $sum_pq = $p + $q; 171 $p_plus = $p + 1.0; 172 $p_minus = $p - 1.0; 173 $h = 1.0 - $sum_pq * $x / $p_plus; 174 if (abs($h) < XMININ) { 175 $h = XMININ; 176 } 177 $h = 1.0 / $h; 178 $frac = $h; 179 $m = 1; 180 $delta = 0.0; 181 while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION) { 182 $m2 = 2 * $m; 183 // even index for d 184 $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2)); 185 $h = 1.0 + $d * $h; 186 if (abs($h) < XMININ) { 187 $h = XMININ; 188 } 189 $h = 1.0 / $h; 190 $c = 1.0 + $d / $c; 191 if (abs($c) < XMININ) { 192 $c = XMININ; 193 } 194 $frac *= $h * $c; 195 // odd index for d 196 $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2)); 197 $h = 1.0 + $d * $h; 198 if (abs($h) < XMININ) { 199 $h = XMININ; 200 } 201 $h = 1.0 / $h; 202 $c = 1.0 + $d / $c; 203 if (abs($c) < XMININ) { 204 $c = XMININ; 205 } 206 $delta = $h * $c; 207 $frac *= $delta; 208 ++$m; 209 } 210 return $frac; 211 } 212 213 214 /** 215 * logGamma function 216 * 217 * @version 1.1 218 * @author Jaco van Kooten 219 * 220 * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher. 221 * 222 * The natural logarithm of the gamma function. <br /> 223 * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br /> 224 * Applied Mathematics Division <br /> 225 * Argonne National Laboratory <br /> 226 * Argonne, IL 60439 <br /> 227 * <p> 228 * References: 229 * <ol> 230 * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural 231 * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li> 232 * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li> 233 * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li> 234 * </ol> 235 * </p> 236 * <p> 237 * From the original documentation: 238 * </p> 239 * <p> 240 * This routine calculates the LOG(GAMMA) function for a positive real argument X. 241 * Computation is based on an algorithm outlined in references 1 and 2. 242 * The program uses rational functions that theoretically approximate LOG(GAMMA) 243 * to at least 18 significant decimal digits. The approximation for X > 12 is from 244 * reference 3, while approximations for X < 12.0 are similar to those in reference 245 * 1, but are unpublished. The accuracy achieved depends on the arithmetic system, 246 * the compiler, the intrinsic functions, and proper selection of the 247 * machine-dependent constants. 248 * </p> 249 * <p> 250 * Error returns: <br /> 251 * The program returns the value XINF for X .LE. 0.0 or when overflow would occur. 252 * The computation is believed to be free of underflow and overflow. 253 * </p> 254 * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305 255 */ 256 257 // Function cache for logGamma 258 private static $logGammaCacheResult = 0.0; 259 private static $logGammaCacheX = 0.0; 260 261 private static function logGamma($x) 262 { 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( 269 4.945235359296727046734888, 270 201.8112620856775083915565, 271 2290.838373831346393026739, 272 11319.67205903380828685045, 273 28557.24635671635335736389, 274 38484.96228443793359990269, 275 26377.48787624195437963534, 276 7225.813979700288197698961 277 ); 278 static $lg_p2 = array( 279 4.974607845568932035012064, 280 542.4138599891070494101986, 281 15506.93864978364947665077, 282 184793.2904445632425417223, 283 1088204.76946882876749847, 284 3338152.967987029735917223, 285 5106661.678927352456275255, 286 3074109.054850539556250927 287 ); 288 static $lg_p4 = array( 289 14745.02166059939948905062, 290 2426813.369486704502836312, 291 121475557.4045093227939592, 292 2663432449.630976949898078, 293 29403789566.34553899906876, 294 170266573776.5398868392998, 295 492612579337.743088758812, 296 560625185622.3951465078242 297 ); 298 static $lg_q1 = array( 299 67.48212550303777196073036, 300 1113.332393857199323513008, 301 7738.757056935398733233834, 302 27639.87074403340708898585, 303 54993.10206226157329794414, 304 61611.22180066002127833352, 305 36351.27591501940507276287, 306 8785.536302431013170870835 307 ); 308 static $lg_q2 = array( 309 183.0328399370592604055942, 310 7765.049321445005871323047, 311 133190.3827966074194402448, 312 1136705.821321969608938755, 313 5267964.117437946917577538, 314 13467014.54311101692290052, 315 17827365.30353274213975932, 316 9533095.591844353613395747 317 ); 318 static $lg_q4 = array( 319 2690.530175870899333379843, 320 639388.5654300092398984238, 321 41355999.30241388052042842, 322 1120872109.61614794137657, 323 14886137286.78813811542398, 324 101680358627.2438228077304, 325 341747634550.7377132798597, 326 446315818741.9713286462081 327 ); 328 static $lg_c = array( 329 -0.001910444077728, 330 8.4171387781295e-4, 331 -5.952379913043012e-4, 332 7.93650793500350248e-4, 333 -0.002777777777777681622553, 334 0.08333333333333333331554247, 335 0.0057083835261 336 ); 337 338 // Rough estimate of the fourth root of logGamma_xBig 339 static $lg_frtbig = 2.25e76; 340 static $pnt68 = 0.6796875; 341 342 343 if ($x == self::$logGammaCacheX) { 344 return self::$logGammaCacheResult; 345 } 346 $y = $x; 347 if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) { 348 if ($y <= EPS) { 349 $res = -log(y); 350 } elseif ($y <= 1.5) { 351 // --------------------- 352 // EPS .LT. X .LE. 1.5 353 // --------------------- 354 if ($y < $pnt68) { 355 $corr = -log($y); 356 $xm1 = $y; 357 } else { 358 $corr = 0.0; 359 $xm1 = $y - 1.0; 360 } 361 if ($y <= 0.5 || $y >= $pnt68) { 362 $xden = 1.0; 363 $xnum = 0.0; 364 for ($i = 0; $i < 8; ++$i) { 365 $xnum = $xnum * $xm1 + $lg_p1[$i]; 366 $xden = $xden * $xm1 + $lg_q1[$i]; 367 } 368 $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden)); 369 } else { 370 $xm2 = $y - 1.0; 371 $xden = 1.0; 372 $xnum = 0.0; 373 for ($i = 0; $i < 8; ++$i) { 374 $xnum = $xnum * $xm2 + $lg_p2[$i]; 375 $xden = $xden * $xm2 + $lg_q2[$i]; 376 } 377 $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden)); 378 } 379 } elseif ($y <= 4.0) { 380 // --------------------- 381 // 1.5 .LT. X .LE. 4.0 382 // --------------------- 383 $xm2 = $y - 2.0; 384 $xden = 1.0; 385 $xnum = 0.0; 386 for ($i = 0; $i < 8; ++$i) { 387 $xnum = $xnum * $xm2 + $lg_p2[$i]; 388 $xden = $xden * $xm2 + $lg_q2[$i]; 389 } 390 $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden)); 391 } elseif ($y <= 12.0) { 392 // ---------------------- 393 // 4.0 .LT. X .LE. 12.0 394 // ---------------------- 395 $xm4 = $y - 4.0; 396 $xden = -1.0; 397 $xnum = 0.0; 398 for ($i = 0; $i < 8; ++$i) { 399 $xnum = $xnum * $xm4 + $lg_p4[$i]; 400 $xden = $xden * $xm4 + $lg_q4[$i]; 401 } 402 $res = $lg_d4 + $xm4 * ($xnum / $xden); 403 } else { 404 // --------------------------------- 405 // Evaluate for argument .GE. 12.0 406 // --------------------------------- 407 $res = 0.0; 408 if ($y <= $lg_frtbig) { 409 $res = $lg_c[6]; 410 $ysq = $y * $y; 411 for ($i = 0; $i < 6; ++$i) { 412 $res = $res / $ysq + $lg_c[$i]; 413 } 414 $res /= $y; 415 $corr = log($y); 416 $res = $res + log(SQRT2PI) - 0.5 * $corr; 417 $res += $y * ($corr - 1.0); 418 } 419 } 420 } else { 421 // -------------------------- 422 // Return for bad arguments 423 // -------------------------- 424 $res = MAX_VALUE; 425 } 426 // ------------------------------ 427 // Final adjustments and return 428 // ------------------------------ 429 self::$logGammaCacheX = $x; 430 self::$logGammaCacheResult = $res; 431 return $res; 432 } 433 434 435 // 436 // Private implementation of the incomplete Gamma function 437 // 438 private static function incompleteGamma($a, $x) 439 { 440 static $max = 32; 441 $summer = 0; 442 for ($n=0; $n<=$max; ++$n) { 443 $divisor = $a; 444 for ($i=1; $i<=$n; ++$i) { 445 $divisor *= ($a + $i); 446 } 447 $summer += (pow($x, $n) / $divisor); 448 } 449 return pow($x, $a) * exp(0-$x) * $summer; 450 } 451 452 453 // 454 // Private implementation of the Gamma function 455 // 456 private static function gamma($data) 457 { 458 if ($data == 0.0) { 459 return 0; 460 } 461 462 static $p0 = 1.000000000190015; 463 static $p = array( 464 1 => 76.18009172947146, 465 2 => -86.50532032941677, 466 3 => 24.01409824083091, 467 4 => -1.231739572450155, 468 5 => 1.208650973866179e-3, 469 6 => -5.395239384953e-6 470 ); 471 472 $y = $x = $data; 473 $tmp = $x + 5.5; 474 $tmp -= ($x + 0.5) * log($tmp); 475 476 $summer = $p0; 477 for ($j=1; $j<=6; ++$j) { 478 $summer += ($p[$j] / ++$y); 479 } 480 return exp(0 - $tmp + log(SQRT2PI * $summer / $x)); 481 } 482 483 484 /*************************************************************************** 485 * inverse_ncdf.php 486 * ------------------- 487 * begin : Friday, January 16, 2004 488 * copyright : (C) 2004 Michael Nickerson 489 * email : nickersonm@yahoo.com 490 * 491 ***************************************************************************/ 492 private static function inverseNcdf($p) 493 { 494 // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to 495 // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as 496 // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html 497 // I have not checked the accuracy of this implementation. Be aware that PHP 498 // will truncate the coeficcients to 14 digits. 499 500 // You have permission to use and distribute this function freely for 501 // whatever purpose you want, but please show common courtesy and give credit 502 // where credit is due. 503 504 // Input paramater is $p - probability - where 0 < p < 1. 505 506 // Coefficients in rational approximations 507 static $a = array( 508 1 => -3.969683028665376e+01, 509 2 => 2.209460984245205e+02, 510 3 => -2.759285104469687e+02, 511 4 => 1.383577518672690e+02, 512 5 => -3.066479806614716e+01, 513 6 => 2.506628277459239e+00 514 ); 515 516 static $b = array( 517 1 => -5.447609879822406e+01, 518 2 => 1.615858368580409e+02, 519 3 => -1.556989798598866e+02, 520 4 => 6.680131188771972e+01, 521 5 => -1.328068155288572e+01 522 ); 523 524 static $c = array( 525 1 => -7.784894002430293e-03, 526 2 => -3.223964580411365e-01, 527 3 => -2.400758277161838e+00, 528 4 => -2.549732539343734e+00, 529 5 => 4.374664141464968e+00, 530 6 => 2.938163982698783e+00 531 ); 532 533 static $d = array( 534 1 => 7.784695709041462e-03, 535 2 => 3.224671290700398e-01, 536 3 => 2.445134137142996e+00, 537 4 => 3.754408661907416e+00 538 ); 539 540 // Define lower and upper region break-points. 541 $p_low = 0.02425; //Use lower region approx. below this 542 $p_high = 1 - $p_low; //Use upper region approx. above this 543 544 if (0 < $p && $p < $p_low) { 545 // Rational approximation for lower region. 546 $q = sqrt(-2 * log($p)); 547 return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) / 548 (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1); 549 } elseif ($p_low <= $p && $p <= $p_high) { 550 // Rational approximation for central region. 551 $q = $p - 0.5; 552 $r = $q * $q; 553 return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q / 554 ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1); 555 } elseif ($p_high < $p && $p < 1) { 556 // Rational approximation for upper region. 557 $q = sqrt(-2 * log(1 - $p)); 558 return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) / 559 (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1); 560 } 561 // If 0 < p < 1, return a null value 562 return PHPExcel_Calculation_Functions::NULL(); 563 } 564 565 566 private static function inverseNcdf2($prob) 567 { 568 // Approximation of inverse standard normal CDF developed by 569 // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58. 570 571 $a1 = 2.50662823884; 572 $a2 = -18.61500062529; 573 $a3 = 41.39119773534; 574 $a4 = -25.44106049637; 575 576 $b1 = -8.4735109309; 577 $b2 = 23.08336743743; 578 $b3 = -21.06224101826; 579 $b4 = 3.13082909833; 580 581 $c1 = 0.337475482272615; 582 $c2 = 0.976169019091719; 583 $c3 = 0.160797971491821; 584 $c4 = 2.76438810333863E-02; 585 $c5 = 3.8405729373609E-03; 586 $c6 = 3.951896511919E-04; 587 $c7 = 3.21767881768E-05; 588 $c8 = 2.888167364E-07; 589 $c9 = 3.960315187E-07; 590 591 $y = $prob - 0.5; 592 if (abs($y) < 0.42) { 593 $z = ($y * $y); 594 $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1); 595 } else { 596 if ($y > 0) { 597 $z = log(-log(1 - $prob)); 598 } else { 599 $z = log(-log($prob)); 600 } 601 $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9))))))); 602 if ($y < 0) { 603 $z = -$z; 604 } 605 } 606 return $z; 607 } // function inverseNcdf2() 608 609 610 private static function inverseNcdf3($p) 611 { 612 // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3. 613 // Produces the normal deviate Z corresponding to a given lower 614 // tail area of P; Z is accurate to about 1 part in 10**16. 615 // 616 // This is a PHP version of the original FORTRAN code that can 617 // be found at http://lib.stat.cmu.edu/apstat/ 618 $split1 = 0.425; 619 $split2 = 5; 620 $const1 = 0.180625; 621 $const2 = 1.6; 622 623 // coefficients for p close to 0.5 624 $a0 = 3.3871328727963666080; 625 $a1 = 1.3314166789178437745E+2; 626 $a2 = 1.9715909503065514427E+3; 627 $a3 = 1.3731693765509461125E+4; 628 $a4 = 4.5921953931549871457E+4; 629 $a5 = 6.7265770927008700853E+4; 630 $a6 = 3.3430575583588128105E+4; 631 $a7 = 2.5090809287301226727E+3; 632 633 $b1 = 4.2313330701600911252E+1; 634 $b2 = 6.8718700749205790830E+2; 635 $b3 = 5.3941960214247511077E+3; 636 $b4 = 2.1213794301586595867E+4; 637 $b5 = 3.9307895800092710610E+4; 638 $b6 = 2.8729085735721942674E+4; 639 $b7 = 5.2264952788528545610E+3; 640 641 // coefficients for p not close to 0, 0.5 or 1. 642 $c0 = 1.42343711074968357734; 643 $c1 = 4.63033784615654529590; 644 $c2 = 5.76949722146069140550; 645 $c3 = 3.64784832476320460504; 646 $c4 = 1.27045825245236838258; 647 $c5 = 2.41780725177450611770E-1; 648 $c6 = 2.27238449892691845833E-2; 649 $c7 = 7.74545014278341407640E-4; 650 651 $d1 = 2.05319162663775882187; 652 $d2 = 1.67638483018380384940; 653 $d3 = 6.89767334985100004550E-1; 654 $d4 = 1.48103976427480074590E-1; 655 $d5 = 1.51986665636164571966E-2; 656 $d6 = 5.47593808499534494600E-4; 657 $d7 = 1.05075007164441684324E-9; 658 659 // coefficients for p near 0 or 1. 660 $e0 = 6.65790464350110377720; 661 $e1 = 5.46378491116411436990; 662 $e2 = 1.78482653991729133580; 663 $e3 = 2.96560571828504891230E-1; 664 $e4 = 2.65321895265761230930E-2; 665 $e5 = 1.24266094738807843860E-3; 666 $e6 = 2.71155556874348757815E-5; 667 $e7 = 2.01033439929228813265E-7; 668 669 $f1 = 5.99832206555887937690E-1; 670 $f2 = 1.36929880922735805310E-1; 671 $f3 = 1.48753612908506148525E-2; 672 $f4 = 7.86869131145613259100E-4; 673 $f5 = 1.84631831751005468180E-5; 674 $f6 = 1.42151175831644588870E-7; 675 $f7 = 2.04426310338993978564E-15; 676 677 $q = $p - 0.5; 678 679 // computation for p close to 0.5 680 if (abs($q) <= split1) { 681 $R = $const1 - $q * $q; 682 $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) / 683 ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1); 684 } else { 685 if ($q < 0) { 686 $R = $p; 687 } else { 688 $R = 1 - $p; 689 } 690 $R = pow(-log($R), 2); 691 692 // computation for p not close to 0, 0.5 or 1. 693 if ($R <= $split2) { 694 $R = $R - $const2; 695 $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) / 696 ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1); 697 } else { 698 // computation for p near 0 or 1. 699 $R = $R - $split2; 700 $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) / 701 ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1); 702 } 703 if ($q < 0) { 704 $z = -$z; 705 } 706 } 707 return $z; 708 } 709 710 711 /** 712 * AVEDEV 713 * 714 * Returns the average of the absolute deviations of data points from their mean. 715 * AVEDEV is a measure of the variability in a data set. 716 * 717 * Excel Function: 718 * AVEDEV(value1[,value2[, ...]]) 719 * 720 * @access public 721 * @category Statistical Functions 722 * @param mixed $arg,... Data values 723 * @return float 724 */ 725 public static function AVEDEV() 726 { 727 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 728 729 // Return value 730 $returnValue = null; 731 732 $aMean = self::AVERAGE($aArgs); 733 if ($aMean != PHPExcel_Calculation_Functions::DIV0()) { 734 $aCount = 0; 735 foreach ($aArgs as $k => $arg) { 736 if ((is_bool($arg)) && 737 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 738 $arg = (integer) $arg; 739 } 740 // Is it a numeric value? 741 if ((is_numeric($arg)) && (!is_string($arg))) { 742 if (is_null($returnValue)) { 743 $returnValue = abs($arg - $aMean); 744 } else { 745 $returnValue += abs($arg - $aMean); 746 } 747 ++$aCount; 748 } 749 } 750 751 // Return 752 if ($aCount == 0) { 753 return PHPExcel_Calculation_Functions::DIV0(); 754 } 755 return $returnValue / $aCount; 756 } 757 return PHPExcel_Calculation_Functions::NaN(); 758 } 759 760 761 /** 762 * AVERAGE 763 * 764 * Returns the average (arithmetic mean) of the arguments 765 * 766 * Excel Function: 767 * AVERAGE(value1[,value2[, ...]]) 768 * 769 * @access public 770 * @category Statistical Functions 771 * @param mixed $arg,... Data values 772 * @return float 773 */ 774 public static function AVERAGE() 775 { 776 $returnValue = $aCount = 0; 777 778 // Loop through arguments 779 foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) { 780 if ((is_bool($arg)) && 781 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 782 $arg = (integer) $arg; 783 } 784 // Is it a numeric value? 785 if ((is_numeric($arg)) && (!is_string($arg))) { 786 if (is_null($returnValue)) { 787 $returnValue = $arg; 788 } else { 789 $returnValue += $arg; 790 } 791 ++$aCount; 792 } 793 } 794 795 // Return 796 if ($aCount > 0) { 797 return $returnValue / $aCount; 798 } else { 799 return PHPExcel_Calculation_Functions::DIV0(); 800 } 801 } 802 803 804 /** 805 * AVERAGEA 806 * 807 * Returns the average of its arguments, including numbers, text, and logical values 808 * 809 * Excel Function: 810 * AVERAGEA(value1[,value2[, ...]]) 811 * 812 * @access public 813 * @category Statistical Functions 814 * @param mixed $arg,... Data values 815 * @return float 816 */ 817 public static function AVERAGEA() 818 { 819 $returnValue = null; 820 821 $aCount = 0; 822 // Loop through arguments 823 foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) { 824 if ((is_bool($arg)) && 825 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 826 } else { 827 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 828 if (is_bool($arg)) { 829 $arg = (integer) $arg; 830 } elseif (is_string($arg)) { 831 $arg = 0; 832 } 833 if (is_null($returnValue)) { 834 $returnValue = $arg; 835 } else { 836 $returnValue += $arg; 837 } 838 ++$aCount; 839 } 840 } 841 } 842 843 if ($aCount > 0) { 844 return $returnValue / $aCount; 845 } else { 846 return PHPExcel_Calculation_Functions::DIV0(); 847 } 848 } 849 850 851 /** 852 * AVERAGEIF 853 * 854 * Returns the average value from a range of cells that contain numbers within the list of arguments 855 * 856 * Excel Function: 857 * AVERAGEIF(value1[,value2[, ...]],condition) 858 * 859 * @access public 860 * @category Mathematical and Trigonometric Functions 861 * @param mixed $arg,... Data values 862 * @param string $condition The criteria that defines which cells will be checked. 863 * @param mixed[] $averageArgs Data values 864 * @return float 865 */ 866 public static function AVERAGEIF($aArgs, $condition, $averageArgs = array()) 867 { 868 $returnValue = 0; 869 870 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 871 $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs); 872 if (empty($averageArgs)) { 873 $averageArgs = $aArgs; 874 } 875 $condition = PHPExcel_Calculation_Functions::ifCondition($condition); 876 // Loop through arguments 877 $aCount = 0; 878 foreach ($aArgs as $key => $arg) { 879 if (!is_numeric($arg)) { 880 $arg = PHPExcel_Calculation::wrapResult(strtoupper($arg)); 881 } 882 $testCondition = '='.$arg.$condition; 883 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 884 if ((is_null($returnValue)) || ($arg > $returnValue)) { 885 $returnValue += $arg; 886 ++$aCount; 887 } 888 } 889 } 890 891 if ($aCount > 0) { 892 return $returnValue / $aCount; 893 } 894 return PHPExcel_Calculation_Functions::DIV0(); 895 } 896 897 898 /** 899 * BETADIST 900 * 901 * Returns the beta distribution. 902 * 903 * @param float $value Value at which you want to evaluate the distribution 904 * @param float $alpha Parameter to the distribution 905 * @param float $beta Parameter to the distribution 906 * @param boolean $cumulative 907 * @return float 908 * 909 */ 910 public static function BETADIST($value, $alpha, $beta, $rMin = 0, $rMax = 1) 911 { 912 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 913 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 914 $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta); 915 $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin); 916 $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax); 917 918 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) { 919 if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) { 920 return PHPExcel_Calculation_Functions::NaN(); 921 } 922 if ($rMin > $rMax) { 923 $tmp = $rMin; 924 $rMin = $rMax; 925 $rMax = $tmp; 926 } 927 $value -= $rMin; 928 $value /= ($rMax - $rMin); 929 return self::incompleteBeta($value, $alpha, $beta); 930 } 931 return PHPExcel_Calculation_Functions::VALUE(); 932 } 933 934 935 /** 936 * BETAINV 937 * 938 * Returns the inverse of the beta distribution. 939 * 940 * @param float $probability Probability at which you want to evaluate the distribution 941 * @param float $alpha Parameter to the distribution 942 * @param float $beta Parameter to the distribution 943 * @param float $rMin Minimum value 944 * @param float $rMax Maximum value 945 * @param boolean $cumulative 946 * @return float 947 * 948 */ 949 public static function BETAINV($probability, $alpha, $beta, $rMin = 0, $rMax = 1) 950 { 951 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 952 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 953 $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta); 954 $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin); 955 $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax); 956 957 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) { 958 if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) { 959 return PHPExcel_Calculation_Functions::NaN(); 960 } 961 if ($rMin > $rMax) { 962 $tmp = $rMin; 963 $rMin = $rMax; 964 $rMax = $tmp; 965 } 966 $a = 0; 967 $b = 2; 968 969 $i = 0; 970 while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) { 971 $guess = ($a + $b) / 2; 972 $result = self::BETADIST($guess, $alpha, $beta); 973 if (($result == $probability) || ($result == 0)) { 974 $b = $a; 975 } elseif ($result > $probability) { 976 $b = $guess; 977 } else { 978 $a = $guess; 979 } 980 } 981 if ($i == MAX_ITERATIONS) { 982 return PHPExcel_Calculation_Functions::NA(); 983 } 984 return round($rMin + $guess * ($rMax - $rMin), 12); 985 } 986 return PHPExcel_Calculation_Functions::VALUE(); 987 } 988 989 990 /** 991 * BINOMDIST 992 * 993 * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with 994 * a fixed number of tests or trials, when the outcomes of any trial are only success or failure, 995 * when trials are independent, and when the probability of success is constant throughout the 996 * experiment. For example, BINOMDIST can calculate the probability that two of the next three 997 * babies born are male. 998 * 999 * @param float $value Number of successes in trials 1000 * @param float $trials Number of trials 1001 * @param float $probability Probability of success on each trial 1002 * @param boolean $cumulative 1003 * @return float 1004 * 1005 * @todo Cumulative distribution function 1006 * 1007 */ 1008 public static function BINOMDIST($value, $trials, $probability, $cumulative) 1009 { 1010 $value = floor(PHPExcel_Calculation_Functions::flattenSingleValue($value)); 1011 $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials)); 1012 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 1013 1014 if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) { 1015 if (($value < 0) || ($value > $trials)) { 1016 return PHPExcel_Calculation_Functions::NaN(); 1017 } 1018 if (($probability < 0) || ($probability > 1)) { 1019 return PHPExcel_Calculation_Functions::NaN(); 1020 } 1021 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 1022 if ($cumulative) { 1023 $summer = 0; 1024 for ($i = 0; $i <= $value; ++$i) { 1025 $summer += PHPExcel_Calculation_MathTrig::COMBIN($trials, $i) * pow($probability, $i) * pow(1 - $probability, $trials - $i); 1026 } 1027 return $summer; 1028 } else { 1029 return PHPExcel_Calculation_MathTrig::COMBIN($trials, $value) * pow($probability, $value) * pow(1 - $probability, $trials - $value) ; 1030 } 1031 } 1032 } 1033 return PHPExcel_Calculation_Functions::VALUE(); 1034 } 1035 1036 1037 /** 1038 * CHIDIST 1039 * 1040 * Returns the one-tailed probability of the chi-squared distribution. 1041 * 1042 * @param float $value Value for the function 1043 * @param float $degrees degrees of freedom 1044 * @return float 1045 */ 1046 public static function CHIDIST($value, $degrees) 1047 { 1048 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1049 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees)); 1050 1051 if ((is_numeric($value)) && (is_numeric($degrees))) { 1052 if ($degrees < 1) { 1053 return PHPExcel_Calculation_Functions::NaN(); 1054 } 1055 if ($value < 0) { 1056 if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) { 1057 return 1; 1058 } 1059 return PHPExcel_Calculation_Functions::NaN(); 1060 } 1061 return 1 - (self::incompleteGamma($degrees/2, $value/2) / self::gamma($degrees/2)); 1062 } 1063 return PHPExcel_Calculation_Functions::VALUE(); 1064 } 1065 1066 1067 /** 1068 * CHIINV 1069 * 1070 * Returns the one-tailed probability of the chi-squared distribution. 1071 * 1072 * @param float $probability Probability for the function 1073 * @param float $degrees degrees of freedom 1074 * @return float 1075 */ 1076 public static function CHIINV($probability, $degrees) 1077 { 1078 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 1079 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees)); 1080 1081 if ((is_numeric($probability)) && (is_numeric($degrees))) { 1082 $xLo = 100; 1083 $xHi = 0; 1084 1085 $x = $xNew = 1; 1086 $dx = 1; 1087 $i = 0; 1088 1089 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) { 1090 // Apply Newton-Raphson step 1091 $result = self::CHIDIST($x, $degrees); 1092 $error = $result - $probability; 1093 if ($error == 0.0) { 1094 $dx = 0; 1095 } elseif ($error < 0.0) { 1096 $xLo = $x; 1097 } else { 1098 $xHi = $x; 1099 } 1100 // Avoid division by zero 1101 if ($result != 0.0) { 1102 $dx = $error / $result; 1103 $xNew = $x - $dx; 1104 } 1105 // If the NR fails to converge (which for example may be the 1106 // case if the initial guess is too rough) we apply a bisection 1107 // step to determine a more narrow interval around the root. 1108 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) { 1109 $xNew = ($xLo + $xHi) / 2; 1110 $dx = $xNew - $x; 1111 } 1112 $x = $xNew; 1113 } 1114 if ($i == MAX_ITERATIONS) { 1115 return PHPExcel_Calculation_Functions::NA(); 1116 } 1117 return round($x, 12); 1118 } 1119 return PHPExcel_Calculation_Functions::VALUE(); 1120 } 1121 1122 1123 /** 1124 * CONFIDENCE 1125 * 1126 * Returns the confidence interval for a population mean 1127 * 1128 * @param float $alpha 1129 * @param float $stdDev Standard Deviation 1130 * @param float $size 1131 * @return float 1132 * 1133 */ 1134 public static function CONFIDENCE($alpha, $stdDev, $size) 1135 { 1136 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 1137 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 1138 $size = floor(PHPExcel_Calculation_Functions::flattenSingleValue($size)); 1139 1140 if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) { 1141 if (($alpha <= 0) || ($alpha >= 1)) { 1142 return PHPExcel_Calculation_Functions::NaN(); 1143 } 1144 if (($stdDev <= 0) || ($size < 1)) { 1145 return PHPExcel_Calculation_Functions::NaN(); 1146 } 1147 return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size); 1148 } 1149 return PHPExcel_Calculation_Functions::VALUE(); 1150 } 1151 1152 1153 /** 1154 * CORREL 1155 * 1156 * Returns covariance, the average of the products of deviations for each data point pair. 1157 * 1158 * @param array of mixed Data Series Y 1159 * @param array of mixed Data Series X 1160 * @return float 1161 */ 1162 public static function CORREL($yValues, $xValues = null) 1163 { 1164 if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) { 1165 return PHPExcel_Calculation_Functions::VALUE(); 1166 } 1167 if (!self::checkTrendArrays($yValues, $xValues)) { 1168 return PHPExcel_Calculation_Functions::VALUE(); 1169 } 1170 $yValueCount = count($yValues); 1171 $xValueCount = count($xValues); 1172 1173 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1174 return PHPExcel_Calculation_Functions::NA(); 1175 } elseif ($yValueCount == 1) { 1176 return PHPExcel_Calculation_Functions::DIV0(); 1177 } 1178 1179 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 1180 return $bestFitLinear->getCorrelation(); 1181 } 1182 1183 1184 /** 1185 * COUNT 1186 * 1187 * Counts the number of cells that contain numbers within the list of arguments 1188 * 1189 * Excel Function: 1190 * COUNT(value1[,value2[, ...]]) 1191 * 1192 * @access public 1193 * @category Statistical Functions 1194 * @param mixed $arg,... Data values 1195 * @return int 1196 */ 1197 public static function COUNT() 1198 { 1199 $returnValue = 0; 1200 1201 // Loop through arguments 1202 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 1203 foreach ($aArgs as $k => $arg) { 1204 if ((is_bool($arg)) && 1205 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 1206 $arg = (integer) $arg; 1207 } 1208 // Is it a numeric value? 1209 if ((is_numeric($arg)) && (!is_string($arg))) { 1210 ++$returnValue; 1211 } 1212 } 1213 1214 return $returnValue; 1215 } 1216 1217 1218 /** 1219 * COUNTA 1220 * 1221 * Counts the number of cells that are not empty within the list of arguments 1222 * 1223 * Excel Function: 1224 * COUNTA(value1[,value2[, ...]]) 1225 * 1226 * @access public 1227 * @category Statistical Functions 1228 * @param mixed $arg,... Data values 1229 * @return int 1230 */ 1231 public static function COUNTA() 1232 { 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 numeric, boolean or string value? 1239 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 1240 ++$returnValue; 1241 } 1242 } 1243 1244 return $returnValue; 1245 } 1246 1247 1248 /** 1249 * COUNTBLANK 1250 * 1251 * Counts the number of empty cells within the list of arguments 1252 * 1253 * Excel Function: 1254 * COUNTBLANK(value1[,value2[, ...]]) 1255 * 1256 * @access public 1257 * @category Statistical Functions 1258 * @param mixed $arg,... Data values 1259 * @return int 1260 */ 1261 public static function COUNTBLANK() 1262 { 1263 $returnValue = 0; 1264 1265 // Loop through arguments 1266 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 1267 foreach ($aArgs as $arg) { 1268 // Is it a blank cell? 1269 if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) { 1270 ++$returnValue; 1271 } 1272 } 1273 1274 return $returnValue; 1275 } 1276 1277 1278 /** 1279 * COUNTIF 1280 * 1281 * Counts the number of cells that contain numbers within the list of arguments 1282 * 1283 * Excel Function: 1284 * COUNTIF(value1[,value2[, ...]],condition) 1285 * 1286 * @access public 1287 * @category Statistical Functions 1288 * @param mixed $arg,... Data values 1289 * @param string $condition The criteria that defines which cells will be counted. 1290 * @return int 1291 */ 1292 public static function COUNTIF($aArgs, $condition) 1293 { 1294 $returnValue = 0; 1295 1296 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 1297 $condition = PHPExcel_Calculation_Functions::ifCondition($condition); 1298 // Loop through arguments 1299 foreach ($aArgs as $arg) { 1300 if (!is_numeric($arg)) { 1301 $arg = PHPExcel_Calculation::wrapResult(strtoupper($arg)); 1302 } 1303 $testCondition = '='.$arg.$condition; 1304 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1305 // Is it a value within our criteria 1306 ++$returnValue; 1307 } 1308 } 1309 1310 return $returnValue; 1311 } 1312 1313 1314 /** 1315 * COVAR 1316 * 1317 * Returns covariance, the average of the products of deviations for each data point pair. 1318 * 1319 * @param array of mixed Data Series Y 1320 * @param array of mixed Data Series X 1321 * @return float 1322 */ 1323 public static function COVAR($yValues, $xValues) 1324 { 1325 if (!self::checkTrendArrays($yValues, $xValues)) { 1326 return PHPExcel_Calculation_Functions::VALUE(); 1327 } 1328 $yValueCount = count($yValues); 1329 $xValueCount = count($xValues); 1330 1331 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1332 return PHPExcel_Calculation_Functions::NA(); 1333 } elseif ($yValueCount == 1) { 1334 return PHPExcel_Calculation_Functions::DIV0(); 1335 } 1336 1337 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 1338 return $bestFitLinear->getCovariance(); 1339 } 1340 1341 1342 /** 1343 * CRITBINOM 1344 * 1345 * Returns the smallest value for which the cumulative binomial distribution is greater 1346 * than or equal to a criterion value 1347 * 1348 * See http://support.microsoft.com/kb/828117/ for details of the algorithm used 1349 * 1350 * @param float $trials number of Bernoulli trials 1351 * @param float $probability probability of a success on each trial 1352 * @param float $alpha criterion value 1353 * @return int 1354 * 1355 * @todo Warning. This implementation differs from the algorithm detailed on the MS 1356 * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess 1357 * This eliminates a potential endless loop error, but may have an adverse affect on the 1358 * accuracy of the function (although all my tests have so far returned correct results). 1359 * 1360 */ 1361 public static function CRITBINOM($trials, $probability, $alpha) 1362 { 1363 $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials)); 1364 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 1365 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 1366 1367 if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) { 1368 if ($trials < 0) { 1369 return PHPExcel_Calculation_Functions::NaN(); 1370 } elseif (($probability < 0) || ($probability > 1)) { 1371 return PHPExcel_Calculation_Functions::NaN(); 1372 } elseif (($alpha < 0) || ($alpha > 1)) { 1373 return PHPExcel_Calculation_Functions::NaN(); 1374 } elseif ($alpha <= 0.5) { 1375 $t = sqrt(log(1 / ($alpha * $alpha))); 1376 $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)); 1377 } else { 1378 $t = sqrt(log(1 / pow(1 - $alpha, 2))); 1379 $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t); 1380 } 1381 $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability))); 1382 if ($Guess < 0) { 1383 $Guess = 0; 1384 } elseif ($Guess > $trials) { 1385 $Guess = $trials; 1386 } 1387 1388 $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0; 1389 $EssentiallyZero = 10e-12; 1390 1391 $m = floor($trials * $probability); 1392 ++$TotalUnscaledProbability; 1393 if ($m == $Guess) { 1394 ++$UnscaledPGuess; 1395 } 1396 if ($m <= $Guess) { 1397 ++$UnscaledCumPGuess; 1398 } 1399 1400 $PreviousValue = 1; 1401 $Done = false; 1402 $k = $m + 1; 1403 while ((!$Done) && ($k <= $trials)) { 1404 $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability)); 1405 $TotalUnscaledProbability += $CurrentValue; 1406 if ($k == $Guess) { 1407 $UnscaledPGuess += $CurrentValue; 1408 } 1409 if ($k <= $Guess) { 1410 $UnscaledCumPGuess += $CurrentValue; 1411 } 1412 if ($CurrentValue <= $EssentiallyZero) { 1413 $Done = true; 1414 } 1415 $PreviousValue = $CurrentValue; 1416 ++$k; 1417 } 1418 1419 $PreviousValue = 1; 1420 $Done = false; 1421 $k = $m - 1; 1422 while ((!$Done) && ($k >= 0)) { 1423 $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability); 1424 $TotalUnscaledProbability += $CurrentValue; 1425 if ($k == $Guess) { 1426 $UnscaledPGuess += $CurrentValue; 1427 } 1428 if ($k <= $Guess) { 1429 $UnscaledCumPGuess += $CurrentValue; 1430 } 1431 if ($CurrentValue <= $EssentiallyZero) { 1432 $Done = true; 1433 } 1434 $PreviousValue = $CurrentValue; 1435 --$k; 1436 } 1437 1438 $PGuess = $UnscaledPGuess / $TotalUnscaledProbability; 1439 $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability; 1440 1441 // $CumPGuessMinus1 = $CumPGuess - $PGuess; 1442 $CumPGuessMinus1 = $CumPGuess - 1; 1443 1444 while (true) { 1445 if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) { 1446 return $Guess; 1447 } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) { 1448 $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability); 1449 $CumPGuessMinus1 = $CumPGuess; 1450 $CumPGuess = $CumPGuess + $PGuessPlus1; 1451 $PGuess = $PGuessPlus1; 1452 ++$Guess; 1453 } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) { 1454 $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability; 1455 $CumPGuess = $CumPGuessMinus1; 1456 $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess; 1457 $PGuess = $PGuessMinus1; 1458 --$Guess; 1459 } 1460 } 1461 } 1462 return PHPExcel_Calculation_Functions::VALUE(); 1463 } 1464 1465 1466 /** 1467 * DEVSQ 1468 * 1469 * Returns the sum of squares of deviations of data points from their sample mean. 1470 * 1471 * Excel Function: 1472 * DEVSQ(value1[,value2[, ...]]) 1473 * 1474 * @access public 1475 * @category Statistical Functions 1476 * @param mixed $arg,... Data values 1477 * @return float 1478 */ 1479 public static function DEVSQ() 1480 { 1481 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 1482 1483 // Return value 1484 $returnValue = null; 1485 1486 $aMean = self::AVERAGE($aArgs); 1487 if ($aMean != PHPExcel_Calculation_Functions::DIV0()) { 1488 $aCount = -1; 1489 foreach ($aArgs as $k => $arg) { 1490 // Is it a numeric value? 1491 if ((is_bool($arg)) && 1492 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || 1493 (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 1494 $arg = (integer) $arg; 1495 } 1496 if ((is_numeric($arg)) && (!is_string($arg))) { 1497 if (is_null($returnValue)) { 1498 $returnValue = pow(($arg - $aMean), 2); 1499 } else { 1500 $returnValue += pow(($arg - $aMean), 2); 1501 } 1502 ++$aCount; 1503 } 1504 } 1505 1506 // Return 1507 if (is_null($returnValue)) { 1508 return PHPExcel_Calculation_Functions::NaN(); 1509 } else { 1510 return $returnValue; 1511 } 1512 } 1513 return self::NA(); 1514 } 1515 1516 1517 /** 1518 * EXPONDIST 1519 * 1520 * Returns the exponential distribution. Use EXPONDIST to model the time between events, 1521 * such as how long an automated bank teller takes to deliver cash. For example, you can 1522 * use EXPONDIST to determine the probability that the process takes at most 1 minute. 1523 * 1524 * @param float $value Value of the function 1525 * @param float $lambda The parameter value 1526 * @param boolean $cumulative 1527 * @return float 1528 */ 1529 public static function EXPONDIST($value, $lambda, $cumulative) 1530 { 1531 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1532 $lambda = PHPExcel_Calculation_Functions::flattenSingleValue($lambda); 1533 $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative); 1534 1535 if ((is_numeric($value)) && (is_numeric($lambda))) { 1536 if (($value < 0) || ($lambda < 0)) { 1537 return PHPExcel_Calculation_Functions::NaN(); 1538 } 1539 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 1540 if ($cumulative) { 1541 return 1 - exp(0-$value*$lambda); 1542 } else { 1543 return $lambda * exp(0-$value*$lambda); 1544 } 1545 } 1546 } 1547 return PHPExcel_Calculation_Functions::VALUE(); 1548 } 1549 1550 1551 /** 1552 * FISHER 1553 * 1554 * Returns the Fisher transformation at x. This transformation produces a function that 1555 * is normally distributed rather than skewed. Use this function to perform hypothesis 1556 * testing on the correlation coefficient. 1557 * 1558 * @param float $value 1559 * @return float 1560 */ 1561 public static function FISHER($value) 1562 { 1563 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1564 1565 if (is_numeric($value)) { 1566 if (($value <= -1) || ($value >= 1)) { 1567 return PHPExcel_Calculation_Functions::NaN(); 1568 } 1569 return 0.5 * log((1+$value)/(1-$value)); 1570 } 1571 return PHPExcel_Calculation_Functions::VALUE(); 1572 } 1573 1574 1575 /** 1576 * FISHERINV 1577 * 1578 * Returns the inverse of the Fisher transformation. Use this transformation when 1579 * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then 1580 * FISHERINV(y) = x. 1581 * 1582 * @param float $value 1583 * @return float 1584 */ 1585 public static function FISHERINV($value) 1586 { 1587 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1588 1589 if (is_numeric($value)) { 1590 return (exp(2 * $value) - 1) / (exp(2 * $value) + 1); 1591 } 1592 return PHPExcel_Calculation_Functions::VALUE(); 1593 } 1594 1595 1596 /** 1597 * FORECAST 1598 * 1599 * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. 1600 * 1601 * @param float Value of X for which we want to find Y 1602 * @param array of mixed Data Series Y 1603 * @param array of mixed Data Series X 1604 * @return float 1605 */ 1606 public static function FORECAST($xValue, $yValues, $xValues) 1607 { 1608 $xValue = PHPExcel_Calculation_Functions::flattenSingleValue($xValue); 1609 if (!is_numeric($xValue)) { 1610 return PHPExcel_Calculation_Functions::VALUE(); 1611 } elseif (!self::checkTrendArrays($yValues, $xValues)) { 1612 return PHPExcel_Calculation_Functions::VALUE(); 1613 } 1614 $yValueCount = count($yValues); 1615 $xValueCount = count($xValues); 1616 1617 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1618 return PHPExcel_Calculation_Functions::NA(); 1619 } elseif ($yValueCount == 1) { 1620 return PHPExcel_Calculation_Functions::DIV0(); 1621 } 1622 1623 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 1624 return $bestFitLinear->getValueOfYForX($xValue); 1625 } 1626 1627 1628 /** 1629 * GAMMADIST 1630 * 1631 * Returns the gamma distribution. 1632 * 1633 * @param float $value Value at which you want to evaluate the distribution 1634 * @param float $a Parameter to the distribution 1635 * @param float $b Parameter to the distribution 1636 * @param boolean $cumulative 1637 * @return float 1638 * 1639 */ 1640 public static function GAMMADIST($value, $a, $b, $cumulative) 1641 { 1642 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1643 $a = PHPExcel_Calculation_Functions::flattenSingleValue($a); 1644 $b = PHPExcel_Calculation_Functions::flattenSingleValue($b); 1645 1646 if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) { 1647 if (($value < 0) || ($a <= 0) || ($b <= 0)) { 1648 return PHPExcel_Calculation_Functions::NaN(); 1649 } 1650 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 1651 if ($cumulative) { 1652 return self::incompleteGamma($a, $value / $b) / self::gamma($a); 1653 } else { 1654 return (1 / (pow($b, $a) * self::gamma($a))) * pow($value, $a-1) * exp(0-($value / $b)); 1655 } 1656 } 1657 } 1658 return PHPExcel_Calculation_Functions::VALUE(); 1659 } 1660 1661 1662 /** 1663 * GAMMAINV 1664 * 1665 * Returns the inverse of the beta distribution. 1666 * 1667 * @param float $probability Probability at which you want to evaluate the distribution 1668 * @param float $alpha Parameter to the distribution 1669 * @param float $beta Parameter to the distribution 1670 * @return float 1671 * 1672 */ 1673 public static function GAMMAINV($probability, $alpha, $beta) 1674 { 1675 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 1676 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 1677 $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta); 1678 1679 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) { 1680 if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) { 1681 return PHPExcel_Calculation_Functions::NaN(); 1682 } 1683 1684 $xLo = 0; 1685 $xHi = $alpha * $beta * 5; 1686 1687 $x = $xNew = 1; 1688 $error = $pdf = 0; 1689 $dx = 1024; 1690 $i = 0; 1691 1692 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) { 1693 // Apply Newton-Raphson step 1694 $error = self::GAMMADIST($x, $alpha, $beta, true) - $probability; 1695 if ($error < 0.0) { 1696 $xLo = $x; 1697 } else { 1698 $xHi = $x; 1699 } 1700 $pdf = self::GAMMADIST($x, $alpha, $beta, false); 1701 // Avoid division by zero 1702 if ($pdf != 0.0) { 1703 $dx = $error / $pdf; 1704 $xNew = $x - $dx; 1705 } 1706 // If the NR fails to converge (which for example may be the 1707 // case if the initial guess is too rough) we apply a bisection 1708 // step to determine a more narrow interval around the root. 1709 if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) { 1710 $xNew = ($xLo + $xHi) / 2; 1711 $dx = $xNew - $x; 1712 } 1713 $x = $xNew; 1714 } 1715 if ($i == MAX_ITERATIONS) { 1716 return PHPExcel_Calculation_Functions::NA(); 1717 } 1718 return $x; 1719 } 1720 return PHPExcel_Calculation_Functions::VALUE(); 1721 } 1722 1723 1724 /** 1725 * GAMMALN 1726 * 1727 * Returns the natural logarithm of the gamma function. 1728 * 1729 * @param float $value 1730 * @return float 1731 */ 1732 public static function GAMMALN($value) 1733 { 1734 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 1735 1736 if (is_numeric($value)) { 1737 if ($value <= 0) { 1738 return PHPExcel_Calculation_Functions::NaN(); 1739 } 1740 return log(self::gamma($value)); 1741 } 1742 return PHPExcel_Calculation_Functions::VALUE(); 1743 } 1744 1745 1746 /** 1747 * GEOMEAN 1748 * 1749 * Returns the geometric mean of an array or range of positive data. For example, you 1750 * can use GEOMEAN to calculate average growth rate given compound interest with 1751 * variable rates. 1752 * 1753 * Excel Function: 1754 * GEOMEAN(value1[,value2[, ...]]) 1755 * 1756 * @access public 1757 * @category Statistical Functions 1758 * @param mixed $arg,... Data values 1759 * @return float 1760 */ 1761 public static function GEOMEAN() 1762 { 1763 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 1764 1765 $aMean = PHPExcel_Calculation_MathTrig::PRODUCT($aArgs); 1766 if (is_numeric($aMean) && ($aMean > 0)) { 1767 $aCount = self::COUNT($aArgs) ; 1768 if (self::MIN($aArgs) > 0) { 1769 return pow($aMean, (1 / $aCount)); 1770 } 1771 } 1772 return PHPExcel_Calculation_Functions::NaN(); 1773 } 1774 1775 1776 /** 1777 * GROWTH 1778 * 1779 * Returns values along a predicted emponential trend 1780 * 1781 * @param array of mixed Data Series Y 1782 * @param array of mixed Data Series X 1783 * @param array of mixed Values of X for which we want to find Y 1784 * @param boolean A logical value specifying whether to force the intersect to equal 0. 1785 * @return array of float 1786 */ 1787 public static function GROWTH($yValues, $xValues = array(), $newValues = array(), $const = true) 1788 { 1789 $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues); 1790 $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues); 1791 $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues); 1792 $const = (is_null($const)) ? true : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const); 1793 1794 $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL, $yValues, $xValues, $const); 1795 if (empty($newValues)) { 1796 $newValues = $bestFitExponential->getXValues(); 1797 } 1798 1799 $returnArray = array(); 1800 foreach ($newValues as $xValue) { 1801 $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue); 1802 } 1803 1804 return $returnArray; 1805 } 1806 1807 1808 /** 1809 * HARMEAN 1810 * 1811 * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the 1812 * arithmetic mean of reciprocals. 1813 * 1814 * Excel Function: 1815 * HARMEAN(value1[,value2[, ...]]) 1816 * 1817 * @access public 1818 * @category Statistical Functions 1819 * @param mixed $arg,... Data values 1820 * @return float 1821 */ 1822 public static function HARMEAN() 1823 { 1824 // Return value 1825 $returnValue = PHPExcel_Calculation_Functions::NA(); 1826 1827 // Loop through arguments 1828 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 1829 if (self::MIN($aArgs) < 0) { 1830 return PHPExcel_Calculation_Functions::NaN(); 1831 } 1832 $aCount = 0; 1833 foreach ($aArgs as $arg) { 1834 // Is it a numeric value? 1835 if ((is_numeric($arg)) && (!is_string($arg))) { 1836 if ($arg <= 0) { 1837 return PHPExcel_Calculation_Functions::NaN(); 1838 } 1839 if (is_null($returnValue)) { 1840 $returnValue = (1 / $arg); 1841 } else { 1842 $returnValue += (1 / $arg); 1843 } 1844 ++$aCount; 1845 } 1846 } 1847 1848 // Return 1849 if ($aCount > 0) { 1850 return 1 / ($returnValue / $aCount); 1851 } else { 1852 return $returnValue; 1853 } 1854 } 1855 1856 1857 /** 1858 * HYPGEOMDIST 1859 * 1860 * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of 1861 * sample successes, given the sample size, population successes, and population size. 1862 * 1863 * @param float $sampleSuccesses Number of successes in the sample 1864 * @param float $sampleNumber Size of the sample 1865 * @param float $populationSuccesses Number of successes in the population 1866 * @param float $populationNumber Population size 1867 * @return float 1868 * 1869 */ 1870 public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) 1871 { 1872 $sampleSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleSuccesses)); 1873 $sampleNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleNumber)); 1874 $populationSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationSuccesses)); 1875 $populationNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationNumber)); 1876 1877 if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) { 1878 if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) { 1879 return PHPExcel_Calculation_Functions::NaN(); 1880 } 1881 if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) { 1882 return PHPExcel_Calculation_Functions::NaN(); 1883 } 1884 if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) { 1885 return PHPExcel_Calculation_Functions::NaN(); 1886 } 1887 return PHPExcel_Calculation_MathTrig::COMBIN($populationSuccesses, $sampleSuccesses) * 1888 PHPExcel_Calculation_MathTrig::COMBIN($populationNumber - $populationSuccesses, $sampleNumber - $sampleSuccesses) / 1889 PHPExcel_Calculation_MathTrig::COMBIN($populationNumber, $sampleNumber); 1890 } 1891 return PHPExcel_Calculation_Functions::VALUE(); 1892 } 1893 1894 1895 /** 1896 * INTERCEPT 1897 * 1898 * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. 1899 * 1900 * @param array of mixed Data Series Y 1901 * @param array of mixed Data Series X 1902 * @return float 1903 */ 1904 public static function INTERCEPT($yValues, $xValues) 1905 { 1906 if (!self::checkTrendArrays($yValues, $xValues)) { 1907 return PHPExcel_Calculation_Functions::VALUE(); 1908 } 1909 $yValueCount = count($yValues); 1910 $xValueCount = count($xValues); 1911 1912 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1913 return PHPExcel_Calculation_Functions::NA(); 1914 } elseif ($yValueCount == 1) { 1915 return PHPExcel_Calculation_Functions::DIV0(); 1916 } 1917 1918 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 1919 return $bestFitLinear->getIntersect(); 1920 } 1921 1922 1923 /** 1924 * KURT 1925 * 1926 * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness 1927 * or flatness of a distribution compared with the normal distribution. Positive 1928 * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a 1929 * relatively flat distribution. 1930 * 1931 * @param array Data Series 1932 * @return float 1933 */ 1934 public static function KURT() 1935 { 1936 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 1937 $mean = self::AVERAGE($aArgs); 1938 $stdDev = self::STDEV($aArgs); 1939 1940 if ($stdDev > 0) { 1941 $count = $summer = 0; 1942 // Loop through arguments 1943 foreach ($aArgs as $k => $arg) { 1944 if ((is_bool($arg)) && 1945 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 1946 } else { 1947 // Is it a numeric value? 1948 if ((is_numeric($arg)) && (!is_string($arg))) { 1949 $summer += pow((($arg - $mean) / $stdDev), 4); 1950 ++$count; 1951 } 1952 } 1953 } 1954 1955 // Return 1956 if ($count > 3) { 1957 return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1, 2) / (($count-2) * ($count-3))); 1958 } 1959 } 1960 return PHPExcel_Calculation_Functions::DIV0(); 1961 } 1962 1963 1964 /** 1965 * LARGE 1966 * 1967 * Returns the nth largest value in a data set. You can use this function to 1968 * select a value based on its relative standing. 1969 * 1970 * Excel Function: 1971 * LARGE(value1[,value2[, ...]],entry) 1972 * 1973 * @access public 1974 * @category Statistical Functions 1975 * @param mixed $arg,... Data values 1976 * @param int $entry Position (ordered from the largest) in the array or range of data to return 1977 * @return float 1978 * 1979 */ 1980 public static function LARGE() 1981 { 1982 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 1983 1984 // Calculate 1985 $entry = floor(array_pop($aArgs)); 1986 1987 if ((is_numeric($entry)) && (!is_string($entry))) { 1988 $mArgs = array(); 1989 foreach ($aArgs as $arg) { 1990 // Is it a numeric value? 1991 if ((is_numeric($arg)) && (!is_string($arg))) { 1992 $mArgs[] = $arg; 1993 } 1994 } 1995 $count = self::COUNT($mArgs); 1996 $entry = floor(--$entry); 1997 if (($entry < 0) || ($entry >= $count) || ($count == 0)) { 1998 return PHPExcel_Calculation_Functions::NaN(); 1999 } 2000 rsort($mArgs); 2001 return $mArgs[$entry]; 2002 } 2003 return PHPExcel_Calculation_Functions::VALUE(); 2004 } 2005 2006 2007 /** 2008 * LINEST 2009 * 2010 * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, 2011 * and then returns an array that describes the line. 2012 * 2013 * @param array of mixed Data Series Y 2014 * @param array of mixed Data Series X 2015 * @param boolean A logical value specifying whether to force the intersect to equal 0. 2016 * @param boolean A logical value specifying whether to return additional regression statistics. 2017 * @return array 2018 */ 2019 public static function LINEST($yValues, $xValues = null, $const = true, $stats = false) 2020 { 2021 $const = (is_null($const)) ? true : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const); 2022 $stats = (is_null($stats)) ? false : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats); 2023 if (is_null($xValues)) { 2024 $xValues = range(1, count(PHPExcel_Calculation_Functions::flattenArray($yValues))); 2025 } 2026 2027 if (!self::checkTrendArrays($yValues, $xValues)) { 2028 return PHPExcel_Calculation_Functions::VALUE(); 2029 } 2030 $yValueCount = count($yValues); 2031 $xValueCount = count($xValues); 2032 2033 2034 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2035 return PHPExcel_Calculation_Functions::NA(); 2036 } elseif ($yValueCount == 1) { 2037 return 0; 2038 } 2039 2040 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues, $const); 2041 if ($stats) { 2042 return array( 2043 array( 2044 $bestFitLinear->getSlope(), 2045 $bestFitLinear->getSlopeSE(), 2046 $bestFitLinear->getGoodnessOfFit(), 2047 $bestFitLinear->getF(), 2048 $bestFitLinear->getSSRegression(), 2049 ), 2050 array( 2051 $bestFitLinear->getIntersect(), 2052 $bestFitLinear->getIntersectSE(), 2053 $bestFitLinear->getStdevOfResiduals(), 2054 $bestFitLinear->getDFResiduals(), 2055 $bestFitLinear->getSSResiduals() 2056 ) 2057 ); 2058 } else { 2059 return array( 2060 $bestFitLinear->getSlope(), 2061 $bestFitLinear->getIntersect() 2062 ); 2063 } 2064 } 2065 2066 2067 /** 2068 * LOGEST 2069 * 2070 * Calculates an exponential curve that best fits the X and Y data series, 2071 * and then returns an array that describes the line. 2072 * 2073 * @param array of mixed Data Series Y 2074 * @param array of mixed Data Series X 2075 * @param boolean A logical value specifying whether to force the intersect to equal 0. 2076 * @param boolean A logical value specifying whether to return additional regression statistics. 2077 * @return array 2078 */ 2079 public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false) 2080 { 2081 $const = (is_null($const)) ? true : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const); 2082 $stats = (is_null($stats)) ? false : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats); 2083 if (is_null($xValues)) { 2084 $xValues = range(1, count(PHPExcel_Calculation_Functions::flattenArray($yValues))); 2085 } 2086 2087 if (!self::checkTrendArrays($yValues, $xValues)) { 2088 return PHPExcel_Calculation_Functions::VALUE(); 2089 } 2090 $yValueCount = count($yValues); 2091 $xValueCount = count($xValues); 2092 2093 foreach ($yValues as $value) { 2094 if ($value <= 0.0) { 2095 return PHPExcel_Calculation_Functions::NaN(); 2096 } 2097 } 2098 2099 2100 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2101 return PHPExcel_Calculation_Functions::NA(); 2102 } elseif ($yValueCount == 1) { 2103 return 1; 2104 } 2105 2106 $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL, $yValues, $xValues, $const); 2107 if ($stats) { 2108 return array( 2109 array( 2110 $bestFitExponential->getSlope(), 2111 $bestFitExponential->getSlopeSE(), 2112 $bestFitExponential->getGoodnessOfFit(), 2113 $bestFitExponential->getF(), 2114 $bestFitExponential->getSSRegression(), 2115 ), 2116 array( 2117 $bestFitExponential->getIntersect(), 2118 $bestFitExponential->getIntersectSE(), 2119 $bestFitExponential->getStdevOfResiduals(), 2120 $bestFitExponential->getDFResiduals(), 2121 $bestFitExponential->getSSResiduals() 2122 ) 2123 ); 2124 } else { 2125 return array( 2126 $bestFitExponential->getSlope(), 2127 $bestFitExponential->getIntersect() 2128 ); 2129 } 2130 } 2131 2132 2133 /** 2134 * LOGINV 2135 * 2136 * Returns the inverse of the normal cumulative distribution 2137 * 2138 * @param float $probability 2139 * @param float $mean 2140 * @param float $stdDev 2141 * @return float 2142 * 2143 * @todo Try implementing P J Acklam's refinement algorithm for greater 2144 * accuracy if I can get my head round the mathematics 2145 * (as described at) http://home.online.no/~pjacklam/notes/invnorm/ 2146 */ 2147 public static function LOGINV($probability, $mean, $stdDev) 2148 { 2149 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 2150 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 2151 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 2152 2153 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2154 if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) { 2155 return PHPExcel_Calculation_Functions::NaN(); 2156 } 2157 return exp($mean + $stdDev * self::NORMSINV($probability)); 2158 } 2159 return PHPExcel_Calculation_Functions::VALUE(); 2160 } 2161 2162 2163 /** 2164 * LOGNORMDIST 2165 * 2166 * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed 2167 * with parameters mean and standard_dev. 2168 * 2169 * @param float $value 2170 * @param float $mean 2171 * @param float $stdDev 2172 * @return float 2173 */ 2174 public static function LOGNORMDIST($value, $mean, $stdDev) 2175 { 2176 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2177 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 2178 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 2179 2180 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2181 if (($value <= 0) || ($stdDev <= 0)) { 2182 return PHPExcel_Calculation_Functions::NaN(); 2183 } 2184 return self::NORMSDIST((log($value) - $mean) / $stdDev); 2185 } 2186 return PHPExcel_Calculation_Functions::VALUE(); 2187 } 2188 2189 2190 /** 2191 * MAX 2192 * 2193 * MAX returns the value of the element of the values passed that has the highest value, 2194 * with negative numbers considered smaller than positive numbers. 2195 * 2196 * Excel Function: 2197 * MAX(value1[,value2[, ...]]) 2198 * 2199 * @access public 2200 * @category Statistical Functions 2201 * @param mixed $arg,... Data values 2202 * @return float 2203 */ 2204 public static function MAX() 2205 { 2206 $returnValue = null; 2207 2208 // Loop through arguments 2209 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2210 foreach ($aArgs as $arg) { 2211 // Is it a numeric value? 2212 if ((is_numeric($arg)) && (!is_string($arg))) { 2213 if ((is_null($returnValue)) || ($arg > $returnValue)) { 2214 $returnValue = $arg; 2215 } 2216 } 2217 } 2218 2219 if (is_null($returnValue)) { 2220 return 0; 2221 } 2222 return $returnValue; 2223 } 2224 2225 2226 /** 2227 * MAXA 2228 * 2229 * Returns the greatest value in a list of arguments, including numbers, text, and logical values 2230 * 2231 * Excel Function: 2232 * MAXA(value1[,value2[, ...]]) 2233 * 2234 * @access public 2235 * @category Statistical Functions 2236 * @param mixed $arg,... Data values 2237 * @return float 2238 */ 2239 public static function MAXA() 2240 { 2241 $returnValue = null; 2242 2243 // Loop through arguments 2244 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2245 foreach ($aArgs as $arg) { 2246 // Is it a numeric value? 2247 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 2248 if (is_bool($arg)) { 2249 $arg = (integer) $arg; 2250 } elseif (is_string($arg)) { 2251 $arg = 0; 2252 } 2253 if ((is_null($returnValue)) || ($arg > $returnValue)) { 2254 $returnValue = $arg; 2255 } 2256 } 2257 } 2258 2259 if (is_null($returnValue)) { 2260 return 0; 2261 } 2262 return $returnValue; 2263 } 2264 2265 2266 /** 2267 * MAXIF 2268 * 2269 * Counts the maximum value within a range of cells that contain numbers within the list of arguments 2270 * 2271 * Excel Function: 2272 * MAXIF(value1[,value2[, ...]],condition) 2273 * 2274 * @access public 2275 * @category Mathematical and Trigonometric Functions 2276 * @param mixed $arg,... Data values 2277 * @param string $condition The criteria that defines which cells will be checked. 2278 * @return float 2279 */ 2280 public static function MAXIF($aArgs, $condition, $sumArgs = array()) 2281 { 2282 $returnValue = null; 2283 2284 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 2285 $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); 2286 if (empty($sumArgs)) { 2287 $sumArgs = $aArgs; 2288 } 2289 $condition = PHPExcel_Calculation_Functions::ifCondition($condition); 2290 // Loop through arguments 2291 foreach ($aArgs as $key => $arg) { 2292 if (!is_numeric($arg)) { 2293 $arg = PHPExcel_Calculation::wrapResult(strtoupper($arg)); 2294 } 2295 $testCondition = '='.$arg.$condition; 2296 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 2297 if ((is_null($returnValue)) || ($arg > $returnValue)) { 2298 $returnValue = $arg; 2299 } 2300 } 2301 } 2302 2303 return $returnValue; 2304 } 2305 2306 /** 2307 * MEDIAN 2308 * 2309 * Returns the median of the given numbers. The median is the number in the middle of a set of numbers. 2310 * 2311 * Excel Function: 2312 * MEDIAN(value1[,value2[, ...]]) 2313 * 2314 * @access public 2315 * @category Statistical Functions 2316 * @param mixed $arg,... Data values 2317 * @return float 2318 */ 2319 public static function MEDIAN() 2320 { 2321 $returnValue = PHPExcel_Calculation_Functions::NaN(); 2322 2323 $mArgs = array(); 2324 // Loop through arguments 2325 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2326 foreach ($aArgs as $arg) { 2327 // Is it a numeric value? 2328 if ((is_numeric($arg)) && (!is_string($arg))) { 2329 $mArgs[] = $arg; 2330 } 2331 } 2332 2333 $mValueCount = count($mArgs); 2334 if ($mValueCount > 0) { 2335 sort($mArgs, SORT_NUMERIC); 2336 $mValueCount = $mValueCount / 2; 2337 if ($mValueCount == floor($mValueCount)) { 2338 $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2; 2339 } else { 2340 $mValueCount == floor($mValueCount); 2341 $returnValue = $mArgs[$mValueCount]; 2342 } 2343 } 2344 2345 return $returnValue; 2346 } 2347 2348 2349 /** 2350 * MIN 2351 * 2352 * MIN returns the value of the element of the values passed that has the smallest value, 2353 * with negative numbers considered smaller than positive numbers. 2354 * 2355 * Excel Function: 2356 * MIN(value1[,value2[, ...]]) 2357 * 2358 * @access public 2359 * @category Statistical Functions 2360 * @param mixed $arg,... Data values 2361 * @return float 2362 */ 2363 public static function MIN() 2364 { 2365 $returnValue = null; 2366 2367 // Loop through arguments 2368 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2369 foreach ($aArgs as $arg) { 2370 // Is it a numeric value? 2371 if ((is_numeric($arg)) && (!is_string($arg))) { 2372 if ((is_null($returnValue)) || ($arg < $returnValue)) { 2373 $returnValue = $arg; 2374 } 2375 } 2376 } 2377 2378 if (is_null($returnValue)) { 2379 return 0; 2380 } 2381 return $returnValue; 2382 } 2383 2384 2385 /** 2386 * MINA 2387 * 2388 * Returns the smallest value in a list of arguments, including numbers, text, and logical values 2389 * 2390 * Excel Function: 2391 * MINA(value1[,value2[, ...]]) 2392 * 2393 * @access public 2394 * @category Statistical Functions 2395 * @param mixed $arg,... Data values 2396 * @return float 2397 */ 2398 public static function MINA() 2399 { 2400 $returnValue = null; 2401 2402 // Loop through arguments 2403 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2404 foreach ($aArgs as $arg) { 2405 // Is it a numeric value? 2406 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 2407 if (is_bool($arg)) { 2408 $arg = (integer) $arg; 2409 } elseif (is_string($arg)) { 2410 $arg = 0; 2411 } 2412 if ((is_null($returnValue)) || ($arg < $returnValue)) { 2413 $returnValue = $arg; 2414 } 2415 } 2416 } 2417 2418 if (is_null($returnValue)) { 2419 return 0; 2420 } 2421 return $returnValue; 2422 } 2423 2424 2425 /** 2426 * MINIF 2427 * 2428 * Returns the minimum value within a range of cells that contain numbers within the list of arguments 2429 * 2430 * Excel Function: 2431 * MINIF(value1[,value2[, ...]],condition) 2432 * 2433 * @access public 2434 * @category Mathematical and Trigonometric Functions 2435 * @param mixed $arg,... Data values 2436 * @param string $condition The criteria that defines which cells will be checked. 2437 * @return float 2438 */ 2439 public static function MINIF($aArgs, $condition, $sumArgs = array()) 2440 { 2441 $returnValue = null; 2442 2443 $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); 2444 $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); 2445 if (empty($sumArgs)) { 2446 $sumArgs = $aArgs; 2447 } 2448 $condition = PHPExcel_Calculation_Functions::ifCondition($condition); 2449 // Loop through arguments 2450 foreach ($aArgs as $key => $arg) { 2451 if (!is_numeric($arg)) { 2452 $arg = PHPExcel_Calculation::wrapResult(strtoupper($arg)); 2453 } 2454 $testCondition = '='.$arg.$condition; 2455 if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 2456 if ((is_null($returnValue)) || ($arg < $returnValue)) { 2457 $returnValue = $arg; 2458 } 2459 } 2460 } 2461 2462 return $returnValue; 2463 } 2464 2465 2466 // 2467 // Special variant of array_count_values that isn't limited to strings and integers, 2468 // but can work with floating point numbers as values 2469 // 2470 private static function modeCalc($data) 2471 { 2472 $frequencyArray = array(); 2473 foreach ($data as $datum) { 2474 $found = false; 2475 foreach ($frequencyArray as $key => $value) { 2476 if ((string) $value['value'] == (string) $datum) { 2477 ++$frequencyArray[$key]['frequency']; 2478 $found = true; 2479 break; 2480 } 2481 } 2482 if (!$found) { 2483 $frequencyArray[] = array( 2484 'value' => $datum, 2485 'frequency' => 1 2486 ); 2487 } 2488 } 2489 2490 foreach ($frequencyArray as $key => $value) { 2491 $frequencyList[$key] = $value['frequency']; 2492 $valueList[$key] = $value['value']; 2493 } 2494 array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray); 2495 2496 if ($frequencyArray[0]['frequency'] == 1) { 2497 return PHPExcel_Calculation_Functions::NA(); 2498 } 2499 return $frequencyArray[0]['value']; 2500 } 2501 2502 2503 /** 2504 * MODE 2505 * 2506 * Returns the most frequently occurring, or repetitive, value in an array or range of data 2507 * 2508 * Excel Function: 2509 * MODE(value1[,value2[, ...]]) 2510 * 2511 * @access public 2512 * @category Statistical Functions 2513 * @param mixed $arg,... Data values 2514 * @return float 2515 */ 2516 public static function MODE() 2517 { 2518 $returnValue = PHPExcel_Calculation_Functions::NA(); 2519 2520 // Loop through arguments 2521 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2522 2523 $mArgs = array(); 2524 foreach ($aArgs as $arg) { 2525 // Is it a numeric value? 2526 if ((is_numeric($arg)) && (!is_string($arg))) { 2527 $mArgs[] = $arg; 2528 } 2529 } 2530 2531 if (!empty($mArgs)) { 2532 return self::modeCalc($mArgs); 2533 } 2534 2535 return $returnValue; 2536 } 2537 2538 2539 /** 2540 * NEGBINOMDIST 2541 * 2542 * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that 2543 * there will be number_f failures before the number_s-th success, when the constant 2544 * probability of a success is probability_s. This function is similar to the binomial 2545 * distribution, except that the number of successes is fixed, and the number of trials is 2546 * variable. Like the binomial, trials are assumed to be independent. 2547 * 2548 * @param float $failures Number of Failures 2549 * @param float $successes Threshold number of Successes 2550 * @param float $probability Probability of success on each trial 2551 * @return float 2552 * 2553 */ 2554 public static function NEGBINOMDIST($failures, $successes, $probability) 2555 { 2556 $failures = floor(PHPExcel_Calculation_Functions::flattenSingleValue($failures)); 2557 $successes = floor(PHPExcel_Calculation_Functions::flattenSingleValue($successes)); 2558 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 2559 2560 if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) { 2561 if (($failures < 0) || ($successes < 1)) { 2562 return PHPExcel_Calculation_Functions::NaN(); 2563 } elseif (($probability < 0) || ($probability > 1)) { 2564 return PHPExcel_Calculation_Functions::NaN(); 2565 } 2566 if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) { 2567 if (($failures + $successes - 1) <= 0) { 2568 return PHPExcel_Calculation_Functions::NaN(); 2569 } 2570 } 2571 return (PHPExcel_Calculation_MathTrig::COMBIN($failures + $successes - 1, $successes - 1)) * (pow($probability, $successes)) * (pow(1 - $probability, $failures)); 2572 } 2573 return PHPExcel_Calculation_Functions::VALUE(); 2574 } 2575 2576 2577 /** 2578 * NORMDIST 2579 * 2580 * Returns the normal distribution for the specified mean and standard deviation. This 2581 * function has a very wide range of applications in statistics, including hypothesis 2582 * testing. 2583 * 2584 * @param float $value 2585 * @param float $mean Mean Value 2586 * @param float $stdDev Standard Deviation 2587 * @param boolean $cumulative 2588 * @return float 2589 * 2590 */ 2591 public static function NORMDIST($value, $mean, $stdDev, $cumulative) 2592 { 2593 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2594 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 2595 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 2596 2597 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2598 if ($stdDev < 0) { 2599 return PHPExcel_Calculation_Functions::NaN(); 2600 } 2601 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 2602 if ($cumulative) { 2603 return 0.5 * (1 + PHPExcel_Calculation_Engineering::erfVal(($value - $mean) / ($stdDev * sqrt(2)))); 2604 } else { 2605 return (1 / (SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean, 2) / (2 * ($stdDev * $stdDev)))); 2606 } 2607 } 2608 } 2609 return PHPExcel_Calculation_Functions::VALUE(); 2610 } 2611 2612 2613 /** 2614 * NORMINV 2615 * 2616 * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. 2617 * 2618 * @param float $value 2619 * @param float $mean Mean Value 2620 * @param float $stdDev Standard Deviation 2621 * @return float 2622 * 2623 */ 2624 public static function NORMINV($probability, $mean, $stdDev) 2625 { 2626 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 2627 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 2628 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 2629 2630 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2631 if (($probability < 0) || ($probability > 1)) { 2632 return PHPExcel_Calculation_Functions::NaN(); 2633 } 2634 if ($stdDev < 0) { 2635 return PHPExcel_Calculation_Functions::NaN(); 2636 } 2637 return (self::inverseNcdf($probability) * $stdDev) + $mean; 2638 } 2639 return PHPExcel_Calculation_Functions::VALUE(); 2640 } 2641 2642 2643 /** 2644 * NORMSDIST 2645 * 2646 * Returns the standard normal cumulative distribution function. The distribution has 2647 * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a 2648 * table of standard normal curve areas. 2649 * 2650 * @param float $value 2651 * @return float 2652 */ 2653 public static function NORMSDIST($value) 2654 { 2655 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2656 2657 return self::NORMDIST($value, 0, 1, true); 2658 } 2659 2660 2661 /** 2662 * NORMSINV 2663 * 2664 * Returns the inverse of the standard normal cumulative distribution 2665 * 2666 * @param float $value 2667 * @return float 2668 */ 2669 public static function NORMSINV($value) 2670 { 2671 return self::NORMINV($value, 0, 1); 2672 } 2673 2674 2675 /** 2676 * PERCENTILE 2677 * 2678 * Returns the nth percentile of values in a range.. 2679 * 2680 * Excel Function: 2681 * PERCENTILE(value1[,value2[, ...]],entry) 2682 * 2683 * @access public 2684 * @category Statistical Functions 2685 * @param mixed $arg,... Data values 2686 * @param float $entry Percentile value in the range 0..1, inclusive. 2687 * @return float 2688 */ 2689 public static function PERCENTILE() 2690 { 2691 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2692 2693 // Calculate 2694 $entry = array_pop($aArgs); 2695 2696 if ((is_numeric($entry)) && (!is_string($entry))) { 2697 if (($entry < 0) || ($entry > 1)) { 2698 return PHPExcel_Calculation_Functions::NaN(); 2699 } 2700 $mArgs = array(); 2701 foreach ($aArgs as $arg) { 2702 // Is it a numeric value? 2703 if ((is_numeric($arg)) && (!is_string($arg))) { 2704 $mArgs[] = $arg; 2705 } 2706 } 2707 $mValueCount = count($mArgs); 2708 if ($mValueCount > 0) { 2709 sort($mArgs); 2710 $count = self::COUNT($mArgs); 2711 $index = $entry * ($count-1); 2712 $iBase = floor($index); 2713 if ($index == $iBase) { 2714 return $mArgs[$index]; 2715 } else { 2716 $iNext = $iBase + 1; 2717 $iProportion = $index - $iBase; 2718 return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ; 2719 } 2720 } 2721 } 2722 return PHPExcel_Calculation_Functions::VALUE(); 2723 } 2724 2725 2726 /** 2727 * PERCENTRANK 2728 * 2729 * Returns the rank of a value in a data set as a percentage of the data set. 2730 * 2731 * @param array of number An array of, or a reference to, a list of numbers. 2732 * @param number The number whose rank you want to find. 2733 * @param number The number of significant digits for the returned percentage value. 2734 * @return float 2735 */ 2736 public static function PERCENTRANK($valueSet, $value, $significance = 3) 2737 { 2738 $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet); 2739 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2740 $significance = (is_null($significance)) ? 3 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($significance); 2741 2742 foreach ($valueSet as $key => $valueEntry) { 2743 if (!is_numeric($valueEntry)) { 2744 unset($valueSet[$key]); 2745 } 2746 } 2747 sort($valueSet, SORT_NUMERIC); 2748 $valueCount = count($valueSet); 2749 if ($valueCount == 0) { 2750 return PHPExcel_Calculation_Functions::NaN(); 2751 } 2752 2753 $valueAdjustor = $valueCount - 1; 2754 if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) { 2755 return PHPExcel_Calculation_Functions::NA(); 2756 } 2757 2758 $pos = array_search($value, $valueSet); 2759 if ($pos === false) { 2760 $pos = 0; 2761 $testValue = $valueSet[0]; 2762 while ($testValue < $value) { 2763 $testValue = $valueSet[++$pos]; 2764 } 2765 --$pos; 2766 $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos])); 2767 } 2768 2769 return round($pos / $valueAdjustor, $significance); 2770 } 2771 2772 2773 /** 2774 * PERMUT 2775 * 2776 * Returns the number of permutations for a given number of objects that can be 2777 * selected from number objects. A permutation is any set or subset of objects or 2778 * events where internal order is significant. Permutations are different from 2779 * combinations, for which the internal order is not significant. Use this function 2780 * for lottery-style probability calculations. 2781 * 2782 * @param int $numObjs Number of different objects 2783 * @param int $numInSet Number of objects in each permutation 2784 * @return int Number of permutations 2785 */ 2786 public static function PERMUT($numObjs, $numInSet) 2787 { 2788 $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs); 2789 $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet); 2790 2791 if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { 2792 $numInSet = floor($numInSet); 2793 if ($numObjs < $numInSet) { 2794 return PHPExcel_Calculation_Functions::NaN(); 2795 } 2796 return round(PHPExcel_Calculation_MathTrig::FACT($numObjs) / PHPExcel_Calculation_MathTrig::FACT($numObjs - $numInSet)); 2797 } 2798 return PHPExcel_Calculation_Functions::VALUE(); 2799 } 2800 2801 2802 /** 2803 * POISSON 2804 * 2805 * Returns the Poisson distribution. A common application of the Poisson distribution 2806 * is predicting the number of events over a specific time, such as the number of 2807 * cars arriving at a toll plaza in 1 minute. 2808 * 2809 * @param float $value 2810 * @param float $mean Mean Value 2811 * @param boolean $cumulative 2812 * @return float 2813 * 2814 */ 2815 public static function POISSON($value, $mean, $cumulative) 2816 { 2817 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2818 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 2819 2820 if ((is_numeric($value)) && (is_numeric($mean))) { 2821 if (($value < 0) || ($mean <= 0)) { 2822 return PHPExcel_Calculation_Functions::NaN(); 2823 } 2824 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 2825 if ($cumulative) { 2826 $summer = 0; 2827 for ($i = 0; $i <= floor($value); ++$i) { 2828 $summer += pow($mean, $i) / PHPExcel_Calculation_MathTrig::FACT($i); 2829 } 2830 return exp(0-$mean) * $summer; 2831 } else { 2832 return (exp(0-$mean) * pow($mean, $value)) / PHPExcel_Calculation_MathTrig::FACT($value); 2833 } 2834 } 2835 } 2836 return PHPExcel_Calculation_Functions::VALUE(); 2837 } 2838 2839 2840 /** 2841 * QUARTILE 2842 * 2843 * Returns the quartile of a data set. 2844 * 2845 * Excel Function: 2846 * QUARTILE(value1[,value2[, ...]],entry) 2847 * 2848 * @access public 2849 * @category Statistical Functions 2850 * @param mixed $arg,... Data values 2851 * @param int $entry Quartile value in the range 1..3, inclusive. 2852 * @return float 2853 */ 2854 public static function QUARTILE() 2855 { 2856 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 2857 2858 // Calculate 2859 $entry = floor(array_pop($aArgs)); 2860 2861 if ((is_numeric($entry)) && (!is_string($entry))) { 2862 $entry /= 4; 2863 if (($entry < 0) || ($entry > 1)) { 2864 return PHPExcel_Calculation_Functions::NaN(); 2865 } 2866 return self::PERCENTILE($aArgs, $entry); 2867 } 2868 return PHPExcel_Calculation_Functions::VALUE(); 2869 } 2870 2871 2872 /** 2873 * RANK 2874 * 2875 * Returns the rank of a number in a list of numbers. 2876 * 2877 * @param number The number whose rank you want to find. 2878 * @param array of number An array of, or a reference to, a list of numbers. 2879 * @param mixed Order to sort the values in the value set 2880 * @return float 2881 */ 2882 public static function RANK($value, $valueSet, $order = 0) 2883 { 2884 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 2885 $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet); 2886 $order = (is_null($order)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($order); 2887 2888 foreach ($valueSet as $key => $valueEntry) { 2889 if (!is_numeric($valueEntry)) { 2890 unset($valueSet[$key]); 2891 } 2892 } 2893 2894 if ($order == 0) { 2895 rsort($valueSet, SORT_NUMERIC); 2896 } else { 2897 sort($valueSet, SORT_NUMERIC); 2898 } 2899 $pos = array_search($value, $valueSet); 2900 if ($pos === false) { 2901 return PHPExcel_Calculation_Functions::NA(); 2902 } 2903 2904 return ++$pos; 2905 } 2906 2907 2908 /** 2909 * RSQ 2910 * 2911 * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. 2912 * 2913 * @param array of mixed Data Series Y 2914 * @param array of mixed Data Series X 2915 * @return float 2916 */ 2917 public static function RSQ($yValues, $xValues) 2918 { 2919 if (!self::checkTrendArrays($yValues, $xValues)) { 2920 return PHPExcel_Calculation_Functions::VALUE(); 2921 } 2922 $yValueCount = count($yValues); 2923 $xValueCount = count($xValues); 2924 2925 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2926 return PHPExcel_Calculation_Functions::NA(); 2927 } elseif ($yValueCount == 1) { 2928 return PHPExcel_Calculation_Functions::DIV0(); 2929 } 2930 2931 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 2932 return $bestFitLinear->getGoodnessOfFit(); 2933 } 2934 2935 2936 /** 2937 * SKEW 2938 * 2939 * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry 2940 * of a distribution around its mean. Positive skewness indicates a distribution with an 2941 * asymmetric tail extending toward more positive values. Negative skewness indicates a 2942 * distribution with an asymmetric tail extending toward more negative values. 2943 * 2944 * @param array Data Series 2945 * @return float 2946 */ 2947 public static function SKEW() 2948 { 2949 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 2950 $mean = self::AVERAGE($aArgs); 2951 $stdDev = self::STDEV($aArgs); 2952 2953 $count = $summer = 0; 2954 // Loop through arguments 2955 foreach ($aArgs as $k => $arg) { 2956 if ((is_bool($arg)) && 2957 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 2958 } else { 2959 // Is it a numeric value? 2960 if ((is_numeric($arg)) && (!is_string($arg))) { 2961 $summer += pow((($arg - $mean) / $stdDev), 3); 2962 ++$count; 2963 } 2964 } 2965 } 2966 2967 if ($count > 2) { 2968 return $summer * ($count / (($count-1) * ($count-2))); 2969 } 2970 return PHPExcel_Calculation_Functions::DIV0(); 2971 } 2972 2973 2974 /** 2975 * SLOPE 2976 * 2977 * Returns the slope of the linear regression line through data points in known_y's and known_x's. 2978 * 2979 * @param array of mixed Data Series Y 2980 * @param array of mixed Data Series X 2981 * @return float 2982 */ 2983 public static function SLOPE($yValues, $xValues) 2984 { 2985 if (!self::checkTrendArrays($yValues, $xValues)) { 2986 return PHPExcel_Calculation_Functions::VALUE(); 2987 } 2988 $yValueCount = count($yValues); 2989 $xValueCount = count($xValues); 2990 2991 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2992 return PHPExcel_Calculation_Functions::NA(); 2993 } elseif ($yValueCount == 1) { 2994 return PHPExcel_Calculation_Functions::DIV0(); 2995 } 2996 2997 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 2998 return $bestFitLinear->getSlope(); 2999 } 3000 3001 3002 /** 3003 * SMALL 3004 * 3005 * Returns the nth smallest value in a data set. You can use this function to 3006 * select a value based on its relative standing. 3007 * 3008 * Excel Function: 3009 * SMALL(value1[,value2[, ...]],entry) 3010 * 3011 * @access public 3012 * @category Statistical Functions 3013 * @param mixed $arg,... Data values 3014 * @param int $entry Position (ordered from the smallest) in the array or range of data to return 3015 * @return float 3016 */ 3017 public static function SMALL() 3018 { 3019 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 3020 3021 // Calculate 3022 $entry = array_pop($aArgs); 3023 3024 if ((is_numeric($entry)) && (!is_string($entry))) { 3025 $mArgs = array(); 3026 foreach ($aArgs as $arg) { 3027 // Is it a numeric value? 3028 if ((is_numeric($arg)) && (!is_string($arg))) { 3029 $mArgs[] = $arg; 3030 } 3031 } 3032 $count = self::COUNT($mArgs); 3033 $entry = floor(--$entry); 3034 if (($entry < 0) || ($entry >= $count) || ($count == 0)) { 3035 return PHPExcel_Calculation_Functions::NaN(); 3036 } 3037 sort($mArgs); 3038 return $mArgs[$entry]; 3039 } 3040 return PHPExcel_Calculation_Functions::VALUE(); 3041 } 3042 3043 3044 /** 3045 * STANDARDIZE 3046 * 3047 * Returns a normalized value from a distribution characterized by mean and standard_dev. 3048 * 3049 * @param float $value Value to normalize 3050 * @param float $mean Mean Value 3051 * @param float $stdDev Standard Deviation 3052 * @return float Standardized value 3053 */ 3054 public static function STANDARDIZE($value, $mean, $stdDev) 3055 { 3056 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 3057 $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean); 3058 $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev); 3059 3060 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 3061 if ($stdDev <= 0) { 3062 return PHPExcel_Calculation_Functions::NaN(); 3063 } 3064 return ($value - $mean) / $stdDev ; 3065 } 3066 return PHPExcel_Calculation_Functions::VALUE(); 3067 } 3068 3069 3070 /** 3071 * STDEV 3072 * 3073 * Estimates standard deviation based on a sample. The standard deviation is a measure of how 3074 * widely values are dispersed from the average value (the mean). 3075 * 3076 * Excel Function: 3077 * STDEV(value1[,value2[, ...]]) 3078 * 3079 * @access public 3080 * @category Statistical Functions 3081 * @param mixed $arg,... Data values 3082 * @return float 3083 */ 3084 public static function STDEV() 3085 { 3086 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3087 3088 // Return value 3089 $returnValue = null; 3090 3091 $aMean = self::AVERAGE($aArgs); 3092 if (!is_null($aMean)) { 3093 $aCount = -1; 3094 foreach ($aArgs as $k => $arg) { 3095 if ((is_bool($arg)) && 3096 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 3097 $arg = (integer) $arg; 3098 } 3099 // Is it a numeric value? 3100 if ((is_numeric($arg)) && (!is_string($arg))) { 3101 if (is_null($returnValue)) { 3102 $returnValue = pow(($arg - $aMean), 2); 3103 } else { 3104 $returnValue += pow(($arg - $aMean), 2); 3105 } 3106 ++$aCount; 3107 } 3108 } 3109 3110 // Return 3111 if (($aCount > 0) && ($returnValue >= 0)) { 3112 return sqrt($returnValue / $aCount); 3113 } 3114 } 3115 return PHPExcel_Calculation_Functions::DIV0(); 3116 } 3117 3118 3119 /** 3120 * STDEVA 3121 * 3122 * Estimates standard deviation based on a sample, including numbers, text, and logical values 3123 * 3124 * Excel Function: 3125 * STDEVA(value1[,value2[, ...]]) 3126 * 3127 * @access public 3128 * @category Statistical Functions 3129 * @param mixed $arg,... Data values 3130 * @return float 3131 */ 3132 public static function STDEVA() 3133 { 3134 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3135 3136 $returnValue = null; 3137 3138 $aMean = self::AVERAGEA($aArgs); 3139 if (!is_null($aMean)) { 3140 $aCount = -1; 3141 foreach ($aArgs as $k => $arg) { 3142 if ((is_bool($arg)) && 3143 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 3144 } else { 3145 // Is it a numeric value? 3146 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3147 if (is_bool($arg)) { 3148 $arg = (integer) $arg; 3149 } elseif (is_string($arg)) { 3150 $arg = 0; 3151 } 3152 if (is_null($returnValue)) { 3153 $returnValue = pow(($arg - $aMean), 2); 3154 } else { 3155 $returnValue += pow(($arg - $aMean), 2); 3156 } 3157 ++$aCount; 3158 } 3159 } 3160 } 3161 3162 if (($aCount > 0) && ($returnValue >= 0)) { 3163 return sqrt($returnValue / $aCount); 3164 } 3165 } 3166 return PHPExcel_Calculation_Functions::DIV0(); 3167 } 3168 3169 3170 /** 3171 * STDEVP 3172 * 3173 * Calculates standard deviation based on the entire population 3174 * 3175 * Excel Function: 3176 * STDEVP(value1[,value2[, ...]]) 3177 * 3178 * @access public 3179 * @category Statistical Functions 3180 * @param mixed $arg,... Data values 3181 * @return float 3182 */ 3183 public static function STDEVP() 3184 { 3185 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3186 3187 $returnValue = null; 3188 3189 $aMean = self::AVERAGE($aArgs); 3190 if (!is_null($aMean)) { 3191 $aCount = 0; 3192 foreach ($aArgs as $k => $arg) { 3193 if ((is_bool($arg)) && 3194 ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) { 3195 $arg = (integer) $arg; 3196 } 3197 // Is it a numeric value? 3198 if ((is_numeric($arg)) && (!is_string($arg))) { 3199 if (is_null($returnValue)) { 3200 $returnValue = pow(($arg - $aMean), 2); 3201 } else { 3202 $returnValue += pow(($arg - $aMean), 2); 3203 } 3204 ++$aCount; 3205 } 3206 } 3207 3208 if (($aCount > 0) && ($returnValue >= 0)) { 3209 return sqrt($returnValue / $aCount); 3210 } 3211 } 3212 return PHPExcel_Calculation_Functions::DIV0(); 3213 } 3214 3215 3216 /** 3217 * STDEVPA 3218 * 3219 * Calculates standard deviation based on the entire population, including numbers, text, and logical values 3220 * 3221 * Excel Function: 3222 * STDEVPA(value1[,value2[, ...]]) 3223 * 3224 * @access public 3225 * @category Statistical Functions 3226 * @param mixed $arg,... Data values 3227 * @return float 3228 */ 3229 public static function STDEVPA() 3230 { 3231 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3232 3233 $returnValue = null; 3234 3235 $aMean = self::AVERAGEA($aArgs); 3236 if (!is_null($aMean)) { 3237 $aCount = 0; 3238 foreach ($aArgs as $k => $arg) { 3239 if ((is_bool($arg)) && 3240 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 3241 } else { 3242 // Is it a numeric value? 3243 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3244 if (is_bool($arg)) { 3245 $arg = (integer) $arg; 3246 } elseif (is_string($arg)) { 3247 $arg = 0; 3248 } 3249 if (is_null($returnValue)) { 3250 $returnValue = pow(($arg - $aMean), 2); 3251 } else { 3252 $returnValue += pow(($arg - $aMean), 2); 3253 } 3254 ++$aCount; 3255 } 3256 } 3257 } 3258 3259 if (($aCount > 0) && ($returnValue >= 0)) { 3260 return sqrt($returnValue / $aCount); 3261 } 3262 } 3263 return PHPExcel_Calculation_Functions::DIV0(); 3264 } 3265 3266 3267 /** 3268 * STEYX 3269 * 3270 * Returns the standard error of the predicted y-value for each x in the regression. 3271 * 3272 * @param array of mixed Data Series Y 3273 * @param array of mixed Data Series X 3274 * @return float 3275 */ 3276 public static function STEYX($yValues, $xValues) 3277 { 3278 if (!self::checkTrendArrays($yValues, $xValues)) { 3279 return PHPExcel_Calculation_Functions::VALUE(); 3280 } 3281 $yValueCount = count($yValues); 3282 $xValueCount = count($xValues); 3283 3284 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 3285 return PHPExcel_Calculation_Functions::NA(); 3286 } elseif ($yValueCount == 1) { 3287 return PHPExcel_Calculation_Functions::DIV0(); 3288 } 3289 3290 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues); 3291 return $bestFitLinear->getStdevOfResiduals(); 3292 } 3293 3294 3295 /** 3296 * TDIST 3297 * 3298 * Returns the probability of Student's T distribution. 3299 * 3300 * @param float $value Value for the function 3301 * @param float $degrees degrees of freedom 3302 * @param float $tails number of tails (1 or 2) 3303 * @return float 3304 */ 3305 public static function TDIST($value, $degrees, $tails) 3306 { 3307 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 3308 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees)); 3309 $tails = floor(PHPExcel_Calculation_Functions::flattenSingleValue($tails)); 3310 3311 if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) { 3312 if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) { 3313 return PHPExcel_Calculation_Functions::NaN(); 3314 } 3315 // tdist, which finds the probability that corresponds to a given value 3316 // of t with k degrees of freedom. This algorithm is translated from a 3317 // pascal function on p81 of "Statistical Computing in Pascal" by D 3318 // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd: 3319 // London). The above Pascal algorithm is itself a translation of the 3320 // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer 3321 // Laboratory as reported in (among other places) "Applied Statistics 3322 // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis 3323 // Horwood Ltd.; W. Sussex, England). 3324 $tterm = $degrees; 3325 $ttheta = atan2($value, sqrt($tterm)); 3326 $tc = cos($ttheta); 3327 $ts = sin($ttheta); 3328 $tsum = 0; 3329 3330 if (($degrees % 2) == 1) { 3331 $ti = 3; 3332 $tterm = $tc; 3333 } else { 3334 $ti = 2; 3335 $tterm = 1; 3336 } 3337 3338 $tsum = $tterm; 3339 while ($ti < $degrees) { 3340 $tterm *= $tc * $tc * ($ti - 1) / $ti; 3341 $tsum += $tterm; 3342 $ti += 2; 3343 } 3344 $tsum *= $ts; 3345 if (($degrees % 2) == 1) { 3346 $tsum = M_2DIVPI * ($tsum + $ttheta); 3347 } 3348 $tValue = 0.5 * (1 + $tsum); 3349 if ($tails == 1) { 3350 return 1 - abs($tValue); 3351 } else { 3352 return 1 - abs((1 - $tValue) - $tValue); 3353 } 3354 } 3355 return PHPExcel_Calculation_Functions::VALUE(); 3356 } 3357 3358 3359 /** 3360 * TINV 3361 * 3362 * Returns the one-tailed probability of the chi-squared distribution. 3363 * 3364 * @param float $probability Probability for the function 3365 * @param float $degrees degrees of freedom 3366 * @return float 3367 */ 3368 public static function TINV($probability, $degrees) 3369 { 3370 $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); 3371 $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees)); 3372 3373 if ((is_numeric($probability)) && (is_numeric($degrees))) { 3374 $xLo = 100; 3375 $xHi = 0; 3376 3377 $x = $xNew = 1; 3378 $dx = 1; 3379 $i = 0; 3380 3381 while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) { 3382 // Apply Newton-Raphson step 3383 $result = self::TDIST($x, $degrees, 2); 3384 $error = $result - $probability; 3385 if ($error == 0.0) { 3386 $dx = 0; 3387 } elseif ($error < 0.0) { 3388 $xLo = $x; 3389 } else { 3390 $xHi = $x; 3391 } 3392 // Avoid division by zero 3393 if ($result != 0.0) { 3394 $dx = $error / $result; 3395 $xNew = $x - $dx; 3396 } 3397 // If the NR fails to converge (which for example may be the 3398 // case if the initial guess is too rough) we apply a bisection 3399 // step to determine a more narrow interval around the root. 3400 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) { 3401 $xNew = ($xLo + $xHi) / 2; 3402 $dx = $xNew - $x; 3403 } 3404 $x = $xNew; 3405 } 3406 if ($i == MAX_ITERATIONS) { 3407 return PHPExcel_Calculation_Functions::NA(); 3408 } 3409 return round($x, 12); 3410 } 3411 return PHPExcel_Calculation_Functions::VALUE(); 3412 } 3413 3414 3415 /** 3416 * TREND 3417 * 3418 * Returns values along a linear trend 3419 * 3420 * @param array of mixed Data Series Y 3421 * @param array of mixed Data Series X 3422 * @param array of mixed Values of X for which we want to find Y 3423 * @param boolean A logical value specifying whether to force the intersect to equal 0. 3424 * @return array of float 3425 */ 3426 public static function TREND($yValues, $xValues = array(), $newValues = array(), $const = true) 3427 { 3428 $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues); 3429 $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues); 3430 $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues); 3431 $const = (is_null($const)) ? true : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const); 3432 3433 $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR, $yValues, $xValues, $const); 3434 if (empty($newValues)) { 3435 $newValues = $bestFitLinear->getXValues(); 3436 } 3437 3438 $returnArray = array(); 3439 foreach ($newValues as $xValue) { 3440 $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue); 3441 } 3442 3443 return $returnArray; 3444 } 3445 3446 3447 /** 3448 * TRIMMEAN 3449 * 3450 * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean 3451 * taken by excluding a percentage of data points from the top and bottom tails 3452 * of a data set. 3453 * 3454 * Excel Function: 3455 * TRIMEAN(value1[,value2[, ...]], $discard) 3456 * 3457 * @access public 3458 * @category Statistical Functions 3459 * @param mixed $arg,... Data values 3460 * @param float $discard Percentage to discard 3461 * @return float 3462 */ 3463 public static function TRIMMEAN() 3464 { 3465 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 3466 3467 // Calculate 3468 $percent = array_pop($aArgs); 3469 3470 if ((is_numeric($percent)) && (!is_string($percent))) { 3471 if (($percent < 0) || ($percent > 1)) { 3472 return PHPExcel_Calculation_Functions::NaN(); 3473 } 3474 $mArgs = array(); 3475 foreach ($aArgs as $arg) { 3476 // Is it a numeric value? 3477 if ((is_numeric($arg)) && (!is_string($arg))) { 3478 $mArgs[] = $arg; 3479 } 3480 } 3481 $discard = floor(self::COUNT($mArgs) * $percent / 2); 3482 sort($mArgs); 3483 for ($i=0; $i < $discard; ++$i) { 3484 array_pop($mArgs); 3485 array_shift($mArgs); 3486 } 3487 return self::AVERAGE($mArgs); 3488 } 3489 return PHPExcel_Calculation_Functions::VALUE(); 3490 } 3491 3492 3493 /** 3494 * VARFunc 3495 * 3496 * Estimates variance based on a sample. 3497 * 3498 * Excel Function: 3499 * VAR(value1[,value2[, ...]]) 3500 * 3501 * @access public 3502 * @category Statistical Functions 3503 * @param mixed $arg,... Data values 3504 * @return float 3505 */ 3506 public static function VARFunc() 3507 { 3508 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3509 3510 $summerA = $summerB = 0; 3511 3512 // Loop through arguments 3513 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 3514 $aCount = 0; 3515 foreach ($aArgs as $arg) { 3516 if (is_bool($arg)) { 3517 $arg = (integer) $arg; 3518 } 3519 // Is it a numeric value? 3520 if ((is_numeric($arg)) && (!is_string($arg))) { 3521 $summerA += ($arg * $arg); 3522 $summerB += $arg; 3523 ++$aCount; 3524 } 3525 } 3526 3527 if ($aCount > 1) { 3528 $summerA *= $aCount; 3529 $summerB *= $summerB; 3530 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); 3531 } 3532 return $returnValue; 3533 } 3534 3535 3536 /** 3537 * VARA 3538 * 3539 * Estimates variance based on a sample, including numbers, text, and logical values 3540 * 3541 * Excel Function: 3542 * VARA(value1[,value2[, ...]]) 3543 * 3544 * @access public 3545 * @category Statistical Functions 3546 * @param mixed $arg,... Data values 3547 * @return float 3548 */ 3549 public static function VARA() 3550 { 3551 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3552 3553 $summerA = $summerB = 0; 3554 3555 // Loop through arguments 3556 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3557 $aCount = 0; 3558 foreach ($aArgs as $k => $arg) { 3559 if ((is_string($arg)) && 3560 (PHPExcel_Calculation_Functions::isValue($k))) { 3561 return PHPExcel_Calculation_Functions::VALUE(); 3562 } elseif ((is_string($arg)) && 3563 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 3564 } else { 3565 // Is it a numeric value? 3566 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3567 if (is_bool($arg)) { 3568 $arg = (integer) $arg; 3569 } elseif (is_string($arg)) { 3570 $arg = 0; 3571 } 3572 $summerA += ($arg * $arg); 3573 $summerB += $arg; 3574 ++$aCount; 3575 } 3576 } 3577 } 3578 3579 if ($aCount > 1) { 3580 $summerA *= $aCount; 3581 $summerB *= $summerB; 3582 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); 3583 } 3584 return $returnValue; 3585 } 3586 3587 3588 /** 3589 * VARP 3590 * 3591 * Calculates variance based on the entire population 3592 * 3593 * Excel Function: 3594 * VARP(value1[,value2[, ...]]) 3595 * 3596 * @access public 3597 * @category Statistical Functions 3598 * @param mixed $arg,... Data values 3599 * @return float 3600 */ 3601 public static function VARP() 3602 { 3603 // Return value 3604 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3605 3606 $summerA = $summerB = 0; 3607 3608 // Loop through arguments 3609 $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); 3610 $aCount = 0; 3611 foreach ($aArgs as $arg) { 3612 if (is_bool($arg)) { 3613 $arg = (integer) $arg; 3614 } 3615 // Is it a numeric value? 3616 if ((is_numeric($arg)) && (!is_string($arg))) { 3617 $summerA += ($arg * $arg); 3618 $summerB += $arg; 3619 ++$aCount; 3620 } 3621 } 3622 3623 if ($aCount > 0) { 3624 $summerA *= $aCount; 3625 $summerB *= $summerB; 3626 $returnValue = ($summerA - $summerB) / ($aCount * $aCount); 3627 } 3628 return $returnValue; 3629 } 3630 3631 3632 /** 3633 * VARPA 3634 * 3635 * Calculates variance based on the entire population, including numbers, text, and logical values 3636 * 3637 * Excel Function: 3638 * VARPA(value1[,value2[, ...]]) 3639 * 3640 * @access public 3641 * @category Statistical Functions 3642 * @param mixed $arg,... Data values 3643 * @return float 3644 */ 3645 public static function VARPA() 3646 { 3647 $returnValue = PHPExcel_Calculation_Functions::DIV0(); 3648 3649 $summerA = $summerB = 0; 3650 3651 // Loop through arguments 3652 $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()); 3653 $aCount = 0; 3654 foreach ($aArgs as $k => $arg) { 3655 if ((is_string($arg)) && 3656 (PHPExcel_Calculation_Functions::isValue($k))) { 3657 return PHPExcel_Calculation_Functions::VALUE(); 3658 } elseif ((is_string($arg)) && 3659 (!PHPExcel_Calculation_Functions::isMatrixValue($k))) { 3660 } else { 3661 // Is it a numeric value? 3662 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3663 if (is_bool($arg)) { 3664 $arg = (integer) $arg; 3665 } elseif (is_string($arg)) { 3666 $arg = 0; 3667 } 3668 $summerA += ($arg * $arg); 3669 $summerB += $arg; 3670 ++$aCount; 3671 } 3672 } 3673 } 3674 3675 if ($aCount > 0) { 3676 $summerA *= $aCount; 3677 $summerB *= $summerB; 3678 $returnValue = ($summerA - $summerB) / ($aCount * $aCount); 3679 } 3680 return $returnValue; 3681 } 3682 3683 3684 /** 3685 * WEIBULL 3686 * 3687 * Returns the Weibull distribution. Use this distribution in reliability 3688 * analysis, such as calculating a device's mean time to failure. 3689 * 3690 * @param float $value 3691 * @param float $alpha Alpha Parameter 3692 * @param float $beta Beta Parameter 3693 * @param boolean $cumulative 3694 * @return float 3695 * 3696 */ 3697 public static function WEIBULL($value, $alpha, $beta, $cumulative) 3698 { 3699 $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); 3700 $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha); 3701 $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta); 3702 3703 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) { 3704 if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) { 3705 return PHPExcel_Calculation_Functions::NaN(); 3706 } 3707 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 3708 if ($cumulative) { 3709 return 1 - exp(0 - pow($value / $beta, $alpha)); 3710 } else { 3711 return ($alpha / pow($beta, $alpha)) * pow($value, $alpha - 1) * exp(0 - pow($value / $beta, $alpha)); 3712 } 3713 } 3714 } 3715 return PHPExcel_Calculation_Functions::VALUE(); 3716 } 3717 3718 3719 /** 3720 * ZTEST 3721 * 3722 * Returns the Weibull distribution. Use this distribution in reliability 3723 * analysis, such as calculating a device's mean time to failure. 3724 * 3725 * @param float $dataSet 3726 * @param float $m0 Alpha Parameter 3727 * @param float $sigma Beta Parameter 3728 * @param boolean $cumulative 3729 * @return float 3730 * 3731 */ 3732 public static function ZTEST($dataSet, $m0, $sigma = null) 3733 { 3734 $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet); 3735 $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0); 3736 $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma); 3737 3738 if (is_null($sigma)) { 3739 $sigma = self::STDEV($dataSet); 3740 } 3741 $n = count($dataSet); 3742 3743 return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / SQRT($n))); 3744 } 3745 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |