PageRenderTime 126ms CodeModel.GetById 11ms app.highlight 61ms RepoModel.GetById 1ms app.codeStats 5ms

/branches/v1.7.2/Classes/PHPExcel/Calculation/Functions.php

#
PHP | 11480 lines | 6870 code | 1305 blank | 3305 comment | 2002 complexity | 02619cf6f0e9b2a50f9d61189a94a5ae 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 - 2010 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 - 2010 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/** EPS */
  30define('EPS', 2.22e-16);
  31
  32/** MAX_VALUE */
  33define('MAX_VALUE', 1.2e308);
  34
  35/** LOG_GAMMA_X_MAX_VALUE */
  36define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
  37
  38/** SQRT2PI */
  39define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
  40
  41/** 2 / PI */
  42define('M_2DIVPI', 0.63661977236758134307553505349006);
  43
  44/** XMININ */
  45define('XMININ', 2.23e-308);
  46
  47/** MAX_ITERATIONS */
  48define('MAX_ITERATIONS', 256);
  49/** FINANCIAL_MAX_ITERATIONS */
  50define('FINANCIAL_MAX_ITERATIONS', 128);
  51
  52/** PRECISION */
  53define('PRECISION', 8.88E-016);
  54/** FINANCIAL_PRECISION */
  55define('FINANCIAL_PRECISION', 1.0e-08);
  56
  57/** EULER */
  58define('EULER', 2.71828182845904523536);
  59
  60$savedPrecision = ini_get('precision');
  61if ($savedPrecision < 16) {
  62	ini_set('precision',16);
  63}
  64
  65
  66/** PHPExcel root directory */
  67if (!defined('PHPEXCEL_ROOT')) {
  68	/**
  69	 * @ignore
  70	 */
  71	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  72}
  73
  74/** PHPExcel_Cell */
  75require_once PHPEXCEL_ROOT . 'PHPExcel/Cell.php';
  76
  77/** PHPExcel_Calculation */
  78require_once PHPEXCEL_ROOT . 'PHPExcel/Calculation.php';
  79
  80/** PHPExcel_Cell_DataType */
  81require_once PHPEXCEL_ROOT . 'PHPExcel/Cell/DataType.php';
  82
  83/** PHPExcel_Style_NumberFormat */
  84require_once PHPEXCEL_ROOT . 'PHPExcel/Style/NumberFormat.php';
  85
  86/** PHPExcel_Shared_Date */
  87require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Date.php';
  88
  89/** Matrix */
  90require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/JAMA/Matrix.php';
  91require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
  92
  93
  94/**
  95 * PHPExcel_Calculation_Functions
  96 *
  97 * @category	PHPExcel
  98 * @package		PHPExcel_Calculation
  99 * @copyright	Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
 100 */
 101class PHPExcel_Calculation_Functions {
 102
 103	/** constants */
 104	const COMPATIBILITY_EXCEL		= 'Excel';
 105	const COMPATIBILITY_GNUMERIC	= 'Gnumeric';
 106	const COMPATIBILITY_OPENOFFICE	= 'OpenOfficeCalc';
 107
 108	const RETURNDATE_PHP_NUMERIC	= 'P';
 109	const RETURNDATE_PHP_OBJECT		= 'O';
 110	const RETURNDATE_EXCEL			= 'E';
 111
 112
 113	/**
 114	 *	Compatibility mode to use for error checking and responses
 115	 *
 116	 *	@access	private
 117	 *	@var string
 118	 */
 119	private static $compatibilityMode	= self::COMPATIBILITY_EXCEL;
 120
 121	/**
 122	 *	Data Type to use when returning date values
 123	 *
 124	 *	@access	private
 125	 *	@var integer
 126	 */
 127	private static $ReturnDateType	= self::RETURNDATE_EXCEL;
 128
 129	/**
 130	 *	List of error codes
 131	 *
 132	 *	@access	private
 133	 *	@var array
 134	 */
 135	private static $_errorCodes	= array( 'null'				=> '#NULL!',
 136										 'divisionbyzero'	=> '#DIV/0!',
 137										 'value'			=> '#VALUE!',
 138										 'reference'		=> '#REF!',
 139										 'name'				=> '#NAME?',
 140										 'num'				=> '#NUM!',
 141										 'na'				=> '#N/A',
 142										 'gettingdata'		=> '#GETTING_DATA'
 143									   );
 144
 145
 146	/**
 147	 *	Set the Compatibility Mode
 148	 *
 149	 *	@access	public
 150	 *	@category Function Configuration
 151	 *	@param	 string		$compatibilityMode		Compatibility Mode
 152	 *												Permitted values are:
 153	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL			'Excel'
 154	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC		'Gnumeric'
 155	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE	'OpenOfficeCalc'
 156	 *	@return	 boolean	(Success or Failure)
 157	 */
 158	public static function setCompatibilityMode($compatibilityMode) {
 159		if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
 160			($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
 161			($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
 162			self::$compatibilityMode = $compatibilityMode;
 163			return True;
 164		}
 165		return False;
 166	}	//	function setCompatibilityMode()
 167
 168
 169	/**
 170	 *	Return the current Compatibility Mode
 171	 *
 172	 *	@access	public
 173	 *	@category Function Configuration
 174	 *	@return	 string		Compatibility Mode
 175	 *							Possible Return values are:
 176	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL			'Excel'
 177	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC		'Gnumeric'
 178	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE	'OpenOfficeCalc'
 179	 */
 180	public static function getCompatibilityMode() {
 181		return self::$compatibilityMode;
 182	}	//	function getCompatibilityMode()
 183
 184
 185	/**
 186	 *	Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
 187	 *
 188	 *	@access	public
 189	 *	@category Function Configuration
 190	 *	@param	 string	$returnDateType			Return Date Format
 191	 *												Permitted values are:
 192	 *													PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC		'P'
 193	 *													PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT		'O'
 194	 *													PHPExcel_Calculation_Functions::RETURNDATE_EXCEL			'E'
 195	 *	@return	 boolean							Success or failure
 196	 */
 197	public static function setReturnDateType($returnDateType) {
 198		if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
 199			($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
 200			($returnDateType == self::RETURNDATE_EXCEL)) {
 201			self::$ReturnDateType = $returnDateType;
 202			return True;
 203		}
 204		return False;
 205	}	//	function setReturnDateType()
 206
 207
 208	/**
 209	 *	Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
 210	 *
 211	 *	@access	public
 212	 *	@category Function Configuration
 213	 *	@return	 string		Return Date Format
 214	 *							Possible Return values are:
 215	 *								PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC		'P'
 216	 *								PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT		'O'
 217	 *								PHPExcel_Calculation_Functions::RETURNDATE_EXCEL			'E'
 218	 */
 219	public static function getReturnDateType() {
 220		return self::$ReturnDateType;
 221	}	//	function getReturnDateType()
 222
 223
 224	/**
 225	 *	DUMMY
 226	 *
 227	 *	@access	public
 228	 *	@category Error Returns
 229	 *	@return	string	#Not Yet Implemented
 230	 */
 231	public static function DUMMY() {
 232		return '#Not Yet Implemented';
 233	}	//	function DUMMY()
 234
 235
 236	/**
 237	 *	NA
 238	 *
 239	 *	@access	public
 240	 *	@category Error Returns
 241	 *	@return	string	#N/A!
 242	 */
 243	public static function NA() {
 244		return self::$_errorCodes['na'];
 245	}	//	function NA()
 246
 247
 248	/**
 249	 *	NAN
 250	 *
 251	 *	@access	public
 252	 *	@category Error Returns
 253	 *	@return	string	#NUM!
 254	 */
 255	public static function NaN() {
 256		return self::$_errorCodes['num'];
 257	}	//	function NAN()
 258
 259
 260	/**
 261	 *	NAME
 262	 *
 263	 *	@access	public
 264	 *	@category Error Returns
 265	 *	@return	string	#NAME!
 266	 */
 267	public static function NAME() {
 268		return self::$_errorCodes['name'];
 269	}	//	function NAME()
 270
 271
 272	/**
 273	 *	REF
 274	 *
 275	 *	@access	public
 276	 *	@category Error Returns
 277	 *	@return	string	#REF!
 278	 */
 279	public static function REF() {
 280		return self::$_errorCodes['reference'];
 281	}	//	function REF()
 282
 283
 284	/**
 285	 *	VALUE
 286	 *
 287	 *	@access	public
 288	 *	@category Error Returns
 289	 *	@return	string	#VALUE!
 290	 */
 291	public static function VALUE() {
 292		return self::$_errorCodes['value'];
 293	}	//	function VALUE()
 294
 295
 296	private static function isMatrixValue($idx) {
 297		return ((substr_count($idx,'.') <= 1) || (preg_match('/\.[A-Z]/',$idx) > 0));
 298	}
 299
 300
 301	private static function isValue($idx) {
 302		return (substr_count($idx,'.') == 0);
 303	}
 304
 305
 306	private static function isCellValue($idx) {
 307		return (substr_count($idx,'.') > 1);
 308	}
 309
 310
 311	/**
 312	 *	LOGICAL_AND
 313	 *
 314	 *	Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
 315	 *
 316	 *	Excel Function:
 317	 *		=AND(logical1[,logical2[, ...]])
 318	 *
 319	 *		The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
 320	 *			or references that contain logical values.
 321	 *
 322	 *		Boolean arguments are treated as True or False as appropriate
 323	 *		Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 324	 *		If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 325	 *			the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 326	 *
 327	 *	@access	public
 328	 *	@category Logical Functions
 329	 *	@param	mixed		$arg,...		Data values
 330	 *	@return	boolean		The logical AND of the arguments.
 331	 */
 332	public static function LOGICAL_AND() {
 333		// Return value
 334		$returnValue = True;
 335
 336		// Loop through the arguments
 337		$aArgs = self::flattenArray(func_get_args());
 338		$argCount = 0;
 339		foreach ($aArgs as $arg) {
 340			// Is it a boolean value?
 341			if (is_bool($arg)) {
 342				$returnValue = $returnValue && $arg;
 343			} elseif ((is_numeric($arg)) && (!is_string($arg))) {
 344				$returnValue = $returnValue && ($arg != 0);
 345			} elseif (is_string($arg)) {
 346				$arg = strtoupper($arg);
 347				if ($arg == 'TRUE') {
 348					$arg = 1;
 349				} elseif ($arg == 'FALSE') {
 350					$arg = 0;
 351				} else {
 352					return self::$_errorCodes['value'];
 353				}
 354				$returnValue = $returnValue && ($arg != 0);
 355			}
 356			++$argCount;
 357		}
 358
 359		// Return
 360		if ($argCount == 0) {
 361			return self::$_errorCodes['value'];
 362		}
 363		return $returnValue;
 364	}	//	function LOGICAL_AND()
 365
 366
 367	/**
 368	 *	LOGICAL_OR
 369	 *
 370	 *	Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
 371	 *
 372	 *	Excel Function:
 373	 *		=OR(logical1[,logical2[, ...]])
 374	 *
 375	 *		The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
 376	 *			or references that contain logical values.
 377	 *
 378	 *		Boolean arguments are treated as True or False as appropriate
 379	 *		Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 380	 *		If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 381	 *			the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 382	 *
 383	 *	@access	public
 384	 *	@category Logical Functions
 385	 *	@param	mixed		$arg,...		Data values
 386	 *	@return	boolean		The logical OR of the arguments.
 387	 */
 388	public static function LOGICAL_OR() {
 389		// Return value
 390		$returnValue = False;
 391
 392		// Loop through the arguments
 393		$aArgs = self::flattenArray(func_get_args());
 394		$argCount = 0;
 395		foreach ($aArgs as $arg) {
 396			// Is it a boolean value?
 397			if (is_bool($arg)) {
 398				$returnValue = $returnValue || $arg;
 399			} elseif ((is_numeric($arg)) && (!is_string($arg))) {
 400				$returnValue = $returnValue || ($arg != 0);
 401			} elseif (is_string($arg)) {
 402				$arg = strtoupper($arg);
 403				if ($arg == 'TRUE') {
 404					$arg = 1;
 405				} elseif ($arg == 'FALSE') {
 406					$arg = 0;
 407				} else {
 408					return self::$_errorCodes['value'];
 409				}
 410				$returnValue = $returnValue || ($arg != 0);
 411			}
 412			++$argCount;
 413		}
 414
 415		// Return
 416		if ($argCount == 0) {
 417			return self::$_errorCodes['value'];
 418		}
 419		return $returnValue;
 420	}	//	function LOGICAL_OR()
 421
 422
 423	/**
 424	 *	LOGICAL_FALSE
 425	 *
 426	 *	Returns the boolean FALSE.
 427	 *
 428	 *	Excel Function:
 429	 *		=FALSE()
 430	 *
 431	 *	@access	public
 432	 *	@category Logical Functions
 433	 *	@return	boolean		False
 434	 */
 435	public static function LOGICAL_FALSE() {
 436		return False;
 437	}	//	function LOGICAL_FALSE()
 438
 439
 440	/**
 441	 *	LOGICAL_TRUE
 442	 *
 443	 *	Returns the boolean TRUE.
 444	 *
 445	 *	Excel Function:
 446	 *		=TRUE()
 447	 *
 448	 *	@access	public
 449	 *	@category Logical Functions
 450	 *	@return	boolean		True
 451	 */
 452	public static function LOGICAL_TRUE() {
 453		return True;
 454	}	//	function LOGICAL_TRUE()
 455
 456
 457	/**
 458	 *	LOGICAL_NOT
 459	 *
 460	 *	Returns the boolean inverse of the argument.
 461	 *
 462	 *	Excel Function:
 463	 *		=NOT(logical)
 464	 *
 465	 *		The argument must evaluate to a logical value such as TRUE or FALSE
 466	 *
 467	 *		Boolean arguments are treated as True or False as appropriate
 468	 *		Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 469	 *		If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 470	 *			the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 471	 *
 472	 *	@access	public
 473	 *	@category Logical Functions
 474	 *	@param	mixed		$logical	A value or expression that can be evaluated to TRUE or FALSE
 475	 *	@return	boolean		The boolean inverse of the argument.
 476	 */
 477	public static function LOGICAL_NOT($logical) {
 478		$logical = self::flattenSingleValue($logical);
 479		if (is_string($logical)) {
 480			$logical = strtoupper($logical);
 481			if ($logical == 'TRUE') {
 482				return False;
 483			} elseif ($logical == 'FALSE') {
 484				return True;
 485			} else {
 486				return self::$_errorCodes['value'];
 487			}
 488		}
 489
 490		return !$logical;
 491	}	//	function LOGICAL_NOT()
 492
 493
 494	/**
 495	 *	STATEMENT_IF
 496	 *
 497	 *	Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
 498	 *
 499	 *	Excel Function:
 500	 *		=IF(condition[,returnIfTrue[,returnIfFalse]])
 501	 *
 502	 *		Condition is any value or expression that can be evaluated to TRUE or FALSE.
 503	 *			For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
 504	 *			the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
 505	 *			This argument can use any comparison calculation operator.
 506	 *		ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
 507	 *			For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE,
 508	 *			then the IF function returns the text "Within budget"
 509	 *			If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use
 510	 *			the logical value TRUE for this argument.
 511	 *			ReturnIfTrue can be another formula.
 512	 *		ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
 513	 *			For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE,
 514	 *			then the IF function returns the text "Over budget".
 515	 *			If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
 516	 *			If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
 517	 *			ReturnIfFalse can be another formula.
 518	 *
 519	 *	@access	public
 520	 *	@category Logical Functions
 521	 *	@param	mixed	$condition		Condition to evaluate
 522	 *	@param	mixed	$returnIfTrue	Value to return when condition is true
 523	 *	@param	mixed	$returnIfFalse	Optional value to return when condition is false
 524	 *	@return	mixed	The value of returnIfTrue or returnIfFalse determined by condition
 525	 */
 526	public static function STATEMENT_IF($condition = true, $returnIfTrue = 0, $returnIfFalse = False) {
 527		$condition		= (is_null($condition))		? True :	(boolean) self::flattenSingleValue($condition);
 528		$returnIfTrue	= (is_null($returnIfTrue))	? 0 :		self::flattenSingleValue($returnIfTrue);
 529		$returnIfFalse	= (is_null($returnIfFalse))	? False :	self::flattenSingleValue($returnIfFalse);
 530
 531		return ($condition ? $returnIfTrue : $returnIfFalse);
 532	}	//	function STATEMENT_IF()
 533
 534
 535	/**
 536	 *	STATEMENT_IFERROR
 537	 *
 538	 *	Excel Function:
 539	 *		=IFERROR(testValue,errorpart)
 540	 *
 541	 *	@access	public
 542	 *	@category Logical Functions
 543	 *	@param	mixed	$testValue	Value to check, is also the value returned when no error
 544	 *	@param	mixed	$errorpart	Value to return when testValue is an error condition
 545	 *	@return	mixed	The value of errorpart or testValue determined by error condition
 546	 */
 547	public static function STATEMENT_IFERROR($testValue = '', $errorpart = '') {
 548		$testValue	= (is_null($testValue))	? '' :	self::flattenSingleValue($testValue);
 549		$errorpart	= (is_null($errorpart))	? '' :	self::flattenSingleValue($errorpart);
 550
 551		return self::STATEMENT_IF(self::IS_ERROR($testValue), $errorpart, $testValue);
 552	}	//	function STATEMENT_IFERROR()
 553
 554
 555	/**
 556	 *	ATAN2
 557	 *
 558	 *	This function calculates the arc tangent of the two variables x and y. It is similar to
 559	 *		calculating the arc tangent of y ÷ x, except that the signs of both arguments are used
 560	 *		to determine the quadrant of the result.
 561	 *	The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a
 562	 *		point with coordinates (xCoordinate, yCoordinate). The angle is given in radians between
 563	 *		-pi and pi, excluding -pi.
 564	 *
 565	 *	Note that the Excel ATAN2() function accepts its arguments in the reverse order to the standard
 566	 *		PHP atan2() function, so we need to reverse them here before calling the PHP atan() function.
 567	 *
 568	 *	Excel Function:
 569	 *		ATAN2(xCoordinate,yCoordinate)
 570	 *
 571	 *	@access	public
 572	 *	@category Mathematical and Trigonometric Functions
 573	 *	@param	float	$xCoordinate		The x-coordinate of the point.
 574	 *	@param	float	$yCoordinate		The y-coordinate of the point.
 575	 *	@return	float	The inverse tangent of the specified x- and y-coordinates.
 576	 */
 577	public static function REVERSE_ATAN2($xCoordinate, $yCoordinate) {
 578		$xCoordinate	= (float) self::flattenSingleValue($xCoordinate);
 579		$yCoordinate	= (float) self::flattenSingleValue($yCoordinate);
 580
 581		if (($xCoordinate == 0) && ($yCoordinate == 0)) {
 582			return self::$_errorCodes['divisionbyzero'];
 583		}
 584
 585		return atan2($yCoordinate, $xCoordinate);
 586	}	//	function REVERSE_ATAN2()
 587
 588
 589	/**
 590	 *	LOG_BASE
 591	 *
 592	 *	Returns the logarithm of a number to a specified base. The default base is 10.
 593	 *
 594	 *	Excel Function:
 595	 *		LOG(number[,base])
 596	 *
 597	 *	@access	public
 598	 *	@category Mathematical and Trigonometric Functions
 599	 *	@param	float	$value		The positive real number for which you want the logarithm
 600	 *	@param	float	$base		The base of the logarithm. If base is omitted, it is assumed to be 10.
 601	 *	@return	float
 602	 */
 603	public static function LOG_BASE($number, $base=10) {
 604		$number	= self::flattenSingleValue($number);
 605		$base	= (is_null($base))	? 10 :	(float) self::flattenSingleValue($base);
 606
 607		return log($number, $base);
 608	}	//	function LOG_BASE()
 609
 610
 611	/**
 612	 *	SUM
 613	 *
 614	 *	SUM computes the sum of all the values and cells referenced in the argument list.
 615	 *
 616	 *	Excel Function:
 617	 *		SUM(value1[,value2[, ...]])
 618	 *
 619	 *	@access	public
 620	 *	@category Mathematical and Trigonometric Functions
 621	 *	@param	mixed		$arg,...		Data values
 622	 *	@return	float
 623	 */
 624	public static function SUM() {
 625		// Return value
 626		$returnValue = 0;
 627
 628		// Loop through the arguments
 629		$aArgs = self::flattenArray(func_get_args());
 630		foreach ($aArgs as $arg) {
 631			// Is it a numeric value?
 632			if ((is_numeric($arg)) && (!is_string($arg))) {
 633				$returnValue += $arg;
 634			}
 635		}
 636
 637		// Return
 638		return $returnValue;
 639	}	//	function SUM()
 640
 641
 642	/**
 643	 *	SUMSQ
 644	 *
 645	 *	SUMSQ returns the sum of the squares of the arguments
 646	 *
 647	 *	Excel Function:
 648	 *		SUMSQ(value1[,value2[, ...]])
 649	 *
 650	 *	@access	public
 651	 *	@category Mathematical and Trigonometric Functions
 652	 *	@param	mixed		$arg,...		Data values
 653	 *	@return	float
 654	 */
 655	public static function SUMSQ() {
 656		// Return value
 657		$returnValue = 0;
 658
 659		// Loop through arguments
 660		$aArgs = self::flattenArray(func_get_args());
 661		foreach ($aArgs as $arg) {
 662			// Is it a numeric value?
 663			if ((is_numeric($arg)) && (!is_string($arg))) {
 664				$returnValue += ($arg * $arg);
 665			}
 666		}
 667
 668		// Return
 669		return $returnValue;
 670	}	//	function SUMSQ()
 671
 672
 673	/**
 674	 *	PRODUCT
 675	 *
 676	 *	PRODUCT returns the product of all the values and cells referenced in the argument list.
 677	 *
 678	 *	Excel Function:
 679	 *		PRODUCT(value1[,value2[, ...]])
 680	 *
 681	 *	@access	public
 682	 *	@category Mathematical and Trigonometric Functions
 683	 *	@param	mixed		$arg,...		Data values
 684	 *	@return	float
 685	 */
 686	public static function PRODUCT() {
 687		// Return value
 688		$returnValue = null;
 689
 690		// Loop through arguments
 691		$aArgs = self::flattenArray(func_get_args());
 692		foreach ($aArgs as $arg) {
 693			// Is it a numeric value?
 694			if ((is_numeric($arg)) && (!is_string($arg))) {
 695				if (is_null($returnValue)) {
 696					$returnValue = $arg;
 697				} else {
 698					$returnValue *= $arg;
 699				}
 700			}
 701		}
 702
 703		// Return
 704		if (is_null($returnValue)) {
 705			return 0;
 706		}
 707		return $returnValue;
 708	}	//	function PRODUCT()
 709
 710
 711	/**
 712	 *	QUOTIENT
 713	 *
 714	 *	QUOTIENT function returns the integer portion of a division. Numerator is the divided number
 715	 *		and denominator is the divisor.
 716	 *
 717	 *	Excel Function:
 718	 *		QUOTIENT(value1[,value2[, ...]])
 719	 *
 720	 *	@access	public
 721	 *	@category Mathematical and Trigonometric Functions
 722	 *	@param	mixed		$arg,...		Data values
 723	 *	@return	float
 724	 */
 725	public static function QUOTIENT() {
 726		// Return value
 727		$returnValue = null;
 728
 729		// Loop through arguments
 730		$aArgs = self::flattenArray(func_get_args());
 731		foreach ($aArgs as $arg) {
 732			// Is it a numeric value?
 733			if ((is_numeric($arg)) && (!is_string($arg))) {
 734				if (is_null($returnValue)) {
 735					$returnValue = ($arg == 0) ? 0 : $arg;
 736				} else {
 737					if (($returnValue == 0) || ($arg == 0)) {
 738						$returnValue = 0;
 739					} else {
 740						$returnValue /= $arg;
 741					}
 742				}
 743			}
 744		}
 745
 746		// Return
 747		return intval($returnValue);
 748	}	//	function QUOTIENT()
 749
 750
 751	/**
 752	 *	MIN
 753	 *
 754	 *	MIN returns the value of the element of the values passed that has the smallest value,
 755	 *		with negative numbers considered smaller than positive numbers.
 756	 *
 757	 *	Excel Function:
 758	 *		MIN(value1[,value2[, ...]])
 759	 *
 760	 *	@access	public
 761	 *	@category Statistical Functions
 762	 *	@param	mixed		$arg,...		Data values
 763	 *	@return	float
 764	 */
 765	public static function MIN() {
 766		// Return value
 767		$returnValue = null;
 768
 769		// Loop through arguments
 770		$aArgs = self::flattenArray(func_get_args());
 771		foreach ($aArgs as $arg) {
 772			// Is it a numeric value?
 773			if ((is_numeric($arg)) && (!is_string($arg))) {
 774				if ((is_null($returnValue)) || ($arg < $returnValue)) {
 775					$returnValue = $arg;
 776				}
 777			}
 778		}
 779
 780		// Return
 781		if(is_null($returnValue)) {
 782			return 0;
 783		}
 784		return $returnValue;
 785	}	//	function MIN()
 786
 787
 788	/**
 789	 *	MINA
 790	 *
 791	 *	Returns the smallest value in a list of arguments, including numbers, text, and logical values
 792	 *
 793	 *	Excel Function:
 794	 *		MINA(value1[,value2[, ...]])
 795	 *
 796	 *	@access	public
 797	 *	@category Statistical Functions
 798	 *	@param	mixed		$arg,...		Data values
 799	 *	@return	float
 800	 */
 801	public static function MINA() {
 802		// Return value
 803		$returnValue = null;
 804
 805		// Loop through arguments
 806		$aArgs = self::flattenArray(func_get_args());
 807		foreach ($aArgs as $arg) {
 808			// Is it a numeric value?
 809			if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
 810				if (is_bool($arg)) {
 811					$arg = (integer) $arg;
 812				} elseif (is_string($arg)) {
 813					$arg = 0;
 814				}
 815				if ((is_null($returnValue)) || ($arg < $returnValue)) {
 816					$returnValue = $arg;
 817				}
 818			}
 819		}
 820
 821		// Return
 822		if(is_null($returnValue)) {
 823			return 0;
 824		}
 825		return $returnValue;
 826	}	//	function MINA()
 827
 828
 829	/**
 830	 *	SMALL
 831	 *
 832	 *	Returns the nth smallest value in a data set. You can use this function to
 833	 *		select a value based on its relative standing.
 834	 *
 835	 *	Excel Function:
 836	 *		SMALL(value1[,value2[, ...]],entry)
 837	 *
 838	 *	@access	public
 839	 *	@category Statistical Functions
 840	 *	@param	mixed		$arg,...		Data values
 841	 *	@param	int			$entry			Position (ordered from the smallest) in the array or range of data to return
 842	 *	@return	float
 843	 */
 844	public static function SMALL() {
 845		$aArgs = self::flattenArray(func_get_args());
 846
 847		// Calculate
 848		$entry = array_pop($aArgs);
 849
 850		if ((is_numeric($entry)) && (!is_string($entry))) {
 851			$mArgs = array();
 852			foreach ($aArgs as $arg) {
 853				// Is it a numeric value?
 854				if ((is_numeric($arg)) && (!is_string($arg))) {
 855					$mArgs[] = $arg;
 856				}
 857			}
 858			$count = self::COUNT($mArgs);
 859			$entry = floor(--$entry);
 860			if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
 861				return self::$_errorCodes['num'];
 862			}
 863			sort($mArgs);
 864			return $mArgs[$entry];
 865		}
 866		return self::$_errorCodes['value'];
 867	}	//	function SMALL()
 868
 869
 870	/**
 871	 *	MAX
 872	 *
 873	 *	MAX returns the value of the element of the values passed that has the highest value,
 874	 *		with negative numbers considered smaller than positive numbers.
 875	 *
 876	 *	Excel Function:
 877	 *		MAX(value1[,value2[, ...]])
 878	 *
 879	 *	@access	public
 880	 *	@category Statistical Functions
 881	 *	@param	mixed		$arg,...		Data values
 882	 *	@return	float
 883	 */
 884	public static function MAX() {
 885		// Return value
 886		$returnValue = null;
 887
 888		// Loop through arguments
 889		$aArgs = self::flattenArray(func_get_args());
 890		foreach ($aArgs as $arg) {
 891			// Is it a numeric value?
 892			if ((is_numeric($arg)) && (!is_string($arg))) {
 893				if ((is_null($returnValue)) || ($arg > $returnValue)) {
 894					$returnValue = $arg;
 895				}
 896			}
 897		}
 898
 899		// Return
 900		if(is_null($returnValue)) {
 901			return 0;
 902		}
 903		return $returnValue;
 904	}	//	function MAX()
 905
 906
 907	/**
 908	 *	MAXA
 909	 *
 910	 *	Returns the greatest value in a list of arguments, including numbers, text, and logical values
 911	 *
 912	 *	Excel Function:
 913	 *		MAXA(value1[,value2[, ...]])
 914	 *
 915	 *	@access	public
 916	 *	@category Statistical Functions
 917	 *	@param	mixed		$arg,...		Data values
 918	 *	@return	float
 919	 */
 920	public static function MAXA() {
 921		// Return value
 922		$returnValue = null;
 923
 924		// Loop through arguments
 925		$aArgs = self::flattenArray(func_get_args());
 926		foreach ($aArgs as $arg) {
 927			// Is it a numeric value?
 928			if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
 929				if (is_bool($arg)) {
 930					$arg = (integer) $arg;
 931				} elseif (is_string($arg)) {
 932					$arg = 0;
 933				}
 934				if ((is_null($returnValue)) || ($arg > $returnValue)) {
 935					$returnValue = $arg;
 936				}
 937			}
 938		}
 939
 940		// Return
 941		if(is_null($returnValue)) {
 942			return 0;
 943		}
 944		return $returnValue;
 945	}	//	function MAXA()
 946
 947
 948	/**
 949	 *	LARGE
 950	 *
 951	 *	Returns the nth largest value in a data set. You can use this function to
 952	 *		select a value based on its relative standing.
 953	 *
 954	 *	Excel Function:
 955	 *		LARGE(value1[,value2[, ...]],entry)
 956	 *
 957	 *	@access	public
 958	 *	@category Statistical Functions
 959	 *	@param	mixed		$arg,...		Data values
 960	 *	@param	int			$entry			Position (ordered from the largest) in the array or range of data to return
 961	 *	@return	float
 962	 *
 963	 */
 964	public static function LARGE() {
 965		$aArgs = self::flattenArray(func_get_args());
 966
 967		// Calculate
 968		$entry = floor(array_pop($aArgs));
 969
 970		if ((is_numeric($entry)) && (!is_string($entry))) {
 971			$mArgs = array();
 972			foreach ($aArgs as $arg) {
 973				// Is it a numeric value?
 974				if ((is_numeric($arg)) && (!is_string($arg))) {
 975					$mArgs[] = $arg;
 976				}
 977			}
 978			$count = self::COUNT($mArgs);
 979			$entry = floor(--$entry);
 980			if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
 981				return self::$_errorCodes['num'];
 982			}
 983			rsort($mArgs);
 984			return $mArgs[$entry];
 985		}
 986		return self::$_errorCodes['value'];
 987	}	//	function LARGE()
 988
 989
 990	/**
 991	 *	PERCENTILE
 992	 *
 993	 *	Returns the nth percentile of values in a range..
 994	 *
 995	 *	Excel Function:
 996	 *		PERCENTILE(value1[,value2[, ...]],entry)
 997	 *
 998	 *	@access	public
 999	 *	@category Statistical Functions
1000	 *	@param	mixed		$arg,...		Data values
1001	 *	@param	float		$entry			Percentile value in the range 0..1, inclusive.
1002	 *	@return	float
1003	 */
1004	public static function PERCENTILE() {
1005		$aArgs = self::flattenArray(func_get_args());
1006
1007		// Calculate
1008		$entry = array_pop($aArgs);
1009
1010		if ((is_numeric($entry)) && (!is_string($entry))) {
1011			if (($entry < 0) || ($entry > 1)) {
1012				return self::$_errorCodes['num'];
1013			}
1014			$mArgs = array();
1015			foreach ($aArgs as $arg) {
1016				// Is it a numeric value?
1017				if ((is_numeric($arg)) && (!is_string($arg))) {
1018					$mArgs[] = $arg;
1019				}
1020			}
1021			$mValueCount = count($mArgs);
1022			if ($mValueCount > 0) {
1023				sort($mArgs);
1024				$count = self::COUNT($mArgs);
1025				$index = $entry * ($count-1);
1026				$iBase = floor($index);
1027				if ($index == $iBase) {
1028					return $mArgs[$index];
1029				} else {
1030					$iNext = $iBase + 1;
1031					$iProportion = $index - $iBase;
1032					return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
1033				}
1034			}
1035		}
1036		return self::$_errorCodes['value'];
1037	}	//	function PERCENTILE()
1038
1039
1040	/**
1041	 *	QUARTILE
1042	 *
1043	 *	Returns the quartile of a data set.
1044	 *
1045	 *	Excel Function:
1046	 *		QUARTILE(value1[,value2[, ...]],entry)
1047	 *
1048	 *	@access	public
1049	 *	@category Statistical Functions
1050	 *	@param	mixed		$arg,...		Data values
1051	 *	@param	int			$entry			Quartile value in the range 1..3, inclusive.
1052	 *	@return	float
1053	 */
1054	public static function QUARTILE() {
1055		$aArgs = self::flattenArray(func_get_args());
1056
1057		// Calculate
1058		$entry = floor(array_pop($aArgs));
1059
1060		if ((is_numeric($entry)) && (!is_string($entry))) {
1061			$entry /= 4;
1062			if (($entry < 0) || ($entry > 1)) {
1063				return self::$_errorCodes['num'];
1064			}
1065			return self::PERCENTILE($aArgs,$entry);
1066		}
1067		return self::$_errorCodes['value'];
1068	}	//	function QUARTILE()
1069
1070
1071	/**
1072	 *	COUNT
1073	 *
1074	 *	Counts the number of cells that contain numbers within the list of arguments
1075	 *
1076	 *	Excel Function:
1077	 *		COUNT(value1[,value2[, ...]])
1078	 *
1079	 *	@access	public
1080	 *	@category Statistical Functions
1081	 *	@param	mixed		$arg,...		Data values
1082	 *	@return	int
1083	 */
1084	public static function COUNT() {
1085		// Return value
1086		$returnValue = 0;
1087
1088		// Loop through arguments
1089		$aArgs = self::flattenArrayIndexed(func_get_args());
1090		foreach ($aArgs as $k => $arg) {
1091			if ((is_bool($arg)) &&
1092				((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1093				$arg = (integer) $arg;
1094			}
1095			// Is it a numeric value?
1096			if ((is_numeric($arg)) && (!is_string($arg))) {
1097				++$returnValue;
1098			}
1099		}
1100
1101		// Return
1102		return $returnValue;
1103	}	//	function COUNT()
1104
1105
1106	/**
1107	 *	COUNTBLANK
1108	 *
1109	 *	Counts the number of empty cells within the list of arguments
1110	 *
1111	 *	Excel Function:
1112	 *		COUNTBLANK(value1[,value2[, ...]])
1113	 *
1114	 *	@access	public
1115	 *	@category Statistical Functions
1116	 *	@param	mixed		$arg,...		Data values
1117	 *	@return	int
1118	 */
1119	public static function COUNTBLANK() {
1120		// Return value
1121		$returnValue = 0;
1122
1123		// Loop through arguments
1124		$aArgs = self::flattenArray(func_get_args());
1125		foreach ($aArgs as $arg) {
1126			// Is it a blank cell?
1127			if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
1128				++$returnValue;
1129			}
1130		}
1131
1132		// Return
1133		return $returnValue;
1134	}	//	function COUNTBLANK()
1135
1136
1137	/**
1138	 *	COUNTA
1139	 *
1140	 *	Counts the number of cells that are not empty within the list of arguments
1141	 *
1142	 *	Excel Function:
1143	 *		COUNTA(value1[,value2[, ...]])
1144	 *
1145	 *	@access	public
1146	 *	@category Statistical Functions
1147	 *	@param	mixed		$arg,...		Data values
1148	 *	@return	int
1149	 */
1150	public static function COUNTA() {
1151		// Return value
1152		$returnValue = 0;
1153
1154		// Loop through arguments
1155		$aArgs = self::flattenArray(func_get_args());
1156		foreach ($aArgs as $arg) {
1157			// Is it a numeric, boolean or string value?
1158			if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1159				++$returnValue;
1160			}
1161		}
1162
1163		// Return
1164		return $returnValue;
1165	}	//	function COUNTA()
1166
1167
1168	/**
1169	 *	COUNTIF
1170	 *
1171	 *	Counts the number of cells that contain numbers within the list of arguments
1172	 *
1173	 *	Excel Function:
1174	 *		COUNTIF(value1[,value2[, ...]],condition)
1175	 *
1176	 *	@access	public
1177	 *	@category Statistical Functions
1178	 *	@param	mixed		$arg,...		Data values
1179	 *	@param	string		$condition		The criteria that defines which cells will be counted.
1180	 *	@return	int
1181	 */
1182	public static function COUNTIF($aArgs,$condition) {
1183		// Return value
1184		$returnValue = 0;
1185
1186		$aArgs = self::flattenArray($aArgs);
1187		$condition	= self::flattenSingleValue($condition);
1188		if (!in_array($condition{0},array('>', '<', '='))) {
1189			if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
1190			$condition = '='.$condition;
1191		} else {
1192			preg_match('/([<>=]+)(.*)/',$condition,$matches);
1193			list(,$operator,$operand) = $matches;
1194			if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); }
1195			$condition = $operator.$operand;
1196		}
1197		// Loop through arguments
1198		foreach ($aArgs as $arg) {
1199			if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1200			$testCondition = '='.$arg.$condition;
1201			if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1202				// Is it a value within our criteria
1203				++$returnValue;
1204			}
1205		}
1206
1207		// Return
1208		return $returnValue;
1209	}	//	function COUNTIF()
1210
1211
1212	/**
1213	 *	SUMIF
1214	 *
1215	 *	Counts the number of cells that contain numbers within the list of arguments
1216	 *
1217	 *	Excel Function:
1218	 *		SUMIF(value1[,value2[, ...]],condition)
1219	 *
1220	 *	@access	public
1221	 *	@category Mathematical and Trigonometric Functions
1222	 *	@param	mixed		$arg,...		Data values
1223	 *	@param	string		$condition		The criteria that defines which cells will be summed.
1224	 *	@return	float
1225	 */
1226	public static function SUMIF($aArgs,$condition,$sumArgs = array()) {
1227		// Return value
1228		$returnValue = 0;
1229
1230		$aArgs = self::flattenArray($aArgs);
1231		$sumArgs = self::flattenArray($sumArgs);
1232		if (count($sumArgs) == 0) {
1233			$sumArgs = $aArgs;
1234		}
1235		if (!in_array($condition{0},array('>', '<', '='))) {
1236			if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
1237			$condition = '='.$condition;
1238		} else {
1239			preg_match('/([<>=]+)(.*)/',$condition,$matches);
1240			list(,$operator,$operand) = $matches;
1241			if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); }
1242			$condition = $operator.$operand;
1243		}
1244		// Loop through arguments
1245		foreach ($aArgs as $key => $arg) {
1246			if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
1247			$testCondition = '='.$arg.$condition;
1248			if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
1249				// Is it a value within our criteria
1250				$returnValue += $sumArgs[$key];
1251			}
1252		}
1253
1254		// Return
1255		return $returnValue;
1256	}	//	function SUMIF()
1257
1258
1259	/**
1260	 *	AVERAGE
1261	 *
1262	 *	Returns the average (arithmetic mean) of the arguments
1263	 *
1264	 *	Excel Function:
1265	 *		AVERAGE(value1[,value2[, ...]])
1266	 *
1267	 *	@access	public
1268	 *	@category Statistical Functions
1269	 *	@param	mixed		$arg,...		Data values
1270	 *	@return	float
1271	 */
1272	public static function AVERAGE() {
1273		$aArgs = self::flattenArrayIndexed(func_get_args());
1274
1275		$returnValue = $aCount = 0;
1276		// Loop through arguments
1277		foreach ($aArgs as $k => $arg) {
1278			if ((is_bool($arg)) &&
1279				((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1280				$arg = (integer) $arg;
1281			}
1282			// Is it a numeric value?
1283			if ((is_numeric($arg)) && (!is_string($arg))) {
1284				if (is_null($returnValue)) {
1285					$returnValue = $arg;
1286				} else {
1287					$returnValue += $arg;
1288				}
1289				++$aCount;
1290			}
1291		}
1292
1293		// Return
1294		if ($aCount > 0) {
1295			return $returnValue / $aCount;
1296		} else {
1297			return self::$_errorCodes['divisionbyzero'];
1298		}
1299	}	//	function AVERAGE()
1300
1301
1302	/**
1303	 *	AVERAGEA
1304	 *
1305	 *	Returns the average of its arguments, including numbers, text, and logical values
1306	 *
1307	 *	Excel Function:
1308	 *		AVERAGEA(value1[,value2[, ...]])
1309	 *
1310	 *	@access	public
1311	 *	@category Statistical Functions
1312	 *	@param	mixed		$arg,...		Data values
1313	 *	@return	float
1314	 */
1315	public static function AVERAGEA() {
1316		// Return value
1317		$returnValue = null;
1318
1319		// Loop through arguments
1320		$aArgs = self::flattenArrayIndexed(func_get_args());
1321		$aCount = 0;
1322		foreach ($aArgs as $k => $arg) {
1323			if ((is_bool($arg)) &&
1324				(!self::isMatrixValue($k))) {
1325			} else {
1326				if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
1327					if (is_bool($arg)) {
1328						$arg = (integer) $arg;
1329					} elseif (is_string($arg)) {
1330						$arg = 0;
1331					}
1332					if (is_null($returnValue)) {
1333						$returnValue = $arg;
1334					} else {
1335						$returnValue += $arg;
1336					}
1337					++$aCount;
1338				}
1339			}
1340		}
1341
1342		// Return
1343		if ($aCount > 0) {
1344			return $returnValue / $aCount;
1345		} else {
1346			return self::$_errorCodes['divisionbyzero'];
1347		}
1348	}	//	function AVERAGEA()
1349
1350
1351	/**
1352	 *	MEDIAN
1353	 *
1354	 *	Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
1355	 *
1356	 *	Excel Function:
1357	 *		MEDIAN(value1[,value2[, ...]])
1358	 *
1359	 *	@access	public
1360	 *	@category Statistical Functions
1361	 *	@param	mixed		$arg,...		Data values
1362	 *	@return	float
1363	 */
1364	public static function MEDIAN() {
1365		// Return value
1366		$returnValue = self::$_errorCodes['num'];
1367
1368		$mArgs = array();
1369		// Loop through arguments
1370		$aArgs = self::flattenArray(func_get_args());
1371		foreach ($aArgs as $arg) {
1372			// Is it a numeric value?
1373			if ((is_numeric($arg)) && (!is_string($arg))) {
1374				$mArgs[] = $arg;
1375			}
1376		}
1377
1378		$mValueCount = count($mArgs);
1379		if ($mValueCount > 0) {
1380			sort($mArgs,SORT_NUMERIC);
1381			$mValueCount = $mValueCount / 2;
1382			if ($mValueCount == floor($mValueCount)) {
1383				$returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
1384			} else {
1385				$mValueCount == floor($mValueCount);
1386				$returnValue = $mArgs[$mValueCount];
1387			}
1388		}
1389
1390		// Return
1391		return $returnValue;
1392	}	//	function MEDIAN()
1393
1394
1395	//
1396	//	Special variant of array_count_values that isn't limited to strings and integers,
1397	//		but can work with floating point numbers as values
1398	//
1399	private static function _modeCalc($data) {
1400		$frequencyArray = array();
1401		foreach($data as $datum) {
1402			$found = False;
1403			foreach($frequencyArray as $key => $value) {
1404				if ((string) $value['value'] == (string) $datum) {
1405					++$frequencyArray[$key]['frequency'];
1406					$found = True;
1407					break;
1408				}
1409			}
1410			if (!$found) {
1411				$frequencyArray[] = array('value'		=> $datum,
1412										  'frequency'	=>	1 );
1413			}
1414		}
1415
1416		foreach($frequencyArray as $key => $value) {
1417			$frequencyList[$key] = $value['frequency'];
1418			$valueList[$key] = $value['value'];
1419		}
1420		array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
1421
1422		if ($frequencyArray[0]['frequency'] == 1) {
1423			return self::NA();
1424		}
1425		return $frequencyArray[0]['value'];
1426	}	//	function _modeCalc()
1427
1428
1429	/**
1430	 *	MODE
1431	 *
1432	 *	Returns the most frequently occurring, or repetitive, value in an array or range of data
1433	 *
1434	 *	Excel Function:
1435	 *		MODE(value1[,value2[, ...]])
1436	 *
1437	 *	@access	public
1438	 *	@category Statistical Functions
1439	 *	@param	mixed		$arg,...		Data values
1440	 *	@return	float
1441	 */
1442	public static function MODE() {
1443		// Return value
1444		$returnValue = self::NA();
1445
1446		// Loop through arguments
1447		$aArgs = self::flattenArray(func_get_args());
1448
1449		$mArgs = array();
1450		foreach ($aArgs as $arg) {
1451			// Is it a numeric value?
1452			if ((is_numeric($arg)) && (!is_string($arg))) {
1453				$mArgs[] = $arg;
1454			}
1455		}
1456
1457		if (count($mArgs) > 0) {
1458			return self::_modeCalc($mArgs);
1459		}
1460
1461		// Return
1462		return $returnValue;
1463	}	//	function MODE()
1464
1465
1466	/**
1467	 *	DEVSQ
1468	 *
1469	 *	Returns the sum of squares of deviations of data points from their sample mean.
1470	 *
1471	 *	Excel Function:
1472	 *		DEVSQ(value1[,value2[, ...]])
1473	 *
1474	 *	@access	public
1475	 *	@category Statistical Functions
1476	 *	@param	mixed		$arg,...		Data values
1477	 *	@return	float
1478	 */
1479	public static function DEVSQ() {
1480		$aArgs = self::flattenArrayIndexed(func_get_args());
1481
1482		// Return value
1483		$returnValue = null;
1484
1485		$aMean = self::AVERAGE($aArgs);
1486		if ($aMean != self::$_errorCodes['divisionbyzero']) {
1487			$aCount = -1;
1488			foreach ($aArgs as $k => $arg) {
1489				// Is it a numeric value?
1490				if ((is_bool($arg)) &&
1491					((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1492					$arg = (integer) $arg;
1493				}
1494				if ((is_numeric($arg)) && (!is_string($arg))) {
1495					if (is_null($returnValue)) {
1496						$returnValue = pow(($arg - $aMean),2);
1497					} else {
1498						$returnValue += pow(($arg - $aMean),2);
1499					}
1500					++$aCount;
1501				}
1502			}
1503
1504			// Return
1505			if (is_null($returnValue)) {
1506				return self::$_errorCodes['num'];
1507			} else {
1508				return $returnValue;
1509			}
1510		}
1511		return self::NA();
1512	}	//	function DEVSQ()
1513
1514
1515	/**
1516	 *	AVEDEV
1517	 *
1518	 *	Returns the average of the absolute deviations of data points from their mean.
1519	 *	AVEDEV is a measure of the variability in a data set.
1520	 *
1521	 *	Excel Function:
1522	 *		AVEDEV(value1[,value2[, ...]])
1523	 *
1524	 *	@access	public
1525	 *	@category Statistical Functions
1526	 *	@param	mixed		$arg,...		Data values
1527	 *	@return	float
1528	 */
1529	public static function AVEDEV() {
1530		$aArgs = self::flattenArrayIndexed(func_get_args());
1531
1532		// Return value
1533		$returnValue = null;
1534
1535		$aMean = self::AVERAGE($aArgs);
1536		if ($aMean != self::$_errorCodes['divisionbyzero']) {
1537			$aCount = 0;
1538			foreach ($aArgs as $k => $arg) {
1539				if ((is_bool($arg)) &&
1540					((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1541					$arg = (integer) $arg;
1542				}
1543				// Is it a numeric value?
1544				if ((is_numeric($arg)) && (!is_string($arg))) {
1545					if (is_null($returnValue)) {
1546						$returnValue = abs($arg - $aMean);
1547					} else {
1548						$returnValue += abs($arg - $aMean);
1549					}
1550					++$aCount;
1551				}
1552			}
1553
1554			// Return
1555			if ($aCount == 0) {
1556				return self::$_errorCodes['divisionbyzero'];
1557			}
1558			return $returnValue / $aCount;
1559		}
1560		return self::$_errorCodes['num'];
1561	}	//	function AVEDEV()
1562
1563
1564	/**
1565	 *	GEOMEAN
1566	 *
1567	 *	Returns the geometric mean of an array or range of positive data. For example, you
1568	 *		can use GEOMEAN to calculate average growth rate given compound interest with
1569	 *		variable rates.
1570	 *
1571	 *	Excel Function:
1572	 *		GEOMEAN(value1[,value2[, ...]])
1573	 *
1574	 *	@access	public
1575	 *	@category Statistical Functions
1576	 *	@param	mixed		$arg,...		Data values
1577	 *	@return	float
1578	 */
1579	public static function GEOMEAN() {
1580		$aArgs = self::flattenArray(func_get_args());
1581
1582		$aMean = self::PRODUCT($aArgs);
1583		if (is_numeric($aMean) && ($aMean > 0)) {
1584			$aCount = self::COUNT($aArgs) ;
1585			if (self::MIN($aArgs) > 0) {
1586				return pow($aMean, (1 / $aCount));
1587			}
1588		}
1589		return self::$_errorCodes['num'];
1590	}	//	GEOMEAN()
1591
1592
1593	/**
1594	 *	HARMEAN
1595	 *
1596	 *	Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the
1597	 *		arithmetic mean of reciprocals.
1598	 *
1599	 *	Excel Function:
1600	 *		HARMEAN(value1[,value2[, ...]])
1601	 *
1602	 *	@access	public
1603	 *	@category Statistical Functions
1604	 *	@param	mixed		$arg,...		Data values
1605	 *	@return	float
1606	 */
1607	public static function HARMEAN() {
1608		// Return value
1609		$returnValue = self::NA();
1610
1611		// Loop through arguments
1612		$aArgs = self::flattenArray(func_get_args());
1613		if (self::MIN($aArgs) < 0) {
1614			return self::$_errorCodes['num'];
1615		}
1616		$aCount = 0;
1617		foreach ($aArgs as $arg) {
1618			// Is it a numeric value?
1619			if ((is_numeric($arg)) && (!is_string($arg))) {
1620				if ($arg <= 0) {
1621					return self::$_errorCodes['num'];
1622				}
1623				if (is_null($returnValue)) {
1624					$returnValue = (1 / $arg);
1625				} else {
1626					$returnValue += (1 / $arg);
1627				}
1628				++$aCount;
1629			}
1630		}
1631
1632		// Return
1633		if ($aCount > 0) {
1634			return 1 / ($returnValue / $aCount);
1635		} else {
1636			return $returnValue;
1637		}
1638	}	//	function HARMEAN()
1639
1640
1641	/**
1642	 *	TRIMMEAN
1643	 *
1644	 *	Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
1645	 *	taken by excluding a percentage of data points from the top and bottom tails
1646	 *	of a data set.
1647	 *
1648	 *	Excel Function:
1649	 *		TRIMEAN(value1[,value2[, ...]],$discard)
1650	 *
1651	 *	@access	public
1652	 *	@category Statistical Functions
1653	 *	@param	mixed		$arg,...		Data values
1654	 *	@param	float		$discard		Percentage to discard
1655	 *	@return	float
1656	 */
1657	public static function TRIMMEAN() {
1658		$aArgs = self::flattenArray(func_get_args());
1659
1660		// Calculate
1661		$percent = array_pop($aArgs);
1662
1663		if ((is_numeric($percent)) && (!is_string($percent))) {
1664			if (($percent < 0) || ($percent > 1)) {
1665				return self::$_errorCodes['num'];
1666			}
1667			$mArgs = array();
1668			foreach ($aArgs as $arg) {
1669				// Is it a numeric value?
1670				if ((is_numeric($arg)) && (!is_string($arg))) {
1671					$mArgs[] = $arg;
1672				}
1673			}
1674			$discard = floor(self::COUNT($mArgs) * $percent / 2);
1675			sort($mArgs);
1676			for ($i=0; $i < $discard; ++$i) {
1677				array_pop($mArgs);
1678				array_shift($mArgs);
1679			}
1680			return self::AVERAGE($mArgs);
1681		}
1682		return self::$_errorCodes['value'];
1683	}	//	function TRIMMEAN()
1684
1685
1686	/**
1687	 *	STDEV
1688	 *
1689	 *	Estimates standard deviation based on a sample. The standard deviation is a measure of how
1690	 *	widely values are dispersed from the average value (the mean).
1691	 *
1692	 *	Excel Function:
1693	 *		STDEV(value1[,value2[, ...]])
1694	 *
1695	 *	@access	public
1696	 *	@category Statistical Functions
1697	 *	@param	mixed		$arg,...		Data values
1698	 *	@return	float
1699	 */
1700	public static function STDEV() {
1701		$aArgs = self::flattenArrayIndexed(func_get_args());
1702
1703		// Return value
1704		$returnValue = null;
1705
1706		$aMean = self::AVERAGE($aArgs);
1707		if (!is_null($aMean)) {
1708			$aCount = -1;
1709			foreach ($aArgs as $k => $arg) {
1710				if ((is_bool($arg)) &&
1711					((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1712					$arg = (integer) $arg;
1713				}
1714				// Is it a numeric value?
1715				if ((is_numeric($arg)) && (!is_string($arg))) {
1716					if (is_null($returnValue)) {
1717						$returnValue = pow(($arg - $aMean),2);
1718					} else {
1719						$returnValue += pow(($arg - $aMean),2);
1720					}
1721					++$aCount;
1722				}
1723			}
1724
1725			// Return
1726			if (($aCount > 0) && ($returnValue > 0)) {
1727				return sqrt($returnValue / $aCount);
1728			}
1729		}
1730		return self::$_errorCodes['divisionbyzero'];
1731	}	//	function STDEV()
1732
1733
1734	/**
1735	 *	STDEVA
1736	 *
1737	 *	Estimates standard deviation based on a sample, including numbers, text, and logical values
1738	 *
1739	 *	Excel Function:
1740	 *		STDEVA(value1[,value2[, ...]])
1741	 *
1742	 *	@access	public
1743	 *	@category Statistical Functions
1744	 *	@param	mixed		$arg,...		Data values
1745	 *	@return	float
1746	 */
1747	public static function STDEVA() {
1748		$aArgs = self::flattenArrayIndexed(func_get_args());
1749
1750		// Return value
1751		$returnValue = null;
1752
1753		$aMean = self::AVERAGEA($aArgs);
1754		if (!is_null($aMean)) {
1755			$aCount = -1;
1756			foreach ($aArgs as $k => $arg) {
1757				if ((is_bool($arg)) &&
1758					(!self::isMatrixValue($k))) {
1759				} else {
1760					// Is it a numeric value?
1761					if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
1762						if (is_bool($arg)) {
1763							$arg = (integer) $arg;
1764						} elseif (is_string($arg)) {
1765							$arg = 0;
1766						}
1767						if (is_null($returnValue)) {
1768							$returnValue = pow(($arg - $aMean),2);
1769						} else {
1770							$returnValue += pow(($arg - $aMean),2);
1771						}
1772						++$aCount;
1773					}
1774				}
1775			}
1776
1777			// Return
1778			if (($aCount > 0) && ($returnValue > 0)) {
1779				return sqrt($returnValue / $aCount);
1780			}
1781		}
1782		return self::$_errorCodes['divisionbyzero'];
1783	}	//	function STDEVA()
1784
1785
1786	/**
1787	 *	STDEVP
1788	 *
1789	 *	Calculates standard deviation based on the entire population
1790	 *
1791	 *	Excel Function:
1792	 *		STDEVP(value1[,value2[, ...]])
1793	 *
1794	 *	@access	public
1795	 *	@category Statistical Functions
1796	 *	@param	mixed		$arg,...		Data values
1797	 *	@return	float
1798	 */
1799	public static function STDEVP() {
1800		$aArgs = self::flattenArrayIndexed(func_get_args());
1801
1802		// Return value
1803		$returnValue = null;
1804
1805		$aMean = self::AVERAGE($aArgs);
1806		if (!is_null($aMean)) {
1807			$aCount = 0;
1808			foreach ($aArgs as $k => $arg) {
1809				if ((is_bool($arg)) &&
1810					((!self::isCellValue($k)) || (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE))) {
1811					$arg = (integer) $arg;
1812				}
1813				// Is it a numeric value?
1814				if ((is_numeric($arg)) && (!is_string($arg))) {
1815					if (is_null($returnValue)) {
1816						$returnValue = pow(($arg - $aMean),2);
1817					} else {
1818						$returnValue += pow(($arg - $aMean),2);
1819					}
1820					++$aCount;
1821				}
1822			}
1823
1824			// Return
1825			if (($aCount > 0) && ($returnValue > 0)) {
1826				return sqrt($returnValue / $aCount);
1827			}
1828		}
1829		return self::$_errorCodes['divisionbyzero'];
1830	}	//	function STDEVP()
1831
1832
1833	/**
1834	 *	STDEVPA
1835	 *
1836	 *	Calculates standard deviation based on the entire population, including numbers, text, and logical values
1837	 *
1838	 *	Excel Function:
1839	 *		STDEVPA(value1[,value2[, ...]])
1840	 *
1841	 *	@access	public
1842	 *	@category Statistical Functions
1843	 *	@param	mixed		$arg,...		Data values
1844	 *	@return	float
1845	 */
1846	public static function STDEVPA() {
1847		$aArgs = self::flattenArrayIndexed(func_get_args());
1848
1849		// Return value
1850		$returnValue = null;
1851
1852		$aMean = self::AVERAGEA($aArgs);
1853		if (!is_null($aMean)) {
1854			$aCount = 0;
1855			foreach ($aArgs as $k => $arg) {
1856				if ((is_bool($arg)) &&
1857					(!self::isMatrixValue($k))) {
1858				} else {
1859					// Is it a numeric value?
1860					if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
1861						if (is_bool($arg)) {
1862							$arg = (integer) $arg;
1863						} elseif (is_string($arg)) {
1864							$arg = 0;
1865						}
1866						if (is_null($returnValue)) {
1867							$returnValue = pow(($arg - $aMean),2);
1868						} else {
1869							$returnValue += pow(($arg - $aMean),2);
1870						}
1871						++$aCount;
1872					}
1873				}
1874			}
1875
1876			// Return
1877			if (($aCount > 0) && ($returnValue > 0)) {
1878				return sqrt($returnValue / $aCount);
1879			}
1880		}
1881		return self::$_errorCodes['divisionbyzero'];
1882	}	//	function STDEVPA()
1883
1884
1885	/**
1886	 *	VARFunc
1887	 *
1888	 *	Estimates variance based on a sample.
1889	 *
1890	 *	Excel Function:
1891	 *		VAR(value1[,value2[, ...]])
1892	 *
1893	 *	@access	public
1894	 *	@category Statistical Functions
1895	 *	@param	mixed		$arg,...		Data values
1896	 *	@return	float
1897	 */
1898	public static function VARFunc() {
1899		// Return value
1900		$returnValue = self::$_errorCodes['divisionbyzero'];
1901
1902		$summerA = $summerB = 0;
1903
1904		// Loop through arguments
1905		$aArgs = self::flattenArray(func_get_args());
1906		$aCount = 0;
1907		foreach ($aArgs as $arg) {
1908			if (is_bool($arg)) { $arg = (integer) $arg; }
1909			// Is it a numeric value?
1910			if ((is_numeric($arg)) && (!is_string($arg))) {
1911				$summerA += ($arg * $arg);
1912				$summerB += $arg;
1913				++$aCount;
1914			}
1915		}
1916
1917		// Return
1918		if ($aCount > 1) {
1919			$summerA *= $aCount;
1920			$summerB *= $summerB;
1921			$returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
1922		}
1923		return $returnValue;
1924	}	//	function VARFunc()
1925
1926
1927	/**
1928	 *	VARA
1929	 *
1930	 *	Estimates variance based on a sample, including numbers, text, and logical values
1931	 *
1932	 *	Excel Function:
1933	 *		VARA(value1[,value2[, ...]])
1934	 *
1935	 *	@access	public
1936	 *	@category Statistical Functions
1937	 *	@param	mixed		$a…

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