/**
* MINIF
*
* Returns the minimum value within a range of cells that contain numbers within the list of arguments
*
* Excel Function:
* MINIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be checked.
* @return float
*/
public static function MINIF($aArgs, $condition, $sumArgs = array())
{
// Return value
$returnValue = null;
$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
$sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
if (count($sumArgs) == 0) {
$sumArgs = $aArgs;
}
$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
if (is_null($returnValue) || $arg < $returnValue) {
$returnValue = $arg;
}
}
}
// Return
return $returnValue;
}
/**
* SUMIF
*
* Counts the number of cells that contain numbers within the list of arguments
*
* Excel Function:
* SUMIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be summed.
* @return float
*/
public static function SUMIF($aArgs, $condition, $sumArgs = array())
{
// Return value
$returnValue = 0;
$aArgs = self::flattenArray($aArgs);
$sumArgs = self::flattenArray($sumArgs);
if (count($sumArgs) == 0) {
$sumArgs = $aArgs;
}
if (!in_array($condition[0], array('>', '<', '='))) {
if (!is_numeric($condition)) {
$condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition));
}
$condition = '=' . $condition;
}
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
// Is it a value within our criteria
$returnValue += $sumArgs[$key];
}
}
// Return
return $returnValue;
}
/**
* __filter
*
* Parses the selection criteria, extracts the database rows that match those criteria, and
* returns that subset of rows.
*
* @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[] $criteria The range of cells that contains the conditions you specify.
* You can use any range for the criteria argument, as long as it
* includes at least one column label and at least one cell below
* the column label in which you specify a condition for the
* column.
* @return array of mixed
*
*/
private static function __filter($database, $criteria)
{
$fieldNames = array_shift($database);
$criteriaNames = array_shift($criteria);
// Convert the criteria into a set of AND/OR conditions with [:placeholders]
$testConditions = $testValues = array();
$testConditionsCount = 0;
foreach ($criteriaNames as $key => $criteriaName) {
$testCondition = array();
$testConditionCount = 0;
foreach ($criteria as $row => $criterion) {
if ($criterion[$key] > '') {
$testCondition[] = '[:' . $criteriaName . ']' . PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]);
$testConditionCount++;
}
}
if ($testConditionCount > 1) {
$testConditions[] = 'OR(' . implode(',', $testCondition) . ')';
$testConditionsCount++;
} elseif ($testConditionCount == 1) {
$testConditions[] = $testCondition[0];
$testConditionsCount++;
}
}
if ($testConditionsCount > 1) {
$testConditionSet = 'AND(' . implode(',', $testConditions) . ')';
} elseif ($testConditionsCount == 1) {
$testConditionSet = $testConditions[0];
}
// Loop through each row of the database
foreach ($database as $dataRow => $dataValues) {
// Substitute actual values from the database row for our [:placeholders]
$testConditionList = $testConditionSet;
foreach ($criteriaNames as $key => $criteriaName) {
$k = array_search($criteriaName, $fieldNames);
if (isset($dataValues[$k])) {
$dataValue = $dataValues[$k];
$dataValue = is_string($dataValue) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue;
$testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList);
}
}
// evaluate the criteria against the row data
$result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList);
// If the row failed to meet the criteria, remove it from the database
if (!$result) {
unset($database[$dataRow]);
}
}
return $database;
}
/**
* AVERAGEIF
*
* Returns the average value from a range of cells that contain numbers within the list of arguments
*
* Excel Function:
* AVERAGEIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be checked.
* @return float
*/
public static function AVERAGEIF($aArgs, $condition, $averageArgs = array())
{
// Return value
$returnValue = 0;
$aArgs = self::flattenArray($aArgs);
$averageArgs = self::flattenArray($averageArgs);
if (count($averageArgs) == 0) {
$averageArgs = $aArgs;
}
$condition = self::_ifCondition($condition);
// Loop through arguments
$aCount = 0;
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
if (is_null($returnValue) || $arg > $returnValue) {
$returnValue += $arg;
++$aCount;
}
}
}
// Return
if ($aCount > 0) {
return $returnValue / $aCount;
} else {
return self::$_errorCodes['divisionbyzero'];
}
}
public static function _ifCondition($condition)
{
$condition = PHPExcel_Calculation_Functions::flattenSingleValue($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 = PHPExcel_Calculation::_wrapResult(strtoupper($operand));
}
return $operator . $operand;
}
}
/**
* SUMIF
*
* Counts the number of cells that contain numbers within the list of arguments
*
* Excel Function:
* SUMIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be summed.
* @return float
*/
public static function SUMIF($aArgs, $condition, $sumArgs = array())
{
// Return value
$returnValue = 0;
$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
$sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
if (empty($sumArgs)) {
$sumArgs = $aArgs;
}
$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = str_replace('"', '""', $arg);
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
// Is it a value within our criteria
$returnValue += $sumArgs[$key];
}
}
// Return
return $returnValue;
}
