1 | <?php |
||
2 | |||
3 | namespace PhpOffice\PhpSpreadsheet\Calculation; |
||
4 | |||
5 | use Matrix\Exception as MatrixException; |
||
6 | use Matrix\Matrix; |
||
7 | |||
8 | class MathTrig |
||
9 | { |
||
10 | // |
||
11 | // Private method to return an array of the factors of the input value |
||
12 | // |
||
13 | 12 | private static function factors($value) |
|
14 | { |
||
15 | 12 | $startVal = floor(sqrt($value)); |
|
16 | |||
17 | 12 | $factorArray = []; |
|
18 | 12 | for ($i = $startVal; $i > 1; --$i) { |
|
19 | 11 | if (($value % $i) == 0) { |
|
20 | 8 | $factorArray = array_merge($factorArray, self::factors($value / $i)); |
|
21 | 8 | $factorArray = array_merge($factorArray, self::factors($i)); |
|
22 | 8 | if ($i <= sqrt($value)) { |
|
23 | 8 | break; |
|
24 | } |
||
25 | } |
||
26 | } |
||
27 | 12 | if (!empty($factorArray)) { |
|
28 | 8 | rsort($factorArray); |
|
29 | |||
30 | 8 | return $factorArray; |
|
31 | } |
||
32 | |||
33 | 12 | return [(int) $value]; |
|
34 | } |
||
35 | |||
36 | 6 | private static function romanCut($num, $n) |
|
37 | { |
||
38 | 6 | return ($num - ($num % $n)) / $n; |
|
39 | } |
||
40 | |||
41 | /** |
||
42 | * ATAN2. |
||
43 | * |
||
44 | * This function calculates the arc tangent of the two variables x and y. It is similar to |
||
45 | * calculating the arc tangent of y ÷ x, except that the signs of both arguments are used |
||
46 | * to determine the quadrant of the result. |
||
47 | * The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a |
||
48 | * point with coordinates (xCoordinate, yCoordinate). The angle is given in radians between |
||
49 | * -pi and pi, excluding -pi. |
||
50 | * |
||
51 | * Note that the Excel ATAN2() function accepts its arguments in the reverse order to the standard |
||
52 | * PHP atan2() function, so we need to reverse them here before calling the PHP atan() function. |
||
53 | * |
||
54 | * Excel Function: |
||
55 | * ATAN2(xCoordinate,yCoordinate) |
||
56 | * |
||
57 | * @category Mathematical and Trigonometric Functions |
||
58 | * |
||
59 | * @param float $xCoordinate the x-coordinate of the point |
||
60 | * @param float $yCoordinate the y-coordinate of the point |
||
61 | * |
||
62 | * @return float the inverse tangent of the specified x- and y-coordinates |
||
63 | */ |
||
64 | 16 | public static function ATAN2($xCoordinate = null, $yCoordinate = null) |
|
65 | { |
||
66 | 16 | $xCoordinate = Functions::flattenSingleValue($xCoordinate); |
|
67 | 16 | $yCoordinate = Functions::flattenSingleValue($yCoordinate); |
|
68 | |||
69 | 16 | $xCoordinate = ($xCoordinate !== null) ? $xCoordinate : 0.0; |
|
70 | 16 | $yCoordinate = ($yCoordinate !== null) ? $yCoordinate : 0.0; |
|
71 | |||
72 | 16 | if (((is_numeric($xCoordinate)) || (is_bool($xCoordinate))) && |
|
73 | 16 | ((is_numeric($yCoordinate))) || (is_bool($yCoordinate))) { |
|
74 | 15 | $xCoordinate = (float) $xCoordinate; |
|
75 | 15 | $yCoordinate = (float) $yCoordinate; |
|
76 | |||
77 | 15 | if (($xCoordinate == 0) && ($yCoordinate == 0)) { |
|
78 | 1 | return Functions::DIV0(); |
|
79 | } |
||
80 | |||
81 | 14 | return atan2($yCoordinate, $xCoordinate); |
|
82 | } |
||
83 | |||
84 | 1 | return Functions::VALUE(); |
|
85 | } |
||
86 | |||
87 | /** |
||
88 | * CEILING. |
||
89 | * |
||
90 | * Returns number rounded up, away from zero, to the nearest multiple of significance. |
||
91 | * For example, if you want to avoid using pennies in your prices and your product is |
||
92 | * priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the |
||
93 | * nearest nickel. |
||
94 | * |
||
95 | * Excel Function: |
||
96 | * CEILING(number[,significance]) |
||
97 | * |
||
98 | * @category Mathematical and Trigonometric Functions |
||
99 | * |
||
100 | * @param float $number the number you want to round |
||
101 | * @param float $significance the multiple to which you want to round |
||
102 | * |
||
103 | * @return float Rounded Number |
||
104 | */ |
||
105 | 43 | public static function CEILING($number, $significance = null) |
|
106 | { |
||
107 | 43 | $number = Functions::flattenSingleValue($number); |
|
108 | 43 | $significance = Functions::flattenSingleValue($significance); |
|
109 | |||
110 | 43 | if (($significance === null) && |
|
111 | 43 | (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC)) { |
|
112 | $significance = $number / abs($number); |
||
113 | } |
||
114 | |||
115 | 43 | if ((is_numeric($number)) && (is_numeric($significance))) { |
|
116 | 41 | if (($number == 0.0) || ($significance == 0.0)) { |
|
117 | 3 | return 0.0; |
|
118 | 38 | } elseif (self::SIGN($number) == self::SIGN($significance)) { |
|
119 | 35 | return ceil($number / $significance) * $significance; |
|
120 | } |
||
121 | |||
122 | 3 | return Functions::NAN(); |
|
123 | } |
||
124 | |||
125 | 2 | return Functions::VALUE(); |
|
126 | } |
||
127 | |||
128 | /** |
||
129 | * COMBIN. |
||
130 | * |
||
131 | * Returns the number of combinations for a given number of items. Use COMBIN to |
||
132 | * determine the total possible number of groups for a given number of items. |
||
133 | * |
||
134 | * Excel Function: |
||
135 | * COMBIN(numObjs,numInSet) |
||
136 | * |
||
137 | * @category Mathematical and Trigonometric Functions |
||
138 | * |
||
139 | * @param int $numObjs Number of different objects |
||
140 | * @param int $numInSet Number of objects in each combination |
||
141 | * |
||
142 | * @return int Number of combinations |
||
143 | */ |
||
144 | 24 | public static function COMBIN($numObjs, $numInSet) |
|
145 | { |
||
146 | 24 | $numObjs = Functions::flattenSingleValue($numObjs); |
|
147 | 24 | $numInSet = Functions::flattenSingleValue($numInSet); |
|
148 | |||
149 | 24 | if ((is_numeric($numObjs)) && (is_numeric($numInSet))) { |
|
150 | 23 | if ($numObjs < $numInSet) { |
|
151 | 3 | return Functions::NAN(); |
|
152 | 20 | } elseif ($numInSet < 0) { |
|
153 | 2 | return Functions::NAN(); |
|
154 | } |
||
155 | |||
156 | 18 | return round(self::FACT($numObjs) / self::FACT($numObjs - $numInSet)) / self::FACT($numInSet); |
|
157 | } |
||
158 | |||
159 | 1 | return Functions::VALUE(); |
|
160 | } |
||
161 | |||
162 | /** |
||
163 | * EVEN. |
||
164 | * |
||
165 | * Returns number rounded up to the nearest even integer. |
||
166 | * You can use this function for processing items that come in twos. For example, |
||
167 | * a packing crate accepts rows of one or two items. The crate is full when |
||
168 | * the number of items, rounded up to the nearest two, matches the crate's |
||
169 | * capacity. |
||
170 | * |
||
171 | * Excel Function: |
||
172 | * EVEN(number) |
||
173 | * |
||
174 | * @category Mathematical and Trigonometric Functions |
||
175 | * |
||
176 | * @param float $number Number to round |
||
177 | * |
||
178 | * @return int Rounded Number |
||
179 | */ |
||
180 | 25 | public static function EVEN($number) |
|
181 | { |
||
182 | 25 | $number = Functions::flattenSingleValue($number); |
|
183 | |||
184 | 25 | if ($number === null) { |
|
185 | 1 | return 0; |
|
186 | 24 | } elseif (is_bool($number)) { |
|
187 | 2 | $number = (int) $number; |
|
188 | } |
||
189 | |||
190 | 24 | if (is_numeric($number)) { |
|
191 | 23 | $significance = 2 * self::SIGN($number); |
|
192 | |||
193 | 23 | return (int) self::CEILING($number, $significance); |
|
194 | } |
||
195 | |||
196 | 1 | return Functions::VALUE(); |
|
197 | } |
||
198 | |||
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 | * @category Mathematical and Trigonometric Functions |
||
209 | * |
||
210 | * @param float $factVal Factorial Value |
||
211 | * |
||
212 | * @return int Factorial |
||
213 | */ |
||
214 | 145 | public static function FACT($factVal) |
|
215 | { |
||
216 | 145 | $factVal = Functions::flattenSingleValue($factVal); |
|
217 | |||
218 | 145 | if (is_numeric($factVal)) { |
|
219 | 144 | if ($factVal < 0) { |
|
220 | 1 | return Functions::NAN(); |
|
221 | } |
||
222 | 143 | $factLoop = floor($factVal); |
|
223 | 143 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { |
|
224 | if ($factVal > $factLoop) { |
||
225 | return Functions::NAN(); |
||
226 | } |
||
227 | } |
||
228 | |||
229 | 143 | $factorial = 1; |
|
230 | 143 | while ($factLoop > 1) { |
|
231 | 71 | $factorial *= $factLoop--; |
|
232 | } |
||
233 | |||
234 | 143 | return $factorial; |
|
235 | } |
||
236 | |||
237 | 1 | return Functions::VALUE(); |
|
238 | } |
||
239 | |||
240 | /** |
||
241 | * FACTDOUBLE. |
||
242 | * |
||
243 | * Returns the double factorial of a number. |
||
244 | * |
||
245 | * Excel Function: |
||
246 | * FACTDOUBLE(factVal) |
||
247 | * |
||
248 | * @category Mathematical and Trigonometric Functions |
||
249 | * |
||
250 | * @param float $factVal Factorial Value |
||
251 | * |
||
252 | * @return int Double Factorial |
||
253 | */ |
||
254 | 8 | public static function FACTDOUBLE($factVal) |
|
255 | { |
||
256 | 8 | $factLoop = Functions::flattenSingleValue($factVal); |
|
257 | |||
258 | 8 | if (is_numeric($factLoop)) { |
|
259 | 7 | $factLoop = floor($factLoop); |
|
260 | 7 | if ($factVal < 0) { |
|
261 | 1 | return Functions::NAN(); |
|
262 | } |
||
263 | 6 | $factorial = 1; |
|
264 | 6 | while ($factLoop > 1) { |
|
265 | 5 | $factorial *= $factLoop--; |
|
266 | 5 | --$factLoop; |
|
267 | } |
||
268 | |||
269 | 6 | return $factorial; |
|
270 | } |
||
271 | |||
272 | 1 | return Functions::VALUE(); |
|
273 | } |
||
274 | |||
275 | /** |
||
276 | * FLOOR. |
||
277 | * |
||
278 | * Rounds number down, toward zero, to the nearest multiple of significance. |
||
279 | * |
||
280 | * Excel Function: |
||
281 | * FLOOR(number[,significance]) |
||
282 | * |
||
283 | * @category Mathematical and Trigonometric Functions |
||
284 | * |
||
285 | * @param float $number Number to round |
||
286 | * @param float $significance Significance |
||
287 | * |
||
288 | * @return float Rounded Number |
||
289 | */ |
||
290 | 11 | public static function FLOOR($number, $significance = null) |
|
291 | { |
||
292 | 11 | $number = Functions::flattenSingleValue($number); |
|
293 | 11 | $significance = Functions::flattenSingleValue($significance); |
|
294 | |||
295 | 11 | if (($significance === null) && |
|
296 | 11 | (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC)) { |
|
297 | $significance = $number / abs($number); |
||
298 | } |
||
299 | |||
300 | 11 | if ((is_numeric($number)) && (is_numeric($significance))) { |
|
301 | 9 | if ($significance == 0.0) { |
|
302 | 1 | return Functions::DIV0(); |
|
303 | 8 | } elseif ($number == 0.0) { |
|
304 | return 0.0; |
||
305 | 8 | } elseif (self::SIGN($number) == self::SIGN($significance)) { |
|
306 | 6 | return floor($number / $significance) * $significance; |
|
307 | } |
||
308 | |||
309 | 2 | return Functions::NAN(); |
|
310 | } |
||
311 | |||
312 | 2 | return Functions::VALUE(); |
|
313 | } |
||
314 | |||
315 | 25 | private static function evaluateGCD($a, $b) |
|
316 | { |
||
317 | 25 | return $b ? self::evaluateGCD($b, $a % $b) : $a; |
|
318 | } |
||
319 | |||
320 | /** |
||
321 | * GCD. |
||
322 | * |
||
323 | * Returns the greatest common divisor of a series of numbers. |
||
324 | * The greatest common divisor is the largest integer that divides both |
||
325 | * number1 and number2 without a remainder. |
||
326 | * |
||
327 | * Excel Function: |
||
328 | * GCD(number1[,number2[, ...]]) |
||
329 | * |
||
330 | * @category Mathematical and Trigonometric Functions |
||
331 | * |
||
332 | * @param mixed ...$args Data values |
||
333 | * |
||
334 | * @return int Greatest Common Divisor |
||
335 | */ |
||
336 | 27 | public static function GCD(...$args) |
|
337 | { |
||
338 | 27 | $args = Functions::flattenArray($args); |
|
339 | // Loop through arguments |
||
340 | 27 | foreach (Functions::flattenArray($args) as $value) { |
|
341 | 27 | if (!is_numeric($value)) { |
|
342 | 1 | return Functions::VALUE(); |
|
343 | 27 | } elseif ($value < 0) { |
|
344 | 1 | return Functions::NAN(); |
|
345 | } |
||
346 | } |
||
347 | |||
348 | 25 | $gcd = (int) array_pop($args); |
|
349 | do { |
||
350 | 25 | $gcd = self::evaluateGCD($gcd, (int) array_pop($args)); |
|
351 | 25 | } while (!empty($args)); |
|
352 | |||
353 | 25 | return $gcd; |
|
354 | } |
||
355 | |||
356 | /** |
||
357 | * INT. |
||
358 | * |
||
359 | * Casts a floating point value to an integer |
||
360 | * |
||
361 | * Excel Function: |
||
362 | * INT(number) |
||
363 | * |
||
364 | * @category Mathematical and Trigonometric Functions |
||
365 | * |
||
366 | * @param float $number Number to cast to an integer |
||
367 | * |
||
368 | * @return int Integer value |
||
369 | */ |
||
370 | 19 | public static function INT($number) |
|
371 | { |
||
372 | 19 | $number = Functions::flattenSingleValue($number); |
|
373 | |||
374 | 19 | if ($number === null) { |
|
375 | 1 | return 0; |
|
376 | 18 | } elseif (is_bool($number)) { |
|
377 | 2 | return (int) $number; |
|
378 | } |
||
379 | 16 | if (is_numeric($number)) { |
|
380 | 15 | return (int) floor($number); |
|
381 | } |
||
382 | |||
383 | 1 | return Functions::VALUE(); |
|
384 | } |
||
385 | |||
386 | /** |
||
387 | * LCM. |
||
388 | * |
||
389 | * Returns the lowest common multiplier of a series of numbers |
||
390 | * The least common multiple is the smallest positive integer that is a multiple |
||
391 | * of all integer arguments number1, number2, and so on. Use LCM to add fractions |
||
392 | * with different denominators. |
||
393 | * |
||
394 | * Excel Function: |
||
395 | * LCM(number1[,number2[, ...]]) |
||
396 | * |
||
397 | * @category Mathematical and Trigonometric Functions |
||
398 | * |
||
399 | * @param mixed ...$args Data values |
||
400 | * |
||
401 | * @return int Lowest Common Multiplier |
||
402 | */ |
||
403 | 12 | public static function LCM(...$args) |
|
404 | { |
||
405 | 12 | $returnValue = 1; |
|
406 | 12 | $allPoweredFactors = []; |
|
407 | // Loop through arguments |
||
408 | 12 | foreach (Functions::flattenArray($args) as $value) { |
|
409 | 12 | if (!is_numeric($value)) { |
|
410 | 1 | return Functions::VALUE(); |
|
411 | } |
||
412 | 12 | if ($value == 0) { |
|
413 | 1 | return 0; |
|
414 | 12 | } elseif ($value < 0) { |
|
415 | 1 | return Functions::NAN(); |
|
416 | } |
||
417 | 12 | $myFactors = self::factors(floor($value)); |
|
418 | 12 | $myCountedFactors = array_count_values($myFactors); |
|
419 | 12 | $myPoweredFactors = []; |
|
420 | 12 | foreach ($myCountedFactors as $myCountedFactor => $myCountedPower) { |
|
421 | 12 | $myPoweredFactors[$myCountedFactor] = pow($myCountedFactor, $myCountedPower); |
|
422 | } |
||
423 | 12 | foreach ($myPoweredFactors as $myPoweredValue => $myPoweredFactor) { |
|
424 | 12 | if (isset($allPoweredFactors[$myPoweredValue])) { |
|
425 | 6 | if ($allPoweredFactors[$myPoweredValue] < $myPoweredFactor) { |
|
426 | 6 | $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; |
|
427 | } |
||
428 | } else { |
||
429 | 12 | $allPoweredFactors[$myPoweredValue] = $myPoweredFactor; |
|
430 | } |
||
431 | } |
||
432 | } |
||
433 | 9 | foreach ($allPoweredFactors as $allPoweredFactor) { |
|
434 | 9 | $returnValue *= (int) $allPoweredFactor; |
|
435 | } |
||
436 | |||
437 | 9 | return $returnValue; |
|
438 | } |
||
439 | |||
440 | /** |
||
441 | * LOG_BASE. |
||
442 | * |
||
443 | * Returns the logarithm of a number to a specified base. The default base is 10. |
||
444 | * |
||
445 | * Excel Function: |
||
446 | * LOG(number[,base]) |
||
447 | * |
||
448 | * @category Mathematical and Trigonometric Functions |
||
449 | * |
||
450 | * @param float $number The positive real number for which you want the logarithm |
||
451 | * @param float $base The base of the logarithm. If base is omitted, it is assumed to be 10. |
||
452 | * |
||
453 | * @return float |
||
454 | */ |
||
455 | 69 | public static function logBase($number = null, $base = 10) |
|
456 | { |
||
457 | 69 | $number = Functions::flattenSingleValue($number); |
|
458 | 69 | $base = ($base === null) ? 10 : (float) Functions::flattenSingleValue($base); |
|
459 | |||
460 | 69 | if ((!is_numeric($base)) || (!is_numeric($number))) { |
|
461 | 2 | return Functions::VALUE(); |
|
462 | } |
||
463 | 67 | if (($base <= 0) || ($number <= 0)) { |
|
464 | 18 | return Functions::NAN(); |
|
465 | } |
||
466 | |||
467 | 49 | return log($number, $base); |
|
468 | } |
||
469 | |||
470 | /** |
||
471 | * MDETERM. |
||
472 | * |
||
473 | * Returns the matrix determinant of an array. |
||
474 | * |
||
475 | * Excel Function: |
||
476 | * MDETERM(array) |
||
477 | * |
||
478 | * @category Mathematical and Trigonometric Functions |
||
479 | * |
||
480 | * @param array $matrixValues A matrix of values |
||
481 | * |
||
482 | * @return float |
||
483 | */ |
||
484 | 14 | public static function MDETERM($matrixValues) |
|
485 | { |
||
486 | 14 | $matrixData = []; |
|
487 | 14 | if (!is_array($matrixValues)) { |
|
488 | $matrixValues = [[$matrixValues]]; |
||
489 | } |
||
490 | |||
491 | 14 | $row = $maxColumn = 0; |
|
492 | 14 | foreach ($matrixValues as $matrixRow) { |
|
493 | 14 | if (!is_array($matrixRow)) { |
|
494 | $matrixRow = [$matrixRow]; |
||
495 | } |
||
496 | 14 | $column = 0; |
|
497 | 14 | foreach ($matrixRow as $matrixCell) { |
|
498 | 14 | if ((is_string($matrixCell)) || ($matrixCell === null)) { |
|
499 | 1 | return Functions::VALUE(); |
|
500 | } |
||
501 | 14 | $matrixData[$row][$column] = $matrixCell; |
|
502 | 14 | ++$column; |
|
503 | } |
||
504 | 14 | if ($column > $maxColumn) { |
|
505 | 14 | $maxColumn = $column; |
|
506 | } |
||
507 | 14 | ++$row; |
|
508 | } |
||
509 | |||
510 | 13 | $matrix = new Matrix($matrixData); |
|
511 | 13 | if (!$matrix->isSquare()) { |
|
512 | 1 | return Functions::VALUE(); |
|
513 | } |
||
514 | |||
515 | try { |
||
516 | 12 | return $matrix->determinant(); |
|
517 | } catch (MatrixException $ex) { |
||
518 | return Functions::VALUE(); |
||
519 | } |
||
520 | } |
||
521 | |||
522 | /** |
||
523 | * MINVERSE. |
||
524 | * |
||
525 | * Returns the inverse matrix for the matrix stored in an array. |
||
526 | * |
||
527 | * Excel Function: |
||
528 | * MINVERSE(array) |
||
529 | * |
||
530 | * @category Mathematical and Trigonometric Functions |
||
531 | * |
||
532 | * @param array $matrixValues A matrix of values |
||
533 | * |
||
534 | * @return array |
||
535 | */ |
||
536 | 10 | public static function MINVERSE($matrixValues) |
|
537 | { |
||
538 | 10 | $matrixData = []; |
|
539 | 10 | if (!is_array($matrixValues)) { |
|
540 | $matrixValues = [[$matrixValues]]; |
||
541 | } |
||
542 | |||
543 | 10 | $row = $maxColumn = 0; |
|
544 | 10 | foreach ($matrixValues as $matrixRow) { |
|
545 | 10 | if (!is_array($matrixRow)) { |
|
546 | $matrixRow = [$matrixRow]; |
||
547 | } |
||
548 | 10 | $column = 0; |
|
549 | 10 | foreach ($matrixRow as $matrixCell) { |
|
550 | 10 | if ((is_string($matrixCell)) || ($matrixCell === null)) { |
|
551 | return Functions::VALUE(); |
||
552 | } |
||
553 | 10 | $matrixData[$row][$column] = $matrixCell; |
|
554 | 10 | ++$column; |
|
555 | } |
||
556 | 10 | if ($column > $maxColumn) { |
|
557 | 10 | $maxColumn = $column; |
|
558 | } |
||
559 | 10 | ++$row; |
|
560 | } |
||
561 | |||
562 | 10 | $matrix = new Matrix($matrixData); |
|
563 | 10 | if (!$matrix->isSquare()) { |
|
564 | return Functions::VALUE(); |
||
565 | } |
||
566 | |||
567 | 10 | if ($matrix->determinant() == 0.0) { |
|
568 | return Functions::NAN(); |
||
569 | } |
||
570 | |||
571 | try { |
||
572 | 10 | return $matrix->inverse()->toArray(); |
|
573 | } catch (MatrixException $ex) { |
||
574 | return Functions::VALUE(); |
||
575 | } |
||
576 | } |
||
577 | |||
578 | /** |
||
579 | * MMULT. |
||
580 | * |
||
581 | * @param array $matrixData1 A matrix of values |
||
582 | * @param array $matrixData2 A matrix of values |
||
583 | * |
||
584 | * @return array |
||
585 | */ |
||
586 | 8 | public static function MMULT($matrixData1, $matrixData2) |
|
587 | { |
||
588 | 8 | $matrixAData = $matrixBData = []; |
|
589 | 8 | if (!is_array($matrixData1)) { |
|
590 | $matrixData1 = [[$matrixData1]]; |
||
591 | } |
||
592 | 8 | if (!is_array($matrixData2)) { |
|
593 | $matrixData2 = [[$matrixData2]]; |
||
594 | } |
||
595 | |||
596 | try { |
||
597 | 8 | $rowA = 0; |
|
598 | 8 | foreach ($matrixData1 as $matrixRow) { |
|
599 | 8 | if (!is_array($matrixRow)) { |
|
600 | $matrixRow = [$matrixRow]; |
||
601 | } |
||
602 | 8 | $columnA = 0; |
|
603 | 8 | foreach ($matrixRow as $matrixCell) { |
|
604 | 8 | if ((!is_numeric($matrixCell)) || ($matrixCell === null)) { |
|
605 | return Functions::VALUE(); |
||
606 | } |
||
607 | 8 | $matrixAData[$rowA][$columnA] = $matrixCell; |
|
608 | 8 | ++$columnA; |
|
609 | } |
||
610 | 8 | ++$rowA; |
|
611 | } |
||
612 | 8 | $matrixA = new Matrix($matrixAData); |
|
613 | 8 | $rowB = 0; |
|
614 | 8 | foreach ($matrixData2 as $matrixRow) { |
|
615 | 8 | if (!is_array($matrixRow)) { |
|
616 | 1 | $matrixRow = [$matrixRow]; |
|
617 | } |
||
618 | 8 | $columnB = 0; |
|
619 | 8 | foreach ($matrixRow as $matrixCell) { |
|
620 | 8 | if ((!is_numeric($matrixCell)) || ($matrixCell === null)) { |
|
621 | return Functions::VALUE(); |
||
622 | } |
||
623 | 8 | $matrixBData[$rowB][$columnB] = $matrixCell; |
|
624 | 8 | ++$columnB; |
|
625 | } |
||
626 | 8 | ++$rowB; |
|
627 | } |
||
628 | 8 | $matrixB = new Matrix($matrixBData); |
|
629 | |||
630 | 8 | if ($columnA != $rowB) { |
|
631 | 2 | return Functions::VALUE(); |
|
632 | } |
||
633 | |||
634 | 6 | return $matrixA->multiply($matrixB)->toArray(); |
|
635 | } catch (MatrixException $ex) { |
||
636 | return Functions::VALUE(); |
||
637 | } |
||
638 | } |
||
639 | |||
640 | /** |
||
641 | * MOD. |
||
642 | * |
||
643 | * @param int $a Dividend |
||
644 | * @param int $b Divisor |
||
645 | * |
||
646 | * @return int Remainder |
||
647 | */ |
||
648 | 10 | public static function MOD($a = 1, $b = 1) |
|
649 | { |
||
650 | 10 | $a = (float) Functions::flattenSingleValue($a); |
|
651 | 10 | $b = (float) Functions::flattenSingleValue($b); |
|
652 | |||
653 | 10 | if ($b == 0.0) { |
|
654 | 1 | return Functions::DIV0(); |
|
655 | 9 | } elseif (($a < 0.0) && ($b > 0.0)) { |
|
656 | 1 | return $b - fmod(abs($a), $b); |
|
657 | 8 | } elseif (($a > 0.0) && ($b < 0.0)) { |
|
658 | 2 | return $b + fmod($a, abs($b)); |
|
659 | } |
||
660 | |||
661 | 6 | return fmod($a, $b); |
|
662 | } |
||
663 | |||
664 | /** |
||
665 | * MROUND. |
||
666 | * |
||
667 | * Rounds a number to the nearest multiple of a specified value |
||
668 | * |
||
669 | * @param float $number Number to round |
||
670 | * @param int $multiple Multiple to which you want to round $number |
||
671 | * |
||
672 | * @return float Rounded Number |
||
673 | */ |
||
674 | 13 | public static function MROUND($number, $multiple) |
|
675 | { |
||
676 | 13 | $number = Functions::flattenSingleValue($number); |
|
677 | 13 | $multiple = Functions::flattenSingleValue($multiple); |
|
678 | |||
679 | 13 | if ((is_numeric($number)) && (is_numeric($multiple))) { |
|
680 | 11 | if ($multiple == 0) { |
|
681 | 1 | return 0; |
|
682 | } |
||
683 | 10 | if ((self::SIGN($number)) == (self::SIGN($multiple))) { |
|
684 | 9 | $multiplier = 1 / $multiple; |
|
685 | |||
686 | 9 | return round($number * $multiplier) / $multiplier; |
|
687 | } |
||
688 | |||
689 | 1 | return Functions::NAN(); |
|
690 | } |
||
691 | |||
692 | 2 | return Functions::VALUE(); |
|
693 | } |
||
694 | |||
695 | /** |
||
696 | * MULTINOMIAL. |
||
697 | * |
||
698 | * Returns the ratio of the factorial of a sum of values to the product of factorials. |
||
699 | * |
||
700 | * @param array of mixed Data Series |
||
701 | * |
||
702 | * @return float |
||
703 | */ |
||
704 | 2 | public static function MULTINOMIAL(...$args) |
|
705 | { |
||
706 | 2 | $summer = 0; |
|
707 | 2 | $divisor = 1; |
|
708 | // Loop through arguments |
||
709 | 2 | foreach (Functions::flattenArray($args) as $arg) { |
|
710 | // Is it a numeric value? |
||
711 | 2 | if (is_numeric($arg)) { |
|
712 | 2 | if ($arg < 1) { |
|
713 | return Functions::NAN(); |
||
714 | } |
||
715 | 2 | $summer += floor($arg); |
|
716 | 2 | $divisor *= self::FACT($arg); |
|
717 | } else { |
||
718 | return Functions::VALUE(); |
||
719 | } |
||
720 | } |
||
721 | |||
722 | // Return |
||
723 | 2 | if ($summer > 0) { |
|
724 | 2 | $summer = self::FACT($summer); |
|
725 | |||
726 | 2 | return $summer / $divisor; |
|
727 | } |
||
728 | |||
729 | return 0; |
||
730 | } |
||
731 | |||
732 | /** |
||
733 | * ODD. |
||
734 | * |
||
735 | * Returns number rounded up to the nearest odd integer. |
||
736 | * |
||
737 | * @param float $number Number to round |
||
738 | * |
||
739 | * @return int Rounded Number |
||
740 | */ |
||
741 | 13 | public static function ODD($number) |
|
742 | { |
||
743 | 13 | $number = Functions::flattenSingleValue($number); |
|
744 | |||
745 | 13 | if ($number === null) { |
|
746 | 1 | return 1; |
|
747 | 12 | } elseif (is_bool($number)) { |
|
748 | 2 | return 1; |
|
749 | 10 | } elseif (is_numeric($number)) { |
|
750 | 9 | $significance = self::SIGN($number); |
|
751 | 9 | if ($significance == 0) { |
|
752 | 1 | return 1; |
|
753 | } |
||
754 | |||
755 | 8 | $result = self::CEILING($number, $significance); |
|
756 | 8 | if ($result == self::EVEN($result)) { |
|
757 | 5 | $result += $significance; |
|
758 | } |
||
759 | |||
760 | 8 | return (int) $result; |
|
761 | } |
||
762 | |||
763 | 1 | return Functions::VALUE(); |
|
764 | } |
||
765 | |||
766 | /** |
||
767 | * POWER. |
||
768 | * |
||
769 | * Computes x raised to the power y. |
||
770 | * |
||
771 | * @param float $x |
||
772 | * @param float $y |
||
773 | * |
||
774 | * @return float |
||
775 | */ |
||
776 | 81 | public static function POWER($x = 0, $y = 2) |
|
777 | { |
||
778 | 81 | $x = Functions::flattenSingleValue($x); |
|
779 | 81 | $y = Functions::flattenSingleValue($y); |
|
780 | |||
781 | // Validate parameters |
||
782 | 81 | if ($x == 0.0 && $y == 0.0) { |
|
783 | 1 | return Functions::NAN(); |
|
784 | 80 | } elseif ($x == 0.0 && $y < 0.0) { |
|
785 | 2 | return Functions::DIV0(); |
|
786 | } |
||
787 | |||
788 | // Return |
||
789 | 78 | $result = pow($x, $y); |
|
790 | |||
791 | 78 | return (!is_nan($result) && !is_infinite($result)) ? $result : Functions::NAN(); |
|
792 | } |
||
793 | |||
794 | /** |
||
795 | * PRODUCT. |
||
796 | * |
||
797 | * PRODUCT returns the product of all the values and cells referenced in the argument list. |
||
798 | * |
||
799 | * Excel Function: |
||
800 | * PRODUCT(value1[,value2[, ...]]) |
||
801 | * |
||
802 | * @category Mathematical and Trigonometric Functions |
||
803 | * |
||
804 | * @param mixed ...$args Data values |
||
805 | * |
||
806 | * @return float |
||
807 | */ |
||
808 | 10 | public static function PRODUCT(...$args) |
|
809 | { |
||
810 | // Return value |
||
811 | 10 | $returnValue = null; |
|
812 | |||
813 | // Loop through arguments |
||
814 | 10 | foreach (Functions::flattenArray($args) as $arg) { |
|
815 | // Is it a numeric value? |
||
816 | 10 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
817 | 10 | if ($returnValue === null) { |
|
818 | 10 | $returnValue = $arg; |
|
819 | } else { |
||
820 | 10 | $returnValue *= $arg; |
|
821 | } |
||
822 | } |
||
823 | } |
||
824 | |||
825 | // Return |
||
826 | 10 | if ($returnValue === null) { |
|
827 | return 0; |
||
828 | } |
||
829 | |||
830 | 10 | return $returnValue; |
|
831 | } |
||
832 | |||
833 | /** |
||
834 | * QUOTIENT. |
||
835 | * |
||
836 | * QUOTIENT function returns the integer portion of a division. Numerator is the divided number |
||
837 | * and denominator is the divisor. |
||
838 | * |
||
839 | * Excel Function: |
||
840 | * QUOTIENT(value1[,value2[, ...]]) |
||
841 | * |
||
842 | * @category Mathematical and Trigonometric Functions |
||
843 | * |
||
844 | * @param mixed ...$args Data values |
||
845 | * |
||
846 | * @return float |
||
847 | */ |
||
848 | 6 | public static function QUOTIENT(...$args) |
|
849 | { |
||
850 | // Return value |
||
851 | 6 | $returnValue = null; |
|
852 | |||
853 | // Loop through arguments |
||
854 | 6 | foreach (Functions::flattenArray($args) as $arg) { |
|
855 | // Is it a numeric value? |
||
856 | 6 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
857 | 6 | if ($returnValue === null) { |
|
858 | 6 | $returnValue = ($arg == 0) ? 0 : $arg; |
|
859 | } else { |
||
860 | 6 | if (($returnValue == 0) || ($arg == 0)) { |
|
861 | $returnValue = 0; |
||
862 | } else { |
||
863 | 6 | $returnValue /= $arg; |
|
864 | } |
||
865 | } |
||
866 | } |
||
867 | } |
||
868 | |||
869 | // Return |
||
870 | 6 | return (int) $returnValue; |
|
871 | } |
||
872 | |||
873 | /** |
||
874 | * RAND. |
||
875 | * |
||
876 | * @param int $min Minimal value |
||
877 | * @param int $max Maximal value |
||
878 | * |
||
879 | * @return int Random number |
||
880 | */ |
||
881 | 3 | public static function RAND($min = 0, $max = 0) |
|
882 | { |
||
883 | 3 | $min = Functions::flattenSingleValue($min); |
|
884 | 3 | $max = Functions::flattenSingleValue($max); |
|
885 | |||
886 | 3 | if ($min == 0 && $max == 0) { |
|
887 | 1 | return (mt_rand(0, 10000000)) / 10000000; |
|
888 | } |
||
889 | |||
890 | 3 | return mt_rand($min, $max); |
|
891 | } |
||
892 | |||
893 | 6 | public static function ROMAN($aValue, $style = 0) |
|
894 | { |
||
895 | 6 | $aValue = Functions::flattenSingleValue($aValue); |
|
896 | 6 | $style = ($style === null) ? 0 : (int) Functions::flattenSingleValue($style); |
|
897 | 6 | if ((!is_numeric($aValue)) || ($aValue < 0) || ($aValue >= 4000)) { |
|
898 | return Functions::VALUE(); |
||
899 | } |
||
900 | 6 | $aValue = (int) $aValue; |
|
901 | 6 | if ($aValue == 0) { |
|
902 | return ''; |
||
903 | } |
||
904 | |||
905 | 6 | $mill = ['', 'M', 'MM', 'MMM', 'MMMM', 'MMMMM']; |
|
906 | 6 | $cent = ['', 'C', 'CC', 'CCC', 'CD', 'D', 'DC', 'DCC', 'DCCC', 'CM']; |
|
907 | 6 | $tens = ['', 'X', 'XX', 'XXX', 'XL', 'L', 'LX', 'LXX', 'LXXX', 'XC']; |
|
908 | 6 | $ones = ['', 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX']; |
|
909 | |||
910 | 6 | $roman = ''; |
|
911 | 6 | while ($aValue > 5999) { |
|
912 | $roman .= 'M'; |
||
913 | $aValue -= 1000; |
||
914 | } |
||
915 | 6 | $m = self::romanCut($aValue, 1000); |
|
916 | 6 | $aValue %= 1000; |
|
917 | 6 | $c = self::romanCut($aValue, 100); |
|
918 | 6 | $aValue %= 100; |
|
919 | 6 | $t = self::romanCut($aValue, 10); |
|
920 | 6 | $aValue %= 10; |
|
921 | |||
922 | 6 | return $roman . $mill[$m] . $cent[$c] . $tens[$t] . $ones[$aValue]; |
|
923 | } |
||
924 | |||
925 | /** |
||
926 | * ROUNDUP. |
||
927 | * |
||
928 | * Rounds a number up to a specified number of decimal places |
||
929 | * |
||
930 | * @param float $number Number to round |
||
931 | * @param int $digits Number of digits to which you want to round $number |
||
932 | * |
||
933 | * @return float Rounded Number |
||
934 | */ |
||
935 | 14 | public static function ROUNDUP($number, $digits) |
|
936 | { |
||
937 | 14 | $number = Functions::flattenSingleValue($number); |
|
938 | 14 | $digits = Functions::flattenSingleValue($digits); |
|
939 | |||
940 | 14 | if ((is_numeric($number)) && (is_numeric($digits))) { |
|
941 | 12 | $significance = pow(10, (int) $digits); |
|
942 | 12 | if ($number < 0.0) { |
|
943 | 2 | return floor($number * $significance) / $significance; |
|
944 | } |
||
945 | |||
946 | 10 | return ceil($number * $significance) / $significance; |
|
947 | } |
||
948 | |||
949 | 2 | return Functions::VALUE(); |
|
950 | } |
||
951 | |||
952 | /** |
||
953 | * ROUNDDOWN. |
||
954 | * |
||
955 | * Rounds a number down to a specified number of decimal places |
||
956 | * |
||
957 | * @param float $number Number to round |
||
958 | * @param int $digits Number of digits to which you want to round $number |
||
959 | * |
||
960 | * @return float Rounded Number |
||
961 | */ |
||
962 | 14 | public static function ROUNDDOWN($number, $digits) |
|
963 | { |
||
964 | 14 | $number = Functions::flattenSingleValue($number); |
|
965 | 14 | $digits = Functions::flattenSingleValue($digits); |
|
966 | |||
967 | 14 | if ((is_numeric($number)) && (is_numeric($digits))) { |
|
968 | 12 | $significance = pow(10, (int) $digits); |
|
969 | 12 | if ($number < 0.0) { |
|
970 | 2 | return ceil($number * $significance) / $significance; |
|
971 | } |
||
972 | |||
973 | 10 | return floor($number * $significance) / $significance; |
|
974 | } |
||
975 | |||
976 | 2 | return Functions::VALUE(); |
|
977 | } |
||
978 | |||
979 | /** |
||
980 | * SERIESSUM. |
||
981 | * |
||
982 | * Returns the sum of a power series |
||
983 | * |
||
984 | * @param float $x Input value to the power series |
||
985 | * @param float $n Initial power to which you want to raise $x |
||
986 | * @param float $m Step by which to increase $n for each term in the series |
||
987 | * @param array of mixed Data Series |
||
988 | * |
||
989 | * @return float |
||
990 | */ |
||
991 | 2 | public static function SERIESSUM(...$args) |
|
992 | { |
||
993 | 2 | $returnValue = 0; |
|
994 | |||
995 | // Loop through arguments |
||
996 | 2 | $aArgs = Functions::flattenArray($args); |
|
997 | |||
998 | 2 | $x = array_shift($aArgs); |
|
999 | 2 | $n = array_shift($aArgs); |
|
1000 | 2 | $m = array_shift($aArgs); |
|
1001 | |||
1002 | 2 | if ((is_numeric($x)) && (is_numeric($n)) && (is_numeric($m))) { |
|
1003 | // Calculate |
||
1004 | 2 | $i = 0; |
|
1005 | 2 | foreach ($aArgs as $arg) { |
|
1006 | // Is it a numeric value? |
||
1007 | 2 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
1008 | 2 | $returnValue += $arg * pow($x, $n + ($m * $i++)); |
|
1009 | } else { |
||
1010 | return Functions::VALUE(); |
||
1011 | } |
||
1012 | } |
||
1013 | |||
1014 | 2 | return $returnValue; |
|
1015 | } |
||
1016 | |||
1017 | return Functions::VALUE(); |
||
1018 | } |
||
1019 | |||
1020 | /** |
||
1021 | * SIGN. |
||
1022 | * |
||
1023 | * Determines the sign of a number. Returns 1 if the number is positive, zero (0) |
||
1024 | * if the number is 0, and -1 if the number is negative. |
||
1025 | * |
||
1026 | * @param float $number Number to round |
||
1027 | * |
||
1028 | * @return int sign value |
||
1029 | */ |
||
1030 | 72 | public static function SIGN($number) |
|
1031 | { |
||
1032 | 72 | $number = Functions::flattenSingleValue($number); |
|
1033 | |||
1034 | 72 | if (is_bool($number)) { |
|
1035 | 2 | return (int) $number; |
|
1036 | } |
||
1037 | 70 | if (is_numeric($number)) { |
|
1038 | 69 | if ($number == 0.0) { |
|
1039 | 4 | return 0; |
|
1040 | } |
||
1041 | |||
1042 | 65 | return $number / abs($number); |
|
1043 | } |
||
1044 | |||
1045 | 1 | return Functions::VALUE(); |
|
1046 | } |
||
1047 | |||
1048 | /** |
||
1049 | * SQRTPI. |
||
1050 | * |
||
1051 | * Returns the square root of (number * pi). |
||
1052 | * |
||
1053 | * @param float $number Number |
||
1054 | * |
||
1055 | * @return float Square Root of Number * Pi |
||
1056 | */ |
||
1057 | 15 | public static function SQRTPI($number) |
|
1058 | { |
||
1059 | 15 | $number = Functions::flattenSingleValue($number); |
|
1060 | |||
1061 | 15 | if (is_numeric($number)) { |
|
1062 | 14 | if ($number < 0) { |
|
1063 | 3 | return Functions::NAN(); |
|
1064 | } |
||
1065 | |||
1066 | 11 | return sqrt($number * M_PI); |
|
1067 | } |
||
1068 | |||
1069 | 1 | return Functions::VALUE(); |
|
1070 | } |
||
1071 | |||
1072 | 11 | protected static function filterHiddenArgs($cellReference, $args) |
|
1073 | { |
||
1074 | 11 | return array_filter( |
|
1075 | 11 | $args, |
|
1076 | function ($index) use ($cellReference) { |
||
1077 | 11 | list(, $row, $column) = explode('.', $index); |
|
1078 | |||
1079 | 11 | return $cellReference->getWorksheet()->getRowDimension($row)->getVisible() && |
|
1080 | 11 | $cellReference->getWorksheet()->getColumnDimension($column)->getVisible(); |
|
1081 | 11 | }, |
|
1082 | 11 | ARRAY_FILTER_USE_KEY |
|
1083 | ); |
||
1084 | } |
||
1085 | |||
1086 | 23 | protected static function filterFormulaArgs($cellReference, $args) |
|
1087 | { |
||
1088 | 23 | return array_filter( |
|
1089 | 23 | $args, |
|
1090 | 23 | function ($index) use ($cellReference) { |
|
1091 | 23 | list(, $row, $column) = explode('.', $index); |
|
1092 | 23 | if ($cellReference->getWorksheet()->cellExists($column . $row)) { |
|
1093 | //take this cell out if it contains the SUBTOTAL or AGGREGATE functions in a formula |
||
1094 | 23 | $isFormula = $cellReference->getWorksheet()->getCell($column . $row)->isFormula(); |
|
1095 | 23 | $cellFormula = !preg_match('/^=.*\b(SUBTOTAL|AGGREGATE)\s*\(/i', $cellReference->getWorksheet()->getCell($column . $row)->getValue()); |
|
1096 | |||
1097 | 23 | return !$isFormula || $cellFormula; |
|
1098 | } |
||
1099 | |||
1100 | return true; |
||
1101 | 23 | }, |
|
1102 | 23 | ARRAY_FILTER_USE_KEY |
|
1103 | ); |
||
1104 | } |
||
1105 | |||
1106 | /** |
||
1107 | * SUBTOTAL. |
||
1108 | * |
||
1109 | * Returns a subtotal in a list or database. |
||
1110 | * |
||
1111 | * @param int the number 1 to 11 that specifies which function to |
||
1112 | * use in calculating subtotals within a range |
||
1113 | * list |
||
1114 | * Numbers 101 to 111 shadow the functions of 1 to 11 |
||
1115 | * but ignore any values in the range that are |
||
1116 | * in hidden rows or columns |
||
1117 | * @param array of mixed Data Series |
||
1118 | * |
||
1119 | * @return float |
||
1120 | */ |
||
1121 | 23 | public static function SUBTOTAL(...$args) |
|
1122 | { |
||
1123 | 23 | $cellReference = array_pop($args); |
|
1124 | 23 | $aArgs = Functions::flattenArrayIndexed($args); |
|
1125 | 23 | $subtotal = array_shift($aArgs); |
|
1126 | |||
1127 | // Calculate |
||
1128 | 23 | if ((is_numeric($subtotal)) && (!is_string($subtotal))) { |
|
1129 | 23 | if ($subtotal > 100) { |
|
1130 | 11 | $aArgs = self::filterHiddenArgs($cellReference, $aArgs); |
|
1131 | 11 | $subtotal -= 100; |
|
1132 | } |
||
1133 | |||
1134 | 23 | $aArgs = self::filterFormulaArgs($cellReference, $aArgs); |
|
1135 | 23 | switch ($subtotal) { |
|
1136 | 23 | case 1: |
|
1137 | 2 | return Statistical::AVERAGE($aArgs); |
|
1138 | 21 | case 2: |
|
1139 | 2 | return Statistical::COUNT($aArgs); |
|
1140 | 19 | case 3: |
|
1141 | 2 | return Statistical::COUNTA($aArgs); |
|
1142 | 17 | case 4: |
|
1143 | 2 | return Statistical::MAX($aArgs); |
|
1144 | 15 | case 5: |
|
1145 | 2 | return Statistical::MIN($aArgs); |
|
1146 | 13 | case 6: |
|
1147 | 2 | return self::PRODUCT($aArgs); |
|
1148 | 11 | case 7: |
|
1149 | 2 | return Statistical::STDEV($aArgs); |
|
1150 | 9 | case 8: |
|
1151 | 2 | return Statistical::STDEVP($aArgs); |
|
1152 | 7 | case 9: |
|
1153 | 3 | return self::SUM($aArgs); |
|
1154 | 4 | case 10: |
|
1155 | 2 | return Statistical::VARFunc($aArgs); |
|
1156 | 2 | case 11: |
|
1157 | 2 | return Statistical::VARP($aArgs); |
|
1158 | } |
||
1159 | } |
||
1160 | |||
1161 | return Functions::VALUE(); |
||
0 ignored issues
–
show
Bug
Best Practice
introduced
by
Loading history...
|
|||
1162 | } |
||
1163 | |||
1164 | /** |
||
1165 | * SUM. |
||
1166 | * |
||
1167 | * SUM computes the sum of all the values and cells referenced in the argument list. |
||
1168 | * |
||
1169 | * Excel Function: |
||
1170 | * SUM(value1[,value2[, ...]]) |
||
1171 | * |
||
1172 | * @category Mathematical and Trigonometric Functions |
||
1173 | * |
||
1174 | * @param mixed ...$args Data values |
||
1175 | * |
||
1176 | * @return float |
||
1177 | */ |
||
1178 | 28 | public static function SUM(...$args) |
|
1179 | { |
||
1180 | 28 | $returnValue = 0; |
|
1181 | |||
1182 | // Loop through the arguments |
||
1183 | 28 | foreach (Functions::flattenArray($args) as $arg) { |
|
1184 | // Is it a numeric value? |
||
1185 | 28 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
1186 | 28 | $returnValue += $arg; |
|
1187 | } |
||
1188 | } |
||
1189 | |||
1190 | 28 | return $returnValue; |
|
1191 | } |
||
1192 | |||
1193 | /** |
||
1194 | * SUMIF. |
||
1195 | * |
||
1196 | * Counts the number of cells that contain numbers within the list of arguments |
||
1197 | * |
||
1198 | * Excel Function: |
||
1199 | * SUMIF(value1[,value2[, ...]],condition) |
||
1200 | * |
||
1201 | * @category Mathematical and Trigonometric Functions |
||
1202 | * |
||
1203 | * @param mixed $aArgs Data values |
||
1204 | * @param string $condition the criteria that defines which cells will be summed |
||
1205 | * @param mixed $sumArgs |
||
1206 | * |
||
1207 | * @return float |
||
1208 | */ |
||
1209 | 10 | public static function SUMIF($aArgs, $condition, $sumArgs = []) |
|
1210 | { |
||
1211 | 10 | $returnValue = 0; |
|
1212 | |||
1213 | 10 | $aArgs = Functions::flattenArray($aArgs); |
|
1214 | 10 | $sumArgs = Functions::flattenArray($sumArgs); |
|
1215 | 10 | if (empty($sumArgs)) { |
|
1216 | 1 | $sumArgs = $aArgs; |
|
1217 | } |
||
1218 | 10 | $condition = Functions::ifCondition($condition); |
|
1219 | // Loop through arguments |
||
1220 | 10 | foreach ($aArgs as $key => $arg) { |
|
1221 | 10 | if (!is_numeric($arg)) { |
|
1222 | 6 | $arg = str_replace('"', '""', $arg); |
|
1223 | 6 | $arg = Calculation::wrapResult(strtoupper($arg)); |
|
1224 | } |
||
1225 | |||
1226 | 10 | $testCondition = '=' . $arg . $condition; |
|
1227 | 10 | $sumValue = array_key_exists($key, $sumArgs) ? $sumArgs[$key] : 0; |
|
1228 | |||
1229 | 10 | if (is_numeric($sumValue) && |
|
1230 | 10 | Calculation::getInstance()->_calculateFormulaValue($testCondition)) { |
|
1231 | // Is it a value within our criteria and only numeric can be added to the result |
||
1232 | 10 | $returnValue += $sumValue; |
|
1233 | } |
||
1234 | } |
||
1235 | |||
1236 | 10 | return $returnValue; |
|
1237 | } |
||
1238 | |||
1239 | /** |
||
1240 | * SUMIFS. |
||
1241 | * |
||
1242 | * Counts the number of cells that contain numbers within the list of arguments |
||
1243 | * |
||
1244 | * Excel Function: |
||
1245 | * SUMIFS(value1[,value2[, ...]],condition) |
||
1246 | * |
||
1247 | * @category Mathematical and Trigonometric Functions |
||
1248 | * |
||
1249 | * @param mixed $args Data values |
||
1250 | * @param string $condition the criteria that defines which cells will be summed |
||
1251 | * |
||
1252 | * @return float |
||
1253 | */ |
||
1254 | 2 | public static function SUMIFS(...$args) |
|
1255 | { |
||
1256 | 2 | $arrayList = $args; |
|
1257 | |||
1258 | // Return value |
||
1259 | 2 | $returnValue = 0; |
|
1260 | |||
1261 | 2 | $sumArgs = Functions::flattenArray(array_shift($arrayList)); |
|
1262 | 2 | $aArgsArray = []; |
|
1263 | 2 | $conditions = []; |
|
1264 | |||
1265 | 2 | while (count($arrayList) > 0) { |
|
1266 | 2 | $aArgsArray[] = Functions::flattenArray(array_shift($arrayList)); |
|
1267 | 2 | $conditions[] = Functions::ifCondition(array_shift($arrayList)); |
|
1268 | } |
||
1269 | |||
1270 | // Loop through each sum and see if arguments and conditions are true |
||
1271 | 2 | foreach ($sumArgs as $index => $value) { |
|
1272 | 2 | $valid = true; |
|
1273 | |||
1274 | 2 | foreach ($conditions as $cidx => $condition) { |
|
1275 | 2 | $arg = $aArgsArray[$cidx][$index]; |
|
1276 | |||
1277 | // Loop through arguments |
||
1278 | 2 | if (!is_numeric($arg)) { |
|
1279 | 2 | $arg = Calculation::wrapResult(strtoupper($arg)); |
|
1280 | } |
||
1281 | 2 | $testCondition = '=' . $arg . $condition; |
|
1282 | 2 | if (!Calculation::getInstance()->_calculateFormulaValue($testCondition)) { |
|
1283 | // Is not a value within our criteria |
||
1284 | 2 | $valid = false; |
|
1285 | |||
1286 | 2 | break; // if false found, don't need to check other conditions |
|
1287 | } |
||
1288 | } |
||
1289 | |||
1290 | 2 | if ($valid) { |
|
1291 | 2 | $returnValue += $value; |
|
1292 | } |
||
1293 | } |
||
1294 | |||
1295 | // Return |
||
1296 | 2 | return $returnValue; |
|
1297 | } |
||
1298 | |||
1299 | /** |
||
1300 | * SUMPRODUCT. |
||
1301 | * |
||
1302 | * Excel Function: |
||
1303 | * SUMPRODUCT(value1[,value2[, ...]]) |
||
1304 | * |
||
1305 | * @category Mathematical and Trigonometric Functions |
||
1306 | * |
||
1307 | * @param mixed ...$args Data values |
||
1308 | * |
||
1309 | * @return float |
||
1310 | */ |
||
1311 | 3 | public static function SUMPRODUCT(...$args) |
|
1312 | { |
||
1313 | 3 | $arrayList = $args; |
|
1314 | |||
1315 | 3 | $wrkArray = Functions::flattenArray(array_shift($arrayList)); |
|
1316 | 3 | $wrkCellCount = count($wrkArray); |
|
1317 | |||
1318 | 3 | for ($i = 0; $i < $wrkCellCount; ++$i) { |
|
1319 | 3 | if ((!is_numeric($wrkArray[$i])) || (is_string($wrkArray[$i]))) { |
|
1320 | $wrkArray[$i] = 0; |
||
1321 | } |
||
1322 | } |
||
1323 | |||
1324 | 3 | foreach ($arrayList as $matrixData) { |
|
1325 | 3 | $array2 = Functions::flattenArray($matrixData); |
|
1326 | 3 | $count = count($array2); |
|
1327 | 3 | if ($wrkCellCount != $count) { |
|
1328 | return Functions::VALUE(); |
||
1329 | } |
||
1330 | |||
1331 | 3 | foreach ($array2 as $i => $val) { |
|
1332 | 3 | if ((!is_numeric($val)) || (is_string($val))) { |
|
1333 | $val = 0; |
||
1334 | } |
||
1335 | 3 | $wrkArray[$i] *= $val; |
|
1336 | } |
||
1337 | } |
||
1338 | |||
1339 | 3 | return array_sum($wrkArray); |
|
1340 | } |
||
1341 | |||
1342 | /** |
||
1343 | * SUMSQ. |
||
1344 | * |
||
1345 | * SUMSQ returns the sum of the squares of the arguments |
||
1346 | * |
||
1347 | * Excel Function: |
||
1348 | * SUMSQ(value1[,value2[, ...]]) |
||
1349 | * |
||
1350 | * @category Mathematical and Trigonometric Functions |
||
1351 | * |
||
1352 | * @param mixed ...$args Data values |
||
1353 | * |
||
1354 | * @return float |
||
1355 | */ |
||
1356 | 7 | public static function SUMSQ(...$args) |
|
1357 | { |
||
1358 | 7 | $returnValue = 0; |
|
1359 | |||
1360 | // Loop through arguments |
||
1361 | 7 | foreach (Functions::flattenArray($args) as $arg) { |
|
1362 | // Is it a numeric value? |
||
1363 | 7 | if ((is_numeric($arg)) && (!is_string($arg))) { |
|
1364 | 7 | $returnValue += ($arg * $arg); |
|
1365 | } |
||
1366 | } |
||
1367 | |||
1368 | 7 | return $returnValue; |
|
1369 | } |
||
1370 | |||
1371 | /** |
||
1372 | * SUMX2MY2. |
||
1373 | * |
||
1374 | * @param mixed[] $matrixData1 Matrix #1 |
||
1375 | * @param mixed[] $matrixData2 Matrix #2 |
||
1376 | * |
||
1377 | * @return float |
||
1378 | */ |
||
1379 | 3 | public static function SUMX2MY2($matrixData1, $matrixData2) |
|
1380 | { |
||
1381 | 3 | $array1 = Functions::flattenArray($matrixData1); |
|
1382 | 3 | $array2 = Functions::flattenArray($matrixData2); |
|
1383 | 3 | $count = min(count($array1), count($array2)); |
|
1384 | |||
1385 | 3 | $result = 0; |
|
1386 | 3 | for ($i = 0; $i < $count; ++$i) { |
|
1387 | 3 | if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && |
|
1388 | 3 | ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { |
|
1389 | 3 | $result += ($array1[$i] * $array1[$i]) - ($array2[$i] * $array2[$i]); |
|
1390 | } |
||
1391 | } |
||
1392 | |||
1393 | 3 | return $result; |
|
1394 | } |
||
1395 | |||
1396 | /** |
||
1397 | * SUMX2PY2. |
||
1398 | * |
||
1399 | * @param mixed[] $matrixData1 Matrix #1 |
||
1400 | * @param mixed[] $matrixData2 Matrix #2 |
||
1401 | * |
||
1402 | * @return float |
||
1403 | */ |
||
1404 | 3 | public static function SUMX2PY2($matrixData1, $matrixData2) |
|
1405 | { |
||
1406 | 3 | $array1 = Functions::flattenArray($matrixData1); |
|
1407 | 3 | $array2 = Functions::flattenArray($matrixData2); |
|
1408 | 3 | $count = min(count($array1), count($array2)); |
|
1409 | |||
1410 | 3 | $result = 0; |
|
1411 | 3 | for ($i = 0; $i < $count; ++$i) { |
|
1412 | 3 | if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && |
|
1413 | 3 | ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { |
|
1414 | 3 | $result += ($array1[$i] * $array1[$i]) + ($array2[$i] * $array2[$i]); |
|
1415 | } |
||
1416 | } |
||
1417 | |||
1418 | 3 | return $result; |
|
1419 | } |
||
1420 | |||
1421 | /** |
||
1422 | * SUMXMY2. |
||
1423 | * |
||
1424 | * @param mixed[] $matrixData1 Matrix #1 |
||
1425 | * @param mixed[] $matrixData2 Matrix #2 |
||
1426 | * |
||
1427 | * @return float |
||
1428 | */ |
||
1429 | 3 | public static function SUMXMY2($matrixData1, $matrixData2) |
|
1430 | { |
||
1431 | 3 | $array1 = Functions::flattenArray($matrixData1); |
|
1432 | 3 | $array2 = Functions::flattenArray($matrixData2); |
|
1433 | 3 | $count = min(count($array1), count($array2)); |
|
1434 | |||
1435 | 3 | $result = 0; |
|
1436 | 3 | for ($i = 0; $i < $count; ++$i) { |
|
1437 | 3 | if (((is_numeric($array1[$i])) && (!is_string($array1[$i]))) && |
|
1438 | 3 | ((is_numeric($array2[$i])) && (!is_string($array2[$i])))) { |
|
1439 | 3 | $result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]); |
|
1440 | } |
||
1441 | } |
||
1442 | |||
1443 | 3 | return $result; |
|
1444 | } |
||
1445 | |||
1446 | /** |
||
1447 | * TRUNC. |
||
1448 | * |
||
1449 | * Truncates value to the number of fractional digits by number_digits. |
||
1450 | * |
||
1451 | * @param float $value |
||
1452 | * @param int $digits |
||
1453 | * |
||
1454 | * @return float Truncated value |
||
1455 | */ |
||
1456 | 19 | public static function TRUNC($value = 0, $digits = 0) |
|
1457 | { |
||
1458 | 19 | $value = Functions::flattenSingleValue($value); |
|
1459 | 19 | $digits = Functions::flattenSingleValue($digits); |
|
1460 | |||
1461 | // Validate parameters |
||
1462 | 19 | if ((!is_numeric($value)) || (!is_numeric($digits))) { |
|
1463 | 2 | return Functions::VALUE(); |
|
1464 | } |
||
1465 | 17 | $digits = floor($digits); |
|
1466 | |||
1467 | // Truncate |
||
1468 | 17 | $adjust = pow(10, $digits); |
|
1469 | |||
1470 | 17 | if (($digits > 0) && (rtrim((int) ((abs($value) - abs((int) $value)) * $adjust), '0') < $adjust / 10)) { |
|
1471 | 2 | return $value; |
|
1472 | } |
||
1473 | |||
1474 | 15 | return ((int) ($value * $adjust)) / $adjust; |
|
1475 | } |
||
1476 | |||
1477 | /** |
||
1478 | * SEC. |
||
1479 | * |
||
1480 | * Returns the secant of an angle. |
||
1481 | * |
||
1482 | * @param float $angle Number |
||
1483 | * |
||
1484 | * @return float|string The secant of the angle |
||
1485 | */ |
||
1486 | 14 | public static function SEC($angle) |
|
1487 | { |
||
1488 | 14 | $angle = Functions::flattenSingleValue($angle); |
|
1489 | |||
1490 | 14 | if (!is_numeric($angle)) { |
|
1491 | 1 | return Functions::VALUE(); |
|
1492 | } |
||
1493 | |||
1494 | 13 | $result = cos($angle); |
|
1495 | |||
1496 | 13 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1497 | } |
||
1498 | |||
1499 | /** |
||
1500 | * SECH. |
||
1501 | * |
||
1502 | * Returns the hyperbolic secant of an angle. |
||
1503 | * |
||
1504 | * @param float $angle Number |
||
1505 | * |
||
1506 | * @return float|string The hyperbolic secant of the angle |
||
1507 | */ |
||
1508 | 14 | public static function SECH($angle) |
|
1509 | { |
||
1510 | 14 | $angle = Functions::flattenSingleValue($angle); |
|
1511 | |||
1512 | 14 | if (!is_numeric($angle)) { |
|
1513 | 1 | return Functions::VALUE(); |
|
1514 | } |
||
1515 | |||
1516 | 13 | $result = cosh($angle); |
|
1517 | |||
1518 | 13 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1519 | } |
||
1520 | |||
1521 | /** |
||
1522 | * CSC. |
||
1523 | * |
||
1524 | * Returns the cosecant of an angle. |
||
1525 | * |
||
1526 | * @param float $angle Number |
||
1527 | * |
||
1528 | * @return float|string The cosecant of the angle |
||
1529 | */ |
||
1530 | 10 | public static function CSC($angle) |
|
1531 | { |
||
1532 | 10 | $angle = Functions::flattenSingleValue($angle); |
|
1533 | |||
1534 | 10 | if (!is_numeric($angle)) { |
|
1535 | 1 | return Functions::VALUE(); |
|
1536 | } |
||
1537 | |||
1538 | 9 | $result = sin($angle); |
|
1539 | |||
1540 | 9 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1541 | } |
||
1542 | |||
1543 | /** |
||
1544 | * CSCH. |
||
1545 | * |
||
1546 | * Returns the hyperbolic cosecant of an angle. |
||
1547 | * |
||
1548 | * @param float $angle Number |
||
1549 | * |
||
1550 | * @return float|string The hyperbolic cosecant of the angle |
||
1551 | */ |
||
1552 | 14 | public static function CSCH($angle) |
|
1553 | { |
||
1554 | 14 | $angle = Functions::flattenSingleValue($angle); |
|
1555 | |||
1556 | 14 | if (!is_numeric($angle)) { |
|
1557 | 1 | return Functions::VALUE(); |
|
1558 | } |
||
1559 | |||
1560 | 13 | $result = sinh($angle); |
|
1561 | |||
1562 | 13 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1563 | } |
||
1564 | |||
1565 | /** |
||
1566 | * COT. |
||
1567 | * |
||
1568 | * Returns the cotangent of an angle. |
||
1569 | * |
||
1570 | * @param float $angle Number |
||
1571 | * |
||
1572 | * @return float|string The cotangent of the angle |
||
1573 | */ |
||
1574 | 10 | public static function COT($angle) |
|
1575 | { |
||
1576 | 10 | $angle = Functions::flattenSingleValue($angle); |
|
1577 | |||
1578 | 10 | if (!is_numeric($angle)) { |
|
1579 | 1 | return Functions::VALUE(); |
|
1580 | } |
||
1581 | |||
1582 | 9 | $result = tan($angle); |
|
1583 | |||
1584 | 9 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1585 | } |
||
1586 | |||
1587 | /** |
||
1588 | * COTH. |
||
1589 | * |
||
1590 | * Returns the hyperbolic cotangent of an angle. |
||
1591 | * |
||
1592 | * @param float $angle Number |
||
1593 | * |
||
1594 | * @return float|string The hyperbolic cotangent of the angle |
||
1595 | */ |
||
1596 | 14 | public static function COTH($angle) |
|
1597 | { |
||
1598 | 14 | $angle = Functions::flattenSingleValue($angle); |
|
1599 | |||
1600 | 14 | if (!is_numeric($angle)) { |
|
1601 | 1 | return Functions::VALUE(); |
|
1602 | } |
||
1603 | |||
1604 | 13 | $result = tanh($angle); |
|
1605 | |||
1606 | 13 | return ($result == 0.0) ? Functions::DIV0() : 1 / $result; |
|
1607 | } |
||
1608 | |||
1609 | /** |
||
1610 | * ACOT. |
||
1611 | * |
||
1612 | * Returns the arccotangent of a number. |
||
1613 | * |
||
1614 | * @param float $number Number |
||
1615 | * |
||
1616 | * @return float|string The arccotangent of the number |
||
1617 | */ |
||
1618 | 14 | public static function ACOT($number) |
|
1619 | { |
||
1620 | 14 | $number = Functions::flattenSingleValue($number); |
|
1621 | |||
1622 | 14 | if (!is_numeric($number)) { |
|
1623 | 1 | return Functions::VALUE(); |
|
1624 | } |
||
1625 | |||
1626 | 13 | return (M_PI / 2) - atan($number); |
|
1627 | } |
||
1628 | |||
1629 | /** |
||
1630 | * ACOTH. |
||
1631 | * |
||
1632 | * Returns the hyperbolic arccotangent of a number. |
||
1633 | * |
||
1634 | * @param float $number Number |
||
1635 | * |
||
1636 | * @return float|string The hyperbolic arccotangent of the number |
||
1637 | */ |
||
1638 | 14 | public static function ACOTH($number) |
|
1639 | { |
||
1640 | 14 | $number = Functions::flattenSingleValue($number); |
|
1641 | |||
1642 | 14 | if (!is_numeric($number)) { |
|
1643 | 1 | return Functions::VALUE(); |
|
1644 | } |
||
1645 | |||
1646 | 13 | $result = log(($number + 1) / ($number - 1)) / 2; |
|
1647 | |||
1648 | 13 | return is_nan($result) ? Functions::NAN() : $result; |
|
1649 | } |
||
1650 | } |
||
1651 |