Instructor: All right, I've got an expert-level pro tip
for you, this is a fun one.
We're gonna talk about the LET Function
and I want to give you a little sneak peek into LET,
it's not a dynamic array function,
but it is a brand new addition to Excel,
and it can really come in handy
when you start building these complex nested formulas
like the ones we've been creating in some of these demos.
Now, LET works a little bit differently
from any other formula you've ever used.
What LET does is it allows you
to actually declare variable names,
assign values to those variables,
and then use them within calculations all
inside the context of a single formula.
So it's a great tool for writing very clean,
user-friendly formula syntax,
and it can help you write complex formulas
in a way that you typically see
with object-oriented programming languages
like Python, Java, VBA, Ruby, and so on.
So here's what the syntax looks like.
You're gonna start by declaring the name
of your first variable.
Note that it must begin with a letter.
Then you're gonna apply or assign a value
or calculation to that variable.
Note that the variable only exists
within the context of this formula.
You can't call that variable
or reference it outside of this formula.
From there, you can either define a calculation
that uses the variable
or variables that you've declared,
or you can name or declare additional variables
if you'd like to use more than one.
And then this pattern kind of continues on.
You can declare multiple pairs of variable names and values.
So here's what this looks like.
This may not be the most practical example,
but in this case we're defining a new calculated field
in column E called Profit.
And to do that we're using a LET formula,
where we are defining a variable called Sales,
assigning the values from cells C2 through C10
to that Sales variable.
We're then defining a second variable called Margin,
assigning the values from cells D2 through D10 to Margin.
And then our final argument here, our calculation,
references both of those variables,
multiplies them together, returns the Profit.
So quick pro tip here, LET is commonly used
to write clean, efficient, and user-friendly formulas.
You're not gonna use it all the time,
but in cases where you have very complex nested formulas,
it can be a great tool to improve readability
especially if you're sharing workbooks
with other users who may not have the full context.
So with that, what I'd like to do is jump back
to our Dynamic Array workbook.
I wanna revisit that complicated CHOOSE function
that we wrote in the last demo,
and see if we can translate that using a LET function
to make it a little bit more readable and user-friendly.
So let's go ahead and give that a shot.
All right, so if you'd like to follow along with this one,
it's gonna be tricky, take it slow.
We're in the Dynamic_Array_Formulas workbook
and we're still in that last tab here, CHOOSE and LET.
And if you remember, right here in J3,
we had finished our last demo
with this beast of a formula, right?
We're combining SORT with CHOOSE with UNIQUE
and AVERAGEIFS to create this single-sorted array
based on a calculated average salary field.
And if I were to take this workbook
and share it with my colleague,
they would likely look at this formula
and have no idea how to follow it.
It's complicated, it's messy.
There are a lot of nested functions,
a lot of different cell references here.
It just really takes a long time to wrap your head around it
and reverse engineer what's actually going on.
So what I wanna do is preserve this version of the formula,
but basically copy and paste it here in column M,
and see if we can rewrite it using LET to see
if that helps with readability.
So first things first,
before I copy and paste this formula
because I'm moving it,
I do need to care about my cell references.
So any reference that points to our original data array,
I'm gonna use the F4 shortcut just to lock it in,
so that those references don't slide or shift
as I copy and paste this formula into column M.
So press Enter, nothing should change, that looks good.
Now we can copy that formula,
paste it right here in M3.
And there we have it, we got the right values,
so far, so good.
Now because we're gonna need to essentially rewrite this
from scratch, here's a little tip for you.
I'm gonna take this version of my formula,
I'm gonna copy everything, press Enter,
and then just down here kind of beneath my values,
I'm gonna type a single apostrophe,
and then paste that formula, and that single dash
or apostrophe or whatever you call it is telling Excel,
"Hey, just read this as text,
don't treat it like a formula."
And that's gonna allow me to just glance down
and see it here and have it as a guide,
which we can delete later.
So let's go ahead back to M3,
and it's actually gonna be a little tricky
to try to keep these components.
So now that I have that text version
of the formula down below, I'm gonna delete this one.
We're actually just gonna kind of write it
from scratch using the LET approach.
So one tip, when writing LET is not
to just extend the formula on the same single line.
What we're gonna do is use line breaks using Alt + Enter
to make it a bit more user-friendly.
So because of that, let's drag our formula bar,
give us a little more room to work with,
and that's gonna help us out.
So we're gonna start with a LET function.
We're gonna open the first parenthesis.
And now here's where I'm gonna use my first line break
with an Alt + Enter.
And remember, the first thing I'm gonna do inside
of my LET function is define or declare all of my variables.
In this case,
there are three variables we're gonna want to declare.
First variable is our list
of industries right here in column G.
The second variable is our average salaries,
which we've produced here in column H.
And then this is the tricky one,
the third variable is that joined array
that we created using the CHOOSE function
and referencing those two spill ranges,
industries and average salaries.
So we're gonna start by declaring our first variable,
which we can name anything we want.
Let's call it Industry, you know, that's user-friendly.
And now we assign a value to that variable.
So how are we defining this unique list of industries?
Well, we're doing it with the UNIQUE function.
So I could either reference this
and type it out from scratch
or it could actually reference the cells again,
let's go ahead and do that.
It's gonna be the unique list of industries from column A.
So that's A3 through A842.
Close the parenthesis and let's scroll back up.
And that's all we need to do
to declare the Industry variable.
So I'm gonna press a Comma + Alt + Enter
to enter the next line.
And now the second variable we need to declare,
we're gonna call AvgSalary, comma,
and this is where we define what average salary means.
And this one's a little bit tricky,
but it's essentially this whole AVERAGEIFS function
right down here, so AVERAGEIFS,
and we're gonna be averaging the AvgSalary values
in column D.
And the criteria range, just like before,
is our list of industries A3 through A842.
And the criteria itself,
this is where we would've referenced, you know,
our spill range here.
We need our unique list of industries here,
but we've already defined that as a variable.
And I can call that variable right here
inside the same formula.
So I can just type Industry, close the parenthesis,
press a comma, and now our average salaries
are declared and defined.
Now I'm gonna Alt + Enter,
and we're gonna declare our third variable,
which is that joined array.
We can call it something like Array,
and this is where the CHOOSE function comes into play.
Because remember, we needed to use CHOOSE
to join those Industry values and those AvgSalary values
into one cohesive array.
To do that, we used an array constant, one comma two,
close the curly bracket,
and the first array was our list of industries,
which we've declared as a variable,
and our second was our list of average salaries,
which is also declared as a variable.
And that's all we need for the CHOOSE function.
And then last step, we're gonna Comma + Alt + Enter down.
This final piece is where we actually input the calculation
that's gonna use those variables
that we declared and produce the formula output.
So here our actual calculation now
that we have all these pieces is actually pretty simple.
We're gonna SORT that array that we defined
in the line above and our sort_index is the second column,
sort_order is descending with -1,
close off two parentheses,
and in theory, this should do the trick.
So cross your fingers, press Enter, and there you have it.
Look at that, we get the exact same results
as our sorted array here
that we had built using SORT, CHOOSE, UNIQUE, AVERAGEIFS.
We're using all of those same components,
but now we're writing it as part of a LET function,
which makes it much more clear for users to look through
and say, okay, we're assigning a variable called Industry,
which is the unique list of industries from column A,
we're declaring a variable called AvgSalary,
which is using an AVERAGEIFS function.
We're declaring a variable called Array
that's combining them together with CHOOSE,
and then we're finally sorting
that array descending based on the second column.
And not only is it clear and user-friendly,
but it's also easier to modify as needed.
So instead of the AvgSalary, for instance, by Industry,
what if we wanted to change this to the maximum salary?
Well, now that we have these variables declared
and we're using these line breaks to make it very clear,
all we need to do is change these AvgSalary variables
to MaxSalary
and change the AVERAGEIFS function to a MAXIFS function,
press Enter, and there you go.
Now instead of averaging the salaries by industry,
we're pulling out the maximum salary instead.
And there you have it, quick and easy change.
So we can go ahead and get rid of this reference down below,
and there you have it.
That's how we can use LET to simplify complex formulas
and make them clear and user-friendly.