Today I'm going to share with you a new function in Excel that allows you to create functions. A function to create functions. It's called the Lambda function. This is currently only
available in the Beta channel. This is a function that's both for Excel
beginners and experts. Because even if you don't end
up creating your own Lambdas, you need to know what they are because you'll probably end up
using someone else's Lambda. I'm really curious to get your
impression of this function so comment below as you're watching this and let me know what you think. Personally, at first I
thought it's a nice to have and then I saw Recursive Lambdas. It changed everything. Now I'm no going to talk so much because you need to see this. (upbeat music) Now this is going to blow your mind. In this example, we have a list of names and the skills of each person. Now, unfortunately, we have mistakes here because some people put in office 365 where they should have put
in Microsoft 365 instead or Power Point with a space
where it should be PowerPoint. So here we have a list of before and after and we want to get this corrected. So notice that some of them are going to require multiple corrections because they have made mistakes
for every single skill. What we want to do is go to each cell and loop it through the before, whenever it comes across a match, it should take the after version of it make the replacement and put
it through this list again. So it's similar to a "For
Next" or a "Do While" in VBA we're going to do this with a formula, check this out. I've already written the Lambda for this. My function is called "Mega Replace". So the moment I start to
type it, it shows up here it needs the texts that I want to replace then it also needs the
start of my before table and the start of the after one. Now I going to fix this
because this is a table and the moment I press enter right here beside a table it's going to add this
as a part of the table. Now, when I do this press enter take a look at what's going to happen. Wow. Everything is replaced. Look at this, Officer 365 PowerPoint, Microsoft 365 PowerPoint
written correctly. The one where we had a lot of mistakes that was this month for example, it's all corrected. This is an example of a Recursive Lambda. Now we're going to get to the
engine of Lambdas in a second but let me show you a
practical example of a Lambda that's not recursive. So let's see in your workbook, you generally need to do a certain task which is to sort certain fields by the sum of another
field in descending order. So for example, you
want to sort department by the sum of salary. Kind of like doing a sum ifs on your salaries by department and then sorting them just by department. Because notice that the sales
department is repeated here so are the other departments. Now you can create a
Lambda for this so that all you have to do is type
in your own custom function. I called mine "Sort by Sum". This one just needs two arguments, the column that you want to sort, so let's say this and then the column that has your numbers that you want to do a sum ifs on and then you close bracket press enter. I have departments sorted
by the sum of salary. And if you don't trust me,
we can double-check it here. I'll do a sum ifs quickly. My sum range is this, my criteria range one is this and my criteria one is this and hash to make it spill and press enter, notice I have the correct sorting. And because this is the Lambda I can apply it to anything I want as long as it has a similar structure. So sort by position, I go to sort by sum and then I take the position column and then the salary column, close bracket press enter and I have my positions sorted by the sum of salary in descending order. So this is already been programmed that it always gives it
to me in descending order. And the reason I did
this is this is something that I might use often in my workbook. Now, how are this' set up? Well, let's cover the basics
of Lambdas right here. What are Lambdas to begin with? These are just functions that you create. These functions act like machines. Once you've created that machine you can put through it other ingredients and reuse it as often as you want. This means that you don't have to recreate
the machine every time. So let's do an example here. Let's say you have this list of prices and I want to calculate
a 30% discount rate. So what would be the price
after a 30% discount. To do that with a formula I just go B4 multiply it with one minus 30% close
bracket and press enter. If this is a formula that I'm using a lot I could turn it into a machine this way I can reuse it with other similar ingredients. So how would this look as a Lambda? Well, the great thing
about creating your machine with Lambdas is that you can create it directly
in your formula bar. It's a function. So I'd start with typing in Lambda and then you need
parameter or calculation. So basically each argument is either a parameter or a calculation. Well, any where you see cell references, these are going to become your parameters. So here I'm just referencing B4, B4 is going to be a parameter and you can call this
parameter anything you want. Now I'm not really creative with names and I don't like to waste
time thinking of good names so I usually just go
with A B C D or X Y Z. So I'm going to call the parameter B4 which is my cell reference, A. This is my calculation. And I have to mention the
parameter at the beginning. So it's A comma so then I go to the next argument and it's always that the
last argument of your Lambda is your calculation. Anything before are your parameters. Now I've created my machine. I need to close the bracket
to close the Lambda function. So what happens when you press enter? Well, before you do that, just take a look to see if you're
subscribed to this channel. If you aren't yet, consider subscribing. Let's take a look, when I present her, I get an error. Why? Because I've just created the machine but I'm not putting anything through it. I need to put an ingredient in there. To test your machine on the grid here, you can do it by putting
in a cell reference. To do that at the end of your Lambda function open the bracket and do a cell reference, that's going to act like your parameter. So since I only have one parameter this B4 is going to become A and it's going to get
put through my machine. Now, when I press enter, I get what I got back before. Now, obviously this formula right now is not so useful. It's better just to
have what I had before. The advantage of machines like this, like the Lambda function is that you can give this a name and then you can reference
this anywhere on the grid. And we're going to see this in a second but I want to show you a Lambda that has two parameters. So let's say instead of 30% you want this to be dynamic as well. So you can turn it into a parameter. I'm going to call this one B. Now because this machine
needs two ingredients A and B, I have to have them as
parameters on the side here. So it's A comma B comma and last is my calculation. Okay, now, what's going to happen when I press enter? Let's check it out. It's no going to work. Why doesn't it work? Well, the machine needs A and B to work. I've only given it one thing, I've only given it A. So what is B? Well, it's 30%. So you can either do a cell reference or directly type it in. Now, when I pull this
down or send this down, I get the same results. Okay, so how can I turn this into a name that I can reuse? Well, once you test that your
Lambda works on the grid, you can copy it, it's actually this, copy
that equals as well. Do just copy the machine part of it and then you go to name
managers under formulas, Name manager, you create a new name. You can call this anything you want, I'll do CalcDiscount and then paste in my
machine in here, and okay. Now, whenever you use
more than one parameter, I recommend you add in the comment what each parameter is used for. So something like A equals your value and B equals your discount, and okay. Here so now we have this as a Lambda. This means that whenever
we need to call this all we have to do is type in the name of our function which was CalcDiscount. And it needed two things which was this and our discount rate. I'm just going to put
the discount rate here, let's fix it. Close bracket press enter, I have nothing so it's 30. If I put 20%, now I have a 20% discount. This is something I can
reuse anywhere in my file. Now let's take a look
at the practical Lambda I showed you before. How can we create that? Well, the first step to creating Lambdas is to try it out on the grid. This works for Lambdas
that aren't recursive. All you have to do is figure
the function you need first then turn it into a
machine, basically a Lambda and then give it a name in Name manager so you can reuse that
machine anywhere you want. In this case, we want to sort any column that we want by the sum of another column. So here, for example, I want
to either sort the departments or sort the position in descending order based on the sum of salary. So I'm going to pick
position as my Guinea pig. I'll try to write up
the function that works and then I'm going to
turn it into a Lambda. So I'll start from the
inside of the function and work outwards. I need this sum ifs function. The sum range is this one, my criteria range, that's position here and the criteria itself well, it's the unique values
of my position column. Now, when I close and close
sum ifs and I press enter, my results are going to spill because it's giving me the
sum of each unique position. Now because I don't want
to get the values back I actually want to get the position back. I can put this inside this sort by function. Sort by needs an array, the array is what you
want to see in the end. I only want to see position in the end. But not all positions, I want to see the unique positions by array is my sum ifs and I want to sort this
in descending order. So I need a minus one here,
close bracket, press enter. And I get my position
sorted by the sum of salary. Now we can quickly test that just to make sure it works. I'll do a quick sum ifs. This is my sum range, my criteria range and my criteria itself. I want it to spill so I'm going to use the hash, close bracket press enter, it works. Now that I've created my machine, I'm going to turn this into a Lambda. So let's go and start off with Lambda. First thing is to figure
out our parameters. Well, that depends on how many
different references we have. I have two different references. It's either this one or this one. The C4 to C19 reference, that's where I have my labels and D4 to D19 that's
where I have my numbers. So I'll be better this time, I'll go with A as my labels and for my numbers, so I need two parameters here. Now, the last argument
remember is the calculation now and I have to replace every time I see a cell reference here, I have to replace it with my parameters. So this is going to become my A because C4 to C19 are my labels. So that's going to be A. I'm going to replace this also with A and this A as well. D4 to D19 that's going to
become my number parameters I'm going to change it to "num". My Lambda is done. Let's close the bracket press enter. Well, it's no going to work because I can test it to see once I pass the ingredients to it Is it going to work? I'll pass department this time and then comma and my numbers. I guess you have to respect the order of the parameters, when I close bracket and press enter I get Sales, Finance and Procurement. Okay, so this works. Now I can turn it into a name that I can reuse anywhere I want. So I'm going to press control C go to formulas, Name manager add a new name, call this "sort by some" and paste in our values. Now I can quickly add A equals labels and "num" equals numbers, and okay. Okay, so sort by some, is my new machine. So now I can use it anywhere. I want to sort division by Revenue. I'm going to go with sort by sum the machine needs two things, first is the column I want returned and then my numbers. Close bracket press
enter, I have it sorted. I can use this anywhere
I want in my workbook. Now the last one and the
most exciting one for me is the Recursive Lambdas. Now, because this video
is already so long, I'm no going to do this
justice if I rushed for this. So I'm going to keep this as
a topic of our next video. Okay, so tune in next week and let's talk about Recursive Lambdas. Okay, so that's an introduction to the new Excel Lambda function. Next week, I'm going to
cover Recursive Lambdas and we're going to do that
last example together. Let me know what you think. Do you have the Beta channel? Are you able to test Lambdas? Let me know below. Now, I assume since
you're watching this far you enjoy the content of the channel, so if you do, consider subscribing and I'm going to see
you in the next video. (upbeat music)
