Things About Lambda Function In Excel You May Not Have Known
Brand new in Excel for 2021 - LAMBDA is the
most powerful function ever. This is awesome!
LAMBDA is the most powerful function in Excel for
one reason. It lets you create your own functions and use them throughout your spreadsheet. Let's take a look and see how this works.
Let's say we want to create a formula that adds 1 to the value over here in this column x. So let's type in the LAMBDA function and the first thing it asks for is a parameter or calculation. I'm going to put in x as an input variable and this can be anything you want to use. It just so happens I'm matching the x title that I have over here. Then it wants to know what the calculation is. Well let's take x plus one. That's our formula. One of the things that's
tricky about the LAMBDA function when you enter it directly into your spreadsheet is it needs to know what the input value is for x in this formula.
It doesn't know that it's pulling data from over here so we're going to tell it that the input value is going to be this value 8 over here
L in A2. Hit Enter and it's taken that value 8 and added 1 to it. Let's copy this down and you can see that it uses A3 as the input for this one
and A4 as the input for this one and
adds one to each of those values.
The real power of LAMBDA is when you name a function of your own.
So let's take this x plus one idea and we'll create our own function called
PLUSONE. The way you do that is come up here to Formulas and go to Name Manager. We're gonna create a new name and we're gonna call this one PLUSONE and in the formula for that we're gonna have it be a LAMBDA function with an input of x and a value of x plus 1 for the calculation.
That's all you have to do. We'll Hit OK and now it creates a name called PLUSONE. I'm going to close that and over here I'm gonna reference my PLUSONE formula and you can see it's in the list now and the parameter that we're gonna pass in is gonna be this A2 value and there it adds one to that value.
We'll copy it down and you can see that it is now its own function called PLUSONE. That's the real power of the LAMBDA function.
We can now use this newly named function throughout the spreadsheet
and it saves with your spreadsheet so you can come back and use it again in the future. Now let's take LAMBDA a little bit farther.
Let's say we want two input variables instead of one so let's create a column here called y and we'll put some numbers in here as well. We'll clear this out and we're going to go directly to the Name Manager and create something that is going to multiply x and y so let's call it MULTIPLYXY and the formula we want to use here is going to be =LAMBDA and we're going to input both an x and a y variables and for the calculation we want it to be x times y. Hit OK.
Another feature of the LAMBDA function is that the input value can be a range as well as an individual value. So for example, let's say we delete these and we edit this A2 value for input and instead of selecting just the A2 value of 8 let's go ahead and do the range of the entire column and we'll do the same thing on column B. That calculates the values by multiplying these two across and it spills the results dynamically over the additional cells. The LAMBDA function handles dynamic values as well.
A LAMBDA function is even more powerful when combined with Data Types. So let's clear this data out and for x we're going to put in some states.
We're going to highlight that and go to Data and in the list I'm going to pick Geography as a data type. That assigns the show card for each one of these states and now if I were to reference them I could say = this state and put a period in and select the value that I want like Median Household Income.
So if we were to take and copy this now let's create a LAMBDA function. So I'm going to go back to Formulas, go to Name Manager, create a new, and we're going to call this StateMedianIncome and in the formula let's put in LAMBDA. We'll pass in a parameter called x which is the state and we're going to paste the value that we calculated for the median household income, except instead of A2 I'm referencing x.
Put an End Parentheses on that, hit OK, and now it's created our StateMedianIncome.
So now I can do StateMedianIncome and pass in the parameter for this and now I have my values. So the LAMBDA function works great using Data Types as well and if we clear this out we can also use a dynamic range with multiple states and it spills the values over into the additional cells.
If you've discovered any other ways to use the LAMBDA function please share them
Click the thumbs-up and leave a comment. I really do appreciate your support!
If you have any query or want any help related to excel feel free to join our Telegram channel and message us..
Channel link :
0 Comments