/**
* TRUNC
*
* Truncates value to the number of fractional digits by number_digits.
*
* @param float $value
* @param int $digits
* @return float Truncated value
*/
public static function TRUNC($value = 0, $digits = 0)
{
$value = Functions::flattenSingleValue($value);
$digits = Functions::flattenSingleValue($digits);
// Validate parameters
if (!is_numeric($value) || !is_numeric($digits)) {
return Functions::VALUE();
}
$digits = floor($digits);
// Truncate
$adjust = pow(10, $digits);
if ($digits > 0 && rtrim(intval((abs($value) - abs(intval($value))) * $adjust), '0') < $adjust / 10) {
return $value;
}
return intval($value * $adjust) / $adjust;
}
/**
* HLOOKUP
* The HLOOKUP function searches for value in the top-most row of lookup_array and returns the value in the same column based on the index_number.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The row number in table_array from which the matching value must be returned. The first row is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
{
$lookup_value = Functions::flattenSingleValue($lookup_value);
$index_number = Functions::flattenSingleValue($index_number);
$not_exact_match = Functions::flattenSingleValue($not_exact_match);
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return Functions::VALUE();
}
// index_number must be less than or equal to the number of columns in lookup_array
if (!is_array($lookup_array) || empty($lookup_array)) {
return Functions::REF();
} else {
$f = array_keys($lookup_array);
$firstRow = array_pop($f);
if (!is_array($lookup_array[$firstRow]) || $index_number - 1 > count($lookup_array[$firstRow])) {
return Functions::REF();
} else {
$columnKeys = array_keys($lookup_array[$firstRow]);
$firstkey = $f[0] - 1;
$returnColumn = $firstkey + $index_number;
$firstColumn = array_shift($f);
}
}
if (!$not_exact_match) {
$firstRowH = asort($lookup_array[$firstColumn]);
}
$rowNumber = $rowValue = false;
foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) {
if (is_numeric($lookup_value) && is_numeric($rowData) && $rowData > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData) && strtolower($rowData) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData;
}
if ($rowNumber !== false) {
if (!$not_exact_match && $rowValue != $lookup_value) {
// if an exact match is required, we have what we need to return an appropriate response
return Functions::NA();
} else {
// otherwise return the appropriate value
return $lookup_array[$returnColumn][$rowNumber];
}
}
return Functions::NA();
}
/**
* VALUE
*
* @param mixed $value Value to check
* @return boolean
*/
public static function VALUE($value = '')
{
$value = Functions::flattenSingleValue($value);
if (!is_numeric($value)) {
$numberValue = str_replace(\PHPExcel\Shared\StringHelper::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . \PHPExcel\Shared\StringHelper::getCurrencyCode()));
if (is_numeric($numberValue)) {
return (double) $numberValue;
}
$dateSetting = Functions::getReturnDateType();
Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
if (strpos($value, ':') !== false) {
$timeValue = DateTime::TIMEVALUE($value);
if ($timeValue !== Functions::VALUE()) {
Functions::setReturnDateType($dateSetting);
return $timeValue;
}
}
$dateValue = DateTime::DATEVALUE($value);
if ($dateValue !== Functions::VALUE()) {
Functions::setReturnDateType($dateSetting);
return $dateValue;
}
Functions::setReturnDateType($dateSetting);
return Functions::VALUE();
}
return (double) $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();
}
/**
* EOMONTH
*
* Returns the date value for the last day of the month that is the indicated number of months
* before or after start_date.
* Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
*
* Excel Function:
* EOMONTH(dateValue,adjustmentMonths)
*
* @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer),
* PHP DateTime object, or a standard date string
* @param int $adjustmentMonths The number of months before or after start_date.
* A positive value for months yields a future date;
* a negative value yields a past date.
* @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
* depending on the value of the ReturnDateType flag
*/
public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
{
$dateValue = Functions::flattenSingleValue($dateValue);
$adjustmentMonths = Functions::flattenSingleValue($adjustmentMonths);
if (!is_numeric($adjustmentMonths)) {
return Functions::VALUE();
}
$adjustmentMonths = floor($adjustmentMonths);
if (is_string($dateValue = self::getDateValue($dateValue))) {
return Functions::VALUE();
}
// Execute function
$PHPDateObject = self::adjustDateByMonths($dateValue, $adjustmentMonths + 1);
$adjustDays = (int) $PHPDateObject->format('d');
$adjustDaysString = '-' . $adjustDays . ' days';
$PHPDateObject->modify($adjustDaysString);
switch (Functions::getReturnDateType()) {
case Functions::RETURNDATE_EXCEL:
return (double) \PHPExcel\Shared\Date::PHPToExcel($PHPDateObject);
case Functions::RETURNDATE_PHP_NUMERIC:
return (int) \PHPExcel\Shared\Date::excelToPHP(\PHPExcel\Shared\Date::PHPToExcel($PHPDateObject));
case Functions::RETURNDATE_PHP_OBJECT:
return $PHPDateObject;
}
}
/**
* CONVERTUOM
*
* Converts a number from one measurement system to another.
* For example, CONVERT can translate a table of distances in miles to a table of distances
* in kilometers.
*
* Excel Function:
* CONVERT(value,fromUOM,toUOM)
*
* @param float $value The value in fromUOM to convert.
* @param string $fromUOM The units for value.
* @param string $toUOM The units for the result.
*
* @return float
*/
public static function CONVERTUOM($value, $fromUOM, $toUOM)
{
$value = Functions::flattenSingleValue($value);
$fromUOM = Functions::flattenSingleValue($fromUOM);
$toUOM = Functions::flattenSingleValue($toUOM);
if (!is_numeric($value)) {
return Functions::VALUE();
}
$fromMultiplier = 1.0;
if (isset(self::$conversionUnits[$fromUOM])) {
$unitGroup1 = self::$conversionUnits[$fromUOM]['Group'];
} else {
$fromMultiplier = substr($fromUOM, 0, 1);
$fromUOM = substr($fromUOM, 1);
if (isset(self::$conversionMultipliers[$fromMultiplier])) {
$fromMultiplier = self::$conversionMultipliers[$fromMultiplier]['multiplier'];
} else {
return Functions::NA();
}
if (isset(self::$conversionUnits[$fromUOM]) && self::$conversionUnits[$fromUOM]['AllowPrefix']) {
$unitGroup1 = self::$conversionUnits[$fromUOM]['Group'];
} else {
return Functions::NA();
}
}
$value *= $fromMultiplier;
$toMultiplier = 1.0;
if (isset(self::$conversionUnits[$toUOM])) {
$unitGroup2 = self::$conversionUnits[$toUOM]['Group'];
} else {
$toMultiplier = substr($toUOM, 0, 1);
$toUOM = substr($toUOM, 1);
if (isset(self::$conversionMultipliers[$toMultiplier])) {
$toMultiplier = self::$conversionMultipliers[$toMultiplier]['multiplier'];
} else {
return Functions::NA();
}
if (isset(self::$conversionUnits[$toUOM]) && self::$conversionUnits[$toUOM]['AllowPrefix']) {
$unitGroup2 = self::$conversionUnits[$toUOM]['Group'];
} else {
return Functions::NA();
}
}
if ($unitGroup1 != $unitGroup2) {
return Functions::NA();
}
if ($fromUOM == $toUOM && $fromMultiplier == $toMultiplier) {
// We've already factored $fromMultiplier into the value, so we need
// to reverse it again
return $value / $fromMultiplier;
} elseif ($unitGroup1 == 'Temperature') {
if ($fromUOM == 'F' || $fromUOM == 'fah') {
if ($toUOM == 'F' || $toUOM == 'fah') {
return $value;
} else {
$value = ($value - 32) / 1.8;
if ($toUOM == 'K' || $toUOM == 'kel') {
$value += 273.15;
}
return $value;
}
} elseif (($fromUOM == 'K' || $fromUOM == 'kel') && ($toUOM == 'K' || $toUOM == 'kel')) {
return $value;
} elseif (($fromUOM == 'C' || $fromUOM == 'cel') && ($toUOM == 'C' || $toUOM == 'cel')) {
return $value;
}
if ($toUOM == 'F' || $toUOM == 'fah') {
if ($fromUOM == 'K' || $fromUOM == 'kel') {
$value -= 273.15;
}
return $value * 1.8 + 32;
}
if ($toUOM == 'C' || $toUOM == 'cel') {
return $value - 273.15;
}
return $value + 273.15;
}
return $value * self::$unitConversions[$unitGroup1][$fromUOM][$toUOM] / $toMultiplier;
}
/**
* NOT
*
* Returns the boolean inverse of the argument.
*
* Excel Function:
* =NOT(logical)
*
* The argument must evaluate to a logical value such as TRUE or FALSE
*
* Boolean arguments are treated as True or False as appropriate
* Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
* If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
* the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
*
* @access public
* @category Logical Functions
* @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE
* @return boolean The boolean inverse of the argument.
*/
public static function NOT($logical = false)
{
$logical = Functions::flattenSingleValue($logical);
if (is_string($logical)) {
$logical = strtoupper($logical);
if ($logical == 'TRUE' || $logical == \PHPExcel\Calculation::getTRUE()) {
return false;
} elseif ($logical == 'FALSE' || $logical == \PHPExcel\Calculation::getFALSE()) {
return true;
} else {
return Functions::VALUE();
}
}
return !$logical;
}
