1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5use Complex\Complex; 6use Complex\Exception as ComplexException; 7use PhpOffice\PhpSpreadsheet\Calculation\Engineering\ConvertUOM; 8 9class Engineering 10{ 11 /** 12 * EULER. 13 */ 14 const EULER = 2.71828182845904523536; 15 16 /** 17 * parseComplex. 18 * 19 * Parses a complex number into its real and imaginary parts, and an I or J suffix 20 * 21 * @deprecated 2.0.0 No longer used by internal code. Please use the Complex\Complex class instead 22 * 23 * @param string $complexNumber The complex number 24 * 25 * @return mixed[] Indexed on "real", "imaginary" and "suffix" 26 */ 27 public static function parseComplex($complexNumber) 28 { 29 $complex = new Complex($complexNumber); 30 31 return [ 32 'real' => $complex->getReal(), 33 'imaginary' => $complex->getImaginary(), 34 'suffix' => $complex->getSuffix(), 35 ]; 36 } 37 38 /** 39 * Formats a number base string value with leading zeroes. 40 * 41 * @param string $xVal The "number" to pad 42 * @param int $places The length that we want to pad this value 43 * 44 * @return string The padded "number" 45 */ 46 private static function nbrConversionFormat($xVal, $places) 47 { 48 if ($places !== null) { 49 if (is_numeric($places)) { 50 $places = (int) $places; 51 } else { 52 return Functions::VALUE(); 53 } 54 if ($places < 0) { 55 return Functions::NAN(); 56 } 57 if (strlen($xVal) <= $places) { 58 return substr(str_pad($xVal, $places, '0', STR_PAD_LEFT), -10); 59 } 60 61 return Functions::NAN(); 62 } 63 64 return substr($xVal, -10); 65 } 66 67 /** 68 * BESSELI. 69 * 70 * Returns the modified Bessel function In(x), which is equivalent to the Bessel function evaluated 71 * for purely imaginary arguments 72 * 73 * Excel Function: 74 * BESSELI(x,ord) 75 * 76 * @param float $x The value at which to evaluate the function. 77 * If x is nonnumeric, BESSELI returns the #VALUE! error value. 78 * @param int $ord The order of the Bessel function. 79 * If ord is not an integer, it is truncated. 80 * If $ord is nonnumeric, BESSELI returns the #VALUE! error value. 81 * If $ord < 0, BESSELI returns the #NUM! error value. 82 * 83 * @return float|string Result, or a string containing an error 84 */ 85 public static function BESSELI($x, $ord) 86 { 87 $x = ($x === null) ? 0.0 : Functions::flattenSingleValue($x); 88 $ord = ($ord === null) ? 0.0 : Functions::flattenSingleValue($ord); 89 90 if ((is_numeric($x)) && (is_numeric($ord))) { 91 $ord = floor($ord); 92 if ($ord < 0) { 93 return Functions::NAN(); 94 } 95 96 if (abs($x) <= 30) { 97 $fResult = $fTerm = ($x / 2) ** $ord / MathTrig::FACT($ord); 98 $ordK = 1; 99 $fSqrX = ($x * $x) / 4; 100 do { 101 $fTerm *= $fSqrX; 102 $fTerm /= ($ordK * ($ordK + $ord)); 103 $fResult += $fTerm; 104 } while ((abs($fTerm) > 1e-12) && (++$ordK < 100)); 105 } else { 106 $f_2_PI = 2 * M_PI; 107 108 $fXAbs = abs($x); 109 $fResult = exp($fXAbs) / sqrt($f_2_PI * $fXAbs); 110 if (($ord & 1) && ($x < 0)) { 111 $fResult = -$fResult; 112 } 113 } 114 115 return (is_nan($fResult)) ? Functions::NAN() : $fResult; 116 } 117 118 return Functions::VALUE(); 119 } 120 121 /** 122 * BESSELJ. 123 * 124 * Returns the Bessel function 125 * 126 * Excel Function: 127 * BESSELJ(x,ord) 128 * 129 * @param float $x The value at which to evaluate the function. 130 * If x is nonnumeric, BESSELJ returns the #VALUE! error value. 131 * @param int $ord The order of the Bessel function. If n is not an integer, it is truncated. 132 * If $ord is nonnumeric, BESSELJ returns the #VALUE! error value. 133 * If $ord < 0, BESSELJ returns the #NUM! error value. 134 * 135 * @return float|string Result, or a string containing an error 136 */ 137 public static function BESSELJ($x, $ord) 138 { 139 $x = ($x === null) ? 0.0 : Functions::flattenSingleValue($x); 140 $ord = ($ord === null) ? 0.0 : Functions::flattenSingleValue($ord); 141 142 if ((is_numeric($x)) && (is_numeric($ord))) { 143 $ord = floor($ord); 144 if ($ord < 0) { 145 return Functions::NAN(); 146 } 147 148 $fResult = 0; 149 if (abs($x) <= 30) { 150 $fResult = $fTerm = ($x / 2) ** $ord / MathTrig::FACT($ord); 151 $ordK = 1; 152 $fSqrX = ($x * $x) / -4; 153 do { 154 $fTerm *= $fSqrX; 155 $fTerm /= ($ordK * ($ordK + $ord)); 156 $fResult += $fTerm; 157 } while ((abs($fTerm) > 1e-12) && (++$ordK < 100)); 158 } else { 159 $f_PI_DIV_2 = M_PI / 2; 160 $f_PI_DIV_4 = M_PI / 4; 161 162 $fXAbs = abs($x); 163 $fResult = sqrt(Functions::M_2DIVPI / $fXAbs) * cos($fXAbs - $ord * $f_PI_DIV_2 - $f_PI_DIV_4); 164 if (($ord & 1) && ($x < 0)) { 165 $fResult = -$fResult; 166 } 167 } 168 169 return (is_nan($fResult)) ? Functions::NAN() : $fResult; 170 } 171 172 return Functions::VALUE(); 173 } 174 175 private static function besselK0($fNum) 176 { 177 if ($fNum <= 2) { 178 $fNum2 = $fNum * 0.5; 179 $y = ($fNum2 * $fNum2); 180 $fRet = -log($fNum2) * self::BESSELI($fNum, 0) + 181 (-0.57721566 + $y * (0.42278420 + $y * (0.23069756 + $y * (0.3488590e-1 + $y * (0.262698e-2 + $y * 182 (0.10750e-3 + $y * 0.74e-5)))))); 183 } else { 184 $y = 2 / $fNum; 185 $fRet = exp(-$fNum) / sqrt($fNum) * 186 (1.25331414 + $y * (-0.7832358e-1 + $y * (0.2189568e-1 + $y * (-0.1062446e-1 + $y * 187 (0.587872e-2 + $y * (-0.251540e-2 + $y * 0.53208e-3)))))); 188 } 189 190 return $fRet; 191 } 192 193 private static function besselK1($fNum) 194 { 195 if ($fNum <= 2) { 196 $fNum2 = $fNum * 0.5; 197 $y = ($fNum2 * $fNum2); 198 $fRet = log($fNum2) * self::BESSELI($fNum, 1) + 199 (1 + $y * (0.15443144 + $y * (-0.67278579 + $y * (-0.18156897 + $y * (-0.1919402e-1 + $y * 200 (-0.110404e-2 + $y * (-0.4686e-4))))))) / $fNum; 201 } else { 202 $y = 2 / $fNum; 203 $fRet = exp(-$fNum) / sqrt($fNum) * 204 (1.25331414 + $y * (0.23498619 + $y * (-0.3655620e-1 + $y * (0.1504268e-1 + $y * (-0.780353e-2 + $y * 205 (0.325614e-2 + $y * (-0.68245e-3))))))); 206 } 207 208 return $fRet; 209 } 210 211 /** 212 * BESSELK. 213 * 214 * Returns the modified Bessel function Kn(x), which is equivalent to the Bessel functions evaluated 215 * for purely imaginary arguments. 216 * 217 * Excel Function: 218 * BESSELK(x,ord) 219 * 220 * @param float $x The value at which to evaluate the function. 221 * If x is nonnumeric, BESSELK returns the #VALUE! error value. 222 * @param int $ord The order of the Bessel function. If n is not an integer, it is truncated. 223 * If $ord is nonnumeric, BESSELK returns the #VALUE! error value. 224 * If $ord < 0, BESSELK returns the #NUM! error value. 225 * 226 * @return float|string Result, or a string containing an error 227 */ 228 public static function BESSELK($x, $ord) 229 { 230 $x = ($x === null) ? 0.0 : Functions::flattenSingleValue($x); 231 $ord = ($ord === null) ? 0.0 : Functions::flattenSingleValue($ord); 232 233 if ((is_numeric($x)) && (is_numeric($ord))) { 234 if (($ord < 0) || ($x == 0.0)) { 235 return Functions::NAN(); 236 } 237 238 switch (floor($ord)) { 239 case 0: 240 $fBk = self::besselK0($x); 241 242 break; 243 case 1: 244 $fBk = self::besselK1($x); 245 246 break; 247 default: 248 $fTox = 2 / $x; 249 $fBkm = self::besselK0($x); 250 $fBk = self::besselK1($x); 251 for ($n = 1; $n < $ord; ++$n) { 252 $fBkp = $fBkm + $n * $fTox * $fBk; 253 $fBkm = $fBk; 254 $fBk = $fBkp; 255 } 256 } 257 258 return (is_nan($fBk)) ? Functions::NAN() : $fBk; 259 } 260 261 return Functions::VALUE(); 262 } 263 264 private static function besselY0($fNum) 265 { 266 if ($fNum < 8.0) { 267 $y = ($fNum * $fNum); 268 $f1 = -2957821389.0 + $y * (7062834065.0 + $y * (-512359803.6 + $y * (10879881.29 + $y * (-86327.92757 + $y * 228.4622733)))); 269 $f2 = 40076544269.0 + $y * (745249964.8 + $y * (7189466.438 + $y * (47447.26470 + $y * (226.1030244 + $y)))); 270 $fRet = $f1 / $f2 + 0.636619772 * self::BESSELJ($fNum, 0) * log($fNum); 271 } else { 272 $z = 8.0 / $fNum; 273 $y = ($z * $z); 274 $xx = $fNum - 0.785398164; 275 $f1 = 1 + $y * (-0.1098628627e-2 + $y * (0.2734510407e-4 + $y * (-0.2073370639e-5 + $y * 0.2093887211e-6))); 276 $f2 = -0.1562499995e-1 + $y * (0.1430488765e-3 + $y * (-0.6911147651e-5 + $y * (0.7621095161e-6 + $y * (-0.934945152e-7)))); 277 $fRet = sqrt(0.636619772 / $fNum) * (sin($xx) * $f1 + $z * cos($xx) * $f2); 278 } 279 280 return $fRet; 281 } 282 283 private static function besselY1($fNum) 284 { 285 if ($fNum < 8.0) { 286 $y = ($fNum * $fNum); 287 $f1 = $fNum * (-0.4900604943e13 + $y * (0.1275274390e13 + $y * (-0.5153438139e11 + $y * (0.7349264551e9 + $y * 288 (-0.4237922726e7 + $y * 0.8511937935e4))))); 289 $f2 = 0.2499580570e14 + $y * (0.4244419664e12 + $y * (0.3733650367e10 + $y * (0.2245904002e8 + $y * 290 (0.1020426050e6 + $y * (0.3549632885e3 + $y))))); 291 $fRet = $f1 / $f2 + 0.636619772 * (self::BESSELJ($fNum, 1) * log($fNum) - 1 / $fNum); 292 } else { 293 $fRet = sqrt(0.636619772 / $fNum) * sin($fNum - 2.356194491); 294 } 295 296 return $fRet; 297 } 298 299 /** 300 * BESSELY. 301 * 302 * Returns the Bessel function, which is also called the Weber function or the Neumann function. 303 * 304 * Excel Function: 305 * BESSELY(x,ord) 306 * 307 * @param float $x The value at which to evaluate the function. 308 * If x is nonnumeric, BESSELK returns the #VALUE! error value. 309 * @param int $ord The order of the Bessel function. If n is not an integer, it is truncated. 310 * If $ord is nonnumeric, BESSELK returns the #VALUE! error value. 311 * If $ord < 0, BESSELK returns the #NUM! error value. 312 * 313 * @return float|string Result, or a string containing an error 314 */ 315 public static function BESSELY($x, $ord) 316 { 317 $x = ($x === null) ? 0.0 : Functions::flattenSingleValue($x); 318 $ord = ($ord === null) ? 0.0 : Functions::flattenSingleValue($ord); 319 320 if ((is_numeric($x)) && (is_numeric($ord))) { 321 if (($ord < 0) || ($x == 0.0)) { 322 return Functions::NAN(); 323 } 324 325 switch (floor($ord)) { 326 case 0: 327 $fBy = self::besselY0($x); 328 329 break; 330 case 1: 331 $fBy = self::besselY1($x); 332 333 break; 334 default: 335 $fTox = 2 / $x; 336 $fBym = self::besselY0($x); 337 $fBy = self::besselY1($x); 338 for ($n = 1; $n < $ord; ++$n) { 339 $fByp = $n * $fTox * $fBy - $fBym; 340 $fBym = $fBy; 341 $fBy = $fByp; 342 } 343 } 344 345 return (is_nan($fBy)) ? Functions::NAN() : $fBy; 346 } 347 348 return Functions::VALUE(); 349 } 350 351 /** 352 * BINTODEC. 353 * 354 * Return a binary value as decimal. 355 * 356 * Excel Function: 357 * BIN2DEC(x) 358 * 359 * @param string $x The binary number (as a string) that you want to convert. The number 360 * cannot contain more than 10 characters (10 bits). The most significant 361 * bit of number is the sign bit. The remaining 9 bits are magnitude bits. 362 * Negative numbers are represented using two's-complement notation. 363 * If number is not a valid binary number, or if number contains more than 364 * 10 characters (10 bits), BIN2DEC returns the #NUM! error value. 365 * 366 * @return string 367 */ 368 public static function BINTODEC($x) 369 { 370 $x = Functions::flattenSingleValue($x); 371 372 if (is_bool($x)) { 373 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 374 $x = (int) $x; 375 } else { 376 return Functions::VALUE(); 377 } 378 } 379 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 380 $x = floor($x); 381 } 382 $x = (string) $x; 383 if (strlen($x) > preg_match_all('/[01]/', $x, $out)) { 384 return Functions::NAN(); 385 } 386 if (strlen($x) > 10) { 387 return Functions::NAN(); 388 } elseif (strlen($x) == 10) { 389 // Two's Complement 390 $x = substr($x, -9); 391 392 return '-' . (512 - bindec($x)); 393 } 394 395 return bindec($x); 396 } 397 398 /** 399 * BINTOHEX. 400 * 401 * Return a binary value as hex. 402 * 403 * Excel Function: 404 * BIN2HEX(x[,places]) 405 * 406 * @param string $x The binary number (as a string) that you want to convert. The number 407 * cannot contain more than 10 characters (10 bits). The most significant 408 * bit of number is the sign bit. The remaining 9 bits are magnitude bits. 409 * Negative numbers are represented using two's-complement notation. 410 * If number is not a valid binary number, or if number contains more than 411 * 10 characters (10 bits), BIN2HEX returns the #NUM! error value. 412 * @param int $places The number of characters to use. If places is omitted, BIN2HEX uses the 413 * minimum number of characters necessary. Places is useful for padding the 414 * return value with leading 0s (zeros). 415 * If places is not an integer, it is truncated. 416 * If places is nonnumeric, BIN2HEX returns the #VALUE! error value. 417 * If places is negative, BIN2HEX returns the #NUM! error value. 418 * 419 * @return string 420 */ 421 public static function BINTOHEX($x, $places = null) 422 { 423 $x = Functions::flattenSingleValue($x); 424 $places = Functions::flattenSingleValue($places); 425 426 // Argument X 427 if (is_bool($x)) { 428 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 429 $x = (int) $x; 430 } else { 431 return Functions::VALUE(); 432 } 433 } 434 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 435 $x = floor($x); 436 } 437 $x = (string) $x; 438 if (strlen($x) > preg_match_all('/[01]/', $x, $out)) { 439 return Functions::NAN(); 440 } 441 if (strlen($x) > 10) { 442 return Functions::NAN(); 443 } elseif (strlen($x) == 10) { 444 // Two's Complement 445 return str_repeat('F', 8) . substr(strtoupper(dechex(bindec(substr($x, -9)))), -2); 446 } 447 $hexVal = (string) strtoupper(dechex(bindec($x))); 448 449 return self::nbrConversionFormat($hexVal, $places); 450 } 451 452 /** 453 * BINTOOCT. 454 * 455 * Return a binary value as octal. 456 * 457 * Excel Function: 458 * BIN2OCT(x[,places]) 459 * 460 * @param string $x The binary number (as a string) that you want to convert. The number 461 * cannot contain more than 10 characters (10 bits). The most significant 462 * bit of number is the sign bit. The remaining 9 bits are magnitude bits. 463 * Negative numbers are represented using two's-complement notation. 464 * If number is not a valid binary number, or if number contains more than 465 * 10 characters (10 bits), BIN2OCT returns the #NUM! error value. 466 * @param int $places The number of characters to use. If places is omitted, BIN2OCT uses the 467 * minimum number of characters necessary. Places is useful for padding the 468 * return value with leading 0s (zeros). 469 * If places is not an integer, it is truncated. 470 * If places is nonnumeric, BIN2OCT returns the #VALUE! error value. 471 * If places is negative, BIN2OCT returns the #NUM! error value. 472 * 473 * @return string 474 */ 475 public static function BINTOOCT($x, $places = null) 476 { 477 $x = Functions::flattenSingleValue($x); 478 $places = Functions::flattenSingleValue($places); 479 480 if (is_bool($x)) { 481 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 482 $x = (int) $x; 483 } else { 484 return Functions::VALUE(); 485 } 486 } 487 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 488 $x = floor($x); 489 } 490 $x = (string) $x; 491 if (strlen($x) > preg_match_all('/[01]/', $x, $out)) { 492 return Functions::NAN(); 493 } 494 if (strlen($x) > 10) { 495 return Functions::NAN(); 496 } elseif (strlen($x) == 10) { 497 // Two's Complement 498 return str_repeat('7', 7) . substr(strtoupper(decoct(bindec(substr($x, -9)))), -3); 499 } 500 $octVal = (string) decoct(bindec($x)); 501 502 return self::nbrConversionFormat($octVal, $places); 503 } 504 505 /** 506 * DECTOBIN. 507 * 508 * Return a decimal value as binary. 509 * 510 * Excel Function: 511 * DEC2BIN(x[,places]) 512 * 513 * @param string $x The decimal integer you want to convert. If number is negative, 514 * valid place values are ignored and DEC2BIN returns a 10-character 515 * (10-bit) binary number in which the most significant bit is the sign 516 * bit. The remaining 9 bits are magnitude bits. Negative numbers are 517 * represented using two's-complement notation. 518 * If number < -512 or if number > 511, DEC2BIN returns the #NUM! error 519 * value. 520 * If number is nonnumeric, DEC2BIN returns the #VALUE! error value. 521 * If DEC2BIN requires more than places characters, it returns the #NUM! 522 * error value. 523 * @param int $places The number of characters to use. If places is omitted, DEC2BIN uses 524 * the minimum number of characters necessary. Places is useful for 525 * padding the return value with leading 0s (zeros). 526 * If places is not an integer, it is truncated. 527 * If places is nonnumeric, DEC2BIN returns the #VALUE! error value. 528 * If places is zero or negative, DEC2BIN returns the #NUM! error value. 529 * 530 * @return string 531 */ 532 public static function DECTOBIN($x, $places = null) 533 { 534 $x = Functions::flattenSingleValue($x); 535 $places = Functions::flattenSingleValue($places); 536 537 if (is_bool($x)) { 538 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 539 $x = (int) $x; 540 } else { 541 return Functions::VALUE(); 542 } 543 } 544 $x = (string) $x; 545 if (strlen($x) > preg_match_all('/[-0123456789.]/', $x, $out)) { 546 return Functions::VALUE(); 547 } 548 549 $x = (string) floor($x); 550 if ($x < -512 || $x > 511) { 551 return Functions::NAN(); 552 } 553 554 $r = decbin($x); 555 // Two's Complement 556 $r = substr($r, -10); 557 if (strlen($r) >= 11) { 558 return Functions::NAN(); 559 } 560 561 return self::nbrConversionFormat($r, $places); 562 } 563 564 /** 565 * DECTOHEX. 566 * 567 * Return a decimal value as hex. 568 * 569 * Excel Function: 570 * DEC2HEX(x[,places]) 571 * 572 * @param string $x The decimal integer you want to convert. If number is negative, 573 * places is ignored and DEC2HEX returns a 10-character (40-bit) 574 * hexadecimal number in which the most significant bit is the sign 575 * bit. The remaining 39 bits are magnitude bits. Negative numbers 576 * are represented using two's-complement notation. 577 * If number < -549,755,813,888 or if number > 549,755,813,887, 578 * DEC2HEX returns the #NUM! error value. 579 * If number is nonnumeric, DEC2HEX returns the #VALUE! error value. 580 * If DEC2HEX requires more than places characters, it returns the 581 * #NUM! error value. 582 * @param int $places The number of characters to use. If places is omitted, DEC2HEX uses 583 * the minimum number of characters necessary. Places is useful for 584 * padding the return value with leading 0s (zeros). 585 * If places is not an integer, it is truncated. 586 * If places is nonnumeric, DEC2HEX returns the #VALUE! error value. 587 * If places is zero or negative, DEC2HEX returns the #NUM! error value. 588 * 589 * @return string 590 */ 591 public static function DECTOHEX($x, $places = null) 592 { 593 $x = Functions::flattenSingleValue($x); 594 $places = Functions::flattenSingleValue($places); 595 596 if (is_bool($x)) { 597 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 598 $x = (int) $x; 599 } else { 600 return Functions::VALUE(); 601 } 602 } 603 $x = (string) $x; 604 if (strlen($x) > preg_match_all('/[-0123456789.]/', $x, $out)) { 605 return Functions::VALUE(); 606 } 607 $x = (string) floor($x); 608 $r = strtoupper(dechex($x)); 609 if (strlen($r) == 8) { 610 // Two's Complement 611 $r = 'FF' . $r; 612 } 613 614 return self::nbrConversionFormat($r, $places); 615 } 616 617 /** 618 * DECTOOCT. 619 * 620 * Return an decimal value as octal. 621 * 622 * Excel Function: 623 * DEC2OCT(x[,places]) 624 * 625 * @param string $x The decimal integer you want to convert. If number is negative, 626 * places is ignored and DEC2OCT returns a 10-character (30-bit) 627 * octal number in which the most significant bit is the sign bit. 628 * The remaining 29 bits are magnitude bits. Negative numbers are 629 * represented using two's-complement notation. 630 * If number < -536,870,912 or if number > 536,870,911, DEC2OCT 631 * returns the #NUM! error value. 632 * If number is nonnumeric, DEC2OCT returns the #VALUE! error value. 633 * If DEC2OCT requires more than places characters, it returns the 634 * #NUM! error value. 635 * @param int $places The number of characters to use. If places is omitted, DEC2OCT uses 636 * the minimum number of characters necessary. Places is useful for 637 * padding the return value with leading 0s (zeros). 638 * If places is not an integer, it is truncated. 639 * If places is nonnumeric, DEC2OCT returns the #VALUE! error value. 640 * If places is zero or negative, DEC2OCT returns the #NUM! error value. 641 * 642 * @return string 643 */ 644 public static function DECTOOCT($x, $places = null) 645 { 646 $xorig = $x; 647 $x = Functions::flattenSingleValue($x); 648 $places = Functions::flattenSingleValue($places); 649 650 if (is_bool($x)) { 651 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 652 $x = (int) $x; 653 } else { 654 return Functions::VALUE(); 655 } 656 } 657 $x = (string) $x; 658 if (strlen($x) > preg_match_all('/[-0123456789.]/', $x, $out)) { 659 return Functions::VALUE(); 660 } 661 $x = (string) floor($x); 662 $r = decoct($x); 663 if (strlen($r) == 11) { 664 // Two's Complement 665 $r = substr($r, -10); 666 } 667 668 return self::nbrConversionFormat($r, $places); 669 } 670 671 /** 672 * HEXTOBIN. 673 * 674 * Return a hex value as binary. 675 * 676 * Excel Function: 677 * HEX2BIN(x[,places]) 678 * 679 * @param string $x the hexadecimal number you want to convert. 680 * Number cannot contain more than 10 characters. 681 * The most significant bit of number is the sign bit (40th bit from the right). 682 * The remaining 9 bits are magnitude bits. 683 * Negative numbers are represented using two's-complement notation. 684 * If number is negative, HEX2BIN ignores places and returns a 10-character binary number. 685 * If number is negative, it cannot be less than FFFFFFFE00, 686 * and if number is positive, it cannot be greater than 1FF. 687 * If number is not a valid hexadecimal number, HEX2BIN returns the #NUM! error value. 688 * If HEX2BIN requires more than places characters, it returns the #NUM! error value. 689 * @param int $places The number of characters to use. If places is omitted, 690 * HEX2BIN uses the minimum number of characters necessary. Places 691 * is useful for padding the return value with leading 0s (zeros). 692 * If places is not an integer, it is truncated. 693 * If places is nonnumeric, HEX2BIN returns the #VALUE! error value. 694 * If places is negative, HEX2BIN returns the #NUM! error value. 695 * 696 * @return string 697 */ 698 public static function HEXTOBIN($x, $places = null) 699 { 700 $x = Functions::flattenSingleValue($x); 701 $places = Functions::flattenSingleValue($places); 702 703 if (is_bool($x)) { 704 return Functions::VALUE(); 705 } 706 $x = (string) $x; 707 if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/', strtoupper($x), $out)) { 708 return Functions::NAN(); 709 } 710 711 return self::DECTOBIN(self::HEXTODEC($x), $places); 712 } 713 714 /** 715 * HEXTODEC. 716 * 717 * Return a hex value as decimal. 718 * 719 * Excel Function: 720 * HEX2DEC(x) 721 * 722 * @param string $x The hexadecimal number you want to convert. This number cannot 723 * contain more than 10 characters (40 bits). The most significant 724 * bit of number is the sign bit. The remaining 39 bits are magnitude 725 * bits. Negative numbers are represented using two's-complement 726 * notation. 727 * If number is not a valid hexadecimal number, HEX2DEC returns the 728 * #NUM! error value. 729 * 730 * @return string 731 */ 732 public static function HEXTODEC($x) 733 { 734 $x = Functions::flattenSingleValue($x); 735 736 if (is_bool($x)) { 737 return Functions::VALUE(); 738 } 739 $x = (string) $x; 740 if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/', strtoupper($x), $out)) { 741 return Functions::NAN(); 742 } 743 744 if (strlen($x) > 10) { 745 return Functions::NAN(); 746 } 747 748 $binX = ''; 749 foreach (str_split($x) as $char) { 750 $binX .= str_pad(base_convert($char, 16, 2), 4, '0', STR_PAD_LEFT); 751 } 752 if (strlen($binX) == 40 && $binX[0] == '1') { 753 for ($i = 0; $i < 40; ++$i) { 754 $binX[$i] = ($binX[$i] == '1' ? '0' : '1'); 755 } 756 757 return (bindec($binX) + 1) * -1; 758 } 759 760 return bindec($binX); 761 } 762 763 /** 764 * HEXTOOCT. 765 * 766 * Return a hex value as octal. 767 * 768 * Excel Function: 769 * HEX2OCT(x[,places]) 770 * 771 * @param string $x The hexadecimal number you want to convert. Number cannot 772 * contain more than 10 characters. The most significant bit of 773 * number is the sign bit. The remaining 39 bits are magnitude 774 * bits. Negative numbers are represented using two's-complement 775 * notation. 776 * If number is negative, HEX2OCT ignores places and returns a 777 * 10-character octal number. 778 * If number is negative, it cannot be less than FFE0000000, and 779 * if number is positive, it cannot be greater than 1FFFFFFF. 780 * If number is not a valid hexadecimal number, HEX2OCT returns 781 * the #NUM! error value. 782 * If HEX2OCT requires more than places characters, it returns 783 * the #NUM! error value. 784 * @param int $places The number of characters to use. If places is omitted, HEX2OCT 785 * uses the minimum number of characters necessary. Places is 786 * useful for padding the return value with leading 0s (zeros). 787 * If places is not an integer, it is truncated. 788 * If places is nonnumeric, HEX2OCT returns the #VALUE! error 789 * value. 790 * If places is negative, HEX2OCT returns the #NUM! error value. 791 * 792 * @return string 793 */ 794 public static function HEXTOOCT($x, $places = null) 795 { 796 $x = Functions::flattenSingleValue($x); 797 $places = Functions::flattenSingleValue($places); 798 799 if (is_bool($x)) { 800 return Functions::VALUE(); 801 } 802 $x = (string) $x; 803 if (strlen($x) > preg_match_all('/[0123456789ABCDEF]/', strtoupper($x), $out)) { 804 return Functions::NAN(); 805 } 806 807 $decimal = self::HEXTODEC($x); 808 if ($decimal < -536870912 || $decimal > 536870911) { 809 return Functions::NAN(); 810 } 811 812 return self::DECTOOCT($decimal, $places); 813 } 814 815 /** 816 * OCTTOBIN. 817 * 818 * Return an octal value as binary. 819 * 820 * Excel Function: 821 * OCT2BIN(x[,places]) 822 * 823 * @param string $x The octal number you want to convert. Number may not 824 * contain more than 10 characters. The most significant 825 * bit of number is the sign bit. The remaining 29 bits 826 * are magnitude bits. Negative numbers are represented 827 * using two's-complement notation. 828 * If number is negative, OCT2BIN ignores places and returns 829 * a 10-character binary number. 830 * If number is negative, it cannot be less than 7777777000, 831 * and if number is positive, it cannot be greater than 777. 832 * If number is not a valid octal number, OCT2BIN returns 833 * the #NUM! error value. 834 * If OCT2BIN requires more than places characters, it 835 * returns the #NUM! error value. 836 * @param int $places The number of characters to use. If places is omitted, 837 * OCT2BIN uses the minimum number of characters necessary. 838 * Places is useful for padding the return value with 839 * leading 0s (zeros). 840 * If places is not an integer, it is truncated. 841 * If places is nonnumeric, OCT2BIN returns the #VALUE! 842 * error value. 843 * If places is negative, OCT2BIN returns the #NUM! error 844 * value. 845 * 846 * @return string 847 */ 848 public static function OCTTOBIN($x, $places = null) 849 { 850 $x = Functions::flattenSingleValue($x); 851 $places = Functions::flattenSingleValue($places); 852 853 if (is_bool($x)) { 854 return Functions::VALUE(); 855 } 856 $x = (string) $x; 857 if (preg_match_all('/[01234567]/', $x, $out) != strlen($x)) { 858 return Functions::NAN(); 859 } 860 861 return self::DECTOBIN(self::OCTTODEC($x), $places); 862 } 863 864 /** 865 * OCTTODEC. 866 * 867 * Return an octal value as decimal. 868 * 869 * Excel Function: 870 * OCT2DEC(x) 871 * 872 * @param string $x The octal number you want to convert. Number may not contain 873 * more than 10 octal characters (30 bits). The most significant 874 * bit of number is the sign bit. The remaining 29 bits are 875 * magnitude bits. Negative numbers are represented using 876 * two's-complement notation. 877 * If number is not a valid octal number, OCT2DEC returns the 878 * #NUM! error value. 879 * 880 * @return string 881 */ 882 public static function OCTTODEC($x) 883 { 884 $x = Functions::flattenSingleValue($x); 885 886 if (is_bool($x)) { 887 return Functions::VALUE(); 888 } 889 $x = (string) $x; 890 if (preg_match_all('/[01234567]/', $x, $out) != strlen($x)) { 891 return Functions::NAN(); 892 } 893 $binX = ''; 894 foreach (str_split($x) as $char) { 895 $binX .= str_pad(decbin((int) $char), 3, '0', STR_PAD_LEFT); 896 } 897 if (strlen($binX) == 30 && $binX[0] == '1') { 898 for ($i = 0; $i < 30; ++$i) { 899 $binX[$i] = ($binX[$i] == '1' ? '0' : '1'); 900 } 901 902 return (bindec($binX) + 1) * -1; 903 } 904 905 return bindec($binX); 906 } 907 908 /** 909 * OCTTOHEX. 910 * 911 * Return an octal value as hex. 912 * 913 * Excel Function: 914 * OCT2HEX(x[,places]) 915 * 916 * @param string $x The octal number you want to convert. Number may not contain 917 * more than 10 octal characters (30 bits). The most significant 918 * bit of number is the sign bit. The remaining 29 bits are 919 * magnitude bits. Negative numbers are represented using 920 * two's-complement notation. 921 * If number is negative, OCT2HEX ignores places and returns a 922 * 10-character hexadecimal number. 923 * If number is not a valid octal number, OCT2HEX returns the 924 * #NUM! error value. 925 * If OCT2HEX requires more than places characters, it returns 926 * the #NUM! error value. 927 * @param int $places The number of characters to use. If places is omitted, OCT2HEX 928 * uses the minimum number of characters necessary. Places is useful 929 * for padding the return value with leading 0s (zeros). 930 * If places is not an integer, it is truncated. 931 * If places is nonnumeric, OCT2HEX returns the #VALUE! error value. 932 * If places is negative, OCT2HEX returns the #NUM! error value. 933 * 934 * @return string 935 */ 936 public static function OCTTOHEX($x, $places = null) 937 { 938 $x = Functions::flattenSingleValue($x); 939 $places = Functions::flattenSingleValue($places); 940 941 if (is_bool($x)) { 942 return Functions::VALUE(); 943 } 944 $x = (string) $x; 945 if (preg_match_all('/[01234567]/', $x, $out) != strlen($x)) { 946 return Functions::NAN(); 947 } 948 $hexVal = strtoupper(dechex(self::OCTTODEC($x))); 949 950 return self::nbrConversionFormat($hexVal, $places); 951 } 952 953 /** 954 * COMPLEX. 955 * 956 * Converts real and imaginary coefficients into a complex number of the form x +/- yi or x +/- yj. 957 * 958 * Excel Function: 959 * COMPLEX(realNumber,imaginary[,suffix]) 960 * 961 * @param float $realNumber the real coefficient of the complex number 962 * @param float $imaginary the imaginary coefficient of the complex number 963 * @param string $suffix The suffix for the imaginary component of the complex number. 964 * If omitted, the suffix is assumed to be "i". 965 * 966 * @return string 967 */ 968 public static function COMPLEX($realNumber = 0.0, $imaginary = 0.0, $suffix = 'i') 969 { 970 $realNumber = ($realNumber === null) ? 0.0 : Functions::flattenSingleValue($realNumber); 971 $imaginary = ($imaginary === null) ? 0.0 : Functions::flattenSingleValue($imaginary); 972 $suffix = ($suffix === null) ? 'i' : Functions::flattenSingleValue($suffix); 973 974 if ( 975 ((is_numeric($realNumber)) && (is_numeric($imaginary))) && 976 (($suffix == 'i') || ($suffix == 'j') || ($suffix == '')) 977 ) { 978 $complex = new Complex($realNumber, $imaginary, $suffix); 979 980 return (string) $complex; 981 } 982 983 return Functions::VALUE(); 984 } 985 986 /** 987 * IMAGINARY. 988 * 989 * Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. 990 * 991 * Excel Function: 992 * IMAGINARY(complexNumber) 993 * 994 * @param string $complexNumber the complex number for which you want the imaginary 995 * coefficient 996 * 997 * @return float 998 */ 999 public static function IMAGINARY($complexNumber) 1000 { 1001 $complexNumber = Functions::flattenSingleValue($complexNumber); 1002 1003 return (new Complex($complexNumber))->getImaginary(); 1004 } 1005 1006 /** 1007 * IMREAL. 1008 * 1009 * Returns the real coefficient of a complex number in x + yi or x + yj text format. 1010 * 1011 * Excel Function: 1012 * IMREAL(complexNumber) 1013 * 1014 * @param string $complexNumber the complex number for which you want the real coefficient 1015 * 1016 * @return float 1017 */ 1018 public static function IMREAL($complexNumber) 1019 { 1020 $complexNumber = Functions::flattenSingleValue($complexNumber); 1021 1022 return (new Complex($complexNumber))->getReal(); 1023 } 1024 1025 /** 1026 * IMABS. 1027 * 1028 * Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format. 1029 * 1030 * Excel Function: 1031 * IMABS(complexNumber) 1032 * 1033 * @param string $complexNumber the complex number for which you want the absolute value 1034 * 1035 * @return float 1036 */ 1037 public static function IMABS($complexNumber) 1038 { 1039 $complexNumber = Functions::flattenSingleValue($complexNumber); 1040 1041 return (new Complex($complexNumber))->abs(); 1042 } 1043 1044 /** 1045 * IMARGUMENT. 1046 * 1047 * Returns the argument theta of a complex number, i.e. the angle in radians from the real 1048 * axis to the representation of the number in polar coordinates. 1049 * 1050 * Excel Function: 1051 * IMARGUMENT(complexNumber) 1052 * 1053 * @param string $complexNumber the complex number for which you want the argument theta 1054 * 1055 * @return float|string 1056 */ 1057 public static function IMARGUMENT($complexNumber) 1058 { 1059 $complexNumber = Functions::flattenSingleValue($complexNumber); 1060 1061 $complex = new Complex($complexNumber); 1062 if ($complex->getReal() == 0.0 && $complex->getImaginary() == 0.0) { 1063 return Functions::DIV0(); 1064 } 1065 1066 return $complex->argument(); 1067 } 1068 1069 /** 1070 * IMCONJUGATE. 1071 * 1072 * Returns the complex conjugate of a complex number in x + yi or x + yj text format. 1073 * 1074 * Excel Function: 1075 * IMCONJUGATE(complexNumber) 1076 * 1077 * @param string $complexNumber the complex number for which you want the conjugate 1078 * 1079 * @return string 1080 */ 1081 public static function IMCONJUGATE($complexNumber) 1082 { 1083 $complexNumber = Functions::flattenSingleValue($complexNumber); 1084 1085 return (string) (new Complex($complexNumber))->conjugate(); 1086 } 1087 1088 /** 1089 * IMCOS. 1090 * 1091 * Returns the cosine of a complex number in x + yi or x + yj text format. 1092 * 1093 * Excel Function: 1094 * IMCOS(complexNumber) 1095 * 1096 * @param string $complexNumber the complex number for which you want the cosine 1097 * 1098 * @return float|string 1099 */ 1100 public static function IMCOS($complexNumber) 1101 { 1102 $complexNumber = Functions::flattenSingleValue($complexNumber); 1103 1104 return (string) (new Complex($complexNumber))->cos(); 1105 } 1106 1107 /** 1108 * IMCOSH. 1109 * 1110 * Returns the hyperbolic cosine of a complex number in x + yi or x + yj text format. 1111 * 1112 * Excel Function: 1113 * IMCOSH(complexNumber) 1114 * 1115 * @param string $complexNumber the complex number for which you want the hyperbolic cosine 1116 * 1117 * @return float|string 1118 */ 1119 public static function IMCOSH($complexNumber) 1120 { 1121 $complexNumber = Functions::flattenSingleValue($complexNumber); 1122 1123 return (string) (new Complex($complexNumber))->cosh(); 1124 } 1125 1126 /** 1127 * IMCOT. 1128 * 1129 * Returns the cotangent of a complex number in x + yi or x + yj text format. 1130 * 1131 * Excel Function: 1132 * IMCOT(complexNumber) 1133 * 1134 * @param string $complexNumber the complex number for which you want the cotangent 1135 * 1136 * @return float|string 1137 */ 1138 public static function IMCOT($complexNumber) 1139 { 1140 $complexNumber = Functions::flattenSingleValue($complexNumber); 1141 1142 return (string) (new Complex($complexNumber))->cot(); 1143 } 1144 1145 /** 1146 * IMCSC. 1147 * 1148 * Returns the cosecant of a complex number in x + yi or x + yj text format. 1149 * 1150 * Excel Function: 1151 * IMCSC(complexNumber) 1152 * 1153 * @param string $complexNumber the complex number for which you want the cosecant 1154 * 1155 * @return float|string 1156 */ 1157 public static function IMCSC($complexNumber) 1158 { 1159 $complexNumber = Functions::flattenSingleValue($complexNumber); 1160 1161 return (string) (new Complex($complexNumber))->csc(); 1162 } 1163 1164 /** 1165 * IMCSCH. 1166 * 1167 * Returns the hyperbolic cosecant of a complex number in x + yi or x + yj text format. 1168 * 1169 * Excel Function: 1170 * IMCSCH(complexNumber) 1171 * 1172 * @param string $complexNumber the complex number for which you want the hyperbolic cosecant 1173 * 1174 * @return float|string 1175 */ 1176 public static function IMCSCH($complexNumber) 1177 { 1178 $complexNumber = Functions::flattenSingleValue($complexNumber); 1179 1180 return (string) (new Complex($complexNumber))->csch(); 1181 } 1182 1183 /** 1184 * IMSIN. 1185 * 1186 * Returns the sine of a complex number in x + yi or x + yj text format. 1187 * 1188 * Excel Function: 1189 * IMSIN(complexNumber) 1190 * 1191 * @param string $complexNumber the complex number for which you want the sine 1192 * 1193 * @return float|string 1194 */ 1195 public static function IMSIN($complexNumber) 1196 { 1197 $complexNumber = Functions::flattenSingleValue($complexNumber); 1198 1199 return (string) (new Complex($complexNumber))->sin(); 1200 } 1201 1202 /** 1203 * IMSINH. 1204 * 1205 * Returns the hyperbolic sine of a complex number in x + yi or x + yj text format. 1206 * 1207 * Excel Function: 1208 * IMSINH(complexNumber) 1209 * 1210 * @param string $complexNumber the complex number for which you want the hyperbolic sine 1211 * 1212 * @return float|string 1213 */ 1214 public static function IMSINH($complexNumber) 1215 { 1216 $complexNumber = Functions::flattenSingleValue($complexNumber); 1217 1218 return (string) (new Complex($complexNumber))->sinh(); 1219 } 1220 1221 /** 1222 * IMSEC. 1223 * 1224 * Returns the secant of a complex number in x + yi or x + yj text format. 1225 * 1226 * Excel Function: 1227 * IMSEC(complexNumber) 1228 * 1229 * @param string $complexNumber the complex number for which you want the secant 1230 * 1231 * @return float|string 1232 */ 1233 public static function IMSEC($complexNumber) 1234 { 1235 $complexNumber = Functions::flattenSingleValue($complexNumber); 1236 1237 return (string) (new Complex($complexNumber))->sec(); 1238 } 1239 1240 /** 1241 * IMSECH. 1242 * 1243 * Returns the hyperbolic secant of a complex number in x + yi or x + yj text format. 1244 * 1245 * Excel Function: 1246 * IMSECH(complexNumber) 1247 * 1248 * @param string $complexNumber the complex number for which you want the hyperbolic secant 1249 * 1250 * @return float|string 1251 */ 1252 public static function IMSECH($complexNumber) 1253 { 1254 $complexNumber = Functions::flattenSingleValue($complexNumber); 1255 1256 return (string) (new Complex($complexNumber))->sech(); 1257 } 1258 1259 /** 1260 * IMTAN. 1261 * 1262 * Returns the tangent of a complex number in x + yi or x + yj text format. 1263 * 1264 * Excel Function: 1265 * IMTAN(complexNumber) 1266 * 1267 * @param string $complexNumber the complex number for which you want the tangent 1268 * 1269 * @return float|string 1270 */ 1271 public static function IMTAN($complexNumber) 1272 { 1273 $complexNumber = Functions::flattenSingleValue($complexNumber); 1274 1275 return (string) (new Complex($complexNumber))->tan(); 1276 } 1277 1278 /** 1279 * IMSQRT. 1280 * 1281 * Returns the square root of a complex number in x + yi or x + yj text format. 1282 * 1283 * Excel Function: 1284 * IMSQRT(complexNumber) 1285 * 1286 * @param string $complexNumber the complex number for which you want the square root 1287 * 1288 * @return string 1289 */ 1290 public static function IMSQRT($complexNumber) 1291 { 1292 $complexNumber = Functions::flattenSingleValue($complexNumber); 1293 1294 $theta = self::IMARGUMENT($complexNumber); 1295 if ($theta === Functions::DIV0()) { 1296 return '0'; 1297 } 1298 1299 return (string) (new Complex($complexNumber))->sqrt(); 1300 } 1301 1302 /** 1303 * IMLN. 1304 * 1305 * Returns the natural logarithm of a complex number in x + yi or x + yj text format. 1306 * 1307 * Excel Function: 1308 * IMLN(complexNumber) 1309 * 1310 * @param string $complexNumber the complex number for which you want the natural logarithm 1311 * 1312 * @return string 1313 */ 1314 public static function IMLN($complexNumber) 1315 { 1316 $complexNumber = Functions::flattenSingleValue($complexNumber); 1317 1318 $complex = new Complex($complexNumber); 1319 if ($complex->getReal() == 0.0 && $complex->getImaginary() == 0.0) { 1320 return Functions::NAN(); 1321 } 1322 1323 return (string) (new Complex($complexNumber))->ln(); 1324 } 1325 1326 /** 1327 * IMLOG10. 1328 * 1329 * Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format. 1330 * 1331 * Excel Function: 1332 * IMLOG10(complexNumber) 1333 * 1334 * @param string $complexNumber the complex number for which you want the common logarithm 1335 * 1336 * @return string 1337 */ 1338 public static function IMLOG10($complexNumber) 1339 { 1340 $complexNumber = Functions::flattenSingleValue($complexNumber); 1341 1342 $complex = new Complex($complexNumber); 1343 if ($complex->getReal() == 0.0 && $complex->getImaginary() == 0.0) { 1344 return Functions::NAN(); 1345 } 1346 1347 return (string) (new Complex($complexNumber))->log10(); 1348 } 1349 1350 /** 1351 * IMLOG2. 1352 * 1353 * Returns the base-2 logarithm of a complex number in x + yi or x + yj text format. 1354 * 1355 * Excel Function: 1356 * IMLOG2(complexNumber) 1357 * 1358 * @param string $complexNumber the complex number for which you want the base-2 logarithm 1359 * 1360 * @return string 1361 */ 1362 public static function IMLOG2($complexNumber) 1363 { 1364 $complexNumber = Functions::flattenSingleValue($complexNumber); 1365 1366 $complex = new Complex($complexNumber); 1367 if ($complex->getReal() == 0.0 && $complex->getImaginary() == 0.0) { 1368 return Functions::NAN(); 1369 } 1370 1371 return (string) (new Complex($complexNumber))->log2(); 1372 } 1373 1374 /** 1375 * IMEXP. 1376 * 1377 * Returns the exponential of a complex number in x + yi or x + yj text format. 1378 * 1379 * Excel Function: 1380 * IMEXP(complexNumber) 1381 * 1382 * @param string $complexNumber the complex number for which you want the exponential 1383 * 1384 * @return string 1385 */ 1386 public static function IMEXP($complexNumber) 1387 { 1388 $complexNumber = Functions::flattenSingleValue($complexNumber); 1389 1390 return (string) (new Complex($complexNumber))->exp(); 1391 } 1392 1393 /** 1394 * IMPOWER. 1395 * 1396 * Returns a complex number in x + yi or x + yj text format raised to a power. 1397 * 1398 * Excel Function: 1399 * IMPOWER(complexNumber,realNumber) 1400 * 1401 * @param string $complexNumber the complex number you want to raise to a power 1402 * @param float $realNumber the power to which you want to raise the complex number 1403 * 1404 * @return string 1405 */ 1406 public static function IMPOWER($complexNumber, $realNumber) 1407 { 1408 $complexNumber = Functions::flattenSingleValue($complexNumber); 1409 $realNumber = Functions::flattenSingleValue($realNumber); 1410 1411 if (!is_numeric($realNumber)) { 1412 return Functions::VALUE(); 1413 } 1414 1415 return (string) (new Complex($complexNumber))->pow($realNumber); 1416 } 1417 1418 /** 1419 * IMDIV. 1420 * 1421 * Returns the quotient of two complex numbers in x + yi or x + yj text format. 1422 * 1423 * Excel Function: 1424 * IMDIV(complexDividend,complexDivisor) 1425 * 1426 * @param string $complexDividend the complex numerator or dividend 1427 * @param string $complexDivisor the complex denominator or divisor 1428 * 1429 * @return string 1430 */ 1431 public static function IMDIV($complexDividend, $complexDivisor) 1432 { 1433 $complexDividend = Functions::flattenSingleValue($complexDividend); 1434 $complexDivisor = Functions::flattenSingleValue($complexDivisor); 1435 1436 try { 1437 return (string) (new Complex($complexDividend))->divideby(new Complex($complexDivisor)); 1438 } catch (ComplexException $e) { 1439 return Functions::NAN(); 1440 } 1441 } 1442 1443 /** 1444 * IMSUB. 1445 * 1446 * Returns the difference of two complex numbers in x + yi or x + yj text format. 1447 * 1448 * Excel Function: 1449 * IMSUB(complexNumber1,complexNumber2) 1450 * 1451 * @param string $complexNumber1 the complex number from which to subtract complexNumber2 1452 * @param string $complexNumber2 the complex number to subtract from complexNumber1 1453 * 1454 * @return string 1455 */ 1456 public static function IMSUB($complexNumber1, $complexNumber2) 1457 { 1458 $complexNumber1 = Functions::flattenSingleValue($complexNumber1); 1459 $complexNumber2 = Functions::flattenSingleValue($complexNumber2); 1460 1461 try { 1462 return (string) (new Complex($complexNumber1))->subtract(new Complex($complexNumber2)); 1463 } catch (ComplexException $e) { 1464 return Functions::NAN(); 1465 } 1466 } 1467 1468 /** 1469 * IMSUM. 1470 * 1471 * Returns the sum of two or more complex numbers in x + yi or x + yj text format. 1472 * 1473 * Excel Function: 1474 * IMSUM(complexNumber[,complexNumber[,...]]) 1475 * 1476 * @param string ...$complexNumbers Series of complex numbers to add 1477 * 1478 * @return string 1479 */ 1480 public static function IMSUM(...$complexNumbers) 1481 { 1482 // Return value 1483 $returnValue = new Complex(0.0); 1484 $aArgs = Functions::flattenArray($complexNumbers); 1485 1486 try { 1487 // Loop through the arguments 1488 foreach ($aArgs as $complex) { 1489 $returnValue = $returnValue->add(new Complex($complex)); 1490 } 1491 } catch (ComplexException $e) { 1492 return Functions::NAN(); 1493 } 1494 1495 return (string) $returnValue; 1496 } 1497 1498 /** 1499 * IMPRODUCT. 1500 * 1501 * Returns the product of two or more complex numbers in x + yi or x + yj text format. 1502 * 1503 * Excel Function: 1504 * IMPRODUCT(complexNumber[,complexNumber[,...]]) 1505 * 1506 * @param string ...$complexNumbers Series of complex numbers to multiply 1507 * 1508 * @return string 1509 */ 1510 public static function IMPRODUCT(...$complexNumbers) 1511 { 1512 // Return value 1513 $returnValue = new Complex(1.0); 1514 $aArgs = Functions::flattenArray($complexNumbers); 1515 1516 try { 1517 // Loop through the arguments 1518 foreach ($aArgs as $complex) { 1519 $returnValue = $returnValue->multiply(new Complex($complex)); 1520 } 1521 } catch (ComplexException $e) { 1522 return Functions::NAN(); 1523 } 1524 1525 return (string) $returnValue; 1526 } 1527 1528 /** 1529 * DELTA. 1530 * 1531 * Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. 1532 * Use this function to filter a set of values. For example, by summing several DELTA 1533 * functions you calculate the count of equal pairs. This function is also known as the 1534 * Kronecker Delta function. 1535 * 1536 * Excel Function: 1537 * DELTA(a[,b]) 1538 * 1539 * @param float $a the first number 1540 * @param float $b The second number. If omitted, b is assumed to be zero. 1541 * 1542 * @return int 1543 */ 1544 public static function DELTA($a, $b = 0) 1545 { 1546 $a = Functions::flattenSingleValue($a); 1547 $b = Functions::flattenSingleValue($b); 1548 1549 return (int) ($a == $b); 1550 } 1551 1552 /** 1553 * GESTEP. 1554 * 1555 * Excel Function: 1556 * GESTEP(number[,step]) 1557 * 1558 * Returns 1 if number >= step; returns 0 (zero) otherwise 1559 * Use this function to filter a set of values. For example, by summing several GESTEP 1560 * functions you calculate the count of values that exceed a threshold. 1561 * 1562 * @param float $number the value to test against step 1563 * @param float $step The threshold value. 1564 * If you omit a value for step, GESTEP uses zero. 1565 * 1566 * @return int 1567 */ 1568 public static function GESTEP($number, $step = 0) 1569 { 1570 $number = Functions::flattenSingleValue($number); 1571 $step = Functions::flattenSingleValue($step); 1572 1573 return (int) ($number >= $step); 1574 } 1575 1576 // 1577 // Private method to calculate the erf value 1578 // 1579 private static $twoSqrtPi = 1.128379167095512574; 1580 1581 public static function erfVal($x) 1582 { 1583 if (abs($x) > 2.2) { 1584 return 1 - self::erfcVal($x); 1585 } 1586 $sum = $term = $x; 1587 $xsqr = ($x * $x); 1588 $j = 1; 1589 do { 1590 $term *= $xsqr / $j; 1591 $sum -= $term / (2 * $j + 1); 1592 ++$j; 1593 $term *= $xsqr / $j; 1594 $sum += $term / (2 * $j + 1); 1595 ++$j; 1596 if ($sum == 0.0) { 1597 break; 1598 } 1599 } while (abs($term / $sum) > Functions::PRECISION); 1600 1601 return self::$twoSqrtPi * $sum; 1602 } 1603 1604 /** 1605 * Validate arguments passed to the bitwise functions. 1606 * 1607 * @param mixed $value 1608 * 1609 * @return int 1610 */ 1611 private static function validateBitwiseArgument($value) 1612 { 1613 $value = Functions::flattenSingleValue($value); 1614 1615 if (is_int($value)) { 1616 return $value; 1617 } elseif (is_numeric($value)) { 1618 if ($value == (int) ($value)) { 1619 $value = (int) ($value); 1620 if (($value > 2 ** 48 - 1) || ($value < 0)) { 1621 throw new Exception(Functions::NAN()); 1622 } 1623 1624 return $value; 1625 } 1626 1627 throw new Exception(Functions::NAN()); 1628 } 1629 1630 throw new Exception(Functions::VALUE()); 1631 } 1632 1633 /** 1634 * BITAND. 1635 * 1636 * Returns the bitwise AND of two integer values. 1637 * 1638 * Excel Function: 1639 * BITAND(number1, number2) 1640 * 1641 * @param int $number1 1642 * @param int $number2 1643 * 1644 * @return int|string 1645 */ 1646 public static function BITAND($number1, $number2) 1647 { 1648 try { 1649 $number1 = self::validateBitwiseArgument($number1); 1650 $number2 = self::validateBitwiseArgument($number2); 1651 } catch (Exception $e) { 1652 return $e->getMessage(); 1653 } 1654 1655 return $number1 & $number2; 1656 } 1657 1658 /** 1659 * BITOR. 1660 * 1661 * Returns the bitwise OR of two integer values. 1662 * 1663 * Excel Function: 1664 * BITOR(number1, number2) 1665 * 1666 * @param int $number1 1667 * @param int $number2 1668 * 1669 * @return int|string 1670 */ 1671 public static function BITOR($number1, $number2) 1672 { 1673 try { 1674 $number1 = self::validateBitwiseArgument($number1); 1675 $number2 = self::validateBitwiseArgument($number2); 1676 } catch (Exception $e) { 1677 return $e->getMessage(); 1678 } 1679 1680 return $number1 | $number2; 1681 } 1682 1683 /** 1684 * BITXOR. 1685 * 1686 * Returns the bitwise XOR of two integer values. 1687 * 1688 * Excel Function: 1689 * BITXOR(number1, number2) 1690 * 1691 * @param int $number1 1692 * @param int $number2 1693 * 1694 * @return int|string 1695 */ 1696 public static function BITXOR($number1, $number2) 1697 { 1698 try { 1699 $number1 = self::validateBitwiseArgument($number1); 1700 $number2 = self::validateBitwiseArgument($number2); 1701 } catch (Exception $e) { 1702 return $e->getMessage(); 1703 } 1704 1705 return $number1 ^ $number2; 1706 } 1707 1708 /** 1709 * BITLSHIFT. 1710 * 1711 * Returns the number value shifted left by shift_amount bits. 1712 * 1713 * Excel Function: 1714 * BITLSHIFT(number, shift_amount) 1715 * 1716 * @param int $number 1717 * @param int $shiftAmount 1718 * 1719 * @return int|string 1720 */ 1721 public static function BITLSHIFT($number, $shiftAmount) 1722 { 1723 try { 1724 $number = self::validateBitwiseArgument($number); 1725 } catch (Exception $e) { 1726 return $e->getMessage(); 1727 } 1728 1729 $shiftAmount = Functions::flattenSingleValue($shiftAmount); 1730 1731 $result = $number << $shiftAmount; 1732 if ($result > 2 ** 48 - 1) { 1733 return Functions::NAN(); 1734 } 1735 1736 return $result; 1737 } 1738 1739 /** 1740 * BITRSHIFT. 1741 * 1742 * Returns the number value shifted right by shift_amount bits. 1743 * 1744 * Excel Function: 1745 * BITRSHIFT(number, shift_amount) 1746 * 1747 * @param int $number 1748 * @param int $shiftAmount 1749 * 1750 * @return int|string 1751 */ 1752 public static function BITRSHIFT($number, $shiftAmount) 1753 { 1754 try { 1755 $number = self::validateBitwiseArgument($number); 1756 } catch (Exception $e) { 1757 return $e->getMessage(); 1758 } 1759 1760 $shiftAmount = Functions::flattenSingleValue($shiftAmount); 1761 1762 return $number >> $shiftAmount; 1763 } 1764 1765 /** 1766 * ERF. 1767 * 1768 * Returns the error function integrated between the lower and upper bound arguments. 1769 * 1770 * Note: In Excel 2007 or earlier, if you input a negative value for the upper or lower bound arguments, 1771 * the function would return a #NUM! error. However, in Excel 2010, the function algorithm was 1772 * improved, so that it can now calculate the function for both positive and negative ranges. 1773 * PhpSpreadsheet follows Excel 2010 behaviour, and accepts negative arguments. 1774 * 1775 * Excel Function: 1776 * ERF(lower[,upper]) 1777 * 1778 * @param float $lower lower bound for integrating ERF 1779 * @param float $upper upper bound for integrating ERF. 1780 * If omitted, ERF integrates between zero and lower_limit 1781 * 1782 * @return float|string 1783 */ 1784 public static function ERF($lower, $upper = null) 1785 { 1786 $lower = Functions::flattenSingleValue($lower); 1787 $upper = Functions::flattenSingleValue($upper); 1788 1789 if (is_numeric($lower)) { 1790 if ($upper === null) { 1791 return self::erfVal($lower); 1792 } 1793 if (is_numeric($upper)) { 1794 return self::erfVal($upper) - self::erfVal($lower); 1795 } 1796 } 1797 1798 return Functions::VALUE(); 1799 } 1800 1801 /** 1802 * ERFPRECISE. 1803 * 1804 * Returns the error function integrated between the lower and upper bound arguments. 1805 * 1806 * Excel Function: 1807 * ERF.PRECISE(limit) 1808 * 1809 * @param float $limit bound for integrating ERF 1810 * 1811 * @return float|string 1812 */ 1813 public static function ERFPRECISE($limit) 1814 { 1815 $limit = Functions::flattenSingleValue($limit); 1816 1817 return self::ERF($limit); 1818 } 1819 1820 // 1821 // Private method to calculate the erfc value 1822 // 1823 private static $oneSqrtPi = 0.564189583547756287; 1824 1825 private static function erfcVal($x) 1826 { 1827 if (abs($x) < 2.2) { 1828 return 1 - self::erfVal($x); 1829 } 1830 if ($x < 0) { 1831 return 2 - self::ERFC(-$x); 1832 } 1833 $a = $n = 1; 1834 $b = $c = $x; 1835 $d = ($x * $x) + 0.5; 1836 $q1 = $q2 = $b / $d; 1837 $t = 0; 1838 do { 1839 $t = $a * $n + $b * $x; 1840 $a = $b; 1841 $b = $t; 1842 $t = $c * $n + $d * $x; 1843 $c = $d; 1844 $d = $t; 1845 $n += 0.5; 1846 $q1 = $q2; 1847 $q2 = $b / $d; 1848 } while ((abs($q1 - $q2) / $q2) > Functions::PRECISION); 1849 1850 return self::$oneSqrtPi * exp(-$x * $x) * $q2; 1851 } 1852 1853 /** 1854 * ERFC. 1855 * 1856 * Returns the complementary ERF function integrated between x and infinity 1857 * 1858 * Note: In Excel 2007 or earlier, if you input a negative value for the lower bound argument, 1859 * the function would return a #NUM! error. However, in Excel 2010, the function algorithm was 1860 * improved, so that it can now calculate the function for both positive and negative x values. 1861 * PhpSpreadsheet follows Excel 2010 behaviour, and accepts nagative arguments. 1862 * 1863 * Excel Function: 1864 * ERFC(x) 1865 * 1866 * @param float $x The lower bound for integrating ERFC 1867 * 1868 * @return float|string 1869 */ 1870 public static function ERFC($x) 1871 { 1872 $x = Functions::flattenSingleValue($x); 1873 1874 if (is_numeric($x)) { 1875 return self::erfcVal($x); 1876 } 1877 1878 return Functions::VALUE(); 1879 } 1880 1881 /** 1882 * getConversionGroups 1883 * Returns a list of the different conversion groups for UOM conversions. 1884 * 1885 * @Deprecated Use the getConversionCategories() method in the ConvertUOM class instead 1886 * 1887 * @return array 1888 */ 1889 public static function getConversionGroups() 1890 { 1891 return Engineering\ConvertUOM::getConversionCategories(); 1892 } 1893 1894 /** 1895 * getConversionGroupUnits 1896 * Returns an array of units of measure, for a specified conversion group, or for all groups. 1897 * 1898 * @Deprecated Use the getConversionCategoryUnits() method in the ConvertUOM class instead 1899 * 1900 * @param null|mixed $category 1901 * 1902 * @return array 1903 */ 1904 public static function getConversionGroupUnits($category = null) 1905 { 1906 return Engineering\ConvertUOM::getConversionCategoryUnits($category); 1907 } 1908 1909 /** 1910 * getConversionGroupUnitDetails. 1911 * 1912 * @Deprecated Use the getConversionCategoryUnitDetails() method in the ConvertUOM class instead 1913 * 1914 * @param null|mixed $category 1915 * 1916 * @return array 1917 */ 1918 public static function getConversionGroupUnitDetails($category = null) 1919 { 1920 return Engineering\ConvertUOM::getConversionCategoryUnitDetails($category); 1921 } 1922 1923 /** 1924 * getConversionMultipliers 1925 * Returns an array of the Multiplier prefixes that can be used with Units of Measure in CONVERTUOM(). 1926 * 1927 * @Deprecated Use the getConversionMultipliers() method in the ConvertUOM class instead 1928 * 1929 * @return array of mixed 1930 */ 1931 public static function getConversionMultipliers() 1932 { 1933 return Engineering\ConvertUOM::getConversionMultipliers(); 1934 } 1935 1936 /** 1937 * getBinaryConversionMultipliers 1938 * Returns an array of the additional Multiplier prefixes that can be used with Information Units of Measure in CONVERTUOM(). 1939 * 1940 * @Deprecated Use the getBinaryConversionMultipliers() method in the ConvertUOM class instead 1941 * 1942 * @return array of mixed 1943 */ 1944 public static function getBinaryConversionMultipliers() 1945 { 1946 return Engineering\ConvertUOM::getBinaryConversionMultipliers(); 1947 } 1948 1949 /** 1950 * CONVERTUOM. 1951 * 1952 * Converts a number from one measurement system to another. 1953 * For example, CONVERT can translate a table of distances in miles to a table of distances 1954 * in kilometers. 1955 * 1956 * Excel Function: 1957 * CONVERT(value,fromUOM,toUOM) 1958 * 1959 * @Deprecated Use the CONVERT() method in the ConvertUOM class instead 1960 * 1961 * @param float|int $value the value in fromUOM to convert 1962 * @param string $fromUOM the units for value 1963 * @param string $toUOM the units for the result 1964 * 1965 * @return float|string 1966 */ 1967 public static function CONVERTUOM($value, $fromUOM, $toUOM) 1968 { 1969 return Engineering\ConvertUOM::CONVERT($value, $fromUOM, $toUOM); 1970 } 1971} 1972