If you write long formulas in
Excel, you're going to need the brand-new Excel LET function. You might've already heard about it and already know its advantages, which are it can make your formulas easier to read, and it can improve the
performance 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 just
once 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 curious
about the second point. Is the impact on
performance 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 for
you, it's in the description of the video, check it
out when you're ready, I'm going to talk more
about 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 for
Excel for Microsoft 365, and it's currently in the preview version. Now the syntax of the LET
function 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 more
name and value combinations. Now name one is basically any name that you want to give your
number 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 a
calculation, 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 seeing
under formulas name manager, if you create a new name, you get name, and refers to is basically
what you see in here, and this can be text, it
can be a cell reference, it can be a formula. Instead of defining the
name for the whole workbook or for a worksheet, you're
actually just defining it for the formula. Now the reason you might
want 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% many
times in this formula, it would be easier to
maintain the formula as well because if that rate
changes, I don't have to go and change all instances where I had 20%, I just change it once at the
beginning of the formula. That's just one example,
but let's take a look at more realistic examples. Let's get rid of this
and do this example here. I want to calculate the
variance in percentage between actual and budget,
and I only want to show the values that are above
plus 10% or below minus 10%. I don't want to bring attention
to anything in between, just bring attention to
the bigger differences. This sounds like the IF function. Before I start and bring
in the IF function, let's just calculate our difference. In this case it would be
actual divided by budget minus one, and you can also
write 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 decimals
here, I can see it's 9.97%. So according to the
logic I want to create, this number shouldn't be visible
because I don't want to show numbers that are within this threshold. So let's go and put this
inside the IF function. If, this is my logical
test, if the absolute value of this is greater than 10%, so I'm actually going to put this in the ABS function to
get 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, and
that's quotation-quotation. Now you can also use the OR function instead of the absolute
function, so you can say or this is greater than 10% or
this 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 than
10%, when I drag this down I only see these values, right? So the bigger differences. This function is just an
IF function without LET, but the moment you start to
see repetition in your formula and they get longer, think about using LET because if you ever need
to change a cell reference, you don't need to change
it in all instances of it, you just change it once. The repetition here is this formula. So I'm going to copy this, go
to the beginning of the formula and start with LET. The name I want to give
this 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 but
now, because I've defined a name for this, I'm going
to change this to VAR, and you don't need quotation
marks for these names here. And then press enter, and
I 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 many
variables as you want, so for this 10%, we could
also add a variable, and to make this easier to
read, you can also break it up by using Alt + Enter. So let's say I want to add
another 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 many
variables you want to use. Now does it really have
an 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 show
you 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 showing
numbers, 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 that
if the change is greater than 10%, give me the up arrow. If it's less than minus
20%, show me the symbol, which means I should
stop and react right now, and if it's less than minus
10% but still not less than minus 20%, I should
see 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 need
to 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 changed
the 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 Windows
key 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, and
I inserted it on the sheet. Now unfortunately when
you insert these emojis, they don't come with any color. To get color you can
update their font color or use conditional
formatting like I did here. Now let's take a look at performance. Does the LET function make
your workbooks faster? Well in this case, it actually
didn't have an impact. Let me show you. So this is where I did the testing. I replicated some numbers
for actual and budget all the way to over 600,000 rows, and I wrote a similar
formula like we had before, just instead of hard coding
the symbols in the formula, I put them in cells and I referenced them. Now the only formula
that I have on this sheet is this one here, I didn't
use conditional formatting because I didn't want to
have any other features impacting performance, I just
wanted it to be this formula. One version was to write it
with the LET function here, and second one was to
actually use two variables, so I used T for the 10% threshold, I just left the 20% out, and
then 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 about
this, I'm going to add the link to it in the description
of this video as well. What I did was to run a full
calculation on the sheet, so I ran it three times, every
time I got a different result and I wrote down this number. Then I changed the
formula 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 closer
look at this formula, we're only dealing with single numbers, so we do repeat this
formula, but the calculation is simple, so maybe Excel
is really fast anyhow and the LET function
doesn't add much value here except it makes it easier to
read and maintain the formulas. But what about if you
use 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 a
list 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 is
calculate the average salary of all the departments
and then we want to get the average salary of each
department and compare it to the average, and we
want to flag our answers, so whether a department
is above the average, we want to see above, if
it's below the average by more than 10%, we
want 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 use
the IF function here. But first off, let's start by calculating the average of all the departments here, and then let's get a
unique list of departments, I'm going to use one of the
new dynamic array formulas, the unique function which
is available in Excel for Microsoft 365, I
want to get a unique list of departments right here. So now I want to do this
part 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 the
average 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 update
this 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 down
and 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'm
going 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 return
the salary but instead, I want to return the categories,
and I'm going to be reusing this function, I'm going to
give 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 if
it's greater than the average, I want to see the word above, otherwise, if my value is less
than my average salary here, multiply it with one minus 10%. So my threshold here
is 10% and I need minus to highlight the average
salaries 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 another
bracket, and that's it. Right, so for my sales, I'm in range, marketing and procurement
earn far below the average, finance, legal, and management
earn above the average. By repeating VAL here,
that really saves me from writing this wrong formula here over and over again, and
making this even longer. Now what about performance in this case. Well I tested this also
on 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 thing
with LET and without LET, and take a look at the difference. So this is the time in milliseconds when I run a full
calculation on this sheet. You can see here it's
nearly two times faster using the LET function than
without the LET function. Right, so you do notice a
considerable difference here. So in terms of performance,
the LET function is going to give you an
advantage when you're dealing with formulas that do
calculations based on huge ranges like this one, otherwise you can use it as a way to make the
formula easier to read and easier to manage. I hope you enjoyed this
tutorial about the LET function and you're ready for
it when the time comes. Now let me tell you a little
bit more about Skillshare. Skillshare offers classes
for lifelong learners. They're designed for real
life and all the circumstances that come with it. The lessons can help you stay inspired, keep learning new
skills, and introduce you to a community of millions. You'll find topics like productivity, business analytics,
leadership and management, entrepreneurship, graphic
design, and a lot more. Skillshare also keeps adding
new topics and new classes so you can continue exploring and learning as much as you like. The first 1,000 of my
subscribers to click the link in the description is going
to get a two-month free trial of premium membership, so
you can continue learning, exploring new topics, without
getting 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 Skillshare
for 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 see
you in the next video. (upbeat music)
