File Source for MathTrig.php

PHPExcel_Calculation

# Source for file MathTrig.php

Documentation is available at MathTrig.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. /**
40. * PHPExcel_Calculation_MathTrig
41. *
42. * @category PHPExcel
43. * @package PHPExcel_Calculation
44. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
45. */
46. class PHPExcel_Calculation_MathTrig {
48. //
49. // Private method to return an array of the factors of the input value
50. //
51. private static function _factors($value) {
52. $startVal = floor( sqrt($value));
54. $factorArray = array();
55. for ($i = $startVal; $i > 1; --$i) {
56. if (($value % $i) == 0) {
57. $factorArray = array_merge($factorArray,self::_factors($value / $i));
58. $factorArray = array_merge($factorArray,self::_factors($i));
59. if ($i <= sqrt($value)) {
60. break;
61. }
62. }
63. }
64. if ( count($factorArray) > 0) {
65. rsort($factorArray);
66. return $factorArray;
67. } else {
68. return array((integer) $value);
69. }
70. } // function _factors()
73. private static function _romanCut($num, $n) {
74. return ($num - ($num % $n ) ) / $n;
75. } // function _romanCut()
78. /**
79. * ATAN2
80. *
81. * This function calculates the arc tangent of the two variables x and y. It is similar to
82. * calculating the arc tangent of y ÷ x, except that the signs of both arguments are used
83. * to determine the quadrant of the result.
84. * The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a
85. * point with coordinates (xCoordinate, yCoordinate). The angle is given in radians between
86. * -pi and pi, excluding -pi.
87. *
88. * Note that the Excel ATAN2() function accepts its arguments in the reverse order to the standard
89. * PHP atan2() function, so we need to reverse them here before calling the PHP atan() function.
90. *
91. * Excel Function:
92. * ATAN2(xCoordinate,yCoordinate)
93. *
94. * @access public
95. * @category Mathematical and Trigonometric Functions
96. * @param float $xCoordinate The x-coordinate of the point.
97. * @param float $yCoordinate The y-coordinate of the point.
98. * @return float The inverse tangent of the specified x- and y-coordinates.
99. */
100. public static function ATAN2($xCoordinate, $yCoordinate) {
101. $xCoordinate = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($xCoordinate);
102. $yCoordinate = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($yCoordinate);
104. if (($xCoordinate == 0) && ($yCoordinate == 0)) {
105. return PHPExcel_Calculation_Functions:: DIV0();
106. }
108. return atan2($yCoordinate, $xCoordinate);
109. } // function REVERSE_ATAN2()
112. /**
113. * CEILING
114. *
115. * Returns number rounded up, away from zero, to the nearest multiple of significance.
116. *
117. * @param float $number Number to round
118. * @param float $significance Significance
119. * @return float Rounded Number
120. */
121. public static function CEILING($number,$significance=null) {
122. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
123. $significance = PHPExcel_Calculation_Functions:: flattenSingleValue($significance);
125. if (( is_null($significance)) && ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC)) {
126. $significance = $number/abs($number);
127. }
129. if (( is_numeric($number)) && ( is_numeric($significance))) {
130. if (self:: SIGN($number) == self:: SIGN($significance)) {
131. if ($significance == 0.0) {
132. return 0;
133. }
134. return ceil($number / $significance) * $significance;
135. } else {
136. return PHPExcel_Calculation_Functions:: NaN();
137. }
138. }
139. return PHPExcel_Calculation_Functions:: VALUE();
140. } // function CEILING()
143. /**
144. * COMBIN
145. *
146. * Returns the number of combinations for a given number of items. Use COMBIN to
147. * determine the total possible number of groups for a given number of items.
148. *
149. * @param int $numObjs Number of different objects
150. * @param int $numInSet Number of objects in each combination
151. * @return int Number of combinations
152. */
153. public static function COMBIN($numObjs,$numInSet) {
154. $numObjs = PHPExcel_Calculation_Functions:: flattenSingleValue($numObjs);
155. $numInSet = PHPExcel_Calculation_Functions:: flattenSingleValue($numInSet);
157. if (( is_numeric($numObjs)) && ( is_numeric($numInSet))) {
158. if ($numObjs < $numInSet) {
159. return PHPExcel_Calculation_Functions:: NaN();
160. } elseif ($numInSet < 0) {
161. return PHPExcel_Calculation_Functions:: NaN();
162. }
163. return round(self:: FACT($numObjs) / self:: FACT($numObjs - $numInSet)) / self:: FACT($numInSet);
164. }
165. return PHPExcel_Calculation_Functions:: VALUE();
166. } // function COMBIN()
169. /**
170. * EVEN
171. *
172. * Returns number rounded up to the nearest even integer.
173. *
174. * @param float $number Number to round
175. * @return int Rounded Number
176. */
177. public static function EVEN($number) {
178. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
180. if ( is_null($number)) {
181. return 0;
182. } elseif ( is_numeric($number)) {
183. $significance = 2 * self:: SIGN($number);
184. return (int) self:: CEILING($number,$significance);
185. }
186. return PHPExcel_Calculation_Functions:: VALUE();
187. } // function EVEN()
190. /**
191. * FACT
192. *
193. * Returns the factorial of a number.
194. *
195. * @param float $factVal Factorial Value
196. * @return int Factorial
197. */
198. public static function FACT($factVal) {
199. $factVal = PHPExcel_Calculation_Functions:: flattenSingleValue($factVal);
201. if ( is_numeric($factVal)) {
202. if ($factVal < 0) {
203. return PHPExcel_Calculation_Functions:: NaN();
204. }
205. $factLoop = floor($factVal);
206. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) {
207. if ($factVal > $factLoop) {
208. return PHPExcel_Calculation_Functions:: NaN();
209. }
210. }
212. $factorial = 1;
213. while ($factLoop > 1) {
214. $factorial *= $factLoop--;
215. }
216. return $factorial ;
217. }
218. return PHPExcel_Calculation_Functions:: VALUE();
219. } // function FACT()
222. /**
223. * FACTDOUBLE
224. *
225. * Returns the double factorial of a number.
226. *
227. * @param float $factVal Factorial Value
228. * @return int Double Factorial
229. */
230. public static function FACTDOUBLE($factVal) {
231. $factLoop = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($factVal));
233. if ( is_numeric($factLoop)) {
234. if ($factVal < 0) {
235. return PHPExcel_Calculation_Functions:: NaN();
236. }
237. $factorial = 1;
238. while ($factLoop > 1) {
239. $factorial *= $factLoop--;
240. --$factLoop;
241. }
242. return $factorial ;
243. }
244. return PHPExcel_Calculation_Functions:: VALUE();
245. } // function FACTDOUBLE()
248. /**
249. * FLOOR
250. *
251. * Rounds number down, toward zero, to the nearest multiple of significance.
252. *
253. * @param float $number Number to round
254. * @param float $significance Significance
255. * @return float Rounded Number
256. */
257. public static function FLOOR($number,$significance=null) {
258. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
259. $significance = PHPExcel_Calculation_Functions:: flattenSingleValue($significance);
261. if (( is_null($significance)) && ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC)) {
262. $significance = $number/abs($number);
263. }
265. if (( is_numeric($number)) && ( is_numeric($significance))) {
266. if ((float) $significance == 0.0) {
267. return PHPExcel_Calculation_Functions:: DIV0();
268. }
269. if (self:: SIGN($number) == self:: SIGN($significance)) {
270. return floor($number / $significance) * $significance;
271. } else {
272. return PHPExcel_Calculation_Functions:: NaN();
273. }
274. }
275. return PHPExcel_Calculation_Functions:: VALUE();
276. } // function FLOOR()
279. /**
280. * GCD
281. *
282. * Returns the greatest common divisor of a series of numbers
283. *
284. * @param $array Values to calculate the Greatest Common Divisor
285. * @return int Greatest Common Divisor
286. */
287. public static function GCD() {
288. $returnValue = 1;
289. $allPoweredFactors = array();
290. // Loop through arguments
291. foreach( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $value) {
292. if ($value == 0) {
293. break;
294. }
295. $myFactors = self::_factors($value);
296. $myCountedFactors = array_count_values($myFactors);
297. $allValuesFactors[] = $myCountedFactors;
298. }
299. $allValuesCount = count($allValuesFactors);
300. $mergedArray = $allValuesFactors[0];
301. for ($i=1;$i < $allValuesCount; ++$i) {
302. $mergedArray = array_intersect_key($mergedArray,$allValuesFactors[$i]);
303. }
304. $mergedArrayValues = count($mergedArray);
305. if ($mergedArrayValues == 0) {
306. return $returnValue;
307. } elseif ($mergedArrayValues > 1) {
308. foreach($mergedArray as $mergedKey => $mergedValue) {
309. foreach($allValuesFactors as $highestPowerTest) {
310. foreach($highestPowerTest as $testKey => $testValue) {
311. if (($testKey == $mergedKey) && ($testValue < $mergedValue)) {
312. $mergedArray[$mergedKey] = $testValue;
313. $mergedValue = $testValue;
314. }
315. }
316. }
317. }
319. $returnValue = 1;
320. foreach($mergedArray as $key => $value) {
321. $returnValue *= pow($key,$value);
322. }
323. return $returnValue;
324. } else {
325. $keys = array_keys($mergedArray);
326. $key = $keys[0];
327. $value = $mergedArray[$key];
328. foreach($allValuesFactors as $testValue) {
329. foreach($testValue as $mergedKey => $mergedValue) {
330. if (($mergedKey == $key) && ($mergedValue < $value)) {
331. $value = $mergedValue;
332. }
333. }
334. }
335. return pow($key,$value);
336. }
337. } // function GCD()
340. /**
341. * INT
342. *
343. * Casts a floating point value to an integer
344. *
345. * @param float $number Number to cast to an integer
346. * @return integer Integer value
347. */
348. public static function INT($number) {
349. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
351. if ( is_numeric($number)) {
352. return (int) floor($number);
353. }
354. return PHPExcel_Calculation_Functions:: VALUE();
355. } // function INT()
358. /**
359. * LCM
360. *
361. * Returns the lowest common multiplier of a series of numbers
362. *
363. * @param $array Values to calculate the Lowest Common Multiplier
364. * @return int Lowest Common Multiplier
365. */
366. public static function LCM() {
367. $returnValue = 1;
368. $allPoweredFactors = array();
369. // Loop through arguments
370. foreach( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $value) {
371. if (! is_numeric($value)) {
372. return PHPExcel_Calculation_Functions:: VALUE();
373. }
374. if ($value == 0) {
375. return 0;
376. } elseif ($value < 0) {
377. return PHPExcel_Calculation_Functions:: NaN();
378. }
379. $myFactors = self::_factors( floor($value));
380. $myCountedFactors = array_count_values($myFactors);
381. $myPoweredFactors = array();
382. foreach($myCountedFactors as $myCountedFactor => $myCountedPower) {
383. $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor,$myCountedPower);
384. }
385. foreach($myPoweredFactors as $myPoweredValue => $myPoweredFactor) {
386. if ( array_key_exists($myPoweredValue,$allPoweredFactors)) {
387. if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) {
388. $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
389. }
390. } else {
391. $allPoweredFactors[$myPoweredValue] = $myPoweredFactor;
392. }
393. }
394. }
395. foreach($allPoweredFactors as $allPoweredFactor) {
396. $returnValue *= (integer) $allPoweredFactor;
397. }
398. return $returnValue;
399. } // function LCM()
402. /**
403. * LOG_BASE
404. *
405. * Returns the logarithm of a number to a specified base. The default base is 10.
406. *
407. * Excel Function:
408. * LOG(number[,base])
409. *
410. * @access public
411. * @category Mathematical and Trigonometric Functions
412. * @param float $value The positive real number for which you want the logarithm
413. * @param float $base The base of the logarithm. If base is omitted, it is assumed to be 10.
414. * @return float
415. */
416. public static function LOG_BASE($number, $base=10) {
417. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
418. $base = ( is_null($base)) ? 10 : (float) PHPExcel_Calculation_Functions:: flattenSingleValue($base);
420. return log($number, $base);
421. } // function LOG_BASE()
424. /**
425. * MDETERM
426. *
427. * @param array $matrixValues A matrix of values
428. * @return float
429. */
430. public static function MDETERM($matrixValues) {
431. $matrixData = array();
432. if (! is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
434. $row = $maxColumn = 0;
435. foreach($matrixValues as $matrixRow) {
436. $column = 0;
437. foreach($matrixRow as $matrixCell) {
438. if (( is_string($matrixCell)) || ($matrixCell === null)) {
439. return PHPExcel_Calculation_Functions:: VALUE();
440. }
441. $matrixData[$column][$row] = $matrixCell;
442. ++$column;
443. }
444. if ($column > $maxColumn) { $maxColumn = $column; }
445. ++$row;
446. }
447. if ($row != $maxColumn) { return PHPExcel_Calculation_Functions:: VALUE(); }
449. try {
450. $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
451. return $matrix->det();
452. } catch (Exception $ex) {
453. return PHPExcel_Calculation_Functions:: VALUE();
454. }
455. } // function MDETERM()
458. /**
459. * MINVERSE
460. *
461. * @param array $matrixValues A matrix of values
462. * @return array
463. */
464. public static function MINVERSE($matrixValues) {
465. $matrixData = array();
466. if (! is_array($matrixValues)) { $matrixValues = array(array($matrixValues)); }
468. $row = $maxColumn = 0;
469. foreach($matrixValues as $matrixRow) {
470. $column = 0;
471. foreach($matrixRow as $matrixCell) {
472. if (( is_string($matrixCell)) || ($matrixCell === null)) {
473. return PHPExcel_Calculation_Functions:: VALUE();
474. }
475. $matrixData[$column][$row] = $matrixCell;
476. ++$column;
477. }
478. if ($column > $maxColumn) { $maxColumn = $column; }
479. ++$row;
480. }
481. if ($row != $maxColumn) { return PHPExcel_Calculation_Functions:: VALUE(); }
483. try {
484. $matrix = new PHPExcel_Shared_JAMA_Matrix($matrixData);
485. return $matrix->inverse()->getArray();
486. } catch (Exception $ex) {
487. return PHPExcel_Calculation_Functions:: VALUE();
488. }
489. } // function MINVERSE()
492. /**
493. * MMULT
494. *
495. * @param array $matrixData1 A matrix of values
496. * @param array $matrixData2 A matrix of values
497. * @return array
498. */
499. public static function MMULT($matrixData1,$matrixData2) {
500. $matrixAData = $matrixBData = array();
501. if (! is_array($matrixData1)) { $matrixData1 = array(array($matrixData1)); }
502. if (! is_array($matrixData2)) { $matrixData2 = array(array($matrixData2)); }
504. $rowA = 0;
505. foreach($matrixData1 as $matrixRow) {
506. $columnA = 0;
507. foreach($matrixRow as $matrixCell) {
508. if (( is_string($matrixCell)) || ($matrixCell === null)) {
509. return PHPExcel_Calculation_Functions:: VALUE();
510. }
511. $matrixAData[$rowA][$columnA] = $matrixCell;
512. ++$columnA;
513. }
514. ++$rowA;
515. }
516. try {
517. $matrixA = new PHPExcel_Shared_JAMA_Matrix($matrixAData);
518. $rowB = 0;
519. foreach($matrixData2 as $matrixRow) {
520. $columnB = 0;
521. foreach($matrixRow as $matrixCell) {
522. if (( is_string($matrixCell)) || ($matrixCell === null)) {
523. return PHPExcel_Calculation_Functions:: VALUE();
524. }
525. $matrixBData[$rowB][$columnB] = $matrixCell;
526. ++$columnB;
527. }
528. ++$rowB;
529. }
530. $matrixB = new PHPExcel_Shared_JAMA_Matrix($matrixBData);
532. if (($rowA != $columnB) || ($rowB != $columnA)) {
533. return PHPExcel_Calculation_Functions:: VALUE();
534. }
536. return $matrixA->times($matrixB)->getArray();
537. } catch (Exception $ex) {
538. return PHPExcel_Calculation_Functions:: VALUE();
539. }
540. } // function MMULT()
543. /**
544. * MOD
545. *
546. * @param int $a Dividend
547. * @param int $b Divisor
548. * @return int Remainder
549. */
550. public static function MOD($a = 1, $b = 1) {
551. $a = PHPExcel_Calculation_Functions:: flattenSingleValue($a);
552. $b = PHPExcel_Calculation_Functions:: flattenSingleValue($b);
554. if ($b == 0.0) {
555. return PHPExcel_Calculation_Functions:: DIV0();
556. } elseif (($a < 0.0) && ($b > 0.0)) {
557. return $b - fmod( abs($a),$b);
558. } elseif (($a > 0.0) && ($b < 0.0)) {
559. return $b + fmod($a, abs($b));
560. }
562. return fmod($a,$b);
563. } // function MOD()
566. /**
567. * MROUND
568. *
569. * Rounds a number to the nearest multiple of a specified value
570. *
571. * @param float $number Number to round
572. * @param int $multiple Multiple to which you want to round $number
573. * @return float Rounded Number
574. */
575. public static function MROUND($number,$multiple) {
576. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
577. $multiple = PHPExcel_Calculation_Functions:: flattenSingleValue($multiple);
579. if (( is_numeric($number)) && ( is_numeric($multiple))) {
580. if ($multiple == 0) {
581. return 0;
582. }
583. if ((self:: SIGN($number)) == (self:: SIGN($multiple))) {
584. $multiplier = 1 / $multiple;
585. return round($number * $multiplier) / $multiplier;
586. }
587. return PHPExcel_Calculation_Functions:: NaN();
588. }
589. return PHPExcel_Calculation_Functions:: VALUE();
590. } // function MROUND()
593. /**
594. * MULTINOMIAL
595. *
596. * Returns the ratio of the factorial of a sum of values to the product of factorials.
597. *
598. * @param array of mixed Data Series
599. * @return float
600. */
601. public static function MULTINOMIAL() {
602. $summer = 0;
603. $divisor = 1;
604. // Loop through arguments
605. foreach ( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $arg) {
606. // Is it a numeric value?
607. if ( is_numeric($arg)) {
608. if ($arg < 1) {
609. return PHPExcel_Calculation_Functions:: NaN();
610. }
611. $summer += floor($arg);
612. $divisor *= self:: FACT($arg);
613. } else {
614. return PHPExcel_Calculation_Functions:: VALUE();
615. }
616. }
618. // Return
619. if ($summer > 0) {
620. $summer = self:: FACT($summer);
621. return $summer / $divisor;
622. }
623. return 0;
624. } // function MULTINOMIAL()
627. /**
628. * ODD
629. *
630. * Returns number rounded up to the nearest odd integer.
631. *
632. * @param float $number Number to round
633. * @return int Rounded Number
634. */
635. public static function ODD($number) {
636. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
638. if ( is_null($number)) {
639. return 1;
640. } elseif ( is_numeric($number)) {
641. $significance = self:: SIGN($number);
642. if ($significance == 0) {
643. return 1;
644. }
646. $result = self:: CEILING($number,$significance);
647. if ($result == self:: EVEN($result)) {
648. $result += $significance;
649. }
651. return (int) $result;
652. }
653. return PHPExcel_Calculation_Functions:: VALUE();
654. } // function ODD()
657. /**
658. * POWER
659. *
660. * Computes x raised to the power y.
661. *
662. * @param float $x
663. * @param float $y
664. * @return float
665. */
666. public static function POWER($x = 0, $y = 2) {
667. $x = PHPExcel_Calculation_Functions:: flattenSingleValue($x);
668. $y = PHPExcel_Calculation_Functions:: flattenSingleValue($y);
670. // Validate parameters
671. if ($x == 0 && $y <= 0) {
672. return PHPExcel_Calculation_Functions:: DIV0();
673. }
675. // Return
676. return pow($x, $y);
677. } // function POWER()
680. /**
681. * PRODUCT
682. *
683. * PRODUCT returns the product of all the values and cells referenced in the argument list.
684. *
685. * Excel Function:
686. * PRODUCT(value1[,value2[, ...]])
687. *
688. * @access public
689. * @category Mathematical and Trigonometric Functions
690. * @param mixed $arg,... Data values
691. * @return float
692. */
693. public static function PRODUCT() {
694. // Return value
695. $returnValue = null;
697. // Loop through arguments
698. foreach ( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $arg) {
699. // Is it a numeric value?
700. if (( is_numeric($arg)) && (! is_string($arg))) {
701. if ( is_null($returnValue)) {
702. $returnValue = $arg;
703. } else {
704. $returnValue *= $arg;
705. }
706. }
707. }
709. // Return
710. if ( is_null($returnValue)) {
711. return 0;
712. }
713. return $returnValue;
714. } // function PRODUCT()
717. /**
718. * QUOTIENT
719. *
720. * QUOTIENT function returns the integer portion of a division. Numerator is the divided number
721. * and denominator is the divisor.
722. *
723. * Excel Function:
724. * QUOTIENT(value1[,value2[, ...]])
725. *
726. * @access public
727. * @category Mathematical and Trigonometric Functions
728. * @param mixed $arg,... Data values
729. * @return float
730. */
731. public static function QUOTIENT() {
732. // Return value
733. $returnValue = null;
735. // Loop through arguments
736. foreach ( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $arg) {
737. // Is it a numeric value?
738. if (( is_numeric($arg)) && (! is_string($arg))) {
739. if ( is_null($returnValue)) {
740. $returnValue = ($arg == 0) ? 0 : $arg;
741. } else {
742. if (($returnValue == 0) || ($arg == 0)) {
743. $returnValue = 0;
744. } else {
745. $returnValue /= $arg;
746. }
747. }
748. }
749. }
751. // Return
752. return intval($returnValue);
753. } // function QUOTIENT()
756. /**
757. * RAND
758. *
759. * @param int $min Minimal value
760. * @param int $max Maximal value
761. * @return int Random number
762. */
763. public static function RAND($min = 0, $max = 0) {
764. $min = PHPExcel_Calculation_Functions:: flattenSingleValue($min);
765. $max = PHPExcel_Calculation_Functions:: flattenSingleValue($max);
767. if ($min == 0 && $max == 0) {
768. return ( rand(0,10000000)) / 10000000;
769. } else {
770. return rand($min, $max);
771. }
772. } // function RAND()
775. public static function ROMAN($aValue, $style=0) {
776. $aValue = (integer) PHPExcel_Calculation_Functions:: flattenSingleValue($aValue);
777. $style = ( is_null($style)) ? 0 : (integer) PHPExcel_Calculation_Functions:: flattenSingleValue($style);
778. if ((! is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) {
779. return PHPExcel_Calculation_Functions:: VALUE();
780. }
781. if ($aValue == 0) {
782. return '';
783. }
785. $mill = Array('', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM');
786. $cent = Array('', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM');
787. $tens = Array('', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC');
788. $ones = Array('', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX');
790. $roman = '';
791. while ($aValue > 5999) {
792. $roman .= 'M';
793. $aValue -= 1000;
794. }
795. $m = self::_romanCut($aValue, 1000); $aValue %= 1000;
796. $c = self::_romanCut($aValue, 100); $aValue %= 100;
797. $t = self::_romanCut($aValue, 10); $aValue %= 10;
799. return $roman.$mill[$m].$cent[$c].$tens[$t].$ones[$aValue];
800. } // function ROMAN()
803. /**
804. * ROUNDUP
805. *
806. * Rounds a number up to a specified number of decimal places
807. *
808. * @param float $number Number to round
809. * @param int $digits Number of digits to which you want to round $number
810. * @return float Rounded Number
811. */
812. public static function ROUNDUP($number,$digits) {
813. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
814. $digits = PHPExcel_Calculation_Functions:: flattenSingleValue($digits);
816. if (( is_numeric($number)) && ( is_numeric($digits))) {
817. $significance = pow(10,$digits);
818. if ($number < 0.0) {
819. return floor($number * $significance) / $significance;
820. } else {
821. return ceil($number * $significance) / $significance;
822. }
823. }
824. return PHPExcel_Calculation_Functions:: VALUE();
825. } // function ROUNDUP()
828. /**
829. * ROUNDDOWN
830. *
831. * Rounds a number down to a specified number of decimal places
832. *
833. * @param float $number Number to round
834. * @param int $digits Number of digits to which you want to round $number
835. * @return float Rounded Number
836. */
837. public static function ROUNDDOWN($number,$digits) {
838. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
839. $digits = PHPExcel_Calculation_Functions:: flattenSingleValue($digits);
841. if (( is_numeric($number)) && ( is_numeric($digits))) {
842. $significance = pow(10,$digits);
843. if ($number < 0.0) {
844. return ceil($number * $significance) / $significance;
845. } else {
846. return floor($number * $significance) / $significance;
847. }
848. }
849. return PHPExcel_Calculation_Functions:: VALUE();
850. } // function ROUNDDOWN()
853. /**
854. * SERIESSUM
855. *
856. * Returns the sum of a power series
857. *
858. * @param float $x Input value to the power series
859. * @param float $n Initial power to which you want to raise $x
860. * @param float $m Step by which to increase $n for each term in the series
861. * @param array of mixed Data Series
862. * @return float
863. */
864. public static function SERIESSUM() {
865. // Return value
866. $returnValue = 0;
868. // Loop through arguments
869. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
871. $x = array_shift($aArgs);
872. $n = array_shift($aArgs);
873. $m = array_shift($aArgs);
875. if (( is_numeric($x)) && ( is_numeric($n)) && ( is_numeric($m))) {
876. // Calculate
877. $i = 0;
878. foreach($aArgs as $arg) {
879. // Is it a numeric value?
880. if (( is_numeric($arg)) && (! is_string($arg))) {
881. $returnValue += $arg * pow($x,$n + ($m * $i++));
882. } else {
883. return PHPExcel_Calculation_Functions:: VALUE();
884. }
885. }
886. // Return
887. return $returnValue;
888. }
889. return PHPExcel_Calculation_Functions:: VALUE();
890. } // function SERIESSUM()
893. /**
894. * SIGN
895. *
896. * Determines the sign of a number. Returns 1 if the number is positive, zero (0)
897. * if the number is 0, and -1 if the number is negative.
898. *
899. * @param float $number Number to round
900. * @return int sign value
901. */
902. public static function SIGN($number) {
903. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
905. if ( is_numeric($number)) {
906. if ($number == 0.0) {
907. return 0;
908. }
909. return $number / abs($number);
910. }
911. return PHPExcel_Calculation_Functions:: VALUE();
912. } // function SIGN()
915. /**
916. * SQRTPI
917. *
918. * Returns the square root of (number * pi).
919. *
920. * @param float $number Number
921. * @return float Square Root of Number * Pi
922. */
923. public static function SQRTPI($number) {
924. $number = PHPExcel_Calculation_Functions:: flattenSingleValue($number);
926. if ( is_numeric($number)) {
927. if ($number < 0) {
928. return PHPExcel_Calculation_Functions:: NaN();
929. }
930. return sqrt($number * M_PI) ;
931. }
932. return PHPExcel_Calculation_Functions:: VALUE();
933. } // function SQRTPI()
936. /**
937. * SUBTOTAL
938. *
939. * Returns a subtotal in a list or database.
940. *
941. * @param int the number 1 to 11 that specifies which function to
942. * use in calculating subtotals within a list.
943. * @param array of mixed Data Series
944. * @return float
945. */
946. public static function SUBTOTAL() {
947. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
949. // Calculate
950. $subtotal = array_shift($aArgs);
952. if (( is_numeric($subtotal)) && (! is_string($subtotal))) {
953. switch($subtotal) {
954. case 1 :
955. return PHPExcel_Calculation_Statistical:: AVERAGE($aArgs);
956. break;
957. case 2 :
958. return PHPExcel_Calculation_Statistical:: COUNT($aArgs);
959. break;
960. case 3 :
961. return PHPExcel_Calculation_Statistical:: COUNTA($aArgs);
962. break;
963. case 4 :
964. return PHPExcel_Calculation_Statistical:: MAX($aArgs);
965. break;
966. case 5 :
967. return PHPExcel_Calculation_Statistical:: MIN($aArgs);
968. break;
969. case 6 :
970. return self:: PRODUCT($aArgs);
971. break;
972. case 7 :
973. return PHPExcel_Calculation_Statistical:: STDEV($aArgs);
974. break;
975. case 8 :
976. return PHPExcel_Calculation_Statistical:: STDEVP($aArgs);
977. break;
978. case 9 :
979. return self:: SUM($aArgs);
980. break;
981. case 10 :
982. return PHPExcel_Calculation_Statistical:: VARFunc($aArgs);
983. break;
984. case 11 :
985. return PHPExcel_Calculation_Statistical:: VARP($aArgs);
986. break;
987. }
988. }
989. return PHPExcel_Calculation_Functions:: VALUE();
990. } // function SUBTOTAL()
993. /**
994. * SUM
995. *
996. * SUM computes the sum of all the values and cells referenced in the argument list.
997. *
998. * Excel Function:
999. * SUM(value1[,value2[, ...]])
1000. *
1001. * @access public
1002. * @category Mathematical and Trigonometric Functions
1003. * @param mixed $arg,... Data values
1004. * @return float
1005. */
1006. public static function SUM() {
1007. // Return value
1008. $returnValue = 0;
1010. // Loop through the arguments
1011. foreach ( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $arg) {
1012. // Is it a numeric value?
1013. if (( is_numeric($arg)) && (! is_string($arg))) {
1014. $returnValue += $arg;
1015. }
1016. }
1018. // Return
1019. return $returnValue;
1020. } // function SUM()
1023. /**
1024. * SUMIF
1025. *
1026. * Counts the number of cells that contain numbers within the list of arguments
1027. *
1028. * Excel Function:
1029. * SUMIF(value1[,value2[, ...]],condition)
1030. *
1031. * @access public
1032. * @category Mathematical and Trigonometric Functions
1033. * @param mixed $arg,... Data values
1034. * @param string $condition The criteria that defines which cells will be summed.
1035. * @return float
1036. */
1037. public static function SUMIF($aArgs,$condition,$sumArgs = array()) {
1038. // Return value
1039. $returnValue = 0;
1041. $aArgs = PHPExcel_Calculation_Functions:: flattenArray($aArgs);
1042. $sumArgs = PHPExcel_Calculation_Functions:: flattenArray($sumArgs);
1043. if ( count($sumArgs) == 0) {
1044. $sumArgs = $aArgs;
1045. }
1046. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
1047. // Loop through arguments
1048. foreach ($aArgs as $key => $arg) {
1049. if (! is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult( strtoupper($arg)); }
1050. $testCondition = '='.$arg.$condition;
1051. if ( PHPExcel_Calculation:: getInstance()->_calculateFormulaValue($testCondition)) {
1052. // Is it a value within our criteria
1053. $returnValue += $sumArgs[$key];
1054. }
1055. }
1057. // Return
1058. return $returnValue;
1059. } // function SUMIF()
1062. /**
1063. * SUMPRODUCT
1064. *
1065. * @param mixed $value Value to check
1066. * @return float
1067. */
1068. public static function SUMPRODUCT() {
1069. $arrayList = func_get_args();
1071. $wrkArray = PHPExcel_Calculation_Functions:: flattenArray( array_shift($arrayList));
1072. $wrkCellCount = count($wrkArray);
1074. foreach($arrayList as $matrixData) {
1075. $array2 = PHPExcel_Calculation_Functions:: flattenArray($matrixData);
1076. $count = count($array2);
1077. if ($wrkCellCount != $count) {
1078. return PHPExcel_Calculation_Functions:: VALUE();
1079. }
1081. foreach ($array2 as $i => $val) {
1082. if ((( is_numeric($wrkArray[$i])) && (! is_string($wrkArray[$i]))) &&
1083. (( is_numeric($val)) && (! is_string($val)))) {
1084. $wrkArray[$i] *= $val;
1085. }
1086. }
1087. }
1089. return array_sum($wrkArray);
1090. } // function SUMPRODUCT()
1093. /**
1094. * SUMSQ
1095. *
1096. * SUMSQ returns the sum of the squares of the arguments
1097. *
1098. * Excel Function:
1099. * SUMSQ(value1[,value2[, ...]])
1100. *
1101. * @access public
1102. * @category Mathematical and Trigonometric Functions
1103. * @param mixed $arg,... Data values
1104. * @return float
1105. */
1106. public static function SUMSQ() {
1107. // Return value
1108. $returnValue = 0;
1110. // Loop through arguments
1111. foreach ( PHPExcel_Calculation_Functions:: flattenArray( func_get_args()) as $arg) {
1112. // Is it a numeric value?
1113. if (( is_numeric($arg)) && (! is_string($arg))) {
1114. $returnValue += ($arg * $arg);
1115. }
1116. }
1118. // Return
1119. return $returnValue;
1120. } // function SUMSQ()
1123. /**
1124. * SUMX2MY2
1125. *
1126. * @param mixed $value Value to check
1127. * @return float
1128. */
1129. public static function SUMX2MY2($matrixData1,$matrixData2) {
1130. $array1 = PHPExcel_Calculation_Functions:: flattenArray($matrixData1);
1131. $array2 = PHPExcel_Calculation_Functions:: flattenArray($matrixData2);
1132. $count1 = count($array1);
1133. $count2 = count($array2);
1134. if ($count1 < $count2) {
1135. $count = $count1;
1136. } else {
1137. $count = $count2;
1138. }
1140. $result = 0;
1141. for ($i = 0; $i < $count; ++$i) {
1142. if ((( is_numeric($array1[$i])) && (! is_string($array1[$i]))) &&
1143. (( is_numeric($array2[$i])) && (! is_string($array2[$i])))) {
1144. $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]);
1145. }
1146. }
1148. return $result;
1149. } // function SUMX2MY2()
1152. /**
1153. * SUMX2PY2
1154. *
1155. * @param mixed $value Value to check
1156. * @return float
1157. */
1158. public static function SUMX2PY2($matrixData1,$matrixData2) {
1159. $array1 = PHPExcel_Calculation_Functions:: flattenArray($matrixData1);
1160. $array2 = PHPExcel_Calculation_Functions:: flattenArray($matrixData2);
1161. $count1 = count($array1);
1162. $count2 = count($array2);
1163. if ($count1 < $count2) {
1164. $count = $count1;
1165. } else {
1166. $count = $count2;
1167. }
1169. $result = 0;
1170. for ($i = 0; $i < $count; ++$i) {
1171. if ((( is_numeric($array1[$i])) && (! is_string($array1[$i]))) &&
1172. (( is_numeric($array2[$i])) && (! is_string($array2[$i])))) {
1173. $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]);
1174. }
1175. }
1177. return $result;
1178. } // function SUMX2PY2()
1181. /**
1182. * SUMXMY2
1183. *
1184. * @param mixed $value Value to check
1185. * @return float
1186. */
1187. public static function SUMXMY2($matrixData1,$matrixData2) {
1188. $array1 = PHPExcel_Calculation_Functions:: flattenArray($matrixData1);
1189. $array2 = PHPExcel_Calculation_Functions:: flattenArray($matrixData2);
1190. $count1 = count($array1);
1191. $count2 = count($array2);
1192. if ($count1 < $count2) {
1193. $count = $count1;
1194. } else {
1195. $count = $count2;
1196. }
1198. $result = 0;
1199. for ($i = 0; $i < $count; ++$i) {
1200. if ((( is_numeric($array1[$i])) && (! is_string($array1[$i]))) &&
1201. (( is_numeric($array2[$i])) && (! is_string($array2[$i])))) {
1202. $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]);
1203. }
1204. }
1206. return $result;
1207. } // function SUMXMY2()
1210. /**
1211. * TRUNC
1212. *
1213. * Truncates value to the number of fractional digits by number_digits.
1214. *
1215. * @param float $value
1216. * @param int $number_digits
1217. * @return float Truncated value
1218. */
1219. public static function TRUNC($value = 0, $number_digits = 0) {
1220. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
1221. $number_digits = PHPExcel_Calculation_Functions:: flattenSingleValue($number_digits);
1223. // Validate parameters
1224. if ($number_digits < 0) {
1225. return PHPExcel_Calculation_Functions:: VALUE();
1226. }
1228. // Truncate
1229. if ($number_digits > 0) {
1230. $value = $value * pow(10, $number_digits);
1231. }
1232. $value = intval($value);
1233. if ($number_digits > 0) {
1234. $value = $value / pow(10, $number_digits);
1235. }
1237. // Return
1238. return $value;
1239. } // function TRUNC()
1241. } // class PHPExcel_Calculation_MathTrig

Documentation generated on Sun, 27 Feb 2011 16:32:39 -0800 by phpDocumentor 1.4.3