Locked lesson.

Upgrade

About this lesson

Learn to use LET to simplify complex formulas and make them easy to debug.

Exercise files

Download this lesson’s related exercise files.

Let Function - Begin.xlsx
25.3 KB Let Function - Complete.xlsx
25.4 KB

Quick reference

The LET Function

The LET Function can be used to simplify complex formulas and make them easier to debug

Instructions

Benefits of the LET function

  • It helps make formulas more readable
  • It helps make complex formulas easier to debug
  • It can make calculations more efficient

How it works

  • The LET function works by declaring pairs of variables and calculations
  • The final parameter of the LET function is the variable to return to the cell

Important Considerations

  • Each variable must be a unique name
  • Variable names cannot contain spaces
  • While it is possible to use a calculation as the final function parameter, we recommend never doing this, rather using one of your variables as the final parameter (as this makes debugging MUCH easier!)
  • The final parameter does NOT need to be the final parameter that was included in the function logic

Recommended LET function structure

=LET(

variable1_name, variable1_formula

[variable1_name, variable1_formula, …]

return, calculation,

return)

Example

  • Assume you have the following COUNTIF() formula

=COUNTIF($D15:$D30,$C4)

  • It could be written using a LET function as follows

=LET(

rngValues, $D15:$D30,

rngCriteria, $C$4,

return, COUNTIF(rngValues,rngCriteria),

return)

Login to download
  • 00:04 In this video, I want to show you a cool function called let
  • 00:07 which was introduced with Excel 2021 and Office 365.
  • 00:11 So as long as you're on those versions or higher, you should be good with this one.
  • 00:15 Now, the function that I want to take a look at here is that I've got a SUMIFS
  • 00:19 statement.
  • 00:20 And if I just go and hit F2, you can see that It's
  • 00:24 actually referring to cells A6 to A13 twice inside the formula.
  • 00:28 Now, the beauty of the LET function is that we can actually reduce this so
  • 00:32 that it only makes one call to the data source instead of two,
  • 00:36 which can hopefully help speed up our formulas.
  • 00:38 Not a big deal if we only have one, but if you've got hundreds of thousands
  • 00:41 in a workbook, this can make your performance difference.
  • 00:43 In addition, this can also make your formula more readable.
  • 00:47 So here's what I'm going to do, I'm going to copy everything except for
  • 00:51 the equals piece as well.
  • 00:52 Okay, so we've just got the original formula here.
  • 00:54 And what I'm going to do is I'm now going to go down to this cell here, and
  • 00:57 I'm going to start typing a new formula.
  • 00:59 And this formula is going to start with LET.
  • 01:01 Now, the first thing that I'm going to do is I'm going to define a couple of ranges.
  • 01:06 The first one that I'm going to do is I'm going to create one called jerseys, okay?
  • 01:10 So just give it a name, jerseys.
  • 01:12 And now I'm going to go and point to the range of jerseys here.
  • 01:18 I'm now going to hit a comma.
  • 01:20 Now, what I recommend you do at this point in time is you actually
  • 01:23 press Alt+Enter because that will actually give you a line break in your formula.
  • 01:28 It makes a little bit easier to read this.
  • 01:30 We're now going to go and put in a new range called caphit,
  • 01:35 and I'm going to grab the caphit level.
  • 01:39 There we go, and here I put another comma.
  • 01:41 And again, I'm going to press Alt+Enter.
  • 01:44 What I'm going to do at this point is I'm going to type in return, and
  • 01:48 this is the formula that I actually want to return at the end.
  • 01:52 I'm going to press Ctrl+V to put in my SUMIFS formula.
  • 01:55 And there's one more component here that's important because return is actually
  • 01:59 a name of a variable.
  • 02:00 If I had just dropped the name and just gone with the SUMIFS,
  • 02:03 I could close the parenthesis and everything would work.
  • 02:06 Because the final parameter of LET is what would you actually like to return
  • 02:10 to the cell and that can be a full formula or, I'm going to hit comma, Alt+Enter.
  • 02:15 I'm now going to type in return.
  • 02:17 Okay, so the way that this is going to work is it says,
  • 02:20 you've defined a variable called jerseys, which is range A6 to A13.
  • 02:25 You've defined a variable called caphit, which is D6 to D13.
  • 02:30 You've defined a variable called return, which is equal to the SUMIFS statement.
  • 02:36 And then the final parameter,
  • 02:37 is what would you actually like me to put in the cell?
  • 02:40 And I'm going to say, whatever you've got in the return variable.
  • 02:43 Now, at this point, I'm going to hit Enter,
  • 02:46 and you're going to see that the formula still works.
  • 02:49 The thing is, the reason it still works right now is because
  • 02:53 it's returning the original SUMIFS.
  • 02:56 This doesn't do anything for me.
  • 02:57 Now, I'm making multiple calls.
  • 02:58 But here check this out.
  • 03:00 We've defined caphit as D6 to D13.
  • 03:04 So I can actually come back in here and say, let's put in caphit,
  • 03:09 and where we've got A6 to A13, I can replace this with jerseys.
  • 03:14 And where I've got A6 to A13 here again, I can replace this with jerseys as well.
  • 03:21 So my formula is starting to get a little bit easier to read.
  • 03:24 I'm going to do a SUMIF of the caphit where the jerseys are less than or
  • 03:28 equal to G7 or greater than or equal to G8, Enter, and it still works.
  • 03:34 So this is pretty cool.
  • 03:36 Now, I can even go even more into this one if I want to,
  • 03:39 I could come back and say, all right, let's do this.
  • 03:42 Let's come right to the beginning here.
  • 03:43 I'm going to press Alt+Enter to break my formula here.
  • 03:45 So these are the ranges that I'm working with.
  • 03:48 I'm going to come down here and press Alt+Enter.
  • 03:50 I'm going to put in another variable here.
  • 03:52 So we're going to go with criteria1,
  • 03:57 and that is going to be equal to less than or
  • 04:02 equal to, and 23 comma.
  • 04:05 Gotta make sure we have that comma to fill that one out.
  • 04:09 And then I'm going to go and say alternate here again, or Alt+Enter.
  • 04:12 We're going to go with criteria2, this is going to be greater than or
  • 04:16 equal to and G8.
  • 04:21 Now that I've put both of these in place, I can come back and
  • 04:26 say instead of this entire text ring right here,
  • 04:30 what I'm going to do is I'm going to put in criteria1.
  • 04:33 And down here I'm going to put in criteria2.
  • 04:40 All right, so jerseys is A6 to A13, caphit D6 to D13,
  • 04:44 criteria1 is less than or equal to and G7 criteria2, greater than or equal to.
  • 04:50 And G8, the return that I want is this calculation SUMIF, caphit, jerseys,
  • 04:55 criteria1, jerseys, criteria 2, and finally return that calculation out.
  • 04:59 And when we hit enter, you'll notice that everything still works nicely.
  • 05:04 I had promise two things from this.
  • 05:05 One that the formula would be more readable, and
  • 05:07 I think the more time you spend with Excel, you'll see that this actually is
  • 05:11 a little bit more readable if you use well-named variables here.
  • 05:14 But the other thing that's really cool is if you're debugging something because it's
  • 05:18 not working, I want to know exactly what criteria1 actually was.
  • 05:22 So I'm going to change the final part of this.
  • 05:24 Instead of returning the return to the cell, let's return criteria1.
  • 05:28 And what do we get?
  • 05:29 Less than or equal to 23, fantastic.
  • 05:32 Let me come back here again.
  • 05:34 Let's change this to criteria2, Enter, greater than or equal to 14, fantastic.
  • 05:41 This is great because it allows me to go step by step and debug and
  • 05:45 see what every single step along the way is doing.
  • 05:47 And finally, when I've got it working, I can just put the return at the end and
  • 05:51 boom, it goes, okay?
  • 05:52 So this is why we actually define a variable called return with our
  • 05:56 calculation.
  • 05:56 We don't just skip that and go directly to the calculation for the last step.

Lesson notes are only available for subscribers.

Gift this course