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