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