LET: Storing Variables Inside Your Excel Formulas - EBOOKS AND TIPS FREE

# 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.
• 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.
• 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. 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.
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 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.