When You Should Use the New Excel LET Function

  

d, and it can improve theperformance of your formulas so they run faster. Now this applies to formulas that use the same expression multiple times. With the LET statement,you can let Excel calculate that expression justonce and then reuse it as often as you need, which results in your formulas running faster. These two benefits are also shared on the Microsoft Support Page. Now I was really curiousabout the second point. Is the impact onperformance that noticeable? That's what we're going to find out today. (upbeat music) Before we get started, a quick thanks goes to today's sponsor, which is Skillshare. I have a special link foryou, it's in the description of the video, check itout when you're ready, I'm going to talk moreabout them and their classes towards the end. Let's see what LET can do for you. First off, let's talk about availability of the LET function. It's available forExcel for Microsoft 365, and it's currently in the preview version. Now the syntax of the LETfunction should start off with LET, open the bracket. First thing we need is name one, and then it's name value one,and then it's your calculation or you continue with morename and value combinations. Now name one is basically any name that you want to give yournumber or your cell reference. Let's say VAT is my name one,and my name value one is 20%. Now, I'm going to do acalculation, I'll take this number and multiply it with VAT. What am I going to get? I'm going to get 20% of this number. The syntax of this is very similar to what you're used to seeingunder formulas name manager, if you create a new name, you get name, and refers to is basicallywhat you see in here, and this can be text, itcan be a cell reference, it can be a formula. Instead of defining thename for the whole workbook or for a worksheet, you'reactually just defining it for the formula. Now the reason you mightwant to do this in here is to make it easier to read. But in this specific case,it doesn't really matter because my formula will be much shorter if I just write B6 times 20%, right? But if I was using an IF function, and I'm using the 20% manytimes in this formula, it would be easier tomaintain the formula as well because if that ratechanges, I don't have to go and change all instances where I had 20%, I just change it once at thebeginning of the formula. That's just one example,but let's take a look at more realistic examples. Let's get rid of thisand do this example here. I want to calculate thevariance in percentage between actual and budget,and I only want to show the values that are aboveplus 10% or below minus 10%. I don't want to bring attentionto anything in between, just bring attention tothe bigger differences. This sounds like the IF function. Before I start and bringin the IF function, let's just calculate our difference. In this case it would beactual divided by budget minus one, and you can alsowrite this differently, you can do actual minus budget in brackets divided by budget, it's the same thing. Now when I press Control+ Enter, I see 10%, if I expand the decimalshere, I can see it's 9.97%. So according to thelogic I want to create, this number shouldn't be visiblebecause I don't want to show numbers that are within this threshold. So let's go and put thisinside the IF function. If, this is my logicaltest, if the absolute value of this is greater than 10%, so I'm actually going to put this in the ABS function toget the absolute value, we can say if this is greater than 10%, then I want to show the results, so I want to show this,or let's add the comma, I want to show nothing, andthat's quotation-quotation. Now you can also use the OR function instead of the absolutefunction, so you can say or this is greater than 10% orthis is less than minus 10%. You get the same result. So now when I press enter,I shouldn't see this value because that's less than10%, when I drag this down I only see these values, right? So the bigger differences. This function is just anIF function without LET, but the moment you start tosee repetition in your formula and they get longer, think about using LET because if you ever needto change a cell reference, you don't need to changeit in all instances of it, you just change it once. The repetition here is this formula. So I'm going to copy this, goto the beginning of the formula and start with LET. The name I want to givethis formula is VAR, let's just call it VAR for variance, and I'm going to paste in the formula, then it's my calculation, right? Which is my IF function butnow, because I've defined a name for this, I'm goingto change this to VAR, and you don't need quotationmarks for these names here. And then press enter, andI need a second bracket because I have another function here, let's say yes, and send this down. It's another way of writing this. Now you can add as manyvariables as you want, so for this 10%, we couldalso add a variable, and to make this easier toread, you can also break it up by using Alt + Enter. So let's say I want to addanother variable for the 10%. I'm going to go down here,call that T for threshold, and put 10% here, comma,let's do Alt + Enter, and replace the 10% with a T. Calculation comes last, press Enter, and send this down. It's up to you how manyvariables you want to use. Now does it really havean impact on performance if you use LET on a large dataset? Well we're going to find out in a second. I just want to showyou another application of the LET function here,I'm just going to unhide this and take you to this formula here, where I have three conditions, and instead of showingnumbers, I'm showing three different symbols. Here, I've defined the same variable that I had before for my formula, and then I'm seeing thatif the change is greater than 10%, give me the up arrow. If it's less than minus20%, show me the symbol, which means I shouldstop and react right now, and if it's less than minus10% but still not less than minus 20%, I shouldsee the down arrow. By using VAR here, I avoid having to type this formula in many times,and in case I'm updating my references, I just needto update it once here. Now if you're wondering how I'm getting the different colors, that's simply by using conditional formatting. So if I go to conditional formatting here and go to manage rules, notice I have three separate rules here. If it's a down arrow and I go to edit rule under format, I've changedthe font color of my symbol to be an orange color. And you can see the symbols here. Now where did I get the symbols? Well the up and down arrows I got from the insert symbols tab, and this icon here I actually got from the Windows icon, so if you use the Windowskey and the dot key, you come to the Windows emojis here and then I just search for it in here, you have a lot of choices, andI inserted it on the sheet. Now unfortunately whenyou insert these emojis, they don't come with any color. To get color you canupdate their font color or use conditionalformatting like I did here. Now let's take a look at performance. Does the LET function makeyour workbooks faster? Well in this case, it actuallydidn't have an impact. Let me show you. So this is where I did the testing. I replicated some numbersfor actual and budget all the way to over 600,000 rows, and I wrote a similarformula like we had before, just instead of hard codingthe symbols in the formula, I put them in cells and I referenced them. Now the only formulathat I have on this sheet is this one here, I didn'tuse conditional formatting because I didn't want tohave any other features impacting performance, I justwanted it to be this formula. One version was to write itwith the LET function here, and second one was toactually use two variables, so I used T for the 10% threshold, I just left the 20% out, andthen the usual IF function. And I calculated the time in milliseconds and here are my time comparisons. The way I did this was to use the add-in from Charles Williams from Fast Excel, if you're interested aboutthis, I'm going to add the link to it in the descriptionof this video as well. What I did was to run a fullcalculation on the sheet, so I ran it three times, everytime I got a different result and I wrote down this number. Then I changed theformula to the other one, I ran it, and compared the results, and take a look at this, there doesn't seem to be much difference in using the LET function here. Now when we take a closerlook at this formula, we're only dealing with single numbers, so we do repeat thisformula, but the calculation is simple, so maybe Excelis really fast anyhow and the LET functiondoesn't add much value here except it makes it easier toread and maintain the formulas. But what about if youuse formulas that involve a lot of ranges, what about if you use dynamic array formulas? Let me take you to another example. In this case, we have alist of names, salary, department, and position. Our dataset goes to line 74, right, so it's not a huge dataset. What we want to do iscalculate the average salary of all the departmentsand then we want to get the average salary of eachdepartment and compare it to the average, and wewant to flag our answers, so whether a departmentis above the average, we want to see above, ifit's below the average by more than 10%, wewant to see far below, and if it's below the average but within 10% we want to see in-range. So we're going to usethe IF function here. But first off, let's start by calculating the average of all the departments here, and then let's get aunique list of departments, I'm going to use one of thenew dynamic array formulas, the unique function whichis available in Excel for Microsoft 365, Iwant to get a unique list of departments right here. So now I want to do thispart here in a single step. Before we use IF or LET or anything, let's just calculate the average salary for each department. Here I'm going to use theaverage IF or average IF(S). Now I prefer to use the IF(S) version because the moment I get more criteria, it makes it easier to updatethis existing formula. The first argument is the average range, that's where I have my numbers,those are my salary numbers, so Control + Shift down, highlight everything, Control+ Backspace to jump back up. The criteria range one,that's the department, Control + Shift downand Control + Backspace, now the last option here is criteria one. That's this one but here's the thing, I want it to spill, so I'mgoing to use hash here, close the bracket, press Enter, and I get the average salary for all the departments in one go. Since I don't want to returnthe salary but instead, I want to return the categories,and I'm going to be reusing this function, I'm going togive this function a name inside the LET statement. I'll just call it my VAL. My value is this. Now, for the calculation,I'm going to start with if, my logical test is if my value is greater than this one, 'cause we want to account for this case first, so ifit's greater than the average, I want to see the word above, otherwise, if my value is lessthan my average salary here, multiply it with one minus 10%. So my threshold hereis 10% and I need minus to highlight the averagesalaries that are far below, then I want to see far below, otherwise I want to see in range. And I need bracket, bracket, and probably anotherbracket, and that's it. Right, so for my sales, I'm in range, marketing and procurementearn far below the average, finance, legal, and managementearn above the average. By repeating VAL here,that really saves me from writing this wrong formula here over and over again, andmaking this even longer. Now what about performance in this case. Well I tested this alsoon a large dataset. Let me show you my results. So I replicated this dataset all the way to over 700,000 rows. And I did the same thingwith LET and without LET, and take a look at the difference. So this is the time in milliseconds when I run a fullcalculation on this sheet. You can see here it'snearly two times faster using the LET function thanwithout the LET function. Right, so you do notice aconsiderable difference here. So in terms of performance,the LET function is going to give you anadvantage when you're dealing with formulas that docalculations based on huge ranges like this one, otherwise you can use it as a way to make theformula easier to read and easier to manage. I hope you enjoyed thistutorial about the LET function and you're ready forit when the time comes. Now let me tell you a littlebit more about Skillshare. Skillshare offers classesfor lifelong learners. They're designed for reallife and all the circumstances that come with it. The lessons can help you stay inspired, keep learning newskills, and introduce you to a community of millions. You'll find topics like productivity, business analytics,leadership and management, entrepreneurship, graphicdesign, and a lot more. Skillshare also keeps addingnew topics and new classes so you can continue exploring and learning as much as you like. The first 1,000 of mysubscribers to click the link in the description is goingto get a two-month free trial of premium membership, soyou can continue learning, exploring new topics, withoutgetting distracted by ads. After the two months are over,if you'd like to continue with your premium membership,it costs less than $10 a month if you get the annual subscription. Many thanks to Skillsharefor sponsoring this video and supporting my channel. If you enjoy this video,give it a thumbs up and if you want to improve your skills, consider subscribing to this channel, and I'm going to seeyou in the next video. (upbeat music)