Use LET function to solve tricky Excel challenges

Intro:
Hello, and welcome to the CPA Australia Podcast, your weekly source for accounting, education, career, and leadership discussion.

Neale Blackwood CPA:
In the April 2021 article, I cover a brand new function called LET. L-E-T. Now, the LET function doesn't actually perform any calculations itself, but it enables you to incorporate variables in a calculation in a cell. Now, this has limited use for normal functions, but has great possibilities for more complex functions in things like budgets and financial models and just more complex Excel files.

Neale Blackwood CPA:
So, the actual syntax or the layout of the LET function is its = LET open bracket, and then basically you have the ability to define variables. Now, in the article, I've used a few examples. I'll use the simple one here.

Neale Blackwood CPA:
So, you can go X comma, so X is the variable, and then I typed in five, so that means X equals five comma. And then Y comma. And then I've typed in a seven. So, there's two variables. X is being defined as five and Y is being defined as seven. Then, I do another comma and then I can just go X plus Y, as an example, and then close the brackets.

Neale Blackwood CPA:
Now, the beauty of this is when you've got formulas that have multiple calculations that are the same within the formula... The common one here is XLOOKUP or VLOOKUP. These type of formulas, you might want to do different things depending on the result of these entries, and I'll go into more detail in a minute in terms of the XLOOKUP. But the beauty is, you can just calculate, let's say, an XLOOKUP or a VLOOKUP once, and then refer to it a number of times, depending on how you want to handle things. That's the example I'm going to give in a moment.

Neale Blackwood CPA:
The other example in the article is the very first thing I thought of when I heard about this LET function. So, in Excel, you can use a formula to extract the sheet name. Now the formula itself, the standalone formula, is quite long and complex, and it uses one part of one calculation three separate times. It's the CELL function.

Neale Blackwood CPA:
The CELL function in Excel can, when you use the file name option, it can basically give you the full path of the file, the file name, and right on the end is the sheet name. That's what we want to extract. But you have to use the CELL function three separate times in the single standalone formula and it becomes quite long.

Neale Blackwood CPA:
So, that's the very first thing I thought of, is that you could set up the LET function to capture this CELL function and then just use the variable. Again, I tend to use just the X as the variable, because it's nice and short and X tends to be a fairly common variable name and so I use that in the example. So, it does shorten the formula and in general shorten formulas are better.

Neale Blackwood CPA:
The only downside is, because LET is so different, is that it might be a little bit difficult for other, say, more beginner users to understand, because you do need to understand the LET function, because the LET function doesn't do the calculation. It just enables you to have the variables.

Neale Blackwood CPA: The calculation is actually within the LET function brackets, and that's sort of on the end of the LET function. So, it may be a bit of a learning curve for new users and beginner users to try and understand the LET function. I see it more for intermediate and advanced users.

Neale Blackwood CPA:
The advantages are, one is, you get a shorter formula. In general, a shorter formula is better. And two, because you're only doing the calculation once for a particular part of the formula, it's quicker.

Neale Blackwood CPA:
Now, you're not going to see any increase in speed in terms of if you're just using the LET function once, but if you use it multiple times in, say, more complex models, then hopefully you will see the advantage or the improvement in speed.

Neale Blackwood CPA:
Okay. Let's go into this XLOOKUP example that I gave now. Obviously, because this is a podcast, you can't go into a lot of detail. If you do want to check out the video, it goes into obviously a lot more detail and you can see everything. But basically, what we have is we want to extract something from a table.

Neale Blackwood CPA:
Now, that table could have a real number in it. That's what we're extracting. It could be a text number, which basically means it's left aligned and Excel doesn't treat it a number until you convert it into a number which is pretty easy to do and I'll explain that in a minute.

Neale Blackwood CPA:
Then we could have an invalid code that we're looking at, which means we return an NA error. So, we got to handle that. Also, there could be text in the column that we're extracting from. If that's the case, we want to return the text error.

Neale Blackwood CPA:
Now, if the cells blank that we return, it actually returns a zero, which we want to return the word zero error. So, there's all these different things we want to return based on the result of this XLOOKUP. And to do that, you have to use the XLOOKUP with a number of if functions.

Neale Blackwood CPA:
In the example, you are repeating the XLOOKUP function three times. The XLOOKUP function is reasonably long, and so that really expands out the function. Also, I threw in an if error overall of the calculations, and just to put a general returner, like an input error type thing, as well.

Neale Blackwood CPA:
So, there was quite a few different messages that I want to return, which was the text within quotation marks, as well as I wanted to return the number. That was the aim of it. So, I had to convert to the number.

Neale Blackwood CPA:
To convert a text number... A text number is a number that looks like a number, but it's left aligned, because anything that's left aligned in Excel, assuming, that there's no other format been applied to, it is treated as text. Unfortunately Excel in a lot of calculations will not treat a text number as a real number until you convert it to a real number, and the easiest way to do that is to simply multiply that by one.

Neale Blackwood CPA:
Now, in the previous article, I covered the is functions. So, I did detail how you can check a text number, if that could be a real number by multiplying the formula by one, and then just using the is number function around that formula, which is what I've done in the example.

Neale Blackwood CPA:
Basically it shortens the formula. It speeds up the formula. In general, as I've mentioned, I think the LET function is going to be used by intermediate to advance users. Initially probably more advanced users are going to use it and hopefully then it'll trickle down.

Neale Blackwood CPA:
Now, just a reminder, it is only available in the subscription version of Excel. I have no idea of the timeframe of getting this into the full version, the sort of DVD version, or when you buy a full version of Excel. But currently in the subscription version. Again, Microsoft is trying to encourage everyone to go the subscription version rather than the full versions.

Neale Blackwood CPA:
So, the LET function, brand new. Offers a lot of possibilities for more complex formulas. It's not really going to be used for the common garden variety sum functions, but it is going to offer a lot of opportunities for things like budgets and financial models and even reporting systems. Hope you found that useful. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on apple podcasts, Spotify or Stitcher.