/** * FIXEDFORMAT * * @param mixed $value Value to check * @param integer $decimals * @param boolean $no_commas * @return boolean */ public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = false) { $value = Functions::flattenSingleValue($value); $decimals = Functions::flattenSingleValue($decimals); $no_commas = Functions::flattenSingleValue($no_commas); // Validate parameters if (!is_numeric($value) || !is_numeric($decimals)) { return Functions::NAN(); } $decimals = floor($decimals); $valueResult = round($value, $decimals); if ($decimals < 0) { $decimals = 0; } if (!$no_commas) { $valueResult = number_format($valueResult, $decimals); } return (string) $valueResult; }
/** * SQRTPI * * Returns the square root of (number * pi). * * @param float $number Number * @return float Square Root of Number * Pi */ public static function SQRTPI($number) { $number = Functions::flattenSingleValue($number); if (is_numeric($number)) { if ($number < 0) { return Functions::NAN(); } return sqrt($number * M_PI); } return Functions::VALUE(); }
/** * WEIBULL * * Returns the Weibull distribution. Use this distribution in reliability * analysis, such as calculating a device's mean time to failure. * * @param float $value * @param float $alpha Alpha Parameter * @param float $beta Beta Parameter * @param boolean $cumulative * @return float * */ public static function WEIBULL($value, $alpha, $beta, $cumulative) { $value = Functions::flattenSingleValue($value); $alpha = Functions::flattenSingleValue($alpha); $beta = Functions::flattenSingleValue($beta); if (is_numeric($value) && is_numeric($alpha) && is_numeric($beta)) { if ($value < 0 || $alpha <= 0 || $beta <= 0) { return Functions::NAN(); } if (is_numeric($cumulative) || is_bool($cumulative)) { if ($cumulative) { return 1 - exp(0 - pow($value / $beta, $alpha)); } else { return $alpha / pow($beta, $alpha) * pow($value, $alpha - 1) * exp(0 - pow($value / $beta, $alpha)); } } } return Functions::VALUE(); }
/** * YIELDMAT * * Returns the annual yield of a security that pays interest at maturity. * * @param mixed settlement The security's settlement date. * The security's settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param mixed issue The security's issue date. * @param int rate The security's interest rate at date of issue. * @param int price The security's price per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 * 1 Actual/actual * 2 Actual/360 * 3 Actual/365 * 4 European 30/360 * @return float */ public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0) { $settlement = Functions::flattenSingleValue($settlement); $maturity = Functions::flattenSingleValue($maturity); $issue = Functions::flattenSingleValue($issue); $rate = Functions::flattenSingleValue($rate); $price = Functions::flattenSingleValue($price); $basis = (int) Functions::flattenSingleValue($basis); // Validate if (is_numeric($rate) && is_numeric($price)) { if ($rate <= 0 || $price <= 0) { return Functions::NAN(); } $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); if (!is_numeric($daysPerYear)) { return $daysPerYear; } $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); if (!is_numeric($daysBetweenIssueAndSettlement)) { // return date error return $daysBetweenIssueAndSettlement; } $daysBetweenIssueAndSettlement *= $daysPerYear; $daysBetweenIssueAndMaturity = DateTime::YEARFRAC($issue, $maturity, $basis); if (!is_numeric($daysBetweenIssueAndMaturity)) { // return date error return $daysBetweenIssueAndMaturity; } $daysBetweenIssueAndMaturity *= $daysPerYear; $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); if (!is_numeric($daysBetweenSettlementAndMaturity)) { // return date error return $daysBetweenSettlementAndMaturity; } $daysBetweenSettlementAndMaturity *= $daysPerYear; return (1 + $daysBetweenIssueAndMaturity / $daysPerYear * $rate - ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate)) / ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate) * ($daysPerYear / $daysBetweenSettlementAndMaturity); } return Functions::VALUE(); }
/** * SECONDOFMINUTE * * Returns the seconds of a time value. * The second is given as an integer in the range 0 (zero) to 59. * * Excel Function: * SECOND(timeValue) * * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), * PHP DateTime object, or a standard time string * @return int Second */ public static function SECONDOFMINUTE($timeValue = 0) { $timeValue = Functions::flattenSingleValue($timeValue); if (!is_numeric($timeValue)) { if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { $testVal = strtok($timeValue, '/-: '); if (strlen($testVal) < strlen($timeValue)) { return Functions::VALUE(); } } $timeValue = self::getTimeValue($timeValue); if (is_string($timeValue)) { return Functions::VALUE(); } } // Execute function if ($timeValue >= 1) { $timeValue = fmod($timeValue, 1); } elseif ($timeValue < 0.0) { return Functions::NAN(); } $timeValue = \PHPExcel\Shared\Date::excelToPHP($timeValue); return (int) gmdate('s', $timeValue); }
/** * DGET * * Extracts a single value from a column of a list or database that matches conditions that you * specify. * * Excel Function: * DGET(database,field,criteria) * * @access public * @category Database Functions * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param string|integer $field Indicates which column is used in the function. Enter the * column label enclosed between double quotation marks, such as * "Age" or "Yield," or a number (without quotation marks) that * represents the position of the column within the list: 1 for * the first column, 2 for the second column, and so on. * @param mixed[] $criteria The range of cells that contains the conditions you specify. * You can use any range for the criteria argument, as long as it * includes at least one column label and at least one cell below * the column label in which you specify a condition for the * column. * @return mixed * */ public static function DGET($database, $field, $criteria) { $field = self::fieldExtract($database, $field); if (is_null($field)) { return null; } // Return $colData = self::getFilteredColumn($database, $field, $criteria); if (count($colData) > 1) { return Functions::NAN(); } return $colData[0]; }
/** * IMPRODUCT * * Returns the product of two or more complex numbers in x + yi or x + yj text format. * * Excel Function: * IMPRODUCT(complexNumber[,complexNumber[,...]]) * * @param string $complexNumber,... Series of complex numbers to multiply * @return string */ public static function IMPRODUCT() { // Return value $returnValue = self::parseComplex('1'); $activeSuffix = ''; // Loop through the arguments $aArgs = Functions::flattenArray(func_get_args()); foreach ($aArgs as $arg) { $parsedComplex = self::parseComplex($arg); $workValue = $returnValue; if ($parsedComplex['suffix'] != '' && $activeSuffix == '') { $activeSuffix = $parsedComplex['suffix']; } elseif ($parsedComplex['suffix'] != '' && $activeSuffix != $parsedComplex['suffix']) { return Functions::NAN(); } $returnValue['real'] = $workValue['real'] * $parsedComplex['real'] - $workValue['imaginary'] * $parsedComplex['imaginary']; $returnValue['imaginary'] = $workValue['real'] * $parsedComplex['imaginary'] + $workValue['imaginary'] * $parsedComplex['real']; } if ($returnValue['imaginary'] == 0.0) { $activeSuffix = ''; } return self::COMPLEX($returnValue['real'], $returnValue['imaginary'], $activeSuffix); }