/**
* fieldExtract
*
* Extracts the column ID to use for the data field.
*
* @access private
* @param mixed[] $database The range of cells that makes up the list or database.
* A database is a list of related data in which rows of related
* information are records, and columns of data are fields. The
* first row of the list contains labels for each column.
* @param mixed $field Indicates which column is used in the function. Enter the
* column label enclosed between double quotation marks, such as
* "Age" or "Yield," or a number (without quotation marks) that
* represents the position of the column within the list: 1 for
* the first column, 2 for the second column, and so on.
* @return string|NULL
*
*/
private static function fieldExtract($database, $field)
{
$field = strtoupper(Functions::flattenSingleValue($field));
$fieldNames = array_map('strtoupper', array_shift($database));
if (is_numeric($field)) {
$keys = array_keys($fieldNames);
return $keys[$field - 1];
}
$key = array_search($field, $fieldNames);
return $key ? $key : null;
}
/**
* 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;
}
/**
* 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;
}
/**
* LOOKUP
* The LOOKUP function searches for value either from a one-row or one-column range or from an array.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_vector The range of cells being searched
* @param result_vector The column from which the matching value must be returned
* @return mixed The value of the found cell
*/
public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
{
$lookup_value = Functions::flattenSingleValue($lookup_value);
if (!is_array($lookup_vector)) {
return Functions::NA();
}
$lookupRows = count($lookup_vector);
$l = array_keys($lookup_vector);
$l = array_shift($l);
$lookupColumns = count($lookup_vector[$l]);
if ($lookupRows == 1 && $lookupColumns > 1 || $lookupRows == 2 && $lookupColumns != 2) {
$lookup_vector = self::TRANSPOSE($lookup_vector);
$lookupRows = count($lookup_vector);
$l = array_keys($lookup_vector);
$lookupColumns = count($lookup_vector[array_shift($l)]);
}
if (is_null($result_vector)) {
$result_vector = $lookup_vector;
}
$resultRows = count($result_vector);
$l = array_keys($result_vector);
$l = array_shift($l);
$resultColumns = count($result_vector[$l]);
if ($resultRows == 1 && $resultColumns > 1 || $resultRows == 2 && $resultColumns != 2) {
$result_vector = self::TRANSPOSE($result_vector);
$resultRows = count($result_vector);
$r = array_keys($result_vector);
$resultColumns = count($result_vector[array_shift($r)]);
}
if ($lookupRows == 2) {
$result_vector = array_pop($lookup_vector);
$lookup_vector = array_shift($lookup_vector);
}
if ($lookupColumns != 2) {
foreach ($lookup_vector as &$value) {
if (is_array($value)) {
$k = array_keys($value);
$key1 = $key2 = array_shift($k);
$key2++;
$dataValue1 = $value[$key1];
} else {
$key1 = 0;
$key2 = 1;
$dataValue1 = $value;
}
$dataValue2 = array_shift($result_vector);
if (is_array($dataValue2)) {
$dataValue2 = array_shift($dataValue2);
}
$value = array($key1 => $dataValue1, $key2 => $dataValue2);
}
unset($value);
}
return self::VLOOKUP($lookup_value, $lookup_vector, 2);
}
/**
* ZTEST
*
* Returns the Weibull distribution. Use this distribution in reliability
* analysis, such as calculating a device's mean time to failure.
*
* @param float $dataSet
* @param float $m0 Alpha Parameter
* @param float $sigma Beta Parameter
* @param boolean $cumulative
* @return float
*
*/
public static function ZTEST($dataSet, $m0, $sigma = null)
{
$dataSet = Functions::flattenArrayIndexed($dataSet);
$m0 = Functions::flattenSingleValue($m0);
$sigma = Functions::flattenSingleValue($sigma);
if (is_null($sigma)) {
$sigma = self::STDEV($dataSet);
}
$n = count($dataSet);
return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / SQRT($n)));
}
/**
* 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();
}
public static function ifCondition($condition)
{
$condition = Functions::flattenSingleValue($condition);
if (!isset($condition[0])) {
$condition = '=""';
}
if (!in_array($condition[0], array('>', '<', '='))) {
if (!is_numeric($condition)) {
$condition = \PHPExcel\Calculation::wrapResult(strtoupper($condition));
}
return '=' . $condition;
} else {
preg_match('/([<>=]+)(.*)/', $condition, $matches);
list(, $operator, $operand) = $matches;
if (!is_numeric($operand)) {
$operand = str_replace('"', '""', $operand);
$operand = \PHPExcel\Calculation::wrapResult(strtoupper($operand));
}
return $operator . $operand;
}
}
/**
* 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;
}
/**
* IFERROR
*
* Excel Function:
* =IFERROR(testValue,errorpart)
*
* @access public
* @category Logical Functions
* @param mixed $testValue Value to check, is also the value returned when no error
* @param mixed $errorpart Value to return when testValue is an error condition
* @return mixed The value of errorpart or testValue determined by error condition
*/
public static function IFERROR($testValue = '', $errorpart = '')
{
$testValue = is_null($testValue) ? '' : Functions::flattenSingleValue($testValue);
$errorpart = is_null($errorpart) ? '' : Functions::flattenSingleValue($errorpart);
return self::statementIf(Functions::isError($testValue), $errorpart, $testValue);
}
