Forum Discussion

tsbarton70's avatar
tsbarton70
Copper Contributor
Feb 06, 2026
Solved

Stacked Excel Formula

Hello everyone. I've spent the past 2-3 hours trying to figure this out on my own without luck. What I'm needing is a formula that will check D11 (highlighted) to make sure that it's within the param...
excel
Formulas and Functions
  • IlirU's avatar
    IlirU
    Feb 10, 2026

    Hi tsbarton70​,

    I created below formula a few days ago but I was very hesitant whether I should post it or not, as it is a very large and difficult formula to understand, but in the end I decided to post it today. If this formula does not serve to you, or in real conditions it does not give the right results, then let me know and I will delete this post.

    =IFNA(TOCOL(EXPAND(BYROW(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,
      BYCOL(B2:G13, LAMBDA(x, ARRAYTOTEXT(LET(d, SUBSTITUTE(x, " ", ""),
rw, ROWS(d), mod, MOD(SEQUENCE(rw), 2), br, BYROW(IF(d = "", 0, d),
      LAMBDA(a, TRIM(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(a, ">", ">"), "<", "<"),
      "[^0-9.,+\-*/^()≤≥<>=% ]", " "), "\s+", " ")))),
tb, IFERROR(TEXTBEFORE(br, " ", LEN(br) - LEN((SUBSTITUTE(br, " ", "")))), br),
op, FILTER(tb, mod = 0), LET(a, --SUBSTITUTE(--FILTER(tb, mod = 1), ",", "."),
 b, --SUBSTITUTE(IFERROR(--BYROW(op, LAMBDA(y,
      IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(y, ">", ">"), "<", "<")),
      "[0-9]+(?:[.,][0-9]+)?"), ""))), 0), ",", "."),
op, XLOOKUP(BYROW(op, LAMBDA(z, IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(z, ">", ">"), "<", "<")),
      "(>=|<=|≥|≤|>|<|=)"), ""))), {"<","≤","=","≥",">"}, {"<","<=","=",">=",">"}),
      IFNA(IF(SWITCH(op, ">=", a >= b, "<=", a <= b, ">", a > b, "<", a < b, "=", a = b, NA()), 0, 1), 0))))))), ", ", ";")), SUM),, 2)), "")

    Hope this helps.

    IlirU

Lorenzo's avatar
Lorenzo
Silver Contributor
Feb 06, 2026

Hi

(initial post edited - due credit m_tarler​)

in H11:

=OUT_PARAMETER( D11:D12, H11:H12, I11:I12 )

where OUT_PARAMETER is LAMBDA:

=LAMBDA([ref_1],[ref_2],[ref_3],
  3 - SUM(
        IF(ISOMITTED(ref_1), 1, COMPARE_TOPARAMETER(ref_1)),
        IF(ISOMITTED(ref_2), 1, COMPARE_TOPARAMETER(ref_2)),
        IF(ISOMITTED(ref_3), 1, COMPARE_TOPARAMETER(ref_3))
      )
)

and COMPARE_TOPARAMETER:

=LAMBDA(reference,
  LET(
    RefValue,     TAKE( reference, 1, 1 ),
    comparerStr,  TAKE( reference, -1, 1 ),
    IF(
        comparerStr = 0, 1,
        LET(
            Operator,       TRIM(TEXTBEFORE(comparerStr, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9})),
            afterOperator,  TRIM(TEXTAFTER(comparerStr, Operator)),
            ComparerValue,  VALUE(TEXTBEFORE(afterOperator, " ", , , , afterOperator)),
            --CHOOSE(
                XMATCH(Operator, {"<", "≤", "=", "≥", ">"}),
                RefValue < ComparerValue,
                RefValue <= ComparerValue,
                RefValue = ComparerValue,
                RefValue >= ComparerValue,
                RefValue > ComparerValue
            )
        )
    )
  )
)

LAMBDAs are stored in Name Manager of the attached wbook