PageRenderTime 184ms CodeModel.GetById 60ms app.highlight 77ms RepoModel.GetById 13ms app.codeStats 3ms

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

#
PHP | 6526 lines | 3853 code | 611 blank | 2062 comment | 1194 complexity | 3cfab9ef8349efae9e35cacbcd775ab4 MD5 | raw file

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

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

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