MrExcel.com - Your One Stop for Excel Tips & Solutions

LET: Storing Variables Inside Your Excel Formulas

March 31, 2020 - by

LET: Storing Variables Inside Your Excel Formulas

Formulas in Excel are already a programming language. When you build a model in Excel, you are essentially writing a program to calculate a set of outputs from a set of inputs. The Calc team Redmond have been working on a couple of enhancements to the Excel formula language to make Excel a bit more like a programming language. The first of these, the LET function is now out in beta. Anyone who opts into the Insider Fast channel of Office 365 should have access to LET.

Sometimes you build a formula that has to refer to the same sub-calculation over and over again. The LET function lets you define a variable and the calculation for that variable. Your calculation can have up to 126 variables. Each variable can re-use the calculations in the previous variables. The final argument in the LET function is a formula that returns a value (or an array) to the cell. That final formula will refer to variables defined earlier in the LET function.

This is easiest to see if I show you an example. I randomly found a formula posted at the MrExcel Message Board in 2010. This formula, from member Special-K99 is designed to find the next-to-last word in a phrase.

If I were to build the original formula step-by-step, I would build it in steps.

  • Step 1: In B4, take the TRIM of the original phrase to get rid of repeated spaces.

    TRIM function to get rid of repeated spaces
    TRIM function to get rid of repeated spaces.

  • Step 2: Figure out how many words are in B4 by comparing the LEN of the trimmed text to the length of the trimmed text after removing spaces with SUBSTITUTE. In a four-word phrase, there are three spaces. In the current problem, you want to find the second word, hence the minus one at the end of this formula.

    LEN and SUBSTITUTE functions to count words
    LEN and SUBSTITUTE functions to count words

  • Step 3: Add a carat (^) before the desired word. This again uses SUBSTITUTE but makes use of the third argument in SUBSTITUTE to find the 2nd space. It won’t always be the 2nd space. You have to use the result from Step 2 as the third argument in Step 3.

    Using carat in SUBSTITUTE
    Using carat in SUBSTITUTE

  • Step 4: Isolate all of the words after the carat using MID and FIND.

    Isolate all of the words after the carat using MID and FIND
    Isolate all of the words after the carat using MID and FIND

  • Step 5: Isolate the next-to-last word using MID and FIND again.

    Isolate the next-to-last word using MID and FIND
    Isolate the next-to-last word using MID and FIND

When broken down into small calculations as shown above, many people can follow the logic of the calculation. I frequently build formulas using the method shown above.

But I don’t want to take up five columns for one formula, so I start consolidating those five formulas into a single formula. The formula in F4 uses E4 twice. Copy everything in the formula bar for E4 after the equals sign. Use Paste to replace E4 in both places. Continue replacing cell references with their formulas until the only thing referenced by the final formula is cell A4. At this point, you have an insanely long formula:

Very long Excel formula
Very long Excel formula

Why is this so confusing? How did five formulas with an average length of 24 characters turn into a 370-character formula? It is because the simple formula in B4 is referenced a total of 12 times in the final formula. If you haven’t stored =TRIM(A4) in cell B4, then you end up typing out TRIM(A4) twelve times in the final formula.

Here are how many times each of the sub-formulas is used in the final formula.

Sub-formulas count in the final formula
Sub-formulas count in the final formula

LET to the Rescue

The LET function allows you to define variables once in the formula and re-use those variables later in the formula. In the figure below, four variables are defined in the variable definitions before a final calculation returns the last word.

LET function to define variables
LET function to define variables

It seems that the best practice here is to use Alt+Enter after each variable definition in the formula. While your formulas could be A, B, C, and D, it does not hurt to use meaningful variable names, just like you would do in any programming language.

Notice in the figure above that after defining TRIMTEXT as =TRIM(A4), the TRIMTEXT variable is re-used in the definition for WhichSpace and CaratText.

Watch Video

You can watch the steps to combine the sub-formulas into the megaformula and into the LET function here:

In other testing, the LET formula calculates about 65% faster than the similar megaformula.

Title Photo: Photo by Allie Smith on Unsplash