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