LET: Storing Variables Inside Your Excel Formulas
March 31, 2020  by Bill Jelen
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 subcalculation 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 reuse 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 SpecialK99 is designed to find the nexttolast word in a phrase.
If I were to build the original formula stepbystep, I would build it in steps.

Step 1: In B4, take the TRIM of the original phrase 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 fourword 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.

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.

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

Step 5: Isolate the nexttolast word using MID and FIND again.
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:
Why is this so confusing? How did five formulas with an average length of 24 characters turn into a 370character 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 subformulas is used in the final formula.
LET to the Rescue
The LET function allows you to define variables once in the formula and reuse 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.
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 reused in the definition for WhichSpace and CaratText.
Watch Video
You can watch the steps to combine the subformulas 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