PageRenderTime 12ms CodeModel.GetById 1289ms app.highlight 289ms RepoModel.GetById 837ms app.codeStats 6ms

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

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

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