PageRenderTime 58ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 1ms

/includes/PHPExcel/PHPExcel/Calculation/Functions.php

http://github.com/indowebit/Ext-PHP
PHP | 12038 lines | 7249 code | 1373 blank | 3416 comment | 2123 complexity | 1dc1f5cc197a387a695f33efb144997f MD5 | raw file
Possible License(s): GPL-3.0

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

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