/** * 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 = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value); if (!is_array($lookup_vector)) { return PHPExcel_Calculation_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); }
/** * TINV * * Returns the one-tailed probability of the chi-squared distribution. * * @param float $probability Probability for the function * @param float $degrees degrees of freedom * @return float */ public static function TINV($probability, $degrees) { $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability); $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees)); if (is_numeric($probability) && is_numeric($degrees)) { $xLo = 100; $xHi = 0; $x = $xNew = 1; $dx = 1; $i = 0; while (abs($dx) > PRECISION && $i++ < MAX_ITERATIONS) { // Apply Newton-Raphson step $result = self::TDIST($x, $degrees, 2); $error = $result - $probability; if ($error == 0.0) { $dx = 0; } elseif ($error < 0.0) { $xLo = $x; } else { $xHi = $x; } // Avoid division by zero if ($result != 0.0) { $dx = $error / $result; $xNew = $x - $dx; } // If the NR fails to converge (which for example may be the // case if the initial guess is too rough) we apply a bisection // step to determine a more narrow interval around the root. if ($xNew < $xLo || $xNew > $xHi || $result == 0.0) { $xNew = ($xLo + $xHi) / 2; $dx = $xNew - $x; } $x = $xNew; } if ($i == MAX_ITERATIONS) { return PHPExcel_Calculation_Functions::NA(); } return round($x, 12); } return PHPExcel_Calculation_Functions::VALUE(); }
public function testNA() { $result = PHPExcel_Calculation_Functions::NA(); $this->assertEquals('#N/A', $result); }
/** * 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 = PHPExcel_Calculation_Functions::flattenSingleValue($value); $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue($fromUOM); $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue($toUOM); if (!is_numeric($value)) { return PHPExcel_Calculation_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 PHPExcel_Calculation_Functions::NA(); } if (isset(self::$_conversionUnits[$fromUOM]) && self::$_conversionUnits[$fromUOM]['AllowPrefix']) { $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group']; } else { return PHPExcel_Calculation_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 PHPExcel_Calculation_Functions::NA(); } if (isset(self::$_conversionUnits[$toUOM]) && self::$_conversionUnits[$toUOM]['AllowPrefix']) { $unitGroup2 = self::$_conversionUnits[$toUOM]['Group']; } else { return PHPExcel_Calculation_Functions::NA(); } } if ($unitGroup1 != $unitGroup2) { return PHPExcel_Calculation_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; }
/** * VLOOKUP * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row 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 column number in table_array from which the matching value must be returned. The first column 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 VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) { $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value); $index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number); $not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match); // index_number must be greater than or equal to 1 if ($index_number < 1) { return PHPExcel_Calculation_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 PHPExcel_Calculation_Functions::REF(); } else { $f = array_keys($lookup_array); $firstRow = array_pop($f); if (!is_array($lookup_array[$firstRow]) || $index_number > count($lookup_array[$firstRow])) { return PHPExcel_Calculation_Functions::REF(); } else { $columnKeys = array_keys($lookup_array[$firstRow]); $returnColumn = $columnKeys[--$index_number]; $firstColumn = array_shift($columnKeys); } } if (!$not_exact_match) { uasort($lookup_array, array('self', '_vlookupSort')); } $rowNumber = $rowValue = false; foreach ($lookup_array as $rowKey => $rowData) { if (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && $rowData[$firstColumn] > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) { break; } $rowNumber = $rowKey; $rowValue = $rowData[$firstColumn]; } 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 PHPExcel_Calculation_Functions::NA(); } else { // otherwise return the appropriate value $result = $lookup_array[$rowNumber][$returnColumn]; if (is_numeric($lookup_value) && is_numeric($result) || !is_numeric($lookup_value) && !is_numeric($result)) { return $result; } } } return PHPExcel_Calculation_Functions::NA(); }
/** * MODE * * Returns the most frequently occurring, or repetitive, value in an array or range of data * * @param array of mixed Data Series * @return float */ public static function MODE() { // Return value $returnValue = PHPExcel_Calculation_Functions::NA(); // Loop through arguments $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); $mArgs = array(); foreach ($aArgs as $arg) { // Is it a numeric value? if (is_numeric($arg) && !is_string($arg)) { $mArgs[] = $arg; } } if (count($mArgs) > 0) { return self::modeCalc($mArgs); } // Return return $returnValue; }
/** * CONVERTUOM * * @param float $value * @param string $fromUOM * @param string $toUOM * @return float */ public static function CONVERTUOM($value, $fromUOM, $toUOM) { $value = PHPExcel_Calculation_Functions::flattenSingleValue ( $value ); $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $fromUOM ); $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $toUOM ); if (! is_numeric ( $value )) { return PHPExcel_Calculation_Functions::VALUE (); } $fromMultiplier = 1; 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 PHPExcel_Calculation_Functions::NA (); } if ((isset ( self::$_conversionUnits [$fromUOM] )) && (self::$_conversionUnits [$fromUOM] ['AllowPrefix'])) { $unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group']; } else { return PHPExcel_Calculation_Functions::NA (); } } $value *= $fromMultiplier; $toMultiplier = 1; 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 PHPExcel_Calculation_Functions::NA (); } if ((isset ( self::$_conversionUnits [$toUOM] )) && (self::$_conversionUnits [$toUOM] ['AllowPrefix'])) { $unitGroup2 = self::$_conversionUnits [$toUOM] ['Group']; } else { return PHPExcel_Calculation_Functions::NA (); } } if ($unitGroup1 != $unitGroup2) { return PHPExcel_Calculation_Functions::NA (); } if ($fromUOM == $toUOM) { return 1.0; } elseif ($unitGroup1 == 'Temperature') { if (($fromUOM == 'F') || ($fromUOM == 'fah')) { if (($toUOM == 'F') || ($toUOM == 'fah')) { return 1.0; } 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 1.0; } elseif ((($fromUOM == 'C') || ($fromUOM == 'cel')) && (($toUOM == 'C') || ($toUOM == 'cel'))) { return 1.0; } 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; } // function CONVERTUOM()