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