Video
Get Access to Your File
Enter your name and email to receive access to the file.
Summary
LET function allows you to repeat the same expression multiple times within a formula or to reuse portions of your formula for easy construction of end formula.
Goal
LET function give provision to declare value or formula with a name which you can link it multiple time in the calculation as an when required.
Result
Return the formula result using the declare variables which are constructed inside LET function in a calculation.
Syntax
=LET(name1, value1, [name2…], [value2…], calculation)
Arguments
| name1 | The name for the 1st value |
| value1 | The value/formula associated with the 1st name |
| [name2] (optional) | Additional names |
| [value2](optional) | Additional values |
| calculation | This is always the final argument where the calculation to be performed. and it can refer to any of the defined names in the LET. |
Usage
When we are constructing a simple or complex formula LET function will be very handy, just imagine it works like a variable/parameter where you can declare your value or formula with a meaningful name and reuse it with the name within the formula construction each time required. If you are familiar with writing DAX Expression it works exactly like VAR.
Example 01
Let us look a very simple example we would like to get the average of quantities sold for the last 10 records
Original Formula
=SUM(A1:A10)/COUNT(A1:A10)
Formula using LET
=LET(total, SUM(A1:A10), count, COUNT(A1:A10), total/count)
With the let function as we can see the formula looks more meaningful and easier to understand the formula construction.
Example 02
In the second example, we are generating an aging report, which group the order delivery status.
As you can see Original Formula we are repeating the calculation (B4-A4), but Formula using LET allows us to store that calculation in a variable name day and call it whenever needed.
Original Formula
=SWITCH(TRUE,(B2-A2)>90,"90 Days Above",(B2-A2)>30,"31 - 60 Days",(B2-A2)>0,"")
Formula using LETs
=LET(day,(B2-A2),
SWITCH(TRUE,day>90,"90 Days Above",day>30,"31 - 60 Days",day>0,"")
)As per Microsoft Tech Community Site LET is now available to Office 365 Subscribers in the Insiders Channel and will be available to users of other channels later this year.
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
- If no, please let us know what to improve.