Excel: The LET Function Explained

In simple terms, LET allows you to simplify and streamline formulas in Excel. It makes them easier to read, easier to manage, and perhaps most importantly, can improve performance by reducing the number of repeated calculations.

Let’s start with a basic example. Suppose you have a list of sales transactions, with Revenue in column B and Cost in column C.

In cell G1, you might have a formula like this:

=IF(SUM(B2:B16) > SUM(C2:C16), "Profit", IF(SUM(B2:B16) = SUM(C2:C16), "BREAK EVEN", "LOSS"))

This formula checks whether the total revenue is greater than, equal to, or less than the total cost, and returns “Profit”, “Break Even”, or “Loss” accordingly. The formula works just fine but it’s not as efficient as it could be.

In G2, I rewrote the same formula using the LET function:

=LET(
    revenue, SUM(B2:B16),
    costs, SUM(C2:C16),
    IF(revenue > costs, "Profit", IF(revenue = costs, "BREAK EVEN", "LOSS"))
)

What’s the Benefit?

Let’s break it down.

In the original formula, Excel calculates SUM(B2:B16) twice and SUM(C2:C16) twice—that’s 4 separate calculations.

In the LET version, each sum is calculated only once and given a name – revenue and costs. That’s just 2 calculations.

Fewer calculations mean better performance, especially in workbooks with large datasets or complex formulas. While the performance difference might be negligible with a small dataset, it becomes noticeable as your formulas grow.

Plus, the LET version is easier to read and troubleshoot. Instead of repeating long formulas, you can define them once and refer to them by name.

How the LET Function Works

Here’s how the LET function is structured:

LET(name1, value1, [name2, value2, ...], calculation)

• Name: This is like a variable name in programming.
• Value: This can be a number, a text string, or another formula and is the value assigned to the Name
• Calculation: This is where you use the names you defined earlier.
  

In the example:

LET(
    revenue, SUM(B2:B16),
    costs, SUM(C2:C16),
    IF(revenue > costs, "Profit", IF(revenue = costs, "BREAK EVEN", "LOSS"))
)

• revenue is assigned the value SUM(B2:B16)
• costs is assigned SUM(C2:C16)
• The IF logic uses these names in place of the original formulas
  

You can define up to 63 name/value pairs in a single LET function which should be more than enough for most practical use cases.

Final Thoughts

If you’re using Excel 365, Excel 2021, or Excel 2024, the LET function is a powerful tool to have in your toolkit. It helps reduce repeated calculations, improve formula performance and makes your formulas easier to read and maintain

Download a copy of the file(s) used in this video: https://share.zight.com/llubEwAN