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