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(); |
||
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; |
||
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(); |
||
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(); |
||
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(); |
||
797 | } |
||
798 | |||
799 | return round($rMin + $guess * ($rMax - $rMin), 12); |
||
800 | } |
||
801 | |||
802 | return Functions::VALUE(); |
||
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 | public static function COUNTIF($aArgs, $condition) |
||
1117 | { |
||
1118 | $returnValue = 0; |
||
1119 | |||
1120 | $aArgs = Functions::flattenArray($aArgs); |
||
1121 | $condition = Functions::ifCondition($condition); |
||
1122 | // Loop through arguments |
||
1123 | foreach ($aArgs as $arg) { |
||
1124 | if (!is_numeric($arg)) { |
||
1125 | $arg = Calculation::wrapResult(strtoupper($arg)); |
||
1126 | } |
||
1127 | $testCondition = '=' . $arg . $condition; |
||
1128 | if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) { |
||
1129 | // Is it a value within our criteria |
||
1130 | ++$returnValue; |
||
1131 | } |
||
1132 | } |
||
1133 | |||
1134 | return $returnValue; |
||
1135 | } |
||
1136 | |||
1137 | /** |
||
1138 | * COVAR. |
||
1139 | * |
||
1140 | * Returns covariance, the average of the products of deviations for each data point pair. |
||
1141 | * |
||
1142 | * @param mixed $yValues array of mixed Data Series Y |
||
1143 | * @param mixed $xValues array of mixed Data Series X |
||
1144 | * |
||
1145 | * @return float |
||
1146 | */ |
||
1147 | public static function COVAR($yValues, $xValues) |
||
1148 | { |
||
1149 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
1150 | return Functions::VALUE(); |
||
1151 | } |
||
1152 | $yValueCount = count($yValues); |
||
1153 | $xValueCount = count($xValues); |
||
1154 | |||
1155 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
1156 | return Functions::NA(); |
||
1157 | } elseif ($yValueCount == 1) { |
||
1158 | return Functions::DIV0(); |
||
1159 | } |
||
1160 | |||
1161 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
1162 | |||
1163 | return $bestFitLinear->getCovariance(); |
||
1164 | } |
||
1165 | |||
1166 | /** |
||
1167 | * CRITBINOM. |
||
1168 | * |
||
1169 | * Returns the smallest value for which the cumulative binomial distribution is greater |
||
1170 | * than or equal to a criterion value |
||
1171 | * |
||
1172 | * See https://support.microsoft.com/en-us/help/828117/ for details of the algorithm used |
||
1173 | * |
||
1174 | * @param float $trials number of Bernoulli trials |
||
1175 | * @param float $probability probability of a success on each trial |
||
1176 | * @param float $alpha criterion value |
||
1177 | * |
||
1178 | * @return int |
||
1179 | * |
||
1180 | * @todo Warning. This implementation differs from the algorithm detailed on the MS |
||
1181 | * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess |
||
1182 | * This eliminates a potential endless loop error, but may have an adverse affect on the |
||
1183 | * accuracy of the function (although all my tests have so far returned correct results). |
||
1184 | */ |
||
1185 | public static function CRITBINOM($trials, $probability, $alpha) |
||
1186 | { |
||
1187 | $trials = floor(Functions::flattenSingleValue($trials)); |
||
1188 | $probability = Functions::flattenSingleValue($probability); |
||
1189 | $alpha = Functions::flattenSingleValue($alpha); |
||
1190 | |||
1191 | if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) { |
||
1192 | if ($trials < 0) { |
||
1193 | return Functions::NAN(); |
||
1194 | } elseif (($probability < 0) || ($probability > 1)) { |
||
1195 | return Functions::NAN(); |
||
1196 | } elseif (($alpha < 0) || ($alpha > 1)) { |
||
1197 | return Functions::NAN(); |
||
1198 | } elseif ($alpha <= 0.5) { |
||
1199 | $t = sqrt(log(1 / ($alpha * $alpha))); |
||
1200 | $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)); |
||
1201 | } else { |
||
1202 | $t = sqrt(log(1 / pow(1 - $alpha, 2))); |
||
1203 | $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t); |
||
1204 | } |
||
1205 | $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability))); |
||
1206 | if ($Guess < 0) { |
||
1207 | $Guess = 0; |
||
1208 | } elseif ($Guess > $trials) { |
||
1209 | $Guess = $trials; |
||
1210 | } |
||
1211 | |||
1212 | $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0; |
||
1213 | $EssentiallyZero = 10e-12; |
||
1214 | |||
1215 | $m = floor($trials * $probability); |
||
1216 | ++$TotalUnscaledProbability; |
||
1217 | if ($m == $Guess) { |
||
1218 | ++$UnscaledPGuess; |
||
1219 | } |
||
1220 | if ($m <= $Guess) { |
||
1221 | ++$UnscaledCumPGuess; |
||
1222 | } |
||
1223 | |||
1224 | $PreviousValue = 1; |
||
1225 | $Done = false; |
||
1226 | $k = $m + 1; |
||
1227 | while ((!$Done) && ($k <= $trials)) { |
||
1228 | $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability)); |
||
1229 | $TotalUnscaledProbability += $CurrentValue; |
||
1230 | if ($k == $Guess) { |
||
1231 | $UnscaledPGuess += $CurrentValue; |
||
1232 | } |
||
1233 | if ($k <= $Guess) { |
||
1234 | $UnscaledCumPGuess += $CurrentValue; |
||
1235 | } |
||
1236 | if ($CurrentValue <= $EssentiallyZero) { |
||
1237 | $Done = true; |
||
1238 | } |
||
1239 | $PreviousValue = $CurrentValue; |
||
1240 | ++$k; |
||
1241 | } |
||
1242 | |||
1243 | $PreviousValue = 1; |
||
1244 | $Done = false; |
||
1245 | $k = $m - 1; |
||
1246 | while ((!$Done) && ($k >= 0)) { |
||
1247 | $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability); |
||
1248 | $TotalUnscaledProbability += $CurrentValue; |
||
1249 | if ($k == $Guess) { |
||
1250 | $UnscaledPGuess += $CurrentValue; |
||
1251 | } |
||
1252 | if ($k <= $Guess) { |
||
1253 | $UnscaledCumPGuess += $CurrentValue; |
||
1254 | } |
||
1255 | if ($CurrentValue <= $EssentiallyZero) { |
||
1256 | $Done = true; |
||
1257 | } |
||
1258 | $PreviousValue = $CurrentValue; |
||
1259 | --$k; |
||
1260 | } |
||
1261 | |||
1262 | $PGuess = $UnscaledPGuess / $TotalUnscaledProbability; |
||
1263 | $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability; |
||
1264 | |||
1265 | $CumPGuessMinus1 = $CumPGuess - 1; |
||
1266 | |||
1267 | while (true) { |
||
1268 | if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) { |
||
1269 | return $Guess; |
||
1270 | } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) { |
||
1271 | $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability); |
||
1272 | $CumPGuessMinus1 = $CumPGuess; |
||
1273 | $CumPGuess = $CumPGuess + $PGuessPlus1; |
||
1274 | $PGuess = $PGuessPlus1; |
||
1275 | ++$Guess; |
||
1276 | } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) { |
||
1277 | $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability; |
||
1278 | $CumPGuess = $CumPGuessMinus1; |
||
1279 | $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess; |
||
1280 | $PGuess = $PGuessMinus1; |
||
1281 | --$Guess; |
||
1282 | } |
||
1283 | } |
||
1284 | } |
||
1285 | |||
1286 | return Functions::VALUE(); |
||
1287 | } |
||
1288 | |||
1289 | /** |
||
1290 | * DEVSQ. |
||
1291 | * |
||
1292 | * Returns the sum of squares of deviations of data points from their sample mean. |
||
1293 | * |
||
1294 | * Excel Function: |
||
1295 | * DEVSQ(value1[,value2[, ...]]) |
||
1296 | * |
||
1297 | * @category Statistical Functions |
||
1298 | * |
||
1299 | * @param mixed ...$args Data values |
||
1300 | * |
||
1301 | * @return float |
||
1302 | */ |
||
1303 | 1 | public static function DEVSQ(...$args) |
|
1304 | { |
||
1305 | 1 | $aArgs = Functions::flattenArrayIndexed($args); |
|
1306 | |||
1307 | // Return value |
||
1308 | 1 | $returnValue = null; |
|
1309 | |||
1310 | 1 | $aMean = self::AVERAGE($aArgs); |
|
1311 | 1 | if ($aMean != Functions::DIV0()) { |
|
1312 | 1 | $aCount = -1; |
|
1313 | 1 | foreach ($aArgs as $k => $arg) { |
|
1314 | // Is it a numeric value? |
||
1315 | 1 | if ((is_bool($arg)) && |
|
1316 | 1 | ((!Functions::isCellValue($k)) || |
|
1317 | 1 | (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))) { |
|
1318 | $arg = (int) $arg; |
||
1319 | } |
||
1320 | 1 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
1321 | 1 | if ($returnValue === null) { |
|
1322 | 1 | $returnValue = pow(($arg - $aMean), 2); |
|
1323 | } else { |
||
1324 | 1 | $returnValue += pow(($arg - $aMean), 2); |
|
1325 | } |
||
1326 | 1 | ++$aCount; |
|
1327 | } |
||
1328 | } |
||
1329 | |||
1330 | // Return |
||
1331 | 1 | if ($returnValue === null) { |
|
1332 | return Functions::NAN(); |
||
1333 | } |
||
1334 | |||
1335 | 1 | return $returnValue; |
|
1336 | } |
||
1337 | |||
1338 | return self::NA(); |
||
1339 | } |
||
1340 | |||
1341 | /** |
||
1342 | * EXPONDIST. |
||
1343 | * |
||
1344 | * Returns the exponential distribution. Use EXPONDIST to model the time between events, |
||
1345 | * such as how long an automated bank teller takes to deliver cash. For example, you can |
||
1346 | * use EXPONDIST to determine the probability that the process takes at most 1 minute. |
||
1347 | * |
||
1348 | * @param float $value Value of the function |
||
1349 | * @param float $lambda The parameter value |
||
1350 | * @param bool $cumulative |
||
1351 | * |
||
1352 | * @return float |
||
1353 | */ |
||
1354 | public static function EXPONDIST($value, $lambda, $cumulative) |
||
1355 | { |
||
1356 | $value = Functions::flattenSingleValue($value); |
||
1357 | $lambda = Functions::flattenSingleValue($lambda); |
||
1358 | $cumulative = Functions::flattenSingleValue($cumulative); |
||
1359 | |||
1360 | if ((is_numeric($value)) && (is_numeric($lambda))) { |
||
1361 | if (($value < 0) || ($lambda < 0)) { |
||
1362 | return Functions::NAN(); |
||
1363 | } |
||
1364 | if ((is_numeric($cumulative)) || (is_bool($cumulative))) { |
||
1365 | if ($cumulative) { |
||
1366 | return 1 - exp(0 - $value * $lambda); |
||
1367 | } |
||
1368 | |||
1369 | return $lambda * exp(0 - $value * $lambda); |
||
1370 | } |
||
1371 | } |
||
1372 | |||
1373 | return Functions::VALUE(); |
||
1374 | } |
||
1375 | |||
1376 | /** |
||
1377 | * FISHER. |
||
1378 | * |
||
1379 | * Returns the Fisher transformation at x. This transformation produces a function that |
||
1380 | * is normally distributed rather than skewed. Use this function to perform hypothesis |
||
1381 | * testing on the correlation coefficient. |
||
1382 | * |
||
1383 | * @param float $value |
||
1384 | * |
||
1385 | * @return float |
||
1386 | */ |
||
1387 | public static function FISHER($value) |
||
1388 | { |
||
1389 | $value = Functions::flattenSingleValue($value); |
||
1390 | |||
1391 | if (is_numeric($value)) { |
||
1392 | if (($value <= -1) || ($value >= 1)) { |
||
1393 | return Functions::NAN(); |
||
1394 | } |
||
1395 | |||
1396 | return 0.5 * log((1 + $value) / (1 - $value)); |
||
1397 | } |
||
1398 | |||
1399 | return Functions::VALUE(); |
||
1400 | } |
||
1401 | |||
1402 | /** |
||
1403 | * FISHERINV. |
||
1404 | * |
||
1405 | * Returns the inverse of the Fisher transformation. Use this transformation when |
||
1406 | * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then |
||
1407 | * FISHERINV(y) = x. |
||
1408 | * |
||
1409 | * @param float $value |
||
1410 | * |
||
1411 | * @return float |
||
1412 | */ |
||
1413 | public static function FISHERINV($value) |
||
1414 | { |
||
1415 | $value = Functions::flattenSingleValue($value); |
||
1416 | |||
1417 | if (is_numeric($value)) { |
||
1418 | return (exp(2 * $value) - 1) / (exp(2 * $value) + 1); |
||
1419 | } |
||
1420 | |||
1421 | return Functions::VALUE(); |
||
1422 | } |
||
1423 | |||
1424 | /** |
||
1425 | * FORECAST. |
||
1426 | * |
||
1427 | * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. |
||
1428 | * |
||
1429 | * @param float $xValue Value of X for which we want to find Y |
||
1430 | * @param mixed $yValues array of mixed Data Series Y |
||
1431 | * @param mixed $xValues of mixed Data Series X |
||
1432 | * |
||
1433 | * @return float |
||
1434 | */ |
||
1435 | public static function FORECAST($xValue, $yValues, $xValues) |
||
1436 | { |
||
1437 | $xValue = Functions::flattenSingleValue($xValue); |
||
1438 | if (!is_numeric($xValue)) { |
||
1439 | return Functions::VALUE(); |
||
1440 | } elseif (!self::checkTrendArrays($yValues, $xValues)) { |
||
1441 | return Functions::VALUE(); |
||
1442 | } |
||
1443 | $yValueCount = count($yValues); |
||
1444 | $xValueCount = count($xValues); |
||
1445 | |||
1446 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
1447 | return Functions::NA(); |
||
1448 | } elseif ($yValueCount == 1) { |
||
1449 | return Functions::DIV0(); |
||
1450 | } |
||
1451 | |||
1452 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
1453 | |||
1454 | return $bestFitLinear->getValueOfYForX($xValue); |
||
1455 | } |
||
1456 | |||
1457 | /** |
||
1458 | * GAMMADIST. |
||
1459 | * |
||
1460 | * Returns the gamma distribution. |
||
1461 | * |
||
1462 | * @param float $value Value at which you want to evaluate the distribution |
||
1463 | * @param float $a Parameter to the distribution |
||
1464 | * @param float $b Parameter to the distribution |
||
1465 | * @param bool $cumulative |
||
1466 | * |
||
1467 | * @return float |
||
1468 | */ |
||
1469 | public static function GAMMADIST($value, $a, $b, $cumulative) |
||
1470 | { |
||
1471 | $value = Functions::flattenSingleValue($value); |
||
1472 | $a = Functions::flattenSingleValue($a); |
||
1473 | $b = Functions::flattenSingleValue($b); |
||
1474 | |||
1475 | if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) { |
||
1476 | if (($value < 0) || ($a <= 0) || ($b <= 0)) { |
||
1477 | return Functions::NAN(); |
||
1478 | } |
||
1479 | if ((is_numeric($cumulative)) || (is_bool($cumulative))) { |
||
1480 | if ($cumulative) { |
||
1481 | return self::incompleteGamma($a, $value / $b) / self::gamma($a); |
||
1482 | } |
||
1483 | |||
1484 | return (1 / (pow($b, $a) * self::gamma($a))) * pow($value, $a - 1) * exp(0 - ($value / $b)); |
||
1485 | } |
||
1486 | } |
||
1487 | |||
1488 | return Functions::VALUE(); |
||
1489 | } |
||
1490 | |||
1491 | /** |
||
1492 | * GAMMAINV. |
||
1493 | * |
||
1494 | * Returns the inverse of the beta distribution. |
||
1495 | * |
||
1496 | * @param float $probability Probability at which you want to evaluate the distribution |
||
1497 | * @param float $alpha Parameter to the distribution |
||
1498 | * @param float $beta Parameter to the distribution |
||
1499 | * |
||
1500 | * @return float |
||
1501 | */ |
||
1502 | public static function GAMMAINV($probability, $alpha, $beta) |
||
1503 | { |
||
1504 | $probability = Functions::flattenSingleValue($probability); |
||
1505 | $alpha = Functions::flattenSingleValue($alpha); |
||
1506 | $beta = Functions::flattenSingleValue($beta); |
||
1507 | |||
1508 | if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) { |
||
1509 | if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) { |
||
1510 | return Functions::NAN(); |
||
1511 | } |
||
1512 | |||
1513 | $xLo = 0; |
||
1514 | $xHi = $alpha * $beta * 5; |
||
1515 | |||
1516 | $x = $xNew = 1; |
||
1517 | $error = $pdf = 0; |
||
1518 | $dx = 1024; |
||
1519 | $i = 0; |
||
1520 | |||
1521 | while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { |
||
1522 | // Apply Newton-Raphson step |
||
1523 | $error = self::GAMMADIST($x, $alpha, $beta, true) - $probability; |
||
1524 | if ($error < 0.0) { |
||
1525 | $xLo = $x; |
||
1526 | } else { |
||
1527 | $xHi = $x; |
||
1528 | } |
||
1529 | $pdf = self::GAMMADIST($x, $alpha, $beta, false); |
||
1530 | // Avoid division by zero |
||
1531 | if ($pdf != 0.0) { |
||
1532 | $dx = $error / $pdf; |
||
1533 | $xNew = $x - $dx; |
||
1534 | } |
||
1535 | // If the NR fails to converge (which for example may be the |
||
1536 | // case if the initial guess is too rough) we apply a bisection |
||
1537 | // step to determine a more narrow interval around the root. |
||
1538 | if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) { |
||
1539 | $xNew = ($xLo + $xHi) / 2; |
||
1540 | $dx = $xNew - $x; |
||
1541 | } |
||
1542 | $x = $xNew; |
||
1543 | } |
||
1544 | if ($i == self::MAX_ITERATIONS) { |
||
1545 | return Functions::NA(); |
||
1546 | } |
||
1547 | |||
1548 | return $x; |
||
1549 | } |
||
1550 | |||
1551 | return Functions::VALUE(); |
||
1552 | } |
||
1553 | |||
1554 | /** |
||
1555 | * GAMMALN. |
||
1556 | * |
||
1557 | * Returns the natural logarithm of the gamma function. |
||
1558 | * |
||
1559 | * @param float $value |
||
1560 | * |
||
1561 | * @return float |
||
1562 | */ |
||
1563 | public static function GAMMALN($value) |
||
1564 | { |
||
1565 | $value = Functions::flattenSingleValue($value); |
||
1566 | |||
1567 | if (is_numeric($value)) { |
||
1568 | if ($value <= 0) { |
||
1569 | return Functions::NAN(); |
||
1570 | } |
||
1571 | |||
1572 | return log(self::gamma($value)); |
||
1573 | } |
||
1574 | |||
1575 | return Functions::VALUE(); |
||
1576 | } |
||
1577 | |||
1578 | /** |
||
1579 | * GEOMEAN. |
||
1580 | * |
||
1581 | * Returns the geometric mean of an array or range of positive data. For example, you |
||
1582 | * can use GEOMEAN to calculate average growth rate given compound interest with |
||
1583 | * variable rates. |
||
1584 | * |
||
1585 | * Excel Function: |
||
1586 | * GEOMEAN(value1[,value2[, ...]]) |
||
1587 | * |
||
1588 | * @category Statistical Functions |
||
1589 | * |
||
1590 | * @param mixed ...$args Data values |
||
1591 | * |
||
1592 | * @return float |
||
1593 | */ |
||
1594 | public static function GEOMEAN(...$args) |
||
1595 | { |
||
1596 | $aArgs = Functions::flattenArray($args); |
||
1597 | |||
1598 | $aMean = MathTrig::PRODUCT($aArgs); |
||
1599 | if (is_numeric($aMean) && ($aMean > 0)) { |
||
1600 | $aCount = self::COUNT($aArgs); |
||
1601 | if (self::MIN($aArgs) > 0) { |
||
1602 | return pow($aMean, (1 / $aCount)); |
||
1603 | } |
||
1604 | } |
||
1605 | |||
1606 | return Functions::NAN(); |
||
1607 | } |
||
1608 | |||
1609 | /** |
||
1610 | * GROWTH. |
||
1611 | * |
||
1612 | * Returns values along a predicted emponential Trend |
||
1613 | * |
||
1614 | * @param mixed[] $yValues Data Series Y |
||
1615 | * @param mixed[] $xValues Data Series X |
||
1616 | * @param mixed[] $newValues Values of X for which we want to find Y |
||
1617 | * @param bool $const a logical value specifying whether to force the intersect to equal 0 |
||
1618 | * |
||
1619 | * @return array of float |
||
1620 | */ |
||
1621 | public static function GROWTH($yValues, $xValues = [], $newValues = [], $const = true) |
||
1622 | { |
||
1623 | $yValues = Functions::flattenArray($yValues); |
||
1624 | $xValues = Functions::flattenArray($xValues); |
||
1625 | $newValues = Functions::flattenArray($newValues); |
||
1626 | $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); |
||
1627 | |||
1628 | $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const); |
||
1629 | if (empty($newValues)) { |
||
1630 | $newValues = $bestFitExponential->getXValues(); |
||
1631 | } |
||
1632 | |||
1633 | $returnArray = []; |
||
1634 | foreach ($newValues as $xValue) { |
||
1635 | $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue); |
||
1636 | } |
||
1637 | |||
1638 | return $returnArray; |
||
1639 | } |
||
1640 | |||
1641 | /** |
||
1642 | * HARMEAN. |
||
1643 | * |
||
1644 | * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the |
||
1645 | * arithmetic mean of reciprocals. |
||
1646 | * |
||
1647 | * Excel Function: |
||
1648 | * HARMEAN(value1[,value2[, ...]]) |
||
1649 | * |
||
1650 | * @category Statistical Functions |
||
1651 | * |
||
1652 | * @param mixed ...$args Data values |
||
1653 | * |
||
1654 | * @return float |
||
1655 | */ |
||
1656 | public static function HARMEAN(...$args) |
||
1657 | { |
||
1658 | // Return value |
||
1659 | $returnValue = Functions::NA(); |
||
1660 | |||
1661 | // Loop through arguments |
||
1662 | $aArgs = Functions::flattenArray($args); |
||
1663 | if (self::MIN($aArgs) < 0) { |
||
1664 | return Functions::NAN(); |
||
1665 | } |
||
1666 | $aCount = 0; |
||
1667 | foreach ($aArgs as $arg) { |
||
1668 | // Is it a numeric value? |
||
1669 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
1670 | if ($arg <= 0) { |
||
1671 | return Functions::NAN(); |
||
1672 | } |
||
1673 | if ($returnValue === null) { |
||
1674 | $returnValue = (1 / $arg); |
||
1675 | } else { |
||
1676 | $returnValue += (1 / $arg); |
||
1677 | } |
||
1678 | ++$aCount; |
||
1679 | } |
||
1680 | } |
||
1681 | |||
1682 | // Return |
||
1683 | if ($aCount > 0) { |
||
1684 | return 1 / ($returnValue / $aCount); |
||
1685 | } |
||
1686 | |||
1687 | return $returnValue; |
||
1688 | } |
||
1689 | |||
1690 | /** |
||
1691 | * HYPGEOMDIST. |
||
1692 | * |
||
1693 | * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of |
||
1694 | * sample successes, given the sample size, population successes, and population size. |
||
1695 | * |
||
1696 | * @param float $sampleSuccesses Number of successes in the sample |
||
1697 | * @param float $sampleNumber Size of the sample |
||
1698 | * @param float $populationSuccesses Number of successes in the population |
||
1699 | * @param float $populationNumber Population size |
||
1700 | * |
||
1701 | * @return float |
||
1702 | */ |
||
1703 | public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) |
||
1704 | { |
||
1705 | $sampleSuccesses = floor(Functions::flattenSingleValue($sampleSuccesses)); |
||
1706 | $sampleNumber = floor(Functions::flattenSingleValue($sampleNumber)); |
||
1707 | $populationSuccesses = floor(Functions::flattenSingleValue($populationSuccesses)); |
||
1708 | $populationNumber = floor(Functions::flattenSingleValue($populationNumber)); |
||
1709 | |||
1710 | if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) { |
||
1711 | if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) { |
||
1712 | return Functions::NAN(); |
||
1713 | } |
||
1714 | if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) { |
||
1715 | return Functions::NAN(); |
||
1716 | } |
||
1717 | if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) { |
||
1718 | return Functions::NAN(); |
||
1719 | } |
||
1720 | |||
1721 | return MathTrig::COMBIN($populationSuccesses, $sampleSuccesses) * |
||
1722 | MathTrig::COMBIN($populationNumber - $populationSuccesses, $sampleNumber - $sampleSuccesses) / |
||
1723 | MathTrig::COMBIN($populationNumber, $sampleNumber); |
||
1724 | } |
||
1725 | |||
1726 | return Functions::VALUE(); |
||
1727 | } |
||
1728 | |||
1729 | /** |
||
1730 | * INTERCEPT. |
||
1731 | * |
||
1732 | * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. |
||
1733 | * |
||
1734 | * @param mixed[] $yValues Data Series Y |
||
1735 | * @param mixed[] $xValues Data Series X |
||
1736 | * |
||
1737 | * @return float |
||
1738 | */ |
||
1739 | public static function INTERCEPT($yValues, $xValues) |
||
1740 | { |
||
1741 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
1742 | return Functions::VALUE(); |
||
1743 | } |
||
1744 | $yValueCount = count($yValues); |
||
1745 | $xValueCount = count($xValues); |
||
1746 | |||
1747 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
1748 | return Functions::NA(); |
||
1749 | } elseif ($yValueCount == 1) { |
||
1750 | return Functions::DIV0(); |
||
1751 | } |
||
1752 | |||
1753 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
1754 | |||
1755 | return $bestFitLinear->getIntersect(); |
||
1756 | } |
||
1757 | |||
1758 | /** |
||
1759 | * KURT. |
||
1760 | * |
||
1761 | * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness |
||
1762 | * or flatness of a distribution compared with the normal distribution. Positive |
||
1763 | * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a |
||
1764 | * relatively flat distribution. |
||
1765 | * |
||
1766 | * @param array ...$args Data Series |
||
1767 | * |
||
1768 | * @return float |
||
1769 | */ |
||
1770 | public static function KURT(...$args) |
||
1771 | { |
||
1772 | $aArgs = Functions::flattenArrayIndexed($args); |
||
1773 | $mean = self::AVERAGE($aArgs); |
||
1774 | $stdDev = self::STDEV($aArgs); |
||
1775 | |||
1776 | if ($stdDev > 0) { |
||
1777 | $count = $summer = 0; |
||
1778 | // Loop through arguments |
||
1779 | foreach ($aArgs as $k => $arg) { |
||
1780 | if ((is_bool($arg)) && |
||
1781 | (!Functions::isMatrixValue($k))) { |
||
1782 | } else { |
||
1783 | // Is it a numeric value? |
||
1784 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
1785 | $summer += pow((($arg - $mean) / $stdDev), 4); |
||
1786 | ++$count; |
||
1787 | } |
||
1788 | } |
||
1789 | } |
||
1790 | |||
1791 | // Return |
||
1792 | if ($count > 3) { |
||
1793 | return $summer * ($count * ($count + 1) / (($count - 1) * ($count - 2) * ($count - 3))) - (3 * pow($count - 1, 2) / (($count - 2) * ($count - 3))); |
||
1794 | } |
||
1795 | } |
||
1796 | |||
1797 | return Functions::DIV0(); |
||
1798 | } |
||
1799 | |||
1800 | /** |
||
1801 | * LARGE. |
||
1802 | * |
||
1803 | * Returns the nth largest value in a data set. You can use this function to |
||
1804 | * select a value based on its relative standing. |
||
1805 | * |
||
1806 | * Excel Function: |
||
1807 | * LARGE(value1[,value2[, ...]],entry) |
||
1808 | * |
||
1809 | * @category Statistical Functions |
||
1810 | * |
||
1811 | * @param mixed $args Data values |
||
1812 | * @param int $entry Position (ordered from the largest) in the array or range of data to return |
||
1813 | * |
||
1814 | * @return float |
||
1815 | */ |
||
1816 | public static function LARGE(...$args) |
||
1817 | { |
||
1818 | $aArgs = Functions::flattenArray($args); |
||
1819 | |||
1820 | // Calculate |
||
1821 | $entry = floor(array_pop($aArgs)); |
||
1822 | |||
1823 | if ((is_numeric($entry)) && (!is_string($entry))) { |
||
1824 | $mArgs = []; |
||
1825 | foreach ($aArgs as $arg) { |
||
1826 | // Is it a numeric value? |
||
1827 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
1828 | $mArgs[] = $arg; |
||
1829 | } |
||
1830 | } |
||
1831 | $count = self::COUNT($mArgs); |
||
1832 | $entry = floor(--$entry); |
||
1833 | if (($entry < 0) || ($entry >= $count) || ($count == 0)) { |
||
1834 | return Functions::NAN(); |
||
1835 | } |
||
1836 | rsort($mArgs); |
||
1837 | |||
1838 | return $mArgs[$entry]; |
||
1839 | } |
||
1840 | |||
1841 | return Functions::VALUE(); |
||
1842 | } |
||
1843 | |||
1844 | /** |
||
1845 | * LINEST. |
||
1846 | * |
||
1847 | * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, |
||
1848 | * and then returns an array that describes the line. |
||
1849 | * |
||
1850 | * @param mixed[] $yValues Data Series Y |
||
1851 | * @param null|mixed[] $xValues Data Series X |
||
1852 | * @param bool $const a logical value specifying whether to force the intersect to equal 0 |
||
1853 | * @param bool $stats a logical value specifying whether to return additional regression statistics |
||
1854 | * |
||
1855 | * @return array |
||
1856 | */ |
||
1857 | public static function LINEST($yValues, $xValues = null, $const = true, $stats = false) |
||
1858 | { |
||
1859 | $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); |
||
1860 | $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats); |
||
1861 | if ($xValues === null) { |
||
1862 | $xValues = range(1, count(Functions::flattenArray($yValues))); |
||
1863 | } |
||
1864 | |||
1865 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
1866 | return Functions::VALUE(); |
||
1867 | } |
||
1868 | $yValueCount = count($yValues); |
||
1869 | $xValueCount = count($xValues); |
||
1870 | |||
1871 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
1872 | return Functions::NA(); |
||
1873 | } elseif ($yValueCount == 1) { |
||
1874 | return 0; |
||
1875 | } |
||
1876 | |||
1877 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const); |
||
1878 | if ($stats) { |
||
1879 | return [ |
||
1880 | [ |
||
1881 | $bestFitLinear->getSlope(), |
||
1882 | $bestFitLinear->getSlopeSE(), |
||
1883 | $bestFitLinear->getGoodnessOfFit(), |
||
1884 | $bestFitLinear->getF(), |
||
1885 | $bestFitLinear->getSSRegression(), |
||
1886 | ], |
||
1887 | [ |
||
1888 | $bestFitLinear->getIntersect(), |
||
1889 | $bestFitLinear->getIntersectSE(), |
||
1890 | $bestFitLinear->getStdevOfResiduals(), |
||
1891 | $bestFitLinear->getDFResiduals(), |
||
1892 | $bestFitLinear->getSSResiduals(), |
||
1893 | ], |
||
1894 | ]; |
||
1895 | } |
||
1896 | |||
1897 | return [ |
||
1898 | $bestFitLinear->getSlope(), |
||
1899 | $bestFitLinear->getIntersect(), |
||
1900 | ]; |
||
1901 | } |
||
1902 | |||
1903 | /** |
||
1904 | * LOGEST. |
||
1905 | * |
||
1906 | * Calculates an exponential curve that best fits the X and Y data series, |
||
1907 | * and then returns an array that describes the line. |
||
1908 | * |
||
1909 | * @param mixed[] $yValues Data Series Y |
||
1910 | * @param null|mixed[] $xValues Data Series X |
||
1911 | * @param bool $const a logical value specifying whether to force the intersect to equal 0 |
||
1912 | * @param bool $stats a logical value specifying whether to return additional regression statistics |
||
1913 | * |
||
1914 | * @return array |
||
1915 | */ |
||
1916 | public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false) |
||
1917 | { |
||
1918 | $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); |
||
1919 | $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats); |
||
1920 | if ($xValues === null) { |
||
1921 | $xValues = range(1, count(Functions::flattenArray($yValues))); |
||
1922 | } |
||
1923 | |||
1924 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
1925 | return Functions::VALUE(); |
||
1926 | } |
||
1927 | $yValueCount = count($yValues); |
||
1928 | $xValueCount = count($xValues); |
||
1929 | |||
1930 | foreach ($yValues as $value) { |
||
1931 | if ($value <= 0.0) { |
||
1932 | return Functions::NAN(); |
||
1933 | } |
||
1934 | } |
||
1935 | |||
1936 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
1937 | return Functions::NA(); |
||
1938 | } elseif ($yValueCount == 1) { |
||
1939 | return 1; |
||
1940 | } |
||
1941 | |||
1942 | $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const); |
||
1943 | if ($stats) { |
||
1944 | return [ |
||
1945 | [ |
||
1946 | $bestFitExponential->getSlope(), |
||
1947 | $bestFitExponential->getSlopeSE(), |
||
1948 | $bestFitExponential->getGoodnessOfFit(), |
||
1949 | $bestFitExponential->getF(), |
||
1950 | $bestFitExponential->getSSRegression(), |
||
1951 | ], |
||
1952 | [ |
||
1953 | $bestFitExponential->getIntersect(), |
||
1954 | $bestFitExponential->getIntersectSE(), |
||
1955 | $bestFitExponential->getStdevOfResiduals(), |
||
1956 | $bestFitExponential->getDFResiduals(), |
||
1957 | $bestFitExponential->getSSResiduals(), |
||
1958 | ], |
||
1959 | ]; |
||
1960 | } |
||
1961 | |||
1962 | return [ |
||
1963 | $bestFitExponential->getSlope(), |
||
1964 | $bestFitExponential->getIntersect(), |
||
1965 | ]; |
||
1966 | } |
||
1967 | |||
1968 | /** |
||
1969 | * LOGINV. |
||
1970 | * |
||
1971 | * Returns the inverse of the normal cumulative distribution |
||
1972 | * |
||
1973 | * @param float $probability |
||
1974 | * @param float $mean |
||
1975 | * @param float $stdDev |
||
1976 | * |
||
1977 | * @return float |
||
1978 | * |
||
1979 | * @todo Try implementing P J Acklam's refinement algorithm for greater |
||
1980 | * accuracy if I can get my head round the mathematics |
||
1981 | * (as described at) http://home.online.no/~pjacklam/notes/invnorm/ |
||
1982 | */ |
||
1983 | public static function LOGINV($probability, $mean, $stdDev) |
||
1984 | { |
||
1985 | $probability = Functions::flattenSingleValue($probability); |
||
1986 | $mean = Functions::flattenSingleValue($mean); |
||
1987 | $stdDev = Functions::flattenSingleValue($stdDev); |
||
1988 | |||
1989 | if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { |
||
1990 | if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) { |
||
1991 | return Functions::NAN(); |
||
1992 | } |
||
1993 | |||
1994 | return exp($mean + $stdDev * self::NORMSINV($probability)); |
||
1995 | } |
||
1996 | |||
1997 | return Functions::VALUE(); |
||
1998 | } |
||
1999 | |||
2000 | /** |
||
2001 | * LOGNORMDIST. |
||
2002 | * |
||
2003 | * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed |
||
2004 | * with parameters mean and standard_dev. |
||
2005 | * |
||
2006 | * @param float $value |
||
2007 | * @param float $mean |
||
2008 | * @param float $stdDev |
||
2009 | * |
||
2010 | * @return float |
||
2011 | */ |
||
2012 | public static function LOGNORMDIST($value, $mean, $stdDev) |
||
2013 | { |
||
2014 | $value = Functions::flattenSingleValue($value); |
||
2015 | $mean = Functions::flattenSingleValue($mean); |
||
2016 | $stdDev = Functions::flattenSingleValue($stdDev); |
||
2017 | |||
2018 | if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { |
||
2019 | if (($value <= 0) || ($stdDev <= 0)) { |
||
2020 | return Functions::NAN(); |
||
2021 | } |
||
2022 | |||
2023 | return self::NORMSDIST((log($value) - $mean) / $stdDev); |
||
2024 | } |
||
2025 | |||
2026 | return Functions::VALUE(); |
||
2027 | } |
||
2028 | |||
2029 | /** |
||
2030 | * MAX. |
||
2031 | * |
||
2032 | * MAX returns the value of the element of the values passed that has the highest value, |
||
2033 | * with negative numbers considered smaller than positive numbers. |
||
2034 | * |
||
2035 | * Excel Function: |
||
2036 | * MAX(value1[,value2[, ...]]) |
||
2037 | * |
||
2038 | * @category Statistical Functions |
||
2039 | * |
||
2040 | * @param mixed ...$args Data values |
||
2041 | * |
||
2042 | * @return float |
||
2043 | */ |
||
2044 | 5 | public static function MAX(...$args) |
|
2045 | { |
||
2046 | 5 | $returnValue = null; |
|
2047 | |||
2048 | // Loop through arguments |
||
2049 | 5 | $aArgs = Functions::flattenArray($args); |
|
2050 | 5 | foreach ($aArgs as $arg) { |
|
2051 | // Is it a numeric value? |
||
2052 | 5 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
2053 | 5 | if (($returnValue === null) || ($arg > $returnValue)) { |
|
2054 | 5 | $returnValue = $arg; |
|
2055 | } |
||
2056 | } |
||
2057 | } |
||
2058 | |||
2059 | 5 | if ($returnValue === null) { |
|
2060 | return 0; |
||
2061 | } |
||
2062 | |||
2063 | 5 | return $returnValue; |
|
2064 | } |
||
2065 | |||
2066 | /** |
||
2067 | * MAXA. |
||
2068 | * |
||
2069 | * Returns the greatest value in a list of arguments, including numbers, text, and logical values |
||
2070 | * |
||
2071 | * Excel Function: |
||
2072 | * MAXA(value1[,value2[, ...]]) |
||
2073 | * |
||
2074 | * @category Statistical Functions |
||
2075 | * |
||
2076 | * @param mixed ...$args Data values |
||
2077 | * |
||
2078 | * @return float |
||
2079 | */ |
||
2080 | 1 | public static function MAXA(...$args) |
|
2081 | { |
||
2082 | 1 | $returnValue = null; |
|
2083 | |||
2084 | // Loop through arguments |
||
2085 | 1 | $aArgs = Functions::flattenArray($args); |
|
2086 | 1 | foreach ($aArgs as $arg) { |
|
2087 | // Is it a numeric value? |
||
2088 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { |
|
2089 | 1 | if (is_bool($arg)) { |
|
2090 | 1 | $arg = (int) $arg; |
|
2091 | 1 | } elseif (is_string($arg)) { |
|
2092 | 1 | $arg = 0; |
|
2093 | } |
||
2094 | 1 | if (($returnValue === null) || ($arg > $returnValue)) { |
|
2095 | 1 | $returnValue = $arg; |
|
2096 | } |
||
2097 | } |
||
2098 | } |
||
2099 | |||
2100 | 1 | if ($returnValue === null) { |
|
2101 | return 0; |
||
2102 | } |
||
2103 | |||
2104 | 1 | return $returnValue; |
|
2105 | } |
||
2106 | |||
2107 | /** |
||
2108 | * MAXIF. |
||
2109 | * |
||
2110 | * Counts the maximum value within a range of cells that contain numbers within the list of arguments |
||
2111 | * |
||
2112 | * Excel Function: |
||
2113 | * MAXIF(value1[,value2[, ...]],condition) |
||
2114 | * |
||
2115 | * @category Mathematical and Trigonometric Functions |
||
2116 | * |
||
2117 | * @param mixed $aArgs Data values |
||
2118 | * @param string $condition the criteria that defines which cells will be checked |
||
2119 | * @param mixed $sumArgs |
||
2120 | * |
||
2121 | * @return float |
||
2122 | */ |
||
2123 | public static function MAXIF($aArgs, $condition, $sumArgs = []) |
||
2124 | { |
||
2125 | $returnValue = null; |
||
2126 | |||
2127 | $aArgs = Functions::flattenArray($aArgs); |
||
2128 | $sumArgs = Functions::flattenArray($sumArgs); |
||
2129 | if (empty($sumArgs)) { |
||
2130 | $sumArgs = $aArgs; |
||
0 ignored issues
–
show
Unused Code
introduced
by
Loading history...
|
|||
2131 | } |
||
2132 | $condition = Functions::ifCondition($condition); |
||
2133 | // Loop through arguments |
||
2134 | foreach ($aArgs as $key => $arg) { |
||
2135 | if (!is_numeric($arg)) { |
||
2136 | $arg = Calculation::wrapResult(strtoupper($arg)); |
||
2137 | } |
||
2138 | $testCondition = '=' . $arg . $condition; |
||
2139 | if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) { |
||
2140 | if (($returnValue === null) || ($arg > $returnValue)) { |
||
2141 | $returnValue = $arg; |
||
2142 | } |
||
2143 | } |
||
2144 | } |
||
2145 | |||
2146 | return $returnValue; |
||
0 ignored issues
–
show
|
|||
2147 | } |
||
2148 | |||
2149 | /** |
||
2150 | * MEDIAN. |
||
2151 | * |
||
2152 | * Returns the median of the given numbers. The median is the number in the middle of a set of numbers. |
||
2153 | * |
||
2154 | * Excel Function: |
||
2155 | * MEDIAN(value1[,value2[, ...]]) |
||
2156 | * |
||
2157 | * @category Statistical Functions |
||
2158 | * |
||
2159 | * @param mixed ...$args Data values |
||
2160 | * |
||
2161 | * @return float |
||
2162 | */ |
||
2163 | 1 | public static function MEDIAN(...$args) |
|
2164 | { |
||
2165 | 1 | $returnValue = Functions::NAN(); |
|
2166 | |||
2167 | 1 | $mArgs = []; |
|
2168 | // Loop through arguments |
||
2169 | 1 | $aArgs = Functions::flattenArray($args); |
|
2170 | 1 | foreach ($aArgs as $arg) { |
|
2171 | // Is it a numeric value? |
||
2172 | 1 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
2173 | 1 | $mArgs[] = $arg; |
|
2174 | } |
||
2175 | } |
||
2176 | |||
2177 | 1 | $mValueCount = count($mArgs); |
|
2178 | 1 | if ($mValueCount > 0) { |
|
2179 | 1 | sort($mArgs, SORT_NUMERIC); |
|
2180 | 1 | $mValueCount = $mValueCount / 2; |
|
2181 | 1 | if ($mValueCount == floor($mValueCount)) { |
|
2182 | 1 | $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2; |
|
2183 | } else { |
||
2184 | 1 | $mValueCount = floor($mValueCount); |
|
2185 | 1 | $returnValue = $mArgs[$mValueCount]; |
|
2186 | } |
||
2187 | } |
||
2188 | |||
2189 | 1 | return $returnValue; |
|
2190 | } |
||
2191 | |||
2192 | /** |
||
2193 | * MIN. |
||
2194 | * |
||
2195 | * MIN returns the value of the element of the values passed that has the smallest value, |
||
2196 | * with negative numbers considered smaller than positive numbers. |
||
2197 | * |
||
2198 | * Excel Function: |
||
2199 | * MIN(value1[,value2[, ...]]) |
||
2200 | * |
||
2201 | * @category Statistical Functions |
||
2202 | * |
||
2203 | * @param mixed ...$args Data values |
||
2204 | * |
||
2205 | * @return float |
||
2206 | */ |
||
2207 | 5 | public static function MIN(...$args) |
|
2208 | { |
||
2209 | 5 | $returnValue = null; |
|
2210 | |||
2211 | // Loop through arguments |
||
2212 | 5 | $aArgs = Functions::flattenArray($args); |
|
2213 | 5 | foreach ($aArgs as $arg) { |
|
2214 | // Is it a numeric value? |
||
2215 | 5 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
2216 | 5 | if (($returnValue === null) || ($arg < $returnValue)) { |
|
2217 | 5 | $returnValue = $arg; |
|
2218 | } |
||
2219 | } |
||
2220 | } |
||
2221 | |||
2222 | 5 | if ($returnValue === null) { |
|
2223 | return 0; |
||
2224 | } |
||
2225 | |||
2226 | 5 | return $returnValue; |
|
2227 | } |
||
2228 | |||
2229 | /** |
||
2230 | * MINA. |
||
2231 | * |
||
2232 | * Returns the smallest value in a list of arguments, including numbers, text, and logical values |
||
2233 | * |
||
2234 | * Excel Function: |
||
2235 | * MINA(value1[,value2[, ...]]) |
||
2236 | * |
||
2237 | * @category Statistical Functions |
||
2238 | * |
||
2239 | * @param mixed ...$args Data values |
||
2240 | * |
||
2241 | * @return float |
||
2242 | */ |
||
2243 | 1 | public static function MINA(...$args) |
|
2244 | { |
||
2245 | 1 | $returnValue = null; |
|
2246 | |||
2247 | // Loop through arguments |
||
2248 | 1 | $aArgs = Functions::flattenArray($args); |
|
2249 | 1 | foreach ($aArgs as $arg) { |
|
2250 | // Is it a numeric value? |
||
2251 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) { |
|
2252 | 1 | if (is_bool($arg)) { |
|
2253 | 1 | $arg = (int) $arg; |
|
2254 | 1 | } elseif (is_string($arg)) { |
|
2255 | 1 | $arg = 0; |
|
2256 | } |
||
2257 | 1 | if (($returnValue === null) || ($arg < $returnValue)) { |
|
2258 | 1 | $returnValue = $arg; |
|
2259 | } |
||
2260 | } |
||
2261 | } |
||
2262 | |||
2263 | 1 | if ($returnValue === null) { |
|
2264 | return 0; |
||
2265 | } |
||
2266 | |||
2267 | 1 | return $returnValue; |
|
2268 | } |
||
2269 | |||
2270 | /** |
||
2271 | * MINIF. |
||
2272 | * |
||
2273 | * Returns the minimum value within a range of cells that contain numbers within the list of arguments |
||
2274 | * |
||
2275 | * Excel Function: |
||
2276 | * MINIF(value1[,value2[, ...]],condition) |
||
2277 | * |
||
2278 | * @category Mathematical and Trigonometric Functions |
||
2279 | * |
||
2280 | * @param mixed $aArgs Data values |
||
2281 | * @param string $condition the criteria that defines which cells will be checked |
||
2282 | * @param mixed $sumArgs |
||
2283 | * |
||
2284 | * @return float |
||
2285 | */ |
||
2286 | public static function MINIF($aArgs, $condition, $sumArgs = []) |
||
2287 | { |
||
2288 | $returnValue = null; |
||
2289 | |||
2290 | $aArgs = Functions::flattenArray($aArgs); |
||
2291 | $sumArgs = Functions::flattenArray($sumArgs); |
||
2292 | if (empty($sumArgs)) { |
||
2293 | $sumArgs = $aArgs; |
||
0 ignored issues
–
show
|
|||
2294 | } |
||
2295 | $condition = Functions::ifCondition($condition); |
||
2296 | // Loop through arguments |
||
2297 | foreach ($aArgs as $key => $arg) { |
||
2298 | if (!is_numeric($arg)) { |
||
2299 | $arg = Calculation::wrapResult(strtoupper($arg)); |
||
2300 | } |
||
2301 | $testCondition = '=' . $arg . $condition; |
||
2302 | if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) { |
||
2303 | if (($returnValue === null) || ($arg < $returnValue)) { |
||
2304 | $returnValue = $arg; |
||
2305 | } |
||
2306 | } |
||
2307 | } |
||
2308 | |||
2309 | return $returnValue; |
||
0 ignored issues
–
show
|
|||
2310 | } |
||
2311 | |||
2312 | // |
||
2313 | // Special variant of array_count_values that isn't limited to strings and integers, |
||
2314 | // but can work with floating point numbers as values |
||
2315 | // |
||
2316 | 1 | private static function modeCalc($data) |
|
2317 | { |
||
2318 | 1 | $frequencyArray = []; |
|
2319 | 1 | foreach ($data as $datum) { |
|
2320 | 1 | $found = false; |
|
2321 | 1 | foreach ($frequencyArray as $key => $value) { |
|
2322 | 1 | if ((string) $value['value'] == (string) $datum) { |
|
2323 | 1 | ++$frequencyArray[$key]['frequency']; |
|
2324 | 1 | $found = true; |
|
2325 | |||
2326 | 1 | break; |
|
2327 | } |
||
2328 | } |
||
2329 | 1 | if (!$found) { |
|
2330 | 1 | $frequencyArray[] = [ |
|
2331 | 1 | 'value' => $datum, |
|
2332 | 1 | 'frequency' => 1, |
|
2333 | ]; |
||
2334 | } |
||
2335 | } |
||
2336 | |||
2337 | 1 | foreach ($frequencyArray as $key => $value) { |
|
2338 | 1 | $frequencyList[$key] = $value['frequency']; |
|
2339 | 1 | $valueList[$key] = $value['value']; |
|
2340 | } |
||
2341 | 1 | array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray); |
|
2342 | |||
2343 | 1 | if ($frequencyArray[0]['frequency'] == 1) { |
|
2344 | 1 | return Functions::NA(); |
|
2345 | } |
||
2346 | |||
2347 | 1 | return $frequencyArray[0]['value']; |
|
2348 | } |
||
2349 | |||
2350 | /** |
||
2351 | * MODE. |
||
2352 | * |
||
2353 | * Returns the most frequently occurring, or repetitive, value in an array or range of data |
||
2354 | * |
||
2355 | * Excel Function: |
||
2356 | * MODE(value1[,value2[, ...]]) |
||
2357 | * |
||
2358 | * @category Statistical Functions |
||
2359 | * |
||
2360 | * @param mixed ...$args Data values |
||
2361 | * |
||
2362 | * @return float |
||
2363 | */ |
||
2364 | 1 | public static function MODE(...$args) |
|
2365 | { |
||
2366 | 1 | $returnValue = Functions::NA(); |
|
2367 | |||
2368 | // Loop through arguments |
||
2369 | 1 | $aArgs = Functions::flattenArray($args); |
|
2370 | |||
2371 | 1 | $mArgs = []; |
|
2372 | 1 | foreach ($aArgs as $arg) { |
|
2373 | // Is it a numeric value? |
||
2374 | 1 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
2375 | 1 | $mArgs[] = $arg; |
|
2376 | } |
||
2377 | } |
||
2378 | |||
2379 | 1 | if (!empty($mArgs)) { |
|
2380 | 1 | return self::modeCalc($mArgs); |
|
2381 | } |
||
2382 | |||
2383 | return $returnValue; |
||
2384 | } |
||
2385 | |||
2386 | /** |
||
2387 | * NEGBINOMDIST. |
||
2388 | * |
||
2389 | * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that |
||
2390 | * there will be number_f failures before the number_s-th success, when the constant |
||
2391 | * probability of a success is probability_s. This function is similar to the binomial |
||
2392 | * distribution, except that the number of successes is fixed, and the number of trials is |
||
2393 | * variable. Like the binomial, trials are assumed to be independent. |
||
2394 | * |
||
2395 | * @param float $failures Number of Failures |
||
2396 | * @param float $successes Threshold number of Successes |
||
2397 | * @param float $probability Probability of success on each trial |
||
2398 | * |
||
2399 | * @return float |
||
2400 | */ |
||
2401 | public static function NEGBINOMDIST($failures, $successes, $probability) |
||
2402 | { |
||
2403 | $failures = floor(Functions::flattenSingleValue($failures)); |
||
2404 | $successes = floor(Functions::flattenSingleValue($successes)); |
||
2405 | $probability = Functions::flattenSingleValue($probability); |
||
2406 | |||
2407 | if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) { |
||
2408 | if (($failures < 0) || ($successes < 1)) { |
||
2409 | return Functions::NAN(); |
||
2410 | } elseif (($probability < 0) || ($probability > 1)) { |
||
2411 | return Functions::NAN(); |
||
2412 | } |
||
2413 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { |
||
2414 | if (($failures + $successes - 1) <= 0) { |
||
2415 | return Functions::NAN(); |
||
2416 | } |
||
2417 | } |
||
2418 | |||
2419 | return (MathTrig::COMBIN($failures + $successes - 1, $successes - 1)) * (pow($probability, $successes)) * (pow(1 - $probability, $failures)); |
||
2420 | } |
||
2421 | |||
2422 | return Functions::VALUE(); |
||
2423 | } |
||
2424 | |||
2425 | /** |
||
2426 | * NORMDIST. |
||
2427 | * |
||
2428 | * Returns the normal distribution for the specified mean and standard deviation. This |
||
2429 | * function has a very wide range of applications in statistics, including hypothesis |
||
2430 | * testing. |
||
2431 | * |
||
2432 | * @param float $value |
||
2433 | * @param float $mean Mean Value |
||
2434 | * @param float $stdDev Standard Deviation |
||
2435 | * @param bool $cumulative |
||
2436 | * |
||
2437 | * @return float |
||
2438 | */ |
||
2439 | public static function NORMDIST($value, $mean, $stdDev, $cumulative) |
||
2440 | { |
||
2441 | $value = Functions::flattenSingleValue($value); |
||
2442 | $mean = Functions::flattenSingleValue($mean); |
||
2443 | $stdDev = Functions::flattenSingleValue($stdDev); |
||
2444 | |||
2445 | if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { |
||
2446 | if ($stdDev < 0) { |
||
2447 | return Functions::NAN(); |
||
2448 | } |
||
2449 | if ((is_numeric($cumulative)) || (is_bool($cumulative))) { |
||
2450 | if ($cumulative) { |
||
2451 | return 0.5 * (1 + Engineering::erfVal(($value - $mean) / ($stdDev * sqrt(2)))); |
||
2452 | } |
||
2453 | |||
2454 | return (1 / (self::SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean, 2) / (2 * ($stdDev * $stdDev)))); |
||
2455 | } |
||
2456 | } |
||
2457 | |||
2458 | return Functions::VALUE(); |
||
2459 | } |
||
2460 | |||
2461 | /** |
||
2462 | * NORMINV. |
||
2463 | * |
||
2464 | * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. |
||
2465 | * |
||
2466 | * @param float $probability |
||
2467 | * @param float $mean Mean Value |
||
2468 | * @param float $stdDev Standard Deviation |
||
2469 | * |
||
2470 | * @return float |
||
2471 | */ |
||
2472 | public static function NORMINV($probability, $mean, $stdDev) |
||
2473 | { |
||
2474 | $probability = Functions::flattenSingleValue($probability); |
||
2475 | $mean = Functions::flattenSingleValue($mean); |
||
2476 | $stdDev = Functions::flattenSingleValue($stdDev); |
||
2477 | |||
2478 | if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) { |
||
2479 | if (($probability < 0) || ($probability > 1)) { |
||
2480 | return Functions::NAN(); |
||
2481 | } |
||
2482 | if ($stdDev < 0) { |
||
2483 | return Functions::NAN(); |
||
2484 | } |
||
2485 | |||
2486 | return (self::inverseNcdf($probability) * $stdDev) + $mean; |
||
2487 | } |
||
2488 | |||
2489 | return Functions::VALUE(); |
||
2490 | } |
||
2491 | |||
2492 | /** |
||
2493 | * NORMSDIST. |
||
2494 | * |
||
2495 | * Returns the standard normal cumulative distribution function. The distribution has |
||
2496 | * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a |
||
2497 | * table of standard normal curve areas. |
||
2498 | * |
||
2499 | * @param float $value |
||
2500 | * |
||
2501 | * @return float |
||
2502 | */ |
||
2503 | public static function NORMSDIST($value) |
||
2504 | { |
||
2505 | $value = Functions::flattenSingleValue($value); |
||
2506 | |||
2507 | return self::NORMDIST($value, 0, 1, true); |
||
2508 | } |
||
2509 | |||
2510 | /** |
||
2511 | * NORMSINV. |
||
2512 | * |
||
2513 | * Returns the inverse of the standard normal cumulative distribution |
||
2514 | * |
||
2515 | * @param float $value |
||
2516 | * |
||
2517 | * @return float |
||
2518 | */ |
||
2519 | public static function NORMSINV($value) |
||
2520 | { |
||
2521 | return self::NORMINV($value, 0, 1); |
||
2522 | } |
||
2523 | |||
2524 | /** |
||
2525 | * PERCENTILE. |
||
2526 | * |
||
2527 | * Returns the nth percentile of values in a range.. |
||
2528 | * |
||
2529 | * Excel Function: |
||
2530 | * PERCENTILE(value1[,value2[, ...]],entry) |
||
2531 | * |
||
2532 | * @category Statistical Functions |
||
2533 | * |
||
2534 | * @param mixed $args Data values |
||
2535 | * @param float $entry Percentile value in the range 0..1, inclusive. |
||
2536 | * |
||
2537 | * @return float |
||
2538 | */ |
||
2539 | public static function PERCENTILE(...$args) |
||
2540 | { |
||
2541 | $aArgs = Functions::flattenArray($args); |
||
2542 | |||
2543 | // Calculate |
||
2544 | $entry = array_pop($aArgs); |
||
2545 | |||
2546 | if ((is_numeric($entry)) && (!is_string($entry))) { |
||
2547 | if (($entry < 0) || ($entry > 1)) { |
||
2548 | return Functions::NAN(); |
||
2549 | } |
||
2550 | $mArgs = []; |
||
2551 | foreach ($aArgs as $arg) { |
||
2552 | // Is it a numeric value? |
||
2553 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
2554 | $mArgs[] = $arg; |
||
2555 | } |
||
2556 | } |
||
2557 | $mValueCount = count($mArgs); |
||
2558 | if ($mValueCount > 0) { |
||
2559 | sort($mArgs); |
||
2560 | $count = self::COUNT($mArgs); |
||
2561 | $index = $entry * ($count - 1); |
||
2562 | $iBase = floor($index); |
||
2563 | if ($index == $iBase) { |
||
2564 | return $mArgs[$index]; |
||
2565 | } |
||
2566 | $iNext = $iBase + 1; |
||
2567 | $iProportion = $index - $iBase; |
||
2568 | |||
2569 | return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion); |
||
2570 | } |
||
2571 | } |
||
2572 | |||
2573 | return Functions::VALUE(); |
||
2574 | } |
||
2575 | |||
2576 | /** |
||
2577 | * PERCENTRANK. |
||
2578 | * |
||
2579 | * Returns the rank of a value in a data set as a percentage of the data set. |
||
2580 | * |
||
2581 | * @param float[] $valueSet An array of, or a reference to, a list of numbers |
||
2582 | * @param int $value the number whose rank you want to find |
||
2583 | * @param int $significance the number of significant digits for the returned percentage value |
||
2584 | * |
||
2585 | * @return float |
||
2586 | */ |
||
2587 | public static function PERCENTRANK($valueSet, $value, $significance = 3) |
||
2588 | { |
||
2589 | $valueSet = Functions::flattenArray($valueSet); |
||
2590 | $value = Functions::flattenSingleValue($value); |
||
2591 | $significance = ($significance === null) ? 3 : (int) Functions::flattenSingleValue($significance); |
||
2592 | |||
2593 | foreach ($valueSet as $key => $valueEntry) { |
||
2594 | if (!is_numeric($valueEntry)) { |
||
2595 | unset($valueSet[$key]); |
||
2596 | } |
||
2597 | } |
||
2598 | sort($valueSet, SORT_NUMERIC); |
||
2599 | $valueCount = count($valueSet); |
||
2600 | if ($valueCount == 0) { |
||
2601 | return Functions::NAN(); |
||
2602 | } |
||
2603 | |||
2604 | $valueAdjustor = $valueCount - 1; |
||
2605 | if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) { |
||
2606 | return Functions::NA(); |
||
2607 | } |
||
2608 | |||
2609 | $pos = array_search($value, $valueSet); |
||
2610 | if ($pos === false) { |
||
2611 | $pos = 0; |
||
2612 | $testValue = $valueSet[0]; |
||
2613 | while ($testValue < $value) { |
||
2614 | $testValue = $valueSet[++$pos]; |
||
2615 | } |
||
2616 | --$pos; |
||
2617 | $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos])); |
||
2618 | } |
||
2619 | |||
2620 | return round($pos / $valueAdjustor, $significance); |
||
2621 | } |
||
2622 | |||
2623 | /** |
||
2624 | * PERMUT. |
||
2625 | * |
||
2626 | * Returns the number of permutations for a given number of objects that can be |
||
2627 | * selected from number objects. A permutation is any set or subset of objects or |
||
2628 | * events where internal order is significant. Permutations are different from |
||
2629 | * combinations, for which the internal order is not significant. Use this function |
||
2630 | * for lottery-style probability calculations. |
||
2631 | * |
||
2632 | * @param int $numObjs Number of different objects |
||
2633 | * @param int $numInSet Number of objects in each permutation |
||
2634 | * |
||
2635 | * @return int Number of permutations |
||
2636 | */ |
||
2637 | public static function PERMUT($numObjs, $numInSet) |
||
2638 | { |
||
2639 | $numObjs = Functions::flattenSingleValue($numObjs); |
||
2640 | $numInSet = Functions::flattenSingleValue($numInSet); |
||
2641 | |||
2642 | if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { |
||
2643 | $numInSet = floor($numInSet); |
||
2644 | if ($numObjs < $numInSet) { |
||
2645 | return Functions::NAN(); |
||
2646 | } |
||
2647 | |||
2648 | return round(MathTrig::FACT($numObjs) / MathTrig::FACT($numObjs - $numInSet)); |
||
2649 | } |
||
2650 | |||
2651 | return Functions::VALUE(); |
||
2652 | } |
||
2653 | |||
2654 | /** |
||
2655 | * POISSON. |
||
2656 | * |
||
2657 | * Returns the Poisson distribution. A common application of the Poisson distribution |
||
2658 | * is predicting the number of events over a specific time, such as the number of |
||
2659 | * cars arriving at a toll plaza in 1 minute. |
||
2660 | * |
||
2661 | * @param float $value |
||
2662 | * @param float $mean Mean Value |
||
2663 | * @param bool $cumulative |
||
2664 | * |
||
2665 | * @return float |
||
2666 | */ |
||
2667 | public static function POISSON($value, $mean, $cumulative) |
||
2668 | { |
||
2669 | $value = Functions::flattenSingleValue($value); |
||
2670 | $mean = Functions::flattenSingleValue($mean); |
||
2671 | |||
2672 | if ((is_numeric($value)) && (is_numeric($mean))) { |
||
2673 | if (($value < 0) || ($mean <= 0)) { |
||
2674 | return Functions::NAN(); |
||
2675 | } |
||
2676 | if ((is_numeric($cumulative)) || (is_bool($cumulative))) { |
||
2677 | if ($cumulative) { |
||
2678 | $summer = 0; |
||
2679 | $floor = floor($value); |
||
2680 | for ($i = 0; $i <= $floor; ++$i) { |
||
2681 | $summer += pow($mean, $i) / MathTrig::FACT($i); |
||
2682 | } |
||
2683 | |||
2684 | return exp(0 - $mean) * $summer; |
||
2685 | } |
||
2686 | |||
2687 | return (exp(0 - $mean) * pow($mean, $value)) / MathTrig::FACT($value); |
||
2688 | } |
||
2689 | } |
||
2690 | |||
2691 | return Functions::VALUE(); |
||
2692 | } |
||
2693 | |||
2694 | /** |
||
2695 | * QUARTILE. |
||
2696 | * |
||
2697 | * Returns the quartile of a data set. |
||
2698 | * |
||
2699 | * Excel Function: |
||
2700 | * QUARTILE(value1[,value2[, ...]],entry) |
||
2701 | * |
||
2702 | * @category Statistical Functions |
||
2703 | * |
||
2704 | * @param mixed $args Data values |
||
2705 | * @param int $entry Quartile value in the range 1..3, inclusive. |
||
2706 | * |
||
2707 | * @return float |
||
2708 | */ |
||
2709 | public static function QUARTILE(...$args) |
||
2710 | { |
||
2711 | $aArgs = Functions::flattenArray($args); |
||
2712 | |||
2713 | // Calculate |
||
2714 | $entry = floor(array_pop($aArgs)); |
||
2715 | |||
2716 | if ((is_numeric($entry)) && (!is_string($entry))) { |
||
2717 | $entry /= 4; |
||
2718 | if (($entry < 0) || ($entry > 1)) { |
||
2719 | return Functions::NAN(); |
||
2720 | } |
||
2721 | |||
2722 | return self::PERCENTILE($aArgs, $entry); |
||
2723 | } |
||
2724 | |||
2725 | return Functions::VALUE(); |
||
2726 | } |
||
2727 | |||
2728 | /** |
||
2729 | * RANK. |
||
2730 | * |
||
2731 | * Returns the rank of a number in a list of numbers. |
||
2732 | * |
||
2733 | * @param int $value the number whose rank you want to find |
||
2734 | * @param float[] $valueSet An array of, or a reference to, a list of numbers |
||
2735 | * @param int $order Order to sort the values in the value set |
||
2736 | * |
||
2737 | * @return float |
||
2738 | */ |
||
2739 | public static function RANK($value, $valueSet, $order = 0) |
||
2740 | { |
||
2741 | $value = Functions::flattenSingleValue($value); |
||
2742 | $valueSet = Functions::flattenArray($valueSet); |
||
2743 | $order = ($order === null) ? 0 : (int) Functions::flattenSingleValue($order); |
||
2744 | |||
2745 | foreach ($valueSet as $key => $valueEntry) { |
||
2746 | if (!is_numeric($valueEntry)) { |
||
2747 | unset($valueSet[$key]); |
||
2748 | } |
||
2749 | } |
||
2750 | |||
2751 | if ($order == 0) { |
||
2752 | rsort($valueSet, SORT_NUMERIC); |
||
2753 | } else { |
||
2754 | sort($valueSet, SORT_NUMERIC); |
||
2755 | } |
||
2756 | $pos = array_search($value, $valueSet); |
||
2757 | if ($pos === false) { |
||
2758 | return Functions::NA(); |
||
2759 | } |
||
2760 | |||
2761 | return ++$pos; |
||
2762 | } |
||
2763 | |||
2764 | /** |
||
2765 | * RSQ. |
||
2766 | * |
||
2767 | * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. |
||
2768 | * |
||
2769 | * @param mixed[] $yValues Data Series Y |
||
2770 | * @param mixed[] $xValues Data Series X |
||
2771 | * |
||
2772 | * @return float |
||
2773 | */ |
||
2774 | public static function RSQ($yValues, $xValues) |
||
2775 | { |
||
2776 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
2777 | return Functions::VALUE(); |
||
2778 | } |
||
2779 | $yValueCount = count($yValues); |
||
2780 | $xValueCount = count($xValues); |
||
2781 | |||
2782 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
2783 | return Functions::NA(); |
||
2784 | } elseif ($yValueCount == 1) { |
||
2785 | return Functions::DIV0(); |
||
2786 | } |
||
2787 | |||
2788 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
2789 | |||
2790 | return $bestFitLinear->getGoodnessOfFit(); |
||
2791 | } |
||
2792 | |||
2793 | /** |
||
2794 | * SKEW. |
||
2795 | * |
||
2796 | * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry |
||
2797 | * of a distribution around its mean. Positive skewness indicates a distribution with an |
||
2798 | * asymmetric tail extending toward more positive values. Negative skewness indicates a |
||
2799 | * distribution with an asymmetric tail extending toward more negative values. |
||
2800 | * |
||
2801 | * @param array ...$args Data Series |
||
2802 | * |
||
2803 | * @return float |
||
2804 | */ |
||
2805 | public static function SKEW(...$args) |
||
2806 | { |
||
2807 | $aArgs = Functions::flattenArrayIndexed($args); |
||
2808 | $mean = self::AVERAGE($aArgs); |
||
2809 | $stdDev = self::STDEV($aArgs); |
||
2810 | |||
2811 | $count = $summer = 0; |
||
2812 | // Loop through arguments |
||
2813 | foreach ($aArgs as $k => $arg) { |
||
2814 | if ((is_bool($arg)) && |
||
2815 | (!Functions::isMatrixValue($k))) { |
||
2816 | } else { |
||
2817 | // Is it a numeric value? |
||
2818 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
2819 | $summer += pow((($arg - $mean) / $stdDev), 3); |
||
2820 | ++$count; |
||
2821 | } |
||
2822 | } |
||
2823 | } |
||
2824 | |||
2825 | if ($count > 2) { |
||
2826 | return $summer * ($count / (($count - 1) * ($count - 2))); |
||
2827 | } |
||
2828 | |||
2829 | return Functions::DIV0(); |
||
2830 | } |
||
2831 | |||
2832 | /** |
||
2833 | * SLOPE. |
||
2834 | * |
||
2835 | * Returns the slope of the linear regression line through data points in known_y's and known_x's. |
||
2836 | * |
||
2837 | * @param mixed[] $yValues Data Series Y |
||
2838 | * @param mixed[] $xValues Data Series X |
||
2839 | * |
||
2840 | * @return float |
||
2841 | */ |
||
2842 | public static function SLOPE($yValues, $xValues) |
||
2843 | { |
||
2844 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
2845 | return Functions::VALUE(); |
||
2846 | } |
||
2847 | $yValueCount = count($yValues); |
||
2848 | $xValueCount = count($xValues); |
||
2849 | |||
2850 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
2851 | return Functions::NA(); |
||
2852 | } elseif ($yValueCount == 1) { |
||
2853 | return Functions::DIV0(); |
||
2854 | } |
||
2855 | |||
2856 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
2857 | |||
2858 | return $bestFitLinear->getSlope(); |
||
2859 | } |
||
2860 | |||
2861 | /** |
||
2862 | * SMALL. |
||
2863 | * |
||
2864 | * Returns the nth smallest value in a data set. You can use this function to |
||
2865 | * select a value based on its relative standing. |
||
2866 | * |
||
2867 | * Excel Function: |
||
2868 | * SMALL(value1[,value2[, ...]],entry) |
||
2869 | * |
||
2870 | * @category Statistical Functions |
||
2871 | * |
||
2872 | * @param mixed $args Data values |
||
2873 | * @param int $entry Position (ordered from the smallest) in the array or range of data to return |
||
2874 | * |
||
2875 | * @return float |
||
2876 | */ |
||
2877 | public static function SMALL(...$args) |
||
2878 | { |
||
2879 | $aArgs = Functions::flattenArray($args); |
||
2880 | |||
2881 | // Calculate |
||
2882 | $entry = array_pop($aArgs); |
||
2883 | |||
2884 | if ((is_numeric($entry)) && (!is_string($entry))) { |
||
2885 | $mArgs = []; |
||
2886 | foreach ($aArgs as $arg) { |
||
2887 | // Is it a numeric value? |
||
2888 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
2889 | $mArgs[] = $arg; |
||
2890 | } |
||
2891 | } |
||
2892 | $count = self::COUNT($mArgs); |
||
2893 | $entry = floor(--$entry); |
||
2894 | if (($entry < 0) || ($entry >= $count) || ($count == 0)) { |
||
2895 | return Functions::NAN(); |
||
2896 | } |
||
2897 | sort($mArgs); |
||
2898 | |||
2899 | return $mArgs[$entry]; |
||
2900 | } |
||
2901 | |||
2902 | return Functions::VALUE(); |
||
2903 | } |
||
2904 | |||
2905 | /** |
||
2906 | * STANDARDIZE. |
||
2907 | * |
||
2908 | * Returns a normalized value from a distribution characterized by mean and standard_dev. |
||
2909 | * |
||
2910 | * @param float $value Value to normalize |
||
2911 | * @param float $mean Mean Value |
||
2912 | * @param float $stdDev Standard Deviation |
||
2913 | * |
||
2914 | * @return float Standardized value |
||
2915 | */ |
||
2916 | public static function STANDARDIZE($value, $mean, $stdDev) |
||
2917 | { |
||
2918 | $value = Functions::flattenSingleValue($value); |
||
2919 | $mean = Functions::flattenSingleValue($mean); |
||
2920 | $stdDev = Functions::flattenSingleValue($stdDev); |
||
2921 | |||
2922 | if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) { |
||
2923 | if ($stdDev <= 0) { |
||
2924 | return Functions::NAN(); |
||
2925 | } |
||
2926 | |||
2927 | return ($value - $mean) / $stdDev; |
||
2928 | } |
||
2929 | |||
2930 | return Functions::VALUE(); |
||
2931 | } |
||
2932 | |||
2933 | /** |
||
2934 | * STDEV. |
||
2935 | * |
||
2936 | * Estimates standard deviation based on a sample. The standard deviation is a measure of how |
||
2937 | * widely values are dispersed from the average value (the mean). |
||
2938 | * |
||
2939 | * Excel Function: |
||
2940 | * STDEV(value1[,value2[, ...]]) |
||
2941 | * |
||
2942 | * @category Statistical Functions |
||
2943 | * |
||
2944 | * @param mixed ...$args Data values |
||
2945 | * |
||
2946 | * @return float |
||
2947 | */ |
||
2948 | 4 | public static function STDEV(...$args) |
|
2949 | { |
||
2950 | 4 | $aArgs = Functions::flattenArrayIndexed($args); |
|
2951 | |||
2952 | // Return value |
||
2953 | 4 | $returnValue = null; |
|
2954 | |||
2955 | 4 | $aMean = self::AVERAGE($aArgs); |
|
2956 | 4 | if ($aMean !== null) { |
|
2957 | 4 | $aCount = -1; |
|
2958 | 4 | foreach ($aArgs as $k => $arg) { |
|
2959 | 4 | if ((is_bool($arg)) && |
|
2960 | 4 | ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))) { |
|
2961 | $arg = (int) $arg; |
||
2962 | } |
||
2963 | // Is it a numeric value? |
||
2964 | 4 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
2965 | 4 | if ($returnValue === null) { |
|
2966 | 4 | $returnValue = pow(($arg - $aMean), 2); |
|
2967 | } else { |
||
2968 | 4 | $returnValue += pow(($arg - $aMean), 2); |
|
2969 | } |
||
2970 | 4 | ++$aCount; |
|
2971 | } |
||
2972 | } |
||
2973 | |||
2974 | // Return |
||
2975 | 4 | if (($aCount > 0) && ($returnValue >= 0)) { |
|
2976 | 4 | return sqrt($returnValue / $aCount); |
|
2977 | } |
||
2978 | } |
||
2979 | |||
2980 | return Functions::DIV0(); |
||
2981 | } |
||
2982 | |||
2983 | /** |
||
2984 | * STDEVA. |
||
2985 | * |
||
2986 | * Estimates standard deviation based on a sample, including numbers, text, and logical values |
||
2987 | * |
||
2988 | * Excel Function: |
||
2989 | * STDEVA(value1[,value2[, ...]]) |
||
2990 | * |
||
2991 | * @category Statistical Functions |
||
2992 | * |
||
2993 | * @param mixed ...$args Data values |
||
2994 | * |
||
2995 | * @return float |
||
2996 | */ |
||
2997 | 1 | public static function STDEVA(...$args) |
|
2998 | { |
||
2999 | 1 | $aArgs = Functions::flattenArrayIndexed($args); |
|
3000 | |||
3001 | 1 | $returnValue = null; |
|
3002 | |||
3003 | 1 | $aMean = self::AVERAGEA($aArgs); |
|
3004 | 1 | if ($aMean !== null) { |
|
3005 | 1 | $aCount = -1; |
|
3006 | 1 | foreach ($aArgs as $k => $arg) { |
|
3007 | 1 | if ((is_bool($arg)) && |
|
3008 | 1 | (!Functions::isMatrixValue($k))) { |
|
3009 | } else { |
||
3010 | // Is it a numeric value? |
||
3011 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { |
|
3012 | 1 | if (is_bool($arg)) { |
|
3013 | 1 | $arg = (int) $arg; |
|
3014 | 1 | } elseif (is_string($arg)) { |
|
3015 | 1 | $arg = 0; |
|
3016 | } |
||
3017 | 1 | if ($returnValue === null) { |
|
3018 | 1 | $returnValue = pow(($arg - $aMean), 2); |
|
3019 | } else { |
||
3020 | 1 | $returnValue += pow(($arg - $aMean), 2); |
|
3021 | } |
||
3022 | 1 | ++$aCount; |
|
3023 | } |
||
3024 | } |
||
3025 | } |
||
3026 | |||
3027 | 1 | if (($aCount > 0) && ($returnValue >= 0)) { |
|
3028 | 1 | return sqrt($returnValue / $aCount); |
|
3029 | } |
||
3030 | } |
||
3031 | |||
3032 | return Functions::DIV0(); |
||
3033 | } |
||
3034 | |||
3035 | /** |
||
3036 | * STDEVP. |
||
3037 | * |
||
3038 | * Calculates standard deviation based on the entire population |
||
3039 | * |
||
3040 | * Excel Function: |
||
3041 | * STDEVP(value1[,value2[, ...]]) |
||
3042 | * |
||
3043 | * @category Statistical Functions |
||
3044 | * |
||
3045 | * @param mixed ...$args Data values |
||
3046 | * |
||
3047 | * @return float |
||
3048 | */ |
||
3049 | 4 | public static function STDEVP(...$args) |
|
3050 | { |
||
3051 | 4 | $aArgs = Functions::flattenArrayIndexed($args); |
|
3052 | |||
3053 | 4 | $returnValue = null; |
|
3054 | |||
3055 | 4 | $aMean = self::AVERAGE($aArgs); |
|
3056 | 4 | if ($aMean !== null) { |
|
3057 | 4 | $aCount = 0; |
|
3058 | 4 | foreach ($aArgs as $k => $arg) { |
|
3059 | 4 | if ((is_bool($arg)) && |
|
3060 | 4 | ((!Functions::isCellValue($k)) || (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE))) { |
|
3061 | $arg = (int) $arg; |
||
3062 | } |
||
3063 | // Is it a numeric value? |
||
3064 | 4 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
3065 | 4 | if ($returnValue === null) { |
|
3066 | 4 | $returnValue = pow(($arg - $aMean), 2); |
|
3067 | } else { |
||
3068 | 4 | $returnValue += pow(($arg - $aMean), 2); |
|
3069 | } |
||
3070 | 4 | ++$aCount; |
|
3071 | } |
||
3072 | } |
||
3073 | |||
3074 | 4 | if (($aCount > 0) && ($returnValue >= 0)) { |
|
3075 | 4 | return sqrt($returnValue / $aCount); |
|
3076 | } |
||
3077 | } |
||
3078 | |||
3079 | return Functions::DIV0(); |
||
3080 | } |
||
3081 | |||
3082 | /** |
||
3083 | * STDEVPA. |
||
3084 | * |
||
3085 | * Calculates standard deviation based on the entire population, including numbers, text, and logical values |
||
3086 | * |
||
3087 | * Excel Function: |
||
3088 | * STDEVPA(value1[,value2[, ...]]) |
||
3089 | * |
||
3090 | * @category Statistical Functions |
||
3091 | * |
||
3092 | * @param mixed ...$args Data values |
||
3093 | * |
||
3094 | * @return float |
||
3095 | */ |
||
3096 | 1 | public static function STDEVPA(...$args) |
|
3097 | { |
||
3098 | 1 | $aArgs = Functions::flattenArrayIndexed($args); |
|
3099 | |||
3100 | 1 | $returnValue = null; |
|
3101 | |||
3102 | 1 | $aMean = self::AVERAGEA($aArgs); |
|
3103 | 1 | if ($aMean !== null) { |
|
3104 | 1 | $aCount = 0; |
|
3105 | 1 | foreach ($aArgs as $k => $arg) { |
|
3106 | 1 | if ((is_bool($arg)) && |
|
3107 | 1 | (!Functions::isMatrixValue($k))) { |
|
3108 | } else { |
||
3109 | // Is it a numeric value? |
||
3110 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { |
|
3111 | 1 | if (is_bool($arg)) { |
|
3112 | 1 | $arg = (int) $arg; |
|
3113 | 1 | } elseif (is_string($arg)) { |
|
3114 | 1 | $arg = 0; |
|
3115 | } |
||
3116 | 1 | if ($returnValue === null) { |
|
3117 | 1 | $returnValue = pow(($arg - $aMean), 2); |
|
3118 | } else { |
||
3119 | 1 | $returnValue += pow(($arg - $aMean), 2); |
|
3120 | } |
||
3121 | 1 | ++$aCount; |
|
3122 | } |
||
3123 | } |
||
3124 | } |
||
3125 | |||
3126 | 1 | if (($aCount > 0) && ($returnValue >= 0)) { |
|
3127 | 1 | return sqrt($returnValue / $aCount); |
|
3128 | } |
||
3129 | } |
||
3130 | |||
3131 | return Functions::DIV0(); |
||
3132 | } |
||
3133 | |||
3134 | /** |
||
3135 | * STEYX. |
||
3136 | * |
||
3137 | * Returns the standard error of the predicted y-value for each x in the regression. |
||
3138 | * |
||
3139 | * @param mixed[] $yValues Data Series Y |
||
3140 | * @param mixed[] $xValues Data Series X |
||
3141 | * |
||
3142 | * @return float |
||
3143 | */ |
||
3144 | public static function STEYX($yValues, $xValues) |
||
3145 | { |
||
3146 | if (!self::checkTrendArrays($yValues, $xValues)) { |
||
3147 | return Functions::VALUE(); |
||
3148 | } |
||
3149 | $yValueCount = count($yValues); |
||
3150 | $xValueCount = count($xValues); |
||
3151 | |||
3152 | if (($yValueCount == 0) || ($yValueCount != $xValueCount)) { |
||
3153 | return Functions::NA(); |
||
3154 | } elseif ($yValueCount == 1) { |
||
3155 | return Functions::DIV0(); |
||
3156 | } |
||
3157 | |||
3158 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues); |
||
3159 | |||
3160 | return $bestFitLinear->getStdevOfResiduals(); |
||
3161 | } |
||
3162 | |||
3163 | /** |
||
3164 | * TDIST. |
||
3165 | * |
||
3166 | * Returns the probability of Student's T distribution. |
||
3167 | * |
||
3168 | * @param float $value Value for the function |
||
3169 | * @param float $degrees degrees of freedom |
||
3170 | * @param float $tails number of tails (1 or 2) |
||
3171 | * |
||
3172 | * @return float |
||
3173 | */ |
||
3174 | public static function TDIST($value, $degrees, $tails) |
||
3175 | { |
||
3176 | $value = Functions::flattenSingleValue($value); |
||
3177 | $degrees = floor(Functions::flattenSingleValue($degrees)); |
||
3178 | $tails = floor(Functions::flattenSingleValue($tails)); |
||
3179 | |||
3180 | if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) { |
||
3181 | if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) { |
||
3182 | return Functions::NAN(); |
||
3183 | } |
||
3184 | // tdist, which finds the probability that corresponds to a given value |
||
3185 | // of t with k degrees of freedom. This algorithm is translated from a |
||
3186 | // pascal function on p81 of "Statistical Computing in Pascal" by D |
||
3187 | // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd: |
||
3188 | // London). The above Pascal algorithm is itself a translation of the |
||
3189 | // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer |
||
3190 | // Laboratory as reported in (among other places) "Applied Statistics |
||
3191 | // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis |
||
3192 | // Horwood Ltd.; W. Sussex, England). |
||
3193 | $tterm = $degrees; |
||
3194 | $ttheta = atan2($value, sqrt($tterm)); |
||
3195 | $tc = cos($ttheta); |
||
3196 | $ts = sin($ttheta); |
||
3197 | $tsum = 0; |
||
3198 | |||
3199 | if (($degrees % 2) == 1) { |
||
3200 | $ti = 3; |
||
3201 | $tterm = $tc; |
||
3202 | } else { |
||
3203 | $ti = 2; |
||
3204 | $tterm = 1; |
||
3205 | } |
||
3206 | |||
3207 | $tsum = $tterm; |
||
3208 | while ($ti < $degrees) { |
||
3209 | $tterm *= $tc * $tc * ($ti - 1) / $ti; |
||
3210 | $tsum += $tterm; |
||
3211 | $ti += 2; |
||
3212 | } |
||
3213 | $tsum *= $ts; |
||
3214 | if (($degrees % 2) == 1) { |
||
3215 | $tsum = Functions::M_2DIVPI * ($tsum + $ttheta); |
||
3216 | } |
||
3217 | $tValue = 0.5 * (1 + $tsum); |
||
3218 | if ($tails == 1) { |
||
3219 | return 1 - abs($tValue); |
||
3220 | } |
||
3221 | |||
3222 | return 1 - abs((1 - $tValue) - $tValue); |
||
3223 | } |
||
3224 | |||
3225 | return Functions::VALUE(); |
||
3226 | } |
||
3227 | |||
3228 | /** |
||
3229 | * TINV. |
||
3230 | * |
||
3231 | * Returns the one-tailed probability of the chi-squared distribution. |
||
3232 | * |
||
3233 | * @param float $probability Probability for the function |
||
3234 | * @param float $degrees degrees of freedom |
||
3235 | * |
||
3236 | * @return float |
||
3237 | */ |
||
3238 | public static function TINV($probability, $degrees) |
||
3239 | { |
||
3240 | $probability = Functions::flattenSingleValue($probability); |
||
3241 | $degrees = floor(Functions::flattenSingleValue($degrees)); |
||
3242 | |||
3243 | if ((is_numeric($probability)) && (is_numeric($degrees))) { |
||
3244 | $xLo = 100; |
||
3245 | $xHi = 0; |
||
3246 | |||
3247 | $x = $xNew = 1; |
||
3248 | $dx = 1; |
||
3249 | $i = 0; |
||
3250 | |||
3251 | while ((abs($dx) > Functions::PRECISION) && ($i++ < self::MAX_ITERATIONS)) { |
||
3252 | // Apply Newton-Raphson step |
||
3253 | $result = self::TDIST($x, $degrees, 2); |
||
3254 | $error = $result - $probability; |
||
3255 | if ($error == 0.0) { |
||
3256 | $dx = 0; |
||
3257 | } elseif ($error < 0.0) { |
||
3258 | $xLo = $x; |
||
3259 | } else { |
||
3260 | $xHi = $x; |
||
3261 | } |
||
3262 | // Avoid division by zero |
||
3263 | if ($result != 0.0) { |
||
3264 | $dx = $error / $result; |
||
3265 | $xNew = $x - $dx; |
||
3266 | } |
||
3267 | // If the NR fails to converge (which for example may be the |
||
3268 | // case if the initial guess is too rough) we apply a bisection |
||
3269 | // step to determine a more narrow interval around the root. |
||
3270 | if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) { |
||
3271 | $xNew = ($xLo + $xHi) / 2; |
||
3272 | $dx = $xNew - $x; |
||
3273 | } |
||
3274 | $x = $xNew; |
||
3275 | } |
||
3276 | if ($i == self::MAX_ITERATIONS) { |
||
3277 | return Functions::NA(); |
||
3278 | } |
||
3279 | |||
3280 | return round($x, 12); |
||
3281 | } |
||
3282 | |||
3283 | return Functions::VALUE(); |
||
3284 | } |
||
3285 | |||
3286 | /** |
||
3287 | * TREND. |
||
3288 | * |
||
3289 | * Returns values along a linear Trend |
||
3290 | * |
||
3291 | * @param mixed[] $yValues Data Series Y |
||
3292 | * @param mixed[] $xValues Data Series X |
||
3293 | * @param mixed[] $newValues Values of X for which we want to find Y |
||
3294 | * @param bool $const a logical value specifying whether to force the intersect to equal 0 |
||
3295 | * |
||
3296 | * @return array of float |
||
3297 | */ |
||
3298 | public static function TREND($yValues, $xValues = [], $newValues = [], $const = true) |
||
3299 | { |
||
3300 | $yValues = Functions::flattenArray($yValues); |
||
3301 | $xValues = Functions::flattenArray($xValues); |
||
3302 | $newValues = Functions::flattenArray($newValues); |
||
3303 | $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const); |
||
3304 | |||
3305 | $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const); |
||
3306 | if (empty($newValues)) { |
||
3307 | $newValues = $bestFitLinear->getXValues(); |
||
3308 | } |
||
3309 | |||
3310 | $returnArray = []; |
||
3311 | foreach ($newValues as $xValue) { |
||
3312 | $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue); |
||
3313 | } |
||
3314 | |||
3315 | return $returnArray; |
||
3316 | } |
||
3317 | |||
3318 | /** |
||
3319 | * TRIMMEAN. |
||
3320 | * |
||
3321 | * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean |
||
3322 | * taken by excluding a percentage of data points from the top and bottom tails |
||
3323 | * of a data set. |
||
3324 | * |
||
3325 | * Excel Function: |
||
3326 | * TRIMEAN(value1[,value2[, ...]], $discard) |
||
3327 | * |
||
3328 | * @category Statistical Functions |
||
3329 | * |
||
3330 | * @param mixed $args Data values |
||
3331 | * @param float $discard Percentage to discard |
||
3332 | * |
||
3333 | * @return float |
||
3334 | */ |
||
3335 | public static function TRIMMEAN(...$args) |
||
3336 | { |
||
3337 | $aArgs = Functions::flattenArray($args); |
||
3338 | |||
3339 | // Calculate |
||
3340 | $percent = array_pop($aArgs); |
||
3341 | |||
3342 | if ((is_numeric($percent)) && (!is_string($percent))) { |
||
3343 | if (($percent < 0) || ($percent > 1)) { |
||
3344 | return Functions::NAN(); |
||
3345 | } |
||
3346 | $mArgs = []; |
||
3347 | foreach ($aArgs as $arg) { |
||
3348 | // Is it a numeric value? |
||
3349 | if ((is_numeric($arg)) && (!is_string($arg))) { |
||
3350 | $mArgs[] = $arg; |
||
3351 | } |
||
3352 | } |
||
3353 | $discard = floor(self::COUNT($mArgs) * $percent / 2); |
||
3354 | sort($mArgs); |
||
3355 | for ($i = 0; $i < $discard; ++$i) { |
||
3356 | array_pop($mArgs); |
||
3357 | array_shift($mArgs); |
||
3358 | } |
||
3359 | |||
3360 | return self::AVERAGE($mArgs); |
||
3361 | } |
||
3362 | |||
3363 | return Functions::VALUE(); |
||
3364 | } |
||
3365 | |||
3366 | /** |
||
3367 | * VARFunc. |
||
3368 | * |
||
3369 | * Estimates variance based on a sample. |
||
3370 | * |
||
3371 | * Excel Function: |
||
3372 | * VAR(value1[,value2[, ...]]) |
||
3373 | * |
||
3374 | * @category Statistical Functions |
||
3375 | * |
||
3376 | * @param mixed ...$args Data values |
||
3377 | * |
||
3378 | * @return float |
||
3379 | */ |
||
3380 | 4 | public static function VARFunc(...$args) |
|
3381 | { |
||
3382 | 4 | $returnValue = Functions::DIV0(); |
|
3383 | |||
3384 | 4 | $summerA = $summerB = 0; |
|
3385 | |||
3386 | // Loop through arguments |
||
3387 | 4 | $aArgs = Functions::flattenArray($args); |
|
3388 | 4 | $aCount = 0; |
|
3389 | 4 | foreach ($aArgs as $arg) { |
|
3390 | 4 | if (is_bool($arg)) { |
|
3391 | 1 | $arg = (int) $arg; |
|
3392 | } |
||
3393 | // Is it a numeric value? |
||
3394 | 4 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
3395 | 4 | $summerA += ($arg * $arg); |
|
3396 | 4 | $summerB += $arg; |
|
3397 | 4 | ++$aCount; |
|
3398 | } |
||
3399 | } |
||
3400 | |||
3401 | 4 | if ($aCount > 1) { |
|
3402 | 4 | $summerA *= $aCount; |
|
3403 | 4 | $summerB *= $summerB; |
|
3404 | 4 | $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); |
|
3405 | } |
||
3406 | |||
3407 | 4 | return $returnValue; |
|
3408 | } |
||
3409 | |||
3410 | /** |
||
3411 | * VARA. |
||
3412 | * |
||
3413 | * Estimates variance based on a sample, including numbers, text, and logical values |
||
3414 | * |
||
3415 | * Excel Function: |
||
3416 | * VARA(value1[,value2[, ...]]) |
||
3417 | * |
||
3418 | * @category Statistical Functions |
||
3419 | * |
||
3420 | * @param mixed ...$args Data values |
||
3421 | * |
||
3422 | * @return float |
||
3423 | */ |
||
3424 | 1 | public static function VARA(...$args) |
|
3425 | { |
||
3426 | 1 | $returnValue = Functions::DIV0(); |
|
3427 | |||
3428 | 1 | $summerA = $summerB = 0; |
|
3429 | |||
3430 | // Loop through arguments |
||
3431 | 1 | $aArgs = Functions::flattenArrayIndexed($args); |
|
3432 | 1 | $aCount = 0; |
|
3433 | 1 | foreach ($aArgs as $k => $arg) { |
|
3434 | 1 | if ((is_string($arg)) && |
|
3435 | 1 | (Functions::isValue($k))) { |
|
3436 | return Functions::VALUE(); |
||
3437 | 1 | } elseif ((is_string($arg)) && |
|
3438 | 1 | (!Functions::isMatrixValue($k))) { |
|
3439 | } else { |
||
3440 | // Is it a numeric value? |
||
3441 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { |
|
3442 | 1 | if (is_bool($arg)) { |
|
3443 | 1 | $arg = (int) $arg; |
|
3444 | 1 | } elseif (is_string($arg)) { |
|
3445 | 1 | $arg = 0; |
|
3446 | } |
||
3447 | 1 | $summerA += ($arg * $arg); |
|
3448 | 1 | $summerB += $arg; |
|
3449 | 1 | ++$aCount; |
|
3450 | } |
||
3451 | } |
||
3452 | } |
||
3453 | |||
3454 | 1 | if ($aCount > 1) { |
|
3455 | 1 | $summerA *= $aCount; |
|
3456 | 1 | $summerB *= $summerB; |
|
3457 | 1 | $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1)); |
|
3458 | } |
||
3459 | |||
3460 | 1 | return $returnValue; |
|
3461 | } |
||
3462 | |||
3463 | /** |
||
3464 | * VARP. |
||
3465 | * |
||
3466 | * Calculates variance based on the entire population |
||
3467 | * |
||
3468 | * Excel Function: |
||
3469 | * VARP(value1[,value2[, ...]]) |
||
3470 | * |
||
3471 | * @category Statistical Functions |
||
3472 | * |
||
3473 | * @param mixed ...$args Data values |
||
3474 | * |
||
3475 | * @return float |
||
3476 | */ |
||
3477 | 4 | public static function VARP(...$args) |
|
3478 | { |
||
3479 | // Return value |
||
3480 | 4 | $returnValue = Functions::DIV0(); |
|
3481 | |||
3482 | 4 | $summerA = $summerB = 0; |
|
3483 | |||
3484 | // Loop through arguments |
||
3485 | 4 | $aArgs = Functions::flattenArray($args); |
|
3486 | 4 | $aCount = 0; |
|
3487 | 4 | foreach ($aArgs as $arg) { |
|
3488 | 4 | if (is_bool($arg)) { |
|
3489 | 1 | $arg = (int) $arg; |
|
3490 | } |
||
3491 | // Is it a numeric value? |
||
3492 | 4 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
3493 | 4 | $summerA += ($arg * $arg); |
|
3494 | 4 | $summerB += $arg; |
|
3495 | 4 | ++$aCount; |
|
3496 | } |
||
3497 | } |
||
3498 | |||
3499 | 4 | if ($aCount > 0) { |
|
3500 | 4 | $summerA *= $aCount; |
|
3501 | 4 | $summerB *= $summerB; |
|
3502 | 4 | $returnValue = ($summerA - $summerB) / ($aCount * $aCount); |
|
3503 | } |
||
3504 | |||
3505 | 4 | return $returnValue; |
|
3506 | } |
||
3507 | |||
3508 | /** |
||
3509 | * VARPA. |
||
3510 | * |
||
3511 | * Calculates variance based on the entire population, including numbers, text, and logical values |
||
3512 | * |
||
3513 | * Excel Function: |
||
3514 | * VARPA(value1[,value2[, ...]]) |
||
3515 | * |
||
3516 | * @category Statistical Functions |
||
3517 | * |
||
3518 | * @param mixed ...$args Data values |
||
3519 | * |
||
3520 | * @return float |
||
3521 | */ |
||
3522 | 1 | public static function VARPA(...$args) |
|
3523 | { |
||
3524 | 1 | $returnValue = Functions::DIV0(); |
|
3525 | |||
3526 | 1 | $summerA = $summerB = 0; |
|
3527 | |||
3528 | // Loop through arguments |
||
3529 | 1 | $aArgs = Functions::flattenArrayIndexed($args); |
|
3530 | 1 | $aCount = 0; |
|
3531 | 1 | foreach ($aArgs as $k => $arg) { |
|
3532 | 1 | if ((is_string($arg)) && |
|
3533 | 1 | (Functions::isValue($k))) { |
|
3534 | return Functions::VALUE(); |
||
3535 | 1 | } elseif ((is_string($arg)) && |
|
3536 | 1 | (!Functions::isMatrixValue($k))) { |
|
3537 | } else { |
||
3538 | // Is it a numeric value? |
||
3539 | 1 | if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) { |
|
3540 | 1 | if (is_bool($arg)) { |
|
3541 | 1 | $arg = (int) $arg; |
|
3542 | 1 | } elseif (is_string($arg)) { |
|
3543 | 1 | $arg = 0; |
|
3544 | } |
||
3545 | 1 | $summerA += ($arg * $arg); |
|
3546 | 1 | $summerB += $arg; |
|
3547 | 1 | ++$aCount; |
|
3548 | } |
||
3549 | } |
||
3550 | } |
||
3551 | |||
3552 | 1 | if ($aCount > 0) { |
|
3553 | 1 | $summerA *= $aCount; |
|
3554 | 1 | $summerB *= $summerB; |
|
3555 | 1 | $returnValue = ($summerA - $summerB) / ($aCount * $aCount); |
|
3556 | } |
||
3557 | |||
3558 | 1 | return $returnValue; |
|
3559 | } |
||
3560 | |||
3561 | /** |
||
3562 | * WEIBULL. |
||
3563 | * |
||
3564 | * Returns the Weibull distribution. Use this distribution in reliability |
||
3565 | * analysis, such as calculating a device's mean time to failure. |
||
3566 | * |
||
3567 | * @param float $value |
||
3568 | * @param float $alpha Alpha Parameter |
||
3569 | * @param float $beta Beta Parameter |
||
3570 | * @param bool $cumulative |
||
3571 | * |
||
3572 | * @return float |
||
3573 | */ |
||
3574 | public static function WEIBULL($value, $alpha, $beta, $cumulative) |
||
3575 | { |
||
3576 | $value = Functions::flattenSingleValue($value); |
||
3577 | $alpha = Functions::flattenSingleValue($alpha); |
||
3578 | $beta = Functions::flattenSingleValue($beta); |
||
3579 | |||
3580 | if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) { |
||
3581 | if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) { |
||
3582 | return Functions::NAN(); |
||
3583 | } |
||
3584 | if ((is_numeric($cumulative)) || (is_bool($cumulative))) { |
||
3585 | if ($cumulative) { |
||
3586 | return 1 - exp(0 - pow($value / $beta, $alpha)); |
||
3587 | } |
||
3588 | |||
3589 | return ($alpha / pow($beta, $alpha)) * pow($value, $alpha - 1) * exp(0 - pow($value / $beta, $alpha)); |
||
3590 | } |
||
3591 | } |
||
3592 | |||
3593 | return Functions::VALUE(); |
||
3594 | } |
||
3595 | |||
3596 | /** |
||
3597 | * ZTEST. |
||
3598 | * |
||
3599 | * Returns the Weibull distribution. Use this distribution in reliability |
||
3600 | * analysis, such as calculating a device's mean time to failure. |
||
3601 | * |
||
3602 | * @param float $dataSet |
||
3603 | * @param float $m0 Alpha Parameter |
||
3604 | * @param float $sigma Beta Parameter |
||
3605 | * |
||
3606 | * @return float |
||
3607 | */ |
||
3608 | public static function ZTEST($dataSet, $m0, $sigma = null) |
||
3609 | { |
||
3610 | $dataSet = Functions::flattenArrayIndexed($dataSet); |
||
3611 | $m0 = Functions::flattenSingleValue($m0); |
||
3612 | $sigma = Functions::flattenSingleValue($sigma); |
||
3613 | |||
3614 | if ($sigma === null) { |
||
3615 | $sigma = self::STDEV($dataSet); |
||
3616 | } |
||
3617 | $n = count($dataSet); |
||
3618 | |||
3619 | return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / sqrt($n))); |
||
3620 | } |
||
3621 | } |
||
3622 |