File Source for Statistical.php

PHPExcel_Calculation

# Source for file Statistical.php

Documentation is available at Statistical.php

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. */
29. /** PHPExcel root directory */
30. if (! defined('PHPEXCEL_ROOT')) {
31. /**
32. * @ignore
33. */
34. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36. }
39. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
42. /** LOG_GAMMA_X_MAX_VALUE */
43. define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
45. /** XMININ */
46. define('XMININ', 2.23e-308);
48. /** EPS */
49. define('EPS', 2.22e-16);
51. /** SQRT2PI */
52. define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
55. /**
56. * PHPExcel_Calculation_Statistical
57. *
58. * @category PHPExcel
59. * @package PHPExcel_Calculation
60. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
61. */
62. class PHPExcel_Calculation_Statistical {
65. private static function _checkTrendArrays(&$array1,&$array2) {
66. if (! is_array($array1)) { $array1 = array($array1); }
67. if (! is_array($array2)) { $array2 = array($array2); }
69. $array1 = PHPExcel_Calculation_Functions:: flattenArray($array1);
70. $array2 = PHPExcel_Calculation_Functions:: flattenArray($array2);
71. foreach($array1 as $key => $value) {
72. if (( is_bool($value)) || ( is_string($value)) || ( is_null($value))) {
73. unset($array1[$key]);
74. unset($array2[$key]);
75. }
76. }
77. foreach($array2 as $key => $value) {
78. if (( is_bool($value)) || ( is_string($value)) || ( is_null($value))) {
79. unset($array1[$key]);
80. unset($array2[$key]);
81. }
82. }
83. $array1 = array_merge($array1);
84. $array2 = array_merge($array2);
86. return True;
87. } // function _checkTrendArrays()
90. /**
91. * Beta function.
92. *
93. * @author Jaco van Kooten
94. *
95. * @param p require p>0
96. * @param q require q>0
97. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
98. */
99. private static function _beta($p, $q) {
100. if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) {
101. return 0.0;
102. } else {
103. return exp(self::_logBeta($p, $q));
104. }
105. } // function _beta()
108. /**
109. * Incomplete beta function
110. *
111. * @author Jaco van Kooten
112. * @author Paul Meagher
113. *
114. * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).
115. * @param x require 0<=x<=1
116. * @param p require p>0
117. * @param q require q>0
118. * @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
119. */
120. private static function _incompleteBeta($x, $p, $q) {
121. if ($x <= 0.0) {
122. return 0.0;
123. } elseif ($x >= 1.0) {
124. return 1.0;
125. } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
126. return 0.0;
127. }
128. $beta_gam = exp((0 - self::_logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
129. if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
130. return $beta_gam * self::_betaFraction($x, $p, $q) / $p;
131. } else {
132. return 1.0 - ($beta_gam * self::_betaFraction(1 - $x, $q, $p) / $q);
133. }
134. } // function _incompleteBeta()
137. // Function cache for _logBeta function
138. private static $_logBetaCache_p = 0.0;
139. private static $_logBetaCache_q = 0.0;
140. private static $_logBetaCache_result = 0.0;
142. /**
143. * The natural logarithm of the beta function.
144. * @param p require p>0
145. * @param q require q>0
146. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
147. * @author Jaco van Kooten
148. */
149. private static function _logBeta($p, $q) {
150. if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
151. self::$_logBetaCache_p = $p;
152. self::$_logBetaCache_q = $q;
153. if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
154. self::$_logBetaCache_result = 0.0;
155. } else {
156. self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
157. }
158. }
159. return self::$_logBetaCache_result;
160. } // function _logBeta()
163. /**
164. * Evaluates of continued fraction part of incomplete beta function.
165. * Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
166. * @author Jaco van Kooten
167. */
168. private static function _betaFraction($x, $p, $q) {
169. $c = 1.0;
170. $sum_pq = $p + $q;
171. $p_plus = $p + 1.0;
172. $p_minus = $p - 1.0;
173. $h = 1.0 - $sum_pq * $x / $p_plus;
174. if (abs($h) < XMININ) {
175. $h = XMININ;
176. }
177. $h = 1.0 / $h;
178. $frac = $h;
179. $m = 1;
180. $delta = 0.0;
181. while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
182. $m2 = 2 * $m;
183. // even index for d
184. $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
185. $h = 1.0 + $d * $h;
186. if ( abs($h) < XMININ) {
187. $h = XMININ;
188. }
189. $h = 1.0 / $h;
190. $c = 1.0 + $d / $c;
191. if ( abs($c) < XMININ) {
192. $c = XMININ;
193. }
194. $frac *= $h * $c;
195. // odd index for d
196. $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
197. $h = 1.0 + $d * $h;
198. if ( abs($h) < XMININ) {
199. $h = XMININ;
200. }
201. $h = 1.0 / $h;
202. $c = 1.0 + $d / $c;
203. if ( abs($c) < XMININ) {
204. $c = XMININ;
205. }
206. $delta = $h * $c;
207. $frac *= $delta;
208. ++$m;
209. }
210. return $frac;
211. } // function _betaFraction()
214. /**
215. * logGamma function
216. *
217. * @version 1.1
218. * @author Jaco van Kooten
219. *
220. * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
221. *
222. * The natural logarithm of the gamma function. <br />
223. * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
224. * Applied Mathematics Division <br />
225. * Argonne National Laboratory <br />
226. * Argonne, IL 60439 <br />
227. * <p>
228. * References:
229. * <ol>
230. * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
231. * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
232. * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
233. * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
234. * </ol>
235. * </p>
236. * <p>
237. * From the original documentation:
238. * </p>
239. * <p>
240. * This routine calculates the LOG(GAMMA) function for a positive real argument X.
241. * Computation is based on an algorithm outlined in references 1 and 2.
242. * The program uses rational functions that theoretically approximate LOG(GAMMA)
243. * to at least 18 significant decimal digits. The approximation for X > 12 is from
244. * reference 3, while approximations for X < 12.0 are similar to those in reference
245. * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
246. * the compiler, the intrinsic functions, and proper selection of the
247. * machine-dependent constants.
248. * </p>
249. * <p>
250. * Error returns: <br />
251. * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
252. * The computation is believed to be free of underflow and overflow.
253. * </p>
254. * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
255. */
257. // Function cache for logGamma
258. private static $_logGammaCache_result = 0.0;
259. private static $_logGammaCache_x = 0.0;
261. private static function _logGamma($x) {
262. // Log Gamma related constants
263. static $lg_d1 = -0.5772156649015328605195174;
264. static $lg_d2 = 0.4227843350984671393993777;
265. static $lg_d4 = 1.791759469228055000094023;
267. static $lg_p1 = array( 4.945235359296727046734888,
268. 201.8112620856775083915565,
269. 2290.838373831346393026739,
270. 11319.67205903380828685045,
271. 28557.24635671635335736389,
272. 38484.96228443793359990269,
273. 26377.48787624195437963534,
274. 7225.813979700288197698961 );
275. static $lg_p2 = array( 4.974607845568932035012064,
276. 542.4138599891070494101986,
277. 15506.93864978364947665077,
278. 184793.2904445632425417223,
279. 1088204.76946882876749847,
280. 3338152.967987029735917223,
281. 5106661.678927352456275255,
282. 3074109.054850539556250927 );
283. static $lg_p4 = array( 14745.02166059939948905062,
284. 2426813.369486704502836312,
285. 121475557.4045093227939592,
286. 2663432449.630976949898078,
287. 29403789566.34553899906876,
288. 170266573776.5398868392998,
289. 492612579337.743088758812,
290. 560625185622.3951465078242 );
292. static $lg_q1 = array( 67.48212550303777196073036,
293. 1113.332393857199323513008,
294. 7738.757056935398733233834,
295. 27639.87074403340708898585,
296. 54993.10206226157329794414,
297. 61611.22180066002127833352,
298. 36351.27591501940507276287,
299. 8785.536302431013170870835 );
300. static $lg_q2 = array( 183.0328399370592604055942,
301. 7765.049321445005871323047,
302. 133190.3827966074194402448,
303. 1136705.821321969608938755,
304. 5267964.117437946917577538,
305. 13467014.54311101692290052,
306. 17827365.30353274213975932,
307. 9533095.591844353613395747 );
308. static $lg_q4 = array( 2690.530175870899333379843,
309. 639388.5654300092398984238,
310. 41355999.30241388052042842,
311. 1120872109.61614794137657,
312. 14886137286.78813811542398,
313. 101680358627.2438228077304,
314. 341747634550.7377132798597,
315. 446315818741.9713286462081 );
317. static $lg_c = array( -0.001910444077728,
318. 8.4171387781295e-4,
319. -5.952379913043012e-4,
320. 7.93650793500350248e-4,
321. -0.002777777777777681622553,
322. 0.08333333333333333331554247,
323. 0.0057083835261 );
325. // Rough estimate of the fourth root of logGamma_xBig
326. static $lg_frtbig = 2.25e76;
327. static $pnt68 = 0.6796875;
330. if ($x == self::$_logGammaCache_x) {
331. return self::$_logGammaCache_result;
332. }
333. $y = $x;
334. if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
335. if ($y <= EPS) {
336. $res = -log(y);
337. } elseif ($y <= 1.5) {
338. // ---------------------
339. // EPS .LT. X .LE. 1.5
340. // ---------------------
341. if ($y < $pnt68) {
342. $corr = -log($y);
343. $xm1 = $y;
344. } else {
345. $corr = 0.0;
346. $xm1 = $y - 1.0;
347. }
348. if ($y <= 0.5 || $y >= $pnt68) {
349. $xden = 1.0;
350. $xnum = 0.0;
351. for ($i = 0; $i < 8; ++$i) {
352. $xnum = $xnum * $xm1 + $lg_p1[$i];
353. $xden = $xden * $xm1 + $lg_q1[$i];
354. }
355. $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
356. } else {
357. $xm2 = $y - 1.0;
358. $xden = 1.0;
359. $xnum = 0.0;
360. for ($i = 0; $i < 8; ++$i) {
361. $xnum = $xnum * $xm2 + $lg_p2[$i];
362. $xden = $xden * $xm2 + $lg_q2[$i];
363. }
364. $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
365. }
366. } elseif ($y <= 4.0) {
367. // ---------------------
368. // 1.5 .LT. X .LE. 4.0
369. // ---------------------
370. $xm2 = $y - 2.0;
371. $xden = 1.0;
372. $xnum = 0.0;
373. for ($i = 0; $i < 8; ++$i) {
374. $xnum = $xnum * $xm2 + $lg_p2[$i];
375. $xden = $xden * $xm2 + $lg_q2[$i];
376. }
377. $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
378. } elseif ($y <= 12.0) {
379. // ----------------------
380. // 4.0 .LT. X .LE. 12.0
381. // ----------------------
382. $xm4 = $y - 4.0;
383. $xden = -1.0;
384. $xnum = 0.0;
385. for ($i = 0; $i < 8; ++$i) {
386. $xnum = $xnum * $xm4 + $lg_p4[$i];
387. $xden = $xden * $xm4 + $lg_q4[$i];
388. }
389. $res = $lg_d4 + $xm4 * ($xnum / $xden);
390. } else {
391. // ---------------------------------
392. // Evaluate for argument .GE. 12.0
393. // ---------------------------------
394. $res = 0.0;
395. if ($y <= $lg_frtbig) {
396. $res = $lg_c[6];
397. $ysq = $y * $y;
398. for ($i = 0; $i < 6; ++$i)
399. $res = $res / $ysq + $lg_c[$i];
400. }
401. $res /= $y;
402. $corr = log($y);
403. $res = $res + log( SQRT2PI) - 0.5 * $corr;
404. $res += $y * ($corr - 1.0);
405. }
406. } else {
407. // --------------------------
408. // Return for bad arguments
409. // --------------------------
410. $res = MAX_VALUE;
411. }
412. // ------------------------------
413. // Final adjustments and return
414. // ------------------------------
415. self::$_logGammaCache_x = $x;
416. self::$_logGammaCache_result = $res;
417. return $res;
418. } // function _logGamma()
421. //
422. // Private implementation of the incomplete Gamma function
423. //
424. private static function _incompleteGamma($a,$x) {
425. static $max = 32;
426. $summer = 0;
427. for ($n=0; $n<=$max; ++$n) {
428. $divisor = $a;
429. for ($i=1; $i<=$n; ++$i) {
430. $divisor *= ($a + $i);
431. }
432. $summer += ( pow($x,$n) / $divisor);
433. }
434. return pow($x,$a) * exp(0-$x) * $summer;
435. } // function _incompleteGamma()
438. //
439. // Private implementation of the Gamma function
440. //
441. private static function _gamma($data) {
442. if ($data == 0.0) return 0;
444. static $p0 = 1.000000000190015;
445. static $p = array ( 1 => 76.18009172947146,
446. 2 => -86.50532032941677,
447. 3 => 24.01409824083091,
448. 4 => -1.231739572450155,
449. 5 => 1.208650973866179e-3,
450. 6 => -5.395239384953e-6
451. );
453. $y = $x = $data;
454. $tmp = $x + 5.5;
455. $tmp -= ($x + 0.5) * log($tmp);
457. $summer = $p0;
458. for ($j=1;$j<=6;++$j) {
459. $summer += ($p[$j] / ++$y);
460. }
461. return exp(0 - $tmp + log( SQRT2PI * $summer / $x));
462. } // function _gamma()
465. /***************************************************************************
466. * inverse_ncdf.php
467. * -------------------
468. * begin : Friday, January 16, 2004
469. * copyright : (C) 2004 Michael Nickerson
470. * email : nickersonm@yahoo.com
471. *
472. ***************************************************************************/
473. private static function _inverse_ncdf($p) {
474. // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
475. // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
476. // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
477. // I have not checked the accuracy of this implementation. Be aware that PHP
478. // will truncate the coeficcients to 14 digits.
480. // You have permission to use and distribute this function freely for
481. // whatever purpose you want, but please show common courtesy and give credit
482. // where credit is due.
484. // Input paramater is $p - probability - where 0 < p < 1.
486. // Coefficients in rational approximations
487. static $a = array( 1 => -3.969683028665376e+01,
488. 2 => 2.209460984245205e+02,
489. 3 => -2.759285104469687e+02,
490. 4 => 1.383577518672690e+02,
491. 5 => -3.066479806614716e+01,
492. 6 => 2.506628277459239e+00
493. );
495. static $b = array( 1 => -5.447609879822406e+01,
496. 2 => 1.615858368580409e+02,
497. 3 => -1.556989798598866e+02,
498. 4 => 6.680131188771972e+01,
499. 5 => -1.328068155288572e+01
500. );
502. static $c = array( 1 => -7.784894002430293e-03,
503. 2 => -3.223964580411365e-01,
504. 3 => -2.400758277161838e+00,
505. 4 => -2.549732539343734e+00,
506. 5 => 4.374664141464968e+00,
507. 6 => 2.938163982698783e+00
508. );
510. static $d = array( 1 => 7.784695709041462e-03,
511. 2 => 3.224671290700398e-01,
512. 3 => 2.445134137142996e+00,
513. 4 => 3.754408661907416e+00
514. );
516. // Define lower and upper region break-points.
517. $p_low = 0.02425; //Use lower region approx. below this
518. $p_high = 1 - $p_low; //Use upper region approx. above this
520. if (0 < $p && $p < $p_low) {
521. // Rational approximation for lower region.
522. $q = sqrt(-2 * log($p));
523. return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
524. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
525. } elseif ($p_low <= $p && $p <= $p_high) {
526. // Rational approximation for central region.
527. $q = $p - 0.5;
528. $r = $q * $q;
529. return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
530. ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
531. } elseif ($p_high < $p && $p < 1) {
532. // Rational approximation for upper region.
533. $q = sqrt(-2 * log(1 - $p));
534. return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
535. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
536. }
537. // If 0 < p < 1, return a null value
538. return PHPExcel_Calculation_Functions:: NULL();
539. } // function _inverse_ncdf()
542. private static function _inverse_ncdf2($prob) {
543. // Approximation of inverse standard normal CDF developed by
544. // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
546. $a1 = 2.50662823884;
547. $a2 = -18.61500062529;
548. $a3 = 41.39119773534;
549. $a4 = -25.44106049637;
551. $b1 = -8.4735109309;
552. $b2 = 23.08336743743;
553. $b3 = -21.06224101826;
554. $b4 = 3.13082909833;
556. $c1 = 0.337475482272615;
557. $c2 = 0.976169019091719;
558. $c3 = 0.160797971491821;
559. $c4 = 2.76438810333863E-02;
560. $c5 = 3.8405729373609E-03;
561. $c6 = 3.951896511919E-04;
562. $c7 = 3.21767881768E-05;
563. $c8 = 2.888167364E-07;
564. $c9 = 3.960315187E-07;
566. $y = $prob - 0.5;
567. if ( abs($y) < 0.42) {
568. $z = ($y * $y);
569. $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
570. } else {
571. if ($y > 0) {
572. $z = log(-log(1 - $prob));
573. } else {
574. $z = log(-log($prob));
575. }
576. $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
577. if ($y < 0) {
578. $z = -$z;
579. }
580. }
581. return $z;
582. } // function _inverse_ncdf2()
585. private static function _inverse_ncdf3($p) {
586. // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
587. // Produces the normal deviate Z corresponding to a given lower
588. // tail area of P; Z is accurate to about 1 part in 10**16.
589. //
590. // This is a PHP version of the original FORTRAN code that can
591. // be found at http://lib.stat.cmu.edu/apstat/
592. $split1 = 0.425;
593. $split2 = 5;
594. $const1 = 0.180625;
595. $const2 = 1.6;
597. // coefficients for p close to 0.5
598. $a0 = 3.3871328727963666080;
599. $a1 = 1.3314166789178437745E+2;
600. $a2 = 1.9715909503065514427E+3;
601. $a3 = 1.3731693765509461125E+4;
602. $a4 = 4.5921953931549871457E+4;
603. $a5 = 6.7265770927008700853E+4;
604. $a6 = 3.3430575583588128105E+4;
605. $a7 = 2.5090809287301226727E+3;
607. $b1 = 4.2313330701600911252E+1;
608. $b2 = 6.8718700749205790830E+2;
609. $b3 = 5.3941960214247511077E+3;
610. $b4 = 2.1213794301586595867E+4;
611. $b5 = 3.9307895800092710610E+4;
612. $b6 = 2.8729085735721942674E+4;
613. $b7 = 5.2264952788528545610E+3;
615. // coefficients for p not close to 0, 0.5 or 1.
616. $c0 = 1.42343711074968357734;
617. $c1 = 4.63033784615654529590;
618. $c2 = 5.76949722146069140550;
619. $c3 = 3.64784832476320460504;
620. $c4 = 1.27045825245236838258;
621. $c5 = 2.41780725177450611770E-1;
622. $c6 = 2.27238449892691845833E-2;
623. $c7 = 7.74545014278341407640E-4;
625. $d1 = 2.05319162663775882187;
626. $d2 = 1.67638483018380384940;
627. $d3 = 6.89767334985100004550E-1;
628. $d4 = 1.48103976427480074590E-1;
629. $d5 = 1.51986665636164571966E-2;
630. $d6 = 5.47593808499534494600E-4;
631. $d7 = 1.05075007164441684324E-9;
633. // coefficients for p near 0 or 1.
634. $e0 = 6.65790464350110377720;
635. $e1 = 5.46378491116411436990;
636. $e2 = 1.78482653991729133580;
637. $e3 = 2.96560571828504891230E-1;
638. $e4 = 2.65321895265761230930E-2;
639. $e5 = 1.24266094738807843860E-3;
640. $e6 = 2.71155556874348757815E-5;
641. $e7 = 2.01033439929228813265E-7;
643. $f1 = 5.99832206555887937690E-1;
644. $f2 = 1.36929880922735805310E-1;
645. $f3 = 1.48753612908506148525E-2;
646. $f4 = 7.86869131145613259100E-4;
647. $f5 = 1.84631831751005468180E-5;
648. $f6 = 1.42151175831644588870E-7;
649. $f7 = 2.04426310338993978564E-15;
651. $q = $p - 0.5;
653. // computation for p close to 0.5
654. if ( abs($q) <= split1) {
655. $R = $const1 - $q * $q;
656. $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
657. ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
658. } else {
659. if ($q < 0) {
660. $R = $p;
661. } else {
662. $R = 1 - $p;
663. }
664. $R = pow(-log($R),2);
666. // computation for p not close to 0, 0.5 or 1.
667. If ($R <= $split2) {
668. $R = $R - $const2;
669. $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
670. ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
671. } else {
672. // computation for p near 0 or 1.
673. $R = $R - $split2;
674. $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
675. ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
676. }
677. if ($q < 0) {
678. $z = -$z;
679. }
680. }
681. return $z;
682. } // function _inverse_ncdf3()
685. /**
686. * AVEDEV
687. *
688. * Returns the average of the absolute deviations of data points from their mean.
689. * AVEDEV is a measure of the variability in a data set.
690. *
691. * Excel Function:
692. * AVEDEV(value1[,value2[, ...]])
693. *
694. * @access public
695. * @category Statistical Functions
696. * @param mixed $arg,... Data values
697. * @return float
698. */
699. public static function AVEDEV() {
700. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
702. // Return value
703. $returnValue = null;
705. $aMean = self:: AVERAGE($aArgs);
706. if ($aMean != PHPExcel_Calculation_Functions:: DIV0()) {
707. $aCount = 0;
708. foreach ($aArgs as $k => $arg) {
709. if (( is_bool($arg)) &&
710. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
711. $arg = (integer) $arg;
712. }
713. // Is it a numeric value?
714. if (( is_numeric($arg)) && (! is_string($arg))) {
715. if ( is_null($returnValue)) {
716. $returnValue = abs($arg - $aMean);
717. } else {
718. $returnValue += abs($arg - $aMean);
719. }
720. ++$aCount;
721. }
722. }
724. // Return
725. if ($aCount == 0) {
726. return PHPExcel_Calculation_Functions:: DIV0();
727. }
728. return $returnValue / $aCount;
729. }
730. return PHPExcel_Calculation_Functions:: NaN();
731. } // function AVEDEV()
734. /**
735. * AVERAGE
736. *
737. * Returns the average (arithmetic mean) of the arguments
738. *
739. * Excel Function:
740. * AVERAGE(value1[,value2[, ...]])
741. *
742. * @access public
743. * @category Statistical Functions
744. * @param mixed $arg,... Data values
745. * @return float
746. */
747. public static function AVERAGE() {
748. $returnValue = $aCount = 0;
750. // Loop through arguments
751. foreach ( PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args()) as $k => $arg) {
752. if (( is_bool($arg)) &&
753. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
754. $arg = (integer) $arg;
755. }
756. // Is it a numeric value?
757. if (( is_numeric($arg)) && (! is_string($arg))) {
758. if ( is_null($returnValue)) {
759. $returnValue = $arg;
760. } else {
761. $returnValue += $arg;
762. }
763. ++$aCount;
764. }
765. }
767. // Return
768. if ($aCount > 0) {
769. return $returnValue / $aCount;
770. } else {
771. return PHPExcel_Calculation_Functions:: DIV0();
772. }
773. } // function AVERAGE()
776. /**
777. * AVERAGEA
778. *
779. * Returns the average of its arguments, including numbers, text, and logical values
780. *
781. * Excel Function:
782. * AVERAGEA(value1[,value2[, ...]])
783. *
784. * @access public
785. * @category Statistical Functions
786. * @param mixed $arg,... Data values
787. * @return float
788. */
789. public static function AVERAGEA() {
790. // Return value
791. $returnValue = null;
793. $aCount = 0;
794. // Loop through arguments
795. foreach ( PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args()) as $k => $arg) {
796. if (( is_bool($arg)) &&
797. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
798. } else {
799. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) && ($arg != '')))) {
800. if ( is_bool($arg)) {
801. $arg = (integer) $arg;
802. } elseif ( is_string($arg)) {
803. $arg = 0;
804. }
805. if ( is_null($returnValue)) {
806. $returnValue = $arg;
807. } else {
808. $returnValue += $arg;
809. }
810. ++$aCount;
811. }
812. }
813. }
815. // Return
816. if ($aCount > 0) {
817. return $returnValue / $aCount;
818. } else {
819. return PHPExcel_Calculation_Functions:: DIV0();
820. }
821. } // function AVERAGEA()
824. /**
825. * AVERAGEIF
826. *
827. * Returns the average value from a range of cells that contain numbers within the list of arguments
828. *
829. * Excel Function:
830. * AVERAGEIF(value1[,value2[, ...]],condition)
831. *
832. * @access public
833. * @category Mathematical and Trigonometric Functions
834. * @param mixed $arg,... Data values
835. * @param string $condition The criteria that defines which cells will be checked.
836. * @return float
837. */
838. public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
839. // Return value
840. $returnValue = 0;
842. $aArgs = PHPExcel_Calculation_Functions:: flattenArray($aArgs);
843. $averageArgs = PHPExcel_Calculation_Functions:: flattenArray($averageArgs);
844. if ( count($averageArgs) == 0) {
845. $averageArgs = $aArgs;
846. }
847. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
848. // Loop through arguments
849. $aCount = 0;
850. foreach ($aArgs as $key => $arg) {
851. if (! is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult( strtoupper($arg)); }
852. $testCondition = '='.$arg.$condition;
853. if ( PHPExcel_Calculation:: getInstance()->_calculateFormulaValue($testCondition)) {
854. if (( is_null($returnValue)) || ($arg > $returnValue)) {
855. $returnValue += $arg;
856. ++$aCount;
857. }
858. }
859. }
861. // Return
862. if ($aCount > 0) {
863. return $returnValue / $aCount;
864. } else {
865. return PHPExcel_Calculation_Functions:: DIV0();
866. }
867. } // function AVERAGEIF()
870. /**
871. * BETADIST
872. *
873. * Returns the beta distribution.
874. *
875. * @param float $value Value at which you want to evaluate the distribution
876. * @param float $alpha Parameter to the distribution
877. * @param float $beta Parameter to the distribution
878. * @param boolean $cumulative
879. * @return float
880. *
881. */
882. public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
883. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
884. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
885. $beta = PHPExcel_Calculation_Functions:: flattenSingleValue($beta);
886. $rMin = PHPExcel_Calculation_Functions:: flattenSingleValue($rMin);
887. $rMax = PHPExcel_Calculation_Functions:: flattenSingleValue($rMax);
889. if (( is_numeric($value)) && ( is_numeric($alpha)) && ( is_numeric($beta)) && ( is_numeric($rMin)) && ( is_numeric($rMax))) {
890. if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
891. return PHPExcel_Calculation_Functions:: NaN();
892. }
893. if ($rMin > $rMax) {
894. $tmp = $rMin;
895. $rMin = $rMax;
896. $rMax = $tmp;
897. }
898. $value -= $rMin;
899. $value /= ($rMax - $rMin);
900. return self::_incompleteBeta($value,$alpha,$beta);
901. }
902. return PHPExcel_Calculation_Functions:: VALUE();
903. } // function BETADIST()
906. /**
907. * BETAINV
908. *
909. * Returns the inverse of the beta distribution.
910. *
911. * @param float $probability Probability at which you want to evaluate the distribution
912. * @param float $alpha Parameter to the distribution
913. * @param float $beta Parameter to the distribution
914. * @param boolean $cumulative
915. * @return float
916. *
917. */
918. public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
919. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
920. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
921. $beta = PHPExcel_Calculation_Functions:: flattenSingleValue($beta);
922. $rMin = PHPExcel_Calculation_Functions:: flattenSingleValue($rMin);
923. $rMax = PHPExcel_Calculation_Functions:: flattenSingleValue($rMax);
925. if (( is_numeric($probability)) && ( is_numeric($alpha)) && ( is_numeric($beta)) && ( is_numeric($rMin)) && ( is_numeric($rMax))) {
926. if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
927. return PHPExcel_Calculation_Functions:: NaN();
928. }
929. if ($rMin > $rMax) {
930. $tmp = $rMin;
931. $rMin = $rMax;
932. $rMax = $tmp;
933. }
934. $a = 0;
935. $b = 2;
937. $i = 0;
938. while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
939. $guess = ($a + $b) / 2;
940. $result = self:: BETADIST($guess, $alpha, $beta);
941. if (($result == $probability) || ($result == 0)) {
942. $b = $a;
943. } elseif ($result > $probability) {
944. $b = $guess;
945. } else {
946. $a = $guess;
947. }
948. }
949. if ($i == MAX_ITERATIONS) {
950. return PHPExcel_Calculation_Functions:: NA();
951. }
952. return round($rMin + $guess * ($rMax - $rMin),12);
953. }
954. return PHPExcel_Calculation_Functions:: VALUE();
955. } // function BETAINV()
958. /**
959. * BINOMDIST
960. *
961. * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
962. * a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
963. * when trials are independent, and when the probability of success is constant throughout the
964. * experiment. For example, BINOMDIST can calculate the probability that two of the next three
965. * babies born are male.
966. *
967. * @param float $value Number of successes in trials
968. * @param float $trials Number of trials
969. * @param float $probability Probability of success on each trial
970. * @param boolean $cumulative
971. * @return float
972. *
973. * @todo Cumulative distribution function
974. *
975. */
976. public static function BINOMDIST($value, $trials, $probability, $cumulative) {
977. $value = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($value));
978. $trials = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($trials));
979. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
981. if (( is_numeric($value)) && ( is_numeric($trials)) && ( is_numeric($probability))) {
982. if (($value < 0) || ($value > $trials)) {
983. return PHPExcel_Calculation_Functions:: NaN();
984. }
985. if (($probability < 0) || ($probability > 1)) {
986. return PHPExcel_Calculation_Functions:: NaN();
987. }
988. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
989. if ($cumulative) {
990. $summer = 0;
991. for ($i = 0; $i <= $value; ++$i) {
992. $summer += PHPExcel_Calculation_MathTrig:: COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
993. }
994. return $summer;
995. } else {
996. return PHPExcel_Calculation_MathTrig:: COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
997. }
998. }
999. }
1000. return PHPExcel_Calculation_Functions:: VALUE();
1001. } // function BINOMDIST()
1004. /**
1005. * CHIDIST
1006. *
1007. * Returns the one-tailed probability of the chi-squared distribution.
1008. *
1009. * @param float $value Value for the function
1010. * @param float $degrees degrees of freedom
1011. * @return float
1012. */
1013. public static function CHIDIST($value, $degrees) {
1014. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1015. $degrees = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($degrees));
1017. if (( is_numeric($value)) && ( is_numeric($degrees))) {
1018. if ($degrees < 1) {
1019. return PHPExcel_Calculation_Functions:: NaN();
1020. }
1021. if ($value < 0) {
1022. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) {
1023. return 1;
1024. }
1025. return PHPExcel_Calculation_Functions:: NaN();
1026. }
1027. return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
1028. }
1029. return PHPExcel_Calculation_Functions:: VALUE();
1030. } // function CHIDIST()
1033. /**
1034. * CHIINV
1035. *
1036. * Returns the one-tailed probability of the chi-squared distribution.
1037. *
1038. * @param float $probability Probability for the function
1039. * @param float $degrees degrees of freedom
1040. * @return float
1041. */
1042. public static function CHIINV($probability, $degrees) {
1043. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
1044. $degrees = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($degrees));
1046. if (( is_numeric($probability)) && ( is_numeric($degrees))) {
1048. $xLo = 100;
1049. $xHi = 0;
1051. $x = $xNew = 1;
1052. $dx = 1;
1053. $i = 0;
1055. while (( abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1056. // Apply Newton-Raphson step
1057. $result = self:: CHIDIST($x, $degrees);
1058. $error = $result - $probability;
1059. if ($error == 0.0) {
1060. $dx = 0;
1061. } elseif ($error < 0.0) {
1062. $xLo = $x;
1063. } else {
1064. $xHi = $x;
1065. }
1066. // Avoid division by zero
1067. if ($result != 0.0) {
1068. $dx = $error / $result;
1069. $xNew = $x - $dx;
1070. }
1071. // If the NR fails to converge (which for example may be the
1072. // case if the initial guess is too rough) we apply a bisection
1073. // step to determine a more narrow interval around the root.
1074. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
1075. $xNew = ($xLo + $xHi) / 2;
1076. $dx = $xNew - $x;
1077. }
1078. $x = $xNew;
1079. }
1080. if ($i == MAX_ITERATIONS) {
1081. return PHPExcel_Calculation_Functions:: NA();
1082. }
1083. return round($x,12);
1084. }
1085. return PHPExcel_Calculation_Functions:: VALUE();
1086. } // function CHIINV()
1089. /**
1090. * CONFIDENCE
1091. *
1092. * Returns the confidence interval for a population mean
1093. *
1094. * @param float $alpha
1095. * @param float $stdDev Standard Deviation
1096. * @param float $size
1097. * @return float
1098. *
1099. */
1100. public static function CONFIDENCE($alpha,$stdDev,$size) {
1101. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
1102. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
1103. $size = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($size));
1105. if (( is_numeric($alpha)) && ( is_numeric($stdDev)) && ( is_numeric($size))) {
1106. if (($alpha <= 0) || ($alpha >= 1)) {
1107. return PHPExcel_Calculation_Functions:: NaN();
1108. }
1109. if (($stdDev <= 0) || ($size < 1)) {
1110. return PHPExcel_Calculation_Functions:: NaN();
1111. }
1112. return self:: NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
1113. }
1114. return PHPExcel_Calculation_Functions:: VALUE();
1115. } // function CONFIDENCE()
1118. /**
1119. * CORREL
1120. *
1121. * Returns covariance, the average of the products of deviations for each data point pair.
1122. *
1123. * @param array of mixed Data Series Y
1124. * @param array of mixed Data Series X
1125. * @return float
1126. */
1127. public static function CORREL($yValues,$xValues=null) {
1128. if (( is_null($xValues)) || (! is_array($yValues)) || (! is_array($xValues))) {
1129. return PHPExcel_Calculation_Functions:: VALUE();
1130. }
1131. if (! self::_checkTrendArrays($yValues,$xValues)) {
1132. return PHPExcel_Calculation_Functions:: VALUE();
1133. }
1134. $yValueCount = count($yValues);
1135. $xValueCount = count($xValues);
1137. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1138. return PHPExcel_Calculation_Functions:: NA();
1139. } elseif ($yValueCount == 1) {
1140. return PHPExcel_Calculation_Functions:: DIV0();
1141. }
1143. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
1144. return $bestFitLinear->getCorrelation();
1145. } // function CORREL()
1148. /**
1149. * COUNT
1150. *
1151. * Counts the number of cells that contain numbers within the list of arguments
1152. *
1153. * Excel Function:
1154. * COUNT(value1[,value2[, ...]])
1155. *
1156. * @access public
1157. * @category Statistical Functions
1158. * @param mixed $arg,... Data values
1159. * @return int
1160. */
1161. public static function COUNT() {
1162. // Return value
1163. $returnValue = 0;
1165. // Loop through arguments
1166. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
1167. foreach ($aArgs as $k => $arg) {
1168. if (( is_bool($arg)) &&
1169. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
1170. $arg = (integer) $arg;
1171. }
1172. // Is it a numeric value?
1173. if (( is_numeric($arg)) && (! is_string($arg))) {
1174. ++$returnValue;
1175. }
1176. }
1178. // Return
1179. return $returnValue;
1180. } // function COUNT()
1183. /**
1184. * COUNTA
1185. *
1186. * Counts the number of cells that are not empty within the list of arguments
1187. *
1188. * Excel Function:
1189. * COUNTA(value1[,value2[, ...]])
1190. *
1191. * @access public
1192. * @category Statistical Functions
1193. * @param mixed $arg,... Data values
1194. * @return int
1195. */
1196. public static function COUNTA() {
1197. // Return value
1198. $returnValue = 0;
1200. // Loop through arguments
1201. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1202. foreach ($aArgs as $arg) {
1203. // Is it a numeric, boolean or string value?
1204. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) && ($arg != '')))) {
1205. ++$returnValue;
1206. }
1207. }
1209. // Return
1210. return $returnValue;
1211. } // function COUNTA()
1214. /**
1215. * COUNTBLANK
1216. *
1217. * Counts the number of empty cells within the list of arguments
1218. *
1219. * Excel Function:
1220. * COUNTBLANK(value1[,value2[, ...]])
1221. *
1222. * @access public
1223. * @category Statistical Functions
1224. * @param mixed $arg,... Data values
1225. * @return int
1226. */
1227. public static function COUNTBLANK() {
1228. // Return value
1229. $returnValue = 0;
1231. // Loop through arguments
1232. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1233. foreach ($aArgs as $arg) {
1234. // Is it a blank cell?
1235. if (( is_null($arg)) || (( is_string($arg)) && ($arg == ''))) {
1236. ++$returnValue;
1237. }
1238. }
1240. // Return
1241. return $returnValue;
1242. } // function COUNTBLANK()
1245. /**
1246. * COUNTIF
1247. *
1248. * Counts the number of cells that contain numbers within the list of arguments
1249. *
1250. * Excel Function:
1251. * COUNTIF(value1[,value2[, ...]],condition)
1252. *
1253. * @access public
1254. * @category Statistical Functions
1255. * @param mixed $arg,... Data values
1256. * @param string $condition The criteria that defines which cells will be counted.
1257. * @return int
1258. */
1259. public static function COUNTIF($aArgs,$condition) {
1260. // Return value
1261. $returnValue = 0;
1263. $aArgs = PHPExcel_Calculation_Functions:: flattenArray($aArgs);
1264. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1265. // Loop through arguments
1266. foreach ($aArgs as $arg) {
1267. if (! is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult( strtoupper($arg)); }
1268. $testCondition = '='.$arg.$condition;
1269. if ( PHPExcel_Calculation:: getInstance()->_calculateFormulaValue($testCondition)) {
1270. // Is it a value within our criteria
1271. ++$returnValue;
1272. }
1273. }
1275. // Return
1276. return $returnValue;
1277. } // function COUNTIF()
1280. /**
1281. * COVAR
1282. *
1283. * Returns covariance, the average of the products of deviations for each data point pair.
1284. *
1285. * @param array of mixed Data Series Y
1286. * @param array of mixed Data Series X
1287. * @return float
1288. */
1289. public static function COVAR($yValues,$xValues) {
1290. if (! self::_checkTrendArrays($yValues,$xValues)) {
1291. return PHPExcel_Calculation_Functions:: VALUE();
1292. }
1293. $yValueCount = count($yValues);
1294. $xValueCount = count($xValues);
1296. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1297. return PHPExcel_Calculation_Functions:: NA();
1298. } elseif ($yValueCount == 1) {
1299. return PHPExcel_Calculation_Functions:: DIV0();
1300. }
1302. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
1303. return $bestFitLinear->getCovariance();
1304. } // function COVAR()
1307. /**
1308. * CRITBINOM
1309. *
1310. * Returns the smallest value for which the cumulative binomial distribution is greater
1311. * than or equal to a criterion value
1312. *
1313. * See http://support.microsoft.com/kb/828117/ for details of the algorithm used
1314. *
1315. * @param float $trials number of Bernoulli trials
1316. * @param float $probability probability of a success on each trial
1317. * @param float $alpha criterion value
1318. * @return int
1319. *
1320. * @todo Warning. This implementation differs from the algorithm detailed on the MS
1321. * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
1322. * This eliminates a potential endless loop error, but may have an adverse affect on the
1323. * accuracy of the function (although all my tests have so far returned correct results).
1324. *
1325. */
1326. public static function CRITBINOM($trials, $probability, $alpha) {
1327. $trials = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($trials));
1328. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
1329. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
1331. if (( is_numeric($trials)) && ( is_numeric($probability)) && ( is_numeric($alpha))) {
1332. if ($trials < 0) {
1333. return PHPExcel_Calculation_Functions:: NaN();
1334. }
1335. if (($probability < 0) || ($probability > 1)) {
1336. return PHPExcel_Calculation_Functions:: NaN();
1337. }
1338. if (($alpha < 0) || ($alpha > 1)) {
1339. return PHPExcel_Calculation_Functions:: NaN();
1340. }
1341. if ($alpha <= 0.5) {
1342. $t = sqrt( log(1 / ($alpha * $alpha)));
1343. $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));
1344. } else {
1345. $t = sqrt( log(1 / pow(1 - $alpha,2)));
1346. $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
1347. }
1348. $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
1349. if ($Guess < 0) {
1350. $Guess = 0;
1351. } elseif ($Guess > $trials) {
1352. $Guess = $trials;
1353. }
1355. $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
1356. $EssentiallyZero = 10e-12;
1358. $m = floor($trials * $probability);
1359. ++$TotalUnscaledProbability;
1360. if ($m == $Guess) { ++$UnscaledPGuess; }
1361. if ($m <= $Guess) { ++$UnscaledCumPGuess; }
1363. $PreviousValue = 1;
1364. $Done = False;
1365. $k = $m + 1;
1366. while ((!$Done) && ($k <= $trials)) {
1367. $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
1368. $TotalUnscaledProbability += $CurrentValue;
1369. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1370. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1371. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1372. $PreviousValue = $CurrentValue;
1373. ++$k;
1374. }
1376. $PreviousValue = 1;
1377. $Done = False;
1378. $k = $m - 1;
1379. while ((!$Done) && ($k >= 0)) {
1380. $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
1381. $TotalUnscaledProbability += $CurrentValue;
1382. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
1383. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
1384. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
1385. $PreviousValue = $CurrentValue;
1386. --$k;
1387. }
1389. $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
1390. $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
1392. // $CumPGuessMinus1 = $CumPGuess - $PGuess;
1393. $CumPGuessMinus1 = $CumPGuess - 1;
1395. while (True) {
1396. if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
1397. return $Guess;
1398. } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
1399. $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
1400. $CumPGuessMinus1 = $CumPGuess;
1401. $CumPGuess = $CumPGuess + $PGuessPlus1;
1402. $PGuess = $PGuessPlus1;
1403. ++$Guess;
1404. } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
1405. $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
1406. $CumPGuess = $CumPGuessMinus1;
1407. $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
1408. $PGuess = $PGuessMinus1;
1409. --$Guess;
1410. }
1411. }
1412. }
1413. return PHPExcel_Calculation_Functions:: VALUE();
1414. } // function CRITBINOM()
1417. /**
1418. * DEVSQ
1419. *
1420. * Returns the sum of squares of deviations of data points from their sample mean.
1421. *
1422. * Excel Function:
1423. * DEVSQ(value1[,value2[, ...]])
1424. *
1425. * @access public
1426. * @category Statistical Functions
1427. * @param mixed $arg,... Data values
1428. * @return float
1429. */
1430. public static function DEVSQ() {
1431. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
1433. // Return value
1434. $returnValue = null;
1436. $aMean = self:: AVERAGE($aArgs);
1437. if ($aMean != PHPExcel_Calculation_Functions:: DIV0()) {
1438. $aCount = -1;
1439. foreach ($aArgs as $k => $arg) {
1440. // Is it a numeric value?
1441. if (( is_bool($arg)) &&
1442. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
1443. $arg = (integer) $arg;
1444. }
1445. if (( is_numeric($arg)) && (! is_string($arg))) {
1446. if ( is_null($returnValue)) {
1447. $returnValue = pow(($arg - $aMean),2);
1448. } else {
1449. $returnValue += pow(($arg - $aMean),2);
1450. }
1451. ++$aCount;
1452. }
1453. }
1455. // Return
1456. if ( is_null($returnValue)) {
1457. return PHPExcel_Calculation_Functions:: NaN();
1458. } else {
1459. return $returnValue;
1460. }
1461. }
1462. return self:: NA();
1463. } // function DEVSQ()
1466. /**
1467. * EXPONDIST
1468. *
1469. * Returns the exponential distribution. Use EXPONDIST to model the time between events,
1470. * such as how long an automated bank teller takes to deliver cash. For example, you can
1471. * use EXPONDIST to determine the probability that the process takes at most 1 minute.
1472. *
1473. * @param float $value Value of the function
1474. * @param float $lambda The parameter value
1475. * @param boolean $cumulative
1476. * @return float
1477. */
1478. public static function EXPONDIST($value, $lambda, $cumulative) {
1479. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1480. $lambda = PHPExcel_Calculation_Functions:: flattenSingleValue($lambda);
1481. $cumulative = PHPExcel_Calculation_Functions:: flattenSingleValue($cumulative);
1483. if (( is_numeric($value)) && ( is_numeric($lambda))) {
1484. if (($value < 0) || ($lambda < 0)) {
1485. return PHPExcel_Calculation_Functions:: NaN();
1486. }
1487. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
1488. if ($cumulative) {
1489. return 1 - exp(0-$value*$lambda);
1490. } else {
1491. return $lambda * exp(0-$value*$lambda);
1492. }
1493. }
1494. }
1495. return PHPExcel_Calculation_Functions:: VALUE();
1496. } // function EXPONDIST()
1499. /**
1500. * FISHER
1501. *
1502. * Returns the Fisher transformation at x. This transformation produces a function that
1503. * is normally distributed rather than skewed. Use this function to perform hypothesis
1504. * testing on the correlation coefficient.
1505. *
1506. * @param float $value
1507. * @return float
1508. */
1509. public static function FISHER($value) {
1510. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1512. if ( is_numeric($value)) {
1513. if (($value <= -1) || ($value >= 1)) {
1514. return PHPExcel_Calculation_Functions:: NaN();
1515. }
1516. return 0.5 * log((1+$value)/(1-$value));
1517. }
1518. return PHPExcel_Calculation_Functions:: VALUE();
1519. } // function FISHER()
1522. /**
1523. * FISHERINV
1524. *
1525. * Returns the inverse of the Fisher transformation. Use this transformation when
1526. * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
1527. * FISHERINV(y) = x.
1528. *
1529. * @param float $value
1530. * @return float
1531. */
1532. public static function FISHERINV($value) {
1533. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1535. if ( is_numeric($value)) {
1536. return ( exp(2 * $value) - 1) / ( exp(2 * $value) + 1);
1537. }
1538. return PHPExcel_Calculation_Functions:: VALUE();
1539. } // function FISHERINV()
1542. /**
1543. * FORECAST
1544. *
1545. * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.
1546. *
1547. * @param float Value of X for which we want to find Y
1548. * @param array of mixed Data Series Y
1549. * @param array of mixed Data Series X
1550. * @return float
1551. */
1552. public static function FORECAST($xValue,$yValues,$xValues) {
1553. $xValue = PHPExcel_Calculation_Functions:: flattenSingleValue($xValue);
1554. if (! is_numeric($xValue)) {
1555. return PHPExcel_Calculation_Functions:: VALUE();
1556. }
1558. if (! self::_checkTrendArrays($yValues,$xValues)) {
1559. return PHPExcel_Calculation_Functions:: VALUE();
1560. }
1561. $yValueCount = count($yValues);
1562. $xValueCount = count($xValues);
1564. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1565. return PHPExcel_Calculation_Functions:: NA();
1566. } elseif ($yValueCount == 1) {
1567. return PHPExcel_Calculation_Functions:: DIV0();
1568. }
1570. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
1571. return $bestFitLinear->getValueOfYForX($xValue);
1572. } // function FORECAST()
1575. /**
1576. * GAMMADIST
1577. *
1578. * Returns the gamma distribution.
1579. *
1580. * @param float $value Value at which you want to evaluate the distribution
1581. * @param float $a Parameter to the distribution
1582. * @param float $b Parameter to the distribution
1583. * @param boolean $cumulative
1584. * @return float
1585. *
1586. */
1587. public static function GAMMADIST($value,$a,$b,$cumulative) {
1588. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1589. $a = PHPExcel_Calculation_Functions:: flattenSingleValue($a);
1590. $b = PHPExcel_Calculation_Functions:: flattenSingleValue($b);
1592. if (( is_numeric($value)) && ( is_numeric($a)) && ( is_numeric($b))) {
1593. if (($value < 0) || ($a <= 0) || ($b <= 0)) {
1594. return PHPExcel_Calculation_Functions:: NaN();
1595. }
1596. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
1597. if ($cumulative) {
1598. return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
1599. } else {
1600. return (1 / ( pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
1601. }
1602. }
1603. }
1604. return PHPExcel_Calculation_Functions:: VALUE();
1605. } // function GAMMADIST()
1608. /**
1609. * GAMMAINV
1610. *
1611. * Returns the inverse of the beta distribution.
1612. *
1613. * @param float $probability Probability at which you want to evaluate the distribution
1614. * @param float $alpha Parameter to the distribution
1615. * @param float $beta Parameter to the distribution
1616. * @return float
1617. *
1618. */
1619. public static function GAMMAINV($probability,$alpha,$beta) {
1620. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
1621. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
1622. $beta = PHPExcel_Calculation_Functions:: flattenSingleValue($beta);
1624. if (( is_numeric($probability)) && ( is_numeric($alpha)) && ( is_numeric($beta))) {
1625. if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
1626. return PHPExcel_Calculation_Functions:: NaN();
1627. }
1629. $xLo = 0;
1630. $xHi = $alpha * $beta * 5;
1632. $x = $xNew = 1;
1633. $error = $pdf = 0;
1634. $dx = 1024;
1635. $i = 0;
1637. while (( abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
1638. // Apply Newton-Raphson step
1639. $error = self:: GAMMADIST($x, $alpha, $beta, True) - $probability;
1640. if ($error < 0.0) {
1641. $xLo = $x;
1642. } else {
1643. $xHi = $x;
1644. }
1645. $pdf = self:: GAMMADIST($x, $alpha, $beta, False);
1646. // Avoid division by zero
1647. if ($pdf != 0.0) {
1648. $dx = $error / $pdf;
1649. $xNew = $x - $dx;
1650. }
1651. // If the NR fails to converge (which for example may be the
1652. // case if the initial guess is too rough) we apply a bisection
1653. // step to determine a more narrow interval around the root.
1654. if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) {
1655. $xNew = ($xLo + $xHi) / 2;
1656. $dx = $xNew - $x;
1657. }
1658. $x = $xNew;
1659. }
1660. if ($i == MAX_ITERATIONS) {
1661. return PHPExcel_Calculation_Functions:: NA();
1662. }
1663. return $x;
1664. }
1665. return PHPExcel_Calculation_Functions:: VALUE();
1666. } // function GAMMAINV()
1669. /**
1670. * GAMMALN
1671. *
1672. * Returns the natural logarithm of the gamma function.
1673. *
1674. * @param float $value
1675. * @return float
1676. */
1677. public static function GAMMALN($value) {
1678. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1680. if ( is_numeric($value)) {
1681. if ($value <= 0) {
1682. return PHPExcel_Calculation_Functions:: NaN();
1683. }
1684. return log(self::_gamma($value));
1685. }
1686. return PHPExcel_Calculation_Functions:: VALUE();
1687. } // function GAMMALN()
1690. /**
1691. * GEOMEAN
1692. *
1693. * Returns the geometric mean of an array or range of positive data. For example, you
1694. * can use GEOMEAN to calculate average growth rate given compound interest with
1695. * variable rates.
1696. *
1697. * Excel Function:
1698. * GEOMEAN(value1[,value2[, ...]])
1699. *
1700. * @access public
1701. * @category Statistical Functions
1702. * @param mixed $arg,... Data values
1703. * @return float
1704. */
1705. public static function GEOMEAN() {
1706. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1708. $aMean = PHPExcel_Calculation_MathTrig:: PRODUCT($aArgs);
1709. if ( is_numeric($aMean) && ($aMean > 0)) {
1710. $aCount = self:: COUNT($aArgs) ;
1711. if (self:: MIN($aArgs) > 0) {
1712. return pow($aMean, (1 / $aCount));
1713. }
1714. }
1715. return PHPExcel_Calculation_Functions:: NaN();
1716. } // GEOMEAN()
1719. /**
1720. * GROWTH
1721. *
1722. * Returns values along a predicted emponential trend
1723. *
1724. * @param array of mixed Data Series Y
1725. * @param array of mixed Data Series X
1726. * @param array of mixed Values of X for which we want to find Y
1727. * @param boolean A logical value specifying whether to force the intersect to equal 0.
1728. * @return array of float
1729. */
1730. public static function GROWTH($yValues,$xValues=array(),$newValues=array(),$const=True) {
1731. $yValues = PHPExcel_Calculation_Functions:: flattenArray($yValues);
1732. $xValues = PHPExcel_Calculation_Functions:: flattenArray($xValues);
1733. $newValues = PHPExcel_Calculation_Functions:: flattenArray($newValues);
1734. $const = ( is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($const);
1736. $bestFitExponential = trendClass:: calculate( trendClass:: TREND_EXPONENTIAL,$yValues,$xValues,$const);
1737. if ( count($newValues) == 0) {
1738. $newValues = $bestFitExponential->getXValues();
1739. }
1741. $returnArray = array();
1742. foreach($newValues as $xValue) {
1743. $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue);
1744. }
1746. return $returnArray;
1747. } // function GROWTH()
1750. /**
1751. * HARMEAN
1752. *
1753. * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the
1754. * arithmetic mean of reciprocals.
1755. *
1756. * Excel Function:
1757. * HARMEAN(value1[,value2[, ...]])
1758. *
1759. * @access public
1760. * @category Statistical Functions
1761. * @param mixed $arg,... Data values
1762. * @return float
1763. */
1764. public static function HARMEAN() {
1765. // Return value
1766. $returnValue = PHPExcel_Calculation_Functions:: NA();
1768. // Loop through arguments
1769. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1770. if (self:: MIN($aArgs) < 0) {
1771. return PHPExcel_Calculation_Functions:: NaN();
1772. }
1773. $aCount = 0;
1774. foreach ($aArgs as $arg) {
1775. // Is it a numeric value?
1776. if (( is_numeric($arg)) && (! is_string($arg))) {
1777. if ($arg <= 0) {
1778. return PHPExcel_Calculation_Functions:: NaN();
1779. }
1780. if ( is_null($returnValue)) {
1781. $returnValue = (1 / $arg);
1782. } else {
1783. $returnValue += (1 / $arg);
1784. }
1785. ++$aCount;
1786. }
1787. }
1789. // Return
1790. if ($aCount > 0) {
1791. return 1 / ($returnValue / $aCount);
1792. } else {
1793. return $returnValue;
1794. }
1795. } // function HARMEAN()
1798. /**
1799. * HYPGEOMDIST
1800. *
1801. * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of
1802. * sample successes, given the sample size, population successes, and population size.
1803. *
1804. * @param float $sampleSuccesses Number of successes in the sample
1805. * @param float $sampleNumber Size of the sample
1806. * @param float $populationSuccesses Number of successes in the population
1807. * @param float $populationNumber Population size
1808. * @return float
1809. *
1810. */
1811. public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
1812. $sampleSuccesses = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($sampleSuccesses));
1813. $sampleNumber = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($sampleNumber));
1814. $populationSuccesses = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($populationSuccesses));
1815. $populationNumber = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($populationNumber));
1817. if (( is_numeric($sampleSuccesses)) && ( is_numeric($sampleNumber)) && ( is_numeric($populationSuccesses)) && ( is_numeric($populationNumber))) {
1818. if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
1819. return PHPExcel_Calculation_Functions:: NaN();
1820. }
1821. if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
1822. return PHPExcel_Calculation_Functions:: NaN();
1823. }
1824. if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
1825. return PHPExcel_Calculation_Functions:: NaN();
1826. }
1827. return PHPExcel_Calculation_MathTrig:: COMBIN($populationSuccesses,$sampleSuccesses) *
1828. PHPExcel_Calculation_MathTrig:: COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
1829. PHPExcel_Calculation_MathTrig:: COMBIN($populationNumber,$sampleNumber);
1830. }
1831. return PHPExcel_Calculation_Functions:: VALUE();
1832. } // function HYPGEOMDIST()
1835. /**
1836. * INTERCEPT
1837. *
1838. * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
1839. *
1840. * @param array of mixed Data Series Y
1841. * @param array of mixed Data Series X
1842. * @return float
1843. */
1844. public static function INTERCEPT($yValues,$xValues) {
1845. if (! self::_checkTrendArrays($yValues,$xValues)) {
1846. return PHPExcel_Calculation_Functions:: VALUE();
1847. }
1848. $yValueCount = count($yValues);
1849. $xValueCount = count($xValues);
1851. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1852. return PHPExcel_Calculation_Functions:: NA();
1853. } elseif ($yValueCount == 1) {
1854. return PHPExcel_Calculation_Functions:: DIV0();
1855. }
1857. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
1858. return $bestFitLinear->getIntersect();
1859. } // function INTERCEPT()
1862. /**
1863. * KURT
1864. *
1865. * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness
1866. * or flatness of a distribution compared with the normal distribution. Positive
1867. * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a
1868. * relatively flat distribution.
1869. *
1870. * @param array Data Series
1871. * @return float
1872. */
1873. public static function KURT() {
1874. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
1875. $mean = self:: AVERAGE($aArgs);
1876. $stdDev = self:: STDEV($aArgs);
1878. if ($stdDev > 0) {
1879. $count = $summer = 0;
1880. // Loop through arguments
1881. foreach ($aArgs as $k => $arg) {
1882. if (( is_bool($arg)) &&
1883. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
1884. } else {
1885. // Is it a numeric value?
1886. if (( is_numeric($arg)) && (! is_string($arg))) {
1887. $summer += pow((($arg - $mean) / $stdDev),4) ;
1888. ++$count;
1889. }
1890. }
1891. }
1893. // Return
1894. if ($count > 3) {
1895. return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1,2) / (($count-2) * ($count-3)));
1896. }
1897. }
1898. return PHPExcel_Calculation_Functions:: DIV0();
1899. } // function KURT()
1902. /**
1903. * LARGE
1904. *
1905. * Returns the nth largest value in a data set. You can use this function to
1906. * select a value based on its relative standing.
1907. *
1908. * Excel Function:
1909. * LARGE(value1[,value2[, ...]],entry)
1910. *
1911. * @access public
1912. * @category Statistical Functions
1913. * @param mixed $arg,... Data values
1914. * @param int $entry Position (ordered from the largest) in the array or range of data to return
1915. * @return float
1916. *
1917. */
1918. public static function LARGE() {
1919. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1921. // Calculate
1922. $entry = floor( array_pop($aArgs));
1924. if (( is_numeric($entry)) && (! is_string($entry))) {
1925. $mArgs = array();
1926. foreach ($aArgs as $arg) {
1927. // Is it a numeric value?
1928. if (( is_numeric($arg)) && (! is_string($arg))) {
1929. $mArgs[] = $arg;
1930. }
1931. }
1932. $count = self:: COUNT($mArgs);
1933. $entry = floor(--$entry);
1934. if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
1935. return PHPExcel_Calculation_Functions:: NaN();
1936. }
1937. rsort($mArgs);
1938. return $mArgs[$entry];
1939. }
1940. return PHPExcel_Calculation_Functions:: VALUE();
1941. } // function LARGE()
1944. /**
1945. * LINEST
1946. *
1947. * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data,
1948. * and then returns an array that describes the line.
1949. *
1950. * @param array of mixed Data Series Y
1951. * @param array of mixed Data Series X
1952. * @param boolean A logical value specifying whether to force the intersect to equal 0.
1953. * @param boolean A logical value specifying whether to return additional regression statistics.
1954. * @return array
1955. */
1956. public static function LINEST($yValues,$xValues=null,$const=True,$stats=False) {
1957. $const = ( is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($const);
1958. $stats = ( is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($stats);
1959. if ( is_null($xValues)) $xValues = range(1, count( PHPExcel_Calculation_Functions:: flattenArray($yValues)));
1961. if (! self::_checkTrendArrays($yValues,$xValues)) {
1962. return PHPExcel_Calculation_Functions:: VALUE();
1963. }
1964. $yValueCount = count($yValues);
1965. $xValueCount = count($xValues);
1968. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
1969. return PHPExcel_Calculation_Functions:: NA();
1970. } elseif ($yValueCount == 1) {
1971. return 0;
1972. }
1974. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues,$const);
1975. if ($stats) {
1976. return array( array( $bestFitLinear->getSlope(),
1977. $bestFitLinear->getSlopeSE(),
1978. $bestFitLinear->getGoodnessOfFit(),
1979. $bestFitLinear->getF(),
1980. $bestFitLinear->getSSRegression(),
1981. ),
1982. array( $bestFitLinear->getIntersect(),
1983. $bestFitLinear->getIntersectSE(),
1984. $bestFitLinear->getStdevOfResiduals(),
1985. $bestFitLinear->getDFResiduals(),
1986. $bestFitLinear->getSSResiduals()
1987. )
1988. );
1989. } else {
1990. return array( $bestFitLinear->getSlope(),
1991. $bestFitLinear->getIntersect()
1992. );
1993. }
1994. } // function LINEST()
1997. /**
1998. * LOGEST
1999. *
2000. * Calculates an exponential curve that best fits the X and Y data series,
2001. * and then returns an array that describes the line.
2002. *
2003. * @param array of mixed Data Series Y
2004. * @param array of mixed Data Series X
2005. * @param boolean A logical value specifying whether to force the intersect to equal 0.
2006. * @param boolean A logical value specifying whether to return additional regression statistics.
2007. * @return array
2008. */
2009. public static function LOGEST($yValues,$xValues=null,$const=True,$stats=False) {
2010. $const = ( is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($const);
2011. $stats = ( is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($stats);
2012. if ( is_null($xValues)) $xValues = range(1, count( PHPExcel_Calculation_Functions:: flattenArray($yValues)));
2014. if (! self::_checkTrendArrays($yValues,$xValues)) {
2015. return PHPExcel_Calculation_Functions:: VALUE();
2016. }
2017. $yValueCount = count($yValues);
2018. $xValueCount = count($xValues);
2020. foreach($yValues as $value) {
2021. if ($value <= 0.0) {
2022. return PHPExcel_Calculation_Functions:: NaN();
2023. }
2024. }
2027. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2028. return PHPExcel_Calculation_Functions:: NA();
2029. } elseif ($yValueCount == 1) {
2030. return 1;
2031. }
2033. $bestFitExponential = trendClass:: calculate( trendClass:: TREND_EXPONENTIAL,$yValues,$xValues,$const);
2034. if ($stats) {
2035. return array( array( $bestFitExponential->getSlope(),
2036. $bestFitExponential->getSlopeSE(),
2037. $bestFitExponential->getGoodnessOfFit(),
2038. $bestFitExponential->getF(),
2039. $bestFitExponential->getSSRegression(),
2040. ),
2041. array( $bestFitExponential->getIntersect(),
2042. $bestFitExponential->getIntersectSE(),
2043. $bestFitExponential->getStdevOfResiduals(),
2044. $bestFitExponential->getDFResiduals(),
2045. $bestFitExponential->getSSResiduals()
2046. )
2047. );
2048. } else {
2049. return array( $bestFitExponential->getSlope(),
2050. $bestFitExponential->getIntersect()
2051. );
2052. }
2053. } // function LOGEST()
2056. /**
2057. * LOGINV
2058. *
2059. * Returns the inverse of the normal cumulative distribution
2060. *
2061. * @param float $value
2062. * @return float
2063. *
2064. * @todo Try implementing P J Acklam's refinement algorithm for greater
2065. * accuracy if I can get my head round the mathematics
2066. * (as described at) http://home.online.no/~pjacklam/notes/invnorm/
2067. */
2068. public static function LOGINV($probability, $mean, $stdDev) {
2069. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
2070. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2071. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
2073. if (( is_numeric($probability)) && ( is_numeric($mean)) && ( is_numeric($stdDev))) {
2074. if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
2075. return PHPExcel_Calculation_Functions:: NaN();
2076. }
2077. return exp($mean + $stdDev * self:: NORMSINV($probability));
2078. }
2079. return PHPExcel_Calculation_Functions:: VALUE();
2080. } // function LOGINV()
2083. /**
2084. * LOGNORMDIST
2085. *
2086. * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed
2087. * with parameters mean and standard_dev.
2088. *
2089. * @param float $value
2090. * @return float
2091. */
2092. public static function LOGNORMDIST($value, $mean, $stdDev) {
2093. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2094. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2095. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
2097. if (( is_numeric($value)) && ( is_numeric($mean)) && ( is_numeric($stdDev))) {
2098. if (($value <= 0) || ($stdDev <= 0)) {
2099. return PHPExcel_Calculation_Functions:: NaN();
2100. }
2101. return self:: NORMSDIST(( log($value) - $mean) / $stdDev);
2102. }
2103. return PHPExcel_Calculation_Functions:: VALUE();
2104. } // function LOGNORMDIST()
2107. /**
2108. * MAX
2109. *
2110. * MAX returns the value of the element of the values passed that has the highest value,
2111. * with negative numbers considered smaller than positive numbers.
2112. *
2113. * Excel Function:
2114. * MAX(value1[,value2[, ...]])
2115. *
2116. * @access public
2117. * @category Statistical Functions
2118. * @param mixed $arg,... Data values
2119. * @return float
2120. */
2121. public static function MAX() {
2122. // Return value
2123. $returnValue = null;
2125. // Loop through arguments
2126. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2127. foreach ($aArgs as $arg) {
2128. // Is it a numeric value?
2129. if (( is_numeric($arg)) && (! is_string($arg))) {
2130. if (( is_null($returnValue)) || ($arg > $returnValue)) {
2131. $returnValue = $arg;
2132. }
2133. }
2134. }
2136. // Return
2137. if( is_null($returnValue)) {
2138. return 0;
2139. }
2140. return $returnValue;
2141. } // function MAX()
2144. /**
2145. * MAXA
2146. *
2147. * Returns the greatest value in a list of arguments, including numbers, text, and logical values
2148. *
2149. * Excel Function:
2150. * MAXA(value1[,value2[, ...]])
2151. *
2152. * @access public
2153. * @category Statistical Functions
2154. * @param mixed $arg,... Data values
2155. * @return float
2156. */
2157. public static function MAXA() {
2158. // Return value
2159. $returnValue = null;
2161. // Loop through arguments
2162. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2163. foreach ($aArgs as $arg) {
2164. // Is it a numeric value?
2165. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) && ($arg != '')))) {
2166. if ( is_bool($arg)) {
2167. $arg = (integer) $arg;
2168. } elseif ( is_string($arg)) {
2169. $arg = 0;
2170. }
2171. if (( is_null($returnValue)) || ($arg > $returnValue)) {
2172. $returnValue = $arg;
2173. }
2174. }
2175. }
2177. // Return
2178. if( is_null($returnValue)) {
2179. return 0;
2180. }
2181. return $returnValue;
2182. } // function MAXA()
2185. /**
2186. * MAXIF
2187. *
2188. * Counts the maximum value within a range of cells that contain numbers within the list of arguments
2189. *
2190. * Excel Function:
2191. * MAXIF(value1[,value2[, ...]],condition)
2192. *
2193. * @access public
2194. * @category Mathematical and Trigonometric Functions
2195. * @param mixed $arg,... Data values
2196. * @param string $condition The criteria that defines which cells will be checked.
2197. * @return float
2198. */
2199. public static function MAXIF($aArgs,$condition,$sumArgs = array()) {
2200. // Return value
2201. $returnValue = null;
2203. $aArgs = PHPExcel_Calculation_Functions:: flattenArray($aArgs);
2204. $sumArgs = PHPExcel_Calculation_Functions:: flattenArray($sumArgs);
2205. if ( count($sumArgs) == 0) {
2206. $sumArgs = $aArgs;
2207. }
2208. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2209. // Loop through arguments
2210. foreach ($aArgs as $key => $arg) {
2211. if (! is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult( strtoupper($arg)); }
2212. $testCondition = '='.$arg.$condition;
2213. if ( PHPExcel_Calculation:: getInstance()->_calculateFormulaValue($testCondition)) {
2214. if (( is_null($returnValue)) || ($arg > $returnValue)) {
2215. $returnValue = $arg;
2216. }
2217. }
2218. }
2220. // Return
2221. return $returnValue;
2222. } // function MAXIF()
2225. /**
2226. * MEDIAN
2227. *
2228. * Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
2229. *
2230. * Excel Function:
2231. * MEDIAN(value1[,value2[, ...]])
2232. *
2233. * @access public
2234. * @category Statistical Functions
2235. * @param mixed $arg,... Data values
2236. * @return float
2237. */
2238. public static function MEDIAN() {
2239. // Return value
2240. $returnValue = PHPExcel_Calculation_Functions:: NaN();
2242. $mArgs = array();
2243. // Loop through arguments
2244. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2245. foreach ($aArgs as $arg) {
2246. // Is it a numeric value?
2247. if (( is_numeric($arg)) && (! is_string($arg))) {
2248. $mArgs[] = $arg;
2249. }
2250. }
2252. $mValueCount = count($mArgs);
2253. if ($mValueCount > 0) {
2254. sort($mArgs,SORT_NUMERIC);
2255. $mValueCount = $mValueCount / 2;
2256. if ($mValueCount == floor($mValueCount)) {
2257. $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
2258. } else {
2259. $mValueCount == floor($mValueCount);
2260. $returnValue = $mArgs[$mValueCount];
2261. }
2262. }
2264. // Return
2265. return $returnValue;
2266. } // function MEDIAN()
2269. /**
2270. * MIN
2271. *
2272. * MIN returns the value of the element of the values passed that has the smallest value,
2273. * with negative numbers considered smaller than positive numbers.
2274. *
2275. * Excel Function:
2276. * MIN(value1[,value2[, ...]])
2277. *
2278. * @access public
2279. * @category Statistical Functions
2280. * @param mixed $arg,... Data values
2281. * @return float
2282. */
2283. public static function MIN() {
2284. // Return value
2285. $returnValue = null;
2287. // Loop through arguments
2288. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2289. foreach ($aArgs as $arg) {
2290. // Is it a numeric value?
2291. if (( is_numeric($arg)) && (! is_string($arg))) {
2292. if (( is_null($returnValue)) || ($arg < $returnValue)) {
2293. $returnValue = $arg;
2294. }
2295. }
2296. }
2298. // Return
2299. if( is_null($returnValue)) {
2300. return 0;
2301. }
2302. return $returnValue;
2303. } // function MIN()
2306. /**
2307. * MINA
2308. *
2309. * Returns the smallest value in a list of arguments, including numbers, text, and logical values
2310. *
2311. * Excel Function:
2312. * MINA(value1[,value2[, ...]])
2313. *
2314. * @access public
2315. * @category Statistical Functions
2316. * @param mixed $arg,... Data values
2317. * @return float
2318. */
2319. public static function MINA() {
2320. // Return value
2321. $returnValue = null;
2323. // Loop through arguments
2324. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2325. foreach ($aArgs as $arg) {
2326. // Is it a numeric value?
2327. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) && ($arg != '')))) {
2328. if ( is_bool($arg)) {
2329. $arg = (integer) $arg;
2330. } elseif ( is_string($arg)) {
2331. $arg = 0;
2332. }
2333. if (( is_null($returnValue)) || ($arg < $returnValue)) {
2334. $returnValue = $arg;
2335. }
2336. }
2337. }
2339. // Return
2340. if( is_null($returnValue)) {
2341. return 0;
2342. }
2343. return $returnValue;
2344. } // function MINA()
2347. /**
2348. * MINIF
2349. *
2350. * Returns the minimum value within a range of cells that contain numbers within the list of arguments
2351. *
2352. * Excel Function:
2353. * MINIF(value1[,value2[, ...]],condition)
2354. *
2355. * @access public
2356. * @category Mathematical and Trigonometric Functions
2357. * @param mixed $arg,... Data values
2358. * @param string $condition The criteria that defines which cells will be checked.
2359. * @return float
2360. */
2361. public static function MINIF($aArgs,$condition,$sumArgs = array()) {
2362. // Return value
2363. $returnValue = null;
2365. $aArgs = PHPExcel_Calculation_Functions:: flattenArray($aArgs);
2366. $sumArgs = PHPExcel_Calculation_Functions:: flattenArray($sumArgs);
2367. if ( count($sumArgs) == 0) {
2368. $sumArgs = $aArgs;
2369. }
2370. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
2371. // Loop through arguments
2372. foreach ($aArgs as $key => $arg) {
2373. if (! is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult( strtoupper($arg)); }
2374. $testCondition = '='.$arg.$condition;
2375. if ( PHPExcel_Calculation:: getInstance()->_calculateFormulaValue($testCondition)) {
2376. if (( is_null($returnValue)) || ($arg < $returnValue)) {
2377. $returnValue = $arg;
2378. }
2379. }
2380. }
2382. // Return
2383. return $returnValue;
2384. } // function MINIF()
2387. //
2388. // Special variant of array_count_values that isn't limited to strings and integers,
2389. // but can work with floating point numbers as values
2390. //
2391. private static function _modeCalc($data) {
2392. $frequencyArray = array();
2393. foreach($data as $datum) {
2394. $found = False;
2395. foreach($frequencyArray as $key => $value) {
2396. if ((string) $value['value'] == (string) $datum) {
2397. ++$frequencyArray[$key]['frequency'];
2398. $found = True;
2399. break;
2400. }
2401. }
2402. if (!$found) {
2403. $frequencyArray[] = array('value' => $datum,
2404. 'frequency' => 1 );
2405. }
2406. }
2408. foreach($frequencyArray as $key => $value) {
2409. $frequencyList[$key] = $value['frequency'];
2410. $valueList[$key] = $value['value'];
2411. }
2412. array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
2414. if ($frequencyArray[0]['frequency'] == 1) {
2415. return PHPExcel_Calculation_Functions:: NA();
2416. }
2417. return $frequencyArray[0]['value'];
2418. } // function _modeCalc()
2421. /**
2422. * MODE
2423. *
2424. * Returns the most frequently occurring, or repetitive, value in an array or range of data
2425. *
2426. * Excel Function:
2427. * MODE(value1[,value2[, ...]])
2428. *
2429. * @access public
2430. * @category Statistical Functions
2431. * @param mixed $arg,... Data values
2432. * @return float
2433. */
2434. public static function MODE() {
2435. // Return value
2436. $returnValue = PHPExcel_Calculation_Functions:: NA();
2438. // Loop through arguments
2439. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2441. $mArgs = array();
2442. foreach ($aArgs as $arg) {
2443. // Is it a numeric value?
2444. if (( is_numeric($arg)) && (! is_string($arg))) {
2445. $mArgs[] = $arg;
2446. }
2447. }
2449. if ( count($mArgs) > 0) {
2450. return self::_modeCalc($mArgs);
2451. }
2453. // Return
2454. return $returnValue;
2455. } // function MODE()
2458. /**
2459. * NEGBINOMDIST
2460. *
2461. * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that
2462. * there will be number_f failures before the number_s-th success, when the constant
2463. * probability of a success is probability_s. This function is similar to the binomial
2464. * distribution, except that the number of successes is fixed, and the number of trials is
2465. * variable. Like the binomial, trials are assumed to be independent.
2466. *
2467. * @param float $failures Number of Failures
2468. * @param float $successes Threshold number of Successes
2469. * @param float $probability Probability of success on each trial
2470. * @return float
2471. *
2472. */
2473. public static function NEGBINOMDIST($failures, $successes, $probability) {
2474. $failures = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($failures));
2475. $successes = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($successes));
2476. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
2478. if (( is_numeric($failures)) && ( is_numeric($successes)) && ( is_numeric($probability))) {
2479. if (($failures < 0) || ($successes < 1)) {
2480. return PHPExcel_Calculation_Functions:: NaN();
2481. }
2482. if (($probability < 0) || ($probability > 1)) {
2483. return PHPExcel_Calculation_Functions:: NaN();
2484. }
2485. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) {
2486. if (($failures + $successes - 1) <= 0) {
2487. return PHPExcel_Calculation_Functions:: NaN();
2488. }
2489. }
2490. return ( PHPExcel_Calculation_MathTrig:: COMBIN($failures + $successes - 1,$successes - 1)) * ( pow($probability,$successes)) * ( pow(1 - $probability,$failures)) ;
2491. }
2492. return PHPExcel_Calculation_Functions:: VALUE();
2493. } // function NEGBINOMDIST()
2496. /**
2497. * NORMDIST
2498. *
2499. * Returns the normal distribution for the specified mean and standard deviation. This
2500. * function has a very wide range of applications in statistics, including hypothesis
2501. * testing.
2502. *
2503. * @param float $value
2504. * @param float $mean Mean Value
2505. * @param float $stdDev Standard Deviation
2506. * @param boolean $cumulative
2507. * @return float
2508. *
2509. */
2510. public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
2511. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2512. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2513. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
2515. if (( is_numeric($value)) && ( is_numeric($mean)) && ( is_numeric($stdDev))) {
2516. if ($stdDev < 0) {
2517. return PHPExcel_Calculation_Functions:: NaN();
2518. }
2519. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
2520. if ($cumulative) {
2521. return 0.5 * (1 + PHPExcel_Calculation_Engineering::_erfVal(($value - $mean) / ($stdDev * sqrt(2))));
2522. } else {
2523. return (1 / ( SQRT2PI * $stdDev)) * exp(0 - ( pow($value - $mean,2) / (2 * ($stdDev * $stdDev))));
2524. }
2525. }
2526. }
2527. return PHPExcel_Calculation_Functions:: VALUE();
2528. } // function NORMDIST()
2531. /**
2532. * NORMINV
2533. *
2534. * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
2535. *
2536. * @param float $value
2537. * @param float $mean Mean Value
2538. * @param float $stdDev Standard Deviation
2539. * @return float
2540. *
2541. */
2542. public static function NORMINV($probability,$mean,$stdDev) {
2543. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
2544. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2545. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
2547. if (( is_numeric($probability)) && ( is_numeric($mean)) && ( is_numeric($stdDev))) {
2548. if (($probability < 0) || ($probability > 1)) {
2549. return PHPExcel_Calculation_Functions:: NaN();
2550. }
2551. if ($stdDev < 0) {
2552. return PHPExcel_Calculation_Functions:: NaN();
2553. }
2554. return (self::_inverse_ncdf($probability) * $stdDev) + $mean;
2555. }
2556. return PHPExcel_Calculation_Functions:: VALUE();
2557. } // function NORMINV()
2560. /**
2561. * NORMSDIST
2562. *
2563. * Returns the standard normal cumulative distribution function. The distribution has
2564. * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a
2565. * table of standard normal curve areas.
2566. *
2567. * @param float $value
2568. * @return float
2569. */
2570. public static function NORMSDIST($value) {
2571. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2573. return self:: NORMDIST($value, 0, 1, True);
2574. } // function NORMSDIST()
2577. /**
2578. * NORMSINV
2579. *
2580. * Returns the inverse of the standard normal cumulative distribution
2581. *
2582. * @param float $value
2583. * @return float
2584. */
2585. public static function NORMSINV($value) {
2586. return self:: NORMINV($value, 0, 1);
2587. } // function NORMSINV()
2590. /**
2591. * PERCENTILE
2592. *
2593. * Returns the nth percentile of values in a range..
2594. *
2595. * Excel Function:
2596. * PERCENTILE(value1[,value2[, ...]],entry)
2597. *
2598. * @access public
2599. * @category Statistical Functions
2600. * @param mixed $arg,... Data values
2601. * @param float $entry Percentile value in the range 0..1, inclusive.
2602. * @return float
2603. */
2604. public static function PERCENTILE() {
2605. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2607. // Calculate
2608. $entry = array_pop($aArgs);
2610. if (( is_numeric($entry)) && (! is_string($entry))) {
2611. if (($entry < 0) || ($entry > 1)) {
2612. return PHPExcel_Calculation_Functions:: NaN();
2613. }
2614. $mArgs = array();
2615. foreach ($aArgs as $arg) {
2616. // Is it a numeric value?
2617. if (( is_numeric($arg)) && (! is_string($arg))) {
2618. $mArgs[] = $arg;
2619. }
2620. }
2621. $mValueCount = count($mArgs);
2622. if ($mValueCount > 0) {
2623. sort($mArgs);
2624. $count = self:: COUNT($mArgs);
2625. $index = $entry * ($count-1);
2626. $iBase = floor($index);
2627. if ($index == $iBase) {
2628. return $mArgs[$index];
2629. } else {
2630. $iNext = $iBase + 1;
2631. $iProportion = $index - $iBase;
2632. return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
2633. }
2634. }
2635. }
2636. return PHPExcel_Calculation_Functions:: VALUE();
2637. } // function PERCENTILE()
2640. /**
2641. * PERCENTRANK
2642. *
2643. * Returns the rank of a value in a data set as a percentage of the data set.
2644. *
2645. * @param array of number An array of, or a reference to, a list of numbers.
2646. * @param number The number whose rank you want to find.
2647. * @param number The number of significant digits for the returned percentage value.
2648. * @return float
2649. */
2650. public static function PERCENTRANK($valueSet,$value,$significance=3) {
2651. $valueSet = PHPExcel_Calculation_Functions:: flattenArray($valueSet);
2652. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2653. $significance = ( is_null($significance)) ? 3 : (integer) PHPExcel_Calculation_Functions:: flattenSingleValue($significance);
2655. foreach($valueSet as $key => $valueEntry) {
2656. if (! is_numeric($valueEntry)) {
2657. unset($valueSet[$key]);
2658. }
2659. }
2660. sort($valueSet,SORT_NUMERIC);
2661. $valueCount = count($valueSet);
2662. if ($valueCount == 0) {
2663. return PHPExcel_Calculation_Functions:: NaN();
2664. }
2666. $valueAdjustor = $valueCount - 1;
2667. if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
2668. return PHPExcel_Calculation_Functions:: NA();
2669. }
2671. $pos = array_search($value,$valueSet);
2672. if ($pos === False) {
2673. $pos = 0;
2674. $testValue = $valueSet[0];
2675. while ($testValue < $value) {
2676. $testValue = $valueSet[++$pos];
2677. }
2678. --$pos;
2679. $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
2680. }
2682. return round($pos / $valueAdjustor,$significance);
2683. } // function PERCENTRANK()
2686. /**
2687. * PERMUT
2688. *
2689. * Returns the number of permutations for a given number of objects that can be
2690. * selected from number objects. A permutation is any set or subset of objects or
2691. * events where internal order is significant. Permutations are different from
2692. * combinations, for which the internal order is not significant. Use this function
2693. * for lottery-style probability calculations.
2694. *
2695. * @param int $numObjs Number of different objects
2696. * @param int $numInSet Number of objects in each permutation
2697. * @return int Number of permutations
2698. */
2699. public static function PERMUT($numObjs,$numInSet) {
2700. $numObjs = PHPExcel_Calculation_Functions:: flattenSingleValue($numObjs);
2701. $numInSet = PHPExcel_Calculation_Functions:: flattenSingleValue($numInSet);
2703. if (( is_numeric($numObjs)) && ( is_numeric($numInSet))) {
2704. $numInSet = floor($numInSet);
2705. if ($numObjs < $numInSet) {
2706. return PHPExcel_Calculation_Functions:: NaN();
2707. }
2708. return round( PHPExcel_Calculation_MathTrig:: FACT($numObjs) / PHPExcel_Calculation_MathTrig:: FACT($numObjs - $numInSet));
2709. }
2710. return PHPExcel_Calculation_Functions:: VALUE();
2711. } // function PERMUT()
2714. /**
2715. * POISSON
2716. *
2717. * Returns the Poisson distribution. A common application of the Poisson distribution
2718. * is predicting the number of events over a specific time, such as the number of
2719. * cars arriving at a toll plaza in 1 minute.
2720. *
2721. * @param float $value
2722. * @param float $mean Mean Value
2723. * @param boolean $cumulative
2724. * @return float
2725. *
2726. */
2727. public static function POISSON($value, $mean, $cumulative) {
2728. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2729. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2731. if (( is_numeric($value)) && ( is_numeric($mean))) {
2732. if (($value <= 0) || ($mean <= 0)) {
2733. return PHPExcel_Calculation_Functions:: NaN();
2734. }
2735. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
2736. if ($cumulative) {
2737. $summer = 0;
2738. for ($i = 0; $i <= floor($value); ++$i) {
2739. $summer += pow($mean,$i) / PHPExcel_Calculation_MathTrig:: FACT($i);
2740. }
2741. return exp(0-$mean) * $summer;
2742. } else {
2743. return ( exp(0-$mean) * pow($mean,$value)) / PHPExcel_Calculation_MathTrig:: FACT($value);
2744. }
2745. }
2746. }
2747. return PHPExcel_Calculation_Functions:: VALUE();
2748. } // function POISSON()
2751. /**
2752. * QUARTILE
2753. *
2754. * Returns the quartile of a data set.
2755. *
2756. * Excel Function:
2757. * QUARTILE(value1[,value2[, ...]],entry)
2758. *
2759. * @access public
2760. * @category Statistical Functions
2761. * @param mixed $arg,... Data values
2762. * @param int $entry Quartile value in the range 1..3, inclusive.
2763. * @return float
2764. */
2765. public static function QUARTILE() {
2766. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2768. // Calculate
2769. $entry = floor( array_pop($aArgs));
2771. if (( is_numeric($entry)) && (! is_string($entry))) {
2772. $entry /= 4;
2773. if (($entry < 0) || ($entry > 1)) {
2774. return PHPExcel_Calculation_Functions:: NaN();
2775. }
2776. return self:: PERCENTILE($aArgs,$entry);
2777. }
2778. return PHPExcel_Calculation_Functions:: VALUE();
2779. } // function QUARTILE()
2782. /**
2783. * RANK
2784. *
2785. * Returns the rank of a number in a list of numbers.
2786. *
2787. * @param number The number whose rank you want to find.
2788. * @param array of number An array of, or a reference to, a list of numbers.
2789. * @param mixed Order to sort the values in the value set
2790. * @return float
2791. */
2792. public static function RANK($value,$valueSet,$order=0) {
2793. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2794. $valueSet = PHPExcel_Calculation_Functions:: flattenArray($valueSet);
2795. $order = ( is_null($order)) ? 0 : (integer) PHPExcel_Calculation_Functions:: flattenSingleValue($order);
2797. foreach($valueSet as $key => $valueEntry) {
2798. if (! is_numeric($valueEntry)) {
2799. unset($valueSet[$key]);
2800. }
2801. }
2803. if ($order == 0) {
2804. rsort($valueSet,SORT_NUMERIC);
2805. } else {
2806. sort($valueSet,SORT_NUMERIC);
2807. }
2808. $pos = array_search($value,$valueSet);
2809. if ($pos === False) {
2810. return PHPExcel_Calculation_Functions:: NA();
2811. }
2813. return ++$pos;
2814. } // function RANK()
2817. /**
2818. * RSQ
2819. *
2820. * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
2821. *
2822. * @param array of mixed Data Series Y
2823. * @param array of mixed Data Series X
2824. * @return float
2825. */
2826. public static function RSQ($yValues,$xValues) {
2827. if (! self::_checkTrendArrays($yValues,$xValues)) {
2828. return PHPExcel_Calculation_Functions:: VALUE();
2829. }
2830. $yValueCount = count($yValues);
2831. $xValueCount = count($xValues);
2833. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2834. return PHPExcel_Calculation_Functions:: NA();
2835. } elseif ($yValueCount == 1) {
2836. return PHPExcel_Calculation_Functions:: DIV0();
2837. }
2839. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
2840. return $bestFitLinear->getGoodnessOfFit();
2841. } // function RSQ()
2844. /**
2845. * SKEW
2846. *
2847. * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry
2848. * of a distribution around its mean. Positive skewness indicates a distribution with an
2849. * asymmetric tail extending toward more positive values. Negative skewness indicates a
2850. * distribution with an asymmetric tail extending toward more negative values.
2851. *
2852. * @param array Data Series
2853. * @return float
2854. */
2855. public static function SKEW() {
2856. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
2857. $mean = self:: AVERAGE($aArgs);
2858. $stdDev = self:: STDEV($aArgs);
2860. $count = $summer = 0;
2861. // Loop through arguments
2862. foreach ($aArgs as $k => $arg) {
2863. if (( is_bool($arg)) &&
2864. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
2865. } else {
2866. // Is it a numeric value?
2867. if (( is_numeric($arg)) && (! is_string($arg))) {
2868. $summer += pow((($arg - $mean) / $stdDev),3) ;
2869. ++$count;
2870. }
2871. }
2872. }
2874. // Return
2875. if ($count > 2) {
2876. return $summer * ($count / (($count-1) * ($count-2)));
2877. }
2878. return PHPExcel_Calculation_Functions:: DIV0();
2879. } // function SKEW()
2882. /**
2883. * SLOPE
2884. *
2885. * Returns the slope of the linear regression line through data points in known_y's and known_x's.
2886. *
2887. * @param array of mixed Data Series Y
2888. * @param array of mixed Data Series X
2889. * @return float
2890. */
2891. public static function SLOPE($yValues,$xValues) {
2892. if (! self::_checkTrendArrays($yValues,$xValues)) {
2893. return PHPExcel_Calculation_Functions:: VALUE();
2894. }
2895. $yValueCount = count($yValues);
2896. $xValueCount = count($xValues);
2898. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
2899. return PHPExcel_Calculation_Functions:: NA();
2900. } elseif ($yValueCount == 1) {
2901. return PHPExcel_Calculation_Functions:: DIV0();
2902. }
2904. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
2905. return $bestFitLinear->getSlope();
2906. } // function SLOPE()
2909. /**
2910. * SMALL
2911. *
2912. * Returns the nth smallest value in a data set. You can use this function to
2913. * select a value based on its relative standing.
2914. *
2915. * Excel Function:
2916. * SMALL(value1[,value2[, ...]],entry)
2917. *
2918. * @access public
2919. * @category Statistical Functions
2920. * @param mixed $arg,... Data values
2921. * @param int $entry Position (ordered from the smallest) in the array or range of data to return
2922. * @return float
2923. */
2924. public static function SMALL() {
2925. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
2927. // Calculate
2928. $entry = array_pop($aArgs);
2930. if (( is_numeric($entry)) && (! is_string($entry))) {
2931. $mArgs = array();
2932. foreach ($aArgs as $arg) {
2933. // Is it a numeric value?
2934. if (( is_numeric($arg)) && (! is_string($arg))) {
2935. $mArgs[] = $arg;
2936. }
2937. }
2938. $count = self:: COUNT($mArgs);
2939. $entry = floor(--$entry);
2940. if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
2941. return PHPExcel_Calculation_Functions:: NaN();
2942. }
2943. sort($mArgs);
2944. return $mArgs[$entry];
2945. }
2946. return PHPExcel_Calculation_Functions:: VALUE();
2947. } // function SMALL()
2950. /**
2951. * STANDARDIZE
2952. *
2953. * Returns a normalized value from a distribution characterized by mean and standard_dev.
2954. *
2955. * @param float $value Value to normalize
2956. * @param float $mean Mean Value
2957. * @param float $stdDev Standard Deviation
2958. * @return float Standardized value
2959. */
2960. public static function STANDARDIZE($value,$mean,$stdDev) {
2961. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
2962. $mean = PHPExcel_Calculation_Functions:: flattenSingleValue($mean);
2963. $stdDev = PHPExcel_Calculation_Functions:: flattenSingleValue($stdDev);
2965. if (( is_numeric($value)) && ( is_numeric($mean)) && ( is_numeric($stdDev))) {
2966. if ($stdDev <= 0) {
2967. return PHPExcel_Calculation_Functions:: NaN();
2968. }
2969. return ($value - $mean) / $stdDev ;
2970. }
2971. return PHPExcel_Calculation_Functions:: VALUE();
2972. } // function STANDARDIZE()
2975. /**
2976. * STDEV
2977. *
2978. * Estimates standard deviation based on a sample. The standard deviation is a measure of how
2979. * widely values are dispersed from the average value (the mean).
2980. *
2981. * Excel Function:
2982. * STDEV(value1[,value2[, ...]])
2983. *
2984. * @access public
2985. * @category Statistical Functions
2986. * @param mixed $arg,... Data values
2987. * @return float
2988. */
2989. public static function STDEV() {
2990. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
2992. // Return value
2993. $returnValue = null;
2995. $aMean = self:: AVERAGE($aArgs);
2996. if (! is_null($aMean)) {
2997. $aCount = -1;
2998. foreach ($aArgs as $k => $arg) {
2999. if (( is_bool($arg)) &&
3000. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
3001. $arg = (integer) $arg;
3002. }
3003. // Is it a numeric value?
3004. if (( is_numeric($arg)) && (! is_string($arg))) {
3005. if ( is_null($returnValue)) {
3006. $returnValue = pow(($arg - $aMean),2);
3007. } else {
3008. $returnValue += pow(($arg - $aMean),2);
3009. }
3010. ++$aCount;
3011. }
3012. }
3014. // Return
3015. if (($aCount > 0) && ($returnValue >= 0)) {
3016. return sqrt($returnValue / $aCount);
3017. }
3018. }
3019. return PHPExcel_Calculation_Functions:: DIV0();
3020. } // function STDEV()
3023. /**
3024. * STDEVA
3025. *
3026. * Estimates standard deviation based on a sample, including numbers, text, and logical values
3027. *
3028. * Excel Function:
3029. * STDEVA(value1[,value2[, ...]])
3030. *
3031. * @access public
3032. * @category Statistical Functions
3033. * @param mixed $arg,... Data values
3034. * @return float
3035. */
3036. public static function STDEVA() {
3037. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
3039. // Return value
3040. $returnValue = null;
3042. $aMean = self:: AVERAGEA($aArgs);
3043. if (! is_null($aMean)) {
3044. $aCount = -1;
3045. foreach ($aArgs as $k => $arg) {
3046. if (( is_bool($arg)) &&
3047. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
3048. } else {
3049. // Is it a numeric value?
3050. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) & ($arg != '')))) {
3051. if ( is_bool($arg)) {
3052. $arg = (integer) $arg;
3053. } elseif ( is_string($arg)) {
3054. $arg = 0;
3055. }
3056. if ( is_null($returnValue)) {
3057. $returnValue = pow(($arg - $aMean),2);
3058. } else {
3059. $returnValue += pow(($arg - $aMean),2);
3060. }
3061. ++$aCount;
3062. }
3063. }
3064. }
3066. // Return
3067. if (($aCount > 0) && ($returnValue >= 0)) {
3068. return sqrt($returnValue / $aCount);
3069. }
3070. }
3071. return PHPExcel_Calculation_Functions:: DIV0();
3072. } // function STDEVA()
3075. /**
3076. * STDEVP
3077. *
3078. * Calculates standard deviation based on the entire population
3079. *
3080. * Excel Function:
3081. * STDEVP(value1[,value2[, ...]])
3082. *
3083. * @access public
3084. * @category Statistical Functions
3085. * @param mixed $arg,... Data values
3086. * @return float
3087. */
3088. public static function STDEVP() {
3089. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
3091. // Return value
3092. $returnValue = null;
3094. $aMean = self:: AVERAGE($aArgs);
3095. if (! is_null($aMean)) {
3096. $aCount = 0;
3097. foreach ($aArgs as $k => $arg) {
3098. if (( is_bool($arg)) &&
3099. ((! PHPExcel_Calculation_Functions:: isCellValue($k)) || ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE))) {
3100. $arg = (integer) $arg;
3101. }
3102. // Is it a numeric value?
3103. if (( is_numeric($arg)) && (! is_string($arg))) {
3104. if ( is_null($returnValue)) {
3105. $returnValue = pow(($arg - $aMean),2);
3106. } else {
3107. $returnValue += pow(($arg - $aMean),2);
3108. }
3109. ++$aCount;
3110. }
3111. }
3113. // Return
3114. if (($aCount > 0) && ($returnValue >= 0)) {
3115. return sqrt($returnValue / $aCount);
3116. }
3117. }
3118. return PHPExcel_Calculation_Functions:: DIV0();
3119. } // function STDEVP()
3122. /**
3123. * STDEVPA
3124. *
3125. * Calculates standard deviation based on the entire population, including numbers, text, and logical values
3126. *
3127. * Excel Function:
3128. * STDEVPA(value1[,value2[, ...]])
3129. *
3130. * @access public
3131. * @category Statistical Functions
3132. * @param mixed $arg,... Data values
3133. * @return float
3134. */
3135. public static function STDEVPA() {
3136. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
3138. // Return value
3139. $returnValue = null;
3141. $aMean = self:: AVERAGEA($aArgs);
3142. if (! is_null($aMean)) {
3143. $aCount = 0;
3144. foreach ($aArgs as $k => $arg) {
3145. if (( is_bool($arg)) &&
3146. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
3147. } else {
3148. // Is it a numeric value?
3149. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) & ($arg != '')))) {
3150. if ( is_bool($arg)) {
3151. $arg = (integer) $arg;
3152. } elseif ( is_string($arg)) {
3153. $arg = 0;
3154. }
3155. if ( is_null($returnValue)) {
3156. $returnValue = pow(($arg - $aMean),2);
3157. } else {
3158. $returnValue += pow(($arg - $aMean),2);
3159. }
3160. ++$aCount;
3161. }
3162. }
3163. }
3165. // Return
3166. if (($aCount > 0) && ($returnValue >= 0)) {
3167. return sqrt($returnValue / $aCount);
3168. }
3169. }
3170. return PHPExcel_Calculation_Functions:: DIV0();
3171. } // function STDEVPA()
3174. /**
3175. * STEYX
3176. *
3177. * Returns the standard error of the predicted y-value for each x in the regression.
3178. *
3179. * @param array of mixed Data Series Y
3180. * @param array of mixed Data Series X
3181. * @return float
3182. */
3183. public static function STEYX($yValues,$xValues) {
3184. if (! self::_checkTrendArrays($yValues,$xValues)) {
3185. return PHPExcel_Calculation_Functions:: VALUE();
3186. }
3187. $yValueCount = count($yValues);
3188. $xValueCount = count($xValues);
3190. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
3191. return PHPExcel_Calculation_Functions:: NA();
3192. } elseif ($yValueCount == 1) {
3193. return PHPExcel_Calculation_Functions:: DIV0();
3194. }
3196. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues);
3197. return $bestFitLinear->getStdevOfResiduals();
3198. } // function STEYX()
3201. /**
3202. * TDIST
3203. *
3204. * Returns the probability of Student's T distribution.
3205. *
3206. * @param float $value Value for the function
3207. * @param float $degrees degrees of freedom
3208. * @param float $tails number of tails (1 or 2)
3209. * @return float
3210. */
3211. public static function TDIST($value, $degrees, $tails) {
3212. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
3213. $degrees = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($degrees));
3214. $tails = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($tails));
3216. if (( is_numeric($value)) && ( is_numeric($degrees)) && ( is_numeric($tails))) {
3217. if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
3218. return PHPExcel_Calculation_Functions:: NaN();
3219. }
3220. // tdist, which finds the probability that corresponds to a given value
3221. // of t with k degrees of freedom. This algorithm is translated from a
3222. // pascal function on p81 of "Statistical Computing in Pascal" by D
3223. // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
3224. // London). The above Pascal algorithm is itself a translation of the
3225. // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
3226. // Laboratory as reported in (among other places) "Applied Statistics
3227. // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
3228. // Horwood Ltd.; W. Sussex, England).
3229. $tterm = $degrees;
3230. $ttheta = atan2($value, sqrt($tterm));
3231. $tc = cos($ttheta);
3232. $ts = sin($ttheta);
3233. $tsum = 0;
3235. if (($degrees % 2) == 1) {
3236. $ti = 3;
3237. $tterm = $tc;
3238. } else {
3239. $ti = 2;
3240. $tterm = 1;
3241. }
3243. $tsum = $tterm;
3244. while ($ti < $degrees) {
3245. $tterm *= $tc * $tc * ($ti - 1) / $ti;
3246. $tsum += $tterm;
3247. $ti += 2;
3248. }
3249. $tsum *= $ts;
3250. if (($degrees % 2) == 1) { $tsum = M_2DIVPI * ($tsum + $ttheta); }
3251. $tValue = 0.5 * (1 + $tsum);
3252. if ($tails == 1) {
3253. return 1 - abs($tValue);
3254. } else {
3255. return 1 - abs((1 - $tValue) - $tValue);
3256. }
3257. }
3258. return PHPExcel_Calculation_Functions:: VALUE();
3259. } // function TDIST()
3262. /**
3263. * TINV
3264. *
3265. * Returns the one-tailed probability of the chi-squared distribution.
3266. *
3267. * @param float $probability Probability for the function
3268. * @param float $degrees degrees of freedom
3269. * @return float
3270. */
3271. public static function TINV($probability, $degrees) {
3272. $probability = PHPExcel_Calculation_Functions:: flattenSingleValue($probability);
3273. $degrees = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($degrees));
3275. if (( is_numeric($probability)) && ( is_numeric($degrees))) {
3276. $xLo = 100;
3277. $xHi = 0;
3279. $x = $xNew = 1;
3280. $dx = 1;
3281. $i = 0;
3283. while (( abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
3284. // Apply Newton-Raphson step
3285. $result = self:: TDIST($x, $degrees, 2);
3286. $error = $result - $probability;
3287. if ($error == 0.0) {
3288. $dx = 0;
3289. } elseif ($error < 0.0) {
3290. $xLo = $x;
3291. } else {
3292. $xHi = $x;
3293. }
3294. // Avoid division by zero
3295. if ($result != 0.0) {
3296. $dx = $error / $result;
3297. $xNew = $x - $dx;
3298. }
3299. // If the NR fails to converge (which for example may be the
3300. // case if the initial guess is too rough) we apply a bisection
3301. // step to determine a more narrow interval around the root.
3302. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
3303. $xNew = ($xLo + $xHi) / 2;
3304. $dx = $xNew - $x;
3305. }
3306. $x = $xNew;
3307. }
3308. if ($i == MAX_ITERATIONS) {
3309. return PHPExcel_Calculation_Functions:: NA();
3310. }
3311. return round($x,12);
3312. }
3313. return PHPExcel_Calculation_Functions:: VALUE();
3314. } // function TINV()
3317. /**
3318. * TREND
3319. *
3320. * Returns values along a linear trend
3321. *
3322. * @param array of mixed Data Series Y
3323. * @param array of mixed Data Series X
3324. * @param array of mixed Values of X for which we want to find Y
3325. * @param boolean A logical value specifying whether to force the intersect to equal 0.
3326. * @return array of float
3327. */
3328. public static function TREND($yValues,$xValues=array(),$newValues=array(),$const=True) {
3329. $yValues = PHPExcel_Calculation_Functions:: flattenArray($yValues);
3330. $xValues = PHPExcel_Calculation_Functions:: flattenArray($xValues);
3331. $newValues = PHPExcel_Calculation_Functions:: flattenArray($newValues);
3332. $const = ( is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions:: flattenSingleValue($const);
3334. $bestFitLinear = trendClass:: calculate( trendClass:: TREND_LINEAR,$yValues,$xValues,$const);
3335. if ( count($newValues) == 0) {
3336. $newValues = $bestFitLinear->getXValues();
3337. }
3339. $returnArray = array();
3340. foreach($newValues as $xValue) {
3341. $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue);
3342. }
3344. return $returnArray;
3345. } // function TREND()
3348. /**
3349. * TRIMMEAN
3350. *
3351. * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
3352. * taken by excluding a percentage of data points from the top and bottom tails
3353. * of a data set.
3354. *
3355. * Excel Function:
3356. * TRIMEAN(value1[,value2[, ...]],$discard)
3357. *
3358. * @access public
3359. * @category Statistical Functions
3360. * @param mixed $arg,... Data values
3361. * @param float $discard Percentage to discard
3362. * @return float
3363. */
3364. public static function TRIMMEAN() {
3365. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
3367. // Calculate
3368. $percent = array_pop($aArgs);
3370. if (( is_numeric($percent)) && (! is_string($percent))) {
3371. if (($percent < 0) || ($percent > 1)) {
3372. return PHPExcel_Calculation_Functions:: NaN();
3373. }
3374. $mArgs = array();
3375. foreach ($aArgs as $arg) {
3376. // Is it a numeric value?
3377. if (( is_numeric($arg)) && (! is_string($arg))) {
3378. $mArgs[] = $arg;
3379. }
3380. }
3381. $discard = floor(self:: COUNT($mArgs) * $percent / 2);
3382. sort($mArgs);
3383. for ($i=0; $i < $discard; ++$i) {
3384. array_pop($mArgs);
3385. array_shift($mArgs);
3386. }
3387. return self:: AVERAGE($mArgs);
3388. }
3389. return PHPExcel_Calculation_Functions:: VALUE();
3390. } // function TRIMMEAN()
3393. /**
3394. * VARFunc
3395. *
3396. * Estimates variance based on a sample.
3397. *
3398. * Excel Function:
3399. * VAR(value1[,value2[, ...]])
3400. *
3401. * @access public
3402. * @category Statistical Functions
3403. * @param mixed $arg,... Data values
3404. * @return float
3405. */
3406. public static function VARFunc() {
3407. // Return value
3408. $returnValue = PHPExcel_Calculation_Functions:: DIV0();
3410. $summerA = $summerB = 0;
3412. // Loop through arguments
3413. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
3414. $aCount = 0;
3415. foreach ($aArgs as $arg) {
3416. if ( is_bool($arg)) { $arg = (integer) $arg; }
3417. // Is it a numeric value?
3418. if (( is_numeric($arg)) && (! is_string($arg))) {
3419. $summerA += ($arg * $arg);
3420. $summerB += $arg;
3421. ++$aCount;
3422. }
3423. }
3425. // Return
3426. if ($aCount > 1) {
3427. $summerA *= $aCount;
3428. $summerB *= $summerB;
3429. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3430. }
3431. return $returnValue;
3432. } // function VARFunc()
3435. /**
3436. * VARA
3437. *
3438. * Estimates variance based on a sample, including numbers, text, and logical values
3439. *
3440. * Excel Function:
3441. * VARA(value1[,value2[, ...]])
3442. *
3443. * @access public
3444. * @category Statistical Functions
3445. * @param mixed $arg,... Data values
3446. * @return float
3447. */
3448. public static function VARA() {
3449. // Return value
3450. $returnValue = PHPExcel_Calculation_Functions:: DIV0();
3452. $summerA = $summerB = 0;
3454. // Loop through arguments
3455. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
3456. $aCount = 0;
3457. foreach ($aArgs as $k => $arg) {
3458. if (( is_string($arg)) &&
3459. ( PHPExcel_Calculation_Functions:: isValue($k))) {
3460. return PHPExcel_Calculation_Functions:: VALUE();
3461. } elseif (( is_string($arg)) &&
3462. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
3463. } else {
3464. // Is it a numeric value?
3465. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) & ($arg != '')))) {
3466. if ( is_bool($arg)) {
3467. $arg = (integer) $arg;
3468. } elseif ( is_string($arg)) {
3469. $arg = 0;
3470. }
3471. $summerA += ($arg * $arg);
3472. $summerB += $arg;
3473. ++$aCount;
3474. }
3475. }
3476. }
3478. // Return
3479. if ($aCount > 1) {
3480. $summerA *= $aCount;
3481. $summerB *= $summerB;
3482. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
3483. }
3484. return $returnValue;
3485. } // function VARA()
3488. /**
3489. * VARP
3490. *
3491. * Calculates variance based on the entire population
3492. *
3493. * Excel Function:
3494. * VARP(value1[,value2[, ...]])
3495. *
3496. * @access public
3497. * @category Statistical Functions
3498. * @param mixed $arg,... Data values
3499. * @return float
3500. */
3501. public static function VARP() {
3502. // Return value
3503. $returnValue = PHPExcel_Calculation_Functions:: DIV0();
3505. $summerA = $summerB = 0;
3507. // Loop through arguments
3508. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
3509. $aCount = 0;
3510. foreach ($aArgs as $arg) {
3511. if ( is_bool($arg)) { $arg = (integer) $arg; }
3512. // Is it a numeric value?
3513. if (( is_numeric($arg)) && (! is_string($arg))) {
3514. $summerA += ($arg * $arg);
3515. $summerB += $arg;
3516. ++$aCount;
3517. }
3518. }
3520. // Return
3521. if ($aCount > 0) {
3522. $summerA *= $aCount;
3523. $summerB *= $summerB;
3524. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3525. }
3526. return $returnValue;
3527. } // function VARP()
3530. /**
3531. * VARPA
3532. *
3533. * Calculates variance based on the entire population, including numbers, text, and logical values
3534. *
3535. * Excel Function:
3536. * VARPA(value1[,value2[, ...]])
3537. *
3538. * @access public
3539. * @category Statistical Functions
3540. * @param mixed $arg,... Data values
3541. * @return float
3542. */
3543. public static function VARPA() {
3544. // Return value
3545. $returnValue = PHPExcel_Calculation_Functions:: DIV0();
3547. $summerA = $summerB = 0;
3549. // Loop through arguments
3550. $aArgs = PHPExcel_Calculation_Functions:: flattenArrayIndexed( func_get_args());
3551. $aCount = 0;
3552. foreach ($aArgs as $k => $arg) {
3553. if (( is_string($arg)) &&
3554. ( PHPExcel_Calculation_Functions:: isValue($k))) {
3555. return PHPExcel_Calculation_Functions:: VALUE();
3556. } elseif (( is_string($arg)) &&
3557. (! PHPExcel_Calculation_Functions:: isMatrixValue($k))) {
3558. } else {
3559. // Is it a numeric value?
3560. if (( is_numeric($arg)) || ( is_bool($arg)) || (( is_string($arg) & ($arg != '')))) {
3561. if ( is_bool($arg)) {
3562. $arg = (integer) $arg;
3563. } elseif ( is_string($arg)) {
3564. $arg = 0;
3565. }
3566. $summerA += ($arg * $arg);
3567. $summerB += $arg;
3568. ++$aCount;
3569. }
3570. }
3571. }
3573. // Return
3574. if ($aCount > 0) {
3575. $summerA *= $aCount;
3576. $summerB *= $summerB;
3577. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
3578. }
3579. return $returnValue;
3580. } // function VARPA()
3583. /**
3584. * WEIBULL
3585. *
3586. * Returns the Weibull distribution. Use this distribution in reliability
3587. * analysis, such as calculating a device's mean time to failure.
3588. *
3589. * @param float $value
3590. * @param float $alpha Alpha Parameter
3591. * @param float $beta Beta Parameter
3592. * @param boolean $cumulative
3593. * @return float
3594. *
3595. */
3596. public static function WEIBULL($value, $alpha, $beta, $cumulative) {
3597. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
3598. $alpha = PHPExcel_Calculation_Functions:: flattenSingleValue($alpha);
3599. $beta = PHPExcel_Calculation_Functions:: flattenSingleValue($beta);
3601. if (( is_numeric($value)) && ( is_numeric($alpha)) && ( is_numeric($beta))) {
3602. if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
3603. return PHPExcel_Calculation_Functions:: NaN();
3604. }
3605. if (( is_numeric($cumulative)) || ( is_bool($cumulative))) {
3606. if ($cumulative) {
3607. return 1 - exp(0 - pow($value / $beta,$alpha));
3608. } else {
3609. return ($alpha / pow($beta,$alpha)) * pow($value,$alpha - 1) * exp(0 - pow($value / $beta,$alpha));
3610. }
3611. }
3612. }
3613. return PHPExcel_Calculation_Functions:: VALUE();
3614. } // function WEIBULL()
3617. /**
3618. * ZTEST
3619. *
3620. * Returns the Weibull distribution. Use this distribution in reliability
3621. * analysis, such as calculating a device's mean time to failure.
3622. *
3623. * @param float $value
3624. * @param float $alpha Alpha Parameter
3625. * @param float $beta Beta Parameter
3626. * @param boolean $cumulative
3627. * @return float
3628. *
3629. */
3630. public static function ZTEST($dataSet, $m0, $sigma=null) {
3631. $dataSet = PHPExcel_Calculation_Functions:: flattenArrayIndexed($dataSet);
3632. $m0 = PHPExcel_Calculation_Functions:: flattenSingleValue($m0);
3633. $sigma = PHPExcel_Calculation_Functions:: flattenSingleValue($sigma);
3635. if ( is_null($sigma)) {
3636. $sigma = self:: STDEV($dataSet);
3637. }
3638. $n = count($dataSet);
3640. return 1 - self:: NORMSDIST((self:: AVERAGE($dataSet) - $m0)/($sigma/SQRT($n)));
3641. } // function ZTEST()
3643. } // class PHPExcel_Calculation_Statistical

Documentation generated on Sun, 27 Feb 2011 16:34:05 -0800 by phpDocumentor 1.4.3