/**
* 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 = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
// Return value
$returnValue = null;
$aMean = self::AVERAGE($aArgs);
if (!is_null($aMean)) {
$aCount = 0;
foreach ($aArgs as $k => $arg) {
if (is_bool($arg) && (!PHPExcel_Calculation_Functions::isCellValue($k) || PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_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;
}
}
// Return
if ($aCount > 0 && $returnValue >= 0) {
return sqrt($returnValue / $aCount);
}
}
return PHPExcel_Calculation_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 = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
$maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
$price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
// Validate
if (is_numeric($price)) {
if ($price <= 0) {
return PHPExcel_Calculation_Functions::NaN();
}
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
++$maturity;
$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
if (!is_numeric($daysBetweenSettlementAndMaturity)) {
// return date error
return $daysBetweenSettlementAndMaturity;
}
} else {
$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement);
}
if ($daysBetweenSettlementAndMaturity > 360) {
return PHPExcel_Calculation_Functions::NaN();
}
return (100 - $price) / $price * (360 / $daysBetweenSettlementAndMaturity);
}
return PHPExcel_Calculation_Functions::VALUE();
}
private function _executeNumericBinaryOperation($cellID, $operand1, $operand2, $operation, $matrixFunction, &$stack)
{
// Validate the two operands
if (!$this->_validateBinaryOperand($cellID, $operand1, $stack)) {
return FALSE;
}
if (!$this->_validateBinaryOperand($cellID, $operand2, $stack)) {
return FALSE;
}
// If either of the operands is a matrix, we need to treat them both as matrices
// (converting the other operand to a matrix if need be); then perform the required
// matrix operation
if (is_array($operand1) || is_array($operand2)) {
// Ensure that both operands are arrays/matrices of the same size
self::_checkMatrixOperands($operand1, $operand2, 2);
try {
// Convert operand 1 from a PHP array to a matrix
$matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
// Perform the required operation against the operand 1 matrix, passing in operand 2
$matrixResult = $matrix->{$matrixFunction}($operand2);
$result = $matrixResult->getArray();
} catch (PHPExcel_Exception $ex) {
$this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
$result = '#VALUE!';
}
} else {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE && (is_string($operand1) && !is_numeric($operand1) && strlen($operand1) > 0 || is_string($operand2) && !is_numeric($operand2) && strlen($operand2) > 0)) {
$result = PHPExcel_Calculation_Functions::VALUE();
} else {
// If we're dealing with non-matrix operations, execute the necessary operation
switch ($operation) {
// Addition
case '+':
$result = $operand1 + $operand2;
break;
// Subtraction
// Subtraction
case '-':
$result = $operand1 - $operand2;
break;
// Multiplication
// Multiplication
case '*':
$result = $operand1 * $operand2;
break;
// Division
// Division
case '/':
if ($operand2 == 0) {
// Trap for Divide by Zero error
$stack->push('Value', '#DIV/0!');
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails('#DIV/0!'));
return FALSE;
} else {
$result = $operand1 / $operand2;
}
break;
// Power
// Power
case '^':
$result = pow($operand1, $operand2);
break;
}
}
}
// Log the result details
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
// And push the result onto the stack
$stack->push('Value', $result);
return TRUE;
}
/**
* SECONDOFMINUTE
*
* @param long $timeValue Excel time serial value or a standard time string
* @return int Second
*/
public static function SECONDOFMINUTE($timeValue = 0)
{
$timeValue = PHPExcel_Calculation_Functions::flattenSingleValue($timeValue);
if (!is_numeric($timeValue)) {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
$testVal = strtok($timeValue, '/-: ');
if (strlen($testVal) < strlen($timeValue)) {
return PHPExcel_Calculation_Functions::VALUE();
}
}
$timeValue = self::_getTimeValue($timeValue);
if (is_string($timeValue)) {
return PHPExcel_Calculation_Functions::VALUE();
}
}
// Execute function
if ($timeValue >= 1) {
$timeValue = fmod($timeValue, 1);
} elseif ($timeValue < 0.0) {
return PHPExcel_Calculation_Functions::NaN();
}
$timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
return (int) gmdate('s', $timeValue);
}
/**
* CONCATENATE
*
* @return string
*/
public static function CONCATENATE()
{
// Return value
$returnValue = '';
// Loop through arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
$arg = (int) $arg;
} else {
$arg = $arg ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
}
}
$returnValue .= $arg;
}
// Return
return $returnValue;
}
/**
* 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 = PHPExcel_Calculation_Functions::flattenSingleValue($number);
$significance = PHPExcel_Calculation_Functions::flattenSingleValue($significance);
if (is_null($significance) && PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
$significance = $number / abs($number);
}
if (is_numeric($number) && is_numeric($significance)) {
if ($significance == 0.0) {
return PHPExcel_Calculation_Functions::DIV0();
} elseif ($number == 0.0) {
return 0.0;
} elseif (self::SIGN($number) == self::SIGN($significance)) {
return floor($number / $significance) * $significance;
} else {
return PHPExcel_Calculation_Functions::NaN();
}
} else {
return PHPExcel_Calculation_Functions::VALUE();
}
}
/**
* 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)
{
$x = PHPExcel_Calculation_Functions::flattenSingleValue($x);
$places = PHPExcel_Calculation_Functions::flattenSingleValue($places);
if (is_bool($x)) {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
$x = (int) $x;
} else {
return PHPExcel_Calculation_Functions::VALUE();
}
}
$x = (string) $x;
if (strlen($x) > preg_match_all('/[-0123456789.]/', $x, $out)) {
return PHPExcel_Calculation_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);
}
