Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
- Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
- PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
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 += ($x ** $n / $divisor); 401 } 402 403 return $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 ( 534 (is_bool($arg)) && 535 ((!Functions::isCellValue($k) && (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_EXCEL)) || 536 (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE)) 537 ) { 538 $arg = (int) $arg; 539 } 540 541 return $arg; 542 } 543 544 /** 545 * @param mixed $arg 546 * @param mixed $k 547 * 548 * @return bool 549 */ 550 private static function isAcceptedCountable($arg, $k) 551 { 552 if ( 553 ((is_numeric($arg)) && (!is_string($arg))) || 554 ((is_numeric($arg)) && (!Functions::isCellValue($k)) && 555 (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_GNUMERIC)) 556 ) { 557 return true; 558 } 559 560 return false; 561 } 562 563 /** 564 * AVEDEV. 565 * 566 * Returns the average of the absolute deviations of data points from their mean. 567 * AVEDEV is a measure of the variability in a data set. 568 * 569 * Excel Function: 570 * AVEDEV(value1[,value2[, ...]]) 571 * 572 * @param mixed ...$args Data values 573 * 574 * @return float|string 575 */ 576 public static function AVEDEV(...$args) 577 { 578 $aArgs = Functions::flattenArrayIndexed($args); 579 580 // Return value 581 $returnValue = 0; 582 583 $aMean = self::AVERAGE(...$args); 584 if ($aMean === Functions::DIV0()) { 585 return Functions::NAN(); 586 } elseif ($aMean === Functions::VALUE()) { 587 return Functions::VALUE(); 588 } 589 590 $aCount = 0; 591 foreach ($aArgs as $k => $arg) { 592 $arg = self::testAcceptedBoolean($arg, $k); 593 // Is it a numeric value? 594 // Strings containing numeric values are only counted if they are string literals (not cell values) 595 // and then only in MS Excel and in Open Office, not in Gnumeric 596 if ((is_string($arg)) && (!is_numeric($arg)) && (!Functions::isCellValue($k))) { 597 return Functions::VALUE(); 598 } 599 if (self::isAcceptedCountable($arg, $k)) { 600 $returnValue += abs($arg - $aMean); 601 ++$aCount; 602 } 603 } 604 605 // Return 606 if ($aCount === 0) { 607 return Functions::DIV0(); 608 } 609 610 return $returnValue / $aCount; 611 } 612 613 /** 614 * AVERAGE. 615 * 616 * Returns the average (arithmetic mean) of the arguments 617 * 618 * Excel Function: 619 * AVERAGE(value1[,value2[, ...]]) 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 * @param mixed ...$args Data values 661 * 662 * @return float|string 663 */ 664 public static function AVERAGEA(...$args) 665 { 666 $returnValue = null; 667 668 $aCount = 0; 669 // Loop through arguments 670 foreach (Functions::flattenArrayIndexed($args) as $k => $arg) { 671 if ( 672 (is_bool($arg)) && 673 (!Functions::isMatrixValue($k)) 674 ) { 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 * @param mixed $aArgs Data values 704 * @param string $condition the criteria that defines which cells will be checked 705 * @param mixed[] $averageArgs Data values 706 * 707 * @return float|string 708 */ 709 public static function AVERAGEIF($aArgs, $condition, $averageArgs = []) 710 { 711 $returnValue = 0; 712 713 $aArgs = Functions::flattenArray($aArgs); 714 $averageArgs = Functions::flattenArray($averageArgs); 715 if (empty($averageArgs)) { 716 $averageArgs = $aArgs; 717 } 718 $condition = Functions::ifCondition($condition); 719 $conditionIsNumeric = strpos($condition, '"') === false; 720 721 // Loop through arguments 722 $aCount = 0; 723 foreach ($aArgs as $key => $arg) { 724 if (!is_numeric($arg)) { 725 if ($conditionIsNumeric) { 726 continue; 727 } 728 $arg = Calculation::wrapResult(strtoupper($arg)); 729 } elseif (!$conditionIsNumeric) { 730 continue; 731 } 732 $testCondition = '=' . $arg . $condition; 733 if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 734 $returnValue += $averageArgs[$key]; 735 ++$aCount; 736 } 737 } 738 739 if ($aCount > 0) { 740 return $returnValue / $aCount; 741 } 742 743 return Functions::DIV0(); 744 } 745 746 /** 747 * BETADIST. 748 * 749 * Returns the beta distribution. 750 * 751 * @param float $value Value at which you want to evaluate the distribution 752 * @param float $alpha Parameter to the distribution 753 * @param float $beta Parameter to the distribution 754 * @param mixed $rMin 755 * @param mixed $rMax 756 * 757 * @return float|string 758 */ 759 public static function BETADIST($value, $alpha, $beta, $rMin = 0, $rMax = 1) 760 { 761 $value = Functions::flattenSingleValue($value); 762 $alpha = Functions::flattenSingleValue($alpha); 763 $beta = Functions::flattenSingleValue($beta); 764 $rMin = Functions::flattenSingleValue($rMin); 765 $rMax = Functions::flattenSingleValue($rMax); 766 767 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) { 768 if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) { 769 return Functions::NAN(); 770 } 771 if ($rMin > $rMax) { 772 $tmp = $rMin; 773 $rMin = $rMax; 774 $rMax = $tmp; 775 } 776 $value -= $rMin; 777 $value /= ($rMax - $rMin); 778 779 return self::incompleteBeta($value, $alpha, $beta); 780 } 781 782 return Functions::VALUE(); 783 } 784 785 /** 786 * BETAINV. 787 * 788 * Returns the inverse of the Beta distribution. 789 * 790 * @param float $probability Probability at which you want to evaluate the distribution 791 * @param float $alpha Parameter to the distribution 792 * @param float $beta Parameter to the distribution 793 * @param float $rMin Minimum value 794 * @param float $rMax Maximum value 795 * 796 * @return float|string 797 */ 798 public static function BETAINV($probability, $alpha, $beta, $rMin = 0, $rMax = 1) 799 { 800 $probability = Functions::flattenSingleValue($probability); 801 $alpha = Functions::flattenSingleValue($alpha); 802 $beta = Functions::flattenSingleValue($beta); 803 $rMin = Functions::flattenSingleValue($rMin); 804 $rMax = Functions::flattenSingleValue($rMax); 805 806 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) { 807 if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) { 808 return Functions::NAN(); 809 } 810 if ($rMin > $rMax) { 811 $tmp = $rMin; 812 $rMin = $rMax; 813 $rMax = $tmp; 814 } 815 $a = 0; 816 $b = 2; 817 818 $i = 0; 819 while ((($b - $a) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { 820 $guess = ($a + $b) / 2; 821 $result = self::BETADIST($guess, $alpha, $beta); 822 if (($result == $probability) || ($result == 0)) { 823 $b = $a; 824 } elseif ($result > $probability) { 825 $b = $guess; 826 } else { 827 $a = $guess; 828 } 829 } 830 if ($i == self::MAX_ITERATIONS) { 831 return Functions::NA(); 832 } 833 834 return round($rMin + $guess * ($rMax - $rMin), 12); 835 } 836 837 return Functions::VALUE(); 838 } 839 840 /** 841 * BINOMDIST. 842 * 843 * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with 844 * a fixed number of tests or trials, when the outcomes of any trial are only success or failure, 845 * when trials are independent, and when the probability of success is constant throughout the 846 * experiment. For example, BINOMDIST can calculate the probability that two of the next three 847 * babies born are male. 848 * 849 * @param float $value Number of successes in trials 850 * @param float $trials Number of trials 851 * @param float $probability Probability of success on each trial 852 * @param bool $cumulative 853 * 854 * @return float|string 855 */ 856 public static function BINOMDIST($value, $trials, $probability, $cumulative) 857 { 858 $value = Functions::flattenSingleValue($value); 859 $trials = Functions::flattenSingleValue($trials); 860 $probability = Functions::flattenSingleValue($probability); 861 862 if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) { 863 $value = floor($value); 864 $trials = floor($trials); 865 if (($value < 0) || ($value > $trials)) { 866 return Functions::NAN(); 867 } 868 if (($probability < 0) || ($probability > 1)) { 869 return Functions::NAN(); 870 } 871 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 872 if ($cumulative) { 873 $summer = 0; 874 for ($i = 0; $i <= $value; ++$i) { 875 $summer += MathTrig::COMBIN($trials, $i) * $probability ** $i * (1 - $probability) ** ($trials - $i); 876 } 877 878 return $summer; 879 } 880 881 return MathTrig::COMBIN($trials, $value) * $probability ** $value * (1 - $probability) ** ($trials - $value); 882 } 883 } 884 885 return Functions::VALUE(); 886 } 887 888 /** 889 * CHIDIST. 890 * 891 * Returns the one-tailed probability of the chi-squared distribution. 892 * 893 * @param float $value Value for the function 894 * @param float $degrees degrees of freedom 895 * 896 * @return float|string 897 */ 898 public static function CHIDIST($value, $degrees) 899 { 900 $value = Functions::flattenSingleValue($value); 901 $degrees = Functions::flattenSingleValue($degrees); 902 903 if ((is_numeric($value)) && (is_numeric($degrees))) { 904 $degrees = floor($degrees); 905 if ($degrees < 1) { 906 return Functions::NAN(); 907 } 908 if ($value < 0) { 909 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 910 return 1; 911 } 912 913 return Functions::NAN(); 914 } 915 916 return 1 - (self::incompleteGamma($degrees / 2, $value / 2) / self::gamma($degrees / 2)); 917 } 918 919 return Functions::VALUE(); 920 } 921 922 /** 923 * CHIINV. 924 * 925 * Returns the one-tailed probability of the chi-squared distribution. 926 * 927 * @param float $probability Probability for the function 928 * @param float $degrees degrees of freedom 929 * 930 * @return float|string 931 */ 932 public static function CHIINV($probability, $degrees) 933 { 934 $probability = Functions::flattenSingleValue($probability); 935 $degrees = Functions::flattenSingleValue($degrees); 936 937 if ((is_numeric($probability)) && (is_numeric($degrees))) { 938 $degrees = floor($degrees); 939 940 $xLo = 100; 941 $xHi = 0; 942 943 $x = $xNew = 1; 944 $dx = 1; 945 $i = 0; 946 947 while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { 948 // Apply Newton-Raphson step 949 $result = 1 - (self::incompleteGamma($degrees / 2, $x / 2) / self::gamma($degrees / 2)); 950 $error = $result - $probability; 951 if ($error == 0.0) { 952 $dx = 0; 953 } elseif ($error < 0.0) { 954 $xLo = $x; 955 } else { 956 $xHi = $x; 957 } 958 // Avoid division by zero 959 if ($result != 0.0) { 960 $dx = $error / $result; 961 $xNew = $x - $dx; 962 } 963 // If the NR fails to converge (which for example may be the 964 // case if the initial guess is too rough) we apply a bisection 965 // step to determine a more narrow interval around the root. 966 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) { 967 $xNew = ($xLo + $xHi) / 2; 968 $dx = $xNew - $x; 969 } 970 $x = $xNew; 971 } 972 if ($i == self::MAX_ITERATIONS) { 973 return Functions::NA(); 974 } 975 976 return round($x, 12); 977 } 978 979 return Functions::VALUE(); 980 } 981 982 /** 983 * CONFIDENCE. 984 * 985 * Returns the confidence interval for a population mean 986 * 987 * @param float $alpha 988 * @param float $stdDev Standard Deviation 989 * @param float $size 990 * 991 * @return float|string 992 */ 993 public static function CONFIDENCE($alpha, $stdDev, $size) 994 { 995 $alpha = Functions::flattenSingleValue($alpha); 996 $stdDev = Functions::flattenSingleValue($stdDev); 997 $size = Functions::flattenSingleValue($size); 998 999 if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) { 1000 $size = floor($size); 1001 if (($alpha <= 0) || ($alpha >= 1)) { 1002 return Functions::NAN(); 1003 } 1004 if (($stdDev <= 0) || ($size < 1)) { 1005 return Functions::NAN(); 1006 } 1007 1008 return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size); 1009 } 1010 1011 return Functions::VALUE(); 1012 } 1013 1014 /** 1015 * CORREL. 1016 * 1017 * Returns covariance, the average of the products of deviations for each data point pair. 1018 * 1019 * @param mixed $yValues array of mixed Data Series Y 1020 * @param null|mixed $xValues array of mixed Data Series X 1021 * 1022 * @return float|string 1023 */ 1024 public static function CORREL($yValues, $xValues = null) 1025 { 1026 if (($xValues === null) || (!is_array($yValues)) || (!is_array($xValues))) { 1027 return Functions::VALUE(); 1028 } 1029 if (!self::checkTrendArrays($yValues, $xValues)) { 1030 return Functions::VALUE(); 1031 } 1032 $yValueCount = count($yValues); 1033 $xValueCount = count($xValues); 1034 1035 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1036 return Functions::NA(); 1037 } elseif ($yValueCount == 1) { 1038 return Functions::DIV0(); 1039 } 1040 1041 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 1042 1043 return $bestFitLinear->getCorrelation(); 1044 } 1045 1046 /** 1047 * COUNT. 1048 * 1049 * Counts the number of cells that contain numbers within the list of arguments 1050 * 1051 * Excel Function: 1052 * COUNT(value1[,value2[, ...]]) 1053 * 1054 * @param mixed ...$args Data values 1055 * 1056 * @return int 1057 */ 1058 public static function COUNT(...$args) 1059 { 1060 $returnValue = 0; 1061 1062 // Loop through arguments 1063 $aArgs = Functions::flattenArrayIndexed($args); 1064 foreach ($aArgs as $k => $arg) { 1065 $arg = self::testAcceptedBoolean($arg, $k); 1066 // Is it a numeric value? 1067 // Strings containing numeric values are only counted if they are string literals (not cell values) 1068 // and then only in MS Excel and in Open Office, not in Gnumeric 1069 if (self::isAcceptedCountable($arg, $k)) { 1070 ++$returnValue; 1071 } 1072 } 1073 1074 return $returnValue; 1075 } 1076 1077 /** 1078 * COUNTA. 1079 * 1080 * Counts the number of cells that are not empty within the list of arguments 1081 * 1082 * Excel Function: 1083 * COUNTA(value1[,value2[, ...]]) 1084 * 1085 * @param mixed ...$args Data values 1086 * 1087 * @return int 1088 */ 1089 public static function COUNTA(...$args) 1090 { 1091 $returnValue = 0; 1092 1093 // Loop through arguments 1094 $aArgs = Functions::flattenArrayIndexed($args); 1095 foreach ($aArgs as $k => $arg) { 1096 // Nulls are counted if literals, but not if cell values 1097 if ($arg !== null || (!Functions::isCellValue($k))) { 1098 ++$returnValue; 1099 } 1100 } 1101 1102 return $returnValue; 1103 } 1104 1105 /** 1106 * COUNTBLANK. 1107 * 1108 * Counts the number of empty cells within the list of arguments 1109 * 1110 * Excel Function: 1111 * COUNTBLANK(value1[,value2[, ...]]) 1112 * 1113 * @param mixed ...$args Data values 1114 * 1115 * @return int 1116 */ 1117 public static function COUNTBLANK(...$args) 1118 { 1119 $returnValue = 0; 1120 1121 // Loop through arguments 1122 $aArgs = Functions::flattenArray($args); 1123 foreach ($aArgs as $arg) { 1124 // Is it a blank cell? 1125 if (($arg === null) || ((is_string($arg)) && ($arg == ''))) { 1126 ++$returnValue; 1127 } 1128 } 1129 1130 return $returnValue; 1131 } 1132 1133 /** 1134 * COUNTIF. 1135 * 1136 * Counts the number of cells that contain numbers within the list of arguments 1137 * 1138 * Excel Function: 1139 * COUNTIF(value1[,value2[, ...]],condition) 1140 * 1141 * @param mixed $aArgs Data values 1142 * @param string $condition the criteria that defines which cells will be counted 1143 * 1144 * @return int 1145 */ 1146 public static function COUNTIF($aArgs, $condition) 1147 { 1148 $returnValue = 0; 1149 1150 $aArgs = Functions::flattenArray($aArgs); 1151 $condition = Functions::ifCondition($condition); 1152 $conditionIsNumeric = strpos($condition, '"') === false; 1153 // Loop through arguments 1154 foreach ($aArgs as $arg) { 1155 if (!is_numeric($arg)) { 1156 if ($conditionIsNumeric) { 1157 continue; 1158 } 1159 $arg = Calculation::wrapResult(strtoupper($arg)); 1160 } elseif (!$conditionIsNumeric) { 1161 continue; 1162 } 1163 $testCondition = '=' . $arg . $condition; 1164 if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1165 // Is it a value within our criteria 1166 ++$returnValue; 1167 } 1168 } 1169 1170 return $returnValue; 1171 } 1172 1173 /** 1174 * COUNTIFS. 1175 * 1176 * Counts the number of cells that contain numbers within the list of arguments 1177 * 1178 * Excel Function: 1179 * COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) 1180 * 1181 * @param mixed $args Criterias 1182 * 1183 * @return int 1184 */ 1185 public static function COUNTIFS(...$args) 1186 { 1187 $arrayList = $args; 1188 1189 // Return value 1190 $returnValue = 0; 1191 1192 if (empty($arrayList)) { 1193 return $returnValue; 1194 } 1195 1196 $aArgsArray = []; 1197 $conditions = []; 1198 1199 while (count($arrayList) > 0) { 1200 $aArgsArray[] = Functions::flattenArray(array_shift($arrayList)); 1201 $conditions[] = Functions::ifCondition(array_shift($arrayList)); 1202 } 1203 1204 // Loop through each arg and see if arguments and conditions are true 1205 foreach (array_keys($aArgsArray[0]) as $index) { 1206 $valid = true; 1207 1208 foreach ($conditions as $cidx => $condition) { 1209 $conditionIsNumeric = strpos($condition, '"') === false; 1210 $arg = $aArgsArray[$cidx][$index]; 1211 1212 // Loop through arguments 1213 if (!is_numeric($arg)) { 1214 if ($conditionIsNumeric) { 1215 $valid = false; 1216 1217 break; // if false found, don't need to check other conditions 1218 } 1219 $arg = Calculation::wrapResult(strtoupper($arg)); 1220 } elseif (!$conditionIsNumeric) { 1221 $valid = false; 1222 1223 break; // if false found, don't need to check other conditions 1224 } 1225 $testCondition = '=' . $arg . $condition; 1226 if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 1227 // Is not a value within our criteria 1228 $valid = false; 1229 1230 break; // if false found, don't need to check other conditions 1231 } 1232 } 1233 1234 if ($valid) { 1235 ++$returnValue; 1236 } 1237 } 1238 1239 // Return 1240 return $returnValue; 1241 } 1242 1243 /** 1244 * COVAR. 1245 * 1246 * Returns covariance, the average of the products of deviations for each data point pair. 1247 * 1248 * @param mixed $yValues array of mixed Data Series Y 1249 * @param mixed $xValues array of mixed Data Series X 1250 * 1251 * @return float|string 1252 */ 1253 public static function COVAR($yValues, $xValues) 1254 { 1255 if (!self::checkTrendArrays($yValues, $xValues)) { 1256 return Functions::VALUE(); 1257 } 1258 $yValueCount = count($yValues); 1259 $xValueCount = count($xValues); 1260 1261 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1262 return Functions::NA(); 1263 } elseif ($yValueCount == 1) { 1264 return Functions::DIV0(); 1265 } 1266 1267 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 1268 1269 return $bestFitLinear->getCovariance(); 1270 } 1271 1272 /** 1273 * CRITBINOM. 1274 * 1275 * Returns the smallest value for which the cumulative binomial distribution is greater 1276 * than or equal to a criterion value 1277 * 1278 * See https://support.microsoft.com/en-us/help/828117/ for details of the algorithm used 1279 * 1280 * @param float $trials number of Bernoulli trials 1281 * @param float $probability probability of a success on each trial 1282 * @param float $alpha criterion value 1283 * 1284 * @return int|string 1285 * 1286 * @TODO Warning. This implementation differs from the algorithm detailed on the MS 1287 * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess 1288 * This eliminates a potential endless loop error, but may have an adverse affect on the 1289 * accuracy of the function (although all my tests have so far returned correct results). 1290 */ 1291 public static function CRITBINOM($trials, $probability, $alpha) 1292 { 1293 $trials = floor(Functions::flattenSingleValue($trials)); 1294 $probability = Functions::flattenSingleValue($probability); 1295 $alpha = Functions::flattenSingleValue($alpha); 1296 1297 if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) { 1298 $trials = (int) $trials; 1299 if ($trials < 0) { 1300 return Functions::NAN(); 1301 } elseif (($probability < 0.0) || ($probability > 1.0)) { 1302 return Functions::NAN(); 1303 } elseif (($alpha < 0.0) || ($alpha > 1.0)) { 1304 return Functions::NAN(); 1305 } 1306 1307 if ($alpha <= 0.5) { 1308 $t = sqrt(log(1 / ($alpha * $alpha))); 1309 $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)); 1310 } else { 1311 $t = sqrt(log(1 / (1 - $alpha) ** 2)); 1312 $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t); 1313 } 1314 1315 $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability))); 1316 if ($Guess < 0) { 1317 $Guess = 0; 1318 } elseif ($Guess > $trials) { 1319 $Guess = $trials; 1320 } 1321 1322 $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0; 1323 $EssentiallyZero = 10e-12; 1324 1325 $m = floor($trials * $probability); 1326 ++$TotalUnscaledProbability; 1327 if ($m == $Guess) { 1328 ++$UnscaledPGuess; 1329 } 1330 if ($m <= $Guess) { 1331 ++$UnscaledCumPGuess; 1332 } 1333 1334 $PreviousValue = 1; 1335 $Done = false; 1336 $k = $m + 1; 1337 while ((!$Done) && ($k <= $trials)) { 1338 $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability)); 1339 $TotalUnscaledProbability += $CurrentValue; 1340 if ($k == $Guess) { 1341 $UnscaledPGuess += $CurrentValue; 1342 } 1343 if ($k <= $Guess) { 1344 $UnscaledCumPGuess += $CurrentValue; 1345 } 1346 if ($CurrentValue <= $EssentiallyZero) { 1347 $Done = true; 1348 } 1349 $PreviousValue = $CurrentValue; 1350 ++$k; 1351 } 1352 1353 $PreviousValue = 1; 1354 $Done = false; 1355 $k = $m - 1; 1356 while ((!$Done) && ($k >= 0)) { 1357 $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability); 1358 $TotalUnscaledProbability += $CurrentValue; 1359 if ($k == $Guess) { 1360 $UnscaledPGuess += $CurrentValue; 1361 } 1362 if ($k <= $Guess) { 1363 $UnscaledCumPGuess += $CurrentValue; 1364 } 1365 if ($CurrentValue <= $EssentiallyZero) { 1366 $Done = true; 1367 } 1368 $PreviousValue = $CurrentValue; 1369 --$k; 1370 } 1371 1372 $PGuess = $UnscaledPGuess / $TotalUnscaledProbability; 1373 $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability; 1374 1375 $CumPGuessMinus1 = $CumPGuess - 1; 1376 1377 while (true) { 1378 if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) { 1379 return $Guess; 1380 } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) { 1381 $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability); 1382 $CumPGuessMinus1 = $CumPGuess; 1383 $CumPGuess = $CumPGuess + $PGuessPlus1; 1384 $PGuess = $PGuessPlus1; 1385 ++$Guess; 1386 } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) { 1387 $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability; 1388 $CumPGuess = $CumPGuessMinus1; 1389 $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess; 1390 $PGuess = $PGuessMinus1; 1391 --$Guess; 1392 } 1393 } 1394 } 1395 1396 return Functions::VALUE(); 1397 } 1398 1399 /** 1400 * DEVSQ. 1401 * 1402 * Returns the sum of squares of deviations of data points from their sample mean. 1403 * 1404 * Excel Function: 1405 * DEVSQ(value1[,value2[, ...]]) 1406 * 1407 * @param mixed ...$args Data values 1408 * 1409 * @return float|string 1410 */ 1411 public static function DEVSQ(...$args) 1412 { 1413 $aArgs = Functions::flattenArrayIndexed($args); 1414 1415 // Return value 1416 $returnValue = null; 1417 1418 $aMean = self::AVERAGE($aArgs); 1419 if ($aMean != Functions::DIV0()) { 1420 $aCount = -1; 1421 foreach ($aArgs as $k => $arg) { 1422 // Is it a numeric value? 1423 if ( 1424 (is_bool($arg)) && 1425 ((!Functions::isCellValue($k)) || 1426 (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE)) 1427 ) { 1428 $arg = (int) $arg; 1429 } 1430 if ((is_numeric($arg)) && (!is_string($arg))) { 1431 if ($returnValue === null) { 1432 $returnValue = ($arg - $aMean) ** 2; 1433 } else { 1434 $returnValue += ($arg - $aMean) ** 2; 1435 } 1436 ++$aCount; 1437 } 1438 } 1439 1440 // Return 1441 if ($returnValue === null) { 1442 return Functions::NAN(); 1443 } 1444 1445 return $returnValue; 1446 } 1447 1448 return Functions::NA(); 1449 } 1450 1451 /** 1452 * EXPONDIST. 1453 * 1454 * Returns the exponential distribution. Use EXPONDIST to model the time between events, 1455 * such as how long an automated bank teller takes to deliver cash. For example, you can 1456 * use EXPONDIST to determine the probability that the process takes at most 1 minute. 1457 * 1458 * @param float $value Value of the function 1459 * @param float $lambda The parameter value 1460 * @param bool $cumulative 1461 * 1462 * @return float|string 1463 */ 1464 public static function EXPONDIST($value, $lambda, $cumulative) 1465 { 1466 $value = Functions::flattenSingleValue($value); 1467 $lambda = Functions::flattenSingleValue($lambda); 1468 $cumulative = Functions::flattenSingleValue($cumulative); 1469 1470 if ((is_numeric($value)) && (is_numeric($lambda))) { 1471 if (($value < 0) || ($lambda < 0)) { 1472 return Functions::NAN(); 1473 } 1474 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 1475 if ($cumulative) { 1476 return 1 - exp(0 - $value * $lambda); 1477 } 1478 1479 return $lambda * exp(0 - $value * $lambda); 1480 } 1481 } 1482 1483 return Functions::VALUE(); 1484 } 1485 1486 private static function betaFunction($a, $b) 1487 { 1488 return (self::gamma($a) * self::gamma($b)) / self::gamma($a + $b); 1489 } 1490 1491 private static function regularizedIncompleteBeta($value, $a, $b) 1492 { 1493 return self::incompleteBeta($value, $a, $b) / self::betaFunction($a, $b); 1494 } 1495 1496 /** 1497 * F.DIST. 1498 * 1499 * Returns the F probability distribution. 1500 * You can use this function to determine whether two data sets have different degrees of diversity. 1501 * For example, you can examine the test scores of men and women entering high school, and determine 1502 * if the variability in the females is different from that found in the males. 1503 * 1504 * @param float $value Value of the function 1505 * @param int $u The numerator degrees of freedom 1506 * @param int $v The denominator degrees of freedom 1507 * @param bool $cumulative If cumulative is TRUE, F.DIST returns the cumulative distribution function; 1508 * if FALSE, it returns the probability density function. 1509 * 1510 * @return float|string 1511 */ 1512 public static function FDIST2($value, $u, $v, $cumulative) 1513 { 1514 $value = Functions::flattenSingleValue($value); 1515 $u = Functions::flattenSingleValue($u); 1516 $v = Functions::flattenSingleValue($v); 1517 $cumulative = Functions::flattenSingleValue($cumulative); 1518 1519 if (is_numeric($value) && is_numeric($u) && is_numeric($v)) { 1520 if ($value < 0 || $u < 1 || $v < 1) { 1521 return Functions::NAN(); 1522 } 1523 1524 $cumulative = (bool) $cumulative; 1525 $u = (int) $u; 1526 $v = (int) $v; 1527 1528 if ($cumulative) { 1529 $adjustedValue = ($u * $value) / ($u * $value + $v); 1530 1531 return self::incompleteBeta($adjustedValue, $u / 2, $v / 2); 1532 } 1533 1534 return (self::gamma(($v + $u) / 2) / (self::gamma($u / 2) * self::gamma($v / 2))) * 1535 (($u / $v) ** ($u / 2)) * 1536 (($value ** (($u - 2) / 2)) / ((1 + ($u / $v) * $value) ** (($u + $v) / 2))); 1537 } 1538 1539 return Functions::VALUE(); 1540 } 1541 1542 /** 1543 * FISHER. 1544 * 1545 * Returns the Fisher transformation at x. This transformation produces a function that 1546 * is normally distributed rather than skewed. Use this function to perform hypothesis 1547 * testing on the correlation coefficient. 1548 * 1549 * @param float $value 1550 * 1551 * @return float|string 1552 */ 1553 public static function FISHER($value) 1554 { 1555 $value = Functions::flattenSingleValue($value); 1556 1557 if (is_numeric($value)) { 1558 if (($value <= -1) || ($value >= 1)) { 1559 return Functions::NAN(); 1560 } 1561 1562 return 0.5 * log((1 + $value) / (1 - $value)); 1563 } 1564 1565 return Functions::VALUE(); 1566 } 1567 1568 /** 1569 * FISHERINV. 1570 * 1571 * Returns the inverse of the Fisher transformation. Use this transformation when 1572 * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then 1573 * FISHERINV(y) = x. 1574 * 1575 * @param float $value 1576 * 1577 * @return float|string 1578 */ 1579 public static function FISHERINV($value) 1580 { 1581 $value = Functions::flattenSingleValue($value); 1582 1583 if (is_numeric($value)) { 1584 return (exp(2 * $value) - 1) / (exp(2 * $value) + 1); 1585 } 1586 1587 return Functions::VALUE(); 1588 } 1589 1590 /** 1591 * FORECAST. 1592 * 1593 * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. 1594 * 1595 * @param float $xValue Value of X for which we want to find Y 1596 * @param mixed $yValues array of mixed Data Series Y 1597 * @param mixed $xValues of mixed Data Series X 1598 * 1599 * @return bool|float|string 1600 */ 1601 public static function FORECAST($xValue, $yValues, $xValues) 1602 { 1603 $xValue = Functions::flattenSingleValue($xValue); 1604 if (!is_numeric($xValue)) { 1605 return Functions::VALUE(); 1606 } elseif (!self::checkTrendArrays($yValues, $xValues)) { 1607 return Functions::VALUE(); 1608 } 1609 $yValueCount = count($yValues); 1610 $xValueCount = count($xValues); 1611 1612 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1613 return Functions::NA(); 1614 } elseif ($yValueCount == 1) { 1615 return Functions::DIV0(); 1616 } 1617 1618 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 1619 1620 return $bestFitLinear->getValueOfYForX($xValue); 1621 } 1622 1623 /** 1624 * GAMMA. 1625 * 1626 * Return the gamma function value. 1627 * 1628 * @param float $value 1629 * 1630 * @return float|string The result, or a string containing an error 1631 */ 1632 public static function GAMMAFunction($value) 1633 { 1634 $value = Functions::flattenSingleValue($value); 1635 if (!is_numeric($value)) { 1636 return Functions::VALUE(); 1637 } elseif ((((int) $value) == ((float) $value)) && $value <= 0.0) { 1638 return Functions::NAN(); 1639 } 1640 1641 return self::gamma($value); 1642 } 1643 1644 /** 1645 * GAMMADIST. 1646 * 1647 * Returns the gamma distribution. 1648 * 1649 * @param float $value Value at which you want to evaluate the distribution 1650 * @param float $a Parameter to the distribution 1651 * @param float $b Parameter to the distribution 1652 * @param bool $cumulative 1653 * 1654 * @return float|string 1655 */ 1656 public static function GAMMADIST($value, $a, $b, $cumulative) 1657 { 1658 $value = Functions::flattenSingleValue($value); 1659 $a = Functions::flattenSingleValue($a); 1660 $b = Functions::flattenSingleValue($b); 1661 1662 if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) { 1663 if (($value < 0) || ($a <= 0) || ($b <= 0)) { 1664 return Functions::NAN(); 1665 } 1666 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 1667 if ($cumulative) { 1668 return self::incompleteGamma($a, $value / $b) / self::gamma($a); 1669 } 1670 1671 return (1 / ($b ** $a * self::gamma($a))) * $value ** ($a - 1) * exp(0 - ($value / $b)); 1672 } 1673 } 1674 1675 return Functions::VALUE(); 1676 } 1677 1678 /** 1679 * GAMMAINV. 1680 * 1681 * Returns the inverse of the Gamma distribution. 1682 * 1683 * @param float $probability Probability at which you want to evaluate the distribution 1684 * @param float $alpha Parameter to the distribution 1685 * @param float $beta Parameter to the distribution 1686 * 1687 * @return float|string 1688 */ 1689 public static function GAMMAINV($probability, $alpha, $beta) 1690 { 1691 $probability = Functions::flattenSingleValue($probability); 1692 $alpha = Functions::flattenSingleValue($alpha); 1693 $beta = Functions::flattenSingleValue($beta); 1694 1695 if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) { 1696 if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) { 1697 return Functions::NAN(); 1698 } 1699 1700 $xLo = 0; 1701 $xHi = $alpha * $beta * 5; 1702 1703 $x = $xNew = 1; 1704 $dx = 1024; 1705 $i = 0; 1706 1707 while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { 1708 // Apply Newton-Raphson step 1709 $error = self::GAMMADIST($x, $alpha, $beta, true) - $probability; 1710 if ($error < 0.0) { 1711 $xLo = $x; 1712 } else { 1713 $xHi = $x; 1714 } 1715 $pdf = self::GAMMADIST($x, $alpha, $beta, false); 1716 // Avoid division by zero 1717 if ($pdf != 0.0) { 1718 $dx = $error / $pdf; 1719 $xNew = $x - $dx; 1720 } 1721 // If the NR fails to converge (which for example may be the 1722 // case if the initial guess is too rough) we apply a bisection 1723 // step to determine a more narrow interval around the root. 1724 if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) { 1725 $xNew = ($xLo + $xHi) / 2; 1726 $dx = $xNew - $x; 1727 } 1728 $x = $xNew; 1729 } 1730 if ($i == self::MAX_ITERATIONS) { 1731 return Functions::NA(); 1732 } 1733 1734 return $x; 1735 } 1736 1737 return Functions::VALUE(); 1738 } 1739 1740 /** 1741 * GAMMALN. 1742 * 1743 * Returns the natural logarithm of the gamma function. 1744 * 1745 * @param float $value 1746 * 1747 * @return float|string 1748 */ 1749 public static function GAMMALN($value) 1750 { 1751 $value = Functions::flattenSingleValue($value); 1752 1753 if (is_numeric($value)) { 1754 if ($value <= 0) { 1755 return Functions::NAN(); 1756 } 1757 1758 return log(self::gamma($value)); 1759 } 1760 1761 return Functions::VALUE(); 1762 } 1763 1764 /** 1765 * GAUSS. 1766 * 1767 * Calculates the probability that a member of a standard normal population will fall between 1768 * the mean and z standard deviations from the mean. 1769 * 1770 * @param float $value 1771 * 1772 * @return float|string The result, or a string containing an error 1773 */ 1774 public static function GAUSS($value) 1775 { 1776 $value = Functions::flattenSingleValue($value); 1777 if (!is_numeric($value)) { 1778 return Functions::VALUE(); 1779 } 1780 1781 return self::NORMDIST($value, 0, 1, true) - 0.5; 1782 } 1783 1784 /** 1785 * GEOMEAN. 1786 * 1787 * Returns the geometric mean of an array or range of positive data. For example, you 1788 * can use GEOMEAN to calculate average growth rate given compound interest with 1789 * variable rates. 1790 * 1791 * Excel Function: 1792 * GEOMEAN(value1[,value2[, ...]]) 1793 * 1794 * @param mixed ...$args Data values 1795 * 1796 * @return float|string 1797 */ 1798 public static function GEOMEAN(...$args) 1799 { 1800 $aArgs = Functions::flattenArray($args); 1801 1802 $aMean = MathTrig::PRODUCT($aArgs); 1803 if (is_numeric($aMean) && ($aMean > 0)) { 1804 $aCount = self::COUNT($aArgs); 1805 if (self::MIN($aArgs) > 0) { 1806 return $aMean ** (1 / $aCount); 1807 } 1808 } 1809 1810 return Functions::NAN(); 1811 } 1812 1813 /** 1814 * GROWTH. 1815 * 1816 * Returns values along a predicted exponential Trend 1817 * 1818 * @param mixed[] $yValues Data Series Y 1819 * @param mixed[] $xValues Data Series X 1820 * @param mixed[] $newValues Values of X for which we want to find Y 1821 * @param bool $const a logical value specifying whether to force the intersect to equal 0 1822 * 1823 * @return array of float 1824 */ 1825 public static function GROWTH($yValues, $xValues = [], $newValues = [], $const = true) 1826 { 1827 $yValues = Functions::flattenArray($yValues); 1828 $xValues = Functions::flattenArray($xValues); 1829 $newValues = Functions::flattenArray($newValues); 1830 $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); 1831 1832 $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const); 1833 if (empty($newValues)) { 1834 $newValues = $bestFitExponential->getXValues(); 1835 } 1836 1837 $returnArray = []; 1838 foreach ($newValues as $xValue) { 1839 $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue); 1840 } 1841 1842 return $returnArray; 1843 } 1844 1845 /** 1846 * HARMEAN. 1847 * 1848 * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the 1849 * arithmetic mean of reciprocals. 1850 * 1851 * Excel Function: 1852 * HARMEAN(value1[,value2[, ...]]) 1853 * 1854 * @param mixed ...$args Data values 1855 * 1856 * @return float|string 1857 */ 1858 public static function HARMEAN(...$args) 1859 { 1860 // Return value 1861 $returnValue = 0; 1862 1863 // Loop through arguments 1864 $aArgs = Functions::flattenArray($args); 1865 if (self::MIN($aArgs) < 0) { 1866 return Functions::NAN(); 1867 } 1868 $aCount = 0; 1869 foreach ($aArgs as $arg) { 1870 // Is it a numeric value? 1871 if ((is_numeric($arg)) && (!is_string($arg))) { 1872 if ($arg <= 0) { 1873 return Functions::NAN(); 1874 } 1875 $returnValue += (1 / $arg); 1876 ++$aCount; 1877 } 1878 } 1879 1880 // Return 1881 if ($aCount > 0) { 1882 return 1 / ($returnValue / $aCount); 1883 } 1884 1885 return Functions::NA(); 1886 } 1887 1888 /** 1889 * HYPGEOMDIST. 1890 * 1891 * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of 1892 * sample successes, given the sample size, population successes, and population size. 1893 * 1894 * @param float $sampleSuccesses Number of successes in the sample 1895 * @param float $sampleNumber Size of the sample 1896 * @param float $populationSuccesses Number of successes in the population 1897 * @param float $populationNumber Population size 1898 * 1899 * @return float|string 1900 */ 1901 public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) 1902 { 1903 $sampleSuccesses = Functions::flattenSingleValue($sampleSuccesses); 1904 $sampleNumber = Functions::flattenSingleValue($sampleNumber); 1905 $populationSuccesses = Functions::flattenSingleValue($populationSuccesses); 1906 $populationNumber = Functions::flattenSingleValue($populationNumber); 1907 1908 if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) { 1909 $sampleSuccesses = floor($sampleSuccesses); 1910 $sampleNumber = floor($sampleNumber); 1911 $populationSuccesses = floor($populationSuccesses); 1912 $populationNumber = floor($populationNumber); 1913 1914 if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) { 1915 return Functions::NAN(); 1916 } 1917 if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) { 1918 return Functions::NAN(); 1919 } 1920 if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) { 1921 return Functions::NAN(); 1922 } 1923 1924 return MathTrig::COMBIN($populationSuccesses, $sampleSuccesses) * 1925 MathTrig::COMBIN($populationNumber - $populationSuccesses, $sampleNumber - $sampleSuccesses) / 1926 MathTrig::COMBIN($populationNumber, $sampleNumber); 1927 } 1928 1929 return Functions::VALUE(); 1930 } 1931 1932 /** 1933 * INTERCEPT. 1934 * 1935 * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. 1936 * 1937 * @param mixed[] $yValues Data Series Y 1938 * @param mixed[] $xValues Data Series X 1939 * 1940 * @return float|string 1941 */ 1942 public static function INTERCEPT($yValues, $xValues) 1943 { 1944 if (!self::checkTrendArrays($yValues, $xValues)) { 1945 return Functions::VALUE(); 1946 } 1947 $yValueCount = count($yValues); 1948 $xValueCount = count($xValues); 1949 1950 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 1951 return Functions::NA(); 1952 } elseif ($yValueCount == 1) { 1953 return Functions::DIV0(); 1954 } 1955 1956 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 1957 1958 return $bestFitLinear->getIntersect(); 1959 } 1960 1961 /** 1962 * KURT. 1963 * 1964 * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness 1965 * or flatness of a distribution compared with the normal distribution. Positive 1966 * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a 1967 * relatively flat distribution. 1968 * 1969 * @param array ...$args Data Series 1970 * 1971 * @return float|string 1972 */ 1973 public static function KURT(...$args) 1974 { 1975 $aArgs = Functions::flattenArrayIndexed($args); 1976 $mean = self::AVERAGE($aArgs); 1977 $stdDev = self::STDEV($aArgs); 1978 1979 if ($stdDev > 0) { 1980 $count = $summer = 0; 1981 // Loop through arguments 1982 foreach ($aArgs as $k => $arg) { 1983 if ( 1984 (is_bool($arg)) && 1985 (!Functions::isMatrixValue($k)) 1986 ) { 1987 } else { 1988 // Is it a numeric value? 1989 if ((is_numeric($arg)) && (!is_string($arg))) { 1990 $summer += (($arg - $mean) / $stdDev) ** 4; 1991 ++$count; 1992 } 1993 } 1994 } 1995 1996 // Return 1997 if ($count > 3) { 1998 return $summer * ($count * ($count + 1) / (($count - 1) * ($count - 2) * ($count - 3))) - (3 * ($count - 1) ** 2 / (($count - 2) * ($count - 3))); 1999 } 2000 } 2001 2002 return Functions::DIV0(); 2003 } 2004 2005 /** 2006 * LARGE. 2007 * 2008 * Returns the nth largest value in a data set. You can use this function to 2009 * select a value based on its relative standing. 2010 * 2011 * Excel Function: 2012 * LARGE(value1[,value2[, ...]],entry) 2013 * 2014 * @param mixed $args Data values 2015 * 2016 * @return float|string The result, or a string containing an error 2017 */ 2018 public static function LARGE(...$args) 2019 { 2020 $aArgs = Functions::flattenArray($args); 2021 $entry = array_pop($aArgs); 2022 2023 if ((is_numeric($entry)) && (!is_string($entry))) { 2024 $entry = (int) floor($entry); 2025 2026 // Calculate 2027 $mArgs = []; 2028 foreach ($aArgs as $arg) { 2029 // Is it a numeric value? 2030 if ((is_numeric($arg)) && (!is_string($arg))) { 2031 $mArgs[] = $arg; 2032 } 2033 } 2034 $count = self::COUNT($mArgs); 2035 --$entry; 2036 if (($entry < 0) || ($entry >= $count) || ($count == 0)) { 2037 return Functions::NAN(); 2038 } 2039 rsort($mArgs); 2040 2041 return $mArgs[$entry]; 2042 } 2043 2044 return Functions::VALUE(); 2045 } 2046 2047 /** 2048 * LINEST. 2049 * 2050 * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, 2051 * and then returns an array that describes the line. 2052 * 2053 * @param mixed[] $yValues Data Series Y 2054 * @param null|mixed[] $xValues Data Series X 2055 * @param bool $const a logical value specifying whether to force the intersect to equal 0 2056 * @param bool $stats a logical value specifying whether to return additional regression statistics 2057 * 2058 * @return array|int|string The result, or a string containing an error 2059 */ 2060 public static function LINEST($yValues, $xValues = null, $const = true, $stats = false) 2061 { 2062 $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); 2063 $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats); 2064 if ($xValues === null) { 2065 $xValues = range(1, count(Functions::flattenArray($yValues))); 2066 } 2067 2068 if (!self::checkTrendArrays($yValues, $xValues)) { 2069 return Functions::VALUE(); 2070 } 2071 $yValueCount = count($yValues); 2072 $xValueCount = count($xValues); 2073 2074 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2075 return Functions::NA(); 2076 } elseif ($yValueCount == 1) { 2077 return 0; 2078 } 2079 2080 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const); 2081 if ($stats) { 2082 return [ 2083 [ 2084 $bestFitLinear->getSlope(), 2085 $bestFitLinear->getSlopeSE(), 2086 $bestFitLinear->getGoodnessOfFit(), 2087 $bestFitLinear->getF(), 2088 $bestFitLinear->getSSRegression(), 2089 ], 2090 [ 2091 $bestFitLinear->getIntersect(), 2092 $bestFitLinear->getIntersectSE(), 2093 $bestFitLinear->getStdevOfResiduals(), 2094 $bestFitLinear->getDFResiduals(), 2095 $bestFitLinear->getSSResiduals(), 2096 ], 2097 ]; 2098 } 2099 2100 return [ 2101 $bestFitLinear->getSlope(), 2102 $bestFitLinear->getIntersect(), 2103 ]; 2104 } 2105 2106 /** 2107 * LOGEST. 2108 * 2109 * Calculates an exponential curve that best fits the X and Y data series, 2110 * and then returns an array that describes the line. 2111 * 2112 * @param mixed[] $yValues Data Series Y 2113 * @param null|mixed[] $xValues Data Series X 2114 * @param bool $const a logical value specifying whether to force the intersect to equal 0 2115 * @param bool $stats a logical value specifying whether to return additional regression statistics 2116 * 2117 * @return array|int|string The result, or a string containing an error 2118 */ 2119 public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false) 2120 { 2121 $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); 2122 $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats); 2123 if ($xValues === null) { 2124 $xValues = range(1, count(Functions::flattenArray($yValues))); 2125 } 2126 2127 if (!self::checkTrendArrays($yValues, $xValues)) { 2128 return Functions::VALUE(); 2129 } 2130 $yValueCount = count($yValues); 2131 $xValueCount = count($xValues); 2132 2133 foreach ($yValues as $value) { 2134 if ($value <= 0.0) { 2135 return Functions::NAN(); 2136 } 2137 } 2138 2139 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 2140 return Functions::NA(); 2141 } elseif ($yValueCount == 1) { 2142 return 1; 2143 } 2144 2145 $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const); 2146 if ($stats) { 2147 return [ 2148 [ 2149 $bestFitExponential->getSlope(), 2150 $bestFitExponential->getSlopeSE(), 2151 $bestFitExponential->getGoodnessOfFit(), 2152 $bestFitExponential->getF(), 2153 $bestFitExponential->getSSRegression(), 2154 ], 2155 [ 2156 $bestFitExponential->getIntersect(), 2157 $bestFitExponential->getIntersectSE(), 2158 $bestFitExponential->getStdevOfResiduals(), 2159 $bestFitExponential->getDFResiduals(), 2160 $bestFitExponential->getSSResiduals(), 2161 ], 2162 ]; 2163 } 2164 2165 return [ 2166 $bestFitExponential->getSlope(), 2167 $bestFitExponential->getIntersect(), 2168 ]; 2169 } 2170 2171 /** 2172 * LOGINV. 2173 * 2174 * Returns the inverse of the normal cumulative distribution 2175 * 2176 * @param float $probability 2177 * @param float $mean 2178 * @param float $stdDev 2179 * 2180 * @return float|string The result, or a string containing an error 2181 * 2182 * @TODO Try implementing P J Acklam's refinement algorithm for greater 2183 * accuracy if I can get my head round the mathematics 2184 * (as described at) http://home.online.no/~pjacklam/notes/invnorm/ 2185 */ 2186 public static function LOGINV($probability, $mean, $stdDev) 2187 { 2188 $probability = Functions::flattenSingleValue($probability); 2189 $mean = Functions::flattenSingleValue($mean); 2190 $stdDev = Functions::flattenSingleValue($stdDev); 2191 2192 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2193 if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) { 2194 return Functions::NAN(); 2195 } 2196 2197 return exp($mean + $stdDev * self::NORMSINV($probability)); 2198 } 2199 2200 return Functions::VALUE(); 2201 } 2202 2203 /** 2204 * LOGNORMDIST. 2205 * 2206 * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed 2207 * with parameters mean and standard_dev. 2208 * 2209 * @param float $value 2210 * @param float $mean 2211 * @param float $stdDev 2212 * 2213 * @return float|string The result, or a string containing an error 2214 */ 2215 public static function LOGNORMDIST($value, $mean, $stdDev) 2216 { 2217 $value = Functions::flattenSingleValue($value); 2218 $mean = Functions::flattenSingleValue($mean); 2219 $stdDev = Functions::flattenSingleValue($stdDev); 2220 2221 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2222 if (($value <= 0) || ($stdDev <= 0)) { 2223 return Functions::NAN(); 2224 } 2225 2226 return self::NORMSDIST((log($value) - $mean) / $stdDev); 2227 } 2228 2229 return Functions::VALUE(); 2230 } 2231 2232 /** 2233 * LOGNORM.DIST. 2234 * 2235 * Returns the lognormal distribution of x, where ln(x) is normally distributed 2236 * with parameters mean and standard_dev. 2237 * 2238 * @param float $value 2239 * @param float $mean 2240 * @param float $stdDev 2241 * @param bool $cumulative 2242 * 2243 * @return float|string The result, or a string containing an error 2244 */ 2245 public static function LOGNORMDIST2($value, $mean, $stdDev, $cumulative = false) 2246 { 2247 $value = Functions::flattenSingleValue($value); 2248 $mean = Functions::flattenSingleValue($mean); 2249 $stdDev = Functions::flattenSingleValue($stdDev); 2250 $cumulative = (bool) Functions::flattenSingleValue($cumulative); 2251 2252 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2253 if (($value <= 0) || ($stdDev <= 0)) { 2254 return Functions::NAN(); 2255 } 2256 2257 if ($cumulative === true) { 2258 return self::NORMSDIST2((log($value) - $mean) / $stdDev, true); 2259 } 2260 2261 return (1 / (sqrt(2 * M_PI) * $stdDev * $value)) * 2262 exp(0 - ((log($value) - $mean) ** 2 / (2 * $stdDev ** 2))); 2263 } 2264 2265 return Functions::VALUE(); 2266 } 2267 2268 /** 2269 * MAX. 2270 * 2271 * MAX returns the value of the element of the values passed that has the highest value, 2272 * with negative numbers considered smaller than positive numbers. 2273 * 2274 * Excel Function: 2275 * MAX(value1[,value2[, ...]]) 2276 * 2277 * @param mixed ...$args Data values 2278 * 2279 * @return float 2280 */ 2281 public static function MAX(...$args) 2282 { 2283 $returnValue = null; 2284 2285 // Loop through arguments 2286 $aArgs = Functions::flattenArray($args); 2287 foreach ($aArgs as $arg) { 2288 // Is it a numeric value? 2289 if ((is_numeric($arg)) && (!is_string($arg))) { 2290 if (($returnValue === null) || ($arg > $returnValue)) { 2291 $returnValue = $arg; 2292 } 2293 } 2294 } 2295 2296 if ($returnValue === null) { 2297 return 0; 2298 } 2299 2300 return $returnValue; 2301 } 2302 2303 /** 2304 * MAXA. 2305 * 2306 * Returns the greatest value in a list of arguments, including numbers, text, and logical values 2307 * 2308 * Excel Function: 2309 * MAXA(value1[,value2[, ...]]) 2310 * 2311 * @param mixed ...$args Data values 2312 * 2313 * @return float 2314 */ 2315 public static function MAXA(...$args) 2316 { 2317 $returnValue = null; 2318 2319 // Loop through arguments 2320 $aArgs = Functions::flattenArray($args); 2321 foreach ($aArgs as $arg) { 2322 // Is it a numeric value? 2323 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 2324 if (is_bool($arg)) { 2325 $arg = (int) $arg; 2326 } elseif (is_string($arg)) { 2327 $arg = 0; 2328 } 2329 if (($returnValue === null) || ($arg > $returnValue)) { 2330 $returnValue = $arg; 2331 } 2332 } 2333 } 2334 2335 if ($returnValue === null) { 2336 return 0; 2337 } 2338 2339 return $returnValue; 2340 } 2341 2342 /** 2343 * MAXIFS. 2344 * 2345 * Counts the maximum value within a range of cells that contain numbers within the list of arguments 2346 * 2347 * Excel Function: 2348 * MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 2349 * 2350 * @param mixed $args Data range and criterias 2351 * 2352 * @return float 2353 */ 2354 public static function MAXIFS(...$args) 2355 { 2356 $arrayList = $args; 2357 2358 // Return value 2359 $returnValue = null; 2360 2361 $maxArgs = Functions::flattenArray(array_shift($arrayList)); 2362 $aArgsArray = []; 2363 $conditions = []; 2364 2365 while (count($arrayList) > 0) { 2366 $aArgsArray[] = Functions::flattenArray(array_shift($arrayList)); 2367 $conditions[] = Functions::ifCondition(array_shift($arrayList)); 2368 } 2369 2370 // Loop through each arg and see if arguments and conditions are true 2371 foreach ($maxArgs as $index => $value) { 2372 $valid = true; 2373 2374 foreach ($conditions as $cidx => $condition) { 2375 $arg = $aArgsArray[$cidx][$index]; 2376 2377 // Loop through arguments 2378 if (!is_numeric($arg)) { 2379 $arg = Calculation::wrapResult(strtoupper($arg)); 2380 } 2381 $testCondition = '=' . $arg . $condition; 2382 if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 2383 // Is not a value within our criteria 2384 $valid = false; 2385 2386 break; // if false found, don't need to check other conditions 2387 } 2388 } 2389 2390 if ($valid) { 2391 $returnValue = $returnValue === null ? $value : max($value, $returnValue); 2392 } 2393 } 2394 2395 // Return 2396 return $returnValue; 2397 } 2398 2399 /** 2400 * MEDIAN. 2401 * 2402 * Returns the median of the given numbers. The median is the number in the middle of a set of numbers. 2403 * 2404 * Excel Function: 2405 * MEDIAN(value1[,value2[, ...]]) 2406 * 2407 * @param mixed ...$args Data values 2408 * 2409 * @return float|string The result, or a string containing an error 2410 */ 2411 public static function MEDIAN(...$args) 2412 { 2413 $returnValue = Functions::NAN(); 2414 2415 $mArgs = []; 2416 // Loop through arguments 2417 $aArgs = Functions::flattenArray($args); 2418 foreach ($aArgs as $arg) { 2419 // Is it a numeric value? 2420 if ((is_numeric($arg)) && (!is_string($arg))) { 2421 $mArgs[] = $arg; 2422 } 2423 } 2424 2425 $mValueCount = count($mArgs); 2426 if ($mValueCount > 0) { 2427 sort($mArgs, SORT_NUMERIC); 2428 $mValueCount = $mValueCount / 2; 2429 if ($mValueCount == floor($mValueCount)) { 2430 $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2; 2431 } else { 2432 $mValueCount = floor($mValueCount); 2433 $returnValue = $mArgs[$mValueCount]; 2434 } 2435 } 2436 2437 return $returnValue; 2438 } 2439 2440 /** 2441 * MIN. 2442 * 2443 * MIN returns the value of the element of the values passed that has the smallest value, 2444 * with negative numbers considered smaller than positive numbers. 2445 * 2446 * Excel Function: 2447 * MIN(value1[,value2[, ...]]) 2448 * 2449 * @param mixed ...$args Data values 2450 * 2451 * @return float 2452 */ 2453 public static function MIN(...$args) 2454 { 2455 $returnValue = null; 2456 2457 // Loop through arguments 2458 $aArgs = Functions::flattenArray($args); 2459 foreach ($aArgs as $arg) { 2460 // Is it a numeric value? 2461 if ((is_numeric($arg)) && (!is_string($arg))) { 2462 if (($returnValue === null) || ($arg < $returnValue)) { 2463 $returnValue = $arg; 2464 } 2465 } 2466 } 2467 2468 if ($returnValue === null) { 2469 return 0; 2470 } 2471 2472 return $returnValue; 2473 } 2474 2475 /** 2476 * MINA. 2477 * 2478 * Returns the smallest value in a list of arguments, including numbers, text, and logical values 2479 * 2480 * Excel Function: 2481 * MINA(value1[,value2[, ...]]) 2482 * 2483 * @param mixed ...$args Data values 2484 * 2485 * @return float 2486 */ 2487 public static function MINA(...$args) 2488 { 2489 $returnValue = null; 2490 2491 // Loop through arguments 2492 $aArgs = Functions::flattenArray($args); 2493 foreach ($aArgs as $arg) { 2494 // Is it a numeric value? 2495 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { 2496 if (is_bool($arg)) { 2497 $arg = (int) $arg; 2498 } elseif (is_string($arg)) { 2499 $arg = 0; 2500 } 2501 if (($returnValue === null) || ($arg < $returnValue)) { 2502 $returnValue = $arg; 2503 } 2504 } 2505 } 2506 2507 if ($returnValue === null) { 2508 return 0; 2509 } 2510 2511 return $returnValue; 2512 } 2513 2514 /** 2515 * MINIFS. 2516 * 2517 * Returns the minimum value within a range of cells that contain numbers within the list of arguments 2518 * 2519 * Excel Function: 2520 * MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 2521 * 2522 * @param mixed $args Data range and criterias 2523 * 2524 * @return float 2525 */ 2526 public static function MINIFS(...$args) 2527 { 2528 $arrayList = $args; 2529 2530 // Return value 2531 $returnValue = null; 2532 2533 $minArgs = Functions::flattenArray(array_shift($arrayList)); 2534 $aArgsArray = []; 2535 $conditions = []; 2536 2537 while (count($arrayList) > 0) { 2538 $aArgsArray[] = Functions::flattenArray(array_shift($arrayList)); 2539 $conditions[] = Functions::ifCondition(array_shift($arrayList)); 2540 } 2541 2542 // Loop through each arg and see if arguments and conditions are true 2543 foreach ($minArgs as $index => $value) { 2544 $valid = true; 2545 2546 foreach ($conditions as $cidx => $condition) { 2547 $arg = $aArgsArray[$cidx][$index]; 2548 2549 // Loop through arguments 2550 if (!is_numeric($arg)) { 2551 $arg = Calculation::wrapResult(strtoupper($arg)); 2552 } 2553 $testCondition = '=' . $arg . $condition; 2554 if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) { 2555 // Is not a value within our criteria 2556 $valid = false; 2557 2558 break; // if false found, don't need to check other conditions 2559 } 2560 } 2561 2562 if ($valid) { 2563 $returnValue = $returnValue === null ? $value : min($value, $returnValue); 2564 } 2565 } 2566 2567 // Return 2568 return $returnValue; 2569 } 2570 2571 // 2572 // Special variant of array_count_values that isn't limited to strings and integers, 2573 // but can work with floating point numbers as values 2574 // 2575 private static function modeCalc($data) 2576 { 2577 $frequencyArray = []; 2578 $index = 0; 2579 $maxfreq = 0; 2580 $maxfreqkey = ''; 2581 $maxfreqdatum = ''; 2582 foreach ($data as $datum) { 2583 $found = false; 2584 ++$index; 2585 foreach ($frequencyArray as $key => $value) { 2586 if ((string) $value['value'] == (string) $datum) { 2587 ++$frequencyArray[$key]['frequency']; 2588 $freq = $frequencyArray[$key]['frequency']; 2589 if ($freq > $maxfreq) { 2590 $maxfreq = $freq; 2591 $maxfreqkey = $key; 2592 $maxfreqdatum = $datum; 2593 } elseif ($freq == $maxfreq) { 2594 if ($frequencyArray[$key]['index'] < $frequencyArray[$maxfreqkey]['index']) { 2595 $maxfreqkey = $key; 2596 $maxfreqdatum = $datum; 2597 } 2598 } 2599 $found = true; 2600 2601 break; 2602 } 2603 } 2604 if (!$found) { 2605 $frequencyArray[] = [ 2606 'value' => $datum, 2607 'frequency' => 1, 2608 'index' => $index, 2609 ]; 2610 } 2611 } 2612 2613 if ($maxfreq <= 1) { 2614 return Functions::NA(); 2615 } 2616 2617 return $maxfreqdatum; 2618 } 2619 2620 /** 2621 * MODE. 2622 * 2623 * Returns the most frequently occurring, or repetitive, value in an array or range of data 2624 * 2625 * Excel Function: 2626 * MODE(value1[,value2[, ...]]) 2627 * 2628 * @param mixed ...$args Data values 2629 * 2630 * @return float|string The result, or a string containing an error 2631 */ 2632 public static function MODE(...$args) 2633 { 2634 $returnValue = Functions::NA(); 2635 2636 // Loop through arguments 2637 $aArgs = Functions::flattenArray($args); 2638 2639 $mArgs = []; 2640 foreach ($aArgs as $arg) { 2641 // Is it a numeric value? 2642 if ((is_numeric($arg)) && (!is_string($arg))) { 2643 $mArgs[] = $arg; 2644 } 2645 } 2646 2647 if (!empty($mArgs)) { 2648 return self::modeCalc($mArgs); 2649 } 2650 2651 return $returnValue; 2652 } 2653 2654 /** 2655 * NEGBINOMDIST. 2656 * 2657 * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that 2658 * there will be number_f failures before the number_s-th success, when the constant 2659 * probability of a success is probability_s. This function is similar to the binomial 2660 * distribution, except that the number of successes is fixed, and the number of trials is 2661 * variable. Like the binomial, trials are assumed to be independent. 2662 * 2663 * @param float $failures Number of Failures 2664 * @param float $successes Threshold number of Successes 2665 * @param float $probability Probability of success on each trial 2666 * 2667 * @return float|string The result, or a string containing an error 2668 */ 2669 public static function NEGBINOMDIST($failures, $successes, $probability) 2670 { 2671 $failures = floor(Functions::flattenSingleValue($failures)); 2672 $successes = floor(Functions::flattenSingleValue($successes)); 2673 $probability = Functions::flattenSingleValue($probability); 2674 2675 if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) { 2676 if (($failures < 0) || ($successes < 1)) { 2677 return Functions::NAN(); 2678 } elseif (($probability < 0) || ($probability > 1)) { 2679 return Functions::NAN(); 2680 } 2681 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 2682 if (($failures + $successes - 1) <= 0) { 2683 return Functions::NAN(); 2684 } 2685 } 2686 2687 return (MathTrig::COMBIN($failures + $successes - 1, $successes - 1)) * ($probability ** $successes) * ((1 - $probability) ** $failures); 2688 } 2689 2690 return Functions::VALUE(); 2691 } 2692 2693 /** 2694 * NORMDIST. 2695 * 2696 * Returns the normal distribution for the specified mean and standard deviation. This 2697 * function has a very wide range of applications in statistics, including hypothesis 2698 * testing. 2699 * 2700 * @param float $value 2701 * @param float $mean Mean Value 2702 * @param float $stdDev Standard Deviation 2703 * @param bool $cumulative 2704 * 2705 * @return float|string The result, or a string containing an error 2706 */ 2707 public static function NORMDIST($value, $mean, $stdDev, $cumulative) 2708 { 2709 $value = Functions::flattenSingleValue($value); 2710 $mean = Functions::flattenSingleValue($mean); 2711 $stdDev = Functions::flattenSingleValue($stdDev); 2712 2713 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2714 if ($stdDev < 0) { 2715 return Functions::NAN(); 2716 } 2717 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 2718 if ($cumulative) { 2719 return 0.5 * (1 + Engineering::erfVal(($value - $mean) / ($stdDev * sqrt(2)))); 2720 } 2721 2722 return (1 / (self::SQRT2PI * $stdDev)) * exp(0 - (($value - $mean) ** 2 / (2 * ($stdDev * $stdDev)))); 2723 } 2724 } 2725 2726 return Functions::VALUE(); 2727 } 2728 2729 /** 2730 * NORMINV. 2731 * 2732 * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. 2733 * 2734 * @param float $probability 2735 * @param float $mean Mean Value 2736 * @param float $stdDev Standard Deviation 2737 * 2738 * @return float|string The result, or a string containing an error 2739 */ 2740 public static function NORMINV($probability, $mean, $stdDev) 2741 { 2742 $probability = Functions::flattenSingleValue($probability); 2743 $mean = Functions::flattenSingleValue($mean); 2744 $stdDev = Functions::flattenSingleValue($stdDev); 2745 2746 if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 2747 if (($probability < 0) || ($probability > 1)) { 2748 return Functions::NAN(); 2749 } 2750 if ($stdDev < 0) { 2751 return Functions::NAN(); 2752 } 2753 2754 return (self::inverseNcdf($probability) * $stdDev) + $mean; 2755 } 2756 2757 return Functions::VALUE(); 2758 } 2759 2760 /** 2761 * NORMSDIST. 2762 * 2763 * Returns the standard normal cumulative distribution function. The distribution has 2764 * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a 2765 * table of standard normal curve areas. 2766 * 2767 * @param float $value 2768 * 2769 * @return float|string The result, or a string containing an error 2770 */ 2771 public static function NORMSDIST($value) 2772 { 2773 $value = Functions::flattenSingleValue($value); 2774 if (!is_numeric($value)) { 2775 return Functions::VALUE(); 2776 } 2777 2778 return self::NORMDIST($value, 0, 1, true); 2779 } 2780 2781 /** 2782 * NORM.S.DIST. 2783 * 2784 * Returns the standard normal cumulative distribution function. The distribution has 2785 * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a 2786 * table of standard normal curve areas. 2787 * 2788 * @param float $value 2789 * @param bool $cumulative 2790 * 2791 * @return float|string The result, or a string containing an error 2792 */ 2793 public static function NORMSDIST2($value, $cumulative) 2794 { 2795 $value = Functions::flattenSingleValue($value); 2796 if (!is_numeric($value)) { 2797 return Functions::VALUE(); 2798 } 2799 $cumulative = (bool) Functions::flattenSingleValue($cumulative); 2800 2801 return self::NORMDIST($value, 0, 1, $cumulative); 2802 } 2803 2804 /** 2805 * NORMSINV. 2806 * 2807 * Returns the inverse of the standard normal cumulative distribution 2808 * 2809 * @param float $value 2810 * 2811 * @return float|string The result, or a string containing an error 2812 */ 2813 public static function NORMSINV($value) 2814 { 2815 return self::NORMINV($value, 0, 1); 2816 } 2817 2818 /** 2819 * PERCENTILE. 2820 * 2821 * Returns the nth percentile of values in a range.. 2822 * 2823 * Excel Function: 2824 * PERCENTILE(value1[,value2[, ...]],entry) 2825 * 2826 * @param mixed $args Data values 2827 * 2828 * @return float|string The result, or a string containing an error 2829 */ 2830 public static function PERCENTILE(...$args) 2831 { 2832 $aArgs = Functions::flattenArray($args); 2833 2834 // Calculate 2835 $entry = array_pop($aArgs); 2836 2837 if ((is_numeric($entry)) && (!is_string($entry))) { 2838 if (($entry < 0) || ($entry > 1)) { 2839 return Functions::NAN(); 2840 } 2841 $mArgs = []; 2842 foreach ($aArgs as $arg) { 2843 // Is it a numeric value? 2844 if ((is_numeric($arg)) && (!is_string($arg))) { 2845 $mArgs[] = $arg; 2846 } 2847 } 2848 $mValueCount = count($mArgs); 2849 if ($mValueCount > 0) { 2850 sort($mArgs); 2851 $count = self::COUNT($mArgs); 2852 $index = $entry * ($count - 1); 2853 $iBase = floor($index); 2854 if ($index == $iBase) { 2855 return $mArgs[$index]; 2856 } 2857 $iNext = $iBase + 1; 2858 $iProportion = $index - $iBase; 2859 2860 return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion); 2861 } 2862 } 2863 2864 return Functions::VALUE(); 2865 } 2866 2867 /** 2868 * PERCENTRANK. 2869 * 2870 * Returns the rank of a value in a data set as a percentage of the data set. 2871 * 2872 * @param float[] $valueSet An array of, or a reference to, a list of numbers 2873 * @param int $value the number whose rank you want to find 2874 * @param int $significance the number of significant digits for the returned percentage value 2875 * 2876 * @return float|string (string if result is an error) 2877 */ 2878 public static function PERCENTRANK($valueSet, $value, $significance = 3) 2879 { 2880 $valueSet = Functions::flattenArray($valueSet); 2881 $value = Functions::flattenSingleValue($value); 2882 $significance = ($significance === null) ? 3 : (int) Functions::flattenSingleValue($significance); 2883 2884 foreach ($valueSet as $key => $valueEntry) { 2885 if (!is_numeric($valueEntry)) { 2886 unset($valueSet[$key]); 2887 } 2888 } 2889 sort($valueSet, SORT_NUMERIC); 2890 $valueCount = count($valueSet); 2891 if ($valueCount == 0) { 2892 return Functions::NAN(); 2893 } 2894 2895 $valueAdjustor = $valueCount - 1; 2896 if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) { 2897 return Functions::NA(); 2898 } 2899 2900 $pos = array_search($value, $valueSet); 2901 if ($pos === false) { 2902 $pos = 0; 2903 $testValue = $valueSet[0]; 2904 while ($testValue < $value) { 2905 $testValue = $valueSet[++$pos]; 2906 } 2907 --$pos; 2908 $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos])); 2909 } 2910 2911 return round($pos / $valueAdjustor, $significance); 2912 } 2913 2914 /** 2915 * PERMUT. 2916 * 2917 * Returns the number of permutations for a given number of objects that can be 2918 * selected from number objects. A permutation is any set or subset of objects or 2919 * events where internal order is significant. Permutations are different from 2920 * combinations, for which the internal order is not significant. Use this function 2921 * for lottery-style probability calculations. 2922 * 2923 * @param int $numObjs Number of different objects 2924 * @param int $numInSet Number of objects in each permutation 2925 * 2926 * @return int|string Number of permutations, or a string containing an error 2927 */ 2928 public static function PERMUT($numObjs, $numInSet) 2929 { 2930 $numObjs = Functions::flattenSingleValue($numObjs); 2931 $numInSet = Functions::flattenSingleValue($numInSet); 2932 2933 if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { 2934 $numInSet = floor($numInSet); 2935 if ($numObjs < $numInSet) { 2936 return Functions::NAN(); 2937 } 2938 2939 return round(MathTrig::FACT($numObjs) / MathTrig::FACT($numObjs - $numInSet)); 2940 } 2941 2942 return Functions::VALUE(); 2943 } 2944 2945 /** 2946 * POISSON. 2947 * 2948 * Returns the Poisson distribution. A common application of the Poisson distribution 2949 * is predicting the number of events over a specific time, such as the number of 2950 * cars arriving at a toll plaza in 1 minute. 2951 * 2952 * @param float $value 2953 * @param float $mean Mean Value 2954 * @param bool $cumulative 2955 * 2956 * @return float|string The result, or a string containing an error 2957 */ 2958 public static function POISSON($value, $mean, $cumulative) 2959 { 2960 $value = Functions::flattenSingleValue($value); 2961 $mean = Functions::flattenSingleValue($mean); 2962 2963 if ((is_numeric($value)) && (is_numeric($mean))) { 2964 if (($value < 0) || ($mean <= 0)) { 2965 return Functions::NAN(); 2966 } 2967 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 2968 if ($cumulative) { 2969 $summer = 0; 2970 $floor = floor($value); 2971 for ($i = 0; $i <= $floor; ++$i) { 2972 $summer += $mean ** $i / MathTrig::FACT($i); 2973 } 2974 2975 return exp(0 - $mean) * $summer; 2976 } 2977 2978 return (exp(0 - $mean) * $mean ** $value) / MathTrig::FACT($value); 2979 } 2980 } 2981 2982 return Functions::VALUE(); 2983 } 2984 2985 /** 2986 * QUARTILE. 2987 * 2988 * Returns the quartile of a data set. 2989 * 2990 * Excel Function: 2991 * QUARTILE(value1[,value2[, ...]],entry) 2992 * 2993 * @param mixed $args Data values 2994 * 2995 * @return float|string The result, or a string containing an error 2996 */ 2997 public static function QUARTILE(...$args) 2998 { 2999 $aArgs = Functions::flattenArray($args); 3000 3001 // Calculate 3002 $entry = floor(array_pop($aArgs)); 3003 3004 if ((is_numeric($entry)) && (!is_string($entry))) { 3005 $entry /= 4; 3006 if (($entry < 0) || ($entry > 1)) { 3007 return Functions::NAN(); 3008 } 3009 3010 return self::PERCENTILE($aArgs, $entry); 3011 } 3012 3013 return Functions::VALUE(); 3014 } 3015 3016 /** 3017 * RANK. 3018 * 3019 * Returns the rank of a number in a list of numbers. 3020 * 3021 * @param int $value the number whose rank you want to find 3022 * @param float[] $valueSet An array of, or a reference to, a list of numbers 3023 * @param int $order Order to sort the values in the value set 3024 * 3025 * @return float|string The result, or a string containing an error 3026 */ 3027 public static function RANK($value, $valueSet, $order = 0) 3028 { 3029 $value = Functions::flattenSingleValue($value); 3030 $valueSet = Functions::flattenArray($valueSet); 3031 $order = ($order === null) ? 0 : (int) Functions::flattenSingleValue($order); 3032 3033 foreach ($valueSet as $key => $valueEntry) { 3034 if (!is_numeric($valueEntry)) { 3035 unset($valueSet[$key]); 3036 } 3037 } 3038 3039 if ($order == 0) { 3040 rsort($valueSet, SORT_NUMERIC); 3041 } else { 3042 sort($valueSet, SORT_NUMERIC); 3043 } 3044 $pos = array_search($value, $valueSet); 3045 if ($pos === false) { 3046 return Functions::NA(); 3047 } 3048 3049 return ++$pos; 3050 } 3051 3052 /** 3053 * RSQ. 3054 * 3055 * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. 3056 * 3057 * @param mixed[] $yValues Data Series Y 3058 * @param mixed[] $xValues Data Series X 3059 * 3060 * @return float|string The result, or a string containing an error 3061 */ 3062 public static function RSQ($yValues, $xValues) 3063 { 3064 if (!self::checkTrendArrays($yValues, $xValues)) { 3065 return Functions::VALUE(); 3066 } 3067 $yValueCount = count($yValues); 3068 $xValueCount = count($xValues); 3069 3070 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 3071 return Functions::NA(); 3072 } elseif ($yValueCount == 1) { 3073 return Functions::DIV0(); 3074 } 3075 3076 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 3077 3078 return $bestFitLinear->getGoodnessOfFit(); 3079 } 3080 3081 /** 3082 * SKEW. 3083 * 3084 * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry 3085 * of a distribution around its mean. Positive skewness indicates a distribution with an 3086 * asymmetric tail extending toward more positive values. Negative skewness indicates a 3087 * distribution with an asymmetric tail extending toward more negative values. 3088 * 3089 * @param array ...$args Data Series 3090 * 3091 * @return float|string The result, or a string containing an error 3092 */ 3093 public static function SKEW(...$args) 3094 { 3095 $aArgs = Functions::flattenArrayIndexed($args); 3096 $mean = self::AVERAGE($aArgs); 3097 $stdDev = self::STDEV($aArgs); 3098 3099 $count = $summer = 0; 3100 // Loop through arguments 3101 foreach ($aArgs as $k => $arg) { 3102 if ( 3103 (is_bool($arg)) && 3104 (!Functions::isMatrixValue($k)) 3105 ) { 3106 } else { 3107 // Is it a numeric value? 3108 if ((is_numeric($arg)) && (!is_string($arg))) { 3109 $summer += (($arg - $mean) / $stdDev) ** 3; 3110 ++$count; 3111 } 3112 } 3113 } 3114 3115 if ($count > 2) { 3116 return $summer * ($count / (($count - 1) * ($count - 2))); 3117 } 3118 3119 return Functions::DIV0(); 3120 } 3121 3122 /** 3123 * SLOPE. 3124 * 3125 * Returns the slope of the linear regression line through data points in known_y's and known_x's. 3126 * 3127 * @param mixed[] $yValues Data Series Y 3128 * @param mixed[] $xValues Data Series X 3129 * 3130 * @return float|string The result, or a string containing an error 3131 */ 3132 public static function SLOPE($yValues, $xValues) 3133 { 3134 if (!self::checkTrendArrays($yValues, $xValues)) { 3135 return Functions::VALUE(); 3136 } 3137 $yValueCount = count($yValues); 3138 $xValueCount = count($xValues); 3139 3140 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 3141 return Functions::NA(); 3142 } elseif ($yValueCount == 1) { 3143 return Functions::DIV0(); 3144 } 3145 3146 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 3147 3148 return $bestFitLinear->getSlope(); 3149 } 3150 3151 /** 3152 * SMALL. 3153 * 3154 * Returns the nth smallest value in a data set. You can use this function to 3155 * select a value based on its relative standing. 3156 * 3157 * Excel Function: 3158 * SMALL(value1[,value2[, ...]],entry) 3159 * 3160 * @param mixed $args Data values 3161 * 3162 * @return float|string The result, or a string containing an error 3163 */ 3164 public static function SMALL(...$args) 3165 { 3166 $aArgs = Functions::flattenArray($args); 3167 3168 // Calculate 3169 $entry = array_pop($aArgs); 3170 3171 if ((is_numeric($entry)) && (!is_string($entry))) { 3172 $entry = (int) floor($entry); 3173 3174 $mArgs = []; 3175 foreach ($aArgs as $arg) { 3176 // Is it a numeric value? 3177 if ((is_numeric($arg)) && (!is_string($arg))) { 3178 $mArgs[] = $arg; 3179 } 3180 } 3181 $count = self::COUNT($mArgs); 3182 --$entry; 3183 if (($entry < 0) || ($entry >= $count) || ($count == 0)) { 3184 return Functions::NAN(); 3185 } 3186 sort($mArgs); 3187 3188 return $mArgs[$entry]; 3189 } 3190 3191 return Functions::VALUE(); 3192 } 3193 3194 /** 3195 * STANDARDIZE. 3196 * 3197 * Returns a normalized value from a distribution characterized by mean and standard_dev. 3198 * 3199 * @param float $value Value to normalize 3200 * @param float $mean Mean Value 3201 * @param float $stdDev Standard Deviation 3202 * 3203 * @return float|string Standardized value, or a string containing an error 3204 */ 3205 public static function STANDARDIZE($value, $mean, $stdDev) 3206 { 3207 $value = Functions::flattenSingleValue($value); 3208 $mean = Functions::flattenSingleValue($mean); 3209 $stdDev = Functions::flattenSingleValue($stdDev); 3210 3211 if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { 3212 if ($stdDev <= 0) { 3213 return Functions::NAN(); 3214 } 3215 3216 return ($value - $mean) / $stdDev; 3217 } 3218 3219 return Functions::VALUE(); 3220 } 3221 3222 /** 3223 * STDEV. 3224 * 3225 * Estimates standard deviation based on a sample. The standard deviation is a measure of how 3226 * widely values are dispersed from the average value (the mean). 3227 * 3228 * Excel Function: 3229 * STDEV(value1[,value2[, ...]]) 3230 * 3231 * @param mixed ...$args Data values 3232 * 3233 * @return float|string The result, or a string containing an error 3234 */ 3235 public static function STDEV(...$args) 3236 { 3237 $aArgs = Functions::flattenArrayIndexed($args); 3238 3239 // Return value 3240 $returnValue = null; 3241 3242 $aMean = self::AVERAGE($aArgs); 3243 if ($aMean !== null) { 3244 $aCount = -1; 3245 foreach ($aArgs as $k => $arg) { 3246 if ( 3247 (is_bool($arg)) && 3248 ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE)) 3249 ) { 3250 $arg = (int) $arg; 3251 } 3252 // Is it a numeric value? 3253 if ((is_numeric($arg)) && (!is_string($arg))) { 3254 if ($returnValue === null) { 3255 $returnValue = ($arg - $aMean) ** 2; 3256 } else { 3257 $returnValue += ($arg - $aMean) ** 2; 3258 } 3259 ++$aCount; 3260 } 3261 } 3262 3263 // Return 3264 if (($aCount > 0) && ($returnValue >= 0)) { 3265 return sqrt($returnValue / $aCount); 3266 } 3267 } 3268 3269 return Functions::DIV0(); 3270 } 3271 3272 /** 3273 * STDEVA. 3274 * 3275 * Estimates standard deviation based on a sample, including numbers, text, and logical values 3276 * 3277 * Excel Function: 3278 * STDEVA(value1[,value2[, ...]]) 3279 * 3280 * @param mixed ...$args Data values 3281 * 3282 * @return float|string 3283 */ 3284 public static function STDEVA(...$args) 3285 { 3286 $aArgs = Functions::flattenArrayIndexed($args); 3287 3288 $returnValue = null; 3289 3290 $aMean = self::AVERAGEA($aArgs); 3291 if ($aMean !== null) { 3292 $aCount = -1; 3293 foreach ($aArgs as $k => $arg) { 3294 if ( 3295 (is_bool($arg)) && 3296 (!Functions::isMatrixValue($k)) 3297 ) { 3298 } else { 3299 // Is it a numeric value? 3300 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3301 if (is_bool($arg)) { 3302 $arg = (int) $arg; 3303 } elseif (is_string($arg)) { 3304 $arg = 0; 3305 } 3306 if ($returnValue === null) { 3307 $returnValue = ($arg - $aMean) ** 2; 3308 } else { 3309 $returnValue += ($arg - $aMean) ** 2; 3310 } 3311 ++$aCount; 3312 } 3313 } 3314 } 3315 3316 if (($aCount > 0) && ($returnValue >= 0)) { 3317 return sqrt($returnValue / $aCount); 3318 } 3319 } 3320 3321 return Functions::DIV0(); 3322 } 3323 3324 /** 3325 * STDEVP. 3326 * 3327 * Calculates standard deviation based on the entire population 3328 * 3329 * Excel Function: 3330 * STDEVP(value1[,value2[, ...]]) 3331 * 3332 * @param mixed ...$args Data values 3333 * 3334 * @return float|string 3335 */ 3336 public static function STDEVP(...$args) 3337 { 3338 $aArgs = Functions::flattenArrayIndexed($args); 3339 3340 $returnValue = null; 3341 3342 $aMean = self::AVERAGE($aArgs); 3343 if ($aMean !== null) { 3344 $aCount = 0; 3345 foreach ($aArgs as $k => $arg) { 3346 if ( 3347 (is_bool($arg)) && 3348 ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE)) 3349 ) { 3350 $arg = (int) $arg; 3351 } 3352 // Is it a numeric value? 3353 if ((is_numeric($arg)) && (!is_string($arg))) { 3354 if ($returnValue === null) { 3355 $returnValue = ($arg - $aMean) ** 2; 3356 } else { 3357 $returnValue += ($arg - $aMean) ** 2; 3358 } 3359 ++$aCount; 3360 } 3361 } 3362 3363 if (($aCount > 0) && ($returnValue >= 0)) { 3364 return sqrt($returnValue / $aCount); 3365 } 3366 } 3367 3368 return Functions::DIV0(); 3369 } 3370 3371 /** 3372 * STDEVPA. 3373 * 3374 * Calculates standard deviation based on the entire population, including numbers, text, and logical values 3375 * 3376 * Excel Function: 3377 * STDEVPA(value1[,value2[, ...]]) 3378 * 3379 * @param mixed ...$args Data values 3380 * 3381 * @return float|string 3382 */ 3383 public static function STDEVPA(...$args) 3384 { 3385 $aArgs = Functions::flattenArrayIndexed($args); 3386 3387 $returnValue = null; 3388 3389 $aMean = self::AVERAGEA($aArgs); 3390 if ($aMean !== null) { 3391 $aCount = 0; 3392 foreach ($aArgs as $k => $arg) { 3393 if ( 3394 (is_bool($arg)) && 3395 (!Functions::isMatrixValue($k)) 3396 ) { 3397 } else { 3398 // Is it a numeric value? 3399 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3400 if (is_bool($arg)) { 3401 $arg = (int) $arg; 3402 } elseif (is_string($arg)) { 3403 $arg = 0; 3404 } 3405 if ($returnValue === null) { 3406 $returnValue = ($arg - $aMean) ** 2; 3407 } else { 3408 $returnValue += ($arg - $aMean) ** 2; 3409 } 3410 ++$aCount; 3411 } 3412 } 3413 } 3414 3415 if (($aCount > 0) && ($returnValue >= 0)) { 3416 return sqrt($returnValue / $aCount); 3417 } 3418 } 3419 3420 return Functions::DIV0(); 3421 } 3422 3423 /** 3424 * STEYX. 3425 * 3426 * Returns the standard error of the predicted y-value for each x in the regression. 3427 * 3428 * @param mixed[] $yValues Data Series Y 3429 * @param mixed[] $xValues Data Series X 3430 * 3431 * @return float|string 3432 */ 3433 public static function STEYX($yValues, $xValues) 3434 { 3435 if (!self::checkTrendArrays($yValues, $xValues)) { 3436 return Functions::VALUE(); 3437 } 3438 $yValueCount = count($yValues); 3439 $xValueCount = count($xValues); 3440 3441 if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { 3442 return Functions::NA(); 3443 } elseif ($yValueCount == 1) { 3444 return Functions::DIV0(); 3445 } 3446 3447 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); 3448 3449 return $bestFitLinear->getStdevOfResiduals(); 3450 } 3451 3452 /** 3453 * TDIST. 3454 * 3455 * Returns the probability of Student's T distribution. 3456 * 3457 * @param float $value Value for the function 3458 * @param float $degrees degrees of freedom 3459 * @param float $tails number of tails (1 or 2) 3460 * 3461 * @return float|string The result, or a string containing an error 3462 */ 3463 public static function TDIST($value, $degrees, $tails) 3464 { 3465 $value = Functions::flattenSingleValue($value); 3466 $degrees = floor(Functions::flattenSingleValue($degrees)); 3467 $tails = floor(Functions::flattenSingleValue($tails)); 3468 3469 if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) { 3470 if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) { 3471 return Functions::NAN(); 3472 } 3473 // tdist, which finds the probability that corresponds to a given value 3474 // of t with k degrees of freedom. This algorithm is translated from a 3475 // pascal function on p81 of "Statistical Computing in Pascal" by D 3476 // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd: 3477 // London). The above Pascal algorithm is itself a translation of the 3478 // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer 3479 // Laboratory as reported in (among other places) "Applied Statistics 3480 // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis 3481 // Horwood Ltd.; W. Sussex, England). 3482 $tterm = $degrees; 3483 $ttheta = atan2($value, sqrt($tterm)); 3484 $tc = cos($ttheta); 3485 $ts = sin($ttheta); 3486 3487 if (($degrees % 2) == 1) { 3488 $ti = 3; 3489 $tterm = $tc; 3490 } else { 3491 $ti = 2; 3492 $tterm = 1; 3493 } 3494 3495 $tsum = $tterm; 3496 while ($ti < $degrees) { 3497 $tterm *= $tc * $tc * ($ti - 1) / $ti; 3498 $tsum += $tterm; 3499 $ti += 2; 3500 } 3501 $tsum *= $ts; 3502 if (($degrees % 2) == 1) { 3503 $tsum = Functions::M_2DIVPI * ($tsum + $ttheta); 3504 } 3505 $tValue = 0.5 * (1 + $tsum); 3506 if ($tails == 1) { 3507 return 1 - abs($tValue); 3508 } 3509 3510 return 1 - abs((1 - $tValue) - $tValue); 3511 } 3512 3513 return Functions::VALUE(); 3514 } 3515 3516 /** 3517 * TINV. 3518 * 3519 * Returns the one-tailed probability of the chi-squared distribution. 3520 * 3521 * @param float $probability Probability for the function 3522 * @param float $degrees degrees of freedom 3523 * 3524 * @return float|string The result, or a string containing an error 3525 */ 3526 public static function TINV($probability, $degrees) 3527 { 3528 $probability = Functions::flattenSingleValue($probability); 3529 $degrees = floor(Functions::flattenSingleValue($degrees)); 3530 3531 if ((is_numeric($probability)) && (is_numeric($degrees))) { 3532 $xLo = 100; 3533 $xHi = 0; 3534 3535 $x = $xNew = 1; 3536 $dx = 1; 3537 $i = 0; 3538 3539 while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { 3540 // Apply Newton-Raphson step 3541 $result = self::TDIST($x, $degrees, 2); 3542 $error = $result - $probability; 3543 if ($error == 0.0) { 3544 $dx = 0; 3545 } elseif ($error < 0.0) { 3546 $xLo = $x; 3547 } else { 3548 $xHi = $x; 3549 } 3550 // Avoid division by zero 3551 if ($result != 0.0) { 3552 $dx = $error / $result; 3553 $xNew = $x - $dx; 3554 } 3555 // If the NR fails to converge (which for example may be the 3556 // case if the initial guess is too rough) we apply a bisection 3557 // step to determine a more narrow interval around the root. 3558 if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) { 3559 $xNew = ($xLo + $xHi) / 2; 3560 $dx = $xNew - $x; 3561 } 3562 $x = $xNew; 3563 } 3564 if ($i == self::MAX_ITERATIONS) { 3565 return Functions::NA(); 3566 } 3567 3568 return round($x, 12); 3569 } 3570 3571 return Functions::VALUE(); 3572 } 3573 3574 /** 3575 * TREND. 3576 * 3577 * Returns values along a linear Trend 3578 * 3579 * @param mixed[] $yValues Data Series Y 3580 * @param mixed[] $xValues Data Series X 3581 * @param mixed[] $newValues Values of X for which we want to find Y 3582 * @param bool $const a logical value specifying whether to force the intersect to equal 0 3583 * 3584 * @return array of float 3585 */ 3586 public static function TREND($yValues, $xValues = [], $newValues = [], $const = true) 3587 { 3588 $yValues = Functions::flattenArray($yValues); 3589 $xValues = Functions::flattenArray($xValues); 3590 $newValues = Functions::flattenArray($newValues); 3591 $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); 3592 3593 $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const); 3594 if (empty($newValues)) { 3595 $newValues = $bestFitLinear->getXValues(); 3596 } 3597 3598 $returnArray = []; 3599 foreach ($newValues as $xValue) { 3600 $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue); 3601 } 3602 3603 return $returnArray; 3604 } 3605 3606 /** 3607 * TRIMMEAN. 3608 * 3609 * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean 3610 * taken by excluding a percentage of data points from the top and bottom tails 3611 * of a data set. 3612 * 3613 * Excel Function: 3614 * TRIMEAN(value1[,value2[, ...]], $discard) 3615 * 3616 * @param mixed $args Data values 3617 * 3618 * @return float|string 3619 */ 3620 public static function TRIMMEAN(...$args) 3621 { 3622 $aArgs = Functions::flattenArray($args); 3623 3624 // Calculate 3625 $percent = array_pop($aArgs); 3626 3627 if ((is_numeric($percent)) && (!is_string($percent))) { 3628 if (($percent < 0) || ($percent > 1)) { 3629 return Functions::NAN(); 3630 } 3631 $mArgs = []; 3632 foreach ($aArgs as $arg) { 3633 // Is it a numeric value? 3634 if ((is_numeric($arg)) && (!is_string($arg))) { 3635 $mArgs[] = $arg; 3636 } 3637 } 3638 $discard = floor(self::COUNT($mArgs) * $percent / 2); 3639 sort($mArgs); 3640 for ($i = 0; $i < $discard; ++$i) { 3641 array_pop($mArgs); 3642 array_shift($mArgs); 3643 } 3644 3645 return self::AVERAGE($mArgs); 3646 } 3647 3648 return Functions::VALUE(); 3649 } 3650 3651 /** 3652 * VARFunc. 3653 * 3654 * Estimates variance based on a sample. 3655 * 3656 * Excel Function: 3657 * VAR(value1[,value2[, ...]]) 3658 * 3659 * @param mixed ...$args Data values 3660 * 3661 * @return float|string (string if result is an error) 3662 */ 3663 public static function VARFunc(...$args) 3664 { 3665 $returnValue = Functions::DIV0(); 3666 3667 $summerA = $summerB = 0; 3668 3669 // Loop through arguments 3670 $aArgs = Functions::flattenArray($args); 3671 $aCount = 0; 3672 foreach ($aArgs as $arg) { 3673 if (is_bool($arg)) { 3674 $arg = (int) $arg; 3675 } 3676 // Is it a numeric value? 3677 if ((is_numeric($arg)) && (!is_string($arg))) { 3678 $summerA += ($arg * $arg); 3679 $summerB += $arg; 3680 ++$aCount; 3681 } 3682 } 3683 3684 if ($aCount > 1) { 3685 $summerA *= $aCount; 3686 $summerB *= $summerB; 3687 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); 3688 } 3689 3690 return $returnValue; 3691 } 3692 3693 /** 3694 * VARA. 3695 * 3696 * Estimates variance based on a sample, including numbers, text, and logical values 3697 * 3698 * Excel Function: 3699 * VARA(value1[,value2[, ...]]) 3700 * 3701 * @param mixed ...$args Data values 3702 * 3703 * @return float|string (string if result is an error) 3704 */ 3705 public static function VARA(...$args) 3706 { 3707 $returnValue = Functions::DIV0(); 3708 3709 $summerA = $summerB = 0; 3710 3711 // Loop through arguments 3712 $aArgs = Functions::flattenArrayIndexed($args); 3713 $aCount = 0; 3714 foreach ($aArgs as $k => $arg) { 3715 if ( 3716 (is_string($arg)) && 3717 (Functions::isValue($k)) 3718 ) { 3719 return Functions::VALUE(); 3720 } elseif ( 3721 (is_string($arg)) && 3722 (!Functions::isMatrixValue($k)) 3723 ) { 3724 } else { 3725 // Is it a numeric value? 3726 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3727 if (is_bool($arg)) { 3728 $arg = (int) $arg; 3729 } elseif (is_string($arg)) { 3730 $arg = 0; 3731 } 3732 $summerA += ($arg * $arg); 3733 $summerB += $arg; 3734 ++$aCount; 3735 } 3736 } 3737 } 3738 3739 if ($aCount > 1) { 3740 $summerA *= $aCount; 3741 $summerB *= $summerB; 3742 $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); 3743 } 3744 3745 return $returnValue; 3746 } 3747 3748 /** 3749 * VARP. 3750 * 3751 * Calculates variance based on the entire population 3752 * 3753 * Excel Function: 3754 * VARP(value1[,value2[, ...]]) 3755 * 3756 * @param mixed ...$args Data values 3757 * 3758 * @return float|string (string if result is an error) 3759 */ 3760 public static function VARP(...$args) 3761 { 3762 // Return value 3763 $returnValue = Functions::DIV0(); 3764 3765 $summerA = $summerB = 0; 3766 3767 // Loop through arguments 3768 $aArgs = Functions::flattenArray($args); 3769 $aCount = 0; 3770 foreach ($aArgs as $arg) { 3771 if (is_bool($arg)) { 3772 $arg = (int) $arg; 3773 } 3774 // Is it a numeric value? 3775 if ((is_numeric($arg)) && (!is_string($arg))) { 3776 $summerA += ($arg * $arg); 3777 $summerB += $arg; 3778 ++$aCount; 3779 } 3780 } 3781 3782 if ($aCount > 0) { 3783 $summerA *= $aCount; 3784 $summerB *= $summerB; 3785 $returnValue = ($summerA - $summerB) / ($aCount * $aCount); 3786 } 3787 3788 return $returnValue; 3789 } 3790 3791 /** 3792 * VARPA. 3793 * 3794 * Calculates variance based on the entire population, including numbers, text, and logical values 3795 * 3796 * Excel Function: 3797 * VARPA(value1[,value2[, ...]]) 3798 * 3799 * @param mixed ...$args Data values 3800 * 3801 * @return float|string (string if result is an error) 3802 */ 3803 public static function VARPA(...$args) 3804 { 3805 $returnValue = Functions::DIV0(); 3806 3807 $summerA = $summerB = 0; 3808 3809 // Loop through arguments 3810 $aArgs = Functions::flattenArrayIndexed($args); 3811 $aCount = 0; 3812 foreach ($aArgs as $k => $arg) { 3813 if ( 3814 (is_string($arg)) && 3815 (Functions::isValue($k)) 3816 ) { 3817 return Functions::VALUE(); 3818 } elseif ( 3819 (is_string($arg)) && 3820 (!Functions::isMatrixValue($k)) 3821 ) { 3822 } else { 3823 // Is it a numeric value? 3824 if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { 3825 if (is_bool($arg)) { 3826 $arg = (int) $arg; 3827 } elseif (is_string($arg)) { 3828 $arg = 0; 3829 } 3830 $summerA += ($arg * $arg); 3831 $summerB += $arg; 3832 ++$aCount; 3833 } 3834 } 3835 } 3836 3837 if ($aCount > 0) { 3838 $summerA *= $aCount; 3839 $summerB *= $summerB; 3840 $returnValue = ($summerA - $summerB) / ($aCount * $aCount); 3841 } 3842 3843 return $returnValue; 3844 } 3845 3846 /** 3847 * WEIBULL. 3848 * 3849 * Returns the Weibull distribution. Use this distribution in reliability 3850 * analysis, such as calculating a device's mean time to failure. 3851 * 3852 * @param float $value 3853 * @param float $alpha Alpha Parameter 3854 * @param float $beta Beta Parameter 3855 * @param bool $cumulative 3856 * 3857 * @return float|string (string if result is an error) 3858 */ 3859 public static function WEIBULL($value, $alpha, $beta, $cumulative) 3860 { 3861 $value = Functions::flattenSingleValue($value); 3862 $alpha = Functions::flattenSingleValue($alpha); 3863 $beta = Functions::flattenSingleValue($beta); 3864 3865 if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) { 3866 if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) { 3867 return Functions::NAN(); 3868 } 3869 if ((is_numeric($cumulative)) || (is_bool($cumulative))) { 3870 if ($cumulative) { 3871 return 1 - exp(0 - ($value / $beta) ** $alpha); 3872 } 3873 3874 return ($alpha / $beta ** $alpha) * $value ** ($alpha - 1) * exp(0 - ($value / $beta) ** $alpha); 3875 } 3876 } 3877 3878 return Functions::VALUE(); 3879 } 3880 3881 /** 3882 * ZTEST. 3883 * 3884 * Returns the Weibull distribution. Use this distribution in reliability 3885 * analysis, such as calculating a device's mean time to failure. 3886 * 3887 * @param float $dataSet 3888 * @param float $m0 Alpha Parameter 3889 * @param float $sigma Beta Parameter 3890 * 3891 * @return float|string (string if result is an error) 3892 */ 3893 public static function ZTEST($dataSet, $m0, $sigma = null) 3894 { 3895 $dataSet = Functions::flattenArrayIndexed($dataSet); 3896 $m0 = Functions::flattenSingleValue($m0); 3897 $sigma = Functions::flattenSingleValue($sigma); 3898 3899 if ($sigma === null) { 3900 $sigma = self::STDEV($dataSet); 3901 } 3902 $n = count($dataSet); 3903 3904 return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / sqrt($n))); 3905 } 3906 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
