diff options
| author | Anton Luka Šijanec <anton@sijanec.eu> | 2024-05-27 13:08:29 +0200 |
|---|---|---|
| committer | Anton Luka Šijanec <anton@sijanec.eu> | 2024-05-27 13:08:29 +0200 |
| commit | 75160b12821f7f4299cce7f0b69c83c1502ae071 (patch) | |
| tree | 27e25e4ccaef45f0c58b22831164050d1af1d4db /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php | |
| parent | prvi-commit (diff) | |
| download | 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar.gz 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar.bz2 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar.lz 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar.xz 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.tar.zst 1ka-75160b12821f7f4299cce7f0b69c83c1502ae071.zip | |
Diffstat (limited to 'vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php')
| -rw-r--r-- | vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php | 3906 |
1 files changed, 3906 insertions, 0 deletions
diff --git a/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php b/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php new file mode 100644 index 0000000..641e9d2 --- /dev/null +++ b/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical.php @@ -0,0 +1,3906 @@ +<?php
+
+namespace PhpOffice\PhpSpreadsheet\Calculation;
+
+use PhpOffice\PhpSpreadsheet\Shared\Trend\Trend;
+
+class Statistical
+{
+ const LOG_GAMMA_X_MAX_VALUE = 2.55e305;
+ const XMININ = 2.23e-308;
+ const EPS = 2.22e-16;
+ const MAX_VALUE = 1.2e308;
+ const MAX_ITERATIONS = 256;
+ const SQRT2PI = 2.5066282746310005024157652848110452530069867406099;
+
+ private static function checkTrendArrays(&$array1, &$array2)
+ {
+ if (!is_array($array1)) {
+ $array1 = [$array1];
+ }
+ if (!is_array($array2)) {
+ $array2 = [$array2];
+ }
+
+ $array1 = Functions::flattenArray($array1);
+ $array2 = Functions::flattenArray($array2);
+ foreach ($array1 as $key => $value) {
+ if ((is_bool($value)) || (is_string($value)) || ($value === null)) {
+ unset($array1[$key], $array2[$key]);
+ }
+ }
+ foreach ($array2 as $key => $value) {
+ if ((is_bool($value)) || (is_string($value)) || ($value === null)) {
+ unset($array1[$key], $array2[$key]);
+ }
+ }
+ $array1 = array_merge($array1);
+ $array2 = array_merge($array2);
+
+ return true;
+ }
+
+ /**
+ * Incomplete beta function.
+ *
+ * @author Jaco van Kooten
+ * @author Paul Meagher
+ *
+ * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).
+ *
+ * @param mixed $x require 0<=x<=1
+ * @param mixed $p require p>0
+ * @param mixed $q require q>0
+ *
+ * @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
+ */
+ private static function incompleteBeta($x, $p, $q)
+ {
+ if ($x <= 0.0) {
+ return 0.0;
+ } elseif ($x >= 1.0) {
+ return 1.0;
+ } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > self::LOG_GAMMA_X_MAX_VALUE)) {
+ return 0.0;
+ }
+ $beta_gam = exp((0 - self::logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
+ if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
+ return $beta_gam * self::betaFraction($x, $p, $q) / $p;
+ }
+
+ return 1.0 - ($beta_gam * self::betaFraction(1 - $x, $q, $p) / $q);
+ }
+
+ // Function cache for logBeta function
+ private static $logBetaCacheP = 0.0;
+
+ private static $logBetaCacheQ = 0.0;
+
+ private static $logBetaCacheResult = 0.0;
+
+ /**
+ * The natural logarithm of the beta function.
+ *
+ * @param mixed $p require p>0
+ * @param mixed $q require q>0
+ *
+ * @return float 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
+ *
+ * @author Jaco van Kooten
+ */
+ private static function logBeta($p, $q)
+ {
+ if ($p != self::$logBetaCacheP || $q != self::$logBetaCacheQ) {
+ self::$logBetaCacheP = $p;
+ self::$logBetaCacheQ = $q;
+ if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > self::LOG_GAMMA_X_MAX_VALUE)) {
+ self::$logBetaCacheResult = 0.0;
+ } else {
+ self::$logBetaCacheResult = self::logGamma($p) + self::logGamma($q) - self::logGamma($p + $q);
+ }
+ }
+
+ return self::$logBetaCacheResult;
+ }
+
+ /**
+ * Evaluates of continued fraction part of incomplete beta function.
+ * Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
+ *
+ * @author Jaco van Kooten
+ *
+ * @param mixed $x
+ * @param mixed $p
+ * @param mixed $q
+ *
+ * @return float
+ */
+ private static function betaFraction($x, $p, $q)
+ {
+ $c = 1.0;
+ $sum_pq = $p + $q;
+ $p_plus = $p + 1.0;
+ $p_minus = $p - 1.0;
+ $h = 1.0 - $sum_pq * $x / $p_plus;
+ if (abs($h) < self::XMININ) {
+ $h = self::XMININ;
+ }
+ $h = 1.0 / $h;
+ $frac = $h;
+ $m = 1;
+ $delta = 0.0;
+ while ($m <= self::MAX_ITERATIONS && abs($delta - 1.0) > Functions::PRECISION) {
+ $m2 = 2 * $m;
+ // even index for d
+ $d = $m * ($q - $m) * $x / (($p_minus + $m2) * ($p + $m2));
+ $h = 1.0 + $d * $h;
+ if (abs($h) < self::XMININ) {
+ $h = self::XMININ;
+ }
+ $h = 1.0 / $h;
+ $c = 1.0 + $d / $c;
+ if (abs($c) < self::XMININ) {
+ $c = self::XMININ;
+ }
+ $frac *= $h * $c;
+ // odd index for d
+ $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
+ $h = 1.0 + $d * $h;
+ if (abs($h) < self::XMININ) {
+ $h = self::XMININ;
+ }
+ $h = 1.0 / $h;
+ $c = 1.0 + $d / $c;
+ if (abs($c) < self::XMININ) {
+ $c = self::XMININ;
+ }
+ $delta = $h * $c;
+ $frac *= $delta;
+ ++$m;
+ }
+
+ return $frac;
+ }
+
+ /**
+ * logGamma function.
+ *
+ * @version 1.1
+ *
+ * @author Jaco van Kooten
+ *
+ * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
+ *
+ * The natural logarithm of the gamma function. <br />
+ * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
+ * Applied Mathematics Division <br />
+ * Argonne National Laboratory <br />
+ * Argonne, IL 60439 <br />
+ * <p>
+ * References:
+ * <ol>
+ * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
+ * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
+ * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
+ * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
+ * </ol>
+ * </p>
+ * <p>
+ * From the original documentation:
+ * </p>
+ * <p>
+ * This routine calculates the LOG(GAMMA) function for a positive real argument X.
+ * Computation is based on an algorithm outlined in references 1 and 2.
+ * The program uses rational functions that theoretically approximate LOG(GAMMA)
+ * to at least 18 significant decimal digits. The approximation for X > 12 is from
+ * reference 3, while approximations for X < 12.0 are similar to those in reference
+ * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
+ * the compiler, the intrinsic functions, and proper selection of the
+ * machine-dependent constants.
+ * </p>
+ * <p>
+ * Error returns: <br />
+ * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
+ * The computation is believed to be free of underflow and overflow.
+ * </p>
+ *
+ * @return float MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
+ */
+
+ // Function cache for logGamma
+ private static $logGammaCacheResult = 0.0;
+
+ private static $logGammaCacheX = 0.0;
+
+ private static function logGamma($x)
+ {
+ // Log Gamma related constants
+ static $lg_d1 = -0.5772156649015328605195174;
+ static $lg_d2 = 0.4227843350984671393993777;
+ static $lg_d4 = 1.791759469228055000094023;
+
+ static $lg_p1 = [
+ 4.945235359296727046734888,
+ 201.8112620856775083915565,
+ 2290.838373831346393026739,
+ 11319.67205903380828685045,
+ 28557.24635671635335736389,
+ 38484.96228443793359990269,
+ 26377.48787624195437963534,
+ 7225.813979700288197698961,
+ ];
+ static $lg_p2 = [
+ 4.974607845568932035012064,
+ 542.4138599891070494101986,
+ 15506.93864978364947665077,
+ 184793.2904445632425417223,
+ 1088204.76946882876749847,
+ 3338152.967987029735917223,
+ 5106661.678927352456275255,
+ 3074109.054850539556250927,
+ ];
+ static $lg_p4 = [
+ 14745.02166059939948905062,
+ 2426813.369486704502836312,
+ 121475557.4045093227939592,
+ 2663432449.630976949898078,
+ 29403789566.34553899906876,
+ 170266573776.5398868392998,
+ 492612579337.743088758812,
+ 560625185622.3951465078242,
+ ];
+ static $lg_q1 = [
+ 67.48212550303777196073036,
+ 1113.332393857199323513008,
+ 7738.757056935398733233834,
+ 27639.87074403340708898585,
+ 54993.10206226157329794414,
+ 61611.22180066002127833352,
+ 36351.27591501940507276287,
+ 8785.536302431013170870835,
+ ];
+ static $lg_q2 = [
+ 183.0328399370592604055942,
+ 7765.049321445005871323047,
+ 133190.3827966074194402448,
+ 1136705.821321969608938755,
+ 5267964.117437946917577538,
+ 13467014.54311101692290052,
+ 17827365.30353274213975932,
+ 9533095.591844353613395747,
+ ];
+ static $lg_q4 = [
+ 2690.530175870899333379843,
+ 639388.5654300092398984238,
+ 41355999.30241388052042842,
+ 1120872109.61614794137657,
+ 14886137286.78813811542398,
+ 101680358627.2438228077304,
+ 341747634550.7377132798597,
+ 446315818741.9713286462081,
+ ];
+ static $lg_c = [
+ -0.001910444077728,
+ 8.4171387781295e-4,
+ -5.952379913043012e-4,
+ 7.93650793500350248e-4,
+ -0.002777777777777681622553,
+ 0.08333333333333333331554247,
+ 0.0057083835261,
+ ];
+
+ // Rough estimate of the fourth root of logGamma_xBig
+ static $lg_frtbig = 2.25e76;
+ static $pnt68 = 0.6796875;
+
+ if ($x == self::$logGammaCacheX) {
+ return self::$logGammaCacheResult;
+ }
+ $y = $x;
+ if ($y > 0.0 && $y <= self::LOG_GAMMA_X_MAX_VALUE) {
+ if ($y <= self::EPS) {
+ $res = -log($y);
+ } elseif ($y <= 1.5) {
+ // ---------------------
+ // EPS .LT. X .LE. 1.5
+ // ---------------------
+ if ($y < $pnt68) {
+ $corr = -log($y);
+ $xm1 = $y;
+ } else {
+ $corr = 0.0;
+ $xm1 = $y - 1.0;
+ }
+ if ($y <= 0.5 || $y >= $pnt68) {
+ $xden = 1.0;
+ $xnum = 0.0;
+ for ($i = 0; $i < 8; ++$i) {
+ $xnum = $xnum * $xm1 + $lg_p1[$i];
+ $xden = $xden * $xm1 + $lg_q1[$i];
+ }
+ $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
+ } else {
+ $xm2 = $y - 1.0;
+ $xden = 1.0;
+ $xnum = 0.0;
+ for ($i = 0; $i < 8; ++$i) {
+ $xnum = $xnum * $xm2 + $lg_p2[$i];
+ $xden = $xden * $xm2 + $lg_q2[$i];
+ }
+ $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
+ }
+ } elseif ($y <= 4.0) {
+ // ---------------------
+ // 1.5 .LT. X .LE. 4.0
+ // ---------------------
+ $xm2 = $y - 2.0;
+ $xden = 1.0;
+ $xnum = 0.0;
+ for ($i = 0; $i < 8; ++$i) {
+ $xnum = $xnum * $xm2 + $lg_p2[$i];
+ $xden = $xden * $xm2 + $lg_q2[$i];
+ }
+ $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
+ } elseif ($y <= 12.0) {
+ // ----------------------
+ // 4.0 .LT. X .LE. 12.0
+ // ----------------------
+ $xm4 = $y - 4.0;
+ $xden = -1.0;
+ $xnum = 0.0;
+ for ($i = 0; $i < 8; ++$i) {
+ $xnum = $xnum * $xm4 + $lg_p4[$i];
+ $xden = $xden * $xm4 + $lg_q4[$i];
+ }
+ $res = $lg_d4 + $xm4 * ($xnum / $xden);
+ } else {
+ // ---------------------------------
+ // Evaluate for argument .GE. 12.0
+ // ---------------------------------
+ $res = 0.0;
+ if ($y <= $lg_frtbig) {
+ $res = $lg_c[6];
+ $ysq = $y * $y;
+ for ($i = 0; $i < 6; ++$i) {
+ $res = $res / $ysq + $lg_c[$i];
+ }
+ $res /= $y;
+ $corr = log($y);
+ $res = $res + log(self::SQRT2PI) - 0.5 * $corr;
+ $res += $y * ($corr - 1.0);
+ }
+ }
+ } else {
+ // --------------------------
+ // Return for bad arguments
+ // --------------------------
+ $res = self::MAX_VALUE;
+ }
+ // ------------------------------
+ // Final adjustments and return
+ // ------------------------------
+ self::$logGammaCacheX = $x;
+ self::$logGammaCacheResult = $res;
+
+ return $res;
+ }
+
+ //
+ // Private implementation of the incomplete Gamma function
+ //
+ private static function incompleteGamma($a, $x)
+ {
+ static $max = 32;
+ $summer = 0;
+ for ($n = 0; $n <= $max; ++$n) {
+ $divisor = $a;
+ for ($i = 1; $i <= $n; ++$i) {
+ $divisor *= ($a + $i);
+ }
+ $summer += ($x ** $n / $divisor);
+ }
+
+ return $x ** $a * exp(0 - $x) * $summer;
+ }
+
+ //
+ // Private implementation of the Gamma function
+ //
+ private static function gamma($data)
+ {
+ if ($data == 0.0) {
+ return 0;
+ }
+
+ static $p0 = 1.000000000190015;
+ static $p = [
+ 1 => 76.18009172947146,
+ 2 => -86.50532032941677,
+ 3 => 24.01409824083091,
+ 4 => -1.231739572450155,
+ 5 => 1.208650973866179e-3,
+ 6 => -5.395239384953e-6,
+ ];
+
+ $y = $x = $data;
+ $tmp = $x + 5.5;
+ $tmp -= ($x + 0.5) * log($tmp);
+
+ $summer = $p0;
+ for ($j = 1; $j <= 6; ++$j) {
+ $summer += ($p[$j] / ++$y);
+ }
+
+ return exp(0 - $tmp + log(self::SQRT2PI * $summer / $x));
+ }
+
+ /*
+ * inverse_ncdf.php
+ * -------------------
+ * begin : Friday, January 16, 2004
+ * copyright : (C) 2004 Michael Nickerson
+ * email : nickersonm@yahoo.com
+ *
+ */
+ private static function inverseNcdf($p)
+ {
+ // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
+ // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
+ // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
+ // I have not checked the accuracy of this implementation. Be aware that PHP
+ // will truncate the coeficcients to 14 digits.
+
+ // You have permission to use and distribute this function freely for
+ // whatever purpose you want, but please show common courtesy and give credit
+ // where credit is due.
+
+ // Input paramater is $p - probability - where 0 < p < 1.
+
+ // Coefficients in rational approximations
+ static $a = [
+ 1 => -3.969683028665376e+01,
+ 2 => 2.209460984245205e+02,
+ 3 => -2.759285104469687e+02,
+ 4 => 1.383577518672690e+02,
+ 5 => -3.066479806614716e+01,
+ 6 => 2.506628277459239e+00,
+ ];
+
+ static $b = [
+ 1 => -5.447609879822406e+01,
+ 2 => 1.615858368580409e+02,
+ 3 => -1.556989798598866e+02,
+ 4 => 6.680131188771972e+01,
+ 5 => -1.328068155288572e+01,
+ ];
+
+ static $c = [
+ 1 => -7.784894002430293e-03,
+ 2 => -3.223964580411365e-01,
+ 3 => -2.400758277161838e+00,
+ 4 => -2.549732539343734e+00,
+ 5 => 4.374664141464968e+00,
+ 6 => 2.938163982698783e+00,
+ ];
+
+ static $d = [
+ 1 => 7.784695709041462e-03,
+ 2 => 3.224671290700398e-01,
+ 3 => 2.445134137142996e+00,
+ 4 => 3.754408661907416e+00,
+ ];
+
+ // Define lower and upper region break-points.
+ $p_low = 0.02425; //Use lower region approx. below this
+ $p_high = 1 - $p_low; //Use upper region approx. above this
+
+ if (0 < $p && $p < $p_low) {
+ // Rational approximation for lower region.
+ $q = sqrt(-2 * log($p));
+
+ return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
+ (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
+ } elseif ($p_low <= $p && $p <= $p_high) {
+ // Rational approximation for central region.
+ $q = $p - 0.5;
+ $r = $q * $q;
+
+ return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
+ ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
+ } elseif ($p_high < $p && $p < 1) {
+ // Rational approximation for upper region.
+ $q = sqrt(-2 * log(1 - $p));
+
+ return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
+ (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
+ }
+ // If 0 < p < 1, return a null value
+ return Functions::NULL();
+ }
+
+ /**
+ * MS Excel does not count Booleans if passed as cell values, but they are counted if passed as literals.
+ * OpenOffice Calc always counts Booleans.
+ * Gnumeric never counts Booleans.
+ *
+ * @param mixed $arg
+ * @param mixed $k
+ *
+ * @return int|mixed
+ */
+ private static function testAcceptedBoolean($arg, $k)
+ {
+ if (
+ (is_bool($arg)) &&
+ ((!Functions::isCellValue($k) && (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_EXCEL)) ||
+ (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE))
+ ) {
+ $arg = (int) $arg;
+ }
+
+ return $arg;
+ }
+
+ /**
+ * @param mixed $arg
+ * @param mixed $k
+ *
+ * @return bool
+ */
+ private static function isAcceptedCountable($arg, $k)
+ {
+ if (
+ ((is_numeric($arg)) && (!is_string($arg))) ||
+ ((is_numeric($arg)) && (!Functions::isCellValue($k)) &&
+ (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_GNUMERIC))
+ ) {
+ return true;
+ }
+
+ return false;
+ }
+
+ /**
+ * AVEDEV.
+ *
+ * Returns the average of the absolute deviations of data points from their mean.
+ * AVEDEV is a measure of the variability in a data set.
+ *
+ * Excel Function:
+ * AVEDEV(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function AVEDEV(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ // Return value
+ $returnValue = 0;
+
+ $aMean = self::AVERAGE(...$args);
+ if ($aMean === Functions::DIV0()) {
+ return Functions::NAN();
+ } elseif ($aMean === Functions::VALUE()) {
+ return Functions::VALUE();
+ }
+
+ $aCount = 0;
+ foreach ($aArgs as $k => $arg) {
+ $arg = self::testAcceptedBoolean($arg, $k);
+ // Is it a numeric value?
+ // Strings containing numeric values are only counted if they are string literals (not cell values)
+ // and then only in MS Excel and in Open Office, not in Gnumeric
+ if ((is_string($arg)) && (!is_numeric($arg)) && (!Functions::isCellValue($k))) {
+ return Functions::VALUE();
+ }
+ if (self::isAcceptedCountable($arg, $k)) {
+ $returnValue += abs($arg - $aMean);
+ ++$aCount;
+ }
+ }
+
+ // Return
+ if ($aCount === 0) {
+ return Functions::DIV0();
+ }
+
+ return $returnValue / $aCount;
+ }
+
+ /**
+ * AVERAGE.
+ *
+ * Returns the average (arithmetic mean) of the arguments
+ *
+ * Excel Function:
+ * AVERAGE(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function AVERAGE(...$args)
+ {
+ $returnValue = $aCount = 0;
+
+ // Loop through arguments
+ foreach (Functions::flattenArrayIndexed($args) as $k => $arg) {
+ $arg = self::testAcceptedBoolean($arg, $k);
+ // Is it a numeric value?
+ // Strings containing numeric values are only counted if they are string literals (not cell values)
+ // and then only in MS Excel and in Open Office, not in Gnumeric
+ if ((is_string($arg)) && (!is_numeric($arg)) && (!Functions::isCellValue($k))) {
+ return Functions::VALUE();
+ }
+ if (self::isAcceptedCountable($arg, $k)) {
+ $returnValue += $arg;
+ ++$aCount;
+ }
+ }
+
+ // Return
+ if ($aCount > 0) {
+ return $returnValue / $aCount;
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * AVERAGEA.
+ *
+ * Returns the average of its arguments, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * AVERAGEA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function AVERAGEA(...$args)
+ {
+ $returnValue = null;
+
+ $aCount = 0;
+ // Loop through arguments
+ foreach (Functions::flattenArrayIndexed($args) as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ $returnValue += $arg;
+ ++$aCount;
+ }
+ }
+ }
+
+ if ($aCount > 0) {
+ return $returnValue / $aCount;
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * AVERAGEIF.
+ *
+ * Returns the average value from a range of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * AVERAGEIF(value1[,value2[, ...]],condition)
+ *
+ * @param mixed $aArgs Data values
+ * @param string $condition the criteria that defines which cells will be checked
+ * @param mixed[] $averageArgs Data values
+ *
+ * @return float|string
+ */
+ public static function AVERAGEIF($aArgs, $condition, $averageArgs = [])
+ {
+ $returnValue = 0;
+
+ $aArgs = Functions::flattenArray($aArgs);
+ $averageArgs = Functions::flattenArray($averageArgs);
+ if (empty($averageArgs)) {
+ $averageArgs = $aArgs;
+ }
+ $condition = Functions::ifCondition($condition);
+ $conditionIsNumeric = strpos($condition, '"') === false;
+
+ // Loop through arguments
+ $aCount = 0;
+ foreach ($aArgs as $key => $arg) {
+ if (!is_numeric($arg)) {
+ if ($conditionIsNumeric) {
+ continue;
+ }
+ $arg = Calculation::wrapResult(strtoupper($arg));
+ } elseif (!$conditionIsNumeric) {
+ continue;
+ }
+ $testCondition = '=' . $arg . $condition;
+ if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
+ $returnValue += $averageArgs[$key];
+ ++$aCount;
+ }
+ }
+
+ if ($aCount > 0) {
+ return $returnValue / $aCount;
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * BETADIST.
+ *
+ * Returns the beta distribution.
+ *
+ * @param float $value Value at which you want to evaluate the distribution
+ * @param float $alpha Parameter to the distribution
+ * @param float $beta Parameter to the distribution
+ * @param mixed $rMin
+ * @param mixed $rMax
+ *
+ * @return float|string
+ */
+ public static function BETADIST($value, $alpha, $beta, $rMin = 0, $rMax = 1)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $alpha = Functions::flattenSingleValue($alpha);
+ $beta = Functions::flattenSingleValue($beta);
+ $rMin = Functions::flattenSingleValue($rMin);
+ $rMax = Functions::flattenSingleValue($rMax);
+
+ if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
+ if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
+ return Functions::NAN();
+ }
+ if ($rMin > $rMax) {
+ $tmp = $rMin;
+ $rMin = $rMax;
+ $rMax = $tmp;
+ }
+ $value -= $rMin;
+ $value /= ($rMax - $rMin);
+
+ return self::incompleteBeta($value, $alpha, $beta);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * BETAINV.
+ *
+ * Returns the inverse of the Beta distribution.
+ *
+ * @param float $probability Probability at which you want to evaluate the distribution
+ * @param float $alpha Parameter to the distribution
+ * @param float $beta Parameter to the distribution
+ * @param float $rMin Minimum value
+ * @param float $rMax Maximum value
+ *
+ * @return float|string
+ */
+ public static function BETAINV($probability, $alpha, $beta, $rMin = 0, $rMax = 1)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $alpha = Functions::flattenSingleValue($alpha);
+ $beta = Functions::flattenSingleValue($beta);
+ $rMin = Functions::flattenSingleValue($rMin);
+ $rMax = Functions::flattenSingleValue($rMax);
+
+ if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
+ if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
+ return Functions::NAN();
+ }
+ if ($rMin > $rMax) {
+ $tmp = $rMin;
+ $rMin = $rMax;
+ $rMax = $tmp;
+ }
+ $a = 0;
+ $b = 2;
+
+ $i = 0;
+ while ((($b - $a) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) {
+ $guess = ($a + $b) / 2;
+ $result = self::BETADIST($guess, $alpha, $beta);
+ if (($result == $probability) || ($result == 0)) {
+ $b = $a;
+ } elseif ($result > $probability) {
+ $b = $guess;
+ } else {
+ $a = $guess;
+ }
+ }
+ if ($i == self::MAX_ITERATIONS) {
+ return Functions::NA();
+ }
+
+ return round($rMin + $guess * ($rMax - $rMin), 12);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * BINOMDIST.
+ *
+ * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
+ * a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
+ * when trials are independent, and when the probability of success is constant throughout the
+ * experiment. For example, BINOMDIST can calculate the probability that two of the next three
+ * babies born are male.
+ *
+ * @param float $value Number of successes in trials
+ * @param float $trials Number of trials
+ * @param float $probability Probability of success on each trial
+ * @param bool $cumulative
+ *
+ * @return float|string
+ */
+ public static function BINOMDIST($value, $trials, $probability, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $trials = Functions::flattenSingleValue($trials);
+ $probability = Functions::flattenSingleValue($probability);
+
+ if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
+ $value = floor($value);
+ $trials = floor($trials);
+ if (($value < 0) || ($value > $trials)) {
+ return Functions::NAN();
+ }
+ if (($probability < 0) || ($probability > 1)) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ $summer = 0;
+ for ($i = 0; $i <= $value; ++$i) {
+ $summer += MathTrig::COMBIN($trials, $i) * $probability ** $i * (1 - $probability) ** ($trials - $i);
+ }
+
+ return $summer;
+ }
+
+ return MathTrig::COMBIN($trials, $value) * $probability ** $value * (1 - $probability) ** ($trials - $value);
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * CHIDIST.
+ *
+ * Returns the one-tailed probability of the chi-squared distribution.
+ *
+ * @param float $value Value for the function
+ * @param float $degrees degrees of freedom
+ *
+ * @return float|string
+ */
+ public static function CHIDIST($value, $degrees)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $degrees = Functions::flattenSingleValue($degrees);
+
+ if ((is_numeric($value)) && (is_numeric($degrees))) {
+ $degrees = floor($degrees);
+ if ($degrees < 1) {
+ return Functions::NAN();
+ }
+ if ($value < 0) {
+ if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) {
+ return 1;
+ }
+
+ return Functions::NAN();
+ }
+
+ return 1 - (self::incompleteGamma($degrees / 2, $value / 2) / self::gamma($degrees / 2));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * CHIINV.
+ *
+ * Returns the one-tailed probability of the chi-squared distribution.
+ *
+ * @param float $probability Probability for the function
+ * @param float $degrees degrees of freedom
+ *
+ * @return float|string
+ */
+ public static function CHIINV($probability, $degrees)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $degrees = Functions::flattenSingleValue($degrees);
+
+ if ((is_numeric($probability)) && (is_numeric($degrees))) {
+ $degrees = floor($degrees);
+
+ $xLo = 100;
+ $xHi = 0;
+
+ $x = $xNew = 1;
+ $dx = 1;
+ $i = 0;
+
+ while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) {
+ // Apply Newton-Raphson step
+ $result = 1 - (self::incompleteGamma($degrees / 2, $x / 2) / self::gamma($degrees / 2));
+ $error = $result - $probability;
+ if ($error == 0.0) {
+ $dx = 0;
+ } elseif ($error < 0.0) {
+ $xLo = $x;
+ } else {
+ $xHi = $x;
+ }
+ // Avoid division by zero
+ if ($result != 0.0) {
+ $dx = $error / $result;
+ $xNew = $x - $dx;
+ }
+ // If the NR fails to converge (which for example may be the
+ // case if the initial guess is too rough) we apply a bisection
+ // step to determine a more narrow interval around the root.
+ if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
+ $xNew = ($xLo + $xHi) / 2;
+ $dx = $xNew - $x;
+ }
+ $x = $xNew;
+ }
+ if ($i == self::MAX_ITERATIONS) {
+ return Functions::NA();
+ }
+
+ return round($x, 12);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * CONFIDENCE.
+ *
+ * Returns the confidence interval for a population mean
+ *
+ * @param float $alpha
+ * @param float $stdDev Standard Deviation
+ * @param float $size
+ *
+ * @return float|string
+ */
+ public static function CONFIDENCE($alpha, $stdDev, $size)
+ {
+ $alpha = Functions::flattenSingleValue($alpha);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+ $size = Functions::flattenSingleValue($size);
+
+ if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
+ $size = floor($size);
+ if (($alpha <= 0) || ($alpha >= 1)) {
+ return Functions::NAN();
+ }
+ if (($stdDev <= 0) || ($size < 1)) {
+ return Functions::NAN();
+ }
+
+ return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * CORREL.
+ *
+ * Returns covariance, the average of the products of deviations for each data point pair.
+ *
+ * @param mixed $yValues array of mixed Data Series Y
+ * @param null|mixed $xValues array of mixed Data Series X
+ *
+ * @return float|string
+ */
+ public static function CORREL($yValues, $xValues = null)
+ {
+ if (($xValues === null) || (!is_array($yValues)) || (!is_array($xValues))) {
+ return Functions::VALUE();
+ }
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getCorrelation();
+ }
+
+ /**
+ * COUNT.
+ *
+ * Counts the number of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * COUNT(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return int
+ */
+ public static function COUNT(...$args)
+ {
+ $returnValue = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArrayIndexed($args);
+ foreach ($aArgs as $k => $arg) {
+ $arg = self::testAcceptedBoolean($arg, $k);
+ // Is it a numeric value?
+ // Strings containing numeric values are only counted if they are string literals (not cell values)
+ // and then only in MS Excel and in Open Office, not in Gnumeric
+ if (self::isAcceptedCountable($arg, $k)) {
+ ++$returnValue;
+ }
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * COUNTA.
+ *
+ * Counts the number of cells that are not empty within the list of arguments
+ *
+ * Excel Function:
+ * COUNTA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return int
+ */
+ public static function COUNTA(...$args)
+ {
+ $returnValue = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArrayIndexed($args);
+ foreach ($aArgs as $k => $arg) {
+ // Nulls are counted if literals, but not if cell values
+ if ($arg !== null || (!Functions::isCellValue($k))) {
+ ++$returnValue;
+ }
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * COUNTBLANK.
+ *
+ * Counts the number of empty cells within the list of arguments
+ *
+ * Excel Function:
+ * COUNTBLANK(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return int
+ */
+ public static function COUNTBLANK(...$args)
+ {
+ $returnValue = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a blank cell?
+ if (($arg === null) || ((is_string($arg)) && ($arg == ''))) {
+ ++$returnValue;
+ }
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * COUNTIF.
+ *
+ * Counts the number of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * COUNTIF(value1[,value2[, ...]],condition)
+ *
+ * @param mixed $aArgs Data values
+ * @param string $condition the criteria that defines which cells will be counted
+ *
+ * @return int
+ */
+ public static function COUNTIF($aArgs, $condition)
+ {
+ $returnValue = 0;
+
+ $aArgs = Functions::flattenArray($aArgs);
+ $condition = Functions::ifCondition($condition);
+ $conditionIsNumeric = strpos($condition, '"') === false;
+ // Loop through arguments
+ foreach ($aArgs as $arg) {
+ if (!is_numeric($arg)) {
+ if ($conditionIsNumeric) {
+ continue;
+ }
+ $arg = Calculation::wrapResult(strtoupper($arg));
+ } elseif (!$conditionIsNumeric) {
+ continue;
+ }
+ $testCondition = '=' . $arg . $condition;
+ if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
+ // Is it a value within our criteria
+ ++$returnValue;
+ }
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * COUNTIFS.
+ *
+ * Counts the number of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
+ *
+ * @param mixed $args Criterias
+ *
+ * @return int
+ */
+ public static function COUNTIFS(...$args)
+ {
+ $arrayList = $args;
+
+ // Return value
+ $returnValue = 0;
+
+ if (empty($arrayList)) {
+ return $returnValue;
+ }
+
+ $aArgsArray = [];
+ $conditions = [];
+
+ while (count($arrayList) > 0) {
+ $aArgsArray[] = Functions::flattenArray(array_shift($arrayList));
+ $conditions[] = Functions::ifCondition(array_shift($arrayList));
+ }
+
+ // Loop through each arg and see if arguments and conditions are true
+ foreach (array_keys($aArgsArray[0]) as $index) {
+ $valid = true;
+
+ foreach ($conditions as $cidx => $condition) {
+ $conditionIsNumeric = strpos($condition, '"') === false;
+ $arg = $aArgsArray[$cidx][$index];
+
+ // Loop through arguments
+ if (!is_numeric($arg)) {
+ if ($conditionIsNumeric) {
+ $valid = false;
+
+ break; // if false found, don't need to check other conditions
+ }
+ $arg = Calculation::wrapResult(strtoupper($arg));
+ } elseif (!$conditionIsNumeric) {
+ $valid = false;
+
+ break; // if false found, don't need to check other conditions
+ }
+ $testCondition = '=' . $arg . $condition;
+ if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
+ // Is not a value within our criteria
+ $valid = false;
+
+ break; // if false found, don't need to check other conditions
+ }
+ }
+
+ if ($valid) {
+ ++$returnValue;
+ }
+ }
+
+ // Return
+ return $returnValue;
+ }
+
+ /**
+ * COVAR.
+ *
+ * Returns covariance, the average of the products of deviations for each data point pair.
+ *
+ * @param mixed $yValues array of mixed Data Series Y
+ * @param mixed $xValues array of mixed Data Series X
+ *
+ * @return float|string
+ */
+ public static function COVAR($yValues, $xValues)
+ {
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getCovariance();
+ }
+
+ /**
+ * CRITBINOM.
+ *
+ * Returns the smallest value for which the cumulative binomial distribution is greater
+ * than or equal to a criterion value
+ *
+ * See https://support.microsoft.com/en-us/help/828117/ for details of the algorithm used
+ *
+ * @param float $trials number of Bernoulli trials
+ * @param float $probability probability of a success on each trial
+ * @param float $alpha criterion value
+ *
+ * @return int|string
+ *
+ * @TODO Warning. This implementation differs from the algorithm detailed on the MS
+ * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
+ * This eliminates a potential endless loop error, but may have an adverse affect on the
+ * accuracy of the function (although all my tests have so far returned correct results).
+ */
+ public static function CRITBINOM($trials, $probability, $alpha)
+ {
+ $trials = floor(Functions::flattenSingleValue($trials));
+ $probability = Functions::flattenSingleValue($probability);
+ $alpha = Functions::flattenSingleValue($alpha);
+
+ if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
+ $trials = (int) $trials;
+ if ($trials < 0) {
+ return Functions::NAN();
+ } elseif (($probability < 0.0) || ($probability > 1.0)) {
+ return Functions::NAN();
+ } elseif (($alpha < 0.0) || ($alpha > 1.0)) {
+ return Functions::NAN();
+ }
+
+ if ($alpha <= 0.5) {
+ $t = sqrt(log(1 / ($alpha * $alpha)));
+ $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));
+ } else {
+ $t = sqrt(log(1 / (1 - $alpha) ** 2));
+ $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
+ }
+
+ $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
+ if ($Guess < 0) {
+ $Guess = 0;
+ } elseif ($Guess > $trials) {
+ $Guess = $trials;
+ }
+
+ $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
+ $EssentiallyZero = 10e-12;
+
+ $m = floor($trials * $probability);
+ ++$TotalUnscaledProbability;
+ if ($m == $Guess) {
+ ++$UnscaledPGuess;
+ }
+ if ($m <= $Guess) {
+ ++$UnscaledCumPGuess;
+ }
+
+ $PreviousValue = 1;
+ $Done = false;
+ $k = $m + 1;
+ while ((!$Done) && ($k <= $trials)) {
+ $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
+ $TotalUnscaledProbability += $CurrentValue;
+ if ($k == $Guess) {
+ $UnscaledPGuess += $CurrentValue;
+ }
+ if ($k <= $Guess) {
+ $UnscaledCumPGuess += $CurrentValue;
+ }
+ if ($CurrentValue <= $EssentiallyZero) {
+ $Done = true;
+ }
+ $PreviousValue = $CurrentValue;
+ ++$k;
+ }
+
+ $PreviousValue = 1;
+ $Done = false;
+ $k = $m - 1;
+ while ((!$Done) && ($k >= 0)) {
+ $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
+ $TotalUnscaledProbability += $CurrentValue;
+ if ($k == $Guess) {
+ $UnscaledPGuess += $CurrentValue;
+ }
+ if ($k <= $Guess) {
+ $UnscaledCumPGuess += $CurrentValue;
+ }
+ if ($CurrentValue <= $EssentiallyZero) {
+ $Done = true;
+ }
+ $PreviousValue = $CurrentValue;
+ --$k;
+ }
+
+ $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
+ $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
+
+ $CumPGuessMinus1 = $CumPGuess - 1;
+
+ while (true) {
+ if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
+ return $Guess;
+ } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
+ $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
+ $CumPGuessMinus1 = $CumPGuess;
+ $CumPGuess = $CumPGuess + $PGuessPlus1;
+ $PGuess = $PGuessPlus1;
+ ++$Guess;
+ } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
+ $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
+ $CumPGuess = $CumPGuessMinus1;
+ $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
+ $PGuess = $PGuessMinus1;
+ --$Guess;
+ }
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * DEVSQ.
+ *
+ * Returns the sum of squares of deviations of data points from their sample mean.
+ *
+ * Excel Function:
+ * DEVSQ(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function DEVSQ(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ // Return value
+ $returnValue = null;
+
+ $aMean = self::AVERAGE($aArgs);
+ if ($aMean != Functions::DIV0()) {
+ $aCount = -1;
+ foreach ($aArgs as $k => $arg) {
+ // Is it a numeric value?
+ if (
+ (is_bool($arg)) &&
+ ((!Functions::isCellValue($k)) ||
+ (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))
+ ) {
+ $arg = (int) $arg;
+ }
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if ($returnValue === null) {
+ $returnValue = ($arg - $aMean) ** 2;
+ } else {
+ $returnValue += ($arg - $aMean) ** 2;
+ }
+ ++$aCount;
+ }
+ }
+
+ // Return
+ if ($returnValue === null) {
+ return Functions::NAN();
+ }
+
+ return $returnValue;
+ }
+
+ return Functions::NA();
+ }
+
+ /**
+ * EXPONDIST.
+ *
+ * Returns the exponential distribution. Use EXPONDIST to model the time between events,
+ * such as how long an automated bank teller takes to deliver cash. For example, you can
+ * use EXPONDIST to determine the probability that the process takes at most 1 minute.
+ *
+ * @param float $value Value of the function
+ * @param float $lambda The parameter value
+ * @param bool $cumulative
+ *
+ * @return float|string
+ */
+ public static function EXPONDIST($value, $lambda, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $lambda = Functions::flattenSingleValue($lambda);
+ $cumulative = Functions::flattenSingleValue($cumulative);
+
+ if ((is_numeric($value)) && (is_numeric($lambda))) {
+ if (($value < 0) || ($lambda < 0)) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ return 1 - exp(0 - $value * $lambda);
+ }
+
+ return $lambda * exp(0 - $value * $lambda);
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ private static function betaFunction($a, $b)
+ {
+ return (self::gamma($a) * self::gamma($b)) / self::gamma($a + $b);
+ }
+
+ private static function regularizedIncompleteBeta($value, $a, $b)
+ {
+ return self::incompleteBeta($value, $a, $b) / self::betaFunction($a, $b);
+ }
+
+ /**
+ * F.DIST.
+ *
+ * Returns the F probability distribution.
+ * You can use this function to determine whether two data sets have different degrees of diversity.
+ * For example, you can examine the test scores of men and women entering high school, and determine
+ * if the variability in the females is different from that found in the males.
+ *
+ * @param float $value Value of the function
+ * @param int $u The numerator degrees of freedom
+ * @param int $v The denominator degrees of freedom
+ * @param bool $cumulative If cumulative is TRUE, F.DIST returns the cumulative distribution function;
+ * if FALSE, it returns the probability density function.
+ *
+ * @return float|string
+ */
+ public static function FDIST2($value, $u, $v, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $u = Functions::flattenSingleValue($u);
+ $v = Functions::flattenSingleValue($v);
+ $cumulative = Functions::flattenSingleValue($cumulative);
+
+ if (is_numeric($value) && is_numeric($u) && is_numeric($v)) {
+ if ($value < 0 || $u < 1 || $v < 1) {
+ return Functions::NAN();
+ }
+
+ $cumulative = (bool) $cumulative;
+ $u = (int) $u;
+ $v = (int) $v;
+
+ if ($cumulative) {
+ $adjustedValue = ($u * $value) / ($u * $value + $v);
+
+ return self::incompleteBeta($adjustedValue, $u / 2, $v / 2);
+ }
+
+ return (self::gamma(($v + $u) / 2) / (self::gamma($u / 2) * self::gamma($v / 2))) *
+ (($u / $v) ** ($u / 2)) *
+ (($value ** (($u - 2) / 2)) / ((1 + ($u / $v) * $value) ** (($u + $v) / 2)));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * FISHER.
+ *
+ * Returns the Fisher transformation at x. This transformation produces a function that
+ * is normally distributed rather than skewed. Use this function to perform hypothesis
+ * testing on the correlation coefficient.
+ *
+ * @param float $value
+ *
+ * @return float|string
+ */
+ public static function FISHER($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+
+ if (is_numeric($value)) {
+ if (($value <= -1) || ($value >= 1)) {
+ return Functions::NAN();
+ }
+
+ return 0.5 * log((1 + $value) / (1 - $value));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * FISHERINV.
+ *
+ * Returns the inverse of the Fisher transformation. Use this transformation when
+ * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
+ * FISHERINV(y) = x.
+ *
+ * @param float $value
+ *
+ * @return float|string
+ */
+ public static function FISHERINV($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+
+ if (is_numeric($value)) {
+ return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * FORECAST.
+ *
+ * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.
+ *
+ * @param float $xValue Value of X for which we want to find Y
+ * @param mixed $yValues array of mixed Data Series Y
+ * @param mixed $xValues of mixed Data Series X
+ *
+ * @return bool|float|string
+ */
+ public static function FORECAST($xValue, $yValues, $xValues)
+ {
+ $xValue = Functions::flattenSingleValue($xValue);
+ if (!is_numeric($xValue)) {
+ return Functions::VALUE();
+ } elseif (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getValueOfYForX($xValue);
+ }
+
+ /**
+ * GAMMA.
+ *
+ * Return the gamma function value.
+ *
+ * @param float $value
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function GAMMAFunction($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+ if (!is_numeric($value)) {
+ return Functions::VALUE();
+ } elseif ((((int) $value) == ((float) $value)) && $value <= 0.0) {
+ return Functions::NAN();
+ }
+
+ return self::gamma($value);
+ }
+
+ /**
+ * GAMMADIST.
+ *
+ * Returns the gamma distribution.
+ *
+ * @param float $value Value at which you want to evaluate the distribution
+ * @param float $a Parameter to the distribution
+ * @param float $b Parameter to the distribution
+ * @param bool $cumulative
+ *
+ * @return float|string
+ */
+ public static function GAMMADIST($value, $a, $b, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $a = Functions::flattenSingleValue($a);
+ $b = Functions::flattenSingleValue($b);
+
+ if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
+ if (($value < 0) || ($a <= 0) || ($b <= 0)) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ return self::incompleteGamma($a, $value / $b) / self::gamma($a);
+ }
+
+ return (1 / ($b ** $a * self::gamma($a))) * $value ** ($a - 1) * exp(0 - ($value / $b));
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * GAMMAINV.
+ *
+ * Returns the inverse of the Gamma distribution.
+ *
+ * @param float $probability Probability at which you want to evaluate the distribution
+ * @param float $alpha Parameter to the distribution
+ * @param float $beta Parameter to the distribution
+ *
+ * @return float|string
+ */
+ public static function GAMMAINV($probability, $alpha, $beta)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $alpha = Functions::flattenSingleValue($alpha);
+ $beta = Functions::flattenSingleValue($beta);
+
+ if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
+ if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
+ return Functions::NAN();
+ }
+
+ $xLo = 0;
+ $xHi = $alpha * $beta * 5;
+
+ $x = $xNew = 1;
+ $dx = 1024;
+ $i = 0;
+
+ while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) {
+ // Apply Newton-Raphson step
+ $error = self::GAMMADIST($x, $alpha, $beta, true) - $probability;
+ if ($error < 0.0) {
+ $xLo = $x;
+ } else {
+ $xHi = $x;
+ }
+ $pdf = self::GAMMADIST($x, $alpha, $beta, false);
+ // Avoid division by zero
+ if ($pdf != 0.0) {
+ $dx = $error / $pdf;
+ $xNew = $x - $dx;
+ }
+ // If the NR fails to converge (which for example may be the
+ // case if the initial guess is too rough) we apply a bisection
+ // step to determine a more narrow interval around the root.
+ if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) {
+ $xNew = ($xLo + $xHi) / 2;
+ $dx = $xNew - $x;
+ }
+ $x = $xNew;
+ }
+ if ($i == self::MAX_ITERATIONS) {
+ return Functions::NA();
+ }
+
+ return $x;
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * GAMMALN.
+ *
+ * Returns the natural logarithm of the gamma function.
+ *
+ * @param float $value
+ *
+ * @return float|string
+ */
+ public static function GAMMALN($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+
+ if (is_numeric($value)) {
+ if ($value <= 0) {
+ return Functions::NAN();
+ }
+
+ return log(self::gamma($value));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * GAUSS.
+ *
+ * Calculates the probability that a member of a standard normal population will fall between
+ * the mean and z standard deviations from the mean.
+ *
+ * @param float $value
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function GAUSS($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+ if (!is_numeric($value)) {
+ return Functions::VALUE();
+ }
+
+ return self::NORMDIST($value, 0, 1, true) - 0.5;
+ }
+
+ /**
+ * GEOMEAN.
+ *
+ * Returns the geometric mean of an array or range of positive data. For example, you
+ * can use GEOMEAN to calculate average growth rate given compound interest with
+ * variable rates.
+ *
+ * Excel Function:
+ * GEOMEAN(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function GEOMEAN(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+
+ $aMean = MathTrig::PRODUCT($aArgs);
+ if (is_numeric($aMean) && ($aMean > 0)) {
+ $aCount = self::COUNT($aArgs);
+ if (self::MIN($aArgs) > 0) {
+ return $aMean ** (1 / $aCount);
+ }
+ }
+
+ return Functions::NAN();
+ }
+
+ /**
+ * GROWTH.
+ *
+ * Returns values along a predicted exponential Trend
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ * @param mixed[] $newValues Values of X for which we want to find Y
+ * @param bool $const a logical value specifying whether to force the intersect to equal 0
+ *
+ * @return array of float
+ */
+ public static function GROWTH($yValues, $xValues = [], $newValues = [], $const = true)
+ {
+ $yValues = Functions::flattenArray($yValues);
+ $xValues = Functions::flattenArray($xValues);
+ $newValues = Functions::flattenArray($newValues);
+ $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
+
+ $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
+ if (empty($newValues)) {
+ $newValues = $bestFitExponential->getXValues();
+ }
+
+ $returnArray = [];
+ foreach ($newValues as $xValue) {
+ $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue);
+ }
+
+ return $returnArray;
+ }
+
+ /**
+ * HARMEAN.
+ *
+ * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the
+ * arithmetic mean of reciprocals.
+ *
+ * Excel Function:
+ * HARMEAN(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function HARMEAN(...$args)
+ {
+ // Return value
+ $returnValue = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ if (self::MIN($aArgs) < 0) {
+ return Functions::NAN();
+ }
+ $aCount = 0;
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if ($arg <= 0) {
+ return Functions::NAN();
+ }
+ $returnValue += (1 / $arg);
+ ++$aCount;
+ }
+ }
+
+ // Return
+ if ($aCount > 0) {
+ return 1 / ($returnValue / $aCount);
+ }
+
+ return Functions::NA();
+ }
+
+ /**
+ * HYPGEOMDIST.
+ *
+ * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of
+ * sample successes, given the sample size, population successes, and population size.
+ *
+ * @param float $sampleSuccesses Number of successes in the sample
+ * @param float $sampleNumber Size of the sample
+ * @param float $populationSuccesses Number of successes in the population
+ * @param float $populationNumber Population size
+ *
+ * @return float|string
+ */
+ public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber)
+ {
+ $sampleSuccesses = Functions::flattenSingleValue($sampleSuccesses);
+ $sampleNumber = Functions::flattenSingleValue($sampleNumber);
+ $populationSuccesses = Functions::flattenSingleValue($populationSuccesses);
+ $populationNumber = Functions::flattenSingleValue($populationNumber);
+
+ if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) {
+ $sampleSuccesses = floor($sampleSuccesses);
+ $sampleNumber = floor($sampleNumber);
+ $populationSuccesses = floor($populationSuccesses);
+ $populationNumber = floor($populationNumber);
+
+ if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
+ return Functions::NAN();
+ }
+ if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
+ return Functions::NAN();
+ }
+ if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
+ return Functions::NAN();
+ }
+
+ return MathTrig::COMBIN($populationSuccesses, $sampleSuccesses) *
+ MathTrig::COMBIN($populationNumber - $populationSuccesses, $sampleNumber - $sampleSuccesses) /
+ MathTrig::COMBIN($populationNumber, $sampleNumber);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * INTERCEPT.
+ *
+ * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ *
+ * @return float|string
+ */
+ public static function INTERCEPT($yValues, $xValues)
+ {
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getIntersect();
+ }
+
+ /**
+ * KURT.
+ *
+ * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness
+ * or flatness of a distribution compared with the normal distribution. Positive
+ * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a
+ * relatively flat distribution.
+ *
+ * @param array ...$args Data Series
+ *
+ * @return float|string
+ */
+ public static function KURT(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+ $mean = self::AVERAGE($aArgs);
+ $stdDev = self::STDEV($aArgs);
+
+ if ($stdDev > 0) {
+ $count = $summer = 0;
+ // Loop through arguments
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $summer += (($arg - $mean) / $stdDev) ** 4;
+ ++$count;
+ }
+ }
+ }
+
+ // Return
+ if ($count > 3) {
+ return $summer * ($count * ($count + 1) / (($count - 1) * ($count - 2) * ($count - 3))) - (3 * ($count - 1) ** 2 / (($count - 2) * ($count - 3)));
+ }
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * LARGE.
+ *
+ * Returns the nth largest value in a data set. You can use this function to
+ * select a value based on its relative standing.
+ *
+ * Excel Function:
+ * LARGE(value1[,value2[, ...]],entry)
+ *
+ * @param mixed $args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function LARGE(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+ $entry = array_pop($aArgs);
+
+ if ((is_numeric($entry)) && (!is_string($entry))) {
+ $entry = (int) floor($entry);
+
+ // Calculate
+ $mArgs = [];
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+ $count = self::COUNT($mArgs);
+ --$entry;
+ if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
+ return Functions::NAN();
+ }
+ rsort($mArgs);
+
+ return $mArgs[$entry];
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * LINEST.
+ *
+ * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data,
+ * and then returns an array that describes the line.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param null|mixed[] $xValues Data Series X
+ * @param bool $const a logical value specifying whether to force the intersect to equal 0
+ * @param bool $stats a logical value specifying whether to return additional regression statistics
+ *
+ * @return array|int|string The result, or a string containing an error
+ */
+ public static function LINEST($yValues, $xValues = null, $const = true, $stats = false)
+ {
+ $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
+ $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
+ if ($xValues === null) {
+ $xValues = range(1, count(Functions::flattenArray($yValues)));
+ }
+
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return 0;
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
+ if ($stats) {
+ return [
+ [
+ $bestFitLinear->getSlope(),
+ $bestFitLinear->getSlopeSE(),
+ $bestFitLinear->getGoodnessOfFit(),
+ $bestFitLinear->getF(),
+ $bestFitLinear->getSSRegression(),
+ ],
+ [
+ $bestFitLinear->getIntersect(),
+ $bestFitLinear->getIntersectSE(),
+ $bestFitLinear->getStdevOfResiduals(),
+ $bestFitLinear->getDFResiduals(),
+ $bestFitLinear->getSSResiduals(),
+ ],
+ ];
+ }
+
+ return [
+ $bestFitLinear->getSlope(),
+ $bestFitLinear->getIntersect(),
+ ];
+ }
+
+ /**
+ * LOGEST.
+ *
+ * Calculates an exponential curve that best fits the X and Y data series,
+ * and then returns an array that describes the line.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param null|mixed[] $xValues Data Series X
+ * @param bool $const a logical value specifying whether to force the intersect to equal 0
+ * @param bool $stats a logical value specifying whether to return additional regression statistics
+ *
+ * @return array|int|string The result, or a string containing an error
+ */
+ public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false)
+ {
+ $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
+ $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
+ if ($xValues === null) {
+ $xValues = range(1, count(Functions::flattenArray($yValues)));
+ }
+
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ foreach ($yValues as $value) {
+ if ($value <= 0.0) {
+ return Functions::NAN();
+ }
+ }
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return 1;
+ }
+
+ $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
+ if ($stats) {
+ return [
+ [
+ $bestFitExponential->getSlope(),
+ $bestFitExponential->getSlopeSE(),
+ $bestFitExponential->getGoodnessOfFit(),
+ $bestFitExponential->getF(),
+ $bestFitExponential->getSSRegression(),
+ ],
+ [
+ $bestFitExponential->getIntersect(),
+ $bestFitExponential->getIntersectSE(),
+ $bestFitExponential->getStdevOfResiduals(),
+ $bestFitExponential->getDFResiduals(),
+ $bestFitExponential->getSSResiduals(),
+ ],
+ ];
+ }
+
+ return [
+ $bestFitExponential->getSlope(),
+ $bestFitExponential->getIntersect(),
+ ];
+ }
+
+ /**
+ * LOGINV.
+ *
+ * Returns the inverse of the normal cumulative distribution
+ *
+ * @param float $probability
+ * @param float $mean
+ * @param float $stdDev
+ *
+ * @return float|string The result, or a string containing an error
+ *
+ * @TODO Try implementing P J Acklam's refinement algorithm for greater
+ * accuracy if I can get my head round the mathematics
+ * (as described at) http://home.online.no/~pjacklam/notes/invnorm/
+ */
+ public static function LOGINV($probability, $mean, $stdDev)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+
+ if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
+ return Functions::NAN();
+ }
+
+ return exp($mean + $stdDev * self::NORMSINV($probability));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * LOGNORMDIST.
+ *
+ * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed
+ * with parameters mean and standard_dev.
+ *
+ * @param float $value
+ * @param float $mean
+ * @param float $stdDev
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function LOGNORMDIST($value, $mean, $stdDev)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+
+ if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if (($value <= 0) || ($stdDev <= 0)) {
+ return Functions::NAN();
+ }
+
+ return self::NORMSDIST((log($value) - $mean) / $stdDev);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * LOGNORM.DIST.
+ *
+ * Returns the lognormal distribution of x, where ln(x) is normally distributed
+ * with parameters mean and standard_dev.
+ *
+ * @param float $value
+ * @param float $mean
+ * @param float $stdDev
+ * @param bool $cumulative
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function LOGNORMDIST2($value, $mean, $stdDev, $cumulative = false)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+ $cumulative = (bool) Functions::flattenSingleValue($cumulative);
+
+ if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if (($value <= 0) || ($stdDev <= 0)) {
+ return Functions::NAN();
+ }
+
+ if ($cumulative === true) {
+ return self::NORMSDIST2((log($value) - $mean) / $stdDev, true);
+ }
+
+ return (1 / (sqrt(2 * M_PI) * $stdDev * $value)) *
+ exp(0 - ((log($value) - $mean) ** 2 / (2 * $stdDev ** 2)));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * MAX.
+ *
+ * MAX returns the value of the element of the values passed that has the highest value,
+ * with negative numbers considered smaller than positive numbers.
+ *
+ * Excel Function:
+ * MAX(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float
+ */
+ public static function MAX(...$args)
+ {
+ $returnValue = null;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if (($returnValue === null) || ($arg > $returnValue)) {
+ $returnValue = $arg;
+ }
+ }
+ }
+
+ if ($returnValue === null) {
+ return 0;
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * MAXA.
+ *
+ * Returns the greatest value in a list of arguments, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * MAXA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float
+ */
+ public static function MAXA(...$args)
+ {
+ $returnValue = null;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ if (($returnValue === null) || ($arg > $returnValue)) {
+ $returnValue = $arg;
+ }
+ }
+ }
+
+ if ($returnValue === null) {
+ return 0;
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * MAXIFS.
+ *
+ * Counts the maximum value within a range of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
+ *
+ * @param mixed $args Data range and criterias
+ *
+ * @return float
+ */
+ public static function MAXIFS(...$args)
+ {
+ $arrayList = $args;
+
+ // Return value
+ $returnValue = null;
+
+ $maxArgs = Functions::flattenArray(array_shift($arrayList));
+ $aArgsArray = [];
+ $conditions = [];
+
+ while (count($arrayList) > 0) {
+ $aArgsArray[] = Functions::flattenArray(array_shift($arrayList));
+ $conditions[] = Functions::ifCondition(array_shift($arrayList));
+ }
+
+ // Loop through each arg and see if arguments and conditions are true
+ foreach ($maxArgs as $index => $value) {
+ $valid = true;
+
+ foreach ($conditions as $cidx => $condition) {
+ $arg = $aArgsArray[$cidx][$index];
+
+ // Loop through arguments
+ if (!is_numeric($arg)) {
+ $arg = Calculation::wrapResult(strtoupper($arg));
+ }
+ $testCondition = '=' . $arg . $condition;
+ if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
+ // Is not a value within our criteria
+ $valid = false;
+
+ break; // if false found, don't need to check other conditions
+ }
+ }
+
+ if ($valid) {
+ $returnValue = $returnValue === null ? $value : max($value, $returnValue);
+ }
+ }
+
+ // Return
+ return $returnValue;
+ }
+
+ /**
+ * MEDIAN.
+ *
+ * Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
+ *
+ * Excel Function:
+ * MEDIAN(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function MEDIAN(...$args)
+ {
+ $returnValue = Functions::NAN();
+
+ $mArgs = [];
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+
+ $mValueCount = count($mArgs);
+ if ($mValueCount > 0) {
+ sort($mArgs, SORT_NUMERIC);
+ $mValueCount = $mValueCount / 2;
+ if ($mValueCount == floor($mValueCount)) {
+ $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
+ } else {
+ $mValueCount = floor($mValueCount);
+ $returnValue = $mArgs[$mValueCount];
+ }
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * MIN.
+ *
+ * MIN returns the value of the element of the values passed that has the smallest value,
+ * with negative numbers considered smaller than positive numbers.
+ *
+ * Excel Function:
+ * MIN(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float
+ */
+ public static function MIN(...$args)
+ {
+ $returnValue = null;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if (($returnValue === null) || ($arg < $returnValue)) {
+ $returnValue = $arg;
+ }
+ }
+ }
+
+ if ($returnValue === null) {
+ return 0;
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * MINA.
+ *
+ * Returns the smallest value in a list of arguments, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * MINA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float
+ */
+ public static function MINA(...$args)
+ {
+ $returnValue = null;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ if (($returnValue === null) || ($arg < $returnValue)) {
+ $returnValue = $arg;
+ }
+ }
+ }
+
+ if ($returnValue === null) {
+ return 0;
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * MINIFS.
+ *
+ * Returns the minimum value within a range of cells that contain numbers within the list of arguments
+ *
+ * Excel Function:
+ * MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
+ *
+ * @param mixed $args Data range and criterias
+ *
+ * @return float
+ */
+ public static function MINIFS(...$args)
+ {
+ $arrayList = $args;
+
+ // Return value
+ $returnValue = null;
+
+ $minArgs = Functions::flattenArray(array_shift($arrayList));
+ $aArgsArray = [];
+ $conditions = [];
+
+ while (count($arrayList) > 0) {
+ $aArgsArray[] = Functions::flattenArray(array_shift($arrayList));
+ $conditions[] = Functions::ifCondition(array_shift($arrayList));
+ }
+
+ // Loop through each arg and see if arguments and conditions are true
+ foreach ($minArgs as $index => $value) {
+ $valid = true;
+
+ foreach ($conditions as $cidx => $condition) {
+ $arg = $aArgsArray[$cidx][$index];
+
+ // Loop through arguments
+ if (!is_numeric($arg)) {
+ $arg = Calculation::wrapResult(strtoupper($arg));
+ }
+ $testCondition = '=' . $arg . $condition;
+ if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
+ // Is not a value within our criteria
+ $valid = false;
+
+ break; // if false found, don't need to check other conditions
+ }
+ }
+
+ if ($valid) {
+ $returnValue = $returnValue === null ? $value : min($value, $returnValue);
+ }
+ }
+
+ // Return
+ return $returnValue;
+ }
+
+ //
+ // Special variant of array_count_values that isn't limited to strings and integers,
+ // but can work with floating point numbers as values
+ //
+ private static function modeCalc($data)
+ {
+ $frequencyArray = [];
+ $index = 0;
+ $maxfreq = 0;
+ $maxfreqkey = '';
+ $maxfreqdatum = '';
+ foreach ($data as $datum) {
+ $found = false;
+ ++$index;
+ foreach ($frequencyArray as $key => $value) {
+ if ((string) $value['value'] == (string) $datum) {
+ ++$frequencyArray[$key]['frequency'];
+ $freq = $frequencyArray[$key]['frequency'];
+ if ($freq > $maxfreq) {
+ $maxfreq = $freq;
+ $maxfreqkey = $key;
+ $maxfreqdatum = $datum;
+ } elseif ($freq == $maxfreq) {
+ if ($frequencyArray[$key]['index'] < $frequencyArray[$maxfreqkey]['index']) {
+ $maxfreqkey = $key;
+ $maxfreqdatum = $datum;
+ }
+ }
+ $found = true;
+
+ break;
+ }
+ }
+ if (!$found) {
+ $frequencyArray[] = [
+ 'value' => $datum,
+ 'frequency' => 1,
+ 'index' => $index,
+ ];
+ }
+ }
+
+ if ($maxfreq <= 1) {
+ return Functions::NA();
+ }
+
+ return $maxfreqdatum;
+ }
+
+ /**
+ * MODE.
+ *
+ * Returns the most frequently occurring, or repetitive, value in an array or range of data
+ *
+ * Excel Function:
+ * MODE(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function MODE(...$args)
+ {
+ $returnValue = Functions::NA();
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+
+ $mArgs = [];
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+
+ if (!empty($mArgs)) {
+ return self::modeCalc($mArgs);
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * NEGBINOMDIST.
+ *
+ * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that
+ * there will be number_f failures before the number_s-th success, when the constant
+ * probability of a success is probability_s. This function is similar to the binomial
+ * distribution, except that the number of successes is fixed, and the number of trials is
+ * variable. Like the binomial, trials are assumed to be independent.
+ *
+ * @param float $failures Number of Failures
+ * @param float $successes Threshold number of Successes
+ * @param float $probability Probability of success on each trial
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NEGBINOMDIST($failures, $successes, $probability)
+ {
+ $failures = floor(Functions::flattenSingleValue($failures));
+ $successes = floor(Functions::flattenSingleValue($successes));
+ $probability = Functions::flattenSingleValue($probability);
+
+ if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) {
+ if (($failures < 0) || ($successes < 1)) {
+ return Functions::NAN();
+ } elseif (($probability < 0) || ($probability > 1)) {
+ return Functions::NAN();
+ }
+ if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) {
+ if (($failures + $successes - 1) <= 0) {
+ return Functions::NAN();
+ }
+ }
+
+ return (MathTrig::COMBIN($failures + $successes - 1, $successes - 1)) * ($probability ** $successes) * ((1 - $probability) ** $failures);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * NORMDIST.
+ *
+ * Returns the normal distribution for the specified mean and standard deviation. This
+ * function has a very wide range of applications in statistics, including hypothesis
+ * testing.
+ *
+ * @param float $value
+ * @param float $mean Mean Value
+ * @param float $stdDev Standard Deviation
+ * @param bool $cumulative
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NORMDIST($value, $mean, $stdDev, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+
+ if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if ($stdDev < 0) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ return 0.5 * (1 + Engineering::erfVal(($value - $mean) / ($stdDev * sqrt(2))));
+ }
+
+ return (1 / (self::SQRT2PI * $stdDev)) * exp(0 - (($value - $mean) ** 2 / (2 * ($stdDev * $stdDev))));
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * NORMINV.
+ *
+ * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
+ *
+ * @param float $probability
+ * @param float $mean Mean Value
+ * @param float $stdDev Standard Deviation
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NORMINV($probability, $mean, $stdDev)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+
+ if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if (($probability < 0) || ($probability > 1)) {
+ return Functions::NAN();
+ }
+ if ($stdDev < 0) {
+ return Functions::NAN();
+ }
+
+ return (self::inverseNcdf($probability) * $stdDev) + $mean;
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * NORMSDIST.
+ *
+ * Returns the standard normal cumulative distribution function. The distribution has
+ * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a
+ * table of standard normal curve areas.
+ *
+ * @param float $value
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NORMSDIST($value)
+ {
+ $value = Functions::flattenSingleValue($value);
+ if (!is_numeric($value)) {
+ return Functions::VALUE();
+ }
+
+ return self::NORMDIST($value, 0, 1, true);
+ }
+
+ /**
+ * NORM.S.DIST.
+ *
+ * Returns the standard normal cumulative distribution function. The distribution has
+ * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a
+ * table of standard normal curve areas.
+ *
+ * @param float $value
+ * @param bool $cumulative
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NORMSDIST2($value, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ if (!is_numeric($value)) {
+ return Functions::VALUE();
+ }
+ $cumulative = (bool) Functions::flattenSingleValue($cumulative);
+
+ return self::NORMDIST($value, 0, 1, $cumulative);
+ }
+
+ /**
+ * NORMSINV.
+ *
+ * Returns the inverse of the standard normal cumulative distribution
+ *
+ * @param float $value
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function NORMSINV($value)
+ {
+ return self::NORMINV($value, 0, 1);
+ }
+
+ /**
+ * PERCENTILE.
+ *
+ * Returns the nth percentile of values in a range..
+ *
+ * Excel Function:
+ * PERCENTILE(value1[,value2[, ...]],entry)
+ *
+ * @param mixed $args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function PERCENTILE(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+
+ // Calculate
+ $entry = array_pop($aArgs);
+
+ if ((is_numeric($entry)) && (!is_string($entry))) {
+ if (($entry < 0) || ($entry > 1)) {
+ return Functions::NAN();
+ }
+ $mArgs = [];
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+ $mValueCount = count($mArgs);
+ if ($mValueCount > 0) {
+ sort($mArgs);
+ $count = self::COUNT($mArgs);
+ $index = $entry * ($count - 1);
+ $iBase = floor($index);
+ if ($index == $iBase) {
+ return $mArgs[$index];
+ }
+ $iNext = $iBase + 1;
+ $iProportion = $index - $iBase;
+
+ return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion);
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * PERCENTRANK.
+ *
+ * Returns the rank of a value in a data set as a percentage of the data set.
+ *
+ * @param float[] $valueSet An array of, or a reference to, a list of numbers
+ * @param int $value the number whose rank you want to find
+ * @param int $significance the number of significant digits for the returned percentage value
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function PERCENTRANK($valueSet, $value, $significance = 3)
+ {
+ $valueSet = Functions::flattenArray($valueSet);
+ $value = Functions::flattenSingleValue($value);
+ $significance = ($significance === null) ? 3 : (int) Functions::flattenSingleValue($significance);
+
+ foreach ($valueSet as $key => $valueEntry) {
+ if (!is_numeric($valueEntry)) {
+ unset($valueSet[$key]);
+ }
+ }
+ sort($valueSet, SORT_NUMERIC);
+ $valueCount = count($valueSet);
+ if ($valueCount == 0) {
+ return Functions::NAN();
+ }
+
+ $valueAdjustor = $valueCount - 1;
+ if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
+ return Functions::NA();
+ }
+
+ $pos = array_search($value, $valueSet);
+ if ($pos === false) {
+ $pos = 0;
+ $testValue = $valueSet[0];
+ while ($testValue < $value) {
+ $testValue = $valueSet[++$pos];
+ }
+ --$pos;
+ $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
+ }
+
+ return round($pos / $valueAdjustor, $significance);
+ }
+
+ /**
+ * PERMUT.
+ *
+ * Returns the number of permutations for a given number of objects that can be
+ * selected from number objects. A permutation is any set or subset of objects or
+ * events where internal order is significant. Permutations are different from
+ * combinations, for which the internal order is not significant. Use this function
+ * for lottery-style probability calculations.
+ *
+ * @param int $numObjs Number of different objects
+ * @param int $numInSet Number of objects in each permutation
+ *
+ * @return int|string Number of permutations, or a string containing an error
+ */
+ public static function PERMUT($numObjs, $numInSet)
+ {
+ $numObjs = Functions::flattenSingleValue($numObjs);
+ $numInSet = Functions::flattenSingleValue($numInSet);
+
+ if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
+ $numInSet = floor($numInSet);
+ if ($numObjs < $numInSet) {
+ return Functions::NAN();
+ }
+
+ return round(MathTrig::FACT($numObjs) / MathTrig::FACT($numObjs - $numInSet));
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * POISSON.
+ *
+ * Returns the Poisson distribution. A common application of the Poisson distribution
+ * is predicting the number of events over a specific time, such as the number of
+ * cars arriving at a toll plaza in 1 minute.
+ *
+ * @param float $value
+ * @param float $mean Mean Value
+ * @param bool $cumulative
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function POISSON($value, $mean, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $mean = Functions::flattenSingleValue($mean);
+
+ if ((is_numeric($value)) && (is_numeric($mean))) {
+ if (($value < 0) || ($mean <= 0)) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ $summer = 0;
+ $floor = floor($value);
+ for ($i = 0; $i <= $floor; ++$i) {
+ $summer += $mean ** $i / MathTrig::FACT($i);
+ }
+
+ return exp(0 - $mean) * $summer;
+ }
+
+ return (exp(0 - $mean) * $mean ** $value) / MathTrig::FACT($value);
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * QUARTILE.
+ *
+ * Returns the quartile of a data set.
+ *
+ * Excel Function:
+ * QUARTILE(value1[,value2[, ...]],entry)
+ *
+ * @param mixed $args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function QUARTILE(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+
+ // Calculate
+ $entry = floor(array_pop($aArgs));
+
+ if ((is_numeric($entry)) && (!is_string($entry))) {
+ $entry /= 4;
+ if (($entry < 0) || ($entry > 1)) {
+ return Functions::NAN();
+ }
+
+ return self::PERCENTILE($aArgs, $entry);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * RANK.
+ *
+ * Returns the rank of a number in a list of numbers.
+ *
+ * @param int $value the number whose rank you want to find
+ * @param float[] $valueSet An array of, or a reference to, a list of numbers
+ * @param int $order Order to sort the values in the value set
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function RANK($value, $valueSet, $order = 0)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $valueSet = Functions::flattenArray($valueSet);
+ $order = ($order === null) ? 0 : (int) Functions::flattenSingleValue($order);
+
+ foreach ($valueSet as $key => $valueEntry) {
+ if (!is_numeric($valueEntry)) {
+ unset($valueSet[$key]);
+ }
+ }
+
+ if ($order == 0) {
+ rsort($valueSet, SORT_NUMERIC);
+ } else {
+ sort($valueSet, SORT_NUMERIC);
+ }
+ $pos = array_search($value, $valueSet);
+ if ($pos === false) {
+ return Functions::NA();
+ }
+
+ return ++$pos;
+ }
+
+ /**
+ * RSQ.
+ *
+ * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function RSQ($yValues, $xValues)
+ {
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getGoodnessOfFit();
+ }
+
+ /**
+ * SKEW.
+ *
+ * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry
+ * of a distribution around its mean. Positive skewness indicates a distribution with an
+ * asymmetric tail extending toward more positive values. Negative skewness indicates a
+ * distribution with an asymmetric tail extending toward more negative values.
+ *
+ * @param array ...$args Data Series
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function SKEW(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+ $mean = self::AVERAGE($aArgs);
+ $stdDev = self::STDEV($aArgs);
+
+ $count = $summer = 0;
+ // Loop through arguments
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $summer += (($arg - $mean) / $stdDev) ** 3;
+ ++$count;
+ }
+ }
+ }
+
+ if ($count > 2) {
+ return $summer * ($count / (($count - 1) * ($count - 2)));
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * SLOPE.
+ *
+ * Returns the slope of the linear regression line through data points in known_y's and known_x's.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function SLOPE($yValues, $xValues)
+ {
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getSlope();
+ }
+
+ /**
+ * SMALL.
+ *
+ * Returns the nth smallest value in a data set. You can use this function to
+ * select a value based on its relative standing.
+ *
+ * Excel Function:
+ * SMALL(value1[,value2[, ...]],entry)
+ *
+ * @param mixed $args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function SMALL(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+
+ // Calculate
+ $entry = array_pop($aArgs);
+
+ if ((is_numeric($entry)) && (!is_string($entry))) {
+ $entry = (int) floor($entry);
+
+ $mArgs = [];
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+ $count = self::COUNT($mArgs);
+ --$entry;
+ if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
+ return Functions::NAN();
+ }
+ sort($mArgs);
+
+ return $mArgs[$entry];
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * STANDARDIZE.
+ *
+ * Returns a normalized value from a distribution characterized by mean and standard_dev.
+ *
+ * @param float $value Value to normalize
+ * @param float $mean Mean Value
+ * @param float $stdDev Standard Deviation
+ *
+ * @return float|string Standardized value, or a string containing an error
+ */
+ public static function STANDARDIZE($value, $mean, $stdDev)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $mean = Functions::flattenSingleValue($mean);
+ $stdDev = Functions::flattenSingleValue($stdDev);
+
+ if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
+ if ($stdDev <= 0) {
+ return Functions::NAN();
+ }
+
+ return ($value - $mean) / $stdDev;
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * STDEV.
+ *
+ * Estimates standard deviation based on a sample. The standard deviation is a measure of how
+ * widely values are dispersed from the average value (the mean).
+ *
+ * Excel Function:
+ * STDEV(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function STDEV(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ // Return value
+ $returnValue = null;
+
+ $aMean = self::AVERAGE($aArgs);
+ if ($aMean !== null) {
+ $aCount = -1;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))
+ ) {
+ $arg = (int) $arg;
+ }
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if ($returnValue === null) {
+ $returnValue = ($arg - $aMean) ** 2;
+ } else {
+ $returnValue += ($arg - $aMean) ** 2;
+ }
+ ++$aCount;
+ }
+ }
+
+ // Return
+ if (($aCount > 0) && ($returnValue >= 0)) {
+ return sqrt($returnValue / $aCount);
+ }
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * STDEVA.
+ *
+ * Estimates standard deviation based on a sample, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * STDEVA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function STDEVA(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ $returnValue = null;
+
+ $aMean = self::AVERAGEA($aArgs);
+ if ($aMean !== null) {
+ $aCount = -1;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ if ($returnValue === null) {
+ $returnValue = ($arg - $aMean) ** 2;
+ } else {
+ $returnValue += ($arg - $aMean) ** 2;
+ }
+ ++$aCount;
+ }
+ }
+ }
+
+ if (($aCount > 0) && ($returnValue >= 0)) {
+ return sqrt($returnValue / $aCount);
+ }
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * STDEVP.
+ *
+ * Calculates standard deviation based on the entire population
+ *
+ * Excel Function:
+ * STDEVP(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function STDEVP(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ $returnValue = null;
+
+ $aMean = self::AVERAGE($aArgs);
+ if ($aMean !== null) {
+ $aCount = 0;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))
+ ) {
+ $arg = (int) $arg;
+ }
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ if ($returnValue === null) {
+ $returnValue = ($arg - $aMean) ** 2;
+ } else {
+ $returnValue += ($arg - $aMean) ** 2;
+ }
+ ++$aCount;
+ }
+ }
+
+ if (($aCount > 0) && ($returnValue >= 0)) {
+ return sqrt($returnValue / $aCount);
+ }
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * STDEVPA.
+ *
+ * Calculates standard deviation based on the entire population, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * STDEVPA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string
+ */
+ public static function STDEVPA(...$args)
+ {
+ $aArgs = Functions::flattenArrayIndexed($args);
+
+ $returnValue = null;
+
+ $aMean = self::AVERAGEA($aArgs);
+ if ($aMean !== null) {
+ $aCount = 0;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_bool($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ if ($returnValue === null) {
+ $returnValue = ($arg - $aMean) ** 2;
+ } else {
+ $returnValue += ($arg - $aMean) ** 2;
+ }
+ ++$aCount;
+ }
+ }
+ }
+
+ if (($aCount > 0) && ($returnValue >= 0)) {
+ return sqrt($returnValue / $aCount);
+ }
+ }
+
+ return Functions::DIV0();
+ }
+
+ /**
+ * STEYX.
+ *
+ * Returns the standard error of the predicted y-value for each x in the regression.
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ *
+ * @return float|string
+ */
+ public static function STEYX($yValues, $xValues)
+ {
+ if (!self::checkTrendArrays($yValues, $xValues)) {
+ return Functions::VALUE();
+ }
+ $yValueCount = count($yValues);
+ $xValueCount = count($xValues);
+
+ if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
+ return Functions::NA();
+ } elseif ($yValueCount == 1) {
+ return Functions::DIV0();
+ }
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
+
+ return $bestFitLinear->getStdevOfResiduals();
+ }
+
+ /**
+ * TDIST.
+ *
+ * Returns the probability of Student's T distribution.
+ *
+ * @param float $value Value for the function
+ * @param float $degrees degrees of freedom
+ * @param float $tails number of tails (1 or 2)
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function TDIST($value, $degrees, $tails)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $degrees = floor(Functions::flattenSingleValue($degrees));
+ $tails = floor(Functions::flattenSingleValue($tails));
+
+ if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
+ if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
+ return Functions::NAN();
+ }
+ // tdist, which finds the probability that corresponds to a given value
+ // of t with k degrees of freedom. This algorithm is translated from a
+ // pascal function on p81 of "Statistical Computing in Pascal" by D
+ // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
+ // London). The above Pascal algorithm is itself a translation of the
+ // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
+ // Laboratory as reported in (among other places) "Applied Statistics
+ // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
+ // Horwood Ltd.; W. Sussex, England).
+ $tterm = $degrees;
+ $ttheta = atan2($value, sqrt($tterm));
+ $tc = cos($ttheta);
+ $ts = sin($ttheta);
+
+ if (($degrees % 2) == 1) {
+ $ti = 3;
+ $tterm = $tc;
+ } else {
+ $ti = 2;
+ $tterm = 1;
+ }
+
+ $tsum = $tterm;
+ while ($ti < $degrees) {
+ $tterm *= $tc * $tc * ($ti - 1) / $ti;
+ $tsum += $tterm;
+ $ti += 2;
+ }
+ $tsum *= $ts;
+ if (($degrees % 2) == 1) {
+ $tsum = Functions::M_2DIVPI * ($tsum + $ttheta);
+ }
+ $tValue = 0.5 * (1 + $tsum);
+ if ($tails == 1) {
+ return 1 - abs($tValue);
+ }
+
+ return 1 - abs((1 - $tValue) - $tValue);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * TINV.
+ *
+ * Returns the one-tailed probability of the chi-squared distribution.
+ *
+ * @param float $probability Probability for the function
+ * @param float $degrees degrees of freedom
+ *
+ * @return float|string The result, or a string containing an error
+ */
+ public static function TINV($probability, $degrees)
+ {
+ $probability = Functions::flattenSingleValue($probability);
+ $degrees = floor(Functions::flattenSingleValue($degrees));
+
+ if ((is_numeric($probability)) && (is_numeric($degrees))) {
+ $xLo = 100;
+ $xHi = 0;
+
+ $x = $xNew = 1;
+ $dx = 1;
+ $i = 0;
+
+ while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) {
+ // Apply Newton-Raphson step
+ $result = self::TDIST($x, $degrees, 2);
+ $error = $result - $probability;
+ if ($error == 0.0) {
+ $dx = 0;
+ } elseif ($error < 0.0) {
+ $xLo = $x;
+ } else {
+ $xHi = $x;
+ }
+ // Avoid division by zero
+ if ($result != 0.0) {
+ $dx = $error / $result;
+ $xNew = $x - $dx;
+ }
+ // If the NR fails to converge (which for example may be the
+ // case if the initial guess is too rough) we apply a bisection
+ // step to determine a more narrow interval around the root.
+ if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
+ $xNew = ($xLo + $xHi) / 2;
+ $dx = $xNew - $x;
+ }
+ $x = $xNew;
+ }
+ if ($i == self::MAX_ITERATIONS) {
+ return Functions::NA();
+ }
+
+ return round($x, 12);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * TREND.
+ *
+ * Returns values along a linear Trend
+ *
+ * @param mixed[] $yValues Data Series Y
+ * @param mixed[] $xValues Data Series X
+ * @param mixed[] $newValues Values of X for which we want to find Y
+ * @param bool $const a logical value specifying whether to force the intersect to equal 0
+ *
+ * @return array of float
+ */
+ public static function TREND($yValues, $xValues = [], $newValues = [], $const = true)
+ {
+ $yValues = Functions::flattenArray($yValues);
+ $xValues = Functions::flattenArray($xValues);
+ $newValues = Functions::flattenArray($newValues);
+ $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
+
+ $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
+ if (empty($newValues)) {
+ $newValues = $bestFitLinear->getXValues();
+ }
+
+ $returnArray = [];
+ foreach ($newValues as $xValue) {
+ $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue);
+ }
+
+ return $returnArray;
+ }
+
+ /**
+ * TRIMMEAN.
+ *
+ * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
+ * taken by excluding a percentage of data points from the top and bottom tails
+ * of a data set.
+ *
+ * Excel Function:
+ * TRIMEAN(value1[,value2[, ...]], $discard)
+ *
+ * @param mixed $args Data values
+ *
+ * @return float|string
+ */
+ public static function TRIMMEAN(...$args)
+ {
+ $aArgs = Functions::flattenArray($args);
+
+ // Calculate
+ $percent = array_pop($aArgs);
+
+ if ((is_numeric($percent)) && (!is_string($percent))) {
+ if (($percent < 0) || ($percent > 1)) {
+ return Functions::NAN();
+ }
+ $mArgs = [];
+ foreach ($aArgs as $arg) {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $mArgs[] = $arg;
+ }
+ }
+ $discard = floor(self::COUNT($mArgs) * $percent / 2);
+ sort($mArgs);
+ for ($i = 0; $i < $discard; ++$i) {
+ array_pop($mArgs);
+ array_shift($mArgs);
+ }
+
+ return self::AVERAGE($mArgs);
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * VARFunc.
+ *
+ * Estimates variance based on a sample.
+ *
+ * Excel Function:
+ * VAR(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function VARFunc(...$args)
+ {
+ $returnValue = Functions::DIV0();
+
+ $summerA = $summerB = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ $aCount = 0;
+ foreach ($aArgs as $arg) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ }
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $summerA += ($arg * $arg);
+ $summerB += $arg;
+ ++$aCount;
+ }
+ }
+
+ if ($aCount > 1) {
+ $summerA *= $aCount;
+ $summerB *= $summerB;
+ $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * VARA.
+ *
+ * Estimates variance based on a sample, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * VARA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function VARA(...$args)
+ {
+ $returnValue = Functions::DIV0();
+
+ $summerA = $summerB = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArrayIndexed($args);
+ $aCount = 0;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_string($arg)) &&
+ (Functions::isValue($k))
+ ) {
+ return Functions::VALUE();
+ } elseif (
+ (is_string($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ $summerA += ($arg * $arg);
+ $summerB += $arg;
+ ++$aCount;
+ }
+ }
+ }
+
+ if ($aCount > 1) {
+ $summerA *= $aCount;
+ $summerB *= $summerB;
+ $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * VARP.
+ *
+ * Calculates variance based on the entire population
+ *
+ * Excel Function:
+ * VARP(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function VARP(...$args)
+ {
+ // Return value
+ $returnValue = Functions::DIV0();
+
+ $summerA = $summerB = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArray($args);
+ $aCount = 0;
+ foreach ($aArgs as $arg) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ }
+ // Is it a numeric value?
+ if ((is_numeric($arg)) && (!is_string($arg))) {
+ $summerA += ($arg * $arg);
+ $summerB += $arg;
+ ++$aCount;
+ }
+ }
+
+ if ($aCount > 0) {
+ $summerA *= $aCount;
+ $summerB *= $summerB;
+ $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * VARPA.
+ *
+ * Calculates variance based on the entire population, including numbers, text, and logical values
+ *
+ * Excel Function:
+ * VARPA(value1[,value2[, ...]])
+ *
+ * @param mixed ...$args Data values
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function VARPA(...$args)
+ {
+ $returnValue = Functions::DIV0();
+
+ $summerA = $summerB = 0;
+
+ // Loop through arguments
+ $aArgs = Functions::flattenArrayIndexed($args);
+ $aCount = 0;
+ foreach ($aArgs as $k => $arg) {
+ if (
+ (is_string($arg)) &&
+ (Functions::isValue($k))
+ ) {
+ return Functions::VALUE();
+ } elseif (
+ (is_string($arg)) &&
+ (!Functions::isMatrixValue($k))
+ ) {
+ } else {
+ // Is it a numeric value?
+ if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
+ if (is_bool($arg)) {
+ $arg = (int) $arg;
+ } elseif (is_string($arg)) {
+ $arg = 0;
+ }
+ $summerA += ($arg * $arg);
+ $summerB += $arg;
+ ++$aCount;
+ }
+ }
+ }
+
+ if ($aCount > 0) {
+ $summerA *= $aCount;
+ $summerB *= $summerB;
+ $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
+ }
+
+ return $returnValue;
+ }
+
+ /**
+ * WEIBULL.
+ *
+ * Returns the Weibull distribution. Use this distribution in reliability
+ * analysis, such as calculating a device's mean time to failure.
+ *
+ * @param float $value
+ * @param float $alpha Alpha Parameter
+ * @param float $beta Beta Parameter
+ * @param bool $cumulative
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function WEIBULL($value, $alpha, $beta, $cumulative)
+ {
+ $value = Functions::flattenSingleValue($value);
+ $alpha = Functions::flattenSingleValue($alpha);
+ $beta = Functions::flattenSingleValue($beta);
+
+ if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
+ if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
+ return Functions::NAN();
+ }
+ if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
+ if ($cumulative) {
+ return 1 - exp(0 - ($value / $beta) ** $alpha);
+ }
+
+ return ($alpha / $beta ** $alpha) * $value ** ($alpha - 1) * exp(0 - ($value / $beta) ** $alpha);
+ }
+ }
+
+ return Functions::VALUE();
+ }
+
+ /**
+ * ZTEST.
+ *
+ * Returns the Weibull distribution. Use this distribution in reliability
+ * analysis, such as calculating a device's mean time to failure.
+ *
+ * @param float $dataSet
+ * @param float $m0 Alpha Parameter
+ * @param float $sigma Beta Parameter
+ *
+ * @return float|string (string if result is an error)
+ */
+ public static function ZTEST($dataSet, $m0, $sigma = null)
+ {
+ $dataSet = Functions::flattenArrayIndexed($dataSet);
+ $m0 = Functions::flattenSingleValue($m0);
+ $sigma = Functions::flattenSingleValue($sigma);
+
+ if ($sigma === null) {
+ $sigma = self::STDEV($dataSet);
+ }
+ $n = count($dataSet);
+
+ return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / sqrt($n)));
+ }
+}
|