private double CalculateWithLookupRange(ExcelDataProvider.IRangeInfo range, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context)
{
var retVal = 0d;
var nMatches = 0;
foreach (var cell in range)
{
if (criteria != null && Evaluate(cell.Value, criteria))
{
var or = cell.Row - range.Address._fromRow;
var oc = cell.Column - range.Address._fromCol;
if (sumRange.Address._fromRow + or <= sumRange.Address._toRow &&
sumRange.Address._fromCol + oc <= sumRange.Address._toCol)
{
var v = sumRange.GetOffset(or, oc);
if (v is ExcelErrorValue)
{
throw (new ExcelErrorValueException((ExcelErrorValue)v));
}
nMatches++;
retVal += ConvertUtil.GetValueDouble(v, true);
}
}
}
return(Divide(retVal, nMatches));
}
private double CalculateWithLookupRange(ExcelDataProvider.IRangeInfo argRange, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context)
{
var returnValue = 0d;
var nMatches = 0;
foreach (var cell in argRange)
{
if (criteria != null && Evaluate(cell.Value, criteria))
{
var rowOffset = cell.Row - argRange.Address._fromRow;
var columnOffset = cell.Column - argRange.Address._fromCol;
if (sumRange.Address._fromRow + rowOffset <= sumRange.Address._toRow &&
sumRange.Address._fromCol + columnOffset <= sumRange.Address._toCol)
{
var val = sumRange.GetOffset(rowOffset, columnOffset);
if (val is ExcelErrorValue)
{
ThrowExcelErrorValueException(((ExcelErrorValue)val));
}
nMatches++;
returnValue += ConvertUtil.GetValueDouble(val, true);
}
}
}
return(Divide(returnValue, nMatches));
}
/// <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));
}
}
private double CalculateWithSumRange(ExcelDataProvider.IRangeInfo range, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context)
{
var retVal = 0d;
foreach (var cell in range)
{
if (criteria != default(string) && _evaluator.Evaluate(cell.Value, criteria))
{
var rowOffset = cell.Row - range.Address._fromRow;
var columnOffset = cell.Column - range.Address._fromCol;
if (sumRange.Address._fromRow + rowOffset <= sumRange.Address._toRow &&
sumRange.Address._fromCol + columnOffset <= sumRange.Address._toCol)
{
var val = sumRange.GetOffset(rowOffset, columnOffset);
if (val is ExcelErrorValue)
{
ThrowExcelErrorValueException((ExcelErrorValue)val);
}
retVal += ConvertUtil.GetValueDouble(val, true);
}
}
}
return(retVal);
}
