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

m_tarler's avatar
m_tarler
Silver Contributor
Feb 06, 2026

So the problem is you need to actually write a simple parser to interpret that second line.  Here is an example that can do that:

=LET(in,D11:I12,
checks, BYCOL(in,LAMBDA(c, IF(N(INDEX(c,1)),LET( val, INDEX(c,1), crit, INDEX(c,2),
      compare, SWITCH(LEFT(crit,1), "<",-2,"≤",-1,"=",0,"≥",1,">",2,3),
      value, --MID(crit,2,SEARCH(" ",crit)-2),
      --CHOOSE(compare+3, val>=value, val>value, val<>value, val<value, val<=value, 0)),0))),
SUM(checks))

Alternatively you can take the above code and make a LAMBDA function out of it so each cell in K would need a very simple formula.  So lets call it "FAILS".  So in the Name Manager (on the 'Formulas' ribbon) click NEW and then enter FAILS as the name and then the following formula in the Refers To: box

=LAMBDA(in, SUM(BYCOL(in,LAMBDA(c, IF(N(INDEX(c,1)),LET( val, INDEX(c,1), crit, INDEX(c,2),       compare, SWITCH(LEFT(crit,1), "<",-2,"≤",-1,"=",0,"≥",1,">",2,3),       value, --MID(crit,2,SEARCH(" ",crit)-2),       --CHOOSE(compare+3, val>=value, val>value, val<>value, val<value, val<=value, 0)),0)))))

it is essentially the same as the above but not defining that range D11:I12 and will now let you enter it like a formula.  so now in K11 you just enter:

=FAILS(D11:I12)

and voila