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