LET function help

[anupambit1797]

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

[#2 · Accepted solution]

@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...

[#3]

Thank you Very Much@djclements , 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

[#4]

@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...

[#5]

Thank you @djclements so much !!

Thanks & Regards

Anupam Shrivastava

[#6]

@anupambit1797 

To play with new functions

=HSTACK(
  GROUPBY(Table3[[#All],[rnti]],Table3[[#All],[lcrId]], LAMBDA(v, ARRAYTOTEXT(UNIQUE(v)) ),3,0  ),
  DROP( GROUPBY(Table3[rnti],Table3[rrmUlUsedMcs], HSTACK( MIN, MAX, AVERAGE ),,0  ),,1)
)

[#7]

Thanks @SergeiBaklan