PageRenderTime 63ms CodeModel.GetById 23ms RepoModel.GetById 0ms app.codeStats 1ms

/framework/lib/classes/PHPExcel/Calculation/Statistical.php

https://bitbucket.org/designbyheart/original
PHP | 3643 lines | 2023 code | 386 blank | 1234 comment | 652 complexity | 2b43157d04f52547e4d3dd0f81aac675 MD5 | raw file
Possible License(s): GPL-3.0

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

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.6, 2011-02-27
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
  36. /** LOG_GAMMA_X_MAX_VALUE */
  37. define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
  38. /** XMININ */
  39. define('XMININ', 2.23e-308);
  40. /** EPS */
  41. define('EPS', 2.22e-16);
  42. /** SQRT2PI */
  43. define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
  44. /**
  45. * PHPExcel_Calculation_Statistical
  46. *
  47. * @category PHPExcel
  48. * @package PHPExcel_Calculation
  49. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  50. */
  51. class PHPExcel_Calculation_Statistical {
  52. private static function _checkTrendArrays(&$array1,&$array2) {
  53. if (!is_array($array1)) { $array1 = array($array1); }
  54. if (!is_array($array2)) { $array2 = array($array2); }
  55. $array1 = PHPExcel_Calculation_Functions::flattenArray($array1);
  56. $array2 = PHPExcel_Calculation_Functions::flattenArray($array2);
  57. foreach($array1 as $key => $value) {
  58. if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
  59. unset($array1[$key]);
  60. unset($array2[$key]);
  61. }
  62. }
  63. foreach($array2 as $key => $value) {
  64. if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
  65. unset($array1[$key]);
  66. unset($array2[$key]);
  67. }
  68. }
  69. $array1 = array_merge($array1);
  70. $array2 = array_merge($array2);
  71. return True;
  72. } // function _checkTrendArrays()
  73. /**
  74. * Beta function.
  75. *
  76. * @author Jaco van Kooten
  77. *
  78. * @param p require p>0
  79. * @param q require q>0
  80. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  81. */
  82. private static function _beta($p, $q) {
  83. if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) {
  84. return 0.0;
  85. } else {
  86. return exp(self::_logBeta($p, $q));
  87. }
  88. } // function _beta()
  89. /**
  90. * Incomplete beta function
  91. *
  92. * @author Jaco van Kooten
  93. * @author Paul Meagher
  94. *
  95. * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).
  96. * @param x require 0<=x<=1
  97. * @param p require p>0
  98. * @param q require q>0
  99. * @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
  100. */
  101. private static function _incompleteBeta($x, $p, $q) {
  102. if ($x <= 0.0) {
  103. return 0.0;
  104. } elseif ($x >= 1.0) {
  105. return 1.0;
  106. } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  107. return 0.0;
  108. }
  109. $beta_gam = exp((0 - self::_logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
  110. if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
  111. return $beta_gam * self::_betaFraction($x, $p, $q) / $p;
  112. } else {
  113. return 1.0 - ($beta_gam * self::_betaFraction(1 - $x, $q, $p) / $q);
  114. }
  115. } // function _incompleteBeta()
  116. // Function cache for _logBeta function
  117. private static $_logBetaCache_p = 0.0;
  118. private static $_logBetaCache_q = 0.0;
  119. private static $_logBetaCache_result = 0.0;
  120. /**
  121. * The natural logarithm of the beta function.
  122. * @param p require p>0
  123. * @param q require q>0
  124. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  125. * @author Jaco van Kooten
  126. */
  127. private static function _logBeta($p, $q) {
  128. if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
  129. self::$_logBetaCache_p = $p;
  130. self::$_logBetaCache_q = $q;
  131. if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  132. self::$_logBetaCache_result = 0.0;
  133. } else {
  134. self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
  135. }
  136. }
  137. return self::$_logBetaCache_result;
  138. } // function _logBeta()
  139. /**
  140. * Evaluates of continued fraction part of incomplete beta function.
  141. * Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
  142. * @author Jaco van Kooten
  143. */
  144. private static function _betaFraction($x, $p, $q) {
  145. $c = 1.0;
  146. $sum_pq = $p + $q;
  147. $p_plus = $p + 1.0;
  148. $p_minus = $p - 1.0;
  149. $h = 1.0 - $sum_pq * $x / $p_plus;
  150. if (abs($h) < XMININ) {
  151. $h = XMININ;
  152. }
  153. $h = 1.0 / $h;
  154. $frac = $h;
  155. $m = 1;
  156. $delta = 0.0;
  157. while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
  158. $m2 = 2 * $m;
  159. // even index for d
  160. $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
  161. $h = 1.0 + $d * $h;
  162. if (abs($h) < XMININ) {
  163. $h = XMININ;
  164. }
  165. $h = 1.0 / $h;
  166. $c = 1.0 + $d / $c;
  167. if (abs($c) < XMININ) {
  168. $c = XMININ;
  169. }
  170. $frac *= $h * $c;
  171. // odd index for d
  172. $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
  173. $h = 1.0 + $d * $h;
  174. if (abs($h) < XMININ) {
  175. $h = XMININ;
  176. }
  177. $h = 1.0 / $h;
  178. $c = 1.0 + $d / $c;
  179. if (abs($c) < XMININ) {
  180. $c = XMININ;
  181. }
  182. $delta = $h * $c;
  183. $frac *= $delta;
  184. ++$m;
  185. }
  186. return $frac;
  187. } // function _betaFraction()
  188. /**
  189. * logGamma function
  190. *
  191. * @version 1.1
  192. * @author Jaco van Kooten
  193. *
  194. * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
  195. *
  196. * The natural logarithm of the gamma function. <br />
  197. * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
  198. * Applied Mathematics Division <br />
  199. * Argonne National Laboratory <br />
  200. * Argonne, IL 60439 <br />
  201. * <p>
  202. * References:
  203. * <ol>
  204. * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
  205. * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
  206. * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
  207. * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
  208. * </ol>
  209. * </p>
  210. * <p>
  211. * From the original documentation:
  212. * </p>
  213. * <p>
  214. * This routine calculates the LOG(GAMMA) function for a positive real argument X.
  215. * Computation is based on an algorithm outlined in references 1 and 2.
  216. * The program uses rational functions that theoretically approximate LOG(GAMMA)
  217. * to at least 18 significant decimal digits. The approximation for X > 12 is from
  218. * reference 3, while approximations for X < 12.0 are similar to those in reference
  219. * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
  220. * the compiler, the intrinsic functions, and proper selection of the
  221. * machine-dependent constants.
  222. * </p>
  223. * <p>
  224. * Error returns: <br />
  225. * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
  226. * The computation is believed to be free of underflow and overflow.
  227. * </p>
  228. * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
  229. */
  230. // Function cache for logGamma
  231. private static $_logGammaCache_result = 0.0;
  232. private static $_logGammaCache_x = 0.0;
  233. private static function _logGamma($x) {
  234. // Log Gamma related constants
  235. static $lg_d1 = -0.5772156649015328605195174;
  236. static $lg_d2 = 0.4227843350984671393993777;
  237. static $lg_d4 = 1.791759469228055000094023;
  238. static $lg_p1 = array( 4.945235359296727046734888,
  239. 201.8112620856775083915565,
  240. 2290.838373831346393026739,
  241. 11319.67205903380828685045,
  242. 28557.24635671635335736389,
  243. 38484.96228443793359990269,
  244. 26377.48787624195437963534,
  245. 7225.813979700288197698961 );
  246. static $lg_p2 = array( 4.974607845568932035012064,
  247. 542.4138599891070494101986,
  248. 15506.93864978364947665077,
  249. 184793.2904445632425417223,
  250. 1088204.76946882876749847,
  251. 3338152.967987029735917223,
  252. 5106661.678927352456275255,
  253. 3074109.054850539556250927 );
  254. static $lg_p4 = array( 14745.02166059939948905062,
  255. 2426813.369486704502836312,
  256. 121475557.4045093227939592,
  257. 2663432449.630976949898078,
  258. 29403789566.34553899906876,
  259. 170266573776.5398868392998,
  260. 492612579337.743088758812,
  261. 560625185622.3951465078242 );
  262. static $lg_q1 = array( 67.48212550303777196073036,
  263. 1113.332393857199323513008,
  264. 7738.757056935398733233834,
  265. 27639.87074403340708898585,
  266. 54993.10206226157329794414,
  267. 61611.22180066002127833352,
  268. 36351.27591501940507276287,
  269. 8785.536302431013170870835 );
  270. static $lg_q2 = array( 183.0328399370592604055942,
  271. 7765.049321445005871323047,
  272. 133190.3827966074194402448,
  273. 1136705.821321969608938755,
  274. 5267964.117437946917577538,
  275. 13467014.54311101692290052,
  276. 17827365.30353274213975932,
  277. 9533095.591844353613395747 );
  278. static $lg_q4 = array( 2690.530175870899333379843,
  279. 639388.5654300092398984238,
  280. 41355999.30241388052042842,
  281. 1120872109.61614794137657,
  282. 14886137286.78813811542398,
  283. 101680358627.2438228077304,
  284. 341747634550.7377132798597,
  285. 446315818741.9713286462081 );
  286. static $lg_c = array( -0.001910444077728,
  287. 8.4171387781295e-4,
  288. -5.952379913043012e-4,
  289. 7.93650793500350248e-4,
  290. -0.002777777777777681622553,
  291. 0.08333333333333333331554247,
  292. 0.0057083835261 );
  293. // Rough estimate of the fourth root of logGamma_xBig
  294. static $lg_frtbig = 2.25e76;
  295. static $pnt68 = 0.6796875;
  296. if ($x == self::$_logGammaCache_x) {
  297. return self::$_logGammaCache_result;
  298. }
  299. $y = $x;
  300. if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
  301. if ($y <= EPS) {
  302. $res = -log(y);
  303. } elseif ($y <= 1.5) {
  304. // ---------------------
  305. // EPS .LT. X .LE. 1.5
  306. // ---------------------
  307. if ($y < $pnt68) {
  308. $corr = -log($y);
  309. $xm1 = $y;
  310. } else {
  311. $corr = 0.0;
  312. $xm1 = $y - 1.0;
  313. }
  314. if ($y <= 0.5 || $y >= $pnt68) {
  315. $xden = 1.0;
  316. $xnum = 0.0;
  317. for ($i = 0; $i < 8; ++$i) {
  318. $xnum = $xnum * $xm1 + $lg_p1[$i];
  319. $xden = $xden * $xm1 + $lg_q1[$i];
  320. }
  321. $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
  322. } else {
  323. $xm2 = $y - 1.0;
  324. $xden = 1.0;
  325. $xnum = 0.0;
  326. for ($i = 0; $i < 8; ++$i) {
  327. $xnum = $xnum * $xm2 + $lg_p2[$i];
  328. $xden = $xden * $xm2 + $lg_q2[$i];
  329. }
  330. $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  331. }
  332. } elseif ($y <= 4.0) {
  333. // ---------------------
  334. // 1.5 .LT. X .LE. 4.0
  335. // ---------------------
  336. $xm2 = $y - 2.0;
  337. $xden = 1.0;
  338. $xnum = 0.0;
  339. for ($i = 0; $i < 8; ++$i) {
  340. $xnum = $xnum * $xm2 + $lg_p2[$i];
  341. $xden = $xden * $xm2 + $lg_q2[$i];
  342. }
  343. $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  344. } elseif ($y <= 12.0) {
  345. // ----------------------
  346. // 4.0 .LT. X .LE. 12.0
  347. // ----------------------
  348. $xm4 = $y - 4.0;
  349. $xden = -1.0;
  350. $xnum = 0.0;
  351. for ($i = 0; $i < 8; ++$i) {
  352. $xnum = $xnum * $xm4 + $lg_p4[$i];
  353. $xden = $xden * $xm4 + $lg_q4[$i];
  354. }
  355. $res = $lg_d4 + $xm4 * ($xnum / $xden);
  356. } else {
  357. // ---------------------------------
  358. // Evaluate for argument .GE. 12.0
  359. // ---------------------------------
  360. $res = 0.0;
  361. if ($y <= $lg_frtbig) {
  362. $res = $lg_c[6];
  363. $ysq = $y * $y;
  364. for ($i = 0; $i < 6; ++$i)
  365. $res = $res / $ysq + $lg_c[$i];
  366. }
  367. $res /= $y;
  368. $corr = log($y);
  369. $res = $res + log(SQRT2PI) - 0.5 * $corr;
  370. $res += $y * ($corr - 1.0);
  371. }
  372. } else {
  373. // --------------------------
  374. // Return for bad arguments
  375. // --------------------------
  376. $res = MAX_VALUE;
  377. }
  378. // ------------------------------
  379. // Final adjustments and return
  380. // ------------------------------
  381. self::$_logGammaCache_x = $x;
  382. self::$_logGammaCache_result = $res;
  383. return $res;
  384. } // function _logGamma()
  385. //
  386. // Private implementation of the incomplete Gamma function
  387. //
  388. private static function _incompleteGamma($a,$x) {
  389. static $max = 32;
  390. $summer = 0;
  391. for ($n=0; $n<=$max; ++$n) {
  392. $divisor = $a;
  393. for ($i=1; $i<=$n; ++$i) {
  394. $divisor *= ($a + $i);
  395. }
  396. $summer += (pow($x,$n) / $divisor);
  397. }
  398. return pow($x,$a) * exp(0-$x) * $summer;
  399. } // function _incompleteGamma()
  400. //
  401. // Private implementation of the Gamma function
  402. //
  403. private static function _gamma($data) {
  404. if ($data == 0.0) return 0;
  405. static $p0 = 1.000000000190015;
  406. static $p = array ( 1 => 76.18009172947146,
  407. 2 => -86.50532032941677,
  408. 3 => 24.01409824083091,
  409. 4 => -1.231739572450155,
  410. 5 => 1.208650973866179e-3,
  411. 6 => -5.395239384953e-6
  412. );
  413. $y = $x = $data;
  414. $tmp = $x + 5.5;
  415. $tmp -= ($x + 0.5) * log($tmp);
  416. $summer = $p0;
  417. for ($j=1;$j<=6;++$j) {
  418. $summer += ($p[$j] / ++$y);
  419. }
  420. return exp(0 - $tmp + log(SQRT2PI * $summer / $x));
  421. } // function _gamma()
  422. /***************************************************************************
  423. * inverse_ncdf.php
  424. * -------------------
  425. * begin : Friday, January 16, 2004
  426. * copyright : (C) 2004 Michael Nickerson
  427. * email : nickersonm@yahoo.com
  428. *
  429. ***************************************************************************/
  430. private static function _inverse_ncdf($p) {
  431. // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
  432. // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
  433. // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
  434. // I have not checked the accuracy of this implementation. Be aware that PHP
  435. // will truncate the coeficcients to 14 digits.
  436. // You have permission to use and distribute this function freely for
  437. // whatever purpose you want, but please show common courtesy and give credit
  438. // where credit is due.
  439. // Input paramater is $p - probability - where 0 < p < 1.
  440. // Coefficients in rational approximations
  441. static $a = array( 1 => -3.969683028665376e+01,
  442. 2 => 2.209460984245205e+02,
  443. 3 => -2.759285104469687e+02,
  444. 4 => 1.383577518672690e+02,
  445. 5 => -3.066479806614716e+01,
  446. 6 => 2.506628277459239e+00
  447. );
  448. static $b = array( 1 => -5.447609879822406e+01,
  449. 2 => 1.615858368580409e+02,
  450. 3 => -1.556989798598866e+02,
  451. 4 => 6.680131188771972e+01,
  452. 5 => -1.328068155288572e+01
  453. );
  454. static $c = array( 1 => -7.784894002430293e-03,
  455. 2 => -3.223964580411365e-01,
  456. 3 => -2.400758277161838e+00,
  457. 4 => -2.549732539343734e+00,
  458. 5 => 4.374664141464968e+00,
  459. 6 => 2.938163982698783e+00
  460. );
  461. static $d = array( 1 => 7.784695709041462e-03,
  462. 2 => 3.224671290700398e-01,
  463. 3 => 2.445134137142996e+00,
  464. 4 => 3.754408661907416e+00
  465. );
  466. // Define lower and upper region break-points.
  467. $p_low = 0.02425; //Use lower region approx. below this
  468. $p_high = 1 - $p_low; //Use upper region approx. above this
  469. if (0 < $p && $p < $p_low) {
  470. // Rational approximation for lower region.
  471. $q = sqrt(-2 * log($p));
  472. return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  473. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  474. } elseif ($p_low <= $p && $p <= $p_high) {
  475. // Rational approximation for central region.
  476. $q = $p - 0.5;
  477. $r = $q * $q;
  478. return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
  479. ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
  480. } elseif ($p_high < $p && $p < 1) {
  481. // Rational approximation for upper region.
  482. $q = sqrt(-2 * log(1 - $p));
  483. return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  484. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  485. }
  486. // If 0 < p < 1, return a null value
  487. return PHPExcel_Calculation_Functions::NULL();
  488. } // function _inverse_ncdf()
  489. private static function _inverse_ncdf2($prob) {
  490. // Approximation of inverse standard normal CDF developed by
  491. // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
  492. $a1 = 2.50662823884;
  493. $a2 = -18.61500062529;
  494. $a3 = 41.39119773534;
  495. $a4 = -25.44106049637;
  496. $b1 = -8.4735109309;
  497. $b2 = 23.08336743743;
  498. $b3 = -21.06224101826;
  499. $b4 = 3.13082909833;
  500. $c1 = 0.337475482272615;
  501. $c2 = 0.976169019091719;
  502. $c3 = 0.160797971491821;
  503. $c4 = 2.76438810333863E-02;
  504. $c5 = 3.8405729373609E-03;
  505. $c6 = 3.951896511919E-04;
  506. $c7 = 3.21767881768E-05;
  507. $c8 = 2.888167364E-07;
  508. $c9 = 3.960315187E-07;
  509. $y = $prob - 0.5;
  510. if (abs($y) < 0.42) {
  511. $z = ($y * $y);
  512. $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
  513. } else {
  514. if ($y > 0) {
  515. $z = log(-log(1 - $prob));
  516. } else {
  517. $z = log(-log($prob));
  518. }
  519. $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
  520. if ($y < 0) {
  521. $z = -$z;
  522. }
  523. }
  524. return $z;
  525. } // function _inverse_ncdf2()
  526. private static function _inverse_ncdf3($p) {
  527. // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
  528. // Produces the normal deviate Z corresponding to a given lower
  529. // tail area of P; Z is accurate to about 1 part in 10**16.
  530. //
  531. // This is a PHP version of the original FORTRAN code that can
  532. // be found at http://lib.stat.cmu.edu/apstat/
  533. $split1 = 0.425;
  534. $split2 = 5;
  535. $const1 = 0.180625;
  536. $const2 = 1.6;
  537. // coefficients for p close to 0.5
  538. $a0 = 3.3871328727963666080;
  539. $a1 = 1.3314166789178437745E+2;
  540. $a2 = 1.9715909503065514427E+3;
  541. $a3 = 1.3731693765509461125E+4;
  542. $a4 = 4.5921953931549871457E+4;
  543. $a5 = 6.7265770927008700853E+4;
  544. $a6 = 3.3430575583588128105E+4;
  545. $a7 = 2.5090809287301226727E+3;
  546. $b1 = 4.2313330701600911252E+1;
  547. $b2 = 6.8718700749205790830E+2;
  548. $b3 = 5.3941960214247511077E+3;
  549. $b4 = 2.1213794301586595867E+4;
  550. $b5 = 3.9307895800092710610E+4;
  551. $b6 = 2.8729085735721942674E+4;
  552. $b7 = 5.2264952788528545610E+3;
  553. // coefficients for p not close to 0, 0.5 or 1.
  554. $c0 = 1.42343711074968357734;
  555. $c1 = 4.63033784615654529590;
  556. $c2 = 5.76949722146069140550;
  557. $c3 = 3.64784832476320460504;
  558. $c4 = 1.27045825245236838258;
  559. $c5 = 2.41780725177450611770E-1;
  560. $c6 = 2.27238449892691845833E-2;
  561. $c7 = 7.74545014278341407640E-4;
  562. $d1 = 2.05319162663775882187;
  563. $d2 = 1.67638483018380384940;
  564. $d3 = 6.89767334985100004550E-1;
  565. $d4 = 1.48103976427480074590E-1;
  566. $d5 = 1.51986665636164571966E-2;
  567. $d6 = 5.47593808499534494600E-4;
  568. $d7 = 1.05075007164441684324E-9;
  569. // coefficients for p near 0 or 1.
  570. $e0 = 6.65790464350110377720;
  571. $e1 = 5.46378491116411436990;
  572. $e2 = 1.78482653991729133580;
  573. $e3 = 2.96560571828504891230E-1;
  574. $e4 = 2.65321895265761230930E-2;
  575. $e5 = 1.24266094738807843860E-3;
  576. $e6 = 2.71155556874348757815E-5;
  577. $e7 = 2.01033439929228813265E-7;
  578. $f1 = 5.99832206555887937690E-1;
  579. $f2 = 1.36929880922735805310E-1;
  580. $f3 = 1.48753612908506148525E-2;
  581. $f4 = 7.86869131145613259100E-4;
  582. $f5 = 1.84631831751005468180E-5;
  583. $f6 = 1.42151175831644588870E-7;
  584. $f7 = 2.04426310338993978564E-15;
  585. $q = $p - 0.5;
  586. // computation for p close to 0.5
  587. if (abs($q) <= split1) {
  588. $R = $const1 - $q * $q;
  589. $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
  590. ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
  591. } else {
  592. if ($q < 0) {
  593. $R = $p;
  594. } else {
  595. $R = 1 - $p;
  596. }
  597. $R = pow(-log($R),2);
  598. // computation for p not close to 0, 0.5 or 1.
  599. If ($R <= $split2) {
  600. $R = $R - $const2;
  601. $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
  602. ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
  603. } else {
  604. // computation for p near 0 or 1.
  605. $R = $R - $split2;
  606. $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
  607. ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
  608. }
  609. if ($q < 0) {
  610. $z = -$z;
  611. }
  612. }
  613. return $z;
  614. } // function _inverse_ncdf3()
  615. /**
  616. * AVEDEV
  617. *
  618. * Returns the average of the absolute deviations of data points from their mean.
  619. * AVEDEV is a measure of the variability in a data set.
  620. *
  621. * Excel Function:
  622. * AVEDEV(value1[,value2[, ...]])
  623. *
  624. * @access public
  625. * @category Statistical Functions
  626. * @param mixed $arg,... Data values
  627. * @return float
  628. */
  629. public static function AVEDEV() {
  630. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  631. // Return value
  632. $returnValue = null;
  633. $aMean = self::AVERAGE($aArgs);
  634. if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
  635. $aCount = 0;
  636. foreach ($aArgs as $k => $arg) {
  637. if ((is_bool($arg)) &&
  638. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  639. $arg = (integer) $arg;
  640. }
  641. // Is it a numeric value?
  642. if ((is_numeric($arg)) && (!is_string($arg))) {
  643. if (is_null($returnValue)) {
  644. $returnValue = abs($arg - $aMean);
  645. } else {
  646. $returnValue += abs($arg - $aMean);
  647. }
  648. ++$aCount;
  649. }
  650. }
  651. // Return
  652. if ($aCount == 0) {
  653. return PHPExcel_Calculation_Functions::DIV0();
  654. }
  655. return $returnValue / $aCount;
  656. }
  657. return PHPExcel_Calculation_Functions::NaN();
  658. } // function AVEDEV()
  659. /**
  660. * AVERAGE
  661. *
  662. * Returns the average (arithmetic mean) of the arguments
  663. *
  664. * Excel Function:
  665. * AVERAGE(value1[,value2[, ...]])
  666. *
  667. * @access public
  668. * @category Statistical Functions
  669. * @param mixed $arg,... Data values
  670. * @return float
  671. */
  672. public static function AVERAGE() {
  673. $returnValue = $aCount = 0;
  674. // Loop through arguments
  675. foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
  676. if ((is_bool($arg)) &&
  677. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  678. $arg = (integer) $arg;
  679. }
  680. // Is it a numeric value?
  681. if ((is_numeric($arg)) && (!is_string($arg))) {
  682. if (is_null($returnValue)) {
  683. $returnValue = $arg;
  684. } else {
  685. $returnValue += $arg;
  686. }
  687. ++$aCount;
  688. }
  689. }
  690. // Return
  691. if ($aCount > 0) {
  692. return $returnValue / $aCount;
  693. } else {
  694. return PHPExcel_Calculation_Functions::DIV0();
  695. }
  696. } // function AVERAGE()
  697. /**
  698. * AVERAGEA
  699. *
  700. * Returns the average of its arguments, including numbers, text, and logical values
  701. *
  702. * Excel Function:
  703. * AVERAGEA(value1[,value2[, ...]])
  704. *
  705. * @access public
  706. * @category Statistical Functions
  707. * @param mixed $arg,... Data values
  708. * @return float
  709. */
  710. public static function AVERAGEA() {
  711. // Return value
  712. $returnValue = null;
  713. $aCount = 0;
  714. // Loop through arguments
  715. foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
  716. if ((is_bool($arg)) &&
  717. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  718. } else {
  719. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  720. if (is_bool($arg)) {
  721. $arg = (integer) $arg;
  722. } elseif (is_string($arg)) {
  723. $arg = 0;
  724. }
  725. if (is_null($returnValue)) {
  726. $returnValue = $arg;
  727. } else {
  728. $returnValue += $arg;
  729. }
  730. ++$aCount;
  731. }
  732. }
  733. }
  734. // Return
  735. if ($aCount > 0) {
  736. return $returnValue / $aCount;
  737. } else {
  738. return PHPExcel_Calculation_Functions::DIV0();
  739. }
  740. } // function AVERAGEA()
  741. /**
  742. * AVERAGEIF
  743. *
  744. * Returns the average value from a range of cells that contain numbers within the list of arguments
  745. *
  746. * Excel Function:
  747. * AVERAGEIF(value1[,value2[, ...]],condition)
  748. *
  749. * @access public
  750. * @category Mathematical and Trigonometric Functions
  751. * @param mixed $arg,... Data values
  752. * @param string $condition The criteria that defines which cells will be checked.
  753. * @return float
  754. */
  755. public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
  756. // Return value
  757. $returnValue = 0;
  758. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  759. $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
  760. if (count($averageArgs) == 0) {
  761. $averageArgs = $aArgs;
  762. }
  763. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  764. // Loop through arguments
  765. $aCount = 0;
  766. foreach ($aArgs as $key => $arg) {
  767. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  768. $testCondition = '='.$arg.$condition;
  769. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  770. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  771. $returnValue += $arg;
  772. ++$aCount;
  773. }
  774. }
  775. }
  776. // Return
  777. if ($aCount > 0) {
  778. return $returnValue / $aCount;
  779. } else {
  780. return PHPExcel_Calculation_Functions::DIV0();
  781. }
  782. } // function AVERAGEIF()
  783. /**
  784. * BETADIST
  785. *
  786. * Returns the beta distribution.
  787. *
  788. * @param float $value Value at which you want to evaluate the distribution
  789. * @param float $alpha Parameter to the distribution
  790. * @param float $beta Parameter to the distribution
  791. * @param boolean $cumulative
  792. * @return float
  793. *
  794. */
  795. public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
  796. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  797. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  798. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  799. $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
  800. $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
  801. if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  802. if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
  803. return PHPExcel_Calculation_Functions::NaN();
  804. }
  805. if ($rMin > $rMax) {
  806. $tmp = $rMin;
  807. $rMin = $rMax;
  808. $rMax = $tmp;
  809. }
  810. $value -= $rMin;
  811. $value /= ($rMax - $rMin);
  812. return self::_incompleteBeta($value,$alpha,$beta);
  813. }
  814. return PHPExcel_Calculation_Functions::VALUE();
  815. } // function BETADIST()
  816. /**
  817. * BETAINV
  818. *
  819. * Returns the inverse of the beta distribution.
  820. *
  821. * @param float $probability Probability at which you want to evaluate the distribution
  822. * @param float $alpha Parameter to the distribution
  823. * @param float $beta Parameter to the distribution
  824. * @param boolean $cumulative
  825. * @return float
  826. *
  827. */
  828. public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
  829. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  830. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  831. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  832. $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
  833. $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
  834. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  835. if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
  836. return PHPExcel_Calculation_Functions::NaN();
  837. }
  838. if ($rMin > $rMax) {
  839. $tmp = $rMin;
  840. $rMin = $rMax;
  841. $rMax = $tmp;
  842. }
  843. $a = 0;
  844. $b = 2;
  845. $i = 0;
  846. while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  847. $guess = ($a + $b) / 2;
  848. $result = self::BETADIST($guess, $alpha, $beta);
  849. if (($result == $probability) || ($result == 0)) {
  850. $b = $a;
  851. } elseif ($result > $probability) {
  852. $b = $guess;
  853. } else {
  854. $a = $guess;
  855. }
  856. }
  857. if ($i == MAX_ITERATIONS) {
  858. return PHPExcel_Calculation_Functions::NA();
  859. }
  860. return round($rMin + $guess * ($rMax - $rMin),12);
  861. }
  862. return PHPExcel_Calculation_Functions::VALUE();
  863. } // function BETAINV()
  864. /**
  865. * BINOMDIST
  866. *
  867. * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
  868. * a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
  869. * when trials are independent, and when the probability of success is constant throughout the
  870. * experiment. For example, BINOMDIST can calculate the probability that two of the next three
  871. * babies born are male.
  872. *
  873. * @param float $value Number of successes in trials
  874. * @param float $trials Number of trials
  875. * @param float $probability Probability of success on each trial
  876. * @param boolean $cumulative
  877. * @return float
  878. *
  879. * @todo Cumulative distribution function
  880. *
  881. */
  882. public static function BINOMDIST($value, $trials, $probability, $cumulative) {
  883. $value = floor(PHPExcel_Calculation_Functions::flattenSingleValue($value));
  884. $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
  885. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  886. if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
  887. if (($value < 0) || ($value > $trials)) {
  888. return PHPExcel_Calculation_Functions::NaN();
  889. }
  890. if (($probability < 0) || ($probability > 1)) {
  891. return PHPExcel_Calculation_Functions::NaN();
  892. }
  893. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  894. if ($cumulative) {
  895. $summer = 0;
  896. for ($i = 0; $i <= $value; ++$i) {
  897. $summer += PHPExcel_Calculation_MathTrig::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
  898. }
  899. return $summer;
  900. } else {
  901. return PHPExcel_Calculation_MathTrig::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
  902. }
  903. }
  904. }
  905. return PHPExcel_Calculation_Functions::VALUE();
  906. } // function BINOMDIST()
  907. /**
  908. * CHIDIST
  909. *
  910. * Returns the one-tailed probability of the chi-squared distribution.
  911. *
  912. * @param float $value Value for the function
  913. * @param float $degrees degrees of freedom
  914. * @return float
  915. */
  916. public static function CHIDIST($value, $degrees) {
  917. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  918. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  919. if ((is_numeric($value)) && (is_numeric($degrees))) {
  920. if ($degrees < 1) {
  921. return PHPExcel_Calculation_Functions::NaN();
  922. }
  923. if ($value < 0) {
  924. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
  925. return 1;
  926. }
  927. return PHPExcel_Calculation_Functions::NaN();
  928. }
  929. return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
  930. }
  931. return PHPExcel_Calculation_Functions::VALUE();
  932. } // function CHIDIST()
  933. /**
  934. * CHIINV
  935. *
  936. * Returns the one-tailed probability of the chi-squared distribution.
  937. *
  938. * @param float $probability Probability for the function
  939. * @param float $degrees degrees of freedom
  940. * @return float
  941. */
  942. public static function CHIINV($probability, $degrees) {
  943. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  944. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  945. if ((is_numeric($probability)) && (is_numeric($degrees))) {
  946. $xLo = 100;
  947. $xHi = 0;
  948. $x = $xNew = 1;
  949. $dx = 1;
  950. $i = 0;
  951. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  952. // Apply Newton-Raphson step
  953. $result = self::CHIDIST($x, $degrees);
  954. $error = $result - $probability;
  955. if ($error == 0.0) {
  956. $dx = 0;
  957. } elseif ($error < 0.0) {
  958. $xLo = $x;
  959. } else {
  960. $xHi = $x;
  961. }
  962. // Avoid division by zero
  963. if ($result != 0.0) {
  964. $dx = $error / $result;
  965. $xNew = $x - $dx;
  966. }
  967. // If the NR fails to converge (which for example may be the
  968. // case if the initial guess is too rough) we apply a bisection
  969. // step to determine a more narrow interval around the root.
  970. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  971. $xNew = ($xLo + $xHi) / 2;
  972. $dx = $xNew - $x;
  973. }
  974. $x = $xNew;
  975. }
  976. if ($i == MAX_ITERATIONS) {
  977. return PHPExcel_Calculation_Functions::NA();
  978. }
  979. return round($x,12);
  980. }
  981. return PHPExcel_Calculation_Functions::VALUE();
  982. } // function CHIINV()
  983. /**
  984. * CONFIDENCE
  985. *
  986. * Returns the confidence interval for a population mean
  987. *
  988. * @param float $alpha
  989. * @param float $stdDev Standard Deviation
  990. * @param float $size
  991. * @return float
  992. *
  993. */
  994. public static function CONFIDENCE($alpha,$stdDev,$size) {
  995. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  996. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  997. $size = floor(PHPExcel_Calculation_Functions::flattenSingleValue($size));
  998. if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
  999. if (($alpha <= 0) || ($alpha >= 1)) {
  1000. return PHPExcel_Calculation_Functions::NaN();
  1001. }
  1002. if (($stdDev <= 0) || ($size < 1)) {
  1003. return PHPExcel_Calculation_Functions::NaN();
  1004. }
  1005. return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
  1006. }
  1007. return PHPExcel_Calculation_Functions::VALUE();
  1008. } // function CONFIDENCE()
  1009. /**
  1010. * CORREL
  1011. *
  1012. * Returns covariance, the average of the products of deviations for each data point pair.
  1013. *
  1014. * @param array of mixed Data Series Y
  1015. * @param array of mixed Data Series X
  1016. * @return float
  1017. */
  1018. public static function CORREL($yValues,$xValues=null) {
  1019. if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
  1020. return PHPExcel_Calculation_Functions::VALUE();
  1021. }
  1022. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1023. return PHPExcel_Calculation_Functions::VALUE();
  1024. }
  1025. $yValueCount = count($yValues);
  1026. $xValueCount = count($xValues);
  1027. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1028. return PHPExcel_Calculation_Functions::NA();
  1029. } elseif ($yValueCount == 1) {
  1030. return PHPExcel_Calculation_Functions::DIV0();
  1031. }
  1032. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1033. return $bestFitLinear->getCorrelation();
  1034. } // function CORREL()
  1035. /**
  1036. * COUNT
  1037. *
  1038. * Counts the number of cells that contain numbers within the list of arguments
  1039. *
  1040. * Excel Function:
  1041. * COUNT(value1[,value2[, ...]])
  1042. *
  1043. * @access public
  1044. * @category Statistical Functions
  1045. * @param mixed $arg,... Data values
  1046. * @return int
  1047. */
  1048. public static function COUNT() {
  1049. // Return value
  1050. $returnValue = 0;
  1051. // Loop through arguments
  1052. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  1053. foreach ($aArgs as $k => $arg) {
  1054. if ((is_bool($arg)) &&
  1055. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  1056. $arg = (integer) $arg;
  1057. }
  1058. // Is it a numeric value?
  1059. if ((is_numeric($arg)) && (!is_string($arg))) {
  1060. ++$returnValue;
  1061. }
  1062. }
  1063. // Return
  1064. return $returnValue;
  1065. } // function COUNT()
  1066. /**
  1067. * COUNTA
  1068. *
  1069. * Counts the number of cells that are not empty within the list of arguments
  1070. *
  1071. * Excel Function:
  1072. * COUNTA(value1[,value2[, ...]])
  1073. *
  1074. * @access public
  1075. * @category Statistical Functions
  1076. * @param mixed $arg,... Data values
  1077. * @return int
  1078. */
  1079. public static function COUNTA() {
  1080. // Return value
  1081. $returnValue = 0;
  1082. // Loop through arguments
  1083. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1084. foreach ($aArgs as $arg) {
  1085. // Is it a numeric, boolean or string value?
  1086. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  1087. ++$returnValue;
  1088. }
  1089. }
  1090. // Return
  1091. return $returnValue;
  1092. } // function COUNTA()
  1093. /**
  1094. * COUNTBLANK
  1095. *
  1096. * Counts the number of empty cells within the list of arguments
  1097. *
  1098. * Excel Function:
  1099. * COUNTBLANK(value1[,value2[, ...]])
  1100. *
  1101. * @access public
  1102. * @category Statistical Functions
  1103. * @param mixed $arg,... Data values
  1104. * @return int
  1105. */
  1106. public static function COUNTBLANK() {
  1107. // Return value
  1108. $returnValue = 0;
  1109. // Loop through arguments
  1110. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1111. foreach ($aArgs as $arg) {
  1112. // Is it a blank cell?
  1113. if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
  1114. ++$returnValue;
  1115. }
  1116. }
  1117. // Return
  1118. return $returnValue;
  1119. } // function COUNTBLANK()
  1120. /**
  1121. * COUNTIF
  1122. *
  1123. * Counts the number of cells that contain numbers within the list of arguments
  1124. *
  1125. * Excel Function:
  1126. * COUNTIF(value1[,value2[, ...]],condition)
  1127. *
  1128. * @access public
  1129. * @category Statistical Functions
  1130. * @param mixed $arg,... Data values
  1131. * @param string $condition The criteria that defines which cells will be counted.
  1132. * @return int
  1133. */
  1134. public static function COUNTIF($aArgs,$condition) {
  1135. // Return value
  1136. $returnValue = 0;
  1137. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  1138. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  1139. // Loop through arguments
  1140. foreach ($aArgs as $arg) {
  1141. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  1142. $testCondition = '='.$arg.$condition;
  1143. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  1144. // Is it a value within our criteria
  1145. ++$returnValue;
  1146. }
  1147. }
  1148. // Return
  1149. return $returnValue;
  1150. } // function COUNTIF()
  1151. /**
  1152. * COVAR
  1153. *
  1154. * Returns covariance, the average of the products of deviations for each data point pair.
  1155. *
  1156. * @param array of mixed Data Series Y
  1157. * @param array of mixed Data Series X
  1158. * @return float
  1159. */
  1160. public static function COVAR($yValues,$xValues) {
  1161. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1162. return PHPExcel_Calculation_Functions::VALUE();
  1163. }
  1164. $yValueCount = count($yValues);
  1165. $xValueCount = count($xValues);
  1166. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1167. return PHPExcel_Calculation_Functions::NA();
  1168. } elseif ($yValueCount == 1) {
  1169. return PHPExcel_Calculation_Functions::DIV0();
  1170. }
  1171. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1172. return $bestFitLinear->getCovariance();
  1173. } // function COVAR()
  1174. /**
  1175. * CRITBINOM
  1176. *
  1177. * Returns the smallest value for which the cumulative binomial distribution is greater
  1178. * than or equal to a criterion value
  1179. *
  1180. * See http://support.microsoft.com/kb/828117/ for details of the algorithm used
  1181. *
  1182. * @param float $trials number of Bernoulli trials
  1183. * @param float $probability probability of a success on each trial
  1184. * @param float $alpha criterion value
  1185. * @return int
  1186. *
  1187. * @todo Warning. This implementation differs from the algorithm detailed on the MS
  1188. * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
  1189. * This eliminates a potential endless loop error, but may have an adverse affect on the
  1190. * accuracy of the function (although all my tests have so far returned correct results).
  1191. *
  1192. */
  1193. public static function CRITBINOM($trials, $probability, $alpha) {
  1194. $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
  1195. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  1196. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  1197. if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
  1198. if ($trials < 0) {
  1199. return PHPExcel_Calculation_Functions::NaN();
  1200. }
  1201. if (($probability < 0) || ($probability > 1)) {
  1202. return PHPExcel_Calculation_Functions::NaN();
  1203. }
  1204. if (($alpha < 0) || ($alpha > 1)) {
  1205. return PHPExcel_Calculation_Functions::NaN();
  1206. }
  1207. if ($alpha <= 0.5) {
  1208. $t = sqrt(log(1 / ($alpha * $alpha)));
  1209. $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));
  1210. } else {
  1211. $t = sqrt(log(1 / pow(1 - $alpha,2)));
  1212. $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
  1213. }
  1214. $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
  1215. if ($Guess < 0) {
  1216. $Guess = 0;
  1217. } elseif ($Guess > $trials) {
  1218. $Guess = $trials;
  1219. }
  1220. $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
  1221. $EssentiallyZero = 10e-12;
  1222. $m = floor($trials * $probability);
  1223. ++$TotalUnscaledProbability;
  1224. if ($m == $Guess) { ++$UnscaledPGuess; }
  1225. if ($m <= $Guess) { ++$UnscaledCumPGuess; }
  1226. $PreviousValue = 1;
  1227. $Done = False;
  1228. $k = $m + 1;
  1229. while ((!$Done) && ($k <= $trials)) {
  1230. $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
  1231. $TotalUnscaledProbability += $CurrentValue;
  1232. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1233. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1234. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
  1235. $PreviousValue = $CurrentValue;
  1236. ++$k;
  1237. }
  1238. $PreviousValue = 1;
  1239. $Done = False;
  1240. $k = $m - 1;
  1241. while ((!$Done) && ($k >= 0)) {
  1242. $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
  1243. $TotalUnscaledProbability += $CurrentValue;
  1244. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1245. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1246. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
  1247. $PreviousValue = $CurrentValue;
  1248. --$k;
  1249. }
  1250. $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
  1251. $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
  1252. // $CumPGuessMinus1 = $CumPGuess - $PGuess;
  1253. $CumPGuessMinus1 = $CumPGuess - 1;
  1254. while (True) {
  1255. if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
  1256. return $Guess;
  1257. } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
  1258. $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
  1259. $CumPGuessMinus1 = $CumPGuess;
  1260. $CumPGuess = $CumPGuess + $PGuessPlus1;
  1261. $PGuess = $PGuessPlus1;
  1262. ++$Guess;
  1263. } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
  1264. $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
  1265. $CumPGuess = $CumPGuessMinus1;
  1266. $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
  1267. $PGuess = $PGuessMinus1;
  1268. --$Guess;
  1269. }
  1270. }
  1271. }
  1272. return PHPExcel_Calculation_Functions::VALUE();
  1273. } // function CRITBINOM()
  1274. /**
  1275. * DEVSQ
  1276. *
  1277. * Returns the sum of squares of deviations of data points from their sample mean.
  1278. *
  1279. * Excel Function:
  1280. * DEVSQ(value1[,value2[, ...]])
  1281. *
  1282. * @access public
  1283. * @category Statistical Functions
  1284. * @param mixed $arg,... Data values
  1285. * @return float
  1286. */
  1287. public static function DEVSQ() {
  1288. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  1289. // Return value
  1290. $returnValue = null;
  1291. $aMean = self::AVERAGE($aArgs);
  1292. if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
  1293. $aCount = -1;
  1294. foreach ($aArgs as $k => $arg) {
  1295. // Is it a numeric value?
  1296. if ((is_bool($arg)) &&
  1297. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  1298. $arg = (integer) $arg;
  1299. }
  1300. if ((is_numeric($arg)) && (!is_string($arg))) {
  1301. if (is_null($returnValue)) {
  1302. $returnValue = pow(($arg - $aMean),2);
  1303. } else {
  1304. $returnValue += pow(($arg - $aMean),2);
  1305. }
  1306. ++$aCount;
  1307. }
  1308. }
  1309. // Return
  1310. if (is_null($returnValue)) {
  1311. return PHPExcel_Calculation_Functions::NaN();
  1312. } else {
  1313. return $returnValue;
  1314. }
  1315. }
  1316. return self::NA();
  1317. } // function DEVSQ()
  1318. /**
  1319. * EXPONDIST
  1320. *
  1321. * Returns the exponential distribution. Use EXPONDIST to model the time between events,
  1322. * such as how long an automated bank teller takes to deliver cash. For example, you can
  1323. * use EXPONDIST to determine the probability that the process takes at most 1 minute.
  1324. *
  1325. * @param float $value Value of the function
  1326. * @param float $lambda The parameter value
  1327. * @param boolean $cumulative
  1328. * @return float
  1329. */
  1330. public static function EXPONDIST($value, $lambda, $cumulative) {
  1331. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1332. $lambda = PHPExcel_Calculation_Functions::flattenSingleValue($lambda);
  1333. $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
  1334. if ((is_numeric($value)) && (is_numeric($lambda))) {
  1335. if (($value < 0) || ($lambda < 0)) {
  1336. return PHPExcel_Calculation_Functions::NaN();
  1337. }
  1338. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  1339. if ($cumulative) {
  1340. return 1 - exp(0-$value*$lambda);
  1341. } else {
  1342. return $lambda * exp(0-$value*$lambda);
  1343. }
  1344. }
  1345. }
  1346. return PHPExcel_Calculation_Functions::VALUE();
  1347. } // function EXPONDIST()
  1348. /**
  1349. * FISHER
  1350. *
  1351. * Returns the Fisher transformation at x. This transformation produces a function that
  1352. * is normally distributed rather than skewed. Use this function to perform hypothesis
  1353. * testing on the correlation coefficient.
  1354. *
  1355. * @param float $value
  1356. * @return float
  1357. */
  1358. public static function FISHER($value) {
  1359. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1360. if (is_numeric($value)) {
  1361. if (($value <= -1) || ($value >= 1)) {
  1362. return PHPExcel_Calculation_Functions::NaN();
  1363. }
  1364. return 0.5 * log((1+$value)/(1-$value));
  1365. }
  1366. return PHPExcel_Calculation_Functions::VALUE();
  1367. } // function FISHER()
  1368. /**
  1369. * FISHERINV
  1370. *
  1371. * Returns the inverse of the Fisher transformation. Use this transformation when
  1372. * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
  1373. * FISHERINV(y) = x.
  1374. *
  1375. * @param float $value
  1376. * @return float
  1377. */
  1378. public static function FISHERINV($value) {
  1379. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1380. if (is_numeric($value)) {
  1381. return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
  1382. }
  1383. return PHPExcel_Calculation_Functions::VALUE();
  1384. } // function FISHERINV()
  1385. /**
  1386. * FORECAST
  1387. *
  1388. * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.
  1389. *
  1390. * @param float Value of X for which we want to find Y
  1391. * @param array of mixed Data Series Y
  1392. * @param array of mixed Data Series X
  1393. * @return float
  1394. */
  1395. public static function FORECAST($xValue,$yValues,$xValues) {
  1396. $xValue = PHPExcel_Calculation_Functions::flattenSingleValue($xValue);
  1397. if (!is_numeric($xValue)) {
  1398. return PHPExcel_Calculation_Functions::VALUE();
  1399. }
  1400. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1401. return PHPExcel_Calculation_Functions::VALUE();
  1402. }
  1403. $yValueCount = count($yValues);
  1404. $xValueCount = count($xValues);
  1405. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1406. return PHPExcel_Calculation_Functions::NA();
  1407. } elseif ($yValueCount == 1) {
  1408. return PHPExcel_Calculation_Functions::DIV0();
  1409. }
  1410. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1411. return $bestFitLinear->getValueOfYForX($xValue);
  1412. } // function FORECAST()
  1413. /**
  1414. * GAMMADIST
  1415. *
  1416. * Returns the gamma distribution.
  1417. *
  1418. * @param float $value Value at which you want to evaluate the distribution
  1419. * @param float $a Parameter to the distribution
  1420. * @param float $b Parameter to the distribution
  1421. * @param boolean $cumulative
  1422. * @return float
  1423. *
  1424. */
  1425. public static function GAMMADIST($value,$a,$b,$cumulative) {
  1426. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1427. $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
  1428. $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
  1429. if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
  1430. if (($value < 0) || ($a <= 0) || ($b <= 0)) {
  1431. return PHPExcel_Calculation_Functions::NaN();
  1432. }
  1433. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  1434. if ($cumulative) {
  1435. return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
  1436. } else {
  1437. return (1 / (pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
  1438. }
  1439. }
  1440. }
  1441. return PHPExcel_Calculation_Functions::VALUE();
  1442. } // function GAMMADIST()
  1443. /**
  1444. * GAMMAINV
  1445. *
  1446. * Returns the inverse of the beta distribution.
  1447. *
  1448. * @param float $probability Probability at which you want to evaluate the distribution
  1449. * @param float $alpha Parameter to the distribution
  1450. * @param float $beta Parameter to the distribution
  1451. * @return float
  1452. *
  1453. */
  1454. public static function GAMMAINV($probability,$alpha,$beta) {
  1455. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  1456. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  1457. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  1458. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
  1459. if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
  1460. return PHPExcel_Calculation_Functions::NaN();
  1461. }
  1462. $xLo = 0;
  1463. $xHi = $

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