Today, I will demonstrate how to utilize Excel functions as Parameter within LAMBDA with simple example.
Our dataset includes the Zone and Sales columns, and our goal is to group the zones and determine the maximum and minimum sales.
To find the maximum sales, we can apply the MAXIFS function, while for the minimum sales, the MINIFS function is appropriate. Aside from these functions, the arguments for the max or min range, criteria range, and criteria remain same.
So, I will define a variable name for the function within LAMBDA and then employ the MAXIFS function for the maximum sales column and the MINIFS function for the minimum sales column.
Formula in D2 cell,
=LET(a, A2:A20, b, B2:B20, c, SORT(UNIQUE(a)), l, LAMBDA(w, w(b, a, c)), VSTACK({"Zone", "Max", "Min"}, HSTACK(c, l(MAXIFS), l(MINIFS))))
The name of the LAMBDA function is l.
LAMBDA(w, w(b, a, c))
•w represents the variable name for the function.
•b refers to the sales column, either max or min range depending on the function.
•a is the Zone column, which serves as the criteria range.
•c consists of unique items from the Zone column, acting as criteria.
Comments