/// <summary>
/// Returns the average (arithmetic mean) of all the cells in a range that meet a given criterion.
/// </summary>
/// <param name="arguments">The arguments used to calculate the average.</param>
/// <param name="context">The context for the function.</param>
/// <returns>Returns the average of all cells in the given range that passed the given criterion.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentCountIsValid(arguments, 2) == false)
{
return(new CompileResult(eErrorType.Value));
}
var cellRangeToCheck = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;
if (cellRangeToCheck == null)
{
return(new CompileResult(eErrorType.Value));
}
var criterionObject = IfHelper.ExtractCriterionObject(arguments.ElementAt(1), context);
if (arguments.Count() > 2)
{
var cellRangeToAverage = arguments.ElementAt(2).Value as ExcelDataProvider.IRangeInfo;
if (cellRangeToAverage == null)
{
return(new CompileResult(eErrorType.Value));
}
else
{
return(this.CalculateAverageUsingAverageRange(cellRangeToCheck, criterionObject, cellRangeToAverage));
}
}
else
{
return(this.CalculateAverageUsingRange(cellRangeToCheck, criterionObject));
}
}
/// <summary>
/// Returns the sum of all cells that meet multiple criteria.
/// </summary>
/// <param name="arguments">The arguments used to calculate the sum.</param>
/// <param name="context">The context for the function.</param>
/// <returns>Returns the sum of all cells in the given range that pass the given criteria.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (!this.ArgumentsAreValid(arguments, 3, out eErrorType errorType))
{
return(new CompileResult(errorType));
}
var sumRange = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;
if (sumRange == null)
{
return(new CompileResult(0d, DataType.Decimal));
}
var indicesOfValidCells = new List <int>();
for (var argumentIndex = 1; argumentIndex < arguments.Count(); argumentIndex += 2)
{
var currentRangeToCompare = arguments.ElementAt(argumentIndex).ValueAsRangeInfo;
if (currentRangeToCompare == null || !IfHelper.RangesAreTheSameShape(sumRange, currentRangeToCompare))
{
return(new CompileResult(eErrorType.Value));
}
var currentCriterion = IfHelper.ExtractCriterionObject(arguments.ElementAt(argumentIndex + 1), context);
// This will always look at every cell in the given range of cells to compare. This is done instead of
// using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
// does not include empty cells that have not been set since the workbook's creation. This function
// wants to consider empty cells for comparing with the criterion, but it can be better optimized.
// A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, CountIf, and CountIfs functions.
var passingIndices = IfHelper.GetIndicesOfCellsPassingCriterion(currentRangeToCompare, currentCriterion);
if (argumentIndex == 1)
{
indicesOfValidCells = passingIndices;
}
else
{
indicesOfValidCells = indicesOfValidCells.Intersect(passingIndices).ToList();
}
}
double sumOfValidValues = 0d;
if (sumRange.Count() > 0)
{
// Again, all cells, including empty cells, need to be available here.
// The IRangeInfo will only provide non-empty cells.
var allSumValues = sumRange.AllValues();
foreach (var cellIndex in indicesOfValidCells)
{
var currentCellValue = allSumValues.ElementAt(cellIndex);
if (currentCellValue is ExcelErrorValue cellError)
{
return(new CompileResult(cellError.Type));
}
else if (ConvertUtil.IsNumeric(currentCellValue, true))
{
sumOfValidValues += ConvertUtil.GetValueDouble(currentCellValue);
}
}
}
return(this.CreateResult(sumOfValidValues, DataType.Decimal));
}
/// <summary>
/// Compares the given <paramref name="testObject"/> against the given <paramref name="rawCriterionObject"/>.
/// This method is expected to be used with any of the *IF or *IFS Excel functions (ex: the AVERAGEIF function)
/// for comparing an object against a criterion. See the documentation for the any of the *IF or *IFS functions
/// for information on acceptable forms of the criterion.
/// </summary>
/// <param name="testObject">The object to compare against the given <paramref name="rawCriterionObject"/>.</param>
/// <param name="rawCriterionObject">The criterion value or expression that dictates whether the given <paramref name="testObject"/> passes or fails.</param>
/// <returns>Returns true if <paramref name="testObject"/> matches the <paramref name="rawCriterionObject"/>.</returns>
public static bool ObjectMatchesCriterion(object testObject, object rawCriterionObject)
{
object criterion = rawCriterionObject;
OperatorType criterionOperator = OperatorType.Equals;
bool criterionIsExpression = false;
if (rawCriterionObject is string rawCriterionString)
{
string criterionString;
if (IfHelper.TryParseCriterionAsExpression(rawCriterionString, out IOperator expressionOperator, out string expressionCriterion))
{
criterionOperator = expressionOperator.Operator;
criterionString = expressionCriterion;
criterionIsExpression = true;
}
else
{
criterionString = rawCriterionString.ToUpper(CultureInfo.CurrentCulture);
}
if (IfHelper.TryParseCriterionStringToObject(criterionString, out object criterionObject))
{
criterion = criterionObject;
}
else
{
criterion = criterionString;
}
}
/// <summary>
/// Calculates the average value of all cells that match the given criterion. The sizes/shapes of
/// <paramref name="cellsToCompare"/> and <paramref name="potentialCellsToAverage"/> do not have to be the same;
/// The size and shape of <paramref name="cellsToCompare"/> is applied to <paramref name="potentialCellsToAverage"/>,
/// using the first cell in <paramref name="potentialCellsToAverage"/> as a reference point.
/// </summary>
/// <param name="cellsToCompare">The range of cells to compare against the <paramref name="comparisonCriterion"/>.</param>
/// <param name="comparisonCriterion">The criterion dictating which cells should be included in the average calculation.</param>
/// <param name="potentialCellsToAverage">
/// If a cell in <paramref name="cellsToCompare"/> passes the criterion, then its
/// corresponding cell in this cell range will be included in the average calculation.</param>
/// <returns>Returns the average for all cells that pass the <paramref name="comparisonCriterion"/>.</returns>
private CompileResult CalculateAverageUsingAverageRange(ExcelDataProvider.IRangeInfo cellsToCompare, object comparisonCriterion, ExcelDataProvider.IRangeInfo potentialCellsToAverage)
{
var sumOfValidValues = 0d;
var numberOfValidValues = 0;
var startingRowForComparison = cellsToCompare.Address._fromRow;
var startingColumnForComparison = cellsToCompare.Address._fromCol;
var endingRowForComparison = cellsToCompare.Address._toRow;
var endingColumnForComparison = cellsToCompare.Address._toCol;
// This will always look at every cell in the given range of cells to compare. This is done instead of
// using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
// does not include empty cells that have not been set since the workbook's creation. This function
// wants to consider empty cells for comparing with the criterion, but it can be better optimized.
// A similar problem and optimization opportunity exists in the AverageIfs, SumIf, SumIfs, CountIf, and CountIfs functions.
for (var currentRow = startingRowForComparison; currentRow <= endingRowForComparison; currentRow++)
{
for (var currentColumn = startingColumnForComparison; currentColumn <= endingColumnForComparison; currentColumn++)
{
var currentCellValue = this.GetFirstArgument(cellsToCompare.GetValue(currentRow, currentColumn));
if (IfHelper.ObjectMatchesCriterion(currentCellValue, comparisonCriterion))
{
var relativeRow = currentRow - startingRowForComparison;
var relativeColumn = currentColumn - startingColumnForComparison;
var valueOfCellToAverage = potentialCellsToAverage.GetOffset(relativeRow, relativeColumn);
if (valueOfCellToAverage is ExcelErrorValue cellError)
{
return(new CompileResult(cellError.Type));
}
else if (ConvertUtil.IsNumeric(valueOfCellToAverage, true))
{
sumOfValidValues += ConvertUtil.GetValueDouble(valueOfCellToAverage);
numberOfValidValues++;
}
}
}
}
if (numberOfValidValues == 0)
{
return(new CompileResult(eErrorType.Div0));
}
else
{
return(this.CreateResult(sumOfValidValues / numberOfValidValues, DataType.Decimal));
}
}
/// <summary>
/// This function applies criteria to cells across multiple ranges and counts the number of times
/// all criteria are met. If multiple cell ranges are being compared against criteria, all ranges must
/// have the same number of rows and columns as the first given range, but the ranges do not have to be
/// adjacent to each other.
/// </summary>
/// <param name="arguments">The arguments being evaluated.</param>
/// <param name="context">The context for this function.</param>
/// <returns>Returns the number of times all criteria are met across a row of cells.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentCountIsValid(arguments, 2) == false)
{
return(new CompileResult(eErrorType.Value));
}
var firstRangeToCompare = arguments.ElementAt(0).ValueAsRangeInfo;
if (firstRangeToCompare == null)
{
return(new CompileResult(eErrorType.Value));
}
var indicesOfValidCells = new List <int>();
for (var argumentIndex = 0; argumentIndex < arguments.Count(); argumentIndex += 2)
{
var currentRangeToCompare = arguments.ElementAt(argumentIndex).ValueAsRangeInfo;
if (currentRangeToCompare == null || !IfHelper.RangesAreTheSameShape(firstRangeToCompare, currentRangeToCompare))
{
return(new CompileResult(eErrorType.Value));
}
var currentCriterion = IfHelper.ExtractCriterionObject(arguments.ElementAt(argumentIndex + 1), context);
// This will always look at every cell in the given range of cells to compare. This is done instead of
// using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
// does not include empty cells that have not been set since the workbook's creation. This function
// wants to consider empty cells for comparing with the criterion, but it can be better optimized.
// A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, SumIfs, and CountIf functions.
var passingIndices = IfHelper.GetIndicesOfCellsPassingCriterion(currentRangeToCompare, currentCriterion);
if (argumentIndex == 0)
{
indicesOfValidCells = passingIndices;
}
else
{
indicesOfValidCells = indicesOfValidCells.Intersect(passingIndices).ToList();
}
}
double count = indicesOfValidCells.Count();
return(this.CreateResult(count, DataType.Integer));
}
/// <summary>
/// Count the number of cells in a cell range that meet a criterion.
/// </summary>
/// <param name="arguments">The arguments used to calculate the number of valid cells.</param>
/// <param name="context">The context for this function.</param>
/// <returns>Returns the number of cells in the cell range that meet the criterion.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentCountIsValid(arguments, 2) == false)
{
return(new CompileResult(eErrorType.Value));
}
var cellRangeToCheck = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;
if (cellRangeToCheck == null)
{
return(new CompileResult(eErrorType.Value));
}
// This will always look at every cell in the given range of cells to compare. This is done instead of
// using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
// does not include empty cells that have not been set since the workbook's creation. This function
// wants to consider empty cells for comparing with the criterion, but it can be better optimized.
// A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, SumIfs, and CountIfs functions.
var criteriaObject = IfHelper.ExtractCriterionObject(arguments.ElementAt(1), context);
double count = cellRangeToCheck.AllValues().Where(cellValue => IfHelper.ObjectMatchesCriterion(cellValue, criteriaObject)).Count();
return(this.CreateResult(count, DataType.Integer));
}
/// <summary>
/// Calculates the average value of all cells that match the given criterion.
/// </summary>
/// <param name="potentialCellsToAverage">
/// The cell range to compare against the given <paramref name="comparisonCriterion"/>
/// If a cell passes the criterion, then its value is included in the average calculation.</param>
/// <param name="comparisonCriterion">The criterion dictating which cells should be included in the average calculation.</param>
/// <returns>Returns the average value for all cells that pass the <paramref name="comparisonCriterion"/>.</returns>
private CompileResult CalculateAverageUsingRange(ExcelDataProvider.IRangeInfo potentialCellsToAverage, object comparisonCriterion)
{
var sumOfValidValues = 0d;
var numberOfValidValues = 0;
var valuesToAverage = potentialCellsToAverage.Select(cell => this.GetFirstArgument(cell.Value)).Where(cellValue => IfHelper.ObjectMatchesCriterion(cellValue, comparisonCriterion));
foreach (var value in valuesToAverage)
{
if (value is ExcelErrorValue cellErrorValue)
{
return(new CompileResult(cellErrorValue.Type));
}
else if (ConvertUtil.IsNumeric(value, true))
{
sumOfValidValues += ConvertUtil.GetValueDouble(value);
numberOfValidValues++;
}
}
if (numberOfValidValues == 0)
{
return(new CompileResult(eErrorType.Div0));
}
else
{
return(this.CreateResult(sumOfValidValues / numberOfValidValues, DataType.Decimal));
}
}