PageRenderTime 67ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 2ms

/add-ons/PHPExcel/PHPExcel/Calculation/Functions.php

https://github.com/jcplat/console-seolan
PHP | 11008 lines | 6544 code | 1235 blank | 3229 comment | 1907 complexity | 860ce4e10d9c9aeb497e9b03e6270be5 MD5 | raw file
Possible License(s): LGPL-2.0, LGPL-2.1, GPL-3.0, Apache-2.0, BSD-3-Clause

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

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