Exciting New Excel LET Function - Comprehensive Guide

the new let function enables you to declare variables and intermediate calculations inside of a formula it's like the Dex file function or let inside a power query and if you're familiar with PowerPivot power query or programming they'll understand these terms but don't be put off let is a dead easy function to learn and it improves the readability and performance of your formulas now let is currently in beta for office 365 users on the insiders Channel so you may not have it yet but it's an exciting new function so I wanted to share it with you the let function syntax is straightforward consisting of name and value pairs and then a calculation if we take an easy example let's say our first variables name is X and X is value is 5 we can continue to add more name and value pairs as we need so we'll give our second variable name y and y's value is 10 and then we can finish with a calculation X plus y and as I type in the variable names you can see they appear in their telethons so I can simply tab to select that close parenthesis and press Enter and you can see my let formula returns 15 the key here is you can add more name and value pairs so I've got 1 2 as you need but you will always have an odd number of arguments because you've got this final calculation now the value arguments in this example are numbers we've got 5 and 10 but you can also use texts or formulas or expressions arrays cell references boolean values or defined names let's take a look at another example one of the main reasons for using let is to improve calculation efficiency often we'll reuse the same calculation inside of a single formula we used to see this regularly when handling errors returned by vlookup however the if error function introduced in Excel 2007 solved that but if you ever find yourself repeating a calculation inside of a formula you know it's time to use let for example here I want to calculate a forecast for sales for the next period I've got a simple rule where if the total sales are greater than 100 then I'll expect a 5% increase but if total sales are less than 100 I'll expect a 3% decrease so in this F formula you can see Excel is performing the same sum 1 2 3 times in actual fact it will only perform it twice once for the logical test and then that logical test will either result in a value that's true or a value that's false so one of these will be calculated but that's double the work for Excel so with let we can have the work Excel has to do let's take a look first I'm going to declare a name for the sales now I like to build my let formulas up in the formula bar the first thing we're going to do is press Alt + Enter to start a new line in the formula it's just going to make it clearer for when you're building the formula as well as when you read it back later we're going to use one line for each name and value pair so the first name is sales current year or CY for short and this is simply the sum of these values here close parentheses comma alt enter to go to the next line here's our I'm going to do my final calculation so if sales current year and you can see it's popped up in the intellisense I'm just going to tab to enter it saves me typing the whole thing if they're greater than 100 then sales current year times 1.05 otherwise sales current year times 0.97 close my F close let and press enter now with this formula Excel only calculates the sum once and then it reuses it here and or here and here and while the formula is slightly longer than the if formula it's twice as efficient so if you ever reuse an expression inside of a formula that's when you know it's time to use let now obviously an example like this the efficiency gain is immaterial but if you can imagine having this type of formula over thousands of rows it's going to start to make a difference now here I have a table of data and I want to be able to toggle between different types of aggregations whether it's total or some minimum maximum or average I actually created this example back in 2016 and I'm updating it here to use with the let function the form control buttons output to cell f6 so as I select a different button it returns the number of the selected button I'm going to use the subtotal function to aggregate the data you could also use the aggregate function if you prefer but I'm going to stick with subtotal now you can see the first argument in subtotal allows me to specify the aggregation method but I need to convert the value returned by the radio button here to the aggregation type numbers in the subtotal function we can see the numbers are 1 for average 4 for max 5 for men and 9 for some so I need to convert 1 2 3 and 4 to their respective numbers and I'm going to use the choose function for this equally I could use the switch function so choose requires an index number and I'm going to use this cell here for that which is the output of my radio button so with the first radio button selected it returns number 1 and 1 is total which should be number 9 in my subtotal list so 1 is 9 2 is min that's number five for subtotal 3 will be max that's number 4 for subtotal and average is number 4 which is number one for subtotal so close parenthesis and you can see at the moment it's returning nine which is the sum as I select a different radio button it returns the relevant number for subtotal so that's how I going to use choose I'm just going to delete that because we don't need it there I just wanted to explain how it works I'll get started with let's again let's pop up to the formula bar odds enter to go to the next line so I'm going to specify my range it's going to be these cells here both enter then I want to name the cell that contains the selection in the radio button so I'm going to call that select and that's this cell here I'll enter now I'm ready to use choose to convert the radio button number two these subtotal aggregation types I'm going to call it AG and we need to choose and here I'll use a previously declared name for the index number and that's select and notice again it appears in the intellisense so I can just tab to enter it the next argument will be the numbers so remember one is total so that's nine then min is five Max is four and averages one close twos comma old enter lastly I just enter the subtotal formula which asks for the function number we'll remember we've declared that up here with Ag so I can just copy and paste that in if I like comma what's the reference well that's the range we declared earlier closed subtotal close let and press Enter now when I choose a different radio button you can see my formula automatically updates now as a finishing touch be nice to add a dynamic label using choose so again we're going to reference this cell here and all I need to do is type in the different text that I want returned depending on the radio button selected so minimum maximum and average closed parenthesis and I'll open this to small text so that it has a nice label that explains exactly what's being displayed in the cell when you use relative references in a let formula you can copy it to other cells and a habit automatically update just like any other formula for example here I want to summarize the data in this table by brand and I can use a some F's formula for this but let will make the formula easier to understand now notice that the dates in row three are proper date serial numbers and they represent the first of each month I've simply formatted the font just only show me the month and year now I'll start by entering my let alt enter to move down to the next row so first of all I need to declare the month start so we'll call that month start and it's this cell here now I need to f4 to absolute the row number because when I copy it down I wanted to stay fixed on this row for the month but when I copy it across I wanted to update to each month respectively so that's month start I also need my month end and I can calculate that with the end of month function again referencing this cell here remember it contains the first of the month so I want the last date of the current month so I only want to move forward zero months ie the current month comma all to enter to the next row here I'll give my brand cell a name so that's this cell here but I want the column reference absolute this time so that when I copy it across it stays anchored to column F comma alt-enter so now that I have my criteria declared I'll use the filter function to return the data for January for brand a datum I'm going to call this brand range and like I said we're using filter so what array are we filtering well I want to return the sales values so we're going to filter the sales column of table 1 comma now the next argument for filter must evaluate to an array of boolean true and false values I have three criteria so I'm going to wrap each one in parentheses and then I'll multiply them by one another because they're and criteria so the first one is to check that the brand matches the brands declared in cell f4 which I've named brand close my parentheses x the next criteria which is to check that the date is greater than or equal to the month start close parentheses x checking that the date is also less than or equal to the month and close parentheses so the multiplication sign will treat all of these criteria as and criteria so filter the sales whether brand and equals the brand in cell f4 and the date cell is greater than or equal to the month start and the date cell is less than or equal to the month end so that is the filter range close my parentheses on filter comma o enter now all they need to do is sum the brand range close some close let's press Enter so if we look at the formula in one cell I can understand everything that's going on I can see what cell the month start is in I can see how the month end is calculated I can see the cell the brand is in and so on and I can read the formula and this makes let a really efficient formula to work with I don't have to go and reference another sheet or look in the name manager to see what the name represents I can see it all in one formula now all I need to do is copy this formula to the remaining cells and because I use some relative references you can see if you look in the formula bar as I move through the cells that those relative references update accordingly now one thing I do want to point out here is when you work with tables structured references like this it's really important that you copy and paste the cells as opposed to using left click and drag to autofill when you use left click and drag the references become relative for the table so if I release that I get cow carers and if we look at this formula can see it references table 1 sales table 1 brand as I move across now it's referencing table one date and table one product category and that's because it's shifted across one with each column that I dragged it to so I'm going to control Z to undo there are ways to make these references absolute so you can left-click and drag but it's a bit manual and it's easier just to remember to copy and paste instead of left clicking and dragging now you may have already noticed that let function can accept arrays as input but it can also return arrays as outputs for example here I want to return a list of the top three brands and I need an array of numbers for the top three so let's start with equals let okay again go up to the formula bar so I need an array of numbers to rank the top three so I'm going to call this my rank array and we just simply type in one two three with semi-colons and curly braces to define those values as an array alt enter now I want to specify my brand range which is going to be this range of cells here comma up enter and then my sales range now you don't strictly have to name everything that you use in your final calculation but it is best practice to do so and that's why I'm doing it here we've got our rank are a brand range and sales range lastly I just need the calculation which is going to use index and match so index want to index the brand range matching the largest values in the sales range returning the top three with the ranked array close parentheses on large and I'm looking up my match function needs to look up a rare I'm looking up the sales range and I want an exact match close parentheses on match close parentheses on index and finally on let and when I press ENTER you'll see the results spilled to the cells below because it's returned me an array of three values so there is an example where there's an input of value which is an array and an output which returns an array and lastly we can use X lookup to look up the brand and notice I've selected the whole array and it's given me this array range operator that way it will dynamically update if this array changes but also it will spill as you'll see so I'm looking up the brands in the brand range here and returning the sales close parenthesis and X lookup also spells the idea of the let function is that you name your variables and then use those names in subsequent values or the final calculation step but there's nothing forcing you to use all of the names in the let function so looking at the left formula here it names cell c4 as a selected brand and c5 is selected category and then the range is the sales column of table one that we saw earlier and finally it uses summits to return the sales value for the selected brand and category however instead of putting some ifs in this final calculation argument we can declare it as a name so let's call it calc and I'll add another comma I also need to add a comma and now add my final argument which is simply the name of the value that I want returned which is my summit so I'll press ENTER I get the same result because the last argument is simply returning this name and that's an important point to remember because it enables us to troubleshoot without getting rid of this final calculation so let's say for example instead of returning the calc I wanted to check what this range name was returning so I can simply type it in and press ENTER and what happens now is it spills the results of the table 1 sales column to the cells below I haven't lost my final calculation it's still here and when I'm happy with all my troubleshooting I can simply replace the last name with the formula that I want returned so you can see there are control sets one do you can see there this calc name hasn't been used in my let formula it's sitting there it's just dormant at the moment so you don't have to use every name that you declare inside of your let although it doesn't really make sense to keep names in side of let if they're not going to be used really this is only useful for when you're troubleshooting so let's put calc back and our formula is complete I hope you're excited about the let function please post in the comments how you plan to use it to improve formula readability and efficiency you can download the excel file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to my channel and don't forget to click the bell icon to be notified when I release my next video thanks for watching

Channel: MyOnlineTrainingHub

Views: 135,867

Rating: 4.9529133 out of 5

Keywords: excel let function, let function

Id: o3ysGnl8ieM

Length: 18min 25sec (1105 seconds)

Published: Wed Apr 15 2020