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