private static object SumIf(List <Expression> p)
{
// get parameters
var range = p[0] as IEnumerable; // range of values to match the criteria against
var sumRange = p.Count < 3 ?
p[0] as XObjectExpression :
p[2] as XObjectExpression; // range of values to sum up
var criteria = p[1].Evaluate(); // the criteria to evaluate
var rangeValues = range.Cast <object>().ToList();
var sumRangeValues = sumRange.Cast <object>().ToList();
// compute total
var ce = new CalcEngine();
var tally = new Tally();
for (var i = 0; i < Math.Max(rangeValues.Count, sumRangeValues.Count); i++)
{
var targetValue = i < rangeValues.Count ? rangeValues[i] : string.Empty;
if (CalcEngineHelpers.ValueSatisfiesCriteria(targetValue, criteria, ce))
{
var value = i < sumRangeValues.Count ? sumRangeValues[i] : 0d;
tally.AddValue(value);
}
}
// done
return(tally.Sum());
}
private static object CountIf(List <Expression> p)
{
CalcEngine ce = new CalcEngine();
var cnt = 0.0;
long processedCount = 0;
if (p[0] is XObjectExpression ienum)
{
long totalCount = CalcEngineHelpers.GetTotalCellsCount(ienum);
var criteria = p[1].Evaluate();
foreach (var value in ienum)
{
if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce))
{
cnt++;
}
processedCount++;
}
// Add count of empty cells outside the used range if they match criteria
if (CalcEngineHelpers.ValueSatisfiesCriteria(string.Empty, criteria, ce))
{
cnt += (totalCount - processedCount);
}
}
return(cnt);
}
private static object SumIfs(List <Expression> p)
{
// get parameters
var sumRange = p[0] as IEnumerable;
var sumRangeValues = new List <object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
var ce = new CalcEngine();
var tally = new Tally();
int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want.
// prepare criteria-parameters:
var criteriaRanges = new Tuple <object, List <object> > [numberOfCriteria];
for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++)
{
var criterion = p[criteriaPair * 2 + 1].Evaluate();
var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable;
var criteriaRangeValues = new List <object>();
foreach (var value in criteriaRange)
{
criteriaRangeValues.Add(value);
}
criteriaRanges[criteriaPair] = new Tuple <object, List <object> >(
criterion,
criteriaRangeValues);
}
for (var i = 0; i < sumRangeValues.Count; i++)
{
bool shouldUseValue = true;
foreach (var criteriaPair in criteriaRanges)
{
if (!CalcEngineHelpers.ValueSatisfiesCriteria(
criteriaPair.Item2[i],
criteriaPair.Item1,
ce))
{
shouldUseValue = false;
break; // we're done with the inner loop as we can't ever get true again.
}
}
if (shouldUseValue)
{
tally.AddValue(sumRangeValues[i]);
}
}
// done
return(tally.Sum());
}
private static object CountIfs(List <Expression> p)
{
// get parameters
var ce = new CalcEngine();
long count = 0;
int numberOfCriteria = p.Count / 2;
long totalCount = 0;
// prepare criteria-parameters:
var criteriaRanges = new Tuple <object, List <object> > [numberOfCriteria];
for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++)
{
var criteriaRange = p[criteriaPair * 2] as XObjectExpression;
var criterion = p[(criteriaPair * 2) + 1].Evaluate();
var criteriaRangeValues = new List <object>();
foreach (var value in criteriaRange)
{
criteriaRangeValues.Add(value);
}
criteriaRanges[criteriaPair] = new Tuple <object, List <object> >(
criterion,
criteriaRangeValues);
if (totalCount == 0)
{
totalCount = CalcEngineHelpers.GetTotalCellsCount(criteriaRange);
}
}
long processedCount = 0;
for (var i = 0; i < criteriaRanges[0].Item2.Count; i++)
{
if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria(
criteriaPair.Item2[i], criteriaPair.Item1, ce)))
{
count++;
}
processedCount++;
}
// Add count of empty cells outside the used range if they match criteria
if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria(
string.Empty, criteriaPair.Item1, ce)))
{
count += (totalCount - processedCount);
}
// done
return(count);
}
public double Count(bool numbersOnly)
{
if (numbersOnly)
{
return(NumericValuesInternal().Length);
}
else
{
return(_list.Count(o => !CalcEngineHelpers.ValueIsBlank(o)));
}
}
private static object CountIfs(List <Expression> p)
{
// get parameters
var ce = new CalcEngine();
int count = 0;
int numberOfCriteria = p.Count / 2;
// prepare criteria-parameters:
var criteriaRanges = new Tuple <object, List <object> > [numberOfCriteria];
for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++)
{
var criteriaRange = p[criteriaPair * 2] as IEnumerable;
var criterion = p[(criteriaPair * 2) + 1].Evaluate();
var criteriaRangeValues = new List <object>();
foreach (var value in criteriaRange)
{
criteriaRangeValues.Add(value);
}
criteriaRanges[criteriaPair] = new Tuple <object, List <object> >(
criterion,
criteriaRangeValues);
}
for (var i = 0; i < criteriaRanges[0].Item2.Count; i++)
{
bool shouldUseValue = true;
foreach (var criteriaPair in criteriaRanges)
{
if (!CalcEngineHelpers.ValueSatisfiesCriteria(
criteriaPair.Item2[i],
criteriaPair.Item1,
ce))
{
shouldUseValue = false;
break; // we're done with the inner loop as we can't ever get true again.
}
}
if (shouldUseValue)
{
count++;
}
}
// done
return(count);
}
static object CountIf(List <Expression> p)
{
CalcEngine ce = new CalcEngine();
var cnt = 0.0;
var ienum = p[0] as IEnumerable;
if (ienum != null)
{
var criteria = (string)p[1].Evaluate();
foreach (var value in ienum)
{
if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce))
{
cnt++;
}
}
}
return(cnt);
}
private static object CountBlank(List <Expression> p)
{
if ((p[0] as XObjectExpression)?.Value as CellRangeReference == null)
{
throw new NoValueAvailableException("COUNTBLANK should have a single argument which is a range reference");
}
var e = p[0] as XObjectExpression;
long totalCount = CalcEngineHelpers.GetTotalCellsCount(e);
long nonBlankCount = 0;
foreach (var value in e)
{
if (!CalcEngineHelpers.ValueIsBlank(value))
{
nonBlankCount++;
}
}
return(0d + totalCount - nonBlankCount);
}
private static object SumIf(List <Expression> p)
{
// get parameters
var range = p[0] as IEnumerable;
var sumRange = p.Count < 3 ? range : p[2] as IEnumerable;
var criteria = p[1].Evaluate();
// build list of values in range and sumRange
var rangeValues = new List <object>();
foreach (var value in range)
{
rangeValues.Add(value);
}
var sumRangeValues = new List <object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
// compute total
var ce = new CalcEngine();
var tally = new Tally();
for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++)
{
if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce))
{
tally.AddValue(sumRangeValues[i]);
}
}
// done
return(tally.Sum());
}