No Description

MathTrig.php 38KB

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