PageRenderTime 67ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 1ms

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

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

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