/**
* 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);
}
