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