PageRenderTime 60ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 2ms

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

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

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