Defined name as the first operand of a comparison against a range is read before its target cell is calculated (stale result on the first Calculate())

[smad2005]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

8.7.0

Spreadsheet application

Excel

Description

When a defined name that points to a formula cell is used as the first (left) operand of a comparison whose second operand is a range (LIMIT>=B1:B3), the cell the name points to is never added to the dependency chain. The first Calculate() reads that cell before it has been calculated, so the formula returns a wrong result (#CALC! for FILTER, 0 for SUMPRODUCT). A second Calculate() returns the correct value only because the target cell already holds the value that was assigned late in the first pass — the ordering itself is never fixed, it just stops mattering.

Swapping the operands (B1:B3<=LIMIT) calculates correctly on the first pass, so the defect depends on nothing but operand order. Present at least since 8.4.1 and still in 8.7.0.

Root Cause: In RpnFormulaExecution.cs, case TokenType.NameValue of ExecuteNextToken returns the name's address as a dependency only if IsSingleAddress is true:

case TokenType.NameValue:
    var ne = (NamedValueExpression)f._expressions[f._tokenIndex];
    s.Push(ne);
    if (ne._name != null)
    {
        var nameAddress = ne.GetAddress();
        // ...
        else if (returnAddresses && (f._funcStack.Count == 0 || ShouldIgnoreAddress(f._funcStack.Peek()) == false))
        {
            if (IsSingleAddress(f))
            {
                return nameAddress;
            }
        }
    }
    break;

IsSingleAddress scans forward while the tokens are address tokens and bails out on the : operator, which Token.TokenTypeIsAddressToken counts as an address token:

private static bool IsSingleAddress(RpnFormula f)
{
    var t = f._tokenIndex + 1;
    while (t < f._tokens.Count && f._tokens[t].TokenTypeIsAddressToken)
    {
        if (f._tokens[t].TokenType == TokenType.Operator && f._tokens[t].Value == ":")
        {
            return false;
        }
        t++;
    }
    return true;
}

The token list is in RPN order, so LIMIT>=B1:B3 is tokenized as LIMIT, B1, B3, :, >=. The look-ahead from LIMIT walks over B1 and B3 — tokens that belong to the next operand, not to the name — and hits that operand's :. IsSingleAddress returns false, the name's address is never returned as a dependency, and the fallback that executes the name formula (the ExpressionType.NameValue branch in CalculateFormulaChain) no longer applies because _tokenIndex has already moved past the name token. The name is therefore compiled from whatever the target cell currently contains.

For B1:B3<=LIMIT the token order is B1, B3, :, LIMIT, <=; the token after LIMIT is <=, which is not an address token, so the dependency is registered and the first Calculate() is correct.

Not FILTER specific — SUMPRODUCT((LIMIT>=B1:B3)*1) returns 0 on the first pass and 2 on the second, while SUMPRODUCT((B1:B3<=LIMIT)*1) returns 2 both times. A second operand that is a single cell (IF(LIMIT>=B1,…), no : in the look-ahead) is also correct. The defect is only observable when the name's target cell is calculated after the consuming cell in the calculation order (D5 vs F1 in the test below); if the target happens to be calculated first, the stale read is masked.

Suggested fix: narrow the look-ahead so that only a : that would join the current token into a range counts — in RPN, exactly one address token between the current token and the : — instead of skipping over an arbitrary number of following address tokens.

Minimal Reproducible Test Case (MSTest):

[TestMethod]
public void CalcNameAsFirstOperandOfRangeComparison()
{
    var pck = new ExcelPackage();
    var ws = pck.Workbook.Worksheets.Add("CalcTest");
    ws.Cells["A1"].Value = 10D;
    ws.Cells["A2"].Value = 20D;
    ws.Cells["A3"].Value = 30D;
    ws.Cells["B1"].Value = 1D;
    ws.Cells["B2"].Value = 2D;
    ws.Cells["B3"].Value = 3D;

    //LIMIT points to a formula cell that is calculated after F1.
    ws.Cells["D5"].Formula = "1+1";
    pck.Workbook.Names.Add("LIMIT", ws.Cells["D5"]);

    //The name is the first operand, so in the RPN token list it is followed by the
    //tokens of B1:B3. IsSingleAddress sees the ':' of the next operand and returns
    //false, so D5 is never added to the dependency chain and LIMIT is read before
    //D5 has been calculated.
    ws.Cells["F1"].Formula = "MAX(FILTER(A1:A3,LIMIT>=B1:B3))";

    ws.Calculate();

    Assert.AreEqual(20D, ws.Cells["F1"].Value);
}

Expected: 20 (the rows where LIMIT >= B are 10 and 20, so MAX is 20) on the first Calculate().
Actual: #CALC! on the first Calculate(), 20 on a second Calculate().

Where to Place the Test in the EPPlus Repository

Target File Path: src/EPPlusTest/CalculationTests.cs

[smad2005]

[JanKallman]

Thanks for reporting this. We will provide a fix shortly.