PageRenderTime 18ms CodeModel.GetById 4ms app.highlight 52ms RepoModel.GetById 1ms app.codeStats 1ms

/branches/v1.7.6/Classes/PHPExcel/Calculation/Statistical.php

#
PHP | 3643 lines | 2023 code | 386 blank | 1234 comment | 652 complexity | e0bf77c3ca30883eb48bd3919340a60e MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1<?php
   2/**
   3 * PHPExcel
   4 *
   5 * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  24 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
  25 * @version		##VERSION##, ##DATE##
  26 */
  27
  28
  29/** PHPExcel root directory */
  30if (!defined('PHPEXCEL_ROOT')) {
  31	/**
  32	 * @ignore
  33	 */
  34	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  35	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  36}
  37
  38
  39require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
  40
  41
  42/** LOG_GAMMA_X_MAX_VALUE */
  43define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
  44
  45/** XMININ */
  46define('XMININ', 2.23e-308);
  47
  48/** EPS */
  49define('EPS', 2.22e-16);
  50
  51/** SQRT2PI */
  52define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
  53
  54
  55/**
  56 * PHPExcel_Calculation_Statistical
  57 *
  58 * @category	PHPExcel
  59 * @package		PHPExcel_Calculation
  60 * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  61 */
  62class 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	 *	@param p require p>0
 145	 *	@param q require q>0
 146	 *	@return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
 147	 *	@author Jaco van Kooten
 148	 */
 149	private static function _logBeta($p, $q) {
 150		if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
 151			self::$_logBetaCache_p = $p;
 152			self::$_logBetaCache_q = $q;
 153			if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
 154				self::$_logBetaCache_result = 0.0;
 155			} else {
 156				self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
 157			}
 158		}
 159		return self::$_logBetaCache_result;
 160	}	//	function _logBeta()
 161
 162
 163	/**
 164	 *	Evaluates of continued fraction part of incomplete beta function.
 165	 *	Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
 166	 *	@author Jaco van Kooten
 167	 */
 168	private static function _betaFraction($x, $p, $q) {
 169		$c = 1.0;
 170		$sum_pq = $p + $q;
 171		$p_plus = $p + 1.0;
 172		$p_minus = $p - 1.0;
 173		$h = 1.0 - $sum_pq * $x / $p_plus;
 174		if (abs($h) < XMININ) {
 175			$h = XMININ;
 176		}
 177		$h = 1.0 / $h;
 178		$frac = $h;
 179		$m	 = 1;
 180		$delta = 0.0;
 181		while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
 182			$m2 = 2 * $m;
 183			// even index for d
 184			$d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
 185			$h = 1.0 + $d * $h;
 186			if (abs($h) < XMININ) {
 187				$h = XMININ;
 188			}
 189			$h = 1.0 / $h;
 190			$c = 1.0 + $d / $c;
 191			if (abs($c) < XMININ) {
 192				$c = XMININ;
 193			}
 194			$frac *= $h * $c;
 195			// odd index for d
 196			$d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
 197			$h = 1.0 + $d * $h;
 198			if (abs($h) < XMININ) {
 199				$h = XMININ;
 200			}
 201			$h = 1.0 / $h;
 202			$c = 1.0 + $d / $c;
 203			if (abs($c) < XMININ) {
 204				$c = XMININ;
 205			}
 206			$delta = $h * $c;
 207			$frac *= $delta;
 208			++$m;
 209		}
 210		return $frac;
 211	}	//	function _betaFraction()
 212
 213
 214	/**
 215	 * logGamma function
 216	 *
 217	 * @version 1.1
 218	 * @author Jaco van Kooten
 219	 *
 220	 * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
 221	 *
 222	 * The natural logarithm of the gamma function. <br />
 223	 * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
 224	 * Applied Mathematics Division <br />
 225	 * Argonne National Laboratory <br />
 226	 * Argonne, IL 60439 <br />
 227	 * <p>
 228	 * References:
 229	 * <ol>
 230	 * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
 231	 *	 Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
 232	 * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
 233	 * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
 234	 * </ol>
 235	 * </p>
 236	 * <p>
 237	 * From the original documentation:
 238	 * </p>
 239	 * <p>
 240	 * This routine calculates the LOG(GAMMA) function for a positive real argument X.
 241	 * Computation is based on an algorithm outlined in references 1 and 2.
 242	 * The program uses rational functions that theoretically approximate LOG(GAMMA)
 243	 * to at least 18 significant decimal digits. The approximation for X > 12 is from
 244	 * reference 3, while approximations for X < 12.0 are similar to those in reference
 245	 * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
 246	 * the compiler, the intrinsic functions, and proper selection of the
 247	 * machine-dependent constants.
 248	 * </p>
 249	 * <p>
 250	 * Error returns: <br />
 251	 * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
 252	 * The computation is believed to be free of underflow and overflow.
 253	 * </p>
 254	 * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
 255	 */
 256
 257	// Function cache for logGamma
 258	private static $_logGammaCache_result	= 0.0;
 259	private static $_logGammaCache_x		= 0.0;
 260
 261	private static function _logGamma($x) {
 262		// Log Gamma related constants
 263		static $lg_d1 = -0.5772156649015328605195174;
 264		static $lg_d2 = 0.4227843350984671393993777;
 265		static $lg_d4 = 1.791759469228055000094023;
 266
 267		static $lg_p1 = array(	4.945235359296727046734888,
 268								201.8112620856775083915565,
 269								2290.838373831346393026739,
 270								11319.67205903380828685045,
 271								28557.24635671635335736389,
 272								38484.96228443793359990269,
 273								26377.48787624195437963534,
 274								7225.813979700288197698961 );
 275		static $lg_p2 = array(	4.974607845568932035012064,
 276								542.4138599891070494101986,
 277								15506.93864978364947665077,
 278								184793.2904445632425417223,
 279								1088204.76946882876749847,
 280								3338152.967987029735917223,
 281								5106661.678927352456275255,
 282								3074109.054850539556250927 );
 283		static $lg_p4 = array(	14745.02166059939948905062,
 284								2426813.369486704502836312,
 285								121475557.4045093227939592,
 286								2663432449.630976949898078,
 287								29403789566.34553899906876,
 288								170266573776.5398868392998,
 289								492612579337.743088758812,
 290								560625185622.3951465078242 );
 291
 292		static $lg_q1 = array(	67.48212550303777196073036,
 293								1113.332393857199323513008,
 294								7738.757056935398733233834,
 295								27639.87074403340708898585,
 296								54993.10206226157329794414,
 297								61611.22180066002127833352,
 298								36351.27591501940507276287,
 299								8785.536302431013170870835 );
 300		static $lg_q2 = array(	183.0328399370592604055942,
 301								7765.049321445005871323047,
 302								133190.3827966074194402448,
 303								1136705.821321969608938755,
 304								5267964.117437946917577538,
 305								13467014.54311101692290052,
 306								17827365.30353274213975932,
 307								9533095.591844353613395747 );
 308		static $lg_q4 = array(	2690.530175870899333379843,
 309								639388.5654300092398984238,
 310								41355999.30241388052042842,
 311								1120872109.61614794137657,
 312								14886137286.78813811542398,
 313								101680358627.2438228077304,
 314								341747634550.7377132798597,
 315								446315818741.9713286462081 );
 316
 317		static $lg_c  = array(	-0.001910444077728,
 318								8.4171387781295e-4,
 319								-5.952379913043012e-4,
 320								7.93650793500350248e-4,
 321								-0.002777777777777681622553,
 322								0.08333333333333333331554247,
 323								0.0057083835261 );
 324
 325	// Rough estimate of the fourth root of logGamma_xBig
 326	static $lg_frtbig = 2.25e76;
 327	static $pnt68	 = 0.6796875;
 328
 329
 330	if ($x == self::$_logGammaCache_x) {
 331		return self::$_logGammaCache_result;
 332	}
 333	$y = $x;
 334	if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
 335		if ($y <= EPS) {
 336			$res = -log(y);
 337		} elseif ($y <= 1.5) {
 338			// ---------------------
 339			//	EPS .LT. X .LE. 1.5
 340			// ---------------------
 341			if ($y < $pnt68) {
 342				$corr = -log($y);
 343				$xm1 = $y;
 344			} else {
 345				$corr = 0.0;
 346				$xm1 = $y - 1.0;
 347			}
 348			if ($y <= 0.5 || $y >= $pnt68) {
 349				$xden = 1.0;
 350				$xnum = 0.0;
 351				for ($i = 0; $i < 8; ++$i) {
 352					$xnum = $xnum * $xm1 + $lg_p1[$i];
 353					$xden = $xden * $xm1 + $lg_q1[$i];
 354				}
 355				$res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
 356			} else {
 357				$xm2 = $y - 1.0;
 358				$xden = 1.0;
 359				$xnum = 0.0;
 360				for ($i = 0; $i < 8; ++$i) {
 361					$xnum = $xnum * $xm2 + $lg_p2[$i];
 362					$xden = $xden * $xm2 + $lg_q2[$i];
 363				}
 364				$res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
 365			}
 366		} elseif ($y <= 4.0) {
 367			// ---------------------
 368			//	1.5 .LT. X .LE. 4.0
 369			// ---------------------
 370			$xm2 = $y - 2.0;
 371			$xden = 1.0;
 372			$xnum = 0.0;
 373			for ($i = 0; $i < 8; ++$i) {
 374				$xnum = $xnum * $xm2 + $lg_p2[$i];
 375				$xden = $xden * $xm2 + $lg_q2[$i];
 376			}
 377			$res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
 378		} elseif ($y <= 12.0) {
 379			// ----------------------
 380			//	4.0 .LT. X .LE. 12.0
 381			// ----------------------
 382			$xm4 = $y - 4.0;
 383			$xden = -1.0;
 384			$xnum = 0.0;
 385			for ($i = 0; $i < 8; ++$i) {
 386				$xnum = $xnum * $xm4 + $lg_p4[$i];
 387				$xden = $xden * $xm4 + $lg_q4[$i];
 388			}
 389			$res = $lg_d4 + $xm4 * ($xnum / $xden);
 390		} else {
 391			// ---------------------------------
 392			//	Evaluate for argument .GE. 12.0
 393			// ---------------------------------
 394			$res = 0.0;
 395			if ($y <= $lg_frtbig) {
 396				$res = $lg_c[6];
 397				$ysq = $y * $y;
 398				for ($i = 0; $i < 6; ++$i)
 399					$res = $res / $ysq + $lg_c[$i];
 400				}
 401				$res /= $y;
 402				$corr = log($y);
 403				$res = $res + log(SQRT2PI) - 0.5 * $corr;
 404				$res += $y * ($corr - 1.0);
 405			}
 406		} else {
 407			// --------------------------
 408			//	Return for bad arguments
 409			// --------------------------
 410			$res = MAX_VALUE;
 411		}
 412		// ------------------------------
 413		//	Final adjustments and return
 414		// ------------------------------
 415		self::$_logGammaCache_x = $x;
 416		self::$_logGammaCache_result = $res;
 417		return $res;
 418	}	//	function _logGamma()
 419
 420
 421	//
 422	//	Private implementation of the incomplete Gamma function
 423	//
 424	private static function _incompleteGamma($a,$x) {
 425		static $max = 32;
 426		$summer = 0;
 427		for ($n=0; $n<=$max; ++$n) {
 428			$divisor = $a;
 429			for ($i=1; $i<=$n; ++$i) {
 430				$divisor *= ($a + $i);
 431			}
 432			$summer += (pow($x,$n) / $divisor);
 433		}
 434		return pow($x,$a) * exp(0-$x) * $summer;
 435	}	//	function _incompleteGamma()
 436
 437
 438	//
 439	//	Private implementation of the Gamma function
 440	//
 441	private static function _gamma($data) {
 442		if ($data == 0.0) return 0;
 443
 444		static $p0 = 1.000000000190015;
 445		static $p = array ( 1 => 76.18009172947146,
 446							2 => -86.50532032941677,
 447							3 => 24.01409824083091,
 448							4 => -1.231739572450155,
 449							5 => 1.208650973866179e-3,
 450							6 => -5.395239384953e-6
 451						  );
 452
 453		$y = $x = $data;
 454		$tmp = $x + 5.5;
 455		$tmp -= ($x + 0.5) * log($tmp);
 456
 457		$summer = $p0;
 458		for ($j=1;$j<=6;++$j) {
 459			$summer += ($p[$j] / ++$y);
 460		}
 461		return exp(0 - $tmp + log(SQRT2PI * $summer / $x));
 462	}	//	function _gamma()
 463
 464
 465	/***************************************************************************
 466	 *								inverse_ncdf.php
 467	 *							-------------------
 468	 *	begin				: Friday, January 16, 2004
 469	 *	copyright			: (C) 2004 Michael Nickerson
 470	 *	email				: nickersonm@yahoo.com
 471	 *
 472	 ***************************************************************************/
 473	private static function _inverse_ncdf($p) {
 474		//	Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
 475		//	PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
 476		//	a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
 477		//	I have not checked the accuracy of this implementation. Be aware that PHP
 478		//	will truncate the coeficcients to 14 digits.
 479
 480		//	You have permission to use and distribute this function freely for
 481		//	whatever purpose you want, but please show common courtesy and give credit
 482		//	where credit is due.
 483
 484		//	Input paramater is $p - probability - where 0 < p < 1.
 485
 486		//	Coefficients in rational approximations
 487		static $a = array(	1 => -3.969683028665376e+01,
 488							2 => 2.209460984245205e+02,
 489							3 => -2.759285104469687e+02,
 490							4 => 1.383577518672690e+02,
 491							5 => -3.066479806614716e+01,
 492							6 => 2.506628277459239e+00
 493						 );
 494
 495		static $b = array(	1 => -5.447609879822406e+01,
 496							2 => 1.615858368580409e+02,
 497							3 => -1.556989798598866e+02,
 498							4 => 6.680131188771972e+01,
 499							5 => -1.328068155288572e+01
 500						 );
 501
 502		static $c = array(	1 => -7.784894002430293e-03,
 503							2 => -3.223964580411365e-01,
 504							3 => -2.400758277161838e+00,
 505							4 => -2.549732539343734e+00,
 506							5 => 4.374664141464968e+00,
 507							6 => 2.938163982698783e+00
 508						 );
 509
 510		static $d = array(	1 => 7.784695709041462e-03,
 511							2 => 3.224671290700398e-01,
 512							3 => 2.445134137142996e+00,
 513							4 => 3.754408661907416e+00
 514						 );
 515
 516		//	Define lower and upper region break-points.
 517		$p_low = 0.02425;			//Use lower region approx. below this
 518		$p_high = 1 - $p_low;		//Use upper region approx. above this
 519
 520		if (0 < $p && $p < $p_low) {
 521			//	Rational approximation for lower region.
 522			$q = sqrt(-2 * log($p));
 523			return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
 524					(((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
 525		} elseif ($p_low <= $p && $p <= $p_high) {
 526			//	Rational approximation for central region.
 527			$q = $p - 0.5;
 528			$r = $q * $q;
 529			return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
 530				   ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
 531		} elseif ($p_high < $p && $p < 1) {
 532			//	Rational approximation for upper region.
 533			$q = sqrt(-2 * log(1 - $p));
 534			return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
 535					 (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
 536		}
 537		//	If 0 < p < 1, return a null value
 538		return PHPExcel_Calculation_Functions::NULL();
 539	}	//	function _inverse_ncdf()
 540
 541
 542	private static function _inverse_ncdf2($prob) {
 543		//	Approximation of inverse standard normal CDF developed by
 544		//	B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
 545
 546		$a1 = 2.50662823884;
 547		$a2 = -18.61500062529;
 548		$a3 = 41.39119773534;
 549		$a4 = -25.44106049637;
 550
 551		$b1 = -8.4735109309;
 552		$b2 = 23.08336743743;
 553		$b3 = -21.06224101826;
 554		$b4 = 3.13082909833;
 555
 556		$c1 = 0.337475482272615;
 557		$c2 = 0.976169019091719;
 558		$c3 = 0.160797971491821;
 559		$c4 = 2.76438810333863E-02;
 560		$c5 = 3.8405729373609E-03;
 561		$c6 = 3.951896511919E-04;
 562		$c7 = 3.21767881768E-05;
 563		$c8 = 2.888167364E-07;
 564		$c9 = 3.960315187E-07;
 565
 566		$y = $prob - 0.5;
 567		if (abs($y) < 0.42) {
 568			$z = ($y * $y);
 569			$z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
 570		} else {
 571			if ($y > 0) {
 572				$z = log(-log(1 - $prob));
 573			} else {
 574				$z = log(-log($prob));
 575			}
 576			$z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
 577			if ($y < 0) {
 578				$z = -$z;
 579			}
 580		}
 581		return $z;
 582	}	//	function _inverse_ncdf2()
 583
 584
 585	private static function _inverse_ncdf3($p) {
 586		//	ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
 587		//	Produces the normal deviate Z corresponding to a given lower
 588		//	tail area of P; Z is accurate to about 1 part in 10**16.
 589		//
 590		//	This is a PHP version of the original FORTRAN code that can
 591		//	be found at http://lib.stat.cmu.edu/apstat/
 592		$split1 = 0.425;
 593		$split2 = 5;
 594		$const1 = 0.180625;
 595		$const2 = 1.6;
 596
 597		//	coefficients for p close to 0.5
 598		$a0 = 3.3871328727963666080;
 599		$a1 = 1.3314166789178437745E+2;
 600		$a2 = 1.9715909503065514427E+3;
 601		$a3 = 1.3731693765509461125E+4;
 602		$a4 = 4.5921953931549871457E+4;
 603		$a5 = 6.7265770927008700853E+4;
 604		$a6 = 3.3430575583588128105E+4;
 605		$a7 = 2.5090809287301226727E+3;
 606
 607		$b1 = 4.2313330701600911252E+1;
 608		$b2 = 6.8718700749205790830E+2;
 609		$b3 = 5.3941960214247511077E+3;
 610		$b4 = 2.1213794301586595867E+4;
 611		$b5 = 3.9307895800092710610E+4;
 612		$b6 = 2.8729085735721942674E+4;
 613		$b7 = 5.2264952788528545610E+3;
 614
 615		//	coefficients for p not close to 0, 0.5 or 1.
 616		$c0 = 1.42343711074968357734;
 617		$c1 = 4.63033784615654529590;
 618		$c2 = 5.76949722146069140550;
 619		$c3 = 3.64784832476320460504;
 620		$c4 = 1.27045825245236838258;
 621		$c5 = 2.41780725177450611770E-1;
 622		$c6 = 2.27238449892691845833E-2;
 623		$c7 = 7.74545014278341407640E-4;
 624
 625		$d1 = 2.05319162663775882187;
 626		$d2 = 1.67638483018380384940;
 627		$d3 = 6.89767334985100004550E-1;
 628		$d4 = 1.48103976427480074590E-1;
 629		$d5 = 1.51986665636164571966E-2;
 630		$d6 = 5.47593808499534494600E-4;
 631		$d7 = 1.05075007164441684324E-9;
 632
 633		//	coefficients for p near 0 or 1.
 634		$e0 = 6.65790464350110377720;
 635		$e1 = 5.46378491116411436990;
 636		$e2 = 1.78482653991729133580;
 637		$e3 = 2.96560571828504891230E-1;
 638		$e4 = 2.65321895265761230930E-2;
 639		$e5 = 1.24266094738807843860E-3;
 640		$e6 = 2.71155556874348757815E-5;
 641		$e7 = 2.01033439929228813265E-7;
 642
 643		$f1 = 5.99832206555887937690E-1;
 644		$f2 = 1.36929880922735805310E-1;
 645		$f3 = 1.48753612908506148525E-2;
 646		$f4 = 7.86869131145613259100E-4;
 647		$f5 = 1.84631831751005468180E-5;
 648		$f6 = 1.42151175831644588870E-7;
 649		$f7 = 2.04426310338993978564E-15;
 650
 651		$q = $p - 0.5;
 652
 653		//	computation for p close to 0.5
 654		if (abs($q) <= split1) {
 655			$R = $const1 - $q * $q;
 656			$z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
 657					  ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
 658		} else {
 659			if ($q < 0) {
 660				$R = $p;
 661			} else {
 662				$R = 1 - $p;
 663			}
 664			$R = pow(-log($R),2);
 665
 666			//	computation for p not close to 0, 0.5 or 1.
 667			If ($R <= $split2) {
 668				$R = $R - $const2;
 669				$z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
 670					 ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
 671			} else {
 672			//	computation for p near 0 or 1.
 673				$R = $R - $split2;
 674				$z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
 675					 ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
 676			}
 677			if ($q < 0) {
 678				$z = -$z;
 679			}
 680		}
 681		return $z;
 682	}	//	function _inverse_ncdf3()
 683
 684
 685	/**
 686	 *	AVEDEV
 687	 *
 688	 *	Returns the average of the absolute deviations of data points from their mean.
 689	 *	AVEDEV is a measure of the variability in a data set.
 690	 *
 691	 *	Excel Function:
 692	 *		AVEDEV(value1[,value2[, ...]])
 693	 *
 694	 *	@access	public
 695	 *	@category Statistical Functions
 696	 *	@param	mixed		$arg,...		Data values
 697	 *	@return	float
 698	 */
 699	public static function AVEDEV() {
 700		$aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
 701
 702		// Return value
 703		$returnValue = null;
 704
 705		$aMean = self::AVERAGE($aArgs);
 706		if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
 707			$aCount = 0;
 708			foreach ($aArgs as $k => $arg) {
 709				if ((is_bool($arg)) &&
 710					((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
 711					$arg = (integer) $arg;
 712				}
 713				// Is it a numeric value?
 714				if ((is_numeric($arg)) && (!is_string($arg))) {
 715					if (is_null($returnValue)) {
 716						$returnValue = abs($arg - $aMean);
 717					} else {
 718						$returnValue += abs($arg - $aMean);
 719					}
 720					++$aCount;
 721				}
 722			}
 723
 724			// Return
 725			if ($aCount == 0) {
 726				return PHPExcel_Calculation_Functions::DIV0();
 727			}
 728			return $returnValue / $aCount;
 729		}
 730		return PHPExcel_Calculation_Functions::NaN();
 731	}	//	function AVEDEV()
 732
 733
 734	/**
 735	 *	AVERAGE
 736	 *
 737	 *	Returns the average (arithmetic mean) of the arguments
 738	 *
 739	 *	Excel Function:
 740	 *		AVERAGE(value1[,value2[, ...]])
 741	 *
 742	 *	@access	public
 743	 *	@category Statistical Functions
 744	 *	@param	mixed		$arg,...		Data values
 745	 *	@return	float
 746	 */
 747	public static function AVERAGE() {
 748		$returnValue = $aCount = 0;
 749
 750		// Loop through arguments
 751		foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
 752			if ((is_bool($arg)) &&
 753				((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
 754				$arg = (integer) $arg;
 755			}
 756			// Is it a numeric value?
 757			if ((is_numeric($arg)) && (!is_string($arg))) {
 758				if (is_null($returnValue)) {
 759					$returnValue = $arg;
 760				} else {
 761					$returnValue += $arg;
 762				}
 763				++$aCount;
 764			}
 765		}
 766
 767		// Return
 768		if ($aCount > 0) {
 769			return $returnValue / $aCount;
 770		} else {
 771			return PHPExcel_Calculation_Functions::DIV0();
 772		}
 773	}	//	function AVERAGE()
 774
 775
 776	/**
 777	 *	AVERAGEA
 778	 *
 779	 *	Returns the average of its arguments, including numbers, text, and logical values
 780	 *
 781	 *	Excel Function:
 782	 *		AVERAGEA(value1[,value2[, ...]])
 783	 *
 784	 *	@access	public
 785	 *	@category Statistical Functions
 786	 *	@param	mixed		$arg,...		Data values
 787	 *	@return	float
 788	 */
 789	public static function AVERAGEA() {
 790		// Return value
 791		$returnValue = null;
 792
 793		$aCount = 0;
 794		// Loop through arguments
 795		foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
 796			if ((is_bool($arg)) &&
 797				(!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
 798			} else {
 799				if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
 800					if (is_bool($arg)) {
 801						$arg = (integer) $arg;
 802					} elseif (is_string($arg)) {
 803						$arg = 0;
 804					}
 805					if (is_null($returnValue)) {
 806						$returnValue = $arg;
 807					} else {
 808						$returnValue += $arg;
 809					}
 810					++$aCount;
 811				}
 812			}
 813		}
 814
 815		// Return
 816		if ($aCount > 0) {
 817			return $returnValue / $aCount;
 818		} else {
 819			return PHPExcel_Calculation_Functions::DIV0();
 820		}
 821	}	//	function AVERAGEA()
 822
 823
 824	/**
 825	 *	AVERAGEIF
 826	 *
 827	 *	Returns the average value from a range of cells that contain numbers within the list of arguments
 828	 *
 829	 *	Excel Function:
 830	 *		AVERAGEIF(value1[,value2[, ...]],condition)
 831	 *
 832	 *	@access	public
 833	 *	@category Mathematical and Trigonometric Functions
 834	 *	@param	mixed		$arg,...		Data values
 835	 *	@param	string		$condition		The criteria that defines which cells will be checked.
 836	 *	@return	float
 837	 */
 838	public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
 839		// Return value
 840		$returnValue = 0;
 841
 842		$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
 843		$averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
 844		if (count($averageArgs) == 0) {
 845			$averageArgs = $aArgs;
 846		}
 847		$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
 848		// Loop through arguments
 849		$aCount = 0;
 850		foreach ($aArgs as $key => $arg) {
 851			if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
 852			$testCondition = '='.$arg.$condition;
 853			if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
 854				if ((is_null($returnValue)) || ($arg > $returnValue)) {
 855					$returnValue += $arg;
 856					++$aCount;
 857				}
 858			}
 859		}
 860
 861		// Return
 862		if ($aCount > 0) {
 863			return $returnValue / $aCount;
 864		} else {
 865			return PHPExcel_Calculation_Functions::DIV0();
 866		}
 867	}	//	function AVERAGEIF()
 868
 869
 870	/**
 871	 * BETADIST
 872	 *
 873	 * Returns the beta distribution.
 874	 *
 875	 * @param	float		$value			Value at which you want to evaluate the distribution
 876	 * @param	float		$alpha			Parameter to the distribution
 877	 * @param	float		$beta			Parameter to the distribution
 878	 * @param	boolean		$cumulative
 879	 * @return	float
 880	 *
 881	 */
 882	public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
 883		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
 884		$alpha	= PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
 885		$beta	= PHPExcel_Calculation_Functions::flattenSingleValue($beta);
 886		$rMin	= PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
 887		$rMax	= PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
 888
 889		if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
 890			if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
 891				return PHPExcel_Calculation_Functions::NaN();
 892			}
 893			if ($rMin > $rMax) {
 894				$tmp = $rMin;
 895				$rMin = $rMax;
 896				$rMax = $tmp;
 897			}
 898			$value -= $rMin;
 899			$value /= ($rMax - $rMin);
 900			return self::_incompleteBeta($value,$alpha,$beta);
 901		}
 902		return PHPExcel_Calculation_Functions::VALUE();
 903	}	//	function BETADIST()
 904
 905
 906	/**
 907	 * BETAINV
 908	 *
 909	 * Returns the inverse of the beta distribution.
 910	 *
 911	 * @param	float		$probability	Probability at which you want to evaluate the distribution
 912	 * @param	float		$alpha			Parameter to the distribution
 913	 * @param	float		$beta			Parameter to the distribution
 914	 * @param	boolean		$cumulative
 915	 * @return	float
 916	 *
 917	 */
 918	public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
 919		$probability	= PHPExcel_Calculation_Functions::flattenSingleValue($probability);
 920		$alpha			= PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
 921		$beta			= PHPExcel_Calculation_Functions::flattenSingleValue($beta);
 922		$rMin			= PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
 923		$rMax			= PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
 924
 925		if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
 926			if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
 927				return PHPExcel_Calculation_Functions::NaN();
 928			}
 929			if ($rMin > $rMax) {
 930				$tmp = $rMin;
 931				$rMin = $rMax;
 932				$rMax = $tmp;
 933			}
 934			$a = 0;
 935			$b = 2;
 936
 937			$i = 0;
 938			while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
 939				$guess = ($a + $b) / 2;
 940				$result = self::BETADIST($guess, $alpha, $beta);
 941				if (($result == $probability) || ($result == 0)) {
 942					$b = $a;
 943				} elseif ($result > $probability) {
 944					$b = $guess;
 945				} else {
 946					$a = $guess;
 947				}
 948			}
 949			if ($i == MAX_ITERATIONS) {
 950				return PHPExcel_Calculation_Functions::NA();
 951			}
 952			return round($rMin + $guess * ($rMax - $rMin),12);
 953		}
 954		return PHPExcel_Calculation_Functions::VALUE();
 955	}	//	function BETAINV()
 956
 957
 958	/**
 959	 *	BINOMDIST
 960	 *
 961	 *	Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
 962	 *	a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
 963	 *	when trials are independent, and when the probability of success is constant throughout the
 964	 *	experiment. For example, BINOMDIST can calculate the probability that two of the next three
 965	 *	babies born are male.
 966	 *
 967	 *	@param	float		$value			Number of successes in trials
 968	 *	@param	float		$trials			Number of trials
 969	 *	@param	float		$probability	Probability of success on each trial
 970	 *	@param	boolean		$cumulative
 971	 *	@return	float
 972	 *
 973	 *	@todo	Cumulative distribution function
 974	 *
 975	 */
 976	public static function BINOMDIST($value, $trials, $probability, $cumulative) {
 977		$value			= floor(PHPExcel_Calculation_Functions::flattenSingleValue($value));
 978		$trials			= floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
 979		$probability	= PHPExcel_Calculation_Functions::flattenSingleValue($probability);
 980
 981		if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
 982			if (($value < 0) || ($value > $trials)) {
 983				return PHPExcel_Calculation_Functions::NaN();
 984			}
 985			if (($probability < 0) || ($probability > 1)) {
 986				return PHPExcel_Calculation_Functions::NaN();
 987			}
 988			if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
 989				if ($cumulative) {
 990					$summer = 0;
 991					for ($i = 0; $i <= $value; ++$i) {
 992						$summer += PHPExcel_Calculation_MathTrig::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
 993					}
 994					return $summer;
 995				} else {
 996					return PHPExcel_Calculation_MathTrig::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
 997				}
 998			}
 999		}
1000		return PHPExcel_Calculation_Functions::VALUE();
1001	}	//	function BINOMDIST()
1002
1003
1004	/**
1005	 *	CHIDIST
1006	 *
1007	 *	Returns the one-tailed probability of the chi-squared distribution.
1008	 *
1009	 *	@param	float		$value			Value for the function
1010	 *	@param	float		$degrees		degrees of freedom
1011	 *	@return	float
1012	 */
1013	public static function CHIDIST($value, $degrees) {
1014		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
1015		$degrees	= floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1016
1017		if ((is_numeric($value)) && (is_numeric($degrees))) {
1018			if ($degrees < 1) {
1019				return PHPExcel_Calculation_Functions::NaN();
1020			}
1021			if ($value < 0) {
1022				if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
1023					return 1;
1024				}
1025				return PHPExcel_Calculation_Functions::NaN();
1026			}
1027			return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
1028		}
1029		return PHPExcel_Calculation_Functions::VALUE();
1030	}	//	function CHIDIST()
1031
1032
1033	/**
1034	 *	CHIINV
1035	 *
1036	 *	Returns the one-tailed probability of the chi-squared distribution.
1037	 *
1038	 *	@param	float		$probability	Probability for the function
1039	 *	@param	float		$degrees		degrees of freedom
1040	 *	@return	float
1041	 */
1042	public static function CHIINV($probability, $degrees) {
1043		$probability	= PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1044		$degrees		= floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
1045
1046		if ((is_numeric($probability)) && (is_numeric($degrees))) {
1047
1048			$xLo = 100;
1049			$xHi = 0;
1050
1051			$x = $xNew = 1;
1052			$dx	= 1;
1053			$i = 0;
1054
1055			while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1056				// Apply Newton-Raphson step
1057				$result = self::CHIDIST($x, $degrees);
1058				$error = $result - $probability;
1059				if ($error == 0.0) {
1060					$dx = 0;
1061				} elseif ($error < 0.0) {
1062					$xLo = $x;
1063				} else {
1064					$xHi = $x;
1065				}
1066				// Avoid division by zero
1067				if ($result != 0.0) {
1068					$dx = $error / $result;
1069					$xNew = $x - $dx;
1070				}
1071				// If the NR fails to converge (which for example may be the
1072				// case if the initial guess is too rough) we apply a bisection
1073				// step to determine a more narrow interval around the root.
1074				if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
1075					$xNew = ($xLo + $xHi) / 2;
1076					$dx = $xNew - $x;
1077				}
1078				$x = $xNew;
1079			}
1080			if ($i == MAX_ITERATIONS) {
1081				return PHPExcel_Calculation_Functions::NA();
1082			}
1083			return round($x,12);
1084		}
1085		return PHPExcel_Calculation_Functions::VALUE();
1086	}	//	function CHIINV()
1087
1088
1089	/**
1090	 * CONFIDENCE
1091	 *
1092	 * Returns the confidence interval for a population mean
1093	 *
1094	 * @param	float		$alpha
1095	 * @param	float		$stdDev		Standard Deviation
1096	 * @param	float		$size
1097	 * @return	float
1098	 *
1099	 */
1100	public static function CONFIDENCE($alpha,$stdDev,$size) {
1101		$alpha	= PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1102		$stdDev	= PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
1103		$size	= floor(PHPExcel_Calculation_Functions::flattenSingleValue($size));
1104
1105		if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
1106			if (($alpha <= 0) || ($alpha >= 1)) {
1107				return PHPExcel_Calculation_Functions::NaN();
1108			}
1109			if (($stdDev <= 0) || ($size < 1)) {
1110				return PHPExcel_Calculation_Functions::NaN();
1111			}
1112			return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
1113		}
1114		return PHPExcel_Calculation_Functions::VALUE();
1115	}	//	function CONFIDENCE()
1116
1117
1118	/**
1119	 *	CORREL
1120	 *
1121	 *	Returns covariance, the average of the products of deviations for each data point pair.
1122	 *
1123	 *	@param	array of mixed		Data Series Y
1124	 *	@param	array of mixed		Data Series X
1125	 *	@return	float
1126	 */
1127	public static function CORREL($yValues,$xValues=null) {
1128		if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
1129			return PHPExcel_Calculation_Functions::VALUE();
1130		}
1131		if (!self::_checkTrendArrays($yValues,$xValues)) {
1132			return PHPExcel_Calculation_Functions::VALUE();
1133		}
1134		$yValueCount = count($yValues);
1135		$xValueCount = count($xValues);
1136
1137		if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1138			return PHPExcel_Calculation_Functions::NA();
1139		} elseif ($yValueCount == 1) {
1140			return PHPExcel_Calculation_Functions::DIV0();
1141		}
1142
1143		$bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1144		return $bestFitLinear->getCorrelation();
1145	}	//	function CORREL()
1146
1147
1148	/**
1149	 *	COUNT
1150	 *
1151	 *	Counts the number of cells that contain numbers within the list of arguments
1152	 *
1153	 *	Excel Function:
1154	 *		COUNT(value1[,value2[, ...]])
1155	 *
1156	 *	@access	public
1157	 *	@category Statistical Functions
1158	 *	@param	mixed		$arg,...		Data values
1159	 *	@return	int
1160	 */
1161	public static function COUNT() {
1162		// Return value
1163		$returnValue = 0;
1164
1165		// Loop through arguments
1166		$aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1167		foreach ($aArgs as $k => $arg) {
1168			if ((is_bool($arg)) &&
1169				((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
1170				$arg = (integer) $arg;
1171			}
1172			// Is it a numeric value?
1173			if ((is_numeric($arg)) && (!is_string($arg))) {
1174				++$returnValue;
1175			}
1176		}
1177
1178		// Return
1179		return $returnValue;
1180	}	//	function COUNT()
1181
1182
1183	/**
1184	 *	COUNTA
1185	 *
1186	 *	Counts the number of cells that are not empty within the list of arguments
1187	 *
1188	 *	Excel Function:
1189	 *		COUNTA(value1[,value2[, ...]])
1190	 *
1191	 *	@access	public
1192	 *	@category Statistical Functions
1193	 *	@param	mixed		$arg,...		Data values
1194	 *	@return	int
1195	 */
1196	public static function COUNTA() {
1197		// Return value
1198		$returnValue = 0;
1199
1200		// Loop through arguments
1201		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1202		foreach ($aArgs as $arg) {
1203			// Is it a numeric, boolean or string value?
1204			if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1205				++$returnValue;
1206			}
1207		}
1208
1209		// Return
1210		return $returnValue;
1211	}	//	function COUNTA()
1212
1213
1214	/**
1215	 *	COUNTBLANK
1216	 *
1217	 *	Counts the number of empty cells within the list of arguments
1218	 *
1219	 *	Excel Function:
1220	 *		COUNTBLANK(value1[,value2[, ...]])
1221	 *
1222	 *	@access	public
1223	 *	@category Statistical Functions
1224	 *	@param	mixed		$arg,...		Data values
1225	 *	@return	int
1226	 */
1227	public static function COUNTBLANK() {
1228		// Return value
1229		$returnValue = 0;
1230
1231		// Loop through arguments
1232		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1233		foreach ($aArgs as $arg) {
1234			// Is it a blank cell?
1235			if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
1236				++$returnValue;
1237			}
1238		}
1239
1240		// Return
1241		return $returnValue;
1242	}	//	function COUNTBLANK()
1243
1244
1245	/**
1246	 *	COUNTIF
1247	 *
1248	 *	Counts the number of cells that contain numbers within the list of arguments
1249	 *
1250	 *	Excel Function:
1251	 *		COUNTIF(value1[,value2[, ...]],condition)
1252	 *
1253	 *	@access	public
1254	 *	@category Statistical Functions
1255	 *	@param	mixed		$arg,...		Data values
1256	 *	@param	string		$condition		The criteria that defines which cells will be counted.
1257	 *	@return	int
1258	 */
1259	public static function COUNTIF($aArgs,$condition) {
1260		// Return value
1261		$returnValue = 0;
1262
1263		$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
1264		$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1265		// Loop through arguments
1266		foreach ($aArgs as $arg) {
1267			if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1268			$testCondition = '='.$arg.$condition;
1269			if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1270				// Is it a value within our criteria
1271				++$returnValue;
1272			}
1273		}
1274
1275		// Return
1276		return $returnValue;
1277	}	//	function COUNTIF()
1278
1279
1280	/**
1281	 *	COVAR
1282	 *
1283	 *	Returns covariance, the average of the products of deviations for each data point pair.
1284	 *
1285	 *	@param	array of mixed		Data Series Y
1286	 *	@param	array of mixed		Data Series X
1287	 *	@return	float
1288	 */
1289	public static function COVAR($yValues,$xValues) {
1290		if (!self::_checkTrendArrays($yValues,$xValues)) {
1291			return PHPExcel_Calculation_Functions::VALUE();
1292		}
1293		$yValueCount = count($yValues);
1294		$xValueCount = count($xValues);
1295
1296		if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1297			return PHPExcel_Calculation_Functions::NA();
1298		} elseif ($yValueCount == 1) {
1299			return PHPExcel_Calculation_Functions::DIV0();
1300		}
1301
1302		$bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1303		return $bestFitLinear->getCovariance();
1304	}	//	function COVAR()
1305
1306
1307	/**
1308	 *	CRITBINOM
1309	 *
1310	 *	Returns the smallest value for which the cumulative binomial distribution is greater
1311	 *	than or equal to a criterion value
1312	 *
1313	 *	See http://support.microsoft.com/kb/828117/ for details of the algorithm used
1314	 *
1315	 *	@param	float		$trials			number of Bernoulli trials
1316	 *	@param	float		$probability	probability of a success on each trial
1317	 *	@param	float		$alpha			criterion value
1318	 *	@return	int
1319	 *
1320	 *	@todo	Warning. This implementation differs from the algorithm detailed on the MS
1321	 *			web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
1322	 *			This eliminates a potential endless loop error, but may have an adverse affect on the
1323	 *			accuracy of the function (although all my tests have so far returned correct results).
1324	 *
1325	 */
1326	public static function CRITBINOM($trials, $probability, $alpha) {
1327		$trials			= floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
1328		$probability	= PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1329		$alpha			= PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1330
1331		if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
1332			if ($trials < 0) {
1333				return PHPExcel_Calculation_Functions::NaN();
1334			}
1335			if (($probability < 0) || ($probability > 1)) {
1336				return PHPExcel_Calculation_Functions::NaN();
1337			}
1338			if (($alpha < 0) || ($alpha > 1)) {
1339				return PHPExcel_Calculation_Functions::NaN();
1340			}
1341			if ($alpha <= 0.5) {
1342				$t = sqrt(log(1 / ($alpha * $alpha)));
1343				$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));
1344			} else {
1345				$t = sqrt(log(1 / pow(1 - $alpha,2)));
1346				$trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
1347			}
1348			$Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
1349			if ($Guess < 0) {
1350				$Guess = 0;
1351			} elseif ($Guess > $trials) {
1352				$Guess = $trials;
1353			}
1354
1355			$TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
1356			$EssentiallyZero = 10e-12;
1357
1358			$m = floor($trials * $probability);
1359			++$TotalUnscaledProbability;
1360			if ($m == $Guess) { ++$UnscaledPGuess; }
1361			if ($m <= $Guess) { ++$UnscaledCumPGuess; }
1362
1363			$PreviousValue = 1;
1364			$Done = False;
1365			$k = $m + 1;
1366			while ((!$Done) && ($k <= $trials)) {
1367				$CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
1368				$TotalUnscaledProbability += $CurrentValue;
1369				if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1370				if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1371				if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1372				$PreviousValue = $CurrentValue;
1373				++$k;
1374			}
1375
1376			$PreviousValue = 1;
1377			$Done = False;
1378			$k = $m - 1;
1379			while ((!$Done) && ($k >= 0)) {
1380				$CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
1381				$TotalUnscaledProbability += $CurrentValue;
1382				if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1383				if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1384				if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1385				$PreviousValue = $CurrentValue;
1386				--$k;
1387			}
1388
1389			$PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
1390			$CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
1391
1392//			$CumPGuessMinus1 = $CumPGuess - $PGuess;
1393			$CumPGuessMinus1 = $CumPGuess - 1;
1394
1395			while (True) {
1396				if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
1397					return $Guess;
1398				} elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
1399					$PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
1400					$CumPGuessMinus1 = $CumPGuess;
1401					$CumPGuess = $CumPGuess + $PGuessPlus1;
1402					$PGuess = $PGuessPlus1;
1403					++$Guess;
1404				} elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
1405					$PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
1406					$CumPGuess = $CumPGuessMinus1;
1407					$CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
1408					$PGuess = $PGuessMinus1;
1409					--$Guess;
1410				}
1411			}
1412		}
1413		return PHPExcel_Calculation_Functions::VALUE();
1414	}	//	function CRITBINOM()
1415
1416
1417	/**
1418	 *	DEVSQ
1419	 *
1420	 *	Returns the sum of squares of deviations of data points from their sample mean.
1421	 *
1422	 *	Excel Function:
1423	 *		DEVSQ(value1[,value2[, ...]])
1424	 *
1425	 *	@access	public
1426	 *	@category Statistical Functions
1427	 *	@param	mixed		$arg,...		Data values
1428	 *	@return	float
1429	 */
1430	public static function DEVSQ() {
1431		$aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
1432
1433		// Return value
1434		$returnValue = null;
1435
1436		$aMean = self::AVERAGE($aArgs);
1437		if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
1438			$aCount = -1;
1439			foreach ($aArgs as $k => $arg) {
1440				// Is it a numeric value?
1441				if ((is_bool($arg)) &&
1442					((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
1443					$arg = (integer) $arg;
1444				}
1445				if ((is_numeric($arg)) && (!is_string($arg))) {
1446					if (is_null($returnValue)) {
1447						$returnValue = pow(($arg - $aMean),2);
1448					} else {
1449						$returnValue += pow(($arg - $aMean),2);
1450					}
1451					++$aCount;
1452				}
1453			}
1454
1455			// Return
1456			if (is_null($returnValue)) {
1457				return PHPExcel_Calculation_Functions::NaN();
1458			} else {
1459				return $returnValue;
1460			}
1461		}
1462		return self::NA();
1463	}	//	function DEVSQ()
1464
1465
1466	/**
1467	 *	EXPONDIST
1468	 *
1469	 *	Returns the exponential distribution. Use EXPONDIST to model the time between events,
1470	 *	such as how long an automated bank teller takes to deliver cash. For example, you can
1471	 *	use EXPONDIST to determine the probability that the process takes at most 1 minute.
1472	 *
1473	 *	@param	float		$value			Value of the function
1474	 *	@param	float		$lambda			The parameter value
1475	 *	@param	boolean		$cumulative
1476	 *	@return	float
1477	 */
1478	public static function EXPONDIST($value, $lambda, $cumulative) {
1479		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
1480		$lambda	= PHPExcel_Calculation_Functions::flattenSingleValue($lambda);
1481		$cumulative	= PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
1482
1483		if ((is_numeric($value)) && (is_numeric($lambda))) {
1484			if (($value < 0) || ($lambda < 0)) {
1485				return PHPExcel_Calculation_Functions::NaN();
1486			}
1487			if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1488				if ($cumulative) {
1489					return 1 - exp(0-$value*$lambda);
1490				} else {
1491					return $lambda * exp(0-$value*$lambda);
1492				}
1493			}
1494		}
1495		return PHPExcel_Calculation_Functions::VALUE();
1496	}	//	function EXPONDIST()
1497
1498
1499	/**
1500	 *	FISHER
1501	 *
1502	 *	Returns the Fisher transformation at x. This transformation produces a function that
1503	 *	is normally distributed rather than skewed. Use this function to perform hypothesis
1504	 *	testing on the correlation coefficient.
1505	 *
1506	 *	@param	float		$value
1507	 *	@return	float
1508	 */
1509	public static function FISHER($value) {
1510		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
1511
1512		if (is_numeric($value)) {
1513			if (($value <= -1) || ($value >= 1)) {
1514				return PHPExcel_Calculation_Functions::NaN();
1515			}
1516			return 0.5 * log((1+$value)/(1-$value));
1517		}
1518		return PHPExcel_Calculation_Functions::VALUE();
1519	}	//	function FISHER()
1520
1521
1522	/**
1523	 *	FISHERINV
1524	 *
1525	 *	Returns the inverse of the Fisher transformation. Use this transformation when
1526	 *	analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
1527	 *	FISHERINV(y) = x.
1528	 *
1529	 *	@param	float		$value
1530	 *	@return	float
1531	 */
1532	public static function FISHERINV($value) {
1533		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
1534
1535		if (is_numeric($value)) {
1536			return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
1537		}
1538		return PHPExcel_Calculation_Functions::VALUE();
1539	}	//	function FISHERINV()
1540
1541
1542	/**
1543	 *	FORECAST
1544	 *
1545	 *	Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.
1546	 *
1547	 *	@param	float				Value of X for which we want to find Y
1548	 *	@param	array of mixed		Data Series Y
1549	 *	@param	array of mixed		Data Series X
1550	 *	@return	float
1551	 */
1552	public static function FORECAST($xValue,$yValues,$xValues) {
1553		$xValue	= PHPExcel_Calculation_Functions::flattenSingleValue($xValue);
1554		if (!is_numeric($xValue)) {
1555			return PHPExcel_Calculation_Functions::VALUE();
1556		}
1557
1558		if (!self::_checkTrendArrays($yValues,$xValues)) {
1559			return PHPExcel_Calculation_Functions::VALUE();
1560		}
1561		$yValueCount = count($yValues);
1562		$xValueCount = count($xValues);
1563
1564		if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1565			return PHPExcel_Calculation_Functions::NA();
1566		} elseif ($yValueCount == 1) {
1567			return PHPExcel_Calculation_Functions::DIV0();
1568		}
1569
1570		$bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
1571		return $bestFitLinear->getValueOfYForX($xValue);
1572	}	//	function FORECAST()
1573
1574
1575	/**
1576	 * GAMMADIST
1577	 *
1578	 * Returns the gamma distribution.
1579	 *
1580	 * @param	float		$value			Value at which you want to evaluate the distribution
1581	 * @param	float		$a				Parameter to the distribution
1582	 * @param	float		$b				Parameter to the distribution
1583	 * @param	boolean		$cumulative
1584	 * @return	float
1585	 *
1586	 */
1587	public static function GAMMADIST($value,$a,$b,$cumulative) {
1588		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
1589		$a		= PHPExcel_Calculation_Functions::flattenSingleValue($a);
1590		$b		= PHPExcel_Calculation_Functions::flattenSingleValue($b);
1591
1592		if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
1593			if (($value < 0) || ($a <= 0) || ($b <= 0)) {
1594				return PHPExcel_Calculation_Functions::NaN();
1595			}
1596			if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
1597				if ($cumulative) {
1598					return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
1599				} else {
1600					return (1 / (pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
1601				}
1602			}
1603		}
1604		return PHPExcel_Calculation_Functions::VALUE();
1605	}	//	function GAMMADIST()
1606
1607
1608	/**
1609	 * GAMMAINV
1610	 *
1611	 * Returns the inverse of the beta distribution.
1612	 *
1613	 * @param	float		$probability	Probability at which you want to evaluate the distribution
1614	 * @param	float		$alpha			Parameter to the distribution
1615	 * @param	float		$beta			Parameter to the distribution
1616	 * @return	float
1617	 *
1618	 */
1619	public static function GAMMAINV($probability,$alpha,$beta) {
1620		$probability	= PHPExcel_Calculation_Functions::flattenSingleValue($probability);
1621		$alpha			= PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
1622		$beta			= PHPExcel_Calculation_Functions::flattenSingleValue($beta);
1623
1624		if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
1625			if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
1626				return PHPExcel_Calculation_Functions::NaN();
1627			}
1628
1629			$xLo = 0;
1630			$xHi…

Large files files are truncated, but you can click here to view the full file