LET Functions
Problem:
Have you ever had to use the same term several times inside a formula, have you developed a super formula or have you wished to find a way to reuse parts of the formula for simpler use? Now you can, by adding the LET function!
Solution :
LET lets you associate a calculation or value with a name in your formula. These names except at the level of a formula. If you uses same expression more than one in a formula, Excel will calculate that expression repeatedly. LET lets you to keep specific name of expression and using saved name it refers that expression. Any expressions named shall be calculated only once, even if they are referred to many times in the formula. That can greatly enhance computationally complex expressions performance.
The function definition for LET works as follows:
LET(name1, value1, [name2…], [value2…], calculation)
name1: The name for the 1st value
value1: The value to associate with the 1st name
name2 (optional): Additional names
value2 (optional): Additional values
calculation: The calculation to perform. It can refer to any of the defined names in the LET.
Structure of formula for LET :
1) =LET(total, SUM(A1:A10), total * 3).
(In this total and SUM(A1:A10) are in pair.)
2) =LET(total, SUM(A1:A10), count, COUNT(A1:A10), total / count)
(if we want to add more names, we just need to define a new pairs)
3) The last function is the calculation
“total *3” or “total / count”
Example:
If you want to show travel history of only one person
Unfiltered Data:
Name | Country Traveled | Budget |
Jone | USA | $390 |
Jackie | England | $234 |
Jackie | India | $219 |
Jone | Japan | $234 |
Jackie | China | $278 |
Jone | Vietnam | $176 |
Filtered Data:
Name | Country Traveled | Budget |
Jackie | England | $234 |
Jackie | India | $219 |
Jackie | China | $278 |