Overview
If you haven’t tried the LET function in your Excel formulas yet, you’re really missing out, especially if you work with a lot of formulas! LET is a unique function that lets you define reusable variables in your formulas. It’s a key programming concept, and LET is further blurring the lines between Excel functions and programming languages. If you’re familiar with named ranges, LET works a bit like that.
In this article, we’ll look at what it is, how it works, why you should use it, some tips to use, some gotchas to watch out for, and five common examples of where you might want to use it. Make sure to download the example file below to see these examples in action!
What is LET?
The word “let” is a commonly-used word in many programming languages for defining variables. Variables allow you to store a value and reuse it over and over. With the LET function in Excel, these variables can refer to:
- Constant values
- Ranges
- Formula calculations
Within a LET function, you can define one or more names to be used in calculations you may already be using or consider using. It’s a lot like named ranges where you can refer to the name instead of a reference, only the scope is just within the formula, not all throughout a sheet or file.
How do you use LET?
First, supply a name. Then for that name, define a constant value, a range to reference, or a calculation. Then if needed, use more names and corresponding definitions. Finally, include a calculation that uses those names where you’d normally put the value, range, or calculation.
Here’s the syntax when you need to define one name:
=LET(name1,value1, result)
And here’s the syntax when you need to define multiple names:
=LET(name1,value1,[name2/value2],…,result)
Why should you use LET?
There are three key reasons people tend to use the LET function:
Performance optimization – any variables you define (especially those with their own formula calculations) only have to be calculated once and they can be referenced over and over again in further calculations within your formula.
Simplicity – reusing variables means less repetitive formula parts, and you can front-load and isolate several different calculations such that the variable names and the rest of the formula can read much easier.
Agility – it’s much easier to update the calculation in one place (the variable) than multiple places (all throughout the formula).
Tips
Here are a few tips for getting the most out of the LET function:
- Up to 126 name/value pairs can be defined in one LET function before the resulting calculation argument.
- Earlier-defined variables can be used by later ones – native nesting!
- Use consistent, descriptive names for variables for clarity (not just in one individual LET function, but throughout your workbooks).
- Use line breaks (Alt+Enter) to separate functions and arguments for clarity – some people like for each name/definition to be on a separate line.
- Consider unused variables for comments Ex: =LET(x,5.6, c_1,”x is the start”,[calc])
Gotchas
There are also a few gotchas to be aware of when using the LET function:
- Variables are not color-coded like repeated references and helper columns, which might make it tougher to build and troubleshoot.
- Variables are not evaluated (when examining each step/argument in a formula works) with F9 formula evaluation.
- Be careful not to accidentally create circular references with definitions – it can be easy to do this when defining names that refer to themselves.
- Try to use LET when it makes formulas less complex, not more (it’s easy to do this!).
Examples
Example 2: Simplify narratives
If you use Excel to draft narratives, the formula can get messy and tough to read. LET allows you to clarify it!
Example 3: Simplify date calculations
Date calculations in Excel can get tricky. LET allows you to simplify them by setting a value, reference, or array once!
Example 4: Simplify tiered commission calculations
Tiered tax or commission calculations can be difficult to build and maintain. LET simplifies them by setting variables!
Example 5: Finding the most-improved player
LET isn’t just for defining variables, it can handle arrays and return multiple results, even for different attributes!
Summary
As you can see, LET is pretty unique. Is it crucial for all your Excel files? Probably not. But it’s a great tool to simplify and organize the different parts of your formulas in a way that helps you build them, understand them, and update them more easily. For your more involved formulas, especially for the resource-intensive ones, consider defining variables with LET and pay it forward to your colleagues and your future self!
In what creative ways have you used the LET function? Let us know in the comments below!
Recent Comments