Forum Discussion
LET function help
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 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 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
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...
