Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

LET function help

Iron Contributor

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?

 

anupambit1797_0-1700784023071.png

 

Thanks & Regards

Anupam Shrivastava

6 Replies
best response confirmed by anupambit1797 (Iron Contributor)
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...

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:-

anupambit1797_2-1700785852462.png

 

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

anupambit1797_3-1700785958595.png

 

Attached is my work sheet

 

Thanks,

Anupam

 

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

Thank you @djclements so much !!

 

Thanks & Regards

Anupam Shrivastava

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

image.png

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
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...

View solution in original post