If you’re not aware of what the LET() function is, prepare to be informed (and get out from under the rock you’ve been hiding under!).
The LET function was introduced in July 2020, and I must admit it wasn’t until this past week that I actually started using it in anger, and now I can’t stop myself using it.
I quite like what you can achieve under certain use cases and I think I’m going to use it a lot more as time goes on. Let’s look at a few examples on how it can make your life easier.
So, what is the LET() function?
Well, this function allows you to substitute a variable for longer parts of a cell formula. Let x = ?, then do something with x. It’s that simple.
You can use as many variable substitutions as you want, I’m using x here, but you can use any variable name you want, the defined variable is only available for use in the Calculation argument for that particular formula. The basic nomenclature is as follows: –
In the above example we end up with the following cell formula: –
| =LET(x,(3^6+123+5^6)^0.5,x^2+x-23) |
So basically, it allows us to assign a variable (x in this instance) to some part of the calculation (the (3^6+123+5^6)^0.5 part), and then use the variable x in the actual calculation that follows. The above could obviously be written in full as follows: –
| =(3^6+123+5^6)+(3^6+123+5^6)^0.5-23 |
Which to be honest in this case isn’t too much different, so why would you want to use LET() instead?
Simplifying repetitive bits…
Well, it has the potential to simplify more complicated cell formulas, in my opinion this helps with the verification and readability of larger formulas. Allowing you to break it up into more manageable chunks if you like.
Especially when you have longer formulas where certain parts of the formula is being repeated multiple times, this really helps. For example, the following formula equation could be written in one of two ways: –
| =FILTER(UNIQUE(SORT(AB117:AB178)),UNIQUE(SORT(AB117:AB178))<=MAX_PROB_DEFLECTION,NA()) |
By recognising the repetitive bits we can simplify things a tiny bit: –
| =LET(x,UNIQUE(SORT(AB117:AB178)),FILTER(x,x<=MAX_PROB_DEFLECTION,NA())) |
If we recognise that the UNIQUE(SORT(AB117:AB178)) is being repeated twice we can pull it out in the LET function give it the variable x and simplify things quite a bit.
Not only is the formula generally shorter in length, but it’s also actually easier to debug if you ever have a need to update a repeated part of the formula in the future. This is because you only need to update it once, rather than updating it multiple times with the potential to miss some part of the formula being updated as intended.
In a larger and/or more complicated formulas where a certain calculation is repeated multiple times, this also makes reading the formula and updating it much easier and a big plus in my opinion is that as a result you’ll hopefully be less prone to making unintentional errors.
It really comes into its own when you have a lot of nested if statements that are testing variations on a common condition, for example the following: –
| =IF(MEDIAN(UNIQUE(AB117:AB178))<=0, “-VE”, IF(MEDIAN(UNIQUE(AB117:AB178))=0, “ZERO”, IF(MEDIAN(UNIQUE(AB117:AB178))>=0, “+VE”, IF(MEDIAN(UNIQUE(AB117:AB178))>=100, “TOO HIGH”)))) |
…can be simplified to the following: –
| =LET(x,MEDIAN(UNIQUE(AB117:AB178)), IF(x<=0,”-VE”, IF(x=0,”ZERO”, IF(x>=0,”+VE”, IF(x>=100,”TOO HIGH”))))) |
I don’t know about you, but the LET function version is so much easier to tell what the hell is going on (if you must use nested IF statements….). Sometimes there is no choice, so who am I to judge, here’s some abomination I created a few years ago to show it happens to the best of us….. the shame.
Thats like 18 IF statements in one formula…. and NO I am not going to try to simplify it using the LET function. It’s beyond help.
Simple formula verification & visualisation…
Another use for LET is simply defining variables for everything in the equation you are evaluating, this comes into it’s own if you are reviewing/checking calculations. As you can see for example in the following equation for the deflection of a simply supported beam, we can review the inputs and output equation in terms of variables, and which input cell reference is what. We might not want to define named ranges for each of the inputs if we are intending to drag the formula down the sheet to cover some finite number of calculations for multiple beams. Usually, I’d advocate named ranges be defined, but in some cases it’s preferable to work with relative cell references: –
| =LET(w, A1, L, A2, E, A3, I, A4, 5*w*L^4/(384*E*I)) |
I don’t know about you, but following a calculation that uses the variables is pretty logical and you’re more likely to spot a mistake you or others have made as a result.
You can even use the LET function defined variables in other defined variables in the LET function, It’s kind of like the movie Inception, but with functions.
This offers the ability to breakdown larger calculations into more manageable chunks and assemble it all together in the final calculation. Usually I’d advocate using another ‘helper’ cell to do this type of thing, so it was more readily understandable. But with the LET function it can be rolled into one formula and remain relatively easy to follow.
For example, the above beam deflection calculation can be split into the numerator and denominator calculations and then the final calculation is calculated using these intermediate defined variables: –
| =LET(w, A1, L, A2, E, A3, I, A4, numerator, 5*w*L^4, denominator, 384*E*I, numerator/denominator) |
While a slightly longer formula results in this case, for more complex calculations this incremental approach of evaluating part of the equation separately really pays dividends when you come to checking the inputs/outputs.
The only rule here is if you are using the defined variable in another defined variable, you need to have defined it before you use it. Similar to any variable definition in programming. Follow that rule and you’ll have no issues, otherwise you’ll be greeted with a #NAME? error.
In several years’ time you’ll probably thank yourself because you can revisit the calculation and (potentially) actually understand what the hell you did/were thinking because you used LET.
We’ve all been there, head scratching trying to figure out what we were actually calculating all those years ago when we have these massive nested IF statements with no named cells, no comments, no notes! Don’t be that person….. put the Fun in Function instead……