Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Nov 24, 2023

LET function help

Dear Experts,

                   In attached sheet, I shared the calculation for the Min/Max/Avg "rrmUlUsedMcs", for each of the rnti(colD), and the lcrId used per rnti.

But , I want to use LET function to achieve this, can you please share on how to use a Single "LET" function and achieve this?

 

 

Thanks & Regards

Anupam Shrivastava

Excel
Formulas and Functions
  • djclements's avatar
    djclements
    Nov 24, 2023

    anupambit1797 Use the LET function to define names for each variable and/or calculation, then use HSTACK to return the final results. For example:

     

    =LET(
   keys, Table3[rnti],
   values, Table3[rrmUlUsedMcs],
   u, UNIQUE(keys),
   a, BYROW(u, LAMBDA(r, TEXTJOIN(", ",, UNIQUE(FILTER(Table3[lcrId], keys=r))))),
   b, MINIFS(values, keys, u),
   c, MAXIFS(values, keys, u),
   d, AVERAGEIFS(values, keys, u),
   HSTACK(u, a, b, c, d)
)

     

    See attached...

  • djclements's avatar
    djclements
    Bronze Contributor
    Nov 24, 2023

    anupambit1797 Use the LET function to define names for each variable and/or calculation, then use HSTACK to return the final results. For example:

     

    =LET(
   keys, Table3[rnti],
   values, Table3[rrmUlUsedMcs],
   u, UNIQUE(keys),
   a, BYROW(u, LAMBDA(r, TEXTJOIN(", ",, UNIQUE(FILTER(Table3[lcrId], keys=r))))),
   b, MINIFS(values, keys, u),
   c, MAXIFS(values, keys, u),
   d, AVERAGEIFS(values, keys, u),
   HSTACK(u, a, b, c, d)
)

     

    See attached...

    • anupambit1797's avatar
      anupambit1797
      Iron Contributor
      Nov 24, 2023

      Thank you Very Muchdjclements , for your fast response..

       

      Also, if I want to name the function say "rrmUlUsedMcs_avg" in NAME Manager, as below:-

       

      But, when I actually use this formula in another cell, say "P6", it gives a CALC err, is something I am missing?

       

      Attached is my work sheet

       

      Thanks,

      Anupam

       

      • djclements's avatar
        djclements
        Bronze Contributor
        Nov 24, 2023

        anupambit1797 In this particular case, the #CALC! error is returned because the FILTER function did not find a match. The reason is because your formula for the defined name "rrmUlUsedMcs_avg" is referencing the wrong Id value in K6, when it should be J6. For example:

         

        =AVERAGE(FILTER(Table3[rrmUlUsedMcs], Table3[rnti]=Sheet1!J6))

         

        Also, since the defined formula is using a relative reference, J6 is relative to the active cell at the time of defining the name. If you always want it to reference the values in column J, use a mixed cell reference with absolute columns and relative rows (=Sheet1!$J6).

         

        Having said that, another option would be to use LAMBDA in the definition of your custom function with a single parameter for "rnti" as follows:

         

        =LAMBDA(rnti, AVERAGE(FILTER(Table3[rrmUlUsedMcs], Table3[rnti]=rnti, 0)))

         

        Then use the new formula in cell P6, for example:

         

        =rrmUlUsedMcs_avg(J6)

         

        See attached...

Resources