/**
* SUMXMY2
*
* @param mixed[] $matrixData1 Matrix #1
* @param mixed[] $matrixData2 Matrix #2
* @return float
*/
public static function SUMXMY2($matrixData1, $matrixData2)
{
$array1 = Functions::flattenArray($matrixData1);
$array2 = Functions::flattenArray($matrixData2);
$count = min(count($array1), count($array2));
$result = 0;
for ($i = 0; $i < $count; ++$i) {
if (is_numeric($array1[$i]) && !is_string($array1[$i]) && (is_numeric($array2[$i]) && !is_string($array2[$i]))) {
$result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]);
}
}
return $result;
}
/**
* MATCH
*
* The MATCH function searches for a specified item in a range of cells
*
* Excel Function:
* =MATCH(lookup_value, lookup_array, [match_type])
*
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param match_type The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. If match_type is 1 or -1, the list has to be ordered.
* @return integer The relative position of the found item
*/
public static function MATCH($lookup_value, $lookup_array, $match_type = 1)
{
$lookup_array = Functions::flattenArray($lookup_array);
$lookup_value = Functions::flattenSingleValue($lookup_value);
$match_type = is_null($match_type) ? 1 : (int) Functions::flattenSingleValue($match_type);
// MATCH is not case sensitive
$lookup_value = strtolower($lookup_value);
// lookup_value type has to be number, text, or logical values
if (!is_numeric($lookup_value) && !is_string($lookup_value) && !is_bool($lookup_value)) {
return Functions::NA();
}
// match_type is 0, 1 or -1
if ($match_type !== 0 && $match_type !== -1 && $match_type !== 1) {
return Functions::NA();
}
// lookup_array should not be empty
$lookupArraySize = count($lookup_array);
if ($lookupArraySize <= 0) {
return Functions::NA();
}
// lookup_array should contain only number, text, or logical values, or empty (null) cells
foreach ($lookup_array as $i => $lookupArrayValue) {
// check the type of the value
if (!is_numeric($lookupArrayValue) && !is_string($lookupArrayValue) && !is_bool($lookupArrayValue) && !is_null($lookupArrayValue)) {
return Functions::NA();
}
// convert strings to lowercase for case-insensitive testing
if (is_string($lookupArrayValue)) {
$lookup_array[$i] = strtolower($lookupArrayValue);
}
if (is_null($lookupArrayValue) && ($match_type == 1 || $match_type == -1)) {
$lookup_array = array_slice($lookup_array, 0, $i - 1);
}
}
// if match_type is 1 or -1, the list has to be ordered
if ($match_type == 1) {
asort($lookup_array);
$keySet = array_keys($lookup_array);
} elseif ($match_type == -1) {
arsort($lookup_array);
$keySet = array_keys($lookup_array);
}
// **
// find the match
// **
foreach ($lookup_array as $i => $lookupArrayValue) {
if ($match_type == 0 && $lookupArrayValue == $lookup_value) {
// exact match
return ++$i;
} elseif ($match_type == -1 && $lookupArrayValue <= $lookup_value) {
$i = array_search($i, $keySet);
// if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
if ($i < 1) {
// 1st cell was already smaller than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i - 1] + 1;
}
} elseif ($match_type == 1 && $lookupArrayValue >= $lookup_value) {
$i = array_search($i, $keySet);
// if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
if ($i < 1) {
// 1st cell was already bigger than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i - 1] + 1;
}
}
}
// unsuccessful in finding a match, return #N/A error value
return Functions::NA();
}
/**
* CONCATENATE
*
* @return string
*/
public static function CONCATENATE()
{
$returnValue = '';
// Loop through arguments
$aArgs = Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
$arg = (int) $arg;
} else {
$arg = $arg ? \PHPExcel\Calculation::getTRUE() : \PHPExcel\Calculation::getFALSE();
}
}
$returnValue .= $arg;
}
return $returnValue;
}
/**
* VARP
*
* Calculates variance based on the entire population
*
* Excel Function:
* VARP(value1[,value2[, ...]])
*
* @access public
* @category Statistical Functions
* @param mixed $arg,... Data values
* @return float
*/
public static function VARP()
{
// Return value
$returnValue = Functions::DIV0();
$summerA = $summerB = 0;
// Loop through arguments
$aArgs = Functions::flattenArray(func_get_args());
$aCount = 0;
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
$arg = (int) $arg;
}
// Is it a numeric value?
if (is_numeric($arg) && !is_string($arg)) {
$summerA += $arg * $arg;
$summerB += $arg;
++$aCount;
}
}
if ($aCount > 0) {
$summerA *= $aCount;
$summerB *= $summerB;
$returnValue = ($summerA - $summerB) / ($aCount * $aCount);
}
return $returnValue;
}
/**
* XNPV
*
* Returns the net present value for a schedule of cash flows that is not necessarily periodic.
* To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
*
* Excel Function:
* =XNPV(rate,values,dates)
*
* @param float $rate The discount rate to apply to the cash flows.
* @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates.
* The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment.
* If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year.
* The series of values must contain at least one positive value and one negative value.
* @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments.
* The first payment date indicates the beginning of the schedule of payments.
* All other dates must be later than this date, but they may occur in any order.
* @return float
*/
public static function XNPV($rate, $values, $dates)
{
$rate = Functions::flattenSingleValue($rate);
if (!is_numeric($rate)) {
return Functions::VALUE();
}
if (!is_array($values) || !is_array($dates)) {
return Functions::VALUE();
}
$values = Functions::flattenArray($values);
$dates = Functions::flattenArray($dates);
$valCount = count($values);
if ($valCount != count($dates)) {
return Functions::NAN();
}
if (min($values) > 0 || max($values) < 0) {
return Functions::VALUE();
}
$xnpv = 0.0;
for ($i = 0; $i < $valCount; ++$i) {
if (!is_numeric($values[$i])) {
return Functions::VALUE();
}
$xnpv += $values[$i] / pow(1 + $rate, DateTime::DATEDIF($dates[0], $dates[$i], 'd') / 365);
}
return is_finite($xnpv) ? $xnpv : Functions::VALUE();
}
/**
* WORKDAY
*
* Returns the date that is the indicated number of working days before or after a date (the
* starting date). Working days exclude weekends and any dates identified as holidays.
* Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected
* delivery times, or the number of days of work performed.
*
* Excel Function:
* WORKDAY(startDate,endDays[,holidays[,holiday[,...]]])
*
* @access public
* @category Date/Time Functions
* @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer),
* PHP DateTime object, or a standard date string
* @param integer $endDays The number of nonweekend and nonholiday days before or after
* startDate. A positive value for days yields a future date; a
* negative value yields a past date.
* @param mixed $holidays,... Optional series of Excel date serial value (float), PHP date
* timestamp (integer), PHP DateTime object, or a standard date
* strings that will be excluded from the working calendar, such
* as state and federal holidays and floating holidays.
* @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 WORKDAY($startDate, $endDays)
{
// Retrieve the mandatory start date and days that are referenced in the function definition
$startDate = Functions::flattenSingleValue($startDate);
$endDays = Functions::flattenSingleValue($endDays);
// Flush the mandatory start date and days that are referenced in the function definition, and get the optional days
$dateArgs = Functions::flattenArray(func_get_args());
array_shift($dateArgs);
array_shift($dateArgs);
if (is_string($startDate = self::getDateValue($startDate)) || !is_numeric($endDays)) {
return Functions::VALUE();
}
$startDate = (double) floor($startDate);
$endDays = (int) floor($endDays);
// If endDays is 0, we always return startDate
if ($endDays == 0) {
return $startDate;
}
$decrementing = $endDays < 0 ? true : false;
// Adjust the start date if it falls over a weekend
$startDoW = self::DAYOFWEEK($startDate, 3);
if (self::DAYOFWEEK($startDate, 3) >= 5) {
$startDate += $decrementing ? -$startDoW + 4 : 7 - $startDoW;
$decrementing ? $endDays++ : $endDays--;
}
// Add endDays
$endDate = (double) $startDate + intval($endDays / 5) * 7 + $endDays % 5;
// Adjust the calculated end date if it falls over a weekend
$endDoW = self::DAYOFWEEK($endDate, 3);
if ($endDoW >= 5) {
$endDate += $decrementing ? -$endDoW + 4 : 7 - $endDoW;
}
// Test any extra holiday parameters
if (!empty($dateArgs)) {
$holidayCountedArray = $holidayDates = array();
foreach ($dateArgs as $holidayDate) {
if ($holidayDate !== null && trim($holidayDate) > '') {
if (is_string($holidayDate = self::getDateValue($holidayDate))) {
return Functions::VALUE();
}
if (self::DAYOFWEEK($holidayDate, 3) < 5) {
$holidayDates[] = $holidayDate;
}
}
}
if ($decrementing) {
rsort($holidayDates, SORT_NUMERIC);
} else {
sort($holidayDates, SORT_NUMERIC);
}
foreach ($holidayDates as $holidayDate) {
if ($decrementing) {
if ($holidayDate <= $startDate && $holidayDate >= $endDate) {
if (!in_array($holidayDate, $holidayCountedArray)) {
--$endDate;
$holidayCountedArray[] = $holidayDate;
}
}
} else {
if ($holidayDate >= $startDate && $holidayDate <= $endDate) {
if (!in_array($holidayDate, $holidayCountedArray)) {
++$endDate;
$holidayCountedArray[] = $holidayDate;
}
}
}
// Adjust the calculated end date if it falls over a weekend
$endDoW = self::DAYOFWEEK($endDate, 3);
if ($endDoW >= 5) {
$endDate += $decrementing ? -$endDoW + 4 : 7 - $endDoW;
}
}
}
switch (Functions::getReturnDateType()) {
case Functions::RETURNDATE_EXCEL:
return (double) $endDate;
case Functions::RETURNDATE_PHP_NUMERIC:
return (int) \PHPExcel\Shared\Date::excelToPHP($endDate);
case Functions::RETURNDATE_PHP_OBJECT:
return \PHPExcel\Shared\Date::excelToPHPObject($endDate);
}
}
/**
* IMPRODUCT
*
* Returns the product of two or more complex numbers in x + yi or x + yj text format.
*
* Excel Function:
* IMPRODUCT(complexNumber[,complexNumber[,...]])
*
* @param string $complexNumber,... Series of complex numbers to multiply
* @return string
*/
public static function IMPRODUCT()
{
// Return value
$returnValue = self::parseComplex('1');
$activeSuffix = '';
// Loop through the arguments
$aArgs = Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
$parsedComplex = self::parseComplex($arg);
$workValue = $returnValue;
if ($parsedComplex['suffix'] != '' && $activeSuffix == '') {
$activeSuffix = $parsedComplex['suffix'];
} elseif ($parsedComplex['suffix'] != '' && $activeSuffix != $parsedComplex['suffix']) {
return Functions::NAN();
}
$returnValue['real'] = $workValue['real'] * $parsedComplex['real'] - $workValue['imaginary'] * $parsedComplex['imaginary'];
$returnValue['imaginary'] = $workValue['real'] * $parsedComplex['imaginary'] + $workValue['imaginary'] * $parsedComplex['real'];
}
if ($returnValue['imaginary'] == 0.0) {
$activeSuffix = '';
}
return self::COMPLEX($returnValue['real'], $returnValue['imaginary'], $activeSuffix);
}
/**
* LOGICAL_OR
*
* Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
*
* Excel Function:
* =OR(logical1[,logical2[, ...]])
*
* The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
* or references that contain logical values.
*
* 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 $arg,... Data values
* @return boolean The logical OR of the arguments.
*/
public static function logicalOr()
{
// Return value
$returnValue = false;
// Loop through the arguments
$aArgs = Functions::flattenArray(func_get_args());
$argCount = -1;
foreach ($aArgs as $argCount => $arg) {
// Is it a boolean value?
if (is_bool($arg)) {
$returnValue = $returnValue || $arg;
} elseif (is_numeric($arg) && !is_string($arg)) {
$returnValue = $returnValue || $arg != 0;
} elseif (is_string($arg)) {
$arg = strtoupper($arg);
if ($arg == 'TRUE' || $arg == \PHPExcel\Calculation::getTRUE()) {
$arg = true;
} elseif ($arg == 'FALSE' || $arg == \PHPExcel\Calculation::getFALSE()) {
$arg = false;
} else {
return Functions::VALUE();
}
$returnValue = $returnValue || $arg != 0;
}
}
// Return
if ($argCount < 0) {
return Functions::VALUE();
}
return $returnValue;
}
