PageRenderTime 66ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 2ms

/branches/wi16-graphs/Classes/PHPExcel/Calculation/Functions.php

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

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