Excel LAMBDA Tutorial

hi everyone kevin here today i want to show you how you can create your very own custom function in microsoft excel using something called lambda this is brand new functionality that's hot off the press and it's currently available to office insiders why would you ever want to write your own custom function well maybe you have a super long formula in excel maybe you end up copying and pasting it all over your sheet or maybe other people are working on your sheet and maybe sometimes your formula gets messed up instead you can turn your formula into a function you could give it a pretty name and then you could call it wherever you are in your workbook now i know it might sound a little bit complicated but today i'm going to break it down i'll show you step by step how you can use it so hopefully it's super clear first off i want to show you how you can get lambda and then we're going to jump through a few examples all right let's jump on the pc and let's get started if you want to follow along i've included a link in the description to this workbook that i'm going to be using also to be able to use lambda you need to be a microsoft 365 subscriber and you also have to be enrolled in the office insider program and don't worry it's easy to enroll in the program and you could even do so from directly in microsoft excel head up to the file menu in the top left hand corner and then on the left hand side go down to the option that says account and here you'll see an option for office insider as an office insider you get access to functionality before the general public and you can provide feedback to microsoft here if we click on office insider we can change the channel now there are two different channels there's a current channel and there's also a beta channel the beta channel gets access to functionality first but it might be a little less stable the current channel gets access to functionality a little bit later and things will be a little bit more stable lambda is currently only available to the beta channel so if you want to test it out click on beta channel and then click on ok now that we have that out of the way you should now have access to lambda within microsoft excel and before we jump into how to construct our own function i want to take a moment talking about the difference between a formula and a function first a formula is an equation that you can design in microsoft excel so here i have a list of all of these different cookie types and i have a quantity associated with them and i want to add all of these up i can use a formula to do this to enter a formula i'll simply type in the equal sign and i can click on the different values and i can add them up once i go through i can hit enter and i see that the sum of all of these is 30. now this is just a basic formula instead i could also use functions so how does a function different well a function is a predefined calculation so let's say i wanted to do the same thing where i sum up all these different values i could use the sum function in microsoft excel the sum function is one of the most popular functions here i enter sum i'll open parentheses and i can simply highlight all of these different input values close parentheses and then press enter here the function gives me the output of 30. it'll take all those different values add them up and then spit out a value so that's the main difference between a formula and a function with lambda i can create my own function very much like the sum function and i could call it the kevin function i could call it buy cookies and really i could give it whatever name i want the great thing about creating a function is i could then call that wherever i am in my workbook and i don't have to know any coding to do it so how do we create our own function well let's jump onto the next worksheet and let's create a function this brings us to the next sheet and we are going to construct our very own custom function this sheet contains order information for the kevin cookie company here you can see some of the fine retailers who carry our delicious cookies you could also see how many boxes of cookies they ordered and you can see the price that they pay and remember these are wholesale prices to calculate the price we simply take the number of boxes ordered and then we multiply it by two dollars and fifty cents and i know the wholesale price is a lot cheaper than the retail price but hey we need to make a margin too now i want to turn this into a function right now this is simply a basic formula to turn this into a function let's click into cell d7 and here we're going to use lambda to create our very own function first enter the equal sign and then let's type in lambda once we type in lambda let's open the parentheses down below we'll see a helpful hint that helps us construct our function right now it says enter either a parameter or a calculation basically a parameter is the value that we want to feed into our function in this case for this function i want it to be the number of boxes ordered so for the parameter i'm going to call this boxes ordered next i'm going to insert a comma and now i can once again enter another parameter so let's say i wanted to pass two values in but in this case i'm just simply passing in boxes ordered so i'll leave the parameter as just one item next i need to enter a calculation and for the calculation i'm going to take the boxes ordered here i could see a helpful hint that this is one of the variables that i could use i'll click on boxes ordered and then i want to multiply this by two dollars and fifty cents next i'll close my parentheses and i can hit enter now it's not working yet simply what we're doing on this sheet is we're going to test to make sure that the formula works as we expect i'll click on this again and here i can see my formula next i'm going to enter another parenthesis and now i could enter a sample value in so i want to enter 250. this is going to be the parameter that i pass in so basically when i enter the 250 this will go in as the boxes ordered value next i'll close the parentheses and i'll hit enter here i can see now that my function will return 625 and that matches exactly with what my formula says over here so i know that my lambda function is now working properly now i want to be able to call this function wherever i am on the sheet so how do we do that well let's get rid of this test value at the end so i'm going to remove the 250. next let's highlight this function right here and let's cut it i'm going to press ctrl x you could also simply do control c now that i've cut the formula let's go up to the top ribbon and let's click on the tab that says formulas once we click on formulas there's an option in the middle called the name manager let's click on that this opens up the name manager and next let's click on new this opens up a control where i can now create the name of my new function i'm going to name this cookie price down below i can define the scope of this function i could call the function from all the different sheets within my workbook or i could specify an individual sheet i want it to be available everywhere so i'll select workbook down below i can also type in a comment for my function this is a good practice especially if you have a lot of functions and maybe you won't remember what these different functions do type in a good descriptive comment down at the very bottom this is where we are going to paste in the lambda function that we just created i'll paste that in and we are all set to go now next let's click on ok this brings me back to the name manager and i can now see that there is a new function called cookie price next let's click on close i'm now back on my sheet and instead of entering a formula to calculate the price i can now use a function in cell d7 i'm going to type in equals and now i could type in cookie price here i see now that there's a new function called cookie price when i click on this i can now open the parentheses and once again the value that i want to feed in is the number of boxes ordered i could click on boxes ordered as the input value to this function next i'll close the parentheses and hit enter here you can see that the function returns the value 625 so it's exactly the same as this formula except this is now a function that's returning this value here i could go down and i can expand the function to all these different rows and here now i see the cookie price now in that example it was pretty basic and you probably wouldn't create a function for something like that but now we're gonna get a little bit more complex now my pricing analysts at the kevin cookie company have told me that we need to offer some discounts to encourage people to order more so here we came up with this discount strategy if you order a lot of cookies so more than 150 boxes will give you a 15 discount if you order a medium quantity let's say more than 100 but maybe less than or equal to 150 we'll give a 10 discount otherwise you get no discount 11 7 convenience stores you got to order more boxes if you want to get a discount 21 boxes just isn't going to cut it to be able to support this discounting strategy i really had to brush up on my excel skills when i click and sell c7 here you can see the function that i entered to be able to figure out what the price is here i have an if function and then i follow it by a nested if function and we finally get the price that we charge the retailers now for every single row here i enter the function again and again and it's really prone for error especially if i have some of my employees come in i really don't have confidence that this function will stay as it is right here instead i'd like to create a simple function where they could simply type in the boxes ordered and it'll spit out the price so once again let's use lambda to turn this into an easy to use function to do that first let's click into the formula that we want to use and i'll simply highlight this and then i'm going to copy it next i'll click on escape next let's go over to cell d7 and once again we are going to use lambda to use lambda once again let's type in equals and then type in lambda next let's open the parentheses and once again we need to pass in a parameter once again my parameter is the boxes ordered for now to simplify i'll simply call this boxes next i'll insert a comma and now we need to type in the calculation now i already copied the calculation so let's paste that in i've now pasted in the calculation and i need to add one more parentheses to close this out now one thing you'll notice with my function that i copied or the if function it uses a reference to cell b7 now i want it to refer to boxes instead this is the variable so in place of b7 let's paste in boxes or the parameter throughout this calculation i've now pasted boxes in all throughout here once again i have my closing parentheses and this all looks good now just like we did before let's test this to make sure it works properly i'm going to insert another parentheses and here just to test it i'll enter 250 boxes just to make sure it works out to the same price i'll type in 250 close parentheses and hit enter here now i can see that the price comes out exactly the same so that shows that my lambda function here is working properly i'll remove the 250 and then hit enter and now let's click into here and once again i'm going to copy this and now let's create our function once again to create a function let's go up to formulas on the top ribbon and then let's click on the name manager this opens up the name manager and let's click on new for the name let's type in cookie price with discounts once i type in the name i'll leave the scope to workbook and i'll type in a quick comment down below let's highlight this value and now let's paste in our lambda function that we typed in in the sheet and then we could click on ok i now have a new function name called cookie price with discounts next let's click on close here now back on the sheet i can now enter equals and i'll start typing in cookie price and here i see with discounts i can click on that i can open the parentheses click on 250 boxes close parentheses and hit enter and here you see my function is working perfectly so this is a lot easier here when i click into here i see this big nasty long function instead when i click over here i see this very beautiful clean function now it says cookie price with discounts here i can see that i pass in 250 and it simply gives me that result i have pretty high confidence that my employees can carry this out along with trusting my employees to be able to carry this out now let's say that maybe my pricing guide has changed so instead of giving a 10 discount maybe we want to experiment with let's say a 12 percent discount now if i had just used all these individual formulas i'd have to go back and i'd have to enter the reference or at least update the reference with this lambda function it's easy i simply update the function and then any place where i call the function is automatically updated i could simply click on the name manager i could go back to the cookie price with discounts and here i can see the discount so here the discount is 0.9 i can now change that to 0.88 now that i have my new discount in place i can click on close my function is now updated and it now factors in the new discount amount so these are two massive benefits of using functions you make it easier for others to use it's easier to parse when you're reading through perhaps complicated functions and as another side benefit if you need to make updates to it any place that uses that function also gets updated automatically hopefully you're starting to see some of the power you get from lambda now before you think that's it lambda does have a few more tricks up its sleeve you can also do recursion with lambda so what does that mean well just like in programming you have the concept of looping to make it real i want to provide a practical example at the kevin cookie company we've recently been brainstorming some new cookie names and at the kevin cookie company when you look at our menu we refer to things like chocolate chip cookie or snickerdoodle we thought we might try to spice things up by making the names more exciting instead of calling a cookie just chocolate chip well let's call it double chocolate chip what's the difference it's really just a chocolate chip cookie we also call snickerdoodle deluxe we also threw sweet in front of sugar of course sugar is sweet but it sounds like just a more impressive name and then we also have our infamous sriracha mint supreme cookie one of our employees isn't a big fan and they tried to hijack our brainstorming activity by throwing numbers in the name so rude of them with lambda and setting up a recursive function i can remove those so how does this work and how would you logically do it well let's say we were to take this text right here and i wanted to remove each number well of course i could go through manually and remove each number but let's say i had hundreds and hundreds of rows i'd much rather use a function so logically to remove the numbers well first off i'll look for the number one if i find number one in this text i'll remove it once we've looked at number one next we'll look at number two if we find number two we'll remove it once we've done that then we'll go to three and we'll work our way all the way through nine and then once we've removed or checked for all of these numbers then we'll get the text and so i wrote a function that does this next we'll type in equals and then i'll simply type in clean name once i type in clean name i'll open my parentheses and the input or the parameter is this text and next i'll enter a comma and this is the text or the input that i want to exclude so i'll select this i'm going to make it an absolute reference close parentheses and hit enter now nothing exciting happened because double chocolate chip had no numbers in it here though when i pull it down once i get down to the sriracha mint supreme cookie you see that it removed all of the numbers previously in microsoft excel this would have been extremely difficult to do so using recursive functions i can now do this or calling a function within a function okay so that's pretty impressive but how does that actually work if we scroll down the page here we'll see the lambda and let's walk through this to see how it works here i once again just like we've been doing all along we enter equals lambda and then i pass in the cookie name so here for example we're sending in sriracha mint supreme cookie next i also enter all of the characters i want to exclude so those are my two parameters that i'm passing in so in this case it would be one through nine next for the calculation i'm going to enter in an if statement here there's an if and first it does the logical test so are there any more characters to exclude so it's looking up here and yes we still have one through nine that we have to check so that comes back false so in this case if it was true just pass back the cookie name but let's say with the sriracha mint supreme cookie it's false there are more characters to exclude so then it goes to the next portion of the if statement and here once again i call the clean name function so basically i'm calling the function within the function and then here within clean name what it does first is it says okay well let's first check the cookie name and let's see if the number one appears if the number one appears it removes it from the name then for the second portion of it once it checks for one well then we've already checked one so it removes one from the set that's in essence how this function works and it simply goes through 1 2 3 all the way through 9. if i scroll down the page even more here we can see an example step by step how it's working or how the recursion works so here it's going to go through multiple times in fact that's going to go through ten times to clean up this text so for the first cookie name here we have four five three and one appear and so what it'll do first is well on the first iteration it'll check for the number one so here it removes one and now we have two through nine remaining in the next step it'll check to see if two appears and if it finds two it'll remove it but there is no two so then it simply works its way through all the numbers until the name is clean once the name is clean there are no more characters to exclude so now it simply passes back the cookie name so this is just a very simple example of how you could use recursion in microsoft excel and this is something that hasn't existed in functions before in excel so this really gives you a lot of power alright well that was a quick look at how you can create your very own custom function in microsoft excel let me know down below in the comments is this a function that you see yourself using if you enjoyed this please give it a thumbs up to see future videos like this hit that subscribe button and also if you want to see me cover any other topics in the future leave a note down below alright well that's all i have for you today i hope you enjoyed and as always i hope to see you next time bye [Music] you

Channel: Kevin Stratvert

Views: 33,640

Rating: 4.9119496 out of 5

Keywords: kevin stratvert, excel, lambda, custom function, function, recursion, recursive, formula, formulas, functions, create function, office, office insider, insider, microsoft excel, excel tips, excel tricks, new, new feature, new features, beta, loop, looping, custom, lambda in excel, basic, no code, no coding, code, macro, vba, spreadsheet, workbook, worksheet, repeat, repeating, microsoft 365, 365, microsoft, microsoft office, channel, name manager

Id: yDNX7V0eZ8U

Length: 18min 41sec (1121 seconds)

Published: Wed Dec 16 2020