/**
* Once the binary search has found a single match, (V/H)LOOKUP steps one by one over subsequent
* values to choose the last matching item.
*/
private static int FindLastIndexInRunOfEqualValues(LookupValueComparer lookupComparer, ValueVector vector,
int firstFoundIndex, int maxIx)
{
for (int i = firstFoundIndex + 1; i < maxIx; i++)
{
if (!lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return i - 1;
}
}
return maxIx - 1;
}
private double EvaluateInternal(ValueVector x, ValueVector y, int size)
{
// error handling is as if the x is fully Evaluated before y
ErrorEval firstXerr = null;
ErrorEval firstYerr = null;
bool accumlatedSome = false;
// first pass: read in data, compute xbar and ybar
double sumx = 0.0, sumy = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
accumlatedSome = true;
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
sumx += nx.NumberValue;
sumy += ny.NumberValue;
}
else
{
// all other combinations of value types are silently ignored
}
}
double xbar = sumx / size;
double ybar = sumy / size;
// second pass: compute summary statistics
double xxbar = 0.0, xybar = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
xxbar += (nx.NumberValue - xbar) * (nx.NumberValue - xbar);
xybar += (nx.NumberValue - xbar) * (ny.NumberValue - ybar);
}
else
{
// all other combinations of value types are silently ignored
}
}
double beta1 = xybar / xxbar;
double beta0 = ybar - beta1 * xbar;
if (firstXerr != null)
{
throw new EvaluationException(firstXerr);
}
if (firstYerr != null)
{
throw new EvaluationException(firstYerr);
}
if (!accumlatedSome)
{
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
if (function == FUNCTION.INTERCEPT)
{
return beta0;
}
else
{
return beta1;
}
}
/**
* Excel has funny behaviour when the some elements in the search vector are the wrong type.
*
*/
private static int PerformBinarySearch(ValueVector vector, LookupValueComparer lookupComparer)
{
// both low and high indexes point to values assumed too low and too high.
BinarySearchIndexes bsi = new BinarySearchIndexes(vector.Size);
while (true)
{
int midIx = bsi.GetMidIx();
if (midIx < 0)
{
return bsi.GetLowIx();
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(midIx));
if (cr.IsTypeMismatch)
{
int newMidIx = HandleMidValueTypeMismatch(lookupComparer, vector, bsi, midIx);
if (newMidIx < 0)
{
continue;
}
midIx = newMidIx;
cr = lookupComparer.CompareTo(vector.GetItem(midIx));
}
if (cr.IsEqual)
{
return FindLastIndexInRunOfEqualValues(lookupComparer, vector, midIx, bsi.GetHighIx());
}
bsi.narrowSearch(midIx, cr.IsLessThan);
}
}
/**
* Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the
* first compatible value.
* @param midIx 'mid' index (value which has the wrong type)
* @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid
* index. Zero or greater signifies that an exact match for the lookup value was found
*/
private static int HandleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector,
BinarySearchIndexes bsi, int midIx)
{
int newMid = midIx;
int highIx = bsi.GetHighIx();
while (true)
{
newMid++;
if (newMid == highIx)
{
// every element from midIx to highIx was the wrong type
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return -1;
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(newMid));
if (cr.IsLessThan && newMid == highIx - 1)
{
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return -1;
// but only when "newMid == highIx-1"? slightly weird.
// It would seem more efficient to always do this.
}
if (cr.IsTypeMismatch)
{
// keep stepping over values Until the right type Is found
continue;
}
if (cr.IsEqual)
{
return newMid;
}
// Note - if moving highIx down (due to lookup<vector[newMid]),
// this execution path only moves highIx it down as far as newMid, not midIx,
// which would be more efficient.
bsi.narrowSearch(newMid, cr.IsLessThan);
return -1;
}
}
public static int LookupIndexOfValue(ValueEval lookupValue, ValueVector vector, bool IsRangeLookup)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue);
int result;
if (IsRangeLookup)
{
result = PerformBinarySearch(vector, lookupComparer);
}
else
{
result = LookupIndexOfExactValue(lookupComparer, vector);
}
if (result < 0)
{
throw new EvaluationException(ErrorEval.NA);
}
return result;
}
/**
* Finds first (lowest index) exact occurrence of specified value.
* @param lookupValue the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this Is the first column of the
* tableArray. For HLOOKUP this Is the first row of the tableArray.
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int LookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector)
{
// Find first occurrence of lookup value
int size = vector.Size;
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return i;
}
}
return -1;
}
/**
* @return zero based index
*/
private static int FindIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
bool matchExact, bool FindLargestLessThanOrEqual)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, matchExact);
if (matchExact)
{
for (int i = 0; i < lookupRange.Size; i++)
{
if (lookupComparer.CompareTo(lookupRange.GetItem(i)).IsEqual)
{
return i;
}
}
throw new EvaluationException(ErrorEval.NA);
}
if (FindLargestLessThanOrEqual)
{
// Note - backward iteration
for (int i = lookupRange.Size - 1; i >= 0; i--)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsTypeMismatch)
{
continue;
}
if (!cmp.IsLessThan)
{
return i;
}
}
throw new EvaluationException(ErrorEval.NA);
}
// else - Find smallest greater than or equal to
// TODO - Is binary search used for (match_type==+1) ?
for (int i = 0; i < lookupRange.Size; i++)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsEqual)
{
return i;
}
if (cmp.IsGreaterThan)
{
if (i < 1)
{
throw new EvaluationException(ErrorEval.NA);
}
return i - 1;
}
}
throw new EvaluationException(ErrorEval.NA);
}
private double EvaluateInternal(ValueVector x, ValueVector y, int size)
{
Accumulator acc = CreateAccumulator();
// error handling is as if the x is fully evaluated before y
ErrorEval firstXerr = null;
ErrorEval firstYerr = null;
bool accumlatedSome = false;
double result = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
accumlatedSome = true;
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
result += acc.Accumulate(nx.NumberValue, ny.NumberValue);
}
else
{
// all other combinations of value types are silently ignored
}
}
if (firstXerr != null)
{
throw new EvaluationException(firstXerr);
}
if (firstYerr != null)
{
throw new EvaluationException(firstYerr);
}
if (!accumlatedSome)
{
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
return result;
}
/**
* Once the binary search has found a single match, (V/H)LOOKUP steps one by one over subsequent
* values to choose the last matching item.
*/
private static int FindLastIndexInRunOfEqualValues(LookupValueComparer lookupComparer, ValueVector vector,
int firstFoundIndex, int maxIx)
{
for (int i = firstFoundIndex + 1; i < maxIx; i++)
{
if (!lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return(i - 1);
}
}
return(maxIx - 1);
}
/**
* Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the
* first compatible value.
* @param midIx 'mid' index (value which has the wrong type)
* @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid
* index. Zero or greater signifies that an exact match for the lookup value was found
*/
private static int HandleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector,
BinarySearchIndexes bsi, int midIx)
{
int newMid = midIx;
int highIx = bsi.GetHighIx();
while (true)
{
newMid++;
if (newMid == highIx)
{
// every element from midIx to highIx was the wrong type
// move highIx down to the low end of the mid values
bsi.NarrowSearch(midIx, true);
return(-1);
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(newMid));
if (cr.IsLessThan && newMid == highIx - 1)
{
// move highIx down to the low end of the mid values
bsi.NarrowSearch(midIx, true);
return(-1);
// but only when "newMid == highIx-1"? slightly weird.
// It would seem more efficient to always do this.
}
if (cr.IsTypeMismatch)
{
// keep stepping over values Until the right type Is found
continue;
}
if (cr.IsEqual)
{
return(newMid);
}
// Note - if moving highIx down (due to lookup<vector[newMid]),
// this execution path only moves highIx it down as far as newMid, not midIx,
// which would be more efficient.
bsi.NarrowSearch(newMid, cr.IsLessThan);
return(-1);
}
}
/**
* Finds first (lowest index) exact occurrence of specified value.
* @param lookupComparer the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this Is the first column of the
* tableArray. For HLOOKUP this Is the first row of the tableArray.
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int LookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector)
{
// Find first occurrence of lookup value
int size = vector.Size;
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return(i);
}
}
return(-1);
}
