Forum Discussion

tsbarton70's avatar
tsbarton70
Copper Contributor
Feb 06, 2026

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 parameters listed below it (<17), then I need it to do the same for H11 and I11 (highlighted) and enter the number (1-3) that are not "equal to or greater/less than" into K11. K11 reflects how many samples in Row 11 that are outside of those parameters. I'm trying to make this worksheet more automated and this is the one thing that I cant figure out.

 

Example 1: (all numbers are within parameters, so a 0 is entered into column K)

 

Example 2: (column G is not within the parameters, therefor there is 1 entry in column K)

excel
Formulas and Functions

2 Replies

  • 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

  • m_tarler's avatar
    m_tarler
    Bronze 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

Resources