/**
* FLOOR
*
* Rounds number down, toward zero, to the nearest multiple of significance.
*
* Excel Function:
* FLOOR(number[,significance])
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param float $number Number to round
* @param float $significance Significance
* @return float Rounded Number
*/
public static function FLOOR($number, $significance = null)
{
$number = Functions::flattenSingleValue($number);
$significance = Functions::flattenSingleValue($significance);
if (is_null($significance) && Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) {
$significance = $number / abs($number);
}
if (is_numeric($number) && is_numeric($significance)) {
if ($significance == 0.0) {
return Functions::DIV0();
} elseif ($number == 0.0) {
return 0.0;
} elseif (self::SIGN($number) == self::SIGN($significance)) {
return floor($number / $significance) * $significance;
} else {
return Functions::NAN();
}
}
return Functions::VALUE();
}
/**
* CONCATENATE
*
* @return string
*/
public static function CONCATENATE()
{
$returnValue = '';
// Loop through arguments
$aArgs = Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
$arg = (int) $arg;
} else {
$arg = $arg ? \PHPExcel\Calculation::getTRUE() : \PHPExcel\Calculation::getFALSE();
}
}
$returnValue .= $arg;
}
return $returnValue;
}
/**
* STDEVP
*
* Calculates standard deviation based on the entire population
*
* Excel Function:
* STDEVP(value1[,value2[, ...]])
*
* @access public
* @category Statistical Functions
* @param mixed $arg,... Data values
* @return float
*/
public static function STDEVP()
{
$aArgs = Functions::flattenArrayIndexed(func_get_args());
$returnValue = null;
$aMean = self::AVERAGE($aArgs);
if (!is_null($aMean)) {
$aCount = 0;
foreach ($aArgs as $k => $arg) {
if (is_bool($arg) && (!Functions::isCellValue($k) || Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE)) {
$arg = (int) $arg;
}
// Is it a numeric value?
if (is_numeric($arg) && !is_string($arg)) {
if (is_null($returnValue)) {
$returnValue = pow($arg - $aMean, 2);
} else {
$returnValue += pow($arg - $aMean, 2);
}
++$aCount;
}
}
if ($aCount > 0 && $returnValue >= 0) {
return sqrt($returnValue / $aCount);
}
}
return Functions::DIV0();
}
/**
* TBILLYIELD
*
* Returns the yield for a Treasury bill.
*
* @param mixed settlement The Treasury bill's settlement date.
* The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
* @param mixed maturity The Treasury bill's maturity date.
* The maturity date is the date when the Treasury bill expires.
* @param int price The Treasury bill's price per $100 face value.
* @return float
*/
public static function TBILLYIELD($settlement, $maturity, $price)
{
$settlement = Functions::flattenSingleValue($settlement);
$maturity = Functions::flattenSingleValue($maturity);
$price = Functions::flattenSingleValue($price);
// Validate
if (is_numeric($price)) {
if ($price <= 0) {
return Functions::NAN();
}
if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
++$maturity;
$daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360;
if (!is_numeric($daysBetweenSettlementAndMaturity)) {
// return date error
return $daysBetweenSettlementAndMaturity;
}
} else {
$daysBetweenSettlementAndMaturity = DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement);
}
if ($daysBetweenSettlementAndMaturity > 360) {
return Functions::NAN();
}
return (100 - $price) / $price * (360 / $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);
}
/**
* DECTOOCT
*
* Return an decimal value as octal.
*
* Excel Function:
* DEC2OCT(x[,places])
*
* @access public
* @category Engineering Functions
* @param string $x The decimal integer you want to convert. If number is negative,
* places is ignored and DEC2OCT returns a 10-character (30-bit)
* octal number in which the most significant bit is the sign bit.
* The remaining 29 bits are magnitude bits. Negative numbers are
* represented using two's-complement notation.
* If number < -536,870,912 or if number > 536,870,911, DEC2OCT
* returns the #NUM! error value.
* If number is nonnumeric, DEC2OCT returns the #VALUE! error value.
* If DEC2OCT requires more than places characters, it returns the
* #NUM! error value.
* @param integer $places The number of characters to use. If places is omitted, DEC2OCT uses
* the minimum number of characters necessary. Places is useful for
* padding the return value with leading 0s (zeros).
* If places is not an integer, it is truncated.
* If places is nonnumeric, DEC2OCT returns the #VALUE! error value.
* If places is zero or negative, DEC2OCT returns the #NUM! error value.
* @return string
*/
public static function DECTOOCT($x, $places = null)
{
$xorig = $x;
$x = Functions::flattenSingleValue($x);
$places = Functions::flattenSingleValue($places);
if (is_bool($x)) {
if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
$x = (int) $x;
} else {
return Functions::VALUE();
}
}
$x = (string) $x;
if (strlen($x) > preg_match_all('/[-0123456789.]/', $x, $out)) {
return Functions::VALUE();
}
$x = (string) floor($x);
$r = decoct($x);
if (strlen($r) == 11) {
// Two's Complement
$r = substr($r, -10);
}
return self::nbrConversionFormat($r, $places);
}
