In the latest version of Excel, a new function called LET has been introduced, which is very helpful for simplifying the process of writing formulas, making our work more efficient and convenient. Here’s a guide to the basic usage of the LET function along with practical examples.
1. Basic Usage of the LET Function
The syntax for the LET function is:
=LET(Name1, Value1, Calculation or Name2, Value2, …)
This function can accept multiple parameters. At least three parameters are needed to form a valid LET formula. For example:
=LET(myVar, 2, myVar + 3)
This means we define a name “myVar” with a value of 2, and then calculate “myVar + 3,” which results in 5.
If there are five parameters, it would look like this:
=LET(X, 2, Y, 3, X + Y)
Here, we define two names, X as 2 and Y as 3, with the final calculation resulting in 5.
To summarize, the number of parameters in the LET function can be 3, 5, 7, 9, etc., with the last parameter being used for calculation and the preceding pairs serving as custom names.
2. Practical Application 1: Enhance Readability
Suppose we need to find the BMI values for employees; traditional formulas can be confusing. For instance, we might enter the formula like this:
=VLOOKUP(A7, A:C, 3, 0) / (VLOOKUP(A7, A:C, 2, 0)^2)
While this gives a result, it may not be easily understood by others. By using the LET function, we can simplify it to:
=LET(weight, VLOOKUP(A7, A:C, 3, 0), height, VLOOKUP(A7, A:C, 2, 0), weight / height^2)
This version is much clearer and easier to read.
3. Practical Application 2: Abbreviate Formulas
When calculating the total rewards for each employee, using the SUMIFS function might lead to repetitive formulas. For example:
=IF(SUMIFS(C:C, A:A, E2) = 0, “”, SUMIFS(C:C, A:A, E2))
Here, we have redundant SUMIFS functions. By utilizing the LET function, we can streamline it to:
=LET(X, SUMIFS(C:C, A:A, E2), IF(X = 0, “”, X))
This way, we can simply use X in subsequent calculations instead of repeating the lengthy formula, significantly simplifying our work.
Conclusion
The LET function can help us write Excel formulas more efficiently, enhancing both readability and functionality. I encourage everyone to try this powerful feature for themselves!
A0001
