Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Blame
Revision Log

Statistical.php @ 1

Last change on this file since 1 was 1, checked in by dungnv, 11 years ago

File size: 106.7 KB

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

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sourcecode/application/libraries/PHPExcel/Calculation/Statistical.php @ 1

Download in other formats: