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

/lib/PHPExcel/Calculation/Statistical.php

https://github.com/yuweijun/blog
PHP | 3745 lines | 2176 code | 376 blank | 1193 comment | 641 complexity | 36beb73f1383cae74278f2e963c7e9b0 MD5 | raw file

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

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

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