PageRenderTime 76ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 1ms

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

#
PHP | 6906 lines | 4038 code | 644 blank | 2224 comment | 1264 complexity | bf83e908b7cdd6aa44136f742821f7a8 MD5 | raw file
Possible License(s): AGPL-1.0, LGPL-2.0, LGPL-2.1, GPL-3.0, LGPL-3.0
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2008 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Calculation
  23. * @copyright Copyright (c) 2006 - 2008 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. define('EPS', 2.22e-16);
  28. define('MAX_VALUE', 1.2e308);
  29. define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
  30. define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
  31. define('XMININ', 2.23e-308);
  32. define('MAX_ITERATIONS', 150);
  33. define('PRECISION', 8.88E-016);
  34. define('EULER', 2.71828182845904523536);
  35. $savedPrecision = ini_get('precision');
  36. if ($savedPrecision < 15) {
  37. ini_set('precision',15);
  38. }
  39. /** PHPExcel_Cell */
  40. require_once 'PHPExcel/Cell.php';
  41. /** PHPExcel_Cell_DataType */
  42. require_once 'PHPExcel/Cell/DataType.php';
  43. /** PHPExcel_Shared_Date */
  44. require_once 'PHPExcel/Shared/Date.php';
  45. /**
  46. * PHPExcel_Calculation_Functions
  47. *
  48. * @category PHPExcel
  49. * @package PHPExcel_Calculation
  50. * @copyright Copyright (c) 2006 - 2008 PHPExcel (http://www.codeplex.com/PHPExcel)
  51. */
  52. class PHPExcel_Calculation_Functions {
  53. /** constants */
  54. const COMPATIBILITY_EXCEL = 'Excel';
  55. const COMPATIBILITY_GNUMERIC = 'Gnumeric';
  56. const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
  57. const RETURNDATE_PHP_NUMERIC = 'P';
  58. const RETURNDATE_PHP_OBJECT = 'O';
  59. const RETURNDATE_EXCEL = 'E';
  60. /**
  61. * Compatibility mode to use for error checking and responses
  62. *
  63. * @var string
  64. */
  65. private static $compatibilityMode = self::COMPATIBILITY_EXCEL;
  66. /**
  67. * Data Type to use when returning date values
  68. *
  69. * @var integer
  70. */
  71. private static $ReturnDateType = self::RETURNDATE_PHP_NUMERIC;
  72. /**
  73. * List of error codes
  74. *
  75. * @var array
  76. */
  77. private static $_errorCodes = array( 'null' => '#NULL!',
  78. 'divisionbyzero' => '#DIV/0!',
  79. 'value' => '#VALUE!',
  80. 'reference' => '#REF!',
  81. 'name' => '#NAME?',
  82. 'num' => '#NUM!',
  83. 'na' => '#N/A'
  84. );
  85. /**
  86. * Set the Compatibility Mode
  87. *
  88. * @param string $compatibilityMode Compatibility Mode
  89. * @return boolean (Success or Failure)
  90. */
  91. public static function setCompatibilityMode($compatibilityMode) {
  92. if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
  93. ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
  94. ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
  95. self::$compatibilityMode = $compatibilityMode;
  96. return True;
  97. }
  98. return False;
  99. }
  100. /**
  101. * Return the current Compatibility Mode
  102. *
  103. * @return string $compatibilityMode Compatibility Mode
  104. */
  105. public static function getCompatibilityMode() {
  106. return self::$compatibilityMode;
  107. }
  108. /**
  109. * Set the Return Date Format (Excel, PHP Serialized or PHP Object)
  110. *
  111. * @param integer $returnDateType Return Date Format
  112. * @return boolean Success or failure
  113. */
  114. public static function setReturnDateType($returnDateType) {
  115. if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
  116. ($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
  117. ($returnDateType == self::RETURNDATE_EXCEL)) {
  118. self::$ReturnDateType = $returnDateType;
  119. return True;
  120. }
  121. return False;
  122. } // function setReturnDateType()
  123. /**
  124. * Return the Return Date Format (Excel, PHP Serialized or PHP Object)
  125. *
  126. * @return integer $returnDateType Return Date Format
  127. */
  128. public static function getReturnDateType() {
  129. return self::$ReturnDateType;
  130. } // function getReturnDateType()
  131. /**
  132. * DUMMY
  133. *
  134. * @return string #NAME?
  135. */
  136. public static function DUMMY() {
  137. return self::$_errorCodes['name'];
  138. }
  139. /**
  140. * NA
  141. *
  142. * @return string #N/A!
  143. */
  144. public static function NA() {
  145. return self::$_errorCodes['na'];
  146. }
  147. /**
  148. * LOGICAL_AND
  149. *
  150. * Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
  151. *
  152. * Booleans arguments are treated as True or False as appropriate
  153. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  154. * If any argument value is a string, or a Null, it is ignored
  155. *
  156. * Quirk of Excel:
  157. * String values passed directly to the function rather than through a cell reference
  158. * e.g.=AND(1,"A",1)
  159. * will return a #VALUE! error, _not_ ignoring the string.
  160. * This behaviour is not replicated
  161. *
  162. * @param array of mixed Data Series
  163. * @return boolean
  164. */
  165. public static function LOGICAL_AND() {
  166. // Return value
  167. $returnValue = True;
  168. // Loop through the arguments
  169. $aArgs = self::flattenArray(func_get_args());
  170. $argCount = 0;
  171. foreach ($aArgs as $arg) {
  172. // Is it a boolean value?
  173. if (is_bool($arg)) {
  174. $returnValue = $returnValue && $arg;
  175. $argCount++;
  176. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  177. $returnValue = $returnValue && ($arg != 0);
  178. $argCount++;
  179. }
  180. }
  181. // Return
  182. if ($argCount == 0) {
  183. return self::$_errorCodes['value'];
  184. }
  185. return $returnValue;
  186. }
  187. /**
  188. * LOGICAL_OR
  189. *
  190. * Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
  191. *
  192. * Booleans arguments are treated as True or False as appropriate
  193. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  194. * If any argument value is a string, or a Null, it is ignored
  195. *
  196. * @param array of mixed Data Series
  197. * @return boolean
  198. */
  199. public static function LOGICAL_OR() {
  200. // Return value
  201. $returnValue = False;
  202. // Loop through the arguments
  203. $aArgs = self::flattenArray(func_get_args());
  204. $argCount = 0;
  205. foreach ($aArgs as $arg) {
  206. // Is it a boolean value?
  207. if (is_bool($arg)) {
  208. $returnValue = $returnValue || $arg;
  209. $argCount++;
  210. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  211. $returnValue = $returnValue || ($arg != 0);
  212. $argCount++;
  213. }
  214. }
  215. // Return
  216. if ($argCount == 0) {
  217. return self::$_errorCodes['value'];
  218. }
  219. return $returnValue;
  220. }
  221. /**
  222. * LOGICAL_FALSE
  223. *
  224. * Returns FALSE.
  225. *
  226. * @return boolean
  227. */
  228. public static function LOGICAL_FALSE() {
  229. return False;
  230. }
  231. /**
  232. * LOGICAL_TRUE
  233. *
  234. * Returns TRUE.
  235. *
  236. * @return boolean
  237. */
  238. public static function LOGICAL_TRUE() {
  239. return True;
  240. }
  241. /**
  242. * ATAN2
  243. *
  244. * This function calculates the arc tangent of the two variables x and y. It is similar to
  245. * calculating the arc tangent of y / x, except that the signs of both arguments are used
  246. * to determine the quadrant of the result.
  247. * Note that Excel reverses the arguments, so we need to reverse them here before calling the
  248. * standard PHP atan() function
  249. *
  250. * @param float $x Number
  251. * @param float $y Number
  252. * @return float Square Root of Number * Pi
  253. */
  254. public static function REVERSE_ATAN2($x, $y) {
  255. $x = self::flattenSingleValue($x);
  256. $y = self::flattenSingleValue($y);
  257. return atan2($y, $x);
  258. }
  259. /**
  260. * SUM
  261. *
  262. * SUM computes the sum of all the values and cells referenced in the argument list.
  263. *
  264. * @param array of mixed Data Series
  265. * @return float
  266. */
  267. public static function SUM() {
  268. // Return value
  269. $returnValue = 0;
  270. // Loop through the arguments
  271. $aArgs = self::flattenArray(func_get_args());
  272. foreach ($aArgs as $arg) {
  273. // Is it a numeric value?
  274. if ((is_numeric($arg)) && (!is_string($arg))) {
  275. $returnValue += $arg;
  276. }
  277. }
  278. // Return
  279. return $returnValue;
  280. }
  281. /**
  282. * SUMSQ
  283. *
  284. * Returns the sum of the squares of the arguments
  285. *
  286. * @param array of mixed Data Series
  287. * @return float
  288. */
  289. public static function SUMSQ() {
  290. // Return value
  291. $returnValue = 0;
  292. // Loop trough arguments
  293. $aArgs = self::flattenArray(func_get_args());
  294. foreach ($aArgs as $arg) {
  295. // Is it a numeric value?
  296. if ((is_numeric($arg)) && (!is_string($arg))) {
  297. $returnValue += pow($arg,2);
  298. }
  299. }
  300. // Return
  301. return $returnValue;
  302. }
  303. /**
  304. * PRODUCT
  305. *
  306. * PRODUCT returns the product of all the values and cells referenced in the argument list.
  307. *
  308. * @param array of mixed Data Series
  309. * @return float
  310. */
  311. public static function PRODUCT() {
  312. // Return value
  313. $returnValue = null;
  314. // Loop trough arguments
  315. $aArgs = self::flattenArray(func_get_args());
  316. foreach ($aArgs as $arg) {
  317. // Is it a numeric value?
  318. if ((is_numeric($arg)) && (!is_string($arg))) {
  319. if (is_null($returnValue)) {
  320. $returnValue = $arg;
  321. } else {
  322. $returnValue *= $arg;
  323. }
  324. }
  325. }
  326. // Return
  327. if (is_null($returnValue)) {
  328. return 0;
  329. }
  330. return $returnValue;
  331. }
  332. /**
  333. * QUOTIENT
  334. *
  335. * QUOTIENT function returns the integer portion of a division.numerator is the divided number
  336. * and denominator is the divisor.
  337. *
  338. * @param array of mixed Data Series
  339. * @return float
  340. */
  341. public static function QUOTIENT() {
  342. // Return value
  343. $returnValue = null;
  344. // Loop trough arguments
  345. $aArgs = self::flattenArray(func_get_args());
  346. foreach ($aArgs as $arg) {
  347. // Is it a numeric value?
  348. if ((is_numeric($arg)) && (!is_string($arg))) {
  349. if (is_null($returnValue)) {
  350. if (($returnValue == 0) || ($arg == 0)) {
  351. $returnValue = 0;
  352. } else {
  353. $returnValue = $arg;
  354. }
  355. } else {
  356. if (($returnValue == 0) || ($arg == 0)) {
  357. $returnValue = 0;
  358. } else {
  359. $returnValue /= $arg;
  360. }
  361. }
  362. }
  363. }
  364. // Return
  365. return intval($returnValue);
  366. }
  367. /**
  368. * MIN
  369. *
  370. * MIN returns the value of the element of the values passed that has the smallest value,
  371. * with negative numbers considered smaller than positive numbers.
  372. *
  373. * @param array of mixed Data Series
  374. * @return float
  375. */
  376. public static function MIN() {
  377. // Return value
  378. $returnValue = null;
  379. // Loop trough arguments
  380. $aArgs = self::flattenArray(func_get_args());
  381. foreach ($aArgs as $arg) {
  382. // Is it a numeric value?
  383. if ((is_numeric($arg)) && (!is_string($arg))) {
  384. if ((is_null($returnValue)) || ($arg < $returnValue)) {
  385. $returnValue = $arg;
  386. }
  387. }
  388. }
  389. // Return
  390. if(is_null($returnValue)) {
  391. return 0;
  392. }
  393. return $returnValue;
  394. }
  395. /**
  396. * MINA
  397. *
  398. * Returns the smallest value in a list of arguments, including numbers, text, and logical values
  399. *
  400. * @param array of mixed Data Series
  401. * @return float
  402. */
  403. public static function MINA() {
  404. // Return value
  405. $returnValue = null;
  406. // Loop through arguments
  407. $aArgs = self::flattenArray(func_get_args());
  408. foreach ($aArgs as $arg) {
  409. // Is it a numeric value?
  410. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  411. if (is_bool($arg)) {
  412. $arg = (integer) $arg;
  413. } elseif (is_string($arg)) {
  414. $arg = 0;
  415. }
  416. if ((is_null($returnValue)) || ($arg < $returnValue)) {
  417. $returnValue = $arg;
  418. }
  419. }
  420. }
  421. // Return
  422. if(is_null($returnValue)) {
  423. return 0;
  424. }
  425. return $returnValue;
  426. }
  427. /**
  428. * SMALL
  429. *
  430. * Returns the nth smallest value in a data set. You can use this function to
  431. * select a value based on its relative standing.
  432. *
  433. * @param array of mixed Data Series
  434. * @param float Entry in the series to return
  435. * @return float
  436. */
  437. public static function SMALL() {
  438. $aArgs = self::flattenArray(func_get_args());
  439. // Calculate
  440. $n = array_pop($aArgs);
  441. if ((is_numeric($n)) && (!is_string($n))) {
  442. $mArgs = array();
  443. foreach ($aArgs as $arg) {
  444. // Is it a numeric value?
  445. if ((is_numeric($arg)) && (!is_string($arg))) {
  446. $mArgs[] = $arg;
  447. }
  448. }
  449. $count = self::COUNT($mArgs);
  450. $n = floor(--$n);
  451. if (($n < 0) || ($n >= $count) || ($count == 0)) {
  452. return self::$_errorCodes['num'];
  453. }
  454. sort($mArgs);
  455. return $mArgs[$n];
  456. }
  457. return self::$_errorCodes['value'];
  458. }
  459. /**
  460. * MAX
  461. *
  462. * MAX returns the value of the element of the values passed that has the highest value,
  463. * with negative numbers considered smaller than positive numbers.
  464. *
  465. * @param array of mixed Data Series
  466. * @return float
  467. */
  468. public static function MAX() {
  469. // Return value
  470. $returnValue = null;
  471. // Loop trough arguments
  472. $aArgs = self::flattenArray(func_get_args());
  473. foreach ($aArgs as $arg) {
  474. // Is it a numeric value?
  475. if ((is_numeric($arg)) && (!is_string($arg))) {
  476. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  477. $returnValue = $arg;
  478. }
  479. }
  480. }
  481. // Return
  482. if(is_null($returnValue)) {
  483. return 0;
  484. }
  485. return $returnValue;
  486. }
  487. /**
  488. * MAXA
  489. *
  490. * Returns the greatest value in a list of arguments, including numbers, text, and logical values
  491. *
  492. * @param array of mixed Data Series
  493. * @return float
  494. */
  495. public static function MAXA() {
  496. // Return value
  497. $returnValue = null;
  498. // Loop through arguments
  499. $aArgs = self::flattenArray(func_get_args());
  500. foreach ($aArgs as $arg) {
  501. // Is it a numeric value?
  502. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  503. if (is_bool($arg)) {
  504. $arg = (integer) $arg;
  505. } elseif (is_string($arg)) {
  506. $arg = 0;
  507. }
  508. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  509. $returnValue = $arg;
  510. }
  511. }
  512. }
  513. // Return
  514. if(is_null($returnValue)) {
  515. return 0;
  516. }
  517. return $returnValue;
  518. }
  519. /**
  520. * LARGE
  521. *
  522. * Returns the nth largest value in a data set. You can use this function to
  523. * select a value based on its relative standing.
  524. *
  525. * @param array of mixed Data Series
  526. * @param float Entry in the series to return
  527. * @return float
  528. *
  529. */
  530. public static function LARGE() {
  531. $aArgs = self::flattenArray(func_get_args());
  532. // Calculate
  533. $n = floor(array_pop($aArgs));
  534. if ((is_numeric($n)) && (!is_string($n))) {
  535. $mArgs = array();
  536. foreach ($aArgs as $arg) {
  537. // Is it a numeric value?
  538. if ((is_numeric($arg)) && (!is_string($arg))) {
  539. $mArgs[] = $arg;
  540. }
  541. }
  542. $count = self::COUNT($mArgs);
  543. $n = floor(--$n);
  544. if (($n < 0) || ($n >= $count) || ($count == 0)) {
  545. return self::$_errorCodes['num'];
  546. }
  547. rsort($mArgs);
  548. return $mArgs[$n];
  549. }
  550. return self::$_errorCodes['value'];
  551. }
  552. /**
  553. * PERCENTILE
  554. *
  555. * Returns the nth percentile of values in a range..
  556. *
  557. * @param array of mixed Data Series
  558. * @param float $entry Entry in the series to return
  559. * @return float
  560. */
  561. public static function PERCENTILE() {
  562. $aArgs = self::flattenArray(func_get_args());
  563. // Calculate
  564. $entry = array_pop($aArgs);
  565. if ((is_numeric($entry)) && (!is_string($entry))) {
  566. if (($entry < 0) || ($entry > 1)) {
  567. return self::$_errorCodes['num'];
  568. }
  569. $mArgs = array();
  570. foreach ($aArgs as $arg) {
  571. // Is it a numeric value?
  572. if ((is_numeric($arg)) && (!is_string($arg))) {
  573. $mArgs[] = $arg;
  574. }
  575. }
  576. $mValueCount = count($mArgs);
  577. if ($mValueCount > 0) {
  578. sort($mArgs);
  579. $count = self::COUNT($mArgs);
  580. $index = $entry * ($count-1);
  581. $iBase = floor($index);
  582. if ($index == $iBase) {
  583. return $mArgs[$index];
  584. } else {
  585. $iNext = $iBase + 1;
  586. $iProportion = $index - $iBase;
  587. return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
  588. }
  589. }
  590. }
  591. return self::$_errorCodes['value'];
  592. }
  593. /**
  594. * QUARTILE
  595. *
  596. * Returns the quartile of a data set.
  597. *
  598. * @param array of mixed Data Series
  599. * @param float $entry Entry in the series to return
  600. * @return float
  601. */
  602. public static function QUARTILE() {
  603. $aArgs = self::flattenArray(func_get_args());
  604. // Calculate
  605. $entry = floor(array_pop($aArgs));
  606. if ((is_numeric($entry)) && (!is_string($entry))) {
  607. $entry /= 4;
  608. if (($entry < 0) || ($entry > 1)) {
  609. return self::$_errorCodes['num'];
  610. }
  611. return self::PERCENTILE($aArgs,$entry);
  612. }
  613. return self::$_errorCodes['value'];
  614. }
  615. /**
  616. * COUNT
  617. *
  618. * Counts the number of cells that contain numbers within the list of arguments
  619. *
  620. * @param array of mixed Data Series
  621. * @return int
  622. */
  623. public static function COUNT() {
  624. // Return value
  625. $returnValue = 0;
  626. // Loop trough arguments
  627. $aArgs = self::flattenArray(func_get_args());
  628. foreach ($aArgs as $arg) {
  629. if ((is_bool($arg)) && (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
  630. $arg = (int) $arg;
  631. }
  632. // Is it a numeric value?
  633. if ((is_numeric($arg)) && (!is_string($arg))) {
  634. ++$returnValue;
  635. }
  636. }
  637. // Return
  638. return $returnValue;
  639. }
  640. /**
  641. * COUNTBLANK
  642. *
  643. * Counts the number of empty cells within the list of arguments
  644. *
  645. * @param array of mixed Data Series
  646. * @return int
  647. */
  648. public static function COUNTBLANK() {
  649. // Return value
  650. $returnValue = 0;
  651. // Loop trough arguments
  652. $aArgs = self::flattenArray(func_get_args());
  653. foreach ($aArgs as $arg) {
  654. // Is it a blank cell?
  655. if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
  656. ++$returnValue;
  657. }
  658. }
  659. // Return
  660. return $returnValue;
  661. }
  662. /**
  663. * COUNTA
  664. *
  665. * Counts the number of cells that are not empty within the list of arguments
  666. *
  667. * @param array of mixed Data Series
  668. * @return int
  669. */
  670. public static function COUNTA() {
  671. // Return value
  672. $returnValue = 0;
  673. // Loop through arguments
  674. $aArgs = self::flattenArray(func_get_args());
  675. foreach ($aArgs as $arg) {
  676. // Is it a numeric, boolean or string value?
  677. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  678. ++$returnValue;
  679. }
  680. }
  681. // Return
  682. return $returnValue;
  683. }
  684. /**
  685. * AVERAGE
  686. *
  687. * Returns the average (arithmetic mean) of the arguments
  688. *
  689. * @param array of mixed Data Series
  690. * @return float
  691. */
  692. public static function AVERAGE() {
  693. // Return value
  694. $returnValue = 0;
  695. // Loop through arguments
  696. $aArgs = self::flattenArray(func_get_args());
  697. $aCount = 0;
  698. foreach ($aArgs as $arg) {
  699. if ((is_bool($arg)) && (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
  700. $arg = (integer) $arg;
  701. }
  702. // Is it a numeric value?
  703. if ((is_numeric($arg)) && (!is_string($arg))) {
  704. if (is_null($returnValue)) {
  705. $returnValue = $arg;
  706. } else {
  707. $returnValue += $arg;
  708. }
  709. ++$aCount;
  710. }
  711. }
  712. // Return
  713. if ($aCount > 0) {
  714. return $returnValue / $aCount;
  715. } else {
  716. return self::$_errorCodes['divisionbyzero'];
  717. }
  718. }
  719. /**
  720. * AVERAGEA
  721. *
  722. * Returns the average of its arguments, including numbers, text, and logical values
  723. *
  724. * @param array of mixed Data Series
  725. * @return float
  726. */
  727. public static function AVERAGEA() {
  728. // Return value
  729. $returnValue = null;
  730. // Loop through arguments
  731. $aArgs = self::flattenArray(func_get_args());
  732. $aCount = 0;
  733. foreach ($aArgs as $arg) {
  734. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  735. if (is_bool($arg)) {
  736. $arg = (integer) $arg;
  737. } elseif (is_string($arg)) {
  738. $arg = 0;
  739. }
  740. if (is_null($returnValue)) {
  741. $returnValue = $arg;
  742. } else {
  743. $returnValue += $arg;
  744. }
  745. ++$aCount;
  746. }
  747. }
  748. // Return
  749. if ($aCount > 0) {
  750. return $returnValue / $aCount;
  751. } else {
  752. return self::$_errorCodes['divisionbyzero'];
  753. }
  754. }
  755. /**
  756. * MEDIAN
  757. *
  758. * Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
  759. *
  760. * @param array of mixed Data Series
  761. * @return float
  762. */
  763. public static function MEDIAN() {
  764. // Return value
  765. $returnValue = self::$_errorCodes['num'];
  766. $mArgs = array();
  767. // Loop through arguments
  768. $aArgs = self::flattenArray(func_get_args());
  769. foreach ($aArgs as $arg) {
  770. // Is it a numeric value?
  771. if ((is_numeric($arg)) && (!is_string($arg))) {
  772. $mArgs[] = $arg;
  773. }
  774. }
  775. $mValueCount = count($mArgs);
  776. if ($mValueCount > 0) {
  777. sort($mArgs,SORT_NUMERIC);
  778. $mValueCount = $mValueCount / 2;
  779. if ($mValueCount == floor($mValueCount)) {
  780. $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
  781. } else {
  782. $mValueCount == floor($mValueCount);
  783. $returnValue = $mArgs[$mValueCount];
  784. }
  785. }
  786. // Return
  787. return $returnValue;
  788. }
  789. //
  790. // Special variant of array_count_values that isn't limited to strings and integers,
  791. // but can work with floating point numbers as values
  792. //
  793. private static function modeCalc($data) {
  794. $frequencyArray = array();
  795. foreach($data as $datum) {
  796. $found = False;
  797. foreach($frequencyArray as $key => $value) {
  798. if ((string)$value['value'] == (string)$datum) {
  799. ++$frequencyArray[$key]['frequency'];
  800. $found = True;
  801. break;
  802. }
  803. }
  804. if (!$found) {
  805. $frequencyArray[] = array('value' => $datum,
  806. 'frequency' => 1 );
  807. }
  808. }
  809. foreach($frequencyArray as $key => $value) {
  810. $frequencyList[$key] = $value['frequency'];
  811. $valueList[$key] = $value['value'];
  812. }
  813. array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
  814. if ($frequencyArray[0]['frequency'] == 1) {
  815. return self::NA();
  816. }
  817. return $frequencyArray[0]['value'];
  818. }
  819. /**
  820. * MODE
  821. *
  822. * Returns the most frequently occurring, or repetitive, value in an array or range of data
  823. *
  824. * @param array of mixed Data Series
  825. * @return float
  826. */
  827. public static function MODE() {
  828. // Return value
  829. $returnValue = self::NA();
  830. // Loop through arguments
  831. $aArgs = self::flattenArray(func_get_args());
  832. $mArgs = array();
  833. foreach ($aArgs as $arg) {
  834. // Is it a numeric value?
  835. if ((is_numeric($arg)) && (!is_string($arg))) {
  836. $mArgs[] = $arg;
  837. }
  838. }
  839. if (count($mArgs) > 0) {
  840. return self::modeCalc($mArgs);
  841. }
  842. // Return
  843. return $returnValue;
  844. }
  845. /**
  846. * DEVSQ
  847. *
  848. * Returns the sum of squares of deviations of data points from their sample mean.
  849. *
  850. * @param array of mixed Data Series
  851. * @return float
  852. */
  853. public static function DEVSQ() {
  854. // Return value
  855. $returnValue = null;
  856. $aMean = self::AVERAGE(func_get_args());
  857. if (!is_null($aMean)) {
  858. $aArgs = self::flattenArray(func_get_args());
  859. $aCount = -1;
  860. foreach ($aArgs as $arg) {
  861. // Is it a numeric value?
  862. if ((is_bool($arg)) && (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
  863. $arg = (int) $arg;
  864. }
  865. if ((is_numeric($arg)) && (!is_string($arg))) {
  866. if (is_null($returnValue)) {
  867. $returnValue = pow(($arg - $aMean),2);
  868. } else {
  869. $returnValue += pow(($arg - $aMean),2);
  870. }
  871. ++$aCount;
  872. }
  873. }
  874. // Return
  875. if (is_null($returnValue)) {
  876. return self::$_errorCodes['num'];
  877. } else {
  878. return $returnValue;
  879. }
  880. }
  881. return self::NA();
  882. }
  883. /**
  884. * AVEDEV
  885. *
  886. * Returns the average of the absolute deviations of data points from their mean.
  887. * AVEDEV is a measure of the variability in a data set.
  888. *
  889. * @param array of mixed Data Series
  890. * @return float
  891. */
  892. public static function AVEDEV() {
  893. $aArgs = self::flattenArray(func_get_args());
  894. // Return value
  895. $returnValue = null;
  896. $aMean = self::AVERAGE($aArgs);
  897. if ($aMean != self::$_errorCodes['divisionbyzero']) {
  898. $aCount = 0;
  899. foreach ($aArgs as $arg) {
  900. if ((is_bool($arg)) && (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
  901. $arg = (integer) $arg;
  902. }
  903. // Is it a numeric value?
  904. if ((is_numeric($arg)) && (!is_string($arg))) {
  905. if (is_null($returnValue)) {
  906. $returnValue = abs($arg - $aMean);
  907. } else {
  908. $returnValue += abs($arg - $aMean);
  909. }
  910. ++$aCount;
  911. }
  912. }
  913. // Return
  914. return $returnValue / $aCount ;
  915. }
  916. return self::$_errorCodes['num'];
  917. }
  918. /**
  919. * GEOMEAN
  920. *
  921. * Returns the geometric mean of an array or range of positive data. For example, you
  922. * can use GEOMEAN to calculate average growth rate given compound interest with
  923. * variable rates.
  924. *
  925. * @param array of mixed Data Series
  926. * @return float
  927. */
  928. public static function GEOMEAN() {
  929. $aMean = self::PRODUCT(func_get_args());
  930. if (is_numeric($aMean) && ($aMean > 0)) {
  931. $aArgs = self::flattenArray(func_get_args());
  932. $aCount = self::COUNT($aArgs) ;
  933. if (self::MIN($aArgs) > 0) {
  934. return pow($aMean, (1 / $aCount));
  935. }
  936. }
  937. return self::$_errorCodes['num'];
  938. }
  939. /**
  940. * HARMEAN
  941. *
  942. * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the
  943. * arithmetic mean of reciprocals.
  944. *
  945. * @param array of mixed Data Series
  946. * @return float
  947. */
  948. public static function HARMEAN() {
  949. // Return value
  950. $returnValue = self::NA();
  951. // Loop through arguments
  952. $aArgs = self::flattenArray(func_get_args());
  953. if (self::MIN($aArgs) < 0) {
  954. return self::$_errorCodes['num'];
  955. }
  956. $aCount = 0;
  957. foreach ($aArgs as $arg) {
  958. // Is it a numeric value?
  959. if ((is_numeric($arg)) && (!is_string($arg))) {
  960. if ($arg <= 0) {
  961. return self::$_errorCodes['num'];
  962. }
  963. if (is_null($returnValue)) {
  964. $returnValue = (1 / $arg);
  965. } else {
  966. $returnValue += (1 / $arg);
  967. }
  968. ++$aCount;
  969. }
  970. }
  971. // Return
  972. if ($aCount > 0) {
  973. return 1 / ($returnValue / $aCount);
  974. } else {
  975. return $returnValue;
  976. }
  977. }
  978. /**
  979. * TRIMMEAN
  980. *
  981. * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
  982. * taken by excluding a percentage of data points from the top and bottom tails
  983. * of a data set.
  984. *
  985. * @param array of mixed Data Series
  986. * @param float Percentage to discard
  987. * @return float
  988. */
  989. public static function TRIMMEAN() {
  990. $aArgs = self::flattenArray(func_get_args());
  991. // Calculate
  992. $percent = array_pop($aArgs);
  993. if ((is_numeric($percent)) && (!is_string($percent))) {
  994. if (($percent < 0) || ($percent > 1)) {
  995. return self::$_errorCodes['num'];
  996. }
  997. $mArgs = array();
  998. foreach ($aArgs as $arg) {
  999. // Is it a numeric value?
  1000. if ((is_numeric($arg)) && (!is_string($arg))) {
  1001. $mArgs[] = $arg;
  1002. }
  1003. }
  1004. $discard = floor(self::COUNT($mArgs) * $percent / 2);
  1005. sort($mArgs);
  1006. for ($i=0; $i < $discard; ++$i) {
  1007. array_pop($mArgs);
  1008. array_shift($mArgs);
  1009. }
  1010. return self::AVERAGE($mArgs);
  1011. }
  1012. return self::$_errorCodes['value'];
  1013. }
  1014. /**
  1015. * STDEV
  1016. *
  1017. * Estimates standard deviation based on a sample. The standard deviation is a measure of how
  1018. * widely values are dispersed from the average value (the mean).
  1019. *
  1020. * @param array of mixed Data Series
  1021. * @return float
  1022. */
  1023. public static function STDEV() {
  1024. // Return value
  1025. $returnValue = null;
  1026. $aMean = self::AVERAGE(func_get_args());
  1027. if (!is_null($aMean)) {
  1028. $aArgs = self::flattenArray(func_get_args());
  1029. $aCount = -1;
  1030. foreach ($aArgs as $arg) {
  1031. // Is it a numeric value?
  1032. if ((is_numeric($arg)) && (!is_string($arg))) {
  1033. if (is_null($returnValue)) {
  1034. $returnValue = pow(($arg - $aMean),2);
  1035. } else {
  1036. $returnValue += pow(($arg - $aMean),2);
  1037. }
  1038. ++$aCount;
  1039. }
  1040. }
  1041. // Return
  1042. if (($aCount > 0) && ($returnValue > 0)) {
  1043. return sqrt($returnValue / $aCount);
  1044. }
  1045. }
  1046. return self::$_errorCodes['divisionbyzero'];
  1047. }
  1048. /**
  1049. * STDEVA
  1050. *
  1051. * Estimates standard deviation based on a sample, including numbers, text, and logical values
  1052. *
  1053. * @param array of mixed Data Series
  1054. * @return float
  1055. */
  1056. public static function STDEVA() {
  1057. // Return value
  1058. $returnValue = null;
  1059. $aMean = self::AVERAGEA(func_get_args());
  1060. if (!is_null($aMean)) {
  1061. $aArgs = self::flattenArray(func_get_args());
  1062. $aCount = -1;
  1063. foreach ($aArgs as $arg) {
  1064. // Is it a numeric value?
  1065. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  1066. if (is_bool($arg)) {
  1067. $arg = (integer) $arg;
  1068. } elseif (is_string($arg)) {
  1069. $arg = 0;
  1070. }
  1071. if (is_null($returnValue)) {
  1072. $returnValue = pow(($arg - $aMean),2);
  1073. } else {
  1074. $returnValue += pow(($arg - $aMean),2);
  1075. }
  1076. ++$aCount;
  1077. }
  1078. }
  1079. // Return
  1080. if (($aCount > 0) && ($returnValue > 0)) {
  1081. return sqrt($returnValue / $aCount);
  1082. }
  1083. }
  1084. return self::$_errorCodes['divisionbyzero'];
  1085. }
  1086. /**
  1087. * STDEVP
  1088. *
  1089. * Calculates standard deviation based on the entire population
  1090. *
  1091. * @param array of mixed Data Series
  1092. * @return float
  1093. */
  1094. public static function STDEVP() {
  1095. // Return value
  1096. $returnValue = null;
  1097. $aMean = self::AVERAGE(func_get_args());
  1098. if (!is_null($aMean)) {
  1099. $aArgs = self::flattenArray(func_get_args());
  1100. $aCount = 0;
  1101. foreach ($aArgs as $arg) {
  1102. // Is it a numeric value?
  1103. if ((is_numeric($arg)) && (!is_string($arg))) {
  1104. if (is_null($returnValue)) {
  1105. $returnValue = pow(($arg - $aMean),2);
  1106. } else {
  1107. $returnValue += pow(($arg - $aMean),2);
  1108. }
  1109. ++$aCount;
  1110. }
  1111. }
  1112. // Return
  1113. if (($aCount > 0) && ($returnValue > 0)) {
  1114. return sqrt($returnValue / $aCount);
  1115. }
  1116. }
  1117. return self::$_errorCodes['divisionbyzero'];
  1118. }
  1119. /**
  1120. * STDEVPA
  1121. *
  1122. * Calculates standard deviation based on the entire population, including numbers, text, and logical values
  1123. *
  1124. * @param array of mixed Data Series
  1125. * @return float
  1126. */
  1127. public static function STDEVPA() {
  1128. // Return value
  1129. $returnValue = null;
  1130. $aMean = self::AVERAGEA(func_get_args());
  1131. if (!is_null($aMean)) {
  1132. $aArgs = self::flattenArray(func_get_args());
  1133. $aCount = 0;
  1134. foreach ($aArgs as $arg) {
  1135. // Is it a numeric value?
  1136. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  1137. if (is_bool($arg)) {
  1138. $arg = (integer) $arg;
  1139. } elseif (is_string($arg)) {
  1140. $arg = 0;
  1141. }
  1142. if (is_null($returnValue)) {
  1143. $returnValue = pow(($arg - $aMean),2);
  1144. } else {
  1145. $returnValue += pow(($arg - $aMean),2);
  1146. }
  1147. ++$aCount;
  1148. }
  1149. }
  1150. // Return
  1151. if (($aCount > 0) && ($returnValue > 0)) {
  1152. return sqrt($returnValue / $aCount);
  1153. }
  1154. }
  1155. return self::$_errorCodes['divisionbyzero'];
  1156. }
  1157. /**
  1158. * VARFunc
  1159. *
  1160. * Estimates variance based on a sample.
  1161. *
  1162. * @param array of mixed Data Series
  1163. * @return float
  1164. */
  1165. public static function VARFunc() {
  1166. // Return value
  1167. $returnValue = self::$_errorCodes['divisionbyzero'];
  1168. $summerA = $summerB = 0;
  1169. // Loop through arguments
  1170. $aArgs = self::flattenArray(func_get_args());
  1171. $aCount = 0;
  1172. foreach ($aArgs as $arg) {
  1173. // Is it a numeric value?
  1174. if ((is_numeric($arg)) && (!is_string($arg))) {
  1175. $summerA += ($arg * $arg);
  1176. $summerB += $arg;
  1177. ++$aCount;
  1178. }
  1179. }
  1180. // Return
  1181. if ($aCount > 1) {
  1182. $summerA = $summerA * $aCount;
  1183. $summerB = ($summerB * $summerB);
  1184. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
  1185. }
  1186. return $returnValue;
  1187. }
  1188. /**
  1189. * VARA
  1190. *
  1191. * Estimates variance based on a sample, including numbers, text, and logical values
  1192. *
  1193. * @param array of mixed Data Series
  1194. * @return float
  1195. */
  1196. public static function VARA() {
  1197. // Return value
  1198. $returnValue = self::$_errorCodes['divisionbyzero'];
  1199. $summerA = $summerB = 0;
  1200. // Loop through arguments
  1201. $aArgs = self::flattenArray(func_get_args());
  1202. $aCount = 0;
  1203. foreach ($aArgs as $arg) {
  1204. // Is it a numeric value?
  1205. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  1206. if (is_bool($arg)) {
  1207. $arg = (integer) $arg;
  1208. } elseif (is_string($arg)) {
  1209. $arg = 0;
  1210. }
  1211. $summerA += ($arg * $arg);
  1212. $summerB += $arg;
  1213. ++$aCount;
  1214. }
  1215. }
  1216. // Return
  1217. if ($aCount > 1) {
  1218. $summerA = $summerA * $aCount;
  1219. $summerB = ($summerB * $summerB);
  1220. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
  1221. }
  1222. return $returnValue;
  1223. }
  1224. /**
  1225. * VARP
  1226. *
  1227. * Calculates variance based on the entire population
  1228. *
  1229. * @param array of mixed Data Series
  1230. * @return float
  1231. */
  1232. public static function VARP() {
  1233. // Return value
  1234. $returnValue = self::$_errorCodes['divisionbyzero'];
  1235. $summerA = $summerB = 0;
  1236. // Loop through arguments
  1237. $aArgs = self::flattenArray(func_get_args());
  1238. $aCount = 0;
  1239. foreach ($aArgs as $arg) {
  1240. // Is it a numeric value?
  1241. if ((is_numeric($arg)) && (!is_string($arg))) {
  1242. $summerA += ($arg * $arg);
  1243. $summerB += $arg;
  1244. ++$aCount;
  1245. }
  1246. }
  1247. // Return
  1248. if ($aCount > 0) {
  1249. $summerA = $summerA * $aCount;
  1250. $summerB = ($summerB * $summerB);
  1251. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
  1252. }
  1253. return $returnValue;
  1254. }
  1255. /**
  1256. * VARPA
  1257. *
  1258. * Calculates variance based on the entire population, including numbers, text, and logical values
  1259. *
  1260. * @param array of mixed Data Series
  1261. * @return float
  1262. */
  1263. public static function VARPA() {
  1264. // Return value
  1265. $returnValue = self::$_errorCodes['divisionbyzero'];
  1266. $summerA = $summerB = 0;
  1267. // Loop through arguments
  1268. $aArgs = self::flattenArray(func_get_args());
  1269. $aCount = 0;
  1270. foreach ($aArgs as $arg) {
  1271. // Is it a numeric value?
  1272. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  1273. if (is_bool($arg)) {
  1274. $arg = (integer) $arg;
  1275. } elseif (is_string($arg)) {
  1276. $arg = 0;
  1277. }
  1278. $summerA += ($arg * $arg);
  1279. $summerB += $arg;
  1280. ++$aCount;
  1281. }
  1282. }
  1283. // Return
  1284. if ($aCount > 0) {
  1285. $summerA = $summerA * $aCount;
  1286. $summerB = ($summerB * $summerB);
  1287. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
  1288. }
  1289. return $returnValue;
  1290. }
  1291. /**
  1292. * SUBTOTAL
  1293. *
  1294. * Returns a subtotal in a list or database.
  1295. *
  1296. * @param int the number 1 to 11 that specifies which function to
  1297. * use in calculating subtotals within a list.
  1298. * @param array of mixed Data Series
  1299. * @return float
  1300. */
  1301. public static function SUBTOTAL() {
  1302. $aArgs = self::flattenArray(func_get_args());
  1303. // Calculate
  1304. $subtotal = array_shift($aArgs);
  1305. if ((is_numeric($subtotal)) && (!is_string($subtotal))) {
  1306. switch($subtotal) {
  1307. case 1 :
  1308. return self::AVERAGE($aArgs);
  1309. break;
  1310. case 2 :
  1311. return self::COUNT($aArgs);
  1312. break;
  1313. case 3 :
  1314. return self::COUNTA($aArgs);
  1315. break;
  1316. case 4 :
  1317. return self::MAX($aArgs);
  1318. break;
  1319. case 5 :
  1320. return self::MIN($aArgs);
  1321. break;
  1322. case 6 :
  1323. return self::PRODUCT($aArgs);
  1324. break;
  1325. case 7 :
  1326. return self::STDEV($aArgs);
  1327. break;
  1328. case 8 :
  1329. return self::STDEVP($aArgs);
  1330. break;
  1331. case 9 :
  1332. return self::SUM($aArgs);
  1333. break;
  1334. case 10 :
  1335. return self::VARFunc($aArgs);
  1336. break;
  1337. case 11 :
  1338. return self::VARP($aArgs);
  1339. break;
  1340. }
  1341. }
  1342. return self::$_errorCodes['value'];
  1343. }
  1344. /**
  1345. * SQRTPI
  1346. *
  1347. * Returns the square root of (number * pi).
  1348. *
  1349. * @param float $number Number
  1350. * @return float Square Root of Number * Pi
  1351. */
  1352. public static function SQRTPI($number) {
  1353. $number = self::flattenSingleValue($number);
  1354. if (is_numeric($number)) {
  1355. if ($number < 0) {
  1356. return self::$_errorCodes['num'];
  1357. }
  1358. return sqrt($number * pi()) ;
  1359. }
  1360. return self::$_errorCodes['value'];
  1361. }
  1362. /**
  1363. * FACT
  1364. *
  1365. * Returns the factorial of a number.
  1366. *
  1367. * @param float $factVal Factorial Value
  1368. * @return int Factorial
  1369. */
  1370. public static function FACT($factVal) {
  1371. $factVal = self::flattenSingleValue($factVal);
  1372. if (is_numeric($factVal)) {
  1373. if ($factVal < 0) {
  1374. return self::$_errorCodes['num'];
  1375. }
  1376. $factLoop = floor($factVal);
  1377. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  1378. if ($factVal > $factLoop) {
  1379. return self::$_errorCodes['num'];
  1380. }
  1381. }
  1382. $factorial = 1;
  1383. while ($factLoop > 1) {
  1384. $factorial *= $factLoop--;
  1385. }
  1386. return $factorial ;
  1387. }
  1388. return self::$_errorCodes['value'];
  1389. }
  1390. /**
  1391. * FACTDOUBLE
  1392. *
  1393. * Returns the double factorial of a number.
  1394. *
  1395. * @param float $factVal Factorial Value
  1396. * @return int Double Factorial
  1397. */
  1398. public static function FACTDOUBLE($factVal) {
  1399. $factLoop = floor(self::flattenSingleValue($factVal));
  1400. if (is_numeric($factLoop)) {
  1401. if ($factVal < 0) {
  1402. return self::$_errorCodes['num'];
  1403. }
  1404. $factorial = 1;
  1405. while ($factLoop > 1) {
  1406. $factorial *= $factLoop--;
  1407. --$factLoop;
  1408. }
  1409. return $factorial ;
  1410. }
  1411. return self::$_errorCodes['value'];
  1412. }
  1413. /**
  1414. * MULTINOMIAL
  1415. *
  1416. * Returns the ratio of the factorial of a sum of values to the product of factorials.
  1417. *
  1418. * @param array of mixed Data Series
  1419. * @return float
  1420. */
  1421. public static function MULTINOMIAL() {
  1422. // Loop through arguments
  1423. $aArgs = self::flattenArray(func_get_args());
  1424. $summer = 0;
  1425. $divisor = 1;
  1426. foreach ($aArgs as $arg) {
  1427. // Is it a numeric value?
  1428. if (is_numeric($arg)) {
  1429. if ($arg < 1) {
  1430. return self::$_errorCodes['num'];
  1431. }
  1432. $summer += floor($arg);
  1433. $divisor *= self::FACT($arg);
  1434. } else {
  1435. return self::$_errorCodes['value'];
  1436. }
  1437. }
  1438. // Return
  1439. if ($summer > 0) {
  1440. $summer = self::FACT($summer);
  1441. return $summer / $divisor;
  1442. }
  1443. return 0;
  1444. }
  1445. /**
  1446. * CEILING
  1447. *
  1448. * Returns number rounded up, away from zero, to the nearest multiple of significance.
  1449. *
  1450. * @param float $number Number to round
  1451. * @param float $significance Significance
  1452. * @return float Rounded Number
  1453. */
  1454. public static function CEILING($number,$significance=null) {
  1455. $number = self::flattenSingleValue($number);
  1456. $significance = self::flattenSingleValue($significance);
  1457. if ((is_null($significance)) && (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC)) {
  1458. $significance = $number/abs($number);
  1459. }
  1460. if ((is_numeric($number)) && (is_numeric($significance))) {
  1461. if (self::SIGN($number) == self::SIGN($significance)) {
  1462. if ($significance == 0.0) {
  1463. return 0;
  1464. }
  1465. return ceil($number / $significance) * $significance;
  1466. } else {
  1467. return self::$_errorCodes['num'];
  1468. }
  1469. }
  1470. return self::$_errorCodes['value'];
  1471. }
  1472. /**
  1473. * EVEN
  1474. *
  1475. * Returns number rounded up to the nearest even integer.
  1476. *
  1477. * @param float $number Number to round
  1478. * @return int Rounded Number
  1479. */
  1480. public static function EVEN($number) {
  1481. $number = self::flattenSingleValue($number);
  1482. if (is_numeric($number)) {
  1483. $significance = 2 * self::SIGN($number);
  1484. return self::CEILING($number,$significance);
  1485. }
  1486. return self::$_errorCodes['value'];
  1487. }
  1488. /**
  1489. * ODD
  1490. *
  1491. * Returns number rounded up to the nearest odd integer.
  1492. *
  1493. * @param float $number Number to round
  1494. * @return int Rounded Number
  1495. */
  1496. public static function ODD($number) {
  1497. $number = self::flattenSingleValue($number);
  1498. if (is_numeric($number)) {
  1499. $significance = self::SIGN($number);
  1500. if ($significance == 0) {
  1501. return 1;
  1502. }
  1503. $result = self::CEILING($number,$significance);
  1504. if (self::IS_EVEN($result)) {
  1505. $result += $significance;
  1506. }
  1507. return $result;
  1508. }
  1509. return self::$_errorCodes['value'];
  1510. }
  1511. /**
  1512. * ROUNDUP
  1513. *
  1514. * Rounds a number up to a specified number of decimal places
  1515. *
  1516. * @param float $number Number to round
  1517. * @param int $digits Number of digits to which you want to round $number
  1518. * @return float Rounded Number
  1519. */
  1520. public static function ROUNDUP($number,$digits) {
  1521. $number = self::flattenSingleValue($number);
  1522. $digits = self::flattenSingleValue($digits);
  1523. if (is_numeric($number)) {
  1524. if ((is_numeric($digits)) && ($digits >= 0)) {
  1525. $significance = pow(10,$digits);
  1526. return ceil($number * $significance) / $significance;
  1527. }
  1528. }
  1529. return self::$_errorCodes['value'];
  1530. }
  1531. /**
  1532. * ROUNDDOWN
  1533. *
  1534. * Rounds a number down to a specified number of decimal places
  1535. *
  1536. * @param float $number Number to round
  1537. * @param int $digits Number of digits to which you want to round $number
  1538. * @return float Rounded Number
  1539. */
  1540. public static function ROUNDDOWN($number,$digits) {
  1541. $number = self::flattenSingleValue($number);
  1542. $digits = self::flattenSingleValue($digits);
  1543. if (is_numeric($number)) {
  1544. if ((is_numeric($digits)) && ($digits >= 0)) {
  1545. $significance = pow(10,$digits);
  1546. return floor($number * $significance) / $significance;
  1547. }
  1548. }
  1549. return self::$_errorCodes['value'];
  1550. }
  1551. /**
  1552. * MROUND
  1553. *
  1554. * Rounds a number to the nearest multiple of a specified value
  1555. *
  1556. * @param float $number Number to round
  1557. * @param int $multiple Multiple to which you want to round $number
  1558. * @return float Rounded Number
  1559. */
  1560. public static function MROUND($number,$multiple) {
  1561. $number = self::flattenSingleValue($number);
  1562. $multiple = self::flattenSingleValue($multiple);
  1563. if ((is_numeric($number)) && (is_numeric($multiple))) {
  1564. if ((self::SIGN($number)) == (self::SIGN($multiple))) {
  1565. $lowerVal = floor($number / $multiple) * $multiple;
  1566. $upperVal = ceil($number / $multiple) * $multiple;
  1567. $adjustUp = abs($number - $upperVal);
  1568. $adjustDown = abs($number - $lowerVal) + PRECISION;
  1569. if ($adjustDown < $adjustUp) {
  1570. return $lowerVal;
  1571. }
  1572. return $upperVal;
  1573. }
  1574. return self::$_errorCodes['num'];
  1575. }
  1576. return self::$_errorCodes['value'];
  1577. }
  1578. /**
  1579. * SIGN
  1580. *
  1581. * Determines the sign of a number. Returns 1 if the number is positive, zero (0)
  1582. * if the number is 0, and -1 if the number is negative.
  1583. *
  1584. * @param float $number Number to round
  1585. * @return int sign value
  1586. */
  1587. public static function SIGN($number) {
  1588. $number = self::flattenSingleValue($number);
  1589. if (is_numeric($number)) {
  1590. if ($number == 0.0) {
  1591. return 0;
  1592. }
  1593. return $number / abs($number);
  1594. }
  1595. return self::$_errorCodes['value'];
  1596. }
  1597. /**
  1598. * FLOOR
  1599. *
  1600. * Rounds number down, toward zero, to the nearest multiple of significance.
  1601. *
  1602. * @param float $number Number to round
  1603. * @param float $significance Significance
  1604. * @return float Rounded Number
  1605. */
  1606. public static function FLOOR($number,$significance=null) {
  1607. $number = self::flattenSingleValue($number);
  1608. $significance = self::flattenSingleValue($significance);
  1609. if ((is_null($significance)) && (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC)) {
  1610. $significance = $number/abs($number);
  1611. }
  1612. if ((is_numeric($number)) && (is_numeric($significance))) {
  1613. if ((float) $significance == 0.0) {
  1614. return self::$_errorCodes['divisionbyzero'];
  1615. }
  1616. if (self::SIGN($number) == self::SIGN($significance)) {
  1617. return floor($number / $significance) * $significance;
  1618. } else {
  1619. return self::$_errorCodes['num'];
  1620. }
  1621. }
  1622. return self::$_errorCodes['value'];
  1623. }
  1624. /**
  1625. * PERMUT
  1626. *
  1627. * Returns the number of permutations for a given number of objects that can be
  1628. * selected from number objects. A permutation is any set or subset of objects or
  1629. * events where internal order is significant. Permutations are different from
  1630. * combinations, for which the internal order is not significant. Use this function
  1631. * for lottery-style probability calculations.
  1632. *
  1633. * @param int $numObjs Number of different objects
  1634. * @param int $numInSet Number of objects in each permutation
  1635. * @return int Number of permutations
  1636. */
  1637. public static function PERMUT($numObjs,$numInSet) {
  1638. $numObjs = self::flattenSingleValue($numObjs);
  1639. $numInSet = self::flattenSingleValue($numInSet);
  1640. if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
  1641. if ($numObjs < $numInSet) {
  1642. return self::$_errorCodes['num'];
  1643. }
  1644. return self::FACT($numObjs) / self::FACT($numObjs - $numInSet);
  1645. }
  1646. return self::$_errorCodes['value'];
  1647. }
  1648. /**
  1649. * COMBIN
  1650. *
  1651. * Returns the number of combinations for a given number of items. Use COMBIN to
  1652. * determine the total possible number of groups for a given number of items.
  1653. *
  1654. * @param int $numObjs Number of different objects
  1655. * @param int $numInSet Number of objects in each combination
  1656. * @return int Number of combinations
  1657. */
  1658. public static function COMBIN($numObjs,$numInSet) {
  1659. $numObjs = self::flattenSingleValue($numObjs);
  1660. $numInSet = self::flattenSingleValue($numInSet);
  1661. if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
  1662. if ($numObjs < $numInSet) {
  1663. return self::$_errorCodes['num'];
  1664. } elseif ($numInSet < 0) {
  1665. return self::$_errorCodes['num'];
  1666. }
  1667. return (self::FACT($numObjs) / self::FACT($numObjs - $numInSet)) / self::FACT($numInSet);
  1668. }
  1669. return self::$_errorCodes['value'];
  1670. }
  1671. /**
  1672. * SERIESSUM
  1673. *
  1674. * Returns the sum of a power series
  1675. *
  1676. * @param float $x Input value to the power series
  1677. * @param float $n Initial power to which you want to raise $x
  1678. * @param float $m Step by which to increase $n for each term in the series
  1679. * @param array of mixed Data Series
  1680. * @return float
  1681. */
  1682. public static function SERIESSUM() {
  1683. // Return value
  1684. $returnValue = 0;
  1685. // Loop trough arguments
  1686. $aArgs = self::flattenArray(func_get_args());
  1687. $x = array_shift($aArgs);
  1688. $n = array_shift($aArgs);
  1689. $m = array_shift($aArgs);
  1690. if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) {
  1691. // Calculate
  1692. $i = 0;
  1693. foreach($aArgs as $arg) {
  1694. // Is it a numeric value?
  1695. if ((is_numeric($arg)) && (!is_string($arg))) {
  1696. $returnValue += $arg * pow($x,$n + ($m * $i++));
  1697. } else {
  1698. return self::$_errorCodes['value'];
  1699. }
  1700. }
  1701. // Return
  1702. return $returnValue;
  1703. }
  1704. return self::$_errorCodes['value'];
  1705. }
  1706. /**
  1707. * STANDARDIZE
  1708. *
  1709. * Returns a normalized value from a distribution characterized by mean and standard_dev.
  1710. *
  1711. * @param float $value Value to normalize
  1712. * @param float $mean Mean Value
  1713. * @param float $stdDev Standard Deviation
  1714. * @return float Standardized value
  1715. */
  1716. public static function STANDARDIZE($value,$mean,$stdDev) {
  1717. $value = self::flattenSingleValue($value);
  1718. $mean = self::flattenSingleValue($mean);
  1719. $stdDev = self::flattenSingleValue($stdDev);
  1720. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  1721. if ($stdDev <= 0) {
  1722. return self::$_errorCodes['num'];
  1723. }
  1724. return ($value - $mean) / $stdDev ;
  1725. }
  1726. return self::$_errorCodes['value'];
  1727. }
  1728. //
  1729. // Private method to return an array of the factors of the input value
  1730. //
  1731. private static function factors($value) {
  1732. $startVal = floor($value/2);
  1733. $factorArray = array();
  1734. for($i=$startVal; $i>1; --$i) {
  1735. if (($value/$i) == floor($value/$i)) {
  1736. $subFactors = self::factors($i);
  1737. if ($i == sqrt($value)) {
  1738. $factorArray = array_merge($factorArray,$subFactors,$subFactors);
  1739. } else {
  1740. $value /= $i;
  1741. $factorArray = array_merge($factorArray,$subFactors);
  1742. }
  1743. }
  1744. }
  1745. if (count($factorArray) > 0) {
  1746. return $factorArray;
  1747. } else {
  1748. return array((integer)$value);
  1749. }
  1750. }
  1751. /**
  1752. * LCM
  1753. *
  1754. * Returns the lowest common multiplier of a series of numbers
  1755. *
  1756. * @param $array Values to calculate the Lowest Common Multiplier
  1757. * @return int Lowest Common Multiplier
  1758. */
  1759. public static function LCM() {
  1760. $aArgs = self::flattenArray(func_get_args());
  1761. $returnValue = 1;
  1762. $allPoweredFactors = array();
  1763. foreach($aArgs as $value) {
  1764. if (!is_numeric($value)) {
  1765. return self::$_errorCodes['value'];
  1766. }
  1767. if ($value < 1) {
  1768. return self::$_errorCodes['num'];
  1769. }
  1770. $myFactors = self::factors(floor($value));
  1771. $myCountedFactors = array_count_values($myFactors);
  1772. $myPoweredFactors = array();
  1773. foreach($myCountedFactors as $myCountedFactor => $myCountedPower) {
  1774. $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor,$myCountedPower);
  1775. }
  1776. foreach($myPoweredFactors as $myPoweredValue => $myPoweredFactor) {
  1777. if (array_key_exists($myPoweredValue,$allPoweredFactors)) {
  1778. if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) {
  1779. $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
  1780. }
  1781. } else {
  1782. $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
  1783. }
  1784. }
  1785. }
  1786. foreach($allPoweredFactors as $allPoweredFactor) {
  1787. $returnValue *= (integer) $allPoweredFactor;
  1788. }
  1789. return $returnValue;
  1790. }
  1791. /**
  1792. * GCD
  1793. *
  1794. * Returns the greatest common divisor of a series of numbers
  1795. *
  1796. * @param $array Values to calculate the Greatest Common Divisor
  1797. * @return int Greatest Common Divisor
  1798. */
  1799. public static function GCD() {
  1800. $aArgs = self::flattenArray(func_get_args());
  1801. $returnValue = 1;
  1802. $allPoweredFactors = array();
  1803. foreach($aArgs as $value) {
  1804. if ($value == 0) {
  1805. return 0;
  1806. }
  1807. $myFactors = self::factors($value);
  1808. $myCountedFactors = array_count_values($myFactors);
  1809. $allValuesFactors[] = $myCountedFactors;
  1810. }
  1811. $allValuesCount = count($allValuesFactors);
  1812. $mergedArray = $allValuesFactors[0];
  1813. for ($i=1;$i < $allValuesCount; ++$i) {
  1814. $mergedArray = array_intersect_key($mergedArray,$allValuesFactors[$i]);
  1815. }
  1816. $mergedArrayValues = count($mergedArray);
  1817. if ($mergedArrayValues == 0) {
  1818. return $returnValue;
  1819. } elseif ($mergedArrayValues > 1) {
  1820. foreach($mergedArray as $mergedKey => $mergedValue) {
  1821. foreach($allValuesFactors as $highestPowerTest) {
  1822. foreach($highestPowerTest as $testKey => $testValue) {
  1823. if (($testKey == $mergedKey) && ($testValue < $mergedValue)) {
  1824. $mergedArray[$mergedKey] = $testValue;
  1825. $mergedValue = $testValue;
  1826. }
  1827. }
  1828. }
  1829. }
  1830. $returnValue = 1;
  1831. foreach($mergedArray as $key => $value) {
  1832. $returnValue *= pow($key,$value);
  1833. }
  1834. return $returnValue;
  1835. } else {
  1836. $keys = array_keys($mergedArray);
  1837. $key = $keys[0];
  1838. $value = $mergedArray[$key];
  1839. foreach($allValuesFactors as $testValue) {
  1840. foreach($testValue as $mergedKey => $mergedValue) {
  1841. if (($mergedKey == $key) && ($mergedValue < $value)) {
  1842. $value = $mergedValue;
  1843. }
  1844. }
  1845. }
  1846. return pow($key,$value);
  1847. }
  1848. }
  1849. /**
  1850. * BINOMDIST
  1851. *
  1852. * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
  1853. * a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
  1854. * when trials are independent, and when the probability of success is constant throughout the
  1855. * experiment. For example, BINOMDIST can calculate the probability that two of the next three
  1856. * babies born are male.
  1857. *
  1858. * @param float $value Number of successes in trials
  1859. * @param float $trials Number of trials
  1860. * @param float $probability Probability of success on each trial
  1861. * @param boolean $cumulative
  1862. * @return float
  1863. *
  1864. * @todo Cumulative distribution function
  1865. *
  1866. */
  1867. public static function BINOMDIST($value, $trials, $probability, $cumulative) {
  1868. $value = floor(self::flattenSingleValue($value));
  1869. $trials = floor(self::flattenSingleValue($trials));
  1870. $probability = self::flattenSingleValue($probability);
  1871. if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
  1872. if (($value < 0) || ($value > $trials)) {
  1873. return self::$_errorCodes['num'];
  1874. }
  1875. if (($probability < 0) || ($probability > 1)) {
  1876. return self::$_errorCodes['num'];
  1877. }
  1878. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  1879. if ($cumulative) {
  1880. $summer = 0;
  1881. for ($i = 0; $i <= $value; ++$i) {
  1882. $summer += self::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
  1883. }
  1884. return $summer;
  1885. } else {
  1886. return self::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
  1887. }
  1888. }
  1889. }
  1890. return self::$_errorCodes['value'];
  1891. }
  1892. /**
  1893. * NEGBINOMDIST
  1894. *
  1895. * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that
  1896. * there will be number_f failures before the number_s-th success, when the constant
  1897. * probability of a success is probability_s. This function is similar to the binomial
  1898. * distribution, except that the number of successes is fixed, and the number of trials is
  1899. * variable. Like the binomial, trials are assumed to be independent.
  1900. *
  1901. * @param float $failures Number of Failures
  1902. * @param float $successes Threshold number of Successes
  1903. * @param float $probability Probability of success on each trial
  1904. * @return float
  1905. *
  1906. */
  1907. public static function NEGBINOMDIST($failures, $successes, $probability) {
  1908. $failures = floor(self::flattenSingleValue($failures));
  1909. $successes = floor(self::flattenSingleValue($successes));
  1910. $probability = self::flattenSingleValue($probability);
  1911. if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) {
  1912. if (($failures < 0) || ($successes < 1)) {
  1913. return self::$_errorCodes['num'];
  1914. }
  1915. if (($probability < 0) || ($probability > 1)) {
  1916. return self::$_errorCodes['num'];
  1917. }
  1918. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  1919. if (($failures + $successes - 1) <= 0) {
  1920. return self::$_errorCodes['num'];
  1921. }
  1922. }
  1923. return (self::COMBIN($failures + $successes - 1,$successes - 1)) * (pow($probability,$successes)) * (pow(1 - $probability,$failures)) ;
  1924. }
  1925. return self::$_errorCodes['value'];
  1926. }
  1927. /**
  1928. * CRITBINOM
  1929. *
  1930. * Returns the smallest value for which the cumulative binomial distribution is greater
  1931. * than or equal to a criterion value
  1932. *
  1933. * See http://support.microsoft.com/kb/828117/ for details of the algorithm used
  1934. *
  1935. * @param float $trials number of Bernoulli trials
  1936. * @param float $probability probability of a success on each trial
  1937. * @param float $alpha criterion value
  1938. * @return int
  1939. *
  1940. * @todo Warning. This implementation differs from the algorithm detailed on the MS
  1941. * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
  1942. * This eliminates a potential endless loop error, but may have an adverse affect on the
  1943. * accuracy of the function (although all my tests have so far returned correct results).
  1944. *
  1945. */
  1946. public static function CRITBINOM($trials, $probability, $alpha) {
  1947. $trials = floor(self::flattenSingleValue($trials));
  1948. $probability = self::flattenSingleValue($probability);
  1949. $alpha = self::flattenSingleValue($alpha);
  1950. if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
  1951. if ($trials < 0) {
  1952. return self::$_errorCodes['num'];
  1953. }
  1954. if (($probability < 0) || ($probability > 1)) {
  1955. return self::$_errorCodes['num'];
  1956. }
  1957. if (($alpha < 0) || ($alpha > 1)) {
  1958. return self::$_errorCodes['num'];
  1959. }
  1960. if ($alpha <= 0.5) {
  1961. $t = sqrt(log(1 / pow($alpha,2)));
  1962. $trialsApprox = 0 - ($t + (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t));
  1963. } else {
  1964. $t = sqrt(log(1 / pow(1 - $alpha,2)));
  1965. $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
  1966. }
  1967. $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
  1968. if ($Guess < 0) {
  1969. $Guess = 0;
  1970. } elseif ($Guess > $trials) {
  1971. $Guess = $trials;
  1972. }
  1973. $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
  1974. $EssentiallyZero = 10e-12;
  1975. $m = floor($trials * $probability);
  1976. ++$TotalUnscaledProbability;
  1977. if ($m == $Guess) { ++$UnscaledPGuess; }
  1978. if ($m <= $Guess) { ++$UnscaledCumPGuess; }
  1979. $PreviousValue = 1;
  1980. $Done = False;
  1981. $k = $m + 1;
  1982. while ((!$Done) && ($k <= $trials)) {
  1983. $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
  1984. $TotalUnscaledProbability += $CurrentValue;
  1985. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1986. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1987. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
  1988. $PreviousValue = $CurrentValue;
  1989. ++$k;
  1990. }
  1991. $PreviousValue = 1;
  1992. $Done = False;
  1993. $k = $m - 1;
  1994. while ((!$Done) && ($k >= 0)) {
  1995. $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
  1996. $TotalUnscaledProbability += $CurrentValue;
  1997. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1998. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1999. if (CurrentValue <= EssentiallyZero) { $Done = True; }
  2000. $PreviousValue = $CurrentValue;
  2001. --$k;
  2002. }
  2003. $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
  2004. $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
  2005. // $CumPGuessMinus1 = $CumPGuess - $PGuess;
  2006. $CumPGuessMinus1 = $CumPGuess - 1;
  2007. while (True) {
  2008. if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
  2009. return $Guess;
  2010. } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
  2011. $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
  2012. $CumPGuessMinus1 = $CumPGuess;
  2013. $CumPGuess = $CumPGuess + $PGuessPlus1;
  2014. $PGuess = $PGuessPlus1;
  2015. ++$Guess;
  2016. } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
  2017. $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
  2018. $CumPGuess = $CumPGuessMinus1;
  2019. $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
  2020. $PGuess = $PGuessMinus1;
  2021. --$Guess;
  2022. }
  2023. }
  2024. }
  2025. return self::$_errorCodes['value'];
  2026. }
  2027. /**
  2028. * CHIDIST
  2029. *
  2030. * Returns the one-tailed probability of the chi-squared distribution.
  2031. *
  2032. * @param float $value Value for the function
  2033. * @param float $degrees degrees of freedom
  2034. * @return float
  2035. */
  2036. public static function CHIDIST($value, $degrees) {
  2037. $value = self::flattenSingleValue($value);
  2038. $degrees = floor(self::flattenSingleValue($degrees));
  2039. if ((is_numeric($value)) && (is_numeric($degrees))) {
  2040. if ($degrees < 1) {
  2041. return self::$_errorCodes['num'];
  2042. }
  2043. if ($value < 0) {
  2044. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  2045. return 1;
  2046. }
  2047. return self::$_errorCodes['num'];
  2048. }
  2049. return 1 - (self::incompleteGamma($degrees/2,$value/2) / self::gamma($degrees/2));
  2050. }
  2051. return self::$_errorCodes['value'];
  2052. }
  2053. /**
  2054. * CHIINV
  2055. *
  2056. * Returns the one-tailed probability of the chi-squared distribution.
  2057. *
  2058. * @param float $probability Probability for the function
  2059. * @param float $degrees degrees of freedom
  2060. * @return float
  2061. */
  2062. public static function CHIINV($probability, $degrees) {
  2063. $probability = self::flattenSingleValue($probability);
  2064. $degrees = floor(self::flattenSingleValue($degrees));
  2065. if ((is_numeric($probability)) && (is_numeric($degrees))) {
  2066. $xLo = 100;
  2067. $xHi = 0;
  2068. $maxIteration = 100;
  2069. $x = $xNew = 1;
  2070. $dx = 1;
  2071. $i = 0;
  2072. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  2073. // Apply Newton-Raphson step
  2074. $result = self::CHIDIST($x, $degrees);
  2075. $error = $result - $probability;
  2076. if ($error == 0.0) {
  2077. $dx = 0;
  2078. } elseif ($error < 0.0) {
  2079. $xLo = $x;
  2080. } else {
  2081. $xHi = $x;
  2082. }
  2083. // Avoid division by zero
  2084. if ($result != 0.0) {
  2085. $dx = $error / $result;
  2086. $xNew = $x - $dx;
  2087. }
  2088. // If the NR fails to converge (which for example may be the
  2089. // case if the initial guess is too rough) we apply a bisection
  2090. // step to determine a more narrow interval around the root.
  2091. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  2092. $xNew = ($xLo + $xHi) / 2;
  2093. $dx = $xNew - $x;
  2094. }
  2095. $x = $xNew;
  2096. }
  2097. if ($i == MAX_ITERATIONS) {
  2098. return self::$_errorCodes['na'];
  2099. }
  2100. return round($x,12);
  2101. }
  2102. return self::$_errorCodes['value'];
  2103. }
  2104. /**
  2105. * EXPONDIST
  2106. *
  2107. * Returns the exponential distribution. Use EXPONDIST to model the time between events,
  2108. * such as how long an automated bank teller takes to deliver cash. For example, you can
  2109. * use EXPONDIST to determine the probability that the process takes at most 1 minute.
  2110. *
  2111. * @param float $value Value of the function
  2112. * @param float $lambda The parameter value
  2113. * @param boolean $cumulative
  2114. * @return float
  2115. */
  2116. public static function EXPONDIST($value, $lambda, $cumulative) {
  2117. $value = self::flattenSingleValue($value);
  2118. $lambda = self::flattenSingleValue($lambda);
  2119. $cumulative = self::flattenSingleValue($cumulative);
  2120. if ((is_numeric($value)) && (is_numeric($lambda))) {
  2121. if (($value < 0) || ($lambda < 0)) {
  2122. return self::$_errorCodes['num'];
  2123. }
  2124. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  2125. if ($cumulative) {
  2126. return 1 - exp(0-$value*$lambda);
  2127. } else {
  2128. return $lambda * exp(0-$value*$lambda);
  2129. }
  2130. }
  2131. }
  2132. return self::$_errorCodes['value'];
  2133. }
  2134. /**
  2135. * FISHER
  2136. *
  2137. * Returns the Fisher transformation at x. This transformation produces a function that
  2138. * is normally distributed rather than skewed. Use this function to perform hypothesis
  2139. * testing on the correlation coefficient.
  2140. *
  2141. * @param float $value
  2142. * @return float
  2143. */
  2144. public static function FISHER($value) {
  2145. $value = self::flattenSingleValue($value);
  2146. if (is_numeric($value)) {
  2147. if (($value <= -1) || ($lambda >= 1)) {
  2148. return self::$_errorCodes['num'];
  2149. }
  2150. return 0.5 * log((1+$value)/(1-$value));
  2151. }
  2152. return self::$_errorCodes['value'];
  2153. }
  2154. /**
  2155. * FISHERINV
  2156. *
  2157. * Returns the inverse of the Fisher transformation. Use this transformation when
  2158. * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
  2159. * FISHERINV(y) = x.
  2160. *
  2161. * @param float $value
  2162. * @return float
  2163. */
  2164. public static function FISHERINV($value) {
  2165. $value = self::flattenSingleValue($value);
  2166. if (is_numeric($value)) {
  2167. return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
  2168. }
  2169. return self::$_errorCodes['value'];
  2170. }
  2171. // Function cache for logBeta
  2172. private static $logBetaCache_p = 0.0;
  2173. private static $logBetaCache_q = 0.0;
  2174. private static $logBetaCache_result = 0.0;
  2175. /**
  2176. * The natural logarithm of the beta function.
  2177. * @param p require p>0
  2178. * @param q require q>0
  2179. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  2180. * @author Jaco van Kooten
  2181. */
  2182. private static function logBeta($p, $q) {
  2183. if ($p != self::$logBetaCache_p || $q != self::$logBetaCache_q) {
  2184. self::$logBetaCache_p = $p;
  2185. self::$logBetaCache_q = $q;
  2186. if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  2187. self::$logBetaCache_result = 0.0;
  2188. } else {
  2189. self::$logBetaCache_result = self::logGamma($p) + self::logGamma($q) - self::logGamma($p + $q);
  2190. }
  2191. }
  2192. return self::$logBetaCache_result;
  2193. }
  2194. /**
  2195. * Evaluates of continued fraction part of incomplete beta function.
  2196. * Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
  2197. * @author Jaco van Kooten
  2198. */
  2199. private static function betaFraction($x, $p, $q) {
  2200. $c = 1.0;
  2201. $sum_pq = $p + $q;
  2202. $p_plus = $p + 1.0;
  2203. $p_minus = $p - 1.0;
  2204. $h = 1.0 - $sum_pq * $x / $p_plus;
  2205. if (abs($h) < XMININ) {
  2206. $h = XMININ;
  2207. }
  2208. $h = 1.0 / $h;
  2209. $frac = $h;
  2210. $m = 1;
  2211. $delta = 0.0;
  2212. while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
  2213. $m2 = 2 * $m;
  2214. // even index for d
  2215. $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
  2216. $h = 1.0 + $d * $h;
  2217. if (abs($h) < XMININ) {
  2218. $h = XMININ;
  2219. }
  2220. $h = 1.0 / $h;
  2221. $c = 1.0 + $d / $c;
  2222. if (abs($c) < XMININ) {
  2223. $c = XMININ;
  2224. }
  2225. $frac *= $h * $c;
  2226. // odd index for d
  2227. $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
  2228. $h = 1.0 + $d * $h;
  2229. if (abs($h) < XMININ) {
  2230. $h = XMININ;
  2231. }
  2232. $h = 1.0 / $h;
  2233. $c = 1.0 + $d / $c;
  2234. if (abs($c) < XMININ) {
  2235. $c = XMININ;
  2236. }
  2237. $delta = $h * $c;
  2238. $frac *= $delta;
  2239. ++$m;
  2240. }
  2241. return $frac;
  2242. }
  2243. /**
  2244. * logGamma function
  2245. *
  2246. * @version 1.1
  2247. * @author Jaco van Kooten
  2248. *
  2249. * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
  2250. *
  2251. * The natural logarithm of the gamma function. <br />
  2252. * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
  2253. * Applied Mathematics Division <br />
  2254. * Argonne National Laboratory <br />
  2255. * Argonne, IL 60439 <br />
  2256. * <p>
  2257. * References:
  2258. * <ol>
  2259. * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
  2260. * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
  2261. * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
  2262. * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
  2263. * </ol>
  2264. * </p>
  2265. * <p>
  2266. * From the original documentation:
  2267. * </p>
  2268. * <p>
  2269. * This routine calculates the LOG(GAMMA) function for a positive real argument X.
  2270. * Computation is based on an algorithm outlined in references 1 and 2.
  2271. * The program uses rational functions that theoretically approximate LOG(GAMMA)
  2272. * to at least 18 significant decimal digits. The approximation for X > 12 is from
  2273. * reference 3, while approximations for X < 12.0 are similar to those in reference
  2274. * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
  2275. * the compiler, the intrinsic functions, and proper selection of the
  2276. * machine-dependent constants.
  2277. * </p>
  2278. * <p>
  2279. * Error returns: <br />
  2280. * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
  2281. * The computation is believed to be free of underflow and overflow.
  2282. * </p>
  2283. * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
  2284. */
  2285. // Function cache for logGamma
  2286. private static $logGammaCache_result = 0.0;
  2287. private static $logGammaCache_x = 0.0;
  2288. private static function logGamma($x) {
  2289. // Log Gamma related constants
  2290. static $lg_d1 = -0.5772156649015328605195174;
  2291. static $lg_d2 = 0.4227843350984671393993777;
  2292. static $lg_d4 = 1.791759469228055000094023;
  2293. static $lg_p1 = array( 4.945235359296727046734888,
  2294. 201.8112620856775083915565,
  2295. 2290.838373831346393026739,
  2296. 11319.67205903380828685045,
  2297. 28557.24635671635335736389,
  2298. 38484.96228443793359990269,
  2299. 26377.48787624195437963534,
  2300. 7225.813979700288197698961 );
  2301. static $lg_p2 = array( 4.974607845568932035012064,
  2302. 542.4138599891070494101986,
  2303. 15506.93864978364947665077,
  2304. 184793.2904445632425417223,
  2305. 1088204.76946882876749847,
  2306. 3338152.967987029735917223,
  2307. 5106661.678927352456275255,
  2308. 3074109.054850539556250927 );
  2309. static $lg_p4 = array( 14745.02166059939948905062,
  2310. 2426813.369486704502836312,
  2311. 121475557.4045093227939592,
  2312. 2663432449.630976949898078,
  2313. 29403789566.34553899906876,
  2314. 170266573776.5398868392998,
  2315. 492612579337.743088758812,
  2316. 560625185622.3951465078242 );
  2317. static $lg_q1 = array( 67.48212550303777196073036,
  2318. 1113.332393857199323513008,
  2319. 7738.757056935398733233834,
  2320. 27639.87074403340708898585,
  2321. 54993.10206226157329794414,
  2322. 61611.22180066002127833352,
  2323. 36351.27591501940507276287,
  2324. 8785.536302431013170870835 );
  2325. static $lg_q2 = array( 183.0328399370592604055942,
  2326. 7765.049321445005871323047,
  2327. 133190.3827966074194402448,
  2328. 1136705.821321969608938755,
  2329. 5267964.117437946917577538,
  2330. 13467014.54311101692290052,
  2331. 17827365.30353274213975932,
  2332. 9533095.591844353613395747 );
  2333. static $lg_q4 = array( 2690.530175870899333379843,
  2334. 639388.5654300092398984238,
  2335. 41355999.30241388052042842,
  2336. 1120872109.61614794137657,
  2337. 14886137286.78813811542398,
  2338. 101680358627.2438228077304,
  2339. 341747634550.7377132798597,
  2340. 446315818741.9713286462081 );
  2341. static $lg_c = array( -0.001910444077728,
  2342. 8.4171387781295e-4,
  2343. -5.952379913043012e-4,
  2344. 7.93650793500350248e-4,
  2345. -0.002777777777777681622553,
  2346. 0.08333333333333333331554247,
  2347. 0.0057083835261 );
  2348. // Rough estimate of the fourth root of logGamma_xBig
  2349. static $lg_frtbig = 2.25e76;
  2350. static $pnt68 = 0.6796875;
  2351. if ($x == self::$logGammaCache_x) {
  2352. return self::$logGammaCache_result;
  2353. }
  2354. $y = $x;
  2355. if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
  2356. if ($y <= EPS) {
  2357. $res = -log(y);
  2358. } elseif ($y <= 1.5) {
  2359. // ---------------------
  2360. // EPS .LT. X .LE. 1.5
  2361. // ---------------------
  2362. if ($y < $pnt68) {
  2363. $corr = -log($y);
  2364. $xm1 = $y;
  2365. } else {
  2366. $corr = 0.0;
  2367. $xm1 = $y - 1.0;
  2368. }
  2369. if ($y <= 0.5 || $y >= $pnt68) {
  2370. $xden = 1.0;
  2371. $xnum = 0.0;
  2372. for ($i = 0; $i < 8; ++$i) {
  2373. $xnum = $xnum * $xm1 + $lg_p1[$i];
  2374. $xden = $xden * $xm1 + $lg_q1[$i];
  2375. }
  2376. $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
  2377. } else {
  2378. $xm2 = $y - 1.0;
  2379. $xden = 1.0;
  2380. $xnum = 0.0;
  2381. for ($i = 0; $i < 8; ++$i) {
  2382. $xnum = $xnum * $xm2 + $lg_p2[$i];
  2383. $xden = $xden * $xm2 + $lg_q2[$i];
  2384. }
  2385. $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  2386. }
  2387. } elseif ($y <= 4.0) {
  2388. // ---------------------
  2389. // 1.5 .LT. X .LE. 4.0
  2390. // ---------------------
  2391. $xm2 = $y - 2.0;
  2392. $xden = 1.0;
  2393. $xnum = 0.0;
  2394. for ($i = 0; $i < 8; ++$i) {
  2395. $xnum = $xnum * $xm2 + $lg_p2[$i];
  2396. $xden = $xden * $xm2 + $lg_q2[$i];
  2397. }
  2398. $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  2399. } elseif ($y <= 12.0) {
  2400. // ----------------------
  2401. // 4.0 .LT. X .LE. 12.0
  2402. // ----------------------
  2403. $xm4 = $y - 4.0;
  2404. $xden = -1.0;
  2405. $xnum = 0.0;
  2406. for ($i = 0; $i < 8; ++$i) {
  2407. $xnum = $xnum * $xm4 + $lg_p4[$i];
  2408. $xden = $xden * $xm4 + $lg_q4[$i];
  2409. }
  2410. $res = $lg_d4 + $xm4 * ($xnum / $xden);
  2411. } else {
  2412. // ---------------------------------
  2413. // Evaluate for argument .GE. 12.0
  2414. // ---------------------------------
  2415. $res = 0.0;
  2416. if ($y <= $lg_frtbig) {
  2417. $res = $lg_c[6];
  2418. $ysq = $y * $y;
  2419. for ($i = 0; $i < 6; ++$i)
  2420. $res = $res / $ysq + $lg_c[$i];
  2421. }
  2422. $res /= $y;
  2423. $corr = log($y);
  2424. $res = $res + log(SQRT2PI) - 0.5 * $corr;
  2425. $res += $y * ($corr - 1.0);
  2426. }
  2427. } else {
  2428. // --------------------------
  2429. // Return for bad arguments
  2430. // --------------------------
  2431. $res = MAX_VALUE;
  2432. }
  2433. // ------------------------------
  2434. // Final adjustments and return
  2435. // ------------------------------
  2436. self::$logGammaCache_x = $x;
  2437. self::$logGammaCache_result = $res;
  2438. return $res;
  2439. }
  2440. /**
  2441. * Beta function.
  2442. *
  2443. * @author Jaco van Kooten
  2444. *
  2445. * @param p require p>0
  2446. * @param q require q>0
  2447. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  2448. */
  2449. private static function beta($p, $q) {
  2450. if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) {
  2451. return 0.0;
  2452. } else {
  2453. return exp(self::logBeta($p, $q));
  2454. }
  2455. }
  2456. /**
  2457. * Incomplete beta function
  2458. *
  2459. * @author Jaco van Kooten
  2460. * @author Paul Meagher
  2461. *
  2462. * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).
  2463. * @param x require 0<=x<=1
  2464. * @param p require p>0
  2465. * @param q require q>0
  2466. * @return 0 if x<0, p<=0, q<=0 or p+q>2.55E305 and 1 if x>1 to avoid errors and over/underflow
  2467. */
  2468. private static function incompleteBeta($x, $p, $q) {
  2469. if ($x <= 0.0) {
  2470. return 0.0;
  2471. } elseif ($x >= 1.0) {
  2472. return 1.0;
  2473. } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  2474. return 0.0;
  2475. }
  2476. $beta_gam = exp((0 - self::logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
  2477. if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
  2478. return $beta_gam * self::betaFraction($x, $p, $q) / $p;
  2479. } else {
  2480. return 1.0 - ($beta_gam * self::betaFraction(1 - $x, $q, $p) / $q);
  2481. }
  2482. }
  2483. /**
  2484. * BETADIST
  2485. *
  2486. * Returns the beta distribution.
  2487. *
  2488. * @param float $value Value at which you want to evaluate the distribution
  2489. * @param float $alpha Parameter to the distribution
  2490. * @param float $beta Parameter to the distribution
  2491. * @param boolean $cumulative
  2492. * @return float
  2493. *
  2494. */
  2495. public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
  2496. $value = self::flattenSingleValue($value);
  2497. $alpha = self::flattenSingleValue($alpha);
  2498. $beta = self::flattenSingleValue($beta);
  2499. $rMin = self::flattenSingleValue($rMin);
  2500. $rMax = self::flattenSingleValue($rMax);
  2501. if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  2502. if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
  2503. return self::$_errorCodes['num'];
  2504. }
  2505. if ($rMin > $rMax) {
  2506. $tmp = $rMin;
  2507. $rMin = $rMax;
  2508. $rMax = $tmp;
  2509. }
  2510. $value -= $rMin;
  2511. $value /= ($rMax - $rMin);
  2512. return self::incompleteBeta($value,$alpha,$beta);
  2513. }
  2514. return self::$_errorCodes['value'];
  2515. }
  2516. /**
  2517. * BETAINV
  2518. *
  2519. * Returns the inverse of the beta distribution.
  2520. *
  2521. * @param float $probability Probability at which you want to evaluate the distribution
  2522. * @param float $alpha Parameter to the distribution
  2523. * @param float $beta Parameter to the distribution
  2524. * @param boolean $cumulative
  2525. * @return float
  2526. *
  2527. */
  2528. public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
  2529. $probability = self::flattenSingleValue($probability);
  2530. $alpha = self::flattenSingleValue($alpha);
  2531. $beta = self::flattenSingleValue($beta);
  2532. $rMin = self::flattenSingleValue($rMin);
  2533. $rMax = self::flattenSingleValue($rMax);
  2534. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  2535. if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
  2536. return self::$_errorCodes['num'];
  2537. }
  2538. if ($rMin > $rMax) {
  2539. $tmp = $rMin;
  2540. $rMin = $rMax;
  2541. $rMax = $tmp;
  2542. }
  2543. $a = 0;
  2544. $b = 2;
  2545. $maxIteration = 100;
  2546. $i = 0;
  2547. while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  2548. $guess = ($a + $b) / 2;
  2549. $result = self::BETADIST($guess, $alpha, $beta);
  2550. if (($result == $probability) || ($result == 0)) {
  2551. $b = $a;
  2552. } elseif ($result > $probability) {
  2553. $b = $guess;
  2554. } else {
  2555. $a = $guess;
  2556. }
  2557. }
  2558. if ($i == MAX_ITERATIONS) {
  2559. return self::$_errorCodes['na'];
  2560. }
  2561. return round($rMin + $guess * ($rMax - $rMin),12);
  2562. }
  2563. return self::$_errorCodes['value'];
  2564. }
  2565. //
  2566. // Private implementation of the incomplete Gamma function
  2567. //
  2568. private static function incompleteGamma($a,$x) {
  2569. static $max = 32;
  2570. $summer = 0;
  2571. for ($n=0; $n<=$max; ++$n) {
  2572. $divisor = $a;
  2573. for ($i=1; $i<=$n; ++$i) {
  2574. $divisor *= ($a + $i);
  2575. }
  2576. $summer += (pow($x,$n) / $divisor);
  2577. }
  2578. return pow($x,$a) * exp(0-$x) * $summer;
  2579. }
  2580. //
  2581. // Private implementation of the Gamma function
  2582. //
  2583. private static function gamma($data) {
  2584. if ($data == 0.0) return 0;
  2585. static $p0 = 1.000000000190015;
  2586. static $p = array ( 1 => 76.18009172947146,
  2587. 2 => -86.50532032941677,
  2588. 3 => 24.01409824083091,
  2589. 4 => -1.231739572450155,
  2590. 5 => 1.208650973866179e-3,
  2591. 6 => -5.395239384953e-6
  2592. );
  2593. $y = $x = $data;
  2594. $tmp = $x + 5.5;
  2595. $tmp -= ($x + 0.5) * log($tmp);
  2596. $summer = $p0;
  2597. for ($j=1;$j<=6;++$j) {
  2598. $summer += ($p[$j] / ++$y);
  2599. }
  2600. return exp(0 - $tmp + log(2.5066282746310005 * $summer / $x));
  2601. }
  2602. /**
  2603. * GAMMADIST
  2604. *
  2605. * Returns the gamma distribution.
  2606. *
  2607. * @param float $value Value at which you want to evaluate the distribution
  2608. * @param float $a Parameter to the distribution
  2609. * @param float $b Parameter to the distribution
  2610. * @param boolean $cumulative
  2611. * @return float
  2612. *
  2613. */
  2614. public static function GAMMADIST($value,$a,$b,$cumulative) {
  2615. $value = self::flattenSingleValue($value);
  2616. $a = self::flattenSingleValue($a);
  2617. $b = self::flattenSingleValue($b);
  2618. if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
  2619. if (($value < 0) || ($a <= 0) || ($b <= 0)) {
  2620. return self::$_errorCodes['num'];
  2621. }
  2622. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  2623. if ($cumulative) {
  2624. return self::incompleteGamma($a,$value / $b) / self::gamma($a);
  2625. } else {
  2626. return (1 / (pow($b,$a) * self::gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
  2627. }
  2628. }
  2629. }
  2630. return self::$_errorCodes['value'];
  2631. }
  2632. /**
  2633. * GAMMAINV
  2634. *
  2635. * Returns the inverse of the beta distribution.
  2636. *
  2637. * @param float $probability Probability at which you want to evaluate the distribution
  2638. * @param float $alpha Parameter to the distribution
  2639. * @param float $beta Parameter to the distribution
  2640. * @param boolean $cumulative
  2641. * @return float
  2642. *
  2643. */
  2644. public static function GAMMAINV($probability,$alpha,$beta) {
  2645. $probability = self::flattenSingleValue($probability);
  2646. $alpha = self::flattenSingleValue($alpha);
  2647. $beta = self::flattenSingleValue($beta);
  2648. $rMin = self::flattenSingleValue($rMin);
  2649. $rMax = self::flattenSingleValue($rMax);
  2650. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
  2651. if (($alpha <= 0) || ($beta <= 0) || ($probability <= 0) || ($probability > 1)) {
  2652. return self::$_errorCodes['num'];
  2653. }
  2654. $xLo = 0;
  2655. $xHi = 100;
  2656. $maxIteration = 100;
  2657. $x = $xNew = 1;
  2658. $dx = 1;
  2659. $i = 0;
  2660. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  2661. // Apply Newton-Raphson step
  2662. $result = self::GAMMADIST($x, $alpha, $beta, True);
  2663. $error = $result - $probability;
  2664. if ($error == 0.0) {
  2665. $dx = 0;
  2666. } elseif ($error < 0.0) {
  2667. $xLo = $x;
  2668. } else {
  2669. $xHi = $x;
  2670. }
  2671. // Avoid division by zero
  2672. if ($result != 0.0) {
  2673. $dx = $error / $result;
  2674. $xNew = $x - $dx;
  2675. }
  2676. // If the NR fails to converge (which for example may be the
  2677. // case if the initial guess is too rough) we apply a bisection
  2678. // step to determine a more narrow interval around the root.
  2679. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  2680. $xNew = ($xLo + $xHi) / 2;
  2681. $dx = $xNew - $x;
  2682. }
  2683. $x = $xNew;
  2684. }
  2685. if ($i == MAX_ITERATIONS) {
  2686. return self::$_errorCodes['na'];
  2687. }
  2688. return round($x,12);
  2689. }
  2690. return self::$_errorCodes['value'];
  2691. }
  2692. /**
  2693. * GAMMALN
  2694. *
  2695. * Returns the natural logarithm of the gamma function.
  2696. *
  2697. * @param float $value
  2698. * @return float
  2699. */
  2700. public static function GAMMALN($value) {
  2701. $value = self::flattenSingleValue($value);
  2702. if (is_numeric($value)) {
  2703. if ($value <= 0) {
  2704. return self::$_errorCodes['num'];
  2705. }
  2706. return log(self::gamma($value));
  2707. }
  2708. return self::$_errorCodes['value'];
  2709. }
  2710. /**
  2711. * NORMDIST
  2712. *
  2713. * Returns the normal distribution for the specified mean and standard deviation. This
  2714. * function has a very wide range of applications in statistics, including hypothesis
  2715. * testing.
  2716. *
  2717. * @param float $value
  2718. * @param float $mean Mean Value
  2719. * @param float $stdDev Standard Deviation
  2720. * @param boolean $cumulative
  2721. * @return float
  2722. *
  2723. */
  2724. public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
  2725. $value = self::flattenSingleValue($value);
  2726. $mean = self::flattenSingleValue($mean);
  2727. $stdDev = self::flattenSingleValue($stdDev);
  2728. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2729. if ($stdDev < 0) {
  2730. return self::$_errorCodes['num'];
  2731. }
  2732. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  2733. if ($cumulative) {
  2734. return 0.5 * (1 + self::erfVal(($value - $mean) / ($stdDev * sqrt(2))));
  2735. } else {
  2736. return (1 / (SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean,2) / (2 * pow($stdDev,2))));
  2737. }
  2738. }
  2739. }
  2740. return self::$_errorCodes['value'];
  2741. }
  2742. /**
  2743. * NORMSDIST
  2744. *
  2745. * Returns the standard normal cumulative distribution function. The distribution has
  2746. * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a
  2747. * table of standard normal curve areas.
  2748. *
  2749. * @param float $value
  2750. * @return float
  2751. */
  2752. public static function NORMSDIST($value) {
  2753. $value = self::flattenSingleValue($value);
  2754. return self::NORMDIST($value, 0, 1, True);
  2755. }
  2756. /**
  2757. * LOGNORMDIST
  2758. *
  2759. * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed
  2760. * with parameters mean and standard_dev.
  2761. *
  2762. * @param float $value
  2763. * @return float
  2764. */
  2765. public static function LOGNORMDIST($value, $mean, $stdDev) {
  2766. $value = self::flattenSingleValue($value);
  2767. $mean = self::flattenSingleValue($mean);
  2768. $stdDev = self::flattenSingleValue($stdDev);
  2769. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2770. if (($value <= 0) || ($stdDev <= 0)) {
  2771. return self::$_errorCodes['num'];
  2772. }
  2773. return self::NORMSDIST((log($value) - $mean) / $stdDev);
  2774. }
  2775. return self::$_errorCodes['value'];
  2776. }
  2777. /***************************************************************************
  2778. * inverse_ncdf.php
  2779. * -------------------
  2780. * begin : Friday, January 16, 2004
  2781. * copyright : (C) 2004 Michael Nickerson
  2782. * email : nickersonm@yahoo.com
  2783. *
  2784. ***************************************************************************/
  2785. private static function inverse_ncdf($p) {
  2786. // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
  2787. // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
  2788. // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
  2789. // I have not checked the accuracy of this implementation. Be aware that PHP
  2790. // will truncate the coeficcients to 14 digits.
  2791. // You have permission to use and distribute this function freely for
  2792. // whatever purpose you want, but please show common courtesy and give credit
  2793. // where credit is due.
  2794. // Input paramater is $p - probability - where 0 < p < 1.
  2795. // Coefficients in rational approximations
  2796. static $a = array( 1 => -3.969683028665376e+01,
  2797. 2 => 2.209460984245205e+02,
  2798. 3 => -2.759285104469687e+02,
  2799. 4 => 1.383577518672690e+02,
  2800. 5 => -3.066479806614716e+01,
  2801. 6 => 2.506628277459239e+00
  2802. );
  2803. static $b = array( 1 => -5.447609879822406e+01,
  2804. 2 => 1.615858368580409e+02,
  2805. 3 => -1.556989798598866e+02,
  2806. 4 => 6.680131188771972e+01,
  2807. 5 => -1.328068155288572e+01
  2808. );
  2809. static $c = array( 1 => -7.784894002430293e-03,
  2810. 2 => -3.223964580411365e-01,
  2811. 3 => -2.400758277161838e+00,
  2812. 4 => -2.549732539343734e+00,
  2813. 5 => 4.374664141464968e+00,
  2814. 6 => 2.938163982698783e+00
  2815. );
  2816. static $d = array( 1 => 7.784695709041462e-03,
  2817. 2 => 3.224671290700398e-01,
  2818. 3 => 2.445134137142996e+00,
  2819. 4 => 3.754408661907416e+00
  2820. );
  2821. // Define lower and upper region break-points.
  2822. $p_low = 0.02425; //Use lower region approx. below this
  2823. $p_high = 1 - $p_low; //Use upper region approx. above this
  2824. if (0 < $p && $p < $p_low) {
  2825. // Rational approximation for lower region.
  2826. $q = sqrt(-2 * log($p));
  2827. return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  2828. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  2829. } elseif ($p_low <= $p && $p <= $p_high) {
  2830. // Rational approximation for central region.
  2831. $q = $p - 0.5;
  2832. $r = $q * $q;
  2833. return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
  2834. ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
  2835. } elseif ($p_high < $p && $p < 1) {
  2836. // Rational approximation for upper region.
  2837. $q = sqrt(-2 * log(1 - $p));
  2838. return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  2839. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  2840. }
  2841. // If 0 < p < 1, return a null value
  2842. return self::$_errorCodes['null'];
  2843. }
  2844. private static function inverse_ncdf2($prob) {
  2845. // Approximation of inverse standard normal CDF developed by
  2846. // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
  2847. $a1 = 2.50662823884;
  2848. $a2 = -18.61500062529;
  2849. $a3 = 41.39119773534;
  2850. $a4 = -25.44106049637;
  2851. $b1 = -8.4735109309;
  2852. $b2 = 23.08336743743;
  2853. $b3 = -21.06224101826;
  2854. $b4 = 3.13082909833;
  2855. $c1 = 0.337475482272615;
  2856. $c2 = 0.976169019091719;
  2857. $c3 = 0.160797971491821;
  2858. $c4 = 2.76438810333863E-02;
  2859. $c5 = 3.8405729373609E-03;
  2860. $c6 = 3.951896511919E-04;
  2861. $c7 = 3.21767881768E-05;
  2862. $c8 = 2.888167364E-07;
  2863. $c9 = 3.960315187E-07;
  2864. $y = $prob - 0.5;
  2865. if (abs($y) < 0.42) {
  2866. $z = pow($y,2);
  2867. $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
  2868. } else {
  2869. if ($y > 0) {
  2870. $z = log(-log(1 - $prob));
  2871. } else {
  2872. $z = log(-log($prob));
  2873. }
  2874. $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
  2875. if ($y < 0) {
  2876. $z = -$z;
  2877. }
  2878. }
  2879. return $z;
  2880. }
  2881. private static function inverse_ncdf3($p) {
  2882. // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
  2883. // Produces the normal deviate Z corresponding to a given lower
  2884. // tail area of P; Z is accurate to about 1 part in 10**16.
  2885. //
  2886. // This is a PHP version of the original FORTRAN code that can
  2887. // be found at http://lib.stat.cmu.edu/apstat/
  2888. $split1 = 0.425;
  2889. $split2 = 5;
  2890. $const1 = 0.180625;
  2891. $const2 = 1.6;
  2892. // coefficients for p close to 0.5
  2893. $a0 = 3.3871328727963666080;
  2894. $a1 = 1.3314166789178437745E+2;
  2895. $a2 = 1.9715909503065514427E+3;
  2896. $a3 = 1.3731693765509461125E+4;
  2897. $a4 = 4.5921953931549871457E+4;
  2898. $a5 = 6.7265770927008700853E+4;
  2899. $a6 = 3.3430575583588128105E+4;
  2900. $a7 = 2.5090809287301226727E+3;
  2901. $b1 = 4.2313330701600911252E+1;
  2902. $b2 = 6.8718700749205790830E+2;
  2903. $b3 = 5.3941960214247511077E+3;
  2904. $b4 = 2.1213794301586595867E+4;
  2905. $b5 = 3.9307895800092710610E+4;
  2906. $b6 = 2.8729085735721942674E+4;
  2907. $b7 = 5.2264952788528545610E+3;
  2908. // coefficients for p not close to 0, 0.5 or 1.
  2909. $c0 = 1.42343711074968357734;
  2910. $c1 = 4.63033784615654529590;
  2911. $c2 = 5.76949722146069140550;
  2912. $c3 = 3.64784832476320460504;
  2913. $c4 = 1.27045825245236838258;
  2914. $c5 = 2.41780725177450611770E-1;
  2915. $c6 = 2.27238449892691845833E-2;
  2916. $c7 = 7.74545014278341407640E-4;
  2917. $d1 = 2.05319162663775882187;
  2918. $d2 = 1.67638483018380384940;
  2919. $d3 = 6.89767334985100004550E-1;
  2920. $d4 = 1.48103976427480074590E-1;
  2921. $d5 = 1.51986665636164571966E-2;
  2922. $d6 = 5.47593808499534494600E-4;
  2923. $d7 = 1.05075007164441684324E-9;
  2924. // coefficients for p near 0 or 1.
  2925. $e0 = 6.65790464350110377720;
  2926. $e1 = 5.46378491116411436990;
  2927. $e2 = 1.78482653991729133580;
  2928. $e3 = 2.96560571828504891230E-1;
  2929. $e4 = 2.65321895265761230930E-2;
  2930. $e5 = 1.24266094738807843860E-3;
  2931. $e6 = 2.71155556874348757815E-5;
  2932. $e7 = 2.01033439929228813265E-7;
  2933. $f1 = 5.99832206555887937690E-1;
  2934. $f2 = 1.36929880922735805310E-1;
  2935. $f3 = 1.48753612908506148525E-2;
  2936. $f4 = 7.86869131145613259100E-4;
  2937. $f5 = 1.84631831751005468180E-5;
  2938. $f6 = 1.42151175831644588870E-7;
  2939. $f7 = 2.04426310338993978564E-15;
  2940. $q = $p - 0.5;
  2941. // computation for p close to 0.5
  2942. if (abs($q) <= split1) {
  2943. $R = $const1 - $q * $q;
  2944. $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
  2945. ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
  2946. } else {
  2947. if ($q < 0) {
  2948. $R = $p;
  2949. } else {
  2950. $R = 1 - $p;
  2951. }
  2952. $R = pow(-log($R),2);
  2953. // computation for p not close to 0, 0.5 or 1.
  2954. If ($R <= $split2) {
  2955. $R = $R - $const2;
  2956. $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
  2957. ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
  2958. } else {
  2959. // computation for p near 0 or 1.
  2960. $R = $R - $split2;
  2961. $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
  2962. ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
  2963. }
  2964. if ($q < 0) {
  2965. $z = -$z;
  2966. }
  2967. }
  2968. return $z;
  2969. }
  2970. /**
  2971. * NORMINV
  2972. *
  2973. * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
  2974. *
  2975. * @param float $value
  2976. * @param float $mean Mean Value
  2977. * @param float $stdDev Standard Deviation
  2978. * @return float
  2979. *
  2980. */
  2981. public static function NORMINV($probability,$mean,$stdDev) {
  2982. $probability = self::flattenSingleValue($probability);
  2983. $mean = self::flattenSingleValue($mean);
  2984. $stdDev = self::flattenSingleValue($stdDev);
  2985. if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2986. if (($probability < 0) || ($probability > 1)) {
  2987. return self::$_errorCodes['num'];
  2988. }
  2989. if ($stdDev < 0) {
  2990. return self::$_errorCodes['num'];
  2991. }
  2992. return (self::inverse_ncdf($probability) * $stdDev) + $mean;
  2993. }
  2994. return self::$_errorCodes['value'];
  2995. }
  2996. /**
  2997. * NORMSINV
  2998. *
  2999. * Returns the inverse of the standard normal cumulative distribution
  3000. *
  3001. * @param float $value
  3002. * @return float
  3003. */
  3004. public static function NORMSINV($value) {
  3005. return self::NORMINV($value, 0, 1);
  3006. }
  3007. /**
  3008. * LOGINV
  3009. *
  3010. * Returns the inverse of the normal cumulative distribution
  3011. *
  3012. * @param float $value
  3013. * @return float
  3014. *
  3015. * @todo Try implementing P J Acklam's refinement algorithm for greater
  3016. * accuracy if I can get my head round the mathematics
  3017. * (as described at) http://home.online.no/~pjacklam/notes/invnorm/
  3018. */
  3019. public static function LOGINV($probability, $mean, $stdDev) {
  3020. $probability = self::flattenSingleValue($probability);
  3021. $mean = self::flattenSingleValue($mean);
  3022. $stdDev = self::flattenSingleValue($stdDev);
  3023. if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  3024. if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
  3025. return self::$_errorCodes['num'];
  3026. }
  3027. return exp($mean + $stdDev * self::NORMSINV($probability));
  3028. }
  3029. return self::$_errorCodes['value'];
  3030. }
  3031. /**
  3032. * HYPGEOMDIST
  3033. *
  3034. * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of
  3035. * sample successes, given the sample size, population successes, and population size.
  3036. *
  3037. * @param float $sampleSuccesses Number of successes in the sample
  3038. * @param float $sampleNumber Size of the sample
  3039. * @param float $populationSuccesses Number of successes in the population
  3040. * @param float $populationNumber Population size
  3041. * @return float
  3042. *
  3043. */
  3044. public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
  3045. $sampleSuccesses = floor(self::flattenSingleValue($sampleSuccesses));
  3046. $sampleNumber = floor(self::flattenSingleValue($sampleNumber));
  3047. $populationSuccesses = floor(self::flattenSingleValue($populationSuccesses));
  3048. $populationNumber = floor(self::flattenSingleValue($populationNumber));
  3049. if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) {
  3050. if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
  3051. return self::$_errorCodes['num'];
  3052. }
  3053. if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
  3054. return self::$_errorCodes['num'];
  3055. }
  3056. if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
  3057. return self::$_errorCodes['num'];
  3058. }
  3059. return self::COMBIN($populationSuccesses,$sampleSuccesses) *
  3060. self::COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
  3061. self::COMBIN($populationNumber,$sampleNumber);
  3062. }
  3063. return self::$_errorCodes['value'];
  3064. }
  3065. public static function hypGeom($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
  3066. return self::COMBIN($populationSuccesses,$sampleSuccesses) *
  3067. self::COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
  3068. self::COMBIN($populationNumber,$sampleNumber);
  3069. }
  3070. /**
  3071. * TDIST
  3072. *
  3073. * Returns the probability of Student's T distribution.
  3074. *
  3075. * @param float $value Value for the function
  3076. * @param float $degrees degrees of freedom
  3077. * @param float $tails number of tails (1 or 2)
  3078. * @return float
  3079. */
  3080. public static function TDIST($value, $degrees, $tails) {
  3081. $value = self::flattenSingleValue($value);
  3082. $degrees = floor(self::flattenSingleValue($degrees));
  3083. $tails = floor(self::flattenSingleValue($tails));
  3084. if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
  3085. if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
  3086. return self::$_errorCodes['num'];
  3087. }
  3088. // tdist, which finds the probability that corresponds to a given value
  3089. // of t with k degrees of freedom. This algorithm is translated from a
  3090. // pascal function on p81 of "Statistical Computing in Pascal" by D
  3091. // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
  3092. // London). The above Pascal algorithm is itself a translation of the
  3093. // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
  3094. // Laboratory as reported in (among other places) "Applied Statistics
  3095. // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
  3096. // Horwood Ltd.; W. Sussex, England).
  3097. // $ta = 2 / pi();
  3098. $ta = 0.636619772367581;
  3099. $tterm = $degrees;
  3100. $ttheta = atan2($value,sqrt($tterm));
  3101. $tc = cos($ttheta);
  3102. $ts = sin($ttheta);
  3103. $tsum = 0;
  3104. if (($degrees % 2) == 1) {
  3105. $ti = 3;
  3106. $tterm = $tc;
  3107. } else {
  3108. $ti = 2;
  3109. $tterm = 1;
  3110. }
  3111. $tsum = $tterm;
  3112. while ($ti < $degrees) {
  3113. $tterm *= $tc * $tc * ($ti - 1) / $ti;
  3114. $tsum += $tterm;
  3115. $ti += 2;
  3116. }
  3117. $tsum *= $ts;
  3118. if (($degrees % 2) == 1) { $tsum = $ta * ($tsum + $ttheta); }
  3119. $tValue = 0.5 * (1 + $tsum);
  3120. if ($tails == 1) {
  3121. return 1 - abs($tValue);
  3122. } else {
  3123. return 1 - abs((1 - $tValue) - $tValue);
  3124. }
  3125. }
  3126. return self::$_errorCodes['value'];
  3127. }
  3128. /**
  3129. * TINV
  3130. *
  3131. * Returns the one-tailed probability of the chi-squared distribution.
  3132. *
  3133. * @param float $probability Probability for the function
  3134. * @param float $degrees degrees of freedom
  3135. * @return float
  3136. */
  3137. public static function TINV($probability, $degrees) {
  3138. $probability = self::flattenSingleValue($probability);
  3139. $degrees = floor(self::flattenSingleValue($degrees));
  3140. if ((is_numeric($probability)) && (is_numeric($degrees))) {
  3141. $xLo = 100;
  3142. $xHi = 0;
  3143. $maxIteration = 100;
  3144. $x = $xNew = 1;
  3145. $dx = 1;
  3146. $i = 0;
  3147. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  3148. // Apply Newton-Raphson step
  3149. $result = self::TDIST($x, $degrees, 2);
  3150. $error = $result - $probability;
  3151. if ($error == 0.0) {
  3152. $dx = 0;
  3153. } elseif ($error < 0.0) {
  3154. $xLo = $x;
  3155. } else {
  3156. $xHi = $x;
  3157. }
  3158. // Avoid division by zero
  3159. if ($result != 0.0) {
  3160. $dx = $error / $result;
  3161. $xNew = $x - $dx;
  3162. }
  3163. // If the NR fails to converge (which for example may be the
  3164. // case if the initial guess is too rough) we apply a bisection
  3165. // step to determine a more narrow interval around the root.
  3166. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  3167. $xNew = ($xLo + $xHi) / 2;
  3168. $dx = $xNew - $x;
  3169. }
  3170. $x = $xNew;
  3171. }
  3172. if ($i == MAX_ITERATIONS) {
  3173. return self::$_errorCodes['na'];
  3174. }
  3175. return round($x,12);
  3176. }
  3177. return self::$_errorCodes['value'];
  3178. }
  3179. /**
  3180. * CONFIDENCE
  3181. *
  3182. * Returns the confidence interval for a population mean
  3183. *
  3184. * @param float $alpha
  3185. * @param float $stdDev Standard Deviation
  3186. * @param float $size
  3187. * @return float
  3188. *
  3189. */
  3190. public static function CONFIDENCE($alpha,$stdDev,$size) {
  3191. $alpha = self::flattenSingleValue($alpha);
  3192. $stdDev = self::flattenSingleValue($stdDev);
  3193. $size = floor(self::flattenSingleValue($size));
  3194. if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
  3195. if (($alpha <= 0) || ($alpha >= 1)) {
  3196. return self::$_errorCodes['num'];
  3197. }
  3198. if (($stdDev <= 0) || ($size < 1)) {
  3199. return self::$_errorCodes['num'];
  3200. }
  3201. return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
  3202. }
  3203. return self::$_errorCodes['value'];
  3204. }
  3205. /**
  3206. * POISSON
  3207. *
  3208. * Returns the Poisson distribution. A common application of the Poisson distribution
  3209. * is predicting the number of events over a specific time, such as the number of
  3210. * cars arriving at a toll plaza in 1 minute.
  3211. *
  3212. * @param float $value
  3213. * @param float $mean Mean Value
  3214. * @param boolean $cumulative
  3215. * @return float
  3216. *
  3217. */
  3218. public static function POISSON($value, $mean, $cumulative) {
  3219. $value = self::flattenSingleValue($value);
  3220. $mean = self::flattenSingleValue($mean);
  3221. if ((is_numeric($value)) && (is_numeric($mean))) {
  3222. if (($value <= 0) || ($mean <= 0)) {
  3223. return self::$_errorCodes['num'];
  3224. }
  3225. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  3226. if ($cumulative) {
  3227. $summer = 0;
  3228. for ($i = 0; $i <= floor($value); ++$i) {
  3229. $summer += pow($mean,$i) / self::FACT($i);
  3230. }
  3231. return exp(0-$mean) * $summer;
  3232. } else {
  3233. return (exp(0-$mean) * pow($mean,$value)) / self::FACT($value);
  3234. }
  3235. }
  3236. }
  3237. return self::$_errorCodes['value'];
  3238. }
  3239. /**
  3240. * WEIBULL
  3241. *
  3242. * Returns the Weibull distribution. Use this distribution in reliability
  3243. * analysis, such as calculating a device's mean time to failure.
  3244. *
  3245. * @param float $value
  3246. * @param float $alpha Alpha Parameter
  3247. * @param float $beta Beta Parameter
  3248. * @param boolean $cumulative
  3249. * @return float
  3250. *
  3251. */
  3252. public static function WEIBULL($value, $alpha, $beta, $cumulative) {
  3253. $value = self::flattenSingleValue($value);
  3254. $alpha = self::flattenSingleValue($alpha);
  3255. $beta = self::flattenSingleValue($beta);
  3256. if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
  3257. if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
  3258. return self::$_errorCodes['num'];
  3259. }
  3260. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  3261. if ($cumulative) {
  3262. return 1 - exp(0 - pow($value / $beta,$alpha));
  3263. } else {
  3264. return ($alpha / pow($beta,$alpha)) * pow($value,$alpha - 1) * exp(0 - pow($value / $beta,$alpha));
  3265. }
  3266. }
  3267. }
  3268. return self::$_errorCodes['value'];
  3269. }
  3270. /**
  3271. * SKEW
  3272. *
  3273. * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry
  3274. * of a distribution around its mean. Positive skewness indicates a distribution with an
  3275. * asymmetric tail extending toward more positive values. Negative skewness indicates a
  3276. * distribution with an asymmetric tail extending toward more negative values.
  3277. *
  3278. * @param array Data Series
  3279. * @return float
  3280. */
  3281. public static function SKEW() {
  3282. $aArgs = self::flattenArray(func_get_args());
  3283. $mean = self::AVERAGE($aArgs);
  3284. $stdDev = self::STDEV($aArgs);
  3285. $count = $summer = 0;
  3286. // Loop through arguments
  3287. foreach ($aArgs as $arg) {
  3288. // Is it a numeric value?
  3289. if ((is_numeric($arg)) && (!is_string($arg))) {
  3290. $summer += pow((($arg - $mean) / $stdDev),3) ;
  3291. ++$count;
  3292. }
  3293. }
  3294. // Return
  3295. if ($count > 2) {
  3296. return $summer * ($count / (($count-1) * ($count-2)));
  3297. }
  3298. return self::$_errorCodes['divisionbyzero'];
  3299. }
  3300. /**
  3301. * KURT
  3302. *
  3303. * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness
  3304. * or flatness of a distribution compared with the normal distribution. Positive
  3305. * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a
  3306. * relatively flat distribution.
  3307. *
  3308. * @param array Data Series
  3309. * @return float
  3310. */
  3311. public static function KURT() {
  3312. $aArgs = self::flattenArray(func_get_args());
  3313. $mean = self::AVERAGE($aArgs);
  3314. $stdDev = self::STDEV($aArgs);
  3315. if ($stdDev > 0) {
  3316. $count = $summer = 0;
  3317. // Loop through arguments
  3318. foreach ($aArgs as $arg) {
  3319. // Is it a numeric value?
  3320. if ((is_numeric($arg)) && (!is_string($arg))) {
  3321. $summer += pow((($arg - $mean) / $stdDev),4) ;
  3322. ++$count;
  3323. }
  3324. }
  3325. // Return
  3326. if ($count > 3) {
  3327. return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1,2) / (($count-2) * ($count-3)));
  3328. }
  3329. }
  3330. return self::$_errorCodes['divisionbyzero'];
  3331. }
  3332. /**
  3333. * RAND
  3334. *
  3335. * @param int $min Minimal value
  3336. * @param int $max Maximal value
  3337. * @return int Random number
  3338. */
  3339. public static function RAND($min = 0, $max = 0) {
  3340. $min = self::flattenSingleValue($min);
  3341. $max = self::flattenSingleValue($max);
  3342. if ($min == 0 && $max == 0) {
  3343. return (rand(0,10000000)) / 10000000;
  3344. } else {
  3345. return rand($min, $max);
  3346. }
  3347. }
  3348. /**
  3349. * MOD
  3350. *
  3351. * @param int $a Dividend
  3352. * @param int $b Divisor
  3353. * @return int Remainder
  3354. */
  3355. public static function MOD($a = 1, $b = 1) {
  3356. $a = self::flattenSingleValue($a);
  3357. $b = self::flattenSingleValue($b);
  3358. return $a % $b;
  3359. }
  3360. /**
  3361. * ASCIICODE
  3362. *
  3363. * @param string $character Value
  3364. * @return int
  3365. */
  3366. public static function ASCIICODE($characters) {
  3367. $characters = self::flattenSingleValue($characters);
  3368. if (is_bool($characters)) {
  3369. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  3370. $characters = (int) $characters;
  3371. } else {
  3372. if ($characters) {
  3373. $characters = 'True';
  3374. } else {
  3375. $characters = 'False';
  3376. }
  3377. }
  3378. }
  3379. if (strlen($characters) > 0) {
  3380. return ord(substr($characters, 0, 1));
  3381. }
  3382. return self::$_errorCodes['value'];
  3383. }
  3384. /**
  3385. * CONCATENATE
  3386. *
  3387. * @return string
  3388. */
  3389. public static function CONCATENATE() {
  3390. // Return value
  3391. $returnValue = '';
  3392. // Loop trough arguments
  3393. $aArgs = self::flattenArray(func_get_args());
  3394. foreach ($aArgs as $arg) {
  3395. if (is_bool($arg)) {
  3396. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  3397. $arg = (int) $arg;
  3398. } else {
  3399. if ($arg) {
  3400. $arg = 'TRUE';
  3401. } else {
  3402. $arg = 'FALSE';
  3403. }
  3404. }
  3405. }
  3406. $returnValue .= $arg;
  3407. }
  3408. // Return
  3409. return $returnValue;
  3410. }
  3411. /**
  3412. * SEARCHSENSITIVE
  3413. *
  3414. * @param string $needle The string to look for
  3415. * @param string $haystack The string in which to look
  3416. * @param int $offset Offset within $haystack
  3417. * @return string
  3418. */
  3419. public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
  3420. $needle = (string) self::flattenSingleValue($needle);
  3421. $haystack = (string) self::flattenSingleValue($haystack);
  3422. $offset = self::flattenSingleValue($offset);
  3423. if (($offset > 0) && (strlen($haystack) > $offset)) {
  3424. $pos = strpos($haystack, $needle, --$offset);
  3425. if ($pos !== false) {
  3426. return ++$pos;
  3427. }
  3428. }
  3429. return self::$_errorCodes['value'];
  3430. }
  3431. /**
  3432. * SEARCHINSENSITIVE
  3433. *
  3434. * @param string $needle The string to look for
  3435. * @param string $haystack The string in which to look
  3436. * @param int $offset Offset within $haystack
  3437. * @return string
  3438. */
  3439. public static function SEARCHINSENSITIVE($needle,$haystack,$offset=1) {
  3440. $needle = (string) self::flattenSingleValue($needle);
  3441. $haystack = (string) self::flattenSingleValue($haystack);
  3442. $offset = self::flattenSingleValue($offset);
  3443. if (($offset > 0) && (strlen($haystack) > $offset)) {
  3444. $pos = stripos($haystack, $needle, --$offset);
  3445. if ($pos !== false) {
  3446. return ++$pos;
  3447. }
  3448. }
  3449. return self::$_errorCodes['value'];
  3450. }
  3451. /**
  3452. * LEFT
  3453. *
  3454. * @param string $value Value
  3455. * @param int $chars Number of characters
  3456. * @return string
  3457. */
  3458. public static function LEFT($value = '', $chars = null) {
  3459. $value = self::flattenSingleValue($value);
  3460. $chars = self::flattenSingleValue($chars);
  3461. return substr($value, 0, $chars);
  3462. }
  3463. /**
  3464. * RIGHT
  3465. *
  3466. * @param string $value Value
  3467. * @param int $chars Number of characters
  3468. * @return string
  3469. */
  3470. public static function RIGHT($value = '', $chars = null) {
  3471. $value = self::flattenSingleValue($value);
  3472. $chars = self::flattenSingleValue($chars);
  3473. return substr($value, strlen($value) - $chars);
  3474. }
  3475. /**
  3476. * MID
  3477. *
  3478. * @param string $value Value
  3479. * @param int $start Start character
  3480. * @param int $chars Number of characters
  3481. * @return string
  3482. */
  3483. public static function MID($value = '', $start = 1, $chars = null) {
  3484. $value = self::flattenSingleValue($value);
  3485. $start = self::flattenSingleValue($start);
  3486. $chars = self::flattenSingleValue($chars);
  3487. return substr($value, --$start, $chars);
  3488. }
  3489. /**
  3490. * RETURNSTRING
  3491. *
  3492. * @param mixed $value Value to check
  3493. * @return boolean
  3494. */
  3495. public static function RETURNSTRING($testValue = '') {
  3496. $testValue = self::flattenSingleValue($testValue);
  3497. if (is_string($testValue)) {
  3498. return $testValue;
  3499. }
  3500. return Null;
  3501. }
  3502. /**
  3503. * TRIMSPACES
  3504. *
  3505. * @param mixed $value Value to check
  3506. * @return string
  3507. */
  3508. public static function TRIMSPACES($stringValue = '') {
  3509. $stringValue = self::flattenSingleValue($stringValue);
  3510. if (is_string($stringValue)) {
  3511. return str_replace(' ',' ',trim($stringValue));
  3512. }
  3513. return Null;
  3514. }
  3515. private static $_invalidChars = Null;
  3516. /**
  3517. * TRIMNONPRINTABLE
  3518. *
  3519. * @param mixed $value Value to check
  3520. * @return string
  3521. */
  3522. public static function TRIMNONPRINTABLE($stringValue = '') {
  3523. $stringValue = self::flattenSingleValue($stringValue);
  3524. if (self::$_invalidChars == Null) {
  3525. self::$_invalidChars = range(chr(0),chr(31));
  3526. }
  3527. if (is_string($stringValue)) {
  3528. return str_replace(self::$_invalidChars,'',trim($stringValue,"\x00..\x1F"));
  3529. }
  3530. return Null;
  3531. }
  3532. /**
  3533. * IS_BLANK
  3534. *
  3535. * @param mixed $value Value to check
  3536. * @return boolean
  3537. */
  3538. public static function IS_BLANK($value = '') {
  3539. $value = self::flattenSingleValue($value);
  3540. return (is_null($value) || (is_string($value) && ($value == '')));
  3541. }
  3542. /**
  3543. * IS_ERR
  3544. *
  3545. * @param mixed $value Value to check
  3546. * @return boolean
  3547. */
  3548. public static function IS_ERR($value = '') {
  3549. $value = self::flattenSingleValue($value);
  3550. return self::IS_ERROR($value) && (!self::IS_NA($value));
  3551. }
  3552. /**
  3553. * IS_ERROR
  3554. *
  3555. * @param mixed $value Value to check
  3556. * @return boolean
  3557. */
  3558. public static function IS_ERROR($value = '') {
  3559. $value = self::flattenSingleValue($value);
  3560. return in_array($value, array_values(self::$_errorCodes));
  3561. }
  3562. /**
  3563. * IS_NA
  3564. *
  3565. * @param mixed $value Value to check
  3566. * @return boolean
  3567. */
  3568. public static function IS_NA($value = '') {
  3569. $value = self::flattenSingleValue($value);
  3570. return ($value == self::$_errorCodes['na']);
  3571. }
  3572. /**
  3573. * IS_EVEN
  3574. *
  3575. * @param mixed $value Value to check
  3576. * @return boolean
  3577. */
  3578. public static function IS_EVEN($value = 0) {
  3579. $value = self::flattenSingleValue($value);
  3580. while (intval($value) != $value) {
  3581. $value *= 10;
  3582. }
  3583. return ($value % 2 == 0);
  3584. }
  3585. /**
  3586. * IS_NUMBER
  3587. *
  3588. * @param mixed $value Value to check
  3589. * @return boolean
  3590. */
  3591. public static function IS_NUMBER($value = 0) {
  3592. $value = self::flattenSingleValue($value);
  3593. return is_numeric($value);
  3594. }
  3595. /**
  3596. * IS_LOGICAL
  3597. *
  3598. * @param mixed $value Value to check
  3599. * @return boolean
  3600. */
  3601. public static function IS_LOGICAL($value = true) {
  3602. $value = self::flattenSingleValue($value);
  3603. return is_bool($value);
  3604. }
  3605. /**
  3606. * IS_TEXT
  3607. *
  3608. * @param mixed $value Value to check
  3609. * @return boolean
  3610. */
  3611. public static function IS_TEXT($value = '') {
  3612. $value = self::flattenSingleValue($value);
  3613. return is_string($value);
  3614. }
  3615. /**
  3616. * STATEMENT_IF
  3617. *
  3618. * @param mixed $value Value to check
  3619. * @param mixed $truepart Value when true
  3620. * @param mixed $falsepart Value when false
  3621. * @return mixed
  3622. */
  3623. public static function STATEMENT_IF($value = true, $truepart = '', $falsepart = '') {
  3624. $value = self::flattenSingleValue($value);
  3625. $truepart = self::flattenSingleValue($truepart);
  3626. $falsepart = self::flattenSingleValue($falsepart);
  3627. return ($value ? $truepart : $falsepart);
  3628. }
  3629. /**
  3630. * STATEMENT_IFERROR
  3631. *
  3632. * @param mixed $value Value to check , is also value when no error
  3633. * @param mixed $errorpart Value when error
  3634. * @return mixed
  3635. */
  3636. public static function STATEMENT_IFERROR($value = '', $errorpart = '') {
  3637. return self::STATEMENT_IF(self::IS_ERROR($value), $errorpart, $value);
  3638. }
  3639. /**
  3640. * VERSION
  3641. *
  3642. * @return string Version information
  3643. */
  3644. public static function VERSION() {
  3645. return 'PHPExcel ##VERSION##, ##DATE##';
  3646. }
  3647. /**
  3648. * DATE
  3649. *
  3650. * @param long $year
  3651. * @param long $month
  3652. * @param long $day
  3653. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3654. * depending on the value of the ReturnDateType flag
  3655. */
  3656. public static function DATE($year = 0, $month = 1, $day = 1) {
  3657. $year = (integer) self::flattenSingleValue($year);
  3658. $month = (integer) self::flattenSingleValue($month);
  3659. $day = (integer) self::flattenSingleValue($day);
  3660. $baseYear = PHPExcel_Shared_Date::getExcelCalendar();
  3661. // Validate parameters
  3662. if ($year < ($baseYear-1900)) {
  3663. return self::$_errorCodes['num'];
  3664. }
  3665. if ((($baseYear-1900) != 0) && ($year < $baseYear) && ($year >= 1900)) {
  3666. return self::$_errorCodes['num'];
  3667. }
  3668. if (($year < $baseYear) && ($year > ($baseYear-1900))) {
  3669. $year += 1900;
  3670. }
  3671. if ($month < 1) {
  3672. // Handle year/month adjustment if month < 1
  3673. --$month;
  3674. $year += ceil($month / 12) - 1;
  3675. $month = 13 - abs($month % 12);
  3676. } elseif ($month > 12) {
  3677. // Handle year/month adjustment if month > 12
  3678. $year += floor($month / 12);
  3679. $month = ($month % 12);
  3680. }
  3681. // Re-validate the year parameter after adjustments
  3682. if (($year < $baseYear) || ($year >= 10000)) {
  3683. return self::$_errorCodes['num'];
  3684. }
  3685. // Execute function
  3686. $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day);
  3687. switch (self::getReturnDateType()) {
  3688. case self::RETURNDATE_EXCEL : return (float) $excelDateValue;
  3689. break;
  3690. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
  3691. break;
  3692. case self::RETURNDATE_PHP_OBJECT : return PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
  3693. break;
  3694. }
  3695. }
  3696. /**
  3697. * TIME
  3698. *
  3699. * @param long $hour
  3700. * @param long $minute
  3701. * @param long $second
  3702. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3703. * depending on the value of the ReturnDateType flag
  3704. */
  3705. public static function TIME($hour = 0, $minute = 0, $second = 0) {
  3706. $hour = self::flattenSingleValue($hour);
  3707. $minute = self::flattenSingleValue($minute);
  3708. $second = self::flattenSingleValue($second);
  3709. if ($hour == '') { $hour = 0; }
  3710. if ($minute == '') { $minute = 0; }
  3711. if ($second == '') { $second = 0; }
  3712. if ((!is_numeric($hour)) || (!is_numeric($minute)) || (!is_numeric($second))) {
  3713. return self::$_errorCodes['value'];
  3714. }
  3715. $hour = (integer) $hour;
  3716. $minute = (integer) $minute;
  3717. $second = (integer) $second;
  3718. if ($second < 0) {
  3719. $minute += floor($second / 60);
  3720. $second = 60 - abs($second % 60);
  3721. if ($second == 60) { $second = 0; }
  3722. } elseif ($second >= 60) {
  3723. $minute += floor($second / 60);
  3724. $second = $second % 60;
  3725. }
  3726. if ($minute < 0) {
  3727. $hour += floor($minute / 60);
  3728. $minute = 60 - abs($minute % 60);
  3729. if ($minute == 60) { $minute = 0; }
  3730. } elseif ($minute >= 60) {
  3731. $hour += floor($minute / 60);
  3732. $minute = $minute % 60;
  3733. }
  3734. if ($hour > 23) {
  3735. $hour = $hour % 24;
  3736. } elseif ($hour < 0) {
  3737. return self::$_errorCodes['num'];
  3738. }
  3739. // Execute function
  3740. switch (self::getReturnDateType()) {
  3741. case self::RETURNDATE_EXCEL : $date = 0;
  3742. $calendar = PHPExcel_Shared_Date::getExcelCalendar();
  3743. if ($calendar != PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900) {
  3744. $date = 1;
  3745. }
  3746. return (float) PHPExcel_Shared_Date::FormattedPHPToExcel($calendar, 1, $date, $hour, $minute, $second);
  3747. break;
  3748. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::FormattedPHPToExcel(1970, 1, 1, $hour-1, $minute, $second)); // -2147468400; // -2147472000 + 3600
  3749. break;
  3750. case self::RETURNDATE_PHP_OBJECT : $dayAdjust = 0;
  3751. if ($hour < 0) {
  3752. $dayAdjust = floor($hour / 24);
  3753. $hour = 24 - abs($hour % 24);
  3754. if ($hour == 24) { $hour = 0; }
  3755. } elseif ($hour >= 24) {
  3756. $dayAdjust = floor($hour / 24);
  3757. $hour = $hour % 24;
  3758. }
  3759. $phpDateObject = new DateTime('1900-01-01 '.$hour.':'.$minute.':'.$second);
  3760. if ($dayAdjust != 0) {
  3761. $phpDateObject->modify($dayAdjust.' days');
  3762. }
  3763. return $phpDateObject;
  3764. break;
  3765. }
  3766. }
  3767. /**
  3768. * DATEVALUE
  3769. *
  3770. * @param string $dateValue
  3771. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3772. * depending on the value of the ReturnDateType flag
  3773. */
  3774. public static function DATEVALUE($dateValue = 1) {
  3775. $dateValue = self::flattenSingleValue($dateValue);
  3776. $PHPDateArray = date_parse($dateValue);
  3777. if (($PHPDateArray === False) || ($PHPDateArray['error_count'] > 0)) {
  3778. $testVal1 = strtok($dateValue,'/- ');
  3779. if ($testVal1 !== False) {
  3780. $testVal2 = strtok('/- ');
  3781. if ($testVal2 !== False) {
  3782. $testVal3 = strtok('/- ');
  3783. if ($testVal3 === False) {
  3784. $testVal3 = strftime('%Y');
  3785. }
  3786. } else {
  3787. return self::$_errorCodes['value'];
  3788. }
  3789. } else {
  3790. return self::$_errorCodes['value'];
  3791. }
  3792. $PHPDateArray = date_parse($testVal1.'-'.$testVal2.'-'.$testVal3);
  3793. if (($PHPDateArray === False) || ($PHPDateArray['error_count'] > 0)) {
  3794. $PHPDateArray = date_parse($testVal2.'-'.$testVal1.'-'.$testVal3);
  3795. if (($PHPDateArray === False) || ($PHPDateArray['error_count'] > 0)) {
  3796. return self::$_errorCodes['value'];
  3797. }
  3798. }
  3799. }
  3800. if (($PHPDateArray !== False) && ($PHPDateArray['error_count'] == 0)) {
  3801. // Execute function
  3802. if ($PHPDateArray['year'] == '') { $PHPDateArray['year'] = strftime('%Y'); }
  3803. if ($PHPDateArray['month'] == '') { $PHPDateArray['month'] = strftime('%m'); }
  3804. if ($PHPDateArray['day'] == '') { $PHPDateArray['day'] = strftime('%d'); }
  3805. $excelDateValue = floor(PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'],$PHPDateArray['month'],$PHPDateArray['day'],$PHPDateArray['hour'],$PHPDateArray['minute'],$PHPDateArray['second']));
  3806. switch (self::getReturnDateType()) {
  3807. case self::RETURNDATE_EXCEL : return (float) $excelDateValue;
  3808. break;
  3809. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
  3810. break;
  3811. case self::RETURNDATE_PHP_OBJECT : return new DateTime($PHPDateArray['year'].'-'.$PHPDateArray['month'].'-'.$PHPDateArray['day'].' 00:00:00');
  3812. break;
  3813. }
  3814. }
  3815. return self::$_errorCodes['value'];
  3816. }
  3817. /**
  3818. * _getDateValue
  3819. *
  3820. * @param string $dateValue
  3821. * @return mixed Excel date/time serial value, or string if error
  3822. */
  3823. private static function _getDateValue($dateValue) {
  3824. if (!is_numeric($dateValue)) {
  3825. if ((is_string($dateValue)) && (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC)) {
  3826. return self::$_errorCodes['value'];
  3827. }
  3828. if ((is_object($dateValue)) && ($dateValue instanceof PHPExcel_Shared_Date::$dateTimeObjectType)) {
  3829. $dateValue = PHPExcel_Shared_Date::PHPToExcel($dateValue);
  3830. } else {
  3831. $saveReturnDateType = self::getReturnDateType();
  3832. self::setReturnDateType(self::RETURNDATE_EXCEL);
  3833. $dateValue = self::DATEVALUE($dateValue);
  3834. self::setReturnDateType($saveReturnDateType);
  3835. }
  3836. } elseif (!is_float($dateValue)) {
  3837. $dateValue = PHPExcel_Shared_Date::PHPToExcel($dateValue);
  3838. }
  3839. return $dateValue;
  3840. }
  3841. /**
  3842. * TIMEVALUE
  3843. *
  3844. * @param string $timeValue
  3845. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3846. * depending on the value of the ReturnDateType flag
  3847. */
  3848. public static function TIMEVALUE($timeValue) {
  3849. $timeValue = self::flattenSingleValue($timeValue);
  3850. if ((($PHPDateArray = date_parse($timeValue)) !== False) && ($PHPDateArray['error_count'] == 0)) {
  3851. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  3852. $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'],$PHPDateArray['month'],$PHPDateArray['day'],$PHPDateArray['hour'],$PHPDateArray['minute'],$PHPDateArray['second']);
  3853. } else {
  3854. $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel(1900,1,1,$PHPDateArray['hour'],$PHPDateArray['minute'],$PHPDateArray['second']) - 1;
  3855. }
  3856. switch (self::getReturnDateType()) {
  3857. case self::RETURNDATE_EXCEL : return (float) $excelDateValue;
  3858. break;
  3859. case self::RETURNDATE_PHP_NUMERIC : return (integer) $phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue+25569) - 3600;;
  3860. break;
  3861. case self::RETURNDATE_PHP_OBJECT : return new DateTime('1900-01-01 '.$PHPDateArray['hour'].':'.$PHPDateArray['minute'].':'.$PHPDateArray['second']);
  3862. break;
  3863. }
  3864. }
  3865. return self::$_errorCodes['value'];
  3866. }
  3867. /**
  3868. * _getTimeValue
  3869. *
  3870. * @param string $timeValue
  3871. * @return mixed Excel date/time serial value, or string if error
  3872. */
  3873. private static function _getTimeValue($timeValue) {
  3874. $saveReturnDateType = self::getReturnDateType();
  3875. self::setReturnDateType(self::RETURNDATE_EXCEL);
  3876. $timeValue = self::TIMEVALUE($timeValue);
  3877. self::setReturnDateType($saveReturnDateType);
  3878. return $timeValue;
  3879. }
  3880. /**
  3881. * DATETIMENOW
  3882. *
  3883. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3884. * depending on the value of the ReturnDateType flag
  3885. */
  3886. public static function DATETIMENOW() {
  3887. switch (self::getReturnDateType()) {
  3888. case self::RETURNDATE_EXCEL : return (float) PHPExcel_Shared_Date::PHPToExcel(time());
  3889. break;
  3890. case self::RETURNDATE_PHP_NUMERIC : return (integer) time();
  3891. break;
  3892. case self::RETURNDATE_PHP_OBJECT : return new DateTime();
  3893. break;
  3894. }
  3895. }
  3896. /**
  3897. * DATENOW
  3898. *
  3899. * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  3900. * depending on the value of the ReturnDateType flag
  3901. */
  3902. public static function DATENOW() {
  3903. $excelDateTime = floor(PHPExcel_Shared_Date::PHPToExcel(time()));
  3904. switch (self::getReturnDateType()) {
  3905. case self::RETURNDATE_EXCEL : return (float) $excelDateTime;
  3906. break;
  3907. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateTime) - 3600;
  3908. break;
  3909. case self::RETURNDATE_PHP_OBJECT : return PHPExcel_Shared_Date::ExcelToPHPObject($excelDateTime);
  3910. break;
  3911. }
  3912. }
  3913. private static function isLeapYear($year) {
  3914. return ((($year % 4) == 0) && (($year % 100) != 0) || (($year % 400) == 0));
  3915. }
  3916. private static function dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, $methodUS) {
  3917. if ($startDay == 31) {
  3918. $startDay--;
  3919. } elseif ($methodUS && ($startMonth == 2 && ($startDay == 29 || ($startDay == 28 && !self::isLeapYear($startYear))))) {
  3920. $startDay = 30;
  3921. }
  3922. if ($endDay == 31) {
  3923. if ($methodUS && $startDay != 30) {
  3924. $endDay = 1;
  3925. if ($endMonth == 12) {
  3926. $endYear++;
  3927. $endMonth = 1;
  3928. } else {
  3929. $endMonth++;
  3930. }
  3931. } else {
  3932. $endDay = 30;
  3933. }
  3934. }
  3935. return $endDay + $endMonth * 30 + $endYear * 360 - $startDay - $startMonth * 30 - $startYear * 360;
  3936. }
  3937. /**
  3938. * DAYS360
  3939. *
  3940. * @param long $startDate Excel date serial value or a standard date string
  3941. * @param long $endDate Excel date serial value or a standard date string
  3942. * @param boolean $method US or European Method
  3943. * @return long PHP date/time serial
  3944. */
  3945. public static function DAYS360($startDate = 0, $endDate = 0, $method = false) {
  3946. $startDate = self::flattenSingleValue($startDate);
  3947. $endDate = self::flattenSingleValue($endDate);
  3948. if (is_string($startDate = self::_getDateValue($startDate))) {
  3949. return self::$_errorCodes['value'];
  3950. }
  3951. if (is_string($endDate = self::_getDateValue($endDate))) {
  3952. return self::$_errorCodes['value'];
  3953. }
  3954. // Execute function
  3955. $PHPStartDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($startDate);
  3956. $startDay = $PHPStartDateObject->format('j');
  3957. $startMonth = $PHPStartDateObject->format('n');
  3958. $startYear = $PHPStartDateObject->format('Y');
  3959. $PHPEndDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($endDate);
  3960. $endDay = $PHPEndDateObject->format('j');
  3961. $endMonth = $PHPEndDateObject->format('n');
  3962. $endYear = $PHPEndDateObject->format('Y');
  3963. return self::dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, !$method);
  3964. }
  3965. /**
  3966. * DATEDIF
  3967. *
  3968. * @param long $startDate Excel date serial value or a standard date string
  3969. * @param long $endDate Excel date serial value or a standard date string
  3970. * @param string $unit
  3971. * @return long Interval between the dates
  3972. */
  3973. public static function DATEDIF($startDate = 0, $endDate = 0, $unit = 'D') {
  3974. $startDate = self::flattenSingleValue($startDate);
  3975. $endDate = self::flattenSingleValue($endDate);
  3976. $unit = strtoupper(self::flattenSingleValue($unit));
  3977. if (is_string($startDate = self::_getDateValue($startDate))) {
  3978. return self::$_errorCodes['value'];
  3979. }
  3980. if (is_string($endDate = self::_getDateValue($endDate))) {
  3981. return self::$_errorCodes['value'];
  3982. }
  3983. // Validate parameters
  3984. if ($startDate >= $endDate) {
  3985. return self::$_errorCodes['num'];
  3986. }
  3987. // Execute function
  3988. $difference = $endDate - $startDate;
  3989. $PHPStartDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($startDate);
  3990. $startDays = $PHPStartDateObject->format('j');
  3991. $startMonths = $PHPStartDateObject->format('n');
  3992. $startYears = $PHPStartDateObject->format('Y');
  3993. $PHPEndDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($endDate);
  3994. $endDays = $PHPEndDateObject->format('j');
  3995. $endMonths = $PHPEndDateObject->format('n');
  3996. $endYears = $PHPEndDateObject->format('Y');
  3997. $retVal = self::$_errorCodes['num'];
  3998. switch ($unit) {
  3999. case 'D':
  4000. $retVal = intval($difference);
  4001. break;
  4002. case 'M':
  4003. $retVal = intval($endMonths - $startMonths) + (intval($endYears - $startYears) * 12);
  4004. // We're only interested in full months
  4005. if ($endDays < $startDays) {
  4006. $retVal--;
  4007. }
  4008. break;
  4009. case 'Y':
  4010. $retVal = intval($endYears - $startYears);
  4011. // We're only interested in full months
  4012. if ($endMonths < $startMonths) {
  4013. $retVal--;
  4014. } elseif (($endMonths == $startMonths) && ($endDays < $startDays)) {
  4015. $retVal--;
  4016. }
  4017. break;
  4018. case 'MD':
  4019. if ($endDays < $startDays) {
  4020. $retVal = $endDays;
  4021. $PHPEndDateObject->modify('-'.$endDays.' days');
  4022. $adjustDays = $PHPEndDateObject->format('j');
  4023. if ($adjustDays > $startDays) {
  4024. $retVal += ($adjustDays - $startDays);
  4025. }
  4026. } else {
  4027. $retVal = $endDays - $startDays;
  4028. }
  4029. break;
  4030. case 'YM':
  4031. $retVal = abs(intval($endMonths - $startMonths));
  4032. // We're only interested in full months
  4033. if ($endDays < $startDays) {
  4034. $retVal--;
  4035. }
  4036. break;
  4037. case 'YD':
  4038. $retVal = intval($difference);
  4039. if ($endYears > $startYears) {
  4040. while ($endYears > $startYears) {
  4041. $PHPEndDateObject->modify('-1 year');
  4042. $endYears = $PHPEndDateObject->format('Y');
  4043. }
  4044. $retVal = abs($PHPEndDateObject->format('z') - $PHPStartDateObject->format('z'));
  4045. }
  4046. break;
  4047. }
  4048. return $retVal;
  4049. }
  4050. /**
  4051. * YEARFRAC
  4052. *
  4053. * @param long $startDate Excel date serial value or a standard date string
  4054. * @param long $endDate Excel date serial value or a standard date string
  4055. * @param integer $method Method used for the calculation
  4056. * @return long PHP date/time serial
  4057. */
  4058. public static function YEARFRAC($startDate = 0, $endDate = 0, $method = 0) {
  4059. $startDate = self::flattenSingleValue($startDate);
  4060. $endDate = self::flattenSingleValue($endDate);
  4061. $method = self::flattenSingleValue($method);
  4062. if (is_string($startDate = self::_getDateValue($startDate))) {
  4063. return self::$_errorCodes['value'];
  4064. }
  4065. if (is_string($endDate = self::_getDateValue($endDate))) {
  4066. return self::$_errorCodes['value'];
  4067. }
  4068. if ((is_numeric($method)) && (!is_string($method))) {
  4069. switch($method) {
  4070. case 0 :
  4071. return self::DAYS360($startDate,$endDate) / 360;
  4072. break;
  4073. case 1 :
  4074. $startYear = self::YEAR($startDate);
  4075. $endYear = self::YEAR($endDate);
  4076. $leapDay = 0;
  4077. if (self::isLeapYear($startYear) || self::isLeapYear($endYear)) {
  4078. $leapDay = 1;
  4079. }
  4080. return self::DATEDIF($startDate,$endDate) / (365 + $leapDay);
  4081. break;
  4082. case 2 :
  4083. return self::DATEDIF($startDate,$endDate) / 360;
  4084. break;
  4085. case 3 :
  4086. return self::DATEDIF($startDate,$endDate) / 365;
  4087. break;
  4088. case 4 :
  4089. return self::DAYS360($startDate,$endDate,True) / 360;
  4090. break;
  4091. }
  4092. }
  4093. return self::$_errorCodes['value'];
  4094. }
  4095. /**
  4096. * NETWORKDAYS
  4097. *
  4098. * @param mixed Start date
  4099. * @param mixed End date
  4100. * @param array of mixed Optional Date Series
  4101. * @return long Interval between the dates
  4102. */
  4103. public static function NETWORKDAYS($startDate,$endDate) {
  4104. // Flush the mandatory start and end date that are referenced in the function definition
  4105. $dateArgs = self::flattenArray(func_get_args());
  4106. array_shift($dateArgs);
  4107. array_shift($dateArgs);
  4108. // Validate the start and end dates
  4109. if (is_string($startDate = $sDate = self::_getDateValue($startDate))) {
  4110. return self::$_errorCodes['value'];
  4111. }
  4112. if (is_string($endDate = $eDate = self::_getDateValue($endDate))) {
  4113. return self::$_errorCodes['value'];
  4114. }
  4115. if ($sDate > $eDate) {
  4116. $startDate = $eDate;
  4117. $endDate = $sDate;
  4118. }
  4119. // Execute function
  4120. $startDoW = 6 - self::DAYOFWEEK($startDate,2);
  4121. if ($startDoW < 0) { $startDoW = 0; }
  4122. $endDoW = self::DAYOFWEEK($endDate,2);
  4123. if ($endDoW >= 6) { $endDoW = 0; }
  4124. $wholeWeekDays = floor(($endDate - $startDate) / 7) * 5;
  4125. $partWeekDays = $endDoW + $startDoW;
  4126. if ($partWeekDays > 5) {
  4127. $partWeekDays -= 5;
  4128. }
  4129. // Test any extra holiday parameters
  4130. $holidayCountedArray = array();
  4131. foreach ($dateArgs as $holidayDate) {
  4132. if (is_string($holidayDate = self::_getDateValue($holidayDate))) {
  4133. return self::$_errorCodes['value'];
  4134. }
  4135. if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) {
  4136. if ((self::DAYOFWEEK($holidayDate,2) < 6) && (!in_array($holidayDate,$holidayCountedArray))) {
  4137. --$partWeekDays;
  4138. $holidayCountedArray[] = $holidayDate;
  4139. }
  4140. }
  4141. }
  4142. if ($sDate > $eDate) {
  4143. return 0 - ($wholeWeekDays + $partWeekDays);
  4144. }
  4145. return $wholeWeekDays + $partWeekDays;
  4146. }
  4147. /**
  4148. * WORKDAY
  4149. *
  4150. * @param mixed Start date
  4151. * @param mixed number of days for adjustment
  4152. * @param array of mixed Optional Date Series
  4153. * @return long Interval between the dates
  4154. */
  4155. public static function WORKDAY($startDate,$endDays) {
  4156. $dateArgs = self::flattenArray(func_get_args());
  4157. array_shift($dateArgs);
  4158. array_shift($dateArgs);
  4159. if (is_string($startDate = self::_getDateValue($startDate))) {
  4160. return self::$_errorCodes['value'];
  4161. }
  4162. if (!is_numeric($endDays)) {
  4163. return self::$_errorCodes['value'];
  4164. }
  4165. $endDate = (float) $startDate + (floor($endDays / 5) * 7) + ($endDays % 5);
  4166. if ($endDays < 0) {
  4167. $endDate += 7;
  4168. }
  4169. $endDoW = self::DAYOFWEEK($endDate,3);
  4170. if ($endDoW >= 5) {
  4171. if ($endDays >= 0) {
  4172. $endDate += (7 - $endDoW);
  4173. } else {
  4174. $endDate -= ($endDoW - 5);
  4175. }
  4176. }
  4177. // Test any extra holiday parameters
  4178. if (count($dateArgs) > 0) {
  4179. $holidayCountedArray = $holidayDates = array();
  4180. foreach ($dateArgs as $holidayDate) {
  4181. if (is_string($holidayDate = self::_getDateValue($holidayDate))) {
  4182. return self::$_errorCodes['value'];
  4183. }
  4184. $holidayDates[] = $holidayDate;
  4185. }
  4186. if ($endDays >= 0) {
  4187. sort($holidayDates, SORT_NUMERIC);
  4188. } else {
  4189. rsort($holidayDates, SORT_NUMERIC);
  4190. }
  4191. foreach ($holidayDates as $holidayDate) {
  4192. if ($endDays >= 0) {
  4193. if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) {
  4194. if ((self::DAYOFWEEK($holidayDate,2) < 6) && (!in_array($holidayDate,$holidayCountedArray))) {
  4195. ++$endDate;
  4196. $holidayCountedArray[] = $holidayDate;
  4197. }
  4198. }
  4199. } else {
  4200. if (($holidayDate <= $startDate) && ($holidayDate >= $endDate)) {
  4201. if ((self::DAYOFWEEK($holidayDate,2) < 6) && (!in_array($holidayDate,$holidayCountedArray))) {
  4202. --$endDate;
  4203. $holidayCountedArray[] = $holidayDate;
  4204. }
  4205. }
  4206. }
  4207. $endDoW = self::DAYOFWEEK($endDate,3);
  4208. if ($endDoW >= 5) {
  4209. if ($endDays >= 0) {
  4210. $endDate += (7 - $endDoW);
  4211. } else {
  4212. $endDate -= ($endDoW - 5);
  4213. }
  4214. }
  4215. }
  4216. }
  4217. switch (self::getReturnDateType()) {
  4218. case self::RETURNDATE_EXCEL : return (float) $endDate;
  4219. break;
  4220. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP($endDate);
  4221. break;
  4222. case self::RETURNDATE_PHP_OBJECT : return PHPExcel_Shared_Date::ExcelToPHPObject($endDate);
  4223. break;
  4224. }
  4225. }
  4226. /**
  4227. * DAYOFMONTH
  4228. *
  4229. * @param long $dateValue Excel date serial value or a standard date string
  4230. * @return int Day
  4231. */
  4232. public static function DAYOFMONTH($dateValue = 1) {
  4233. $dateValue = self::flattenSingleValue($dateValue);
  4234. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4235. return self::$_errorCodes['value'];
  4236. }
  4237. // Execute function
  4238. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4239. return $PHPDateObject->format('j');
  4240. }
  4241. /**
  4242. * DAYOFWEEK
  4243. *
  4244. * @param long $dateValue Excel date serial value or a standard date string
  4245. * @return int Day
  4246. */
  4247. public static function DAYOFWEEK($dateValue = 1, $style = 1) {
  4248. $dateValue = self::flattenSingleValue($dateValue);
  4249. $style = floor(self::flattenSingleValue($style));
  4250. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4251. return self::$_errorCodes['value'];
  4252. }
  4253. // Execute function
  4254. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4255. $DoW = $PHPDateObject->format('w');
  4256. $firstDay = 1;
  4257. switch ($style) {
  4258. case 1: ++$DoW;
  4259. break;
  4260. case 2: if ($DoW == 0) { $DoW = 7; }
  4261. break;
  4262. case 3: if ($DoW == 0) { $DoW = 7; }
  4263. $firstDay = 0;
  4264. --$DoW;
  4265. break;
  4266. default:
  4267. }
  4268. if (self::$compatibilityMode == self::COMPATIBILITY_EXCEL) {
  4269. // Test for Excel's 1900 leap year, and introduce the error as required
  4270. if (($PHPDateObject->format('Y') == 1900) && ($PHPDateObject->format('n') <= 2)) {
  4271. --$DoW;
  4272. if ($DoW < $firstDay) {
  4273. $DoW += 7;
  4274. }
  4275. }
  4276. }
  4277. return $DoW;
  4278. }
  4279. /**
  4280. * WEEKOFYEAR
  4281. *
  4282. * @param long $dateValue Excel date serial value or a standard date string
  4283. * @param boolean $method Week begins on Sunday or Monday
  4284. * @return int Week Number
  4285. */
  4286. public static function WEEKOFYEAR($dateValue = 1, $method = 1) {
  4287. $dateValue = self::flattenSingleValue($dateValue);
  4288. $method = floor(self::flattenSingleValue($method));
  4289. if (!is_numeric($method)) {
  4290. return self::$_errorCodes['value'];
  4291. } elseif (($method < 1) || ($method > 2)) {
  4292. return self::$_errorCodes['num'];
  4293. }
  4294. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4295. return self::$_errorCodes['value'];
  4296. }
  4297. // Execute function
  4298. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4299. $dayOfYear = $PHPDateObject->format('z');
  4300. $dow = $PHPDateObject->format('w');
  4301. $PHPDateObject->modify('-'.$dayOfYear.' days');
  4302. $dow = $PHPDateObject->format('w');
  4303. $daysInFirstWeek = 7 - (($dow + (2 - $method)) % 7);
  4304. $dayOfYear -= $daysInFirstWeek;
  4305. $weekOfYear = ceil($dayOfYear / 7) + 1;
  4306. return $weekOfYear;
  4307. }
  4308. /**
  4309. * MONTHOFYEAR
  4310. *
  4311. * @param long $dateValue Excel date serial value or a standard date string
  4312. * @return int Month
  4313. */
  4314. public static function MONTHOFYEAR($dateValue = 1) {
  4315. $dateValue = self::flattenSingleValue($dateValue);
  4316. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4317. return self::$_errorCodes['value'];
  4318. }
  4319. // Execute function
  4320. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4321. return $PHPDateObject->format('n');
  4322. }
  4323. /**
  4324. * YEAR
  4325. *
  4326. * @param long $dateValue Excel date serial value or a standard date string
  4327. * @return int Year
  4328. */
  4329. public static function YEAR($dateValue = 1) {
  4330. $dateValue = self::flattenSingleValue($dateValue);
  4331. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4332. return self::$_errorCodes['value'];
  4333. }
  4334. // Execute function
  4335. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4336. return $PHPDateObject->format('Y');
  4337. }
  4338. /**
  4339. * HOUROFDAY
  4340. *
  4341. * @param mixed $timeValue Excel time serial value or a standard time string
  4342. * @return int Hour
  4343. */
  4344. public static function HOUROFDAY($timeValue = 0) {
  4345. $timeValue = self::flattenSingleValue($timeValue);
  4346. if (!is_numeric($timeValue)) {
  4347. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4348. $testVal = strtok($timeValue,'/-: ');
  4349. if (strlen($testVal) < strlen($timeValue)) {
  4350. return self::$_errorCodes['value'];
  4351. }
  4352. }
  4353. $timeValue = self::_getTimeValue($timeValue);
  4354. if (is_string($timeValue)) {
  4355. return self::$_errorCodes['value'];
  4356. }
  4357. }
  4358. // Execute function
  4359. if (is_real($timeValue)) {
  4360. $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
  4361. }
  4362. return date('G',$timeValue);
  4363. }
  4364. /**
  4365. * MINUTEOFHOUR
  4366. *
  4367. * @param long $timeValue Excel time serial value or a standard time string
  4368. * @return int Minute
  4369. */
  4370. public static function MINUTEOFHOUR($timeValue = 0) {
  4371. $timeValue = $timeTester = self::flattenSingleValue($timeValue);
  4372. if (!is_numeric($timeValue)) {
  4373. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4374. $testVal = strtok($timeValue,'/-: ');
  4375. if (strlen($testVal) < strlen($timeValue)) {
  4376. return self::$_errorCodes['value'];
  4377. }
  4378. }
  4379. $timeValue = self::_getTimeValue($timeValue);
  4380. if (is_string($timeValue)) {
  4381. return self::$_errorCodes['value'];
  4382. }
  4383. }
  4384. // Execute function
  4385. if (is_real($timeValue)) {
  4386. $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
  4387. }
  4388. return (int) date('i',$timeValue);
  4389. }
  4390. /**
  4391. * SECONDOFMINUTE
  4392. *
  4393. * @param long $timeValue Excel time serial value or a standard time string
  4394. * @return int Second
  4395. */
  4396. public static function SECONDOFMINUTE($timeValue = 0) {
  4397. $timeValue = self::flattenSingleValue($timeValue);
  4398. if (!is_numeric($timeValue)) {
  4399. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4400. $testVal = strtok($timeValue,'/-: ');
  4401. if (strlen($testVal) < strlen($timeValue)) {
  4402. return self::$_errorCodes['value'];
  4403. }
  4404. }
  4405. $timeValue = self::_getTimeValue($timeValue);
  4406. if (is_string($timeValue)) {
  4407. return self::$_errorCodes['value'];
  4408. }
  4409. }
  4410. // Execute function
  4411. if (is_real($timeValue)) {
  4412. $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
  4413. }
  4414. return (int) date('s',$timeValue);
  4415. }
  4416. private static function adjustDateByMonths ($dateValue = 0, $adjustmentMonths = 0) {
  4417. // Execute function
  4418. $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
  4419. $oMonth = (int) $PHPDateObject->format('m');
  4420. $oYear = (int) $PHPDateObject->format('Y');
  4421. $adjustmentMonthsString = (string) $adjustmentMonths;
  4422. if ($adjustmentMonths > 0) {
  4423. $adjustmentMonthsString = '+'.$adjustmentMonths;
  4424. }
  4425. if ($adjustmentMonths != 0) {
  4426. $PHPDateObject->modify($adjustmentMonthsString.' months');
  4427. }
  4428. $nMonth = (int) $PHPDateObject->format('m');
  4429. $nYear = (int) $PHPDateObject->format('Y');
  4430. $monthDiff = ($nMonth - $oMonth) + (($nYear - $oYear) * 12);
  4431. if ($monthDiff != $adjustmentMonths) {
  4432. $adjustDays = (int) $PHPDateObject->format('d');
  4433. $adjustDaysString = '-'.$adjustDays.' days';
  4434. $PHPDateObject->modify($adjustDaysString);
  4435. }
  4436. return $PHPDateObject;
  4437. }
  4438. /**
  4439. * EDATE
  4440. *
  4441. * Returns the serial number that represents the date that is the indicated number of months before or after a specified date
  4442. * (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
  4443. *
  4444. * @param long $dateValue Excel date serial value or a standard date string
  4445. * @param int $adjustmentMonths Number of months to adjust by
  4446. * @return long Excel date serial value
  4447. */
  4448. public static function EDATE($dateValue = 1, $adjustmentMonths = 0) {
  4449. $dateValue = self::flattenSingleValue($dateValue);
  4450. $adjustmentMonths = floor(self::flattenSingleValue($adjustmentMonths));
  4451. if (!is_numeric($adjustmentMonths)) {
  4452. return self::$_errorCodes['value'];
  4453. }
  4454. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4455. return self::$_errorCodes['value'];
  4456. }
  4457. // Execute function
  4458. $PHPDateObject = self::adjustDateByMonths($dateValue,$adjustmentMonths);
  4459. switch (self::getReturnDateType()) {
  4460. case self::RETURNDATE_EXCEL : return (float) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
  4461. break;
  4462. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
  4463. break;
  4464. case self::RETURNDATE_PHP_OBJECT : return $PHPDateObject;
  4465. break;
  4466. }
  4467. }
  4468. /**
  4469. * EOMONTH
  4470. *
  4471. * Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
  4472. * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
  4473. *
  4474. * @param long $dateValue Excel date serial value or a standard date string
  4475. * @param int $adjustmentMonths Number of months to adjust by
  4476. * @return long Excel date serial value
  4477. */
  4478. public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) {
  4479. $dateValue = self::flattenSingleValue($dateValue);
  4480. $adjustmentMonths = floor(self::flattenSingleValue($adjustmentMonths));
  4481. if (!is_numeric($adjustmentMonths)) {
  4482. return self::$_errorCodes['value'];
  4483. }
  4484. if (is_string($dateValue = self::_getDateValue($dateValue))) {
  4485. return self::$_errorCodes['value'];
  4486. }
  4487. // Execute function
  4488. $PHPDateObject = self::adjustDateByMonths($dateValue,$adjustmentMonths+1);
  4489. $adjustDays = (int) $PHPDateObject->format('d');
  4490. $adjustDaysString = '-'.$adjustDays.' days';
  4491. $PHPDateObject->modify($adjustDaysString);
  4492. switch (self::getReturnDateType()) {
  4493. case self::RETURNDATE_EXCEL : return (float) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
  4494. break;
  4495. case self::RETURNDATE_PHP_NUMERIC : return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
  4496. break;
  4497. case self::RETURNDATE_PHP_OBJECT : return $PHPDateObject;
  4498. break;
  4499. }
  4500. }
  4501. /**
  4502. * TRUNC
  4503. *
  4504. * Truncates value to the number of fractional digits by number_digits.
  4505. *
  4506. * @param float $value
  4507. * @param int $number_digits
  4508. * @return float Truncated value
  4509. */
  4510. public static function TRUNC($value = 0, $number_digits = 0) {
  4511. $value = self::flattenSingleValue($value);
  4512. $number_digits = self::flattenSingleValue($number_digits);
  4513. // Validate parameters
  4514. if ($number_digits < 0) {
  4515. return self::$_errorCodes['value'];
  4516. }
  4517. // Truncate
  4518. if ($number_digits > 0) {
  4519. $value = $value * pow(10, $number_digits);
  4520. }
  4521. $value = intval($value);
  4522. if ($number_digits > 0) {
  4523. $value = $value / pow(10, $number_digits);
  4524. }
  4525. // Return
  4526. return $value;
  4527. }
  4528. /**
  4529. * POWER
  4530. *
  4531. * Computes x raised to the power y.
  4532. *
  4533. * @param float $x
  4534. * @param float $y
  4535. * @return float
  4536. */
  4537. public static function POWER($x = 0, $y = 2) {
  4538. $x = self::flattenSingleValue($x);
  4539. $y = self::flattenSingleValue($y);
  4540. // Validate parameters
  4541. if ($x < 0) {
  4542. return self::$_errorCodes['num'];
  4543. }
  4544. if ($x == 0 && $y <= 0) {
  4545. return self::$_errorCodes['divisionbyzero'];
  4546. }
  4547. // Return
  4548. return pow($x, $y);
  4549. }
  4550. /**
  4551. * BINTODEC
  4552. *
  4553. * Return a binary value as Decimal.
  4554. *
  4555. * @param string $x
  4556. * @return string
  4557. */
  4558. public static function BINTODEC($x) {
  4559. $x = self::flattenSingleValue($x);
  4560. if (is_bool($x)) {
  4561. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4562. $x = (int) $x;
  4563. } else {
  4564. return self::$_errorCodes['value'];
  4565. }
  4566. }
  4567. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4568. $x = floor($x);
  4569. }
  4570. $x = (string) $x;
  4571. if (strlen($x) > preg_match_all('/[01]/',$x,$out)) {
  4572. return self::$_errorCodes['num'];
  4573. }
  4574. if (strlen($x) > 10) {
  4575. return self::$_errorCodes['num'];
  4576. } elseif (strlen($x) == 10) {
  4577. // Two's Complement
  4578. $x = substr($x,-9);
  4579. return '-'.(512-bindec($x));
  4580. }
  4581. return bindec($x);
  4582. }
  4583. /**
  4584. * BINTOHEX
  4585. *
  4586. * Return a binary value as Hex.
  4587. *
  4588. * @param string $x
  4589. * @return string
  4590. */
  4591. public static function BINTOHEX($x) {
  4592. $x = floor(self::flattenSingleValue($x));
  4593. if (is_bool($x)) {
  4594. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4595. $x = (int) $x;
  4596. } else {
  4597. return self::$_errorCodes['value'];
  4598. }
  4599. }
  4600. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4601. $x = floor($x);
  4602. }
  4603. $x = (string) $x;
  4604. if (strlen($x) > preg_match_all('/[01]/',$x,$out)) {
  4605. return self::$_errorCodes['num'];
  4606. }
  4607. if (strlen($x) > 10) {
  4608. return self::$_errorCodes['num'];
  4609. } elseif (strlen($x) == 10) {
  4610. // Two's Complement
  4611. return str_repeat('F',8).substr(strtoupper(dechex(bindec(substr($x,-9)))),-2);
  4612. }
  4613. return strtoupper(dechex(bindec($x)));
  4614. }
  4615. /**
  4616. * BINTOOCT
  4617. *
  4618. * Return a binary value as Octal.
  4619. *
  4620. * @param string $x
  4621. * @return string
  4622. */
  4623. public static function BINTOOCT($x) {
  4624. $x = floor(self::flattenSingleValue($x));
  4625. if (is_bool($x)) {
  4626. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4627. $x = (int) $x;
  4628. } else {
  4629. return self::$_errorCodes['value'];
  4630. }
  4631. }
  4632. if (self::$compatibilityMode == self::COMPATIBILITY_GNUMERIC) {
  4633. $x = floor($x);
  4634. }
  4635. $x = (string) $x;
  4636. if (strlen($x) > preg_match_all('/[01]/',$x,$out)) {
  4637. return self::$_errorCodes['num'];
  4638. }
  4639. if (strlen($x) > 10) {
  4640. return self::$_errorCodes['num'];
  4641. } elseif (strlen($x) == 10) {
  4642. // Two's Complement
  4643. return str_repeat('7',7).substr(strtoupper(dechex(bindec(substr($x,-9)))),-3);
  4644. }
  4645. return decoct(bindec($x));
  4646. }
  4647. /**
  4648. * DECTOBIN
  4649. *
  4650. * Return an octal value as binary.
  4651. *
  4652. * @param string $x
  4653. * @return string
  4654. */
  4655. public static function DECTOBIN($x) {
  4656. $x = self::flattenSingleValue($x);
  4657. if (is_bool($x)) {
  4658. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4659. $x = (int) $x;
  4660. } else {
  4661. return self::$_errorCodes['value'];
  4662. }
  4663. }
  4664. $x = (string) $x;
  4665. if (strlen($x) > preg_match_all('/[-0123456789.]/',$x,$out)) {
  4666. return self::$_errorCodes['value'];
  4667. }
  4668. $x = (string) floor($x);
  4669. $r = decbin($x);
  4670. if (strlen($r) == 32) {
  4671. // Two's Complement
  4672. $r = substr($r,-10);
  4673. } elseif (strlen($r) > 11) {
  4674. return self::$_errorCodes['num'];
  4675. }
  4676. return $r;
  4677. }
  4678. /**
  4679. * DECTOOCT
  4680. *
  4681. * Return an octal value as binary.
  4682. *
  4683. * @param string $x
  4684. * @return string
  4685. */
  4686. public static function DECTOOCT($x) {
  4687. $x = self::flattenSingleValue($x);
  4688. if (is_bool($x)) {
  4689. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4690. $x = (int) $x;
  4691. } else {
  4692. return self::$_errorCodes['value'];
  4693. }
  4694. }
  4695. $x = (string) $x;
  4696. if (strlen($x) > preg_match_all('/[-0123456789.]/',$x,$out)) {
  4697. return self::$_errorCodes['value'];
  4698. }
  4699. $x = (string) floor($x);
  4700. $r = decoct($x);
  4701. if (strlen($r) == 11) {
  4702. // Two's Complement
  4703. $r = substr($r,-10);
  4704. }
  4705. return ($r);
  4706. }
  4707. /**
  4708. * DECTOHEX
  4709. *
  4710. * Return an octal value as binary.
  4711. *
  4712. * @param string $x
  4713. * @return string
  4714. */
  4715. public static function DECTOHEX($x) {
  4716. $x = self::flattenSingleValue($x);
  4717. if (is_bool($x)) {
  4718. if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
  4719. $x = (int) $x;
  4720. } else {
  4721. return self::$_errorCodes['value'];
  4722. }
  4723. }
  4724. $x = (string) $x;
  4725. if (strlen($x) > preg_match_all('/[-0123456789.]/',$x,$out)) {
  4726. return self::$_errorCodes['value'];
  4727. }
  4728. $x = (string) floor($x);
  4729. $r = strtoupper(dechex($x));
  4730. if (strlen($r) == 8) {
  4731. // Two's Complement
  4732. $r = 'FF'.$r;
  4733. }
  4734. return ($r);
  4735. }
  4736. /**
  4737. * HEXTOBIN
  4738. *
  4739. * Return a hex value as binary.
  4740. *
  4741. * @param string $x
  4742. * @return string
  4743. */
  4744. public static function HEXTOBIN($x) {
  4745. $x = self::flattenSingleValue($x);
  4746. if (is_bool($x)) {
  4747. return self::$_errorCodes['value'];
  4748. }
  4749. $x = (string) $x;
  4750. if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/',strtoupper($x),$out)) {
  4751. return self::$_errorCodes['num'];
  4752. }
  4753. return decbin(hexdec($x));
  4754. }
  4755. /**
  4756. * HEXTOOCT
  4757. *
  4758. * Return a hex value as octal.
  4759. *
  4760. * @param string $x
  4761. * @return string
  4762. */
  4763. public static function HEXTOOCT($x) {
  4764. $x = self::flattenSingleValue($x);
  4765. if (is_bool($x)) {
  4766. return self::$_errorCodes['value'];
  4767. }
  4768. $x = (string) $x;
  4769. if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/',strtoupper($x),$out)) {
  4770. return self::$_errorCodes['num'];
  4771. }
  4772. return decoct(hexdec($x));
  4773. }
  4774. /**
  4775. * HEXTODEC
  4776. *
  4777. * Return a hex value as octal.
  4778. *
  4779. * @param string $x
  4780. * @return string
  4781. */
  4782. public static function HEXTODEC($x) {
  4783. $x = self::flattenSingleValue($x);
  4784. if (is_bool($x)) {
  4785. return self::$_errorCodes['value'];
  4786. }
  4787. $x = (string) $x;
  4788. if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/',strtoupper($x),$out)) {
  4789. return self::$_errorCodes['num'];
  4790. }
  4791. return hexdec($x);
  4792. }
  4793. /**
  4794. * OCTTOBIN
  4795. *
  4796. * Return an octal value as binary.
  4797. *
  4798. * @param string $x
  4799. * @return string
  4800. */
  4801. public static function OCTTOBIN($x) {
  4802. $x = self::flattenSingleValue($x);
  4803. if (is_bool($x)) {
  4804. return self::$_errorCodes['value'];
  4805. }
  4806. $x = (string) $x;
  4807. if (preg_match_all('/[01234567]/',$x,$out) != strlen($x)) {
  4808. return self::$_errorCodes['num'];
  4809. }
  4810. return decbin(octdec($x));
  4811. }
  4812. /**
  4813. * OCTTODEC
  4814. *
  4815. * Return an octal value as binary.
  4816. *
  4817. * @param string $x
  4818. * @return string
  4819. */
  4820. public static function OCTTODEC($x) {
  4821. $x = self::flattenSingleValue($x);
  4822. if (is_bool($x)) {
  4823. return self::$_errorCodes['value'];
  4824. }
  4825. $x = (string) $x;
  4826. if (preg_match_all('/[01234567]/',$x,$out) != strlen($x)) {
  4827. return self::$_errorCodes['num'];
  4828. }
  4829. return octdec($x);
  4830. }
  4831. /**
  4832. * OCTTOHEX
  4833. *
  4834. * Return an octal value as hex.
  4835. *
  4836. * @param string $x
  4837. * @return string
  4838. */
  4839. public static function OCTTOHEX($x) {
  4840. $x = self::flattenSingleValue($x);
  4841. if (is_bool($x)) {
  4842. return self::$_errorCodes['value'];
  4843. }
  4844. $x = (string) $x;
  4845. if (preg_match_all('/[01234567]/',$x,$out) != strlen($x)) {
  4846. return self::$_errorCodes['num'];
  4847. }
  4848. return strtoupper(dechex(octdec($x)));
  4849. }
  4850. public function parseComplex($complexNumber) {
  4851. $workString = $complexNumber;
  4852. $realNumber = $imaginary = 0;
  4853. // Extract the suffix, if there is one
  4854. $suffix = substr($workString,-1);
  4855. if (!is_numeric($suffix)) {
  4856. $workString = substr($workString,0,-1);
  4857. } else {
  4858. $suffix = '';
  4859. }
  4860. // Split the input into its Real and Imaginary components
  4861. $leadingSign = (($workString{0} == '+') || ($workString{0} == '-')) ? 1 : 0;
  4862. $power = '';
  4863. $realNumber = strtok($workString, '+-');
  4864. if (strtoupper(substr($realNumber,-1)) == 'E') {
  4865. $power = strtok('+-');
  4866. $leadingSign++;
  4867. }
  4868. $realNumber = substr($workString,0,strlen($realNumber)+strlen($power)+$leadingSign);
  4869. if ($suffix != '') {
  4870. $imaginary = substr($workString,strlen($realNumber));
  4871. if (($imaginary == '') && (($realNumber == '') || ($realNumber == '+') || ($realNumber == '-'))) {
  4872. $imaginary = $realNumber.'1';
  4873. $realNumber = '0';
  4874. } else if ($imaginary == '') {
  4875. $imaginary = $realNumber;
  4876. $realNumber = '0';
  4877. } elseif (($imaginary == '+') || ($imaginary == '-')) {
  4878. $imaginary .= '1';
  4879. }
  4880. }
  4881. $complexArray = array( 'real' => $realNumber,
  4882. 'imaginary' => $imaginary,
  4883. 'suffix' => $suffix
  4884. );
  4885. return $complexArray;
  4886. }
  4887. /**
  4888. * COMPLEX
  4889. *
  4890. * returns a complex number of the form x + yi or x + yj.
  4891. *
  4892. * @param float $realNumber
  4893. * @param float $imaginary
  4894. * @param string $suffix
  4895. * @return string
  4896. */
  4897. public static function COMPLEX($realNumber=0.0, $imaginary=0.0, $suffix='i') {
  4898. $realNumber = self::flattenSingleValue($realNumber);
  4899. $imaginary = self::flattenSingleValue($imaginary);
  4900. $suffix = self::flattenSingleValue($suffix);
  4901. if (((is_numeric($realNumber)) && (is_numeric($imaginary))) &&
  4902. (($suffix == 'i') || ($suffix == 'j'))) {
  4903. if ($realNumber == 0.0) {
  4904. if ($imaginary == 0.0) {
  4905. return (string) '0';
  4906. } elseif ($imaginary == 1.0) {
  4907. return (string) $suffix;
  4908. } elseif ($imaginary == -1.0) {
  4909. return (string) '-'.$suffix;
  4910. }
  4911. return (string) $imaginary.$suffix;
  4912. } elseif ($imaginary == 0.0) {
  4913. return (string) $realNumber;
  4914. } elseif ($imaginary == 1.0) {
  4915. return (string) $realNumber.'+'.$suffix;
  4916. } elseif ($imaginary == -1.0) {
  4917. return (string) $realNumber.'-'.$suffix;
  4918. }
  4919. if ($imaginary > 0) { $imaginary = (string) '+'.$imaginary; }
  4920. return (string) $realNumber.$imaginary.$suffix;
  4921. }
  4922. return self::$_errorCodes['value'];
  4923. }
  4924. /**
  4925. * IMAGINARY
  4926. *
  4927. * Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.
  4928. *
  4929. * @param string $complexNumber
  4930. * @return real
  4931. */
  4932. public static function IMAGINARY($complexNumber) {
  4933. $complexNumber = self::flattenSingleValue($complexNumber);
  4934. $parsedComplex = self::parseComplex($complexNumber);
  4935. if (!is_array($parsedComplex)) {
  4936. return $parsedComplex;
  4937. }
  4938. return $parsedComplex['imaginary'];
  4939. }
  4940. /**
  4941. * IMREAL
  4942. *
  4943. * Returns the real coefficient of a complex number in x + yi or x + yj text format.
  4944. *
  4945. * @param string $complexNumber
  4946. * @return real
  4947. */
  4948. public static function IMREAL($complexNumber) {
  4949. $complexNumber = self::flattenSingleValue($complexNumber);
  4950. $parsedComplex = self::parseComplex($complexNumber);
  4951. if (!is_array($parsedComplex)) {
  4952. return $parsedComplex;
  4953. }
  4954. return $parsedComplex['real'];
  4955. }
  4956. /**
  4957. * IMABS
  4958. *
  4959. * Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.
  4960. *
  4961. * @param string $complexNumber
  4962. * @return real
  4963. */
  4964. public static function IMABS($complexNumber) {
  4965. $complexNumber = self::flattenSingleValue($complexNumber);
  4966. $parsedComplex = self::parseComplex($complexNumber);
  4967. if (!is_array($parsedComplex)) {
  4968. return $parsedComplex;
  4969. }
  4970. return sqrt(($parsedComplex['real'] * $parsedComplex['real']) + ($parsedComplex['imaginary'] * $parsedComplex['imaginary']));
  4971. }
  4972. /**
  4973. * IMARGUMENT
  4974. *
  4975. * Returns the argument theta of a complex number, i.e. the angle in radians from the real axis to the representation of the number in polar coordinates.
  4976. *
  4977. * @param string $complexNumber
  4978. * @return string
  4979. */
  4980. public static function IMARGUMENT($complexNumber) {
  4981. $complexNumber = self::flattenSingleValue($complexNumber);
  4982. $parsedComplex = self::parseComplex($complexNumber);
  4983. if (!is_array($parsedComplex)) {
  4984. return $parsedComplex;
  4985. }
  4986. if ($parsedComplex['real'] == 0.0) {
  4987. if ($parsedComplex['imaginary'] == 0.0) {
  4988. return 0.0;
  4989. } elseif($parsedComplex['imaginary'] < 0.0) {
  4990. return pi() / -2;
  4991. } else {
  4992. return pi() / 2;
  4993. }
  4994. } elseif ($parsedComplex['real'] > 0.0) {
  4995. return atan($parsedComplex['imaginary'] / $parsedComplex['real']);
  4996. } elseif ($parsedComplex['imaginary'] < 0.0) {
  4997. return 0 - (pi() - atan(abs($parsedComplex['imaginary']) / abs($parsedComplex['real'])));
  4998. } else {
  4999. return pi() - atan($parsedComplex['imaginary'] / abs($parsedComplex['real']));
  5000. }
  5001. }
  5002. /**
  5003. * IMCONJUGATE
  5004. *
  5005. * Returns the complex conjugate of a complex number in x + yi or x + yj text format.
  5006. *
  5007. * @param string $complexNumber
  5008. * @return string
  5009. */
  5010. public static function IMCONJUGATE($complexNumber) {
  5011. $complexNumber = self::flattenSingleValue($complexNumber);
  5012. $parsedComplex = self::parseComplex($complexNumber);
  5013. if (!is_array($parsedComplex)) {
  5014. return $parsedComplex;
  5015. }
  5016. if ($parsedComplex['imaginary'] == 0.0) {
  5017. return $parsedComplex['real'];
  5018. } else {
  5019. return self::COMPLEX($parsedComplex['real'], 0 - $parsedComplex['imaginary'], $parsedComplex['suffix']);
  5020. }
  5021. }
  5022. /**
  5023. * IMCOS
  5024. *
  5025. * Returns the cosine of a complex number in x + yi or x + yj text format.
  5026. *
  5027. * @param string $complexNumber
  5028. * @return string
  5029. */
  5030. public static function IMCOS($complexNumber) {
  5031. $complexNumber = self::flattenSingleValue($complexNumber);
  5032. $parsedComplex = self::parseComplex($complexNumber);
  5033. if (!is_array($parsedComplex)) {
  5034. return $parsedComplex;
  5035. }
  5036. if ($parsedComplex['imaginary'] == 0.0) {
  5037. return cos($parsedComplex['real']);
  5038. } else {
  5039. return self::IMCONJUGATE(self::COMPLEX(cos($parsedComplex['real']) * cosh($parsedComplex['imaginary']),sin($parsedComplex['real']) * sinh($parsedComplex['imaginary']),$parsedComplex['suffix']));
  5040. }
  5041. }
  5042. /**
  5043. * IMSIN
  5044. *
  5045. * Returns the sine of a complex number in x + yi or x + yj text format.
  5046. *
  5047. * @param string $complexNumber
  5048. * @return string
  5049. */
  5050. public static function IMSIN($complexNumber) {
  5051. $complexNumber = self::flattenSingleValue($complexNumber);
  5052. $parsedComplex = self::parseComplex($complexNumber);
  5053. if (!is_array($parsedComplex)) {
  5054. return $parsedComplex;
  5055. }
  5056. if ($parsedComplex['imaginary'] == 0.0) {
  5057. return sin($parsedComplex['real']);
  5058. } else {
  5059. return self::COMPLEX(sin($parsedComplex['real']) * cosh($parsedComplex['imaginary']),cos($parsedComplex['real']) * sinh($parsedComplex['imaginary']),$parsedComplex['suffix']);
  5060. }
  5061. }
  5062. /**
  5063. * IMSQRT
  5064. *
  5065. * Returns the square root of a complex number in x + yi or x + yj text format.
  5066. *
  5067. * @param string $complexNumber
  5068. * @return string
  5069. */
  5070. public static function IMSQRT($complexNumber) {
  5071. $complexNumber = self::flattenSingleValue($complexNumber);
  5072. $parsedComplex = self::parseComplex($complexNumber);
  5073. if (!is_array($parsedComplex)) {
  5074. return $parsedComplex;
  5075. }
  5076. $theta = self::IMARGUMENT($complexNumber);
  5077. $d1 = cos($theta / 2);
  5078. $d2 = sin($theta / 2);
  5079. $r = sqrt(sqrt(($parsedComplex['real'] * $parsedComplex['real']) + ($parsedComplex['imaginary'] * $parsedComplex['imaginary'])));
  5080. if ($parsedComplex['suffix'] == '') {
  5081. return self::COMPLEX($d1 * $r,$d2 * $r);
  5082. } else {
  5083. return self::COMPLEX($d1 * $r,$d2 * $r,$parsedComplex['suffix']);
  5084. }
  5085. }
  5086. /**
  5087. * IMLN
  5088. *
  5089. * Returns the natural logarithm of a complex number in x + yi or x + yj text format.
  5090. *
  5091. * @param string $complexNumber
  5092. * @return string
  5093. */
  5094. public static function IMLN($complexNumber) {
  5095. $complexNumber = self::flattenSingleValue($complexNumber);
  5096. $parsedComplex = self::parseComplex($complexNumber);
  5097. if (!is_array($parsedComplex)) {
  5098. return $parsedComplex;
  5099. }
  5100. if (($parsedComplex['real'] == 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5101. return self::$_errorCodes['num'];
  5102. }
  5103. $logR = log(sqrt(($parsedComplex['real'] * $parsedComplex['real']) + ($parsedComplex['imaginary'] * $parsedComplex['imaginary'])));
  5104. $t = self::IMARGUMENT($complexNumber);
  5105. if ($parsedComplex['suffix'] == '') {
  5106. return self::COMPLEX($logR,$t);
  5107. } else {
  5108. return self::COMPLEX($logR,$t,$parsedComplex['suffix']);
  5109. }
  5110. }
  5111. /**
  5112. * IMLOG10
  5113. *
  5114. * Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format.
  5115. *
  5116. * @param string $complexNumber
  5117. * @return string
  5118. */
  5119. public static function IMLOG10($complexNumber) {
  5120. $complexNumber = self::flattenSingleValue($complexNumber);
  5121. $parsedComplex = self::parseComplex($complexNumber);
  5122. if (!is_array($parsedComplex)) {
  5123. return $parsedComplex;
  5124. }
  5125. if (($parsedComplex['real'] == 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5126. return self::$_errorCodes['num'];
  5127. } elseif (($parsedComplex['real'] > 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5128. return log10($parsedComplex['real']);
  5129. }
  5130. return self::IMPRODUCT(log10(EULER),self::IMLN($complexNumber));
  5131. }
  5132. /**
  5133. * IMLOG2
  5134. *
  5135. * Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format.
  5136. *
  5137. * @param string $complexNumber
  5138. * @return string
  5139. */
  5140. public static function IMLOG2($complexNumber) {
  5141. $complexNumber = self::flattenSingleValue($complexNumber);
  5142. $parsedComplex = self::parseComplex($complexNumber);
  5143. if (!is_array($parsedComplex)) {
  5144. return $parsedComplex;
  5145. }
  5146. if (($parsedComplex['real'] == 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5147. return self::$_errorCodes['num'];
  5148. } elseif (($parsedComplex['real'] > 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5149. return log($parsedComplex['real'],2);
  5150. }
  5151. return self::IMPRODUCT(log(EULER,2),self::IMLN($complexNumber));
  5152. }
  5153. /**
  5154. * IMEXP
  5155. *
  5156. * Returns the exponential of a complex number in x + yi or x + yj text format.
  5157. *
  5158. * @param string $complexNumber
  5159. * @return string
  5160. */
  5161. public static function IMEXP($complexNumber) {
  5162. $complexNumber = self::flattenSingleValue($complexNumber);
  5163. $parsedComplex = self::parseComplex($complexNumber);
  5164. if (!is_array($parsedComplex)) {
  5165. return $parsedComplex;
  5166. }
  5167. if (($parsedComplex['real'] == 0.0) && ($parsedComplex['imaginary'] == 0.0)) {
  5168. return '1';
  5169. }
  5170. $e = exp($parsedComplex['real']);
  5171. $eX = $e * cos($parsedComplex['imaginary']);
  5172. $eY = $e * sin($parsedComplex['imaginary']);
  5173. if ($parsedComplex['suffix'] == '') {
  5174. return self::COMPLEX($eX,$eY);
  5175. } else {
  5176. return self::COMPLEX($eX,$eY,$parsedComplex['suffix']);
  5177. }
  5178. }
  5179. /**
  5180. * IMPOWER
  5181. *
  5182. * Returns a complex number in x + yi or x + yj text format raised to a power.
  5183. *
  5184. * @param string $complexNumber
  5185. * @return string
  5186. */
  5187. public static function IMPOWER($complexNumber,$realNumber) {
  5188. $complexNumber = self::flattenSingleValue($complexNumber);
  5189. $realNumber = self::flattenSingleValue($realNumber);
  5190. if (!is_numeric($realNumber)) {
  5191. return self::$_errorCodes['value'];
  5192. }
  5193. $parsedComplex = self::parseComplex($complexNumber);
  5194. if (!is_array($parsedComplex)) {
  5195. return $parsedComplex;
  5196. }
  5197. $r = sqrt(($parsedComplex['real'] * $parsedComplex['real']) + ($parsedComplex['imaginary'] * $parsedComplex['imaginary']));
  5198. $rPower = pow($r,$realNumber);
  5199. $theta = self::IMARGUMENT($complexNumber) * $realNumber;
  5200. if ($parsedComplex['imaginary'] == 0.0) {
  5201. return self::COMPLEX($rPower * cos($theta),$rPower * sin($theta),$parsedComplex['suffix']);
  5202. } else {
  5203. return self::COMPLEX($rPower * cos($theta),$rPower * sin($theta),$parsedComplex['suffix']);
  5204. }
  5205. }
  5206. /**
  5207. * IMDIV
  5208. *
  5209. * Returns the quotient of two complex numbers in x + yi or x + yj text format.
  5210. *
  5211. * @param string $complexDividend
  5212. * @param string $complexDivisor
  5213. * @return real
  5214. */
  5215. public static function IMDIV($complexDividend,$complexDivisor) {
  5216. $complexDividend = self::flattenSingleValue($complexDividend);
  5217. $complexDivisor = self::flattenSingleValue($complexDivisor);
  5218. $parsedComplexDividend = self::parseComplex($complexDividend);
  5219. if (!is_array($parsedComplexDividend)) {
  5220. return $parsedComplexDividend;
  5221. }
  5222. $parsedComplexDivisor = self::parseComplex($complexDivisor);
  5223. if (!is_array($parsedComplexDivisor)) {
  5224. return $parsedComplexDividend;
  5225. }
  5226. if ($parsedComplexDividend['suffix'] != $parsedComplexDivisor['suffix']) {
  5227. return self::$_errorCodes['num'];
  5228. }
  5229. $d1 = ($parsedComplexDividend['real'] * $parsedComplexDivisor['real']) + ($parsedComplexDividend['imaginary'] * $parsedComplexDivisor['imaginary']);
  5230. $d2 = ($parsedComplexDividend['imaginary'] * $parsedComplexDivisor['real']) - ($parsedComplexDividend['real'] * $parsedComplexDivisor['imaginary']);
  5231. $d3 = ($parsedComplexDivisor['real'] * $parsedComplexDivisor['real']) + ($parsedComplexDivisor['imaginary'] * $parsedComplexDivisor['imaginary']);
  5232. return $d1/$d3.$d2/$d3.$parsedComplexDivisor['suffix'];
  5233. }
  5234. /**
  5235. * IMSUB
  5236. *
  5237. * Returns the difference of two complex numbers in x + yi or x + yj text format.
  5238. *
  5239. * @param string $complexNumber1
  5240. * @param string $complexNumber2
  5241. * @return real
  5242. */
  5243. public static function IMSUB($complexNumber1,$complexNumber2) {
  5244. $complexNumber1 = self::flattenSingleValue($complexNumber1);
  5245. $complexNumber2 = self::flattenSingleValue($complexNumber2);
  5246. $parsedComplex1 = self::parseComplex($complexNumber1);
  5247. if (!is_array($parsedComplex1)) {
  5248. return $parsedComplex1;
  5249. }
  5250. $parsedComplex2 = self::parseComplex($complexNumber2);
  5251. if (!is_array($parsedComplex2)) {
  5252. return $parsedComplex2;
  5253. }
  5254. if ($parsedComplex1['suffix'] != $parsedComplex2['suffix']) {
  5255. return self::$_errorCodes['num'];
  5256. }
  5257. $d1 = $parsedComplex1['real'] - $parsedComplex2['real'];
  5258. $d2 = $parsedComplex1['imaginary'] - $parsedComplex2['imaginary'];
  5259. return self::COMPLEX($d1,$d2,$parsedComplex1['suffix']);
  5260. }
  5261. /**
  5262. * IMSUM
  5263. *
  5264. * Returns the sum of two or more complex numbers in x + yi or x + yj text format.
  5265. *
  5266. * @param array of mixed Data Series
  5267. * @return real
  5268. */
  5269. public static function IMSUM() {
  5270. // Return value
  5271. $returnValue = self::parseComplex('0');
  5272. $activeSuffix = '';
  5273. // Loop through the arguments
  5274. $aArgs = self::flattenArray(func_get_args());
  5275. foreach ($aArgs as $arg) {
  5276. $parsedComplex = self::parseComplex($arg);
  5277. if (!is_array($parsedComplex)) {
  5278. return $parsedComplex;
  5279. }
  5280. if ($activeSuffix == '') {
  5281. $activeSuffix = $parsedComplex['suffix'];
  5282. } elseif ($activeSuffix != $parsedComplex['suffix']) {
  5283. return self::$_errorCodes['num'];
  5284. }
  5285. $returnValue['real'] += $parsedComplex['real'];
  5286. $returnValue['imaginary'] += $parsedComplex['imaginary'];
  5287. }
  5288. if ($returnValue['imaginary'] == 0.0) { $activeSuffix = ''; }
  5289. return self::COMPLEX($returnValue['real'],$returnValue['imaginary'],$activeSuffix);
  5290. }
  5291. /**
  5292. * IMPRODUCT
  5293. *
  5294. * Returns the product of two or more complex numbers in x + yi or x + yj text format.
  5295. *
  5296. * @param array of mixed Data Series
  5297. * @return real
  5298. */
  5299. public static function IMPRODUCT() {
  5300. // Return value
  5301. $returnValue = self::parseComplex('1');
  5302. $activeSuffix = '';
  5303. // Loop through the arguments
  5304. $aArgs = self::flattenArray(func_get_args());
  5305. foreach ($aArgs as $arg) {
  5306. $parsedComplex = self::parseComplex($arg);
  5307. if (!is_array($parsedComplex)) {
  5308. return $parsedComplex;
  5309. }
  5310. $workValue = $returnValue;
  5311. if (($parsedComplex['suffix'] != '') && ($activeSuffix == '')) {
  5312. $activeSuffix = $parsedComplex['suffix'];
  5313. } elseif (($parsedComplex['suffix'] != '') && ($activeSuffix != $parsedComplex['suffix'])) {
  5314. return self::$_errorCodes['num'];
  5315. }
  5316. $returnValue['real'] = ($workValue['real'] * $parsedComplex['real']) - ($workValue['imaginary'] * $parsedComplex['imaginary']);
  5317. $returnValue['imaginary'] = ($workValue['real'] * $parsedComplex['imaginary']) + ($workValue['imaginary'] * $parsedComplex['real']);
  5318. }
  5319. if ($returnValue['imaginary'] == 0.0) { $activeSuffix = ''; }
  5320. return self::COMPLEX($returnValue['real'],$returnValue['imaginary'],$activeSuffix);
  5321. }
  5322. /**
  5323. * BESSELI
  5324. *
  5325. * Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments
  5326. *
  5327. * @param float $x
  5328. * @param float $n
  5329. * @return int
  5330. */
  5331. public static function BESSELI($x, $n) {
  5332. $x = self::flattenSingleValue($x);
  5333. $n = floor(self::flattenSingleValue($n));
  5334. if ((is_numeric($x)) && (is_numeric($n))) {
  5335. if ($n < 0) {
  5336. return self::$_errorCodes['num'];
  5337. }
  5338. $f_2_PI = 2 * pi();
  5339. if (abs($x) <= 30) {
  5340. $fTerm = pow($x / 2, $n) / self::FACT($n);
  5341. $nK = 1;
  5342. $fResult = $fTerm;
  5343. $fSqrX = pow($x,2) / 4;
  5344. do {
  5345. $fTerm *= $fSqrX;
  5346. $fTerm /= ($nK * ($nK + $n));
  5347. $fResult += $fTerm;
  5348. } while ((abs($fTerm) > 1e-10) && (++$nK < 100));
  5349. } else {
  5350. $fXAbs = abs($x);
  5351. $fResult = exp($fXAbs) / sqrt($f_2_PI * $fXAbs);
  5352. if (($n && 1) && ($x < 0)) {
  5353. $fResult = -$fResult;
  5354. }
  5355. }
  5356. return $fResult;
  5357. }
  5358. return self::$_errorCodes['value'];
  5359. }
  5360. /**
  5361. * BESSELJ
  5362. *
  5363. * Returns the Bessel function
  5364. *
  5365. * @param float $x
  5366. * @param float $n
  5367. * @return int
  5368. */
  5369. public static function BESSELJ($x, $n) {
  5370. $x = self::flattenSingleValue($x);
  5371. $n = floor(self::flattenSingleValue($n));
  5372. if ((is_numeric($x)) && (is_numeric($n))) {
  5373. if ($n < 0) {
  5374. return self::$_errorCodes['num'];
  5375. }
  5376. $f_2_DIV_PI = 2 / pi();
  5377. $f_PI_DIV_2 = pi() / 2;
  5378. $f_PI_DIV_4 = pi() / 4;
  5379. $fResult = 0;
  5380. if (abs($x) <= 30) {
  5381. $fTerm = pow($x / 2, $n) / self::FACT($n);
  5382. $nK = 1;
  5383. $fResult = $fTerm;
  5384. $fSqrX = pow($x,2) / -4;
  5385. do {
  5386. $fTerm *= $fSqrX;
  5387. $fTerm /= ($nK * ($nK + $n));
  5388. $fResult += $fTerm;
  5389. } while ((abs($fTerm) > 1e-10) && (++$nK < 100));
  5390. } else {
  5391. $fXAbs = abs($x);
  5392. $fResult = sqrt($f_2_DIV_PI / $fXAbs) * cos($fXAbs - $n * $f_PI_DIV_2 - $f_PI_DIV_4);
  5393. if (($n && 1) && ($x < 0)) {
  5394. $fResult = -$fResult;
  5395. }
  5396. }
  5397. return $fResult;
  5398. }
  5399. return self::$_errorCodes['value'];
  5400. }
  5401. private static function Besselk0($fNum) {
  5402. if ($fNum <= 2) {
  5403. $fNum2 = $fNum * 0.5;
  5404. $y = pow($fNum2,2);
  5405. $fRet = -log($fNum2) * self::BESSELI($fNum, 0) +
  5406. (-0.57721566 + $y * (0.42278420 + $y * (0.23069756 + $y * (0.3488590e-1 + $y * (0.262698e-2 + $y *
  5407. (0.10750e-3 + $y * 0.74e-5))))));
  5408. } else {
  5409. $y = 2 / $fNum;
  5410. $fRet = exp(-$fNum) / sqrt($fNum) *
  5411. (1.25331414 + $y * (-0.7832358e-1 + $y * (0.2189568e-1 + $y * (-0.1062446e-1 + $y *
  5412. (0.587872e-2 + $y * (-0.251540e-2 + $y * 0.53208e-3))))));
  5413. }
  5414. return $fRet;
  5415. }
  5416. private static function Besselk1($fNum) {
  5417. if ($fNum <= 2) {
  5418. $fNum2 = $fNum * 0.5;
  5419. $y = pow($fNum2,2);
  5420. $fRet = log($fNum2) * self::BESSELI($fNum, 1) +
  5421. (1 + $y * (0.15443144 + $y * (-0.67278579 + $y * (-0.18156897 + $y * (-0.1919402e-1 + $y *
  5422. (-0.110404e-2 + $y * (-0.4686e-4))))))) / $fNum;
  5423. } else {
  5424. $y = 2 / $fNum;
  5425. $fRet = exp(-$fNum) / sqrt($fNum) *
  5426. (1.25331414 + $y * (0.23498619 + $y * (-0.3655620e-1 + $y * (0.1504268e-1 + $y * (-0.780353e-2 + $y *
  5427. (0.325614e-2 + $y * (-0.68245e-3)))))));
  5428. }
  5429. return $fRet;
  5430. }
  5431. /**
  5432. * BESSELK
  5433. *
  5434. * Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments.
  5435. *
  5436. * @param float $x
  5437. * @param float $n
  5438. * @return int
  5439. */
  5440. public static function BESSELK($x, $ord) {
  5441. $x = self::flattenSingleValue($x);
  5442. $n = floor(self::flattenSingleValue($ord));
  5443. if ((is_numeric($x)) && (is_numeric($ord))) {
  5444. if ($ord < 0) {
  5445. return self::$_errorCodes['num'];
  5446. }
  5447. switch($ord) {
  5448. case 0 : return self::Besselk0($x);
  5449. break;
  5450. case 1 : return self::Besselk1($x);
  5451. break;
  5452. default : $fTox = 2 / $x;
  5453. $fBkm = self::Besselk0($x);
  5454. $fBk = self::Besselk1($x);
  5455. for ($n = 1; $n < $ord; $n++) {
  5456. $fBkp = $fBkm + $n * $fTox * $fBk;
  5457. $fBkm = $fBk;
  5458. $fBk = $fBkp;
  5459. }
  5460. }
  5461. return $fBk;
  5462. }
  5463. return self::$_errorCodes['value'];
  5464. }
  5465. private static function Bessely0($fNum) {
  5466. if ($fNum < 8) {
  5467. $y = pow($fNum,2);
  5468. $f1 = -2957821389.0 + $y * (7062834065.0 + $y * (-512359803.6 + $y * (10879881.29 + $y * (-86327.92757 + $y * 228.4622733))));
  5469. $f2 = 40076544269.0 + $y * (745249964.8 + $y * (7189466.438 + $y * (47447.26470 + $y * (226.1030244 + $y))));
  5470. $fRet = $f1 / $f2 + 0.636619772 * self::BESSELJ($fNum, 0) * log($fNum);
  5471. } else {
  5472. $z = 8 / $fNum;
  5473. $y = pow($z,2);
  5474. $xx = $fNum - 0.785398164;
  5475. $f1 = 1 + $y * (-0.1098628627e-2 + $y * (0.2734510407e-4 + $y * (-0.2073370639e-5 + $y * 0.2093887211e-6)));
  5476. $f2 = -0.1562499995e-1 + $y * (0.1430488765e-3 + $y * (-0.6911147651e-5 + $y * (0.7621095161e-6 + $y * (-0.934945152e-7))));
  5477. $fRet = sqrt(0.636619772 / $fNum) * (sin($xx) * $f1 + $z * cos($xx) * $f2);
  5478. }
  5479. return $fRet;
  5480. }
  5481. private static function Bessely1($fNum) {
  5482. if ($fNum < 8) {
  5483. $y = pow($fNum,2);
  5484. $f1 = $fNum * (-0.4900604943e13 + $y * (0.1275274390e13 + $y * (-0.5153438139e11 + $y * (0.7349264551e9 + $y *
  5485. (-0.4237922726e7 + $y * 0.8511937935e4)))));
  5486. $f2 = 0.2499580570e14 + $y * (0.4244419664e12 + $y * (0.3733650367e10 + $y * (0.2245904002e8 + $y *
  5487. (0.1020426050e6 + $y * (0.3549632885e3 + $y)))));
  5488. $fRet = $f1 / $f2 + 0.636619772 * ( self::BESSELJ($fNum, 1) * log($fNum) - 1 / $fNum);
  5489. } else {
  5490. $z = 8 / $fNum;
  5491. $y = $z * $z;
  5492. $xx = $fNum - 2.356194491;
  5493. $f1 = 1 + $y * (0.183105e-2 + $y * (-0.3516396496e-4 + $y * (0.2457520174e-5 + $y * (-0.240337019e6))));
  5494. $f2 = 0.04687499995 + $y * (-0.2002690873e-3 + $y * (0.8449199096e-5 + $y * (-0.88228987e-6 + $y * 0.105787412e-6)));
  5495. $fRet = sqrt(0.636619772 / $fNum) * (sin($xx) * $f1 + $z * cos($xx) * $f2);
  5496. #i12430# ...but this seems to work much better.
  5497. // $fRet = sqrt(0.636619772 / $fNum) * sin($fNum - 2.356194491);
  5498. }
  5499. return $fRet;
  5500. }
  5501. /**
  5502. * BESSELY
  5503. *
  5504. * Returns the Bessel function, which is also called the Weber function or the Neumann function.
  5505. *
  5506. * @param float $x
  5507. * @param float $n
  5508. * @return int
  5509. */
  5510. public static function BESSELY($x, $ord) {
  5511. $x = self::flattenSingleValue($x);
  5512. $n = floor(self::flattenSingleValue($ord));
  5513. if ((is_numeric($x)) && (is_numeric($ord))) {
  5514. if ($ord < 0) {
  5515. return self::$_errorCodes['num'];
  5516. }
  5517. switch($ord) {
  5518. case 0 : return self::Bessely0($x);
  5519. break;
  5520. case 1 : return self::Bessely1($x);
  5521. break;
  5522. default: $fTox = 2 / $x;
  5523. $fBym = self::Bessely0($x);
  5524. $fBy = self::Bessely1($x);
  5525. for ($n = 1; $n < $ord; $n++) {
  5526. $fByp = $n * $fTox * $fBy - $fBym;
  5527. $fBym = $fBy;
  5528. $fBy = $fByp;
  5529. }
  5530. }
  5531. return $fBy;
  5532. }
  5533. return self::$_errorCodes['value'];
  5534. }
  5535. /**
  5536. * DELTA
  5537. *
  5538. * Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise.
  5539. *
  5540. * @param float $a
  5541. * @param float $b
  5542. * @return int
  5543. */
  5544. public static function DELTA($a, $b=0) {
  5545. $a = self::flattenSingleValue($a);
  5546. $b = self::flattenSingleValue($b);
  5547. return (int) ($a == $b);
  5548. }
  5549. /**
  5550. * GESTEP
  5551. *
  5552. * Returns 1 if number = step; returns 0 (zero) otherwise
  5553. *
  5554. * @param float $number
  5555. * @param float $step
  5556. * @return int
  5557. */
  5558. public static function GESTEP($number, $step=0) {
  5559. $number = self::flattenSingleValue($number);
  5560. $step = self::flattenSingleValue($step);
  5561. return (int) ($number >= $step);
  5562. }
  5563. //
  5564. // Private method to calculate the erf value
  5565. //
  5566. private static $two_sqrtpi = 1.128379167095512574;
  5567. private static $rel_error = 1E-15;
  5568. private static function erfVal($x) {
  5569. if (abs($x) > 2.2) {
  5570. return 1 - self::erfcVal($x);
  5571. }
  5572. $sum = $term = $x;
  5573. $xsqr = pow($x,2);
  5574. $j = 1;
  5575. do {
  5576. $term *= $xsqr / $j;
  5577. $sum -= $term / (2 * $j + 1);
  5578. ++$j;
  5579. $term *= $xsqr / $j;
  5580. $sum += $term / (2 * $j + 1);
  5581. ++$j;
  5582. if ($sum == 0) {
  5583. break;
  5584. }
  5585. } while (abs($term / $sum) > self::$rel_error);
  5586. return self::$two_sqrtpi * $sum;
  5587. }
  5588. /**
  5589. * ERF
  5590. *
  5591. * Returns the error function integrated between lower_limit and upper_limit
  5592. *
  5593. * @param float $lower lower bound for integrating ERF
  5594. * @param float $upper upper bound for integrating ERF.
  5595. * If omitted, ERF integrates between zero and lower_limit
  5596. * @return int
  5597. */
  5598. public static function ERF($lower, $upper = 0) {
  5599. $lower = self::flattenSingleValue($lower);
  5600. $upper = self::flattenSingleValue($upper);
  5601. if ((is_numeric($lower)) && (is_numeric($upper))) {
  5602. if (($lower < 0) || ($upper < 0)) {
  5603. return self::$_errorCodes['num'];
  5604. }
  5605. if ($upper > $lower) {
  5606. return self::erfVal($upper) - self::erfVal($lower);
  5607. } else {
  5608. return self::erfVal($lower) - self::erfVal($upper);
  5609. }
  5610. }
  5611. return self::$_errorCodes['value'];
  5612. }
  5613. //
  5614. // Private method to calculate the erfc value
  5615. //
  5616. private static $one_sqrtpi = 0.564189583547756287;
  5617. private static function erfcVal($x) {
  5618. if (abs($x) < 2.2) {
  5619. return 1 - self::erfVal($x);
  5620. }
  5621. if ($x < 0) {
  5622. return 2 - self::erfc(-$x);
  5623. }
  5624. $a = $n = 1;
  5625. $b = $c = $x;
  5626. $d = pow($x,2) + 0.5;
  5627. $q1 = $q2 = $b / $d;
  5628. $t = 0;
  5629. do {
  5630. $t = $a * $n + $b * $x;
  5631. $a = $b;
  5632. $b = $t;
  5633. $t = $c * $n + $d * $x;
  5634. $c = $d;
  5635. $d = $t;
  5636. $n += 0.5;
  5637. $q1 = $q2;
  5638. $q2 = $b / $d;
  5639. } while ((abs($q1 - $q2) / $q2) > self::$rel_error);
  5640. return self::$one_sqrtpi * exp(-$x * $x) * $q2;
  5641. }
  5642. /**
  5643. * ERFC
  5644. *
  5645. * Returns the complementary ERF function integrated between x and infinity
  5646. *
  5647. * @param float $x The lower bound for integrating ERF
  5648. * @return int
  5649. */
  5650. public static function ERFC($x) {
  5651. $x = self::flattenSingleValue($x);
  5652. if (is_numeric($x)) {
  5653. if ($x < 0) {
  5654. return self::$_errorCodes['num'];
  5655. }
  5656. return self::erfcVal($x);
  5657. }
  5658. return self::$_errorCodes['value'];
  5659. }
  5660. /**
  5661. * EFFECT
  5662. *
  5663. * Returns the effective interest rate given the nominal rate and the number of compounding payments per year.
  5664. *
  5665. * @param float $nominal_rate Nominal interest rate
  5666. * @param int $npery Number of compounding payments per year
  5667. * @return float
  5668. */
  5669. public static function EFFECT($nominal_rate = 0, $npery = 0) {
  5670. $nominal_rate = self::flattenSingleValue($$nominal_rate);
  5671. $npery = (int)self::flattenSingleValue($npery);
  5672. // Validate parameters
  5673. if ($$nominal_rate <= 0 || $npery < 1) {
  5674. return self::$_errorCodes['num'];
  5675. }
  5676. return pow((1 + $nominal_rate / $npery), $npery) - 1;
  5677. }
  5678. /**
  5679. * NOMINAL
  5680. *
  5681. * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
  5682. *
  5683. * @param float $effect_rate Effective interest rate
  5684. * @param int $npery Number of compounding payments per year
  5685. * @return float
  5686. */
  5687. public static function NOMINAL($effect_rate = 0, $npery = 0) {
  5688. $effect_rate = self::flattenSingleValue($effect_rate);
  5689. $npery = (int)self::flattenSingleValue($npery);
  5690. // Validate parameters
  5691. if ($effect_rate <= 0 || $npery < 1) {
  5692. return self::$_errorCodes['num'];
  5693. }
  5694. // Calculate
  5695. return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);
  5696. }
  5697. /**
  5698. * PV
  5699. *
  5700. * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
  5701. *
  5702. * @param float $rate Interest rate per period
  5703. * @param int $nper Number of periods
  5704. * @param float $pmt Periodic payment (annuity)
  5705. * @param float $fv Future Value
  5706. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  5707. * @return float
  5708. */
  5709. public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) {
  5710. $rate = self::flattenSingleValue($rate);
  5711. $nper = self::flattenSingleValue($nper);
  5712. $pmt = self::flattenSingleValue($pmt);
  5713. $fv = self::flattenSingleValue($fv);
  5714. $type = self::flattenSingleValue($type);
  5715. // Validate parameters
  5716. if ($type != 0 && $type != 1) {
  5717. return self::$_errorCodes['num'];
  5718. }
  5719. // Calculate
  5720. if (!is_null($rate) && $rate != 0) {
  5721. return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);
  5722. } else {
  5723. return -$fv - $pmt * $nper;
  5724. }
  5725. }
  5726. /**
  5727. * FV
  5728. *
  5729. * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
  5730. *
  5731. * @param float $rate Interest rate per period
  5732. * @param int $nper Number of periods
  5733. * @param float $pmt Periodic payment (annuity)
  5734. * @param float $pv Present Value
  5735. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  5736. * @return float
  5737. */
  5738. public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {
  5739. $rate = self::flattenSingleValue($rate);
  5740. $nper = self::flattenSingleValue($nper);
  5741. $pmt = self::flattenSingleValue($pmt);
  5742. $pv = self::flattenSingleValue($pv);
  5743. $type = self::flattenSingleValue($type);
  5744. // Validate parameters
  5745. if ($type != 0 && $type != 1) {
  5746. return self::$_errorCodes['num'];
  5747. }
  5748. // Calculate
  5749. if (!is_null($rate) && $rate != 0) {
  5750. return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;
  5751. } else {
  5752. return -$pv - $pmt * $nper;
  5753. }
  5754. }
  5755. /**
  5756. * PMT
  5757. *
  5758. * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
  5759. *
  5760. * @param float $rate Interest rate per period
  5761. * @param int $nper Number of periods
  5762. * @param float $pv Present Value
  5763. * @param float $fv Future Value
  5764. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  5765. * @return float
  5766. */
  5767. public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
  5768. $rate = self::flattenSingleValue($rate);
  5769. $nper = self::flattenSingleValue($nper);
  5770. $pv = self::flattenSingleValue($pv);
  5771. $fv = self::flattenSingleValue($fv);
  5772. $type = self::flattenSingleValue($type);
  5773. // Validate parameters
  5774. if ($type != 0 && $type != 1) {
  5775. return self::$_errorCodes['num'];
  5776. }
  5777. // Calculate
  5778. if (!is_null($rate) && $rate != 0) {
  5779. return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
  5780. } else {
  5781. return (-$pv - $fv) / $nper;
  5782. }
  5783. }
  5784. /**
  5785. * NPER
  5786. *
  5787. * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
  5788. *
  5789. * @param float $rate Interest rate per period
  5790. * @param int $pmt Periodic payment (annuity)
  5791. * @param float $pv Present Value
  5792. * @param float $fv Future Value
  5793. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  5794. * @return float
  5795. */
  5796. public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
  5797. $rate = self::flattenSingleValue($rate);
  5798. $pmt = self::flattenSingleValue($pmt);
  5799. $pv = self::flattenSingleValue($pv);
  5800. $fv = self::flattenSingleValue($fv);
  5801. $type = self::flattenSingleValue($type);
  5802. // Validate parameters
  5803. if ($type != 0 && $type != 1) {
  5804. return self::$_errorCodes['num'];
  5805. }
  5806. // Calculate
  5807. if (!is_null($rate) && $rate != 0) {
  5808. if ($pmt == 0 && $pv == 0) {
  5809. return self::$_errorCodes['num'];
  5810. }
  5811. return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
  5812. } else {
  5813. if ($pmt == 0) {
  5814. return self::$_errorCodes['num'];
  5815. }
  5816. return (-$pv -$fv) / $pmt;
  5817. }
  5818. }
  5819. /**
  5820. * NPV
  5821. *
  5822. * Returns the Net Present Value of a cash flow series given a discount rate.
  5823. *
  5824. * @param float Discount interest rate
  5825. * @param array Cash flow series
  5826. * @return float
  5827. */
  5828. public static function NPV() {
  5829. // Return value
  5830. $returnValue = 0;
  5831. // Loop trough arguments
  5832. $aArgs = self::flattenArray(func_get_args());
  5833. // Calculate
  5834. $rate = array_shift($aArgs);
  5835. for ($i = 1; $i <= count($aArgs); ++$i) {
  5836. // Is it a numeric value?
  5837. if (is_numeric($aArgs[$i - 1])) {
  5838. $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
  5839. }
  5840. }
  5841. // Return
  5842. return $returnValue;
  5843. }
  5844. /**
  5845. * ACCRINT
  5846. *
  5847. * Computes the accrued interest for a security that pays periodic interest.
  5848. *
  5849. * @param int $issue
  5850. * @param int $firstInterest
  5851. * @param int $settlement
  5852. * @param int $rate
  5853. * @param int $par
  5854. * @param int $frequency
  5855. * @param int $basis
  5856. * @return int The accrued interest for a security that pays periodic interest.
  5857. */
  5858. /*
  5859. public static function ACCRINT($issue = 0, $firstInterest = 0, $settlement = 0, $rate = 0, $par = 1000, $frequency = 1, $basis = 0) {
  5860. $issue = self::flattenSingleValue($issue);
  5861. $firstInterest = self::flattenSingleValue($firstInterest);
  5862. $settlement = self::flattenSingleValue($settlement);
  5863. $rate = self::flattenSingleValue($rate);
  5864. $par = self::flattenSingleValue($par);
  5865. $frequency = self::flattenSingleValue($frequency);
  5866. $basis = self::flattenSingleValue($basis);
  5867. // Perform checks
  5868. if ($issue >= $settlement || $rate <= 0 || $par <= 0 || !($frequency == 1 || $frequency == 2 || $frequency == 4) || $basis < 0 || $basis > 4) return self::$_errorCodes['num'];
  5869. // Calculate value
  5870. return $par * ($rate / $frequency) *
  5871. }
  5872. */
  5873. /**
  5874. * SLN
  5875. *
  5876. * Returns the straight-line depreciation of an asset for one period
  5877. *
  5878. * @param cost Initial cost of the asset
  5879. * @param salvage Value at the end of the depreciation
  5880. * @param life Number of periods over which the asset is depreciated
  5881. * @return float
  5882. */
  5883. public static function SLN($cost, $salvage, $life) {
  5884. $cost = self::flattenSingleValue($cost);
  5885. $salvage = self::flattenSingleValue($salvage);
  5886. $life = self::flattenSingleValue($life);
  5887. // Calculate
  5888. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) {
  5889. if ($life < 0) {
  5890. return self::$_errorCodes['num'];
  5891. }
  5892. return ($cost - $salvage) / $life;
  5893. }
  5894. return self::$_errorCodes['value'];
  5895. }
  5896. /**
  5897. * CELL_ADDRESS
  5898. *
  5899. * Returns the straight-line depreciation of an asset for one period
  5900. *
  5901. * @param row Row number to use in the cell reference
  5902. * @param column Column number to use in the cell reference
  5903. * @param relativity Flag indicating the type of reference to return
  5904. * @param sheetText Name of worksheet to use
  5905. * @return string
  5906. */
  5907. public static function CELL_ADDRESS($row, $column, $relativity=1, $referenceStyle=True, $sheetText='') {
  5908. $row = self::flattenSingleValue($row);
  5909. $column = self::flattenSingleValue($column);
  5910. $relativity = self::flattenSingleValue($relativity);
  5911. $sheetText = self::flattenSingleValue($sheetText);
  5912. if ($sheetText > '') {
  5913. if (strpos($sheetText,' ') !== False) { $sheetText = "'".$sheetText."'"; }
  5914. $sheetText .='!';
  5915. }
  5916. if (!$referenceStyle) {
  5917. if (($relativity == 2) || ($relativity == 4)) { $column = '['.$column.']'; }
  5918. if (($relativity == 3) || ($relativity == 4)) { $row = '['.$row.']'; }
  5919. return $sheetText.'R'.$row.'C'.$column;
  5920. } else {
  5921. $rowRelative = $columnRelative = '$';
  5922. $column = PHPExcel_Cell::stringFromColumnIndex($column-1);
  5923. if (($relativity == 2) || ($relativity == 4)) { $columnRelative = ''; }
  5924. if (($relativity == 3) || ($relativity == 4)) { $rowRelative = ''; }
  5925. return $sheetText.$columnRelative.$column.$rowRelative.$row;
  5926. }
  5927. }
  5928. public static function CHOOSE() {
  5929. $chooseArgs = func_get_args();
  5930. $chosenEntry = self::flattenSingleValue(array_shift($chooseArgs));
  5931. $entryCount = count($chooseArgs) - 1;
  5932. if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
  5933. $chosenEntry--;
  5934. } else {
  5935. return self::$_errorCodes['value'];
  5936. }
  5937. $chosenEntry = floor($chosenEntry);
  5938. if (($chosenEntry <= 0) || ($chosenEntry > $entryCount)) {
  5939. return self::$_errorCodes['value'];
  5940. }
  5941. if (is_array($chooseArgs[$chosenEntry])) {
  5942. return self::flattenArray($chooseArgs[$chosenEntry]);
  5943. } else {
  5944. return $chooseArgs[$chosenEntry];
  5945. }
  5946. }
  5947. /**
  5948. * MATCH
  5949. * The MATCH function searches for a specified item in a range of cells
  5950. * @param lookup_value The value that you want to match in lookup_array
  5951. * @param lookup_array The range of cells being searched
  5952. * @param match_type The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. If match_type is 1 or -1, the list has to be ordered.
  5953. * @return integer the relative position of the found item
  5954. */
  5955. public static function MATCH($lookup_value, $lookup_array, $match_type=1) {
  5956. // flatten the lookup_array
  5957. $lookup_array = self::flattenArray($lookup_array);
  5958. // flatten lookup_value since it may be a cell reference to a value or the value itself
  5959. $lookup_value = self::flattenSingleValue($lookup_value);
  5960. // MATCH is not case sensitive
  5961. $lookup_value = strtolower($lookup_value);
  5962. /*
  5963. echo "--------------------<br>looking for $lookup_value in <br>";
  5964. print_r($lookup_array);
  5965. echo "<br>";
  5966. //return 1;
  5967. /**/
  5968. // **
  5969. // check inputs
  5970. // **
  5971. // lookup_value type has to be number, text, or logical values
  5972. if (!is_numeric($lookup_value) && !is_string($lookup_value) && !is_bool($lookup_value)){
  5973. // error: lookup_array should contain only number, text, or logical values
  5974. //echo "error: lookup_array should contain only number, text, or logical values<br>";
  5975. return self::$_errorCodes['na'];
  5976. }
  5977. // match_type is 0, 1 or -1
  5978. if ($match_type!==0 && $match_type!==-1 && $match_type!==1){
  5979. // error: wrong value for match_type
  5980. //echo "error: wrong value for match_type<br>";
  5981. return self::$_errorCodes['na'];
  5982. }
  5983. // lookup_array should not be empty
  5984. if (sizeof($lookup_array)<=0){
  5985. // error: empty range
  5986. //echo "error: empty range ".sizeof($lookup_array)."<br>";
  5987. return self::$_errorCodes['na'];
  5988. }
  5989. // lookup_array should contain only number, text, or logical values
  5990. for ($i=0;$i<sizeof($lookup_array);$i++){
  5991. // check the type of the value
  5992. if (!is_numeric($lookup_array[$i]) && !is_string($lookup_array[$i]) && !is_bool($lookup_array[$i])){
  5993. // error: lookup_array should contain only number, text, or logical values
  5994. //echo "error: lookup_array should contain only number, text, or logical values<br>";
  5995. return self::$_errorCodes['na'];
  5996. }
  5997. // convert tpo lowercase
  5998. if (is_string($lookup_array[$i]))
  5999. $lookup_array[$i] = strtolower($lookup_array[$i]);
  6000. }
  6001. // if match_type is 1 or -1, the list has to be ordered
  6002. if($match_type==1 || $match_type==-1){
  6003. // **
  6004. // iniitialization
  6005. // store the last value
  6006. $iLastValue=$lookup_array[0];
  6007. // **
  6008. // loop on the cells
  6009. for ($i=0;$i<sizeof($lookup_array);$i++){
  6010. // check ascending order
  6011. if(($match_type==1 && $lookup_array[$i]<$iLastValue)
  6012. // OR check descending order
  6013. || ($match_type==-1 && $lookup_array[$i]>$iLastValue)){
  6014. // error: list is not ordered correctly
  6015. //echo "error: list is not ordered correctly<br>";
  6016. return self::$_errorCodes['na'];
  6017. }
  6018. }
  6019. }
  6020. // **
  6021. // find the match
  6022. // **
  6023. // loop on the cells
  6024. for ($i=0; $i < sizeof($lookup_array); $i++){
  6025. // if match_type is 0 <=> find the first value that is exactly equal to lookup_value
  6026. if ($match_type==0 && $lookup_array[$i]==$lookup_value){
  6027. // this is the exact match
  6028. return $i+1;
  6029. }
  6030. // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
  6031. if ($match_type==-1 && $lookup_array[$i] < $lookup_value){
  6032. if ($i<1){
  6033. // 1st cell was allready smaller than the lookup_value
  6034. break;
  6035. }
  6036. else
  6037. // the previous cell was the match
  6038. return $i;
  6039. }
  6040. // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
  6041. if ($match_type==1 && $lookup_array[$i] > $lookup_value){
  6042. if ($i<1){
  6043. // 1st cell was allready bigger than the lookup_value
  6044. break;
  6045. }
  6046. else
  6047. // the previous cell was the match
  6048. return $i;
  6049. }
  6050. }
  6051. // unsuccessful in finding a match, return #N/A error value
  6052. //echo "unsuccessful in finding a match<br>";
  6053. return self::$_errorCodes['na'];
  6054. }
  6055. /**
  6056. * Uses an index to choose a value from a reference or array
  6057. * implemented: Return the value of a specified cell or array of cells Array form
  6058. * not implemented: Return a reference to specified cells Reference form
  6059. *
  6060. * @param range_array a range of cells or an array constant
  6061. * @param row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
  6062. * @param column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
  6063. */
  6064. public static function INDEX($range_array,$row_num=null,$column_num=null) {
  6065. // **
  6066. // check inputs
  6067. // **
  6068. // at least one of row_num and column_num is required
  6069. if ($row_num==null && $column_num==null){
  6070. // error: row_num and column_num are both undefined
  6071. //echo "error: row_num and column_num are both undefined<br>";
  6072. return self::$_errorCodes['value'];
  6073. }
  6074. // default values for row_num and column_num
  6075. if ($row_num==null){
  6076. $row_num = 1;
  6077. }
  6078. if ($column_num==null){
  6079. $column_num = 1;
  6080. }
  6081. /* debug
  6082. print_r($range_array);
  6083. echo "<br>$row_num , $column_num<br>";
  6084. /**/
  6085. // row_num and column_num may not have negative values
  6086. if (($row_num!=null && $row_num < 0) || ($column_num!=null && $column_num < 0)) {
  6087. // error: row_num or column_num has negative value
  6088. //echo "error: row_num or column_num has negative value<br>";
  6089. return self::$_errorCodes['value'];
  6090. }
  6091. // **
  6092. // convert column and row numbers into array indeces
  6093. // **
  6094. // array is zero based
  6095. $column_num--;
  6096. $row_num--;
  6097. // retrieve the columns
  6098. $columnKeys = array_keys($range_array);
  6099. // retrieve the rows
  6100. $rowKeys = array_keys($range_array[$columnKeys[0]]);
  6101. // test ranges
  6102. if ($column_num >= sizeof($columnKeys)){
  6103. // error: column_num is out of range
  6104. //echo "error: column_num is out of range - $column_num > ".sizeof($columnKeys)."<br>";
  6105. return self::$_errorCodes['reference'];
  6106. }
  6107. if ($row_num >= sizeof($rowKeys)){
  6108. // error: row_num is out of range
  6109. //echo "error: row_num is out of range - $row_num > ".sizeof($rowKeys)."<br>";
  6110. return self::$_errorCodes['reference'];
  6111. }
  6112. // compute and return result
  6113. return $range_array[$columnKeys[$column_num]][$rowKeys[$row_num]];
  6114. }
  6115. /* public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
  6116. if (($rowNum < 0) || ($columnNum < 0)) {
  6117. return self::$_errorCodes['value'];
  6118. }
  6119. $columnKeys = array_keys($arrayValues);
  6120. $rowKeys = array_keys($arrayValues[$columnKeys[0]]);
  6121. if ($columnNum > count($columnKeys)) {
  6122. return self::$_errorCodes['value'];
  6123. } elseif ($columnNum == 0) {
  6124. if ($rowNum == 0) {
  6125. return $arrayValues;
  6126. }
  6127. $rowNum = $rowKeys[--$rowNum];
  6128. $returnArray = array();
  6129. foreach($arrayValues as $arrayColumn) {
  6130. $returnArray[] = $arrayColumn[$rowNum];
  6131. }
  6132. return $returnArray;
  6133. }
  6134. $columnNum = $columnKeys[--$columnNum];
  6135. if ($rowNum > count($rowKeys)) {
  6136. return self::$_errorCodes['value'];
  6137. } elseif ($rowNum == 0) {
  6138. return $arrayValues[$columnNum];
  6139. }
  6140. $rowNum = $rowKeys[--$rowNum];
  6141. return $arrayValues[$columnNum][$rowNum];
  6142. }
  6143. */
  6144. /**
  6145. * SYD
  6146. *
  6147. * Returns the sum-of-years' digits depreciation of an asset for a specified period.
  6148. *
  6149. * @param cost Initial cost of the asset
  6150. * @param salvage Value at the end of the depreciation
  6151. * @param life Number of periods over which the asset is depreciated
  6152. * @param period Period
  6153. * @return float
  6154. */
  6155. public static function SYD($cost, $salvage, $life, $period) {
  6156. $cost = self::flattenSingleValue($cost);
  6157. $salvage = self::flattenSingleValue($salvage);
  6158. $life = self::flattenSingleValue($life);
  6159. $period = self::flattenSingleValue($period);
  6160. // Calculate
  6161. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) {
  6162. if (($life < 1) || ($salvage < $life)) {
  6163. return self::$_errorCodes['num'];
  6164. }
  6165. return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));
  6166. }
  6167. return self::$_errorCodes['value'];
  6168. }
  6169. /**
  6170. * VLOOKUP
  6171. * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
  6172. * @param lookup_value The value that you want to match in lookup_array
  6173. * @param lookup_array The range of cells being searched
  6174. * @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
  6175. * @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
  6176. * @return mixed The value of the found cell
  6177. */
  6178. public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
  6179. // index_number must be greater than or equal to 1
  6180. if ($index_number < 1) {
  6181. return self::$_errorCodes['value'];
  6182. }
  6183. // index_number must be less than or equal to the number of columns in lookup_array
  6184. if ($index_number > count($lookup_array)) {
  6185. return self::$_errorCodes['reference'];
  6186. }
  6187. // re-index lookup_array with numeric keys starting at 1
  6188. array_unshift($lookup_array, array());
  6189. $lookup_array = array_slice(array_values($lookup_array), 1, count($lookup_array), true);
  6190. // look for an exact match
  6191. $row_number = array_search($lookup_value, $lookup_array[1]);
  6192. // if an exact match is required, we have what we need to return an appropriate response
  6193. if ($not_exact_match == false) {
  6194. if ($row_number === false) {
  6195. return self::$_errorCodes['na'];
  6196. } else {
  6197. return $lookup_array[$index_number][$row_number];
  6198. }
  6199. }
  6200. // TODO: The VLOOKUP spec in Excel states that, at this point, we should search for
  6201. // the highest value that is less than lookup_value. However, documentation on how string
  6202. // values should be treated here is sparse.
  6203. return self::$_errorCodes['na'];
  6204. }
  6205. /**
  6206. * Flatten multidemensional array
  6207. *
  6208. * @param array $array Array to be flattened
  6209. * @return array Flattened array
  6210. */
  6211. public static function flattenArray($array) {
  6212. $arrayValues = array();
  6213. foreach ($array as $value) {
  6214. if (is_scalar($value)) {
  6215. $arrayValues[] = self::flattenSingleValue($value);
  6216. } elseif (is_array($value)) {
  6217. $arrayValues = array_merge($arrayValues, self::flattenArray($value));
  6218. } else {
  6219. $arrayValues[] = $value;
  6220. }
  6221. }
  6222. return $arrayValues;
  6223. }
  6224. /**
  6225. * Convert an array with one element to a flat value
  6226. *
  6227. * @param mixed $value Array or flat value
  6228. * @return mixed
  6229. */
  6230. public static function flattenSingleValue($value = '') {
  6231. if (is_array($value)) {
  6232. $value = self::flattenSingleValue(array_pop($value));
  6233. }
  6234. return $value;
  6235. }
  6236. }
  6237. //
  6238. // There are a few mathematical functions that aren't available on all versions of PHP for all platforms
  6239. // These functions aren't available in Windows implementations of PHP prior to version 5.3.0
  6240. // So we test if they do exist for this version of PHP/operating platform; and if not we create them
  6241. //
  6242. if (!function_exists('acosh')) {
  6243. function acosh($x) {
  6244. return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
  6245. }
  6246. }
  6247. if (!function_exists('asinh')) {
  6248. function asinh($x) {
  6249. return log($x + sqrt(1 + $x * $x));
  6250. }
  6251. }
  6252. if (!function_exists('atanh')) {
  6253. function atanh($x) {
  6254. return (log(1 + $x) - log(1 - $x)) / 2;
  6255. }
  6256. }
  6257. ?>