Exemple #1
0
 /**
  *	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();
 }
Exemple #2
0
 /**
  * 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();
 }
Exemple #3
0
 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;
 }
Exemple #4
0
 /**
  * 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;
 }
Exemple #6
0
 /**
  * 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();
     }
 }
Exemple #7
0
 /**
  * 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);
 }