Excel LAMBDA function - more examples.
You asked for more examples of the brand new Excel LAMBDA function. Here are some complex formulas
that make better use of LAMBDA. If you missed my introduction to LAMBDA video follow the link
in the description of this video to learn more. #1 Count Words. One of the formulas I shared in
my Advanced Functions video is how to count the number of words in a sentence. I'll put a link
in the description to that video if you want to see how that formula is developed. It essentially
takes the length of this sentence and subtracts the length of that sentence without the spaces
because the number of spaces plus one gives you the number of words in that sentence.
So let's take this formula and we'll convert it into a LAMBDA function. First thing I'm going to
do is highlight just the formula portion, copy it, and then go to the Formulas tab, into Name
Manager. Here I'm going to create a new entry and let's call this CountWords for our function name.
In order to create a LAMBDA function we're going to start out with LAMBDA and we're going to pass
in a variable. Well the variable is going to be that sentence so we'll call that our words.
Now I'm going to paste the formula and where I reference A1 I'm going to change that to words.
And we're going to make sure to put an end parentheses and hit OK.
Now our function CountWords is available so I'm going to close that and we're going
to reference CountWords as a new function and we're going to pass in this sentence
and there it counted the number of words in that sentence. #2 Count Words in Array.
Let's take this a step further and use the SUMPRODUCT function to count the words in an array
of sentences. You'll find the same SUMPRODUCT formula in the Advanced Excel Functions video I
mentioned earlier. So let's go ahead and take this SUMPRODUCT formula and we're going to copy that
and we're going to go back into the Name Manager. We'll create a new entry.
This time we'll call it CountWordsArray and we're going to add our LAMBDA function and
this time we're going to pass in the words again but this will be an array of words. I'm going
to paste the formula and where we reference this range we're going to pass in the variable words.
Put the end parentheses and hit OK. Now we can reference that new formula
and we'll choose this range and it counts the total of all the words in all three sentences.
The nice thing about this formula is it'll also work if we change that range
to just a single entry. So if you want the flexibility of counting words in multiple
sentences or in a single sentence this is a formula you'll want to use.
#3 Most Frequent. In my Advanced Excel Functions video I also showed how you can use the INDEX,
MODE, and MATCH to find the most frequent value in a list. So let's go ahead and take this
formula and convert it into a LAMBDA function as well. I'm going to take this and copy it,
go to Name Manager, create a new entry, and here we'll call this MostFrequent.
We'll make our LAMBDA function
and we're going to pass in the array of names in the list.
Now I'm going to substitute the range that I used as my list and now I can reference MostFrequent
with this list of names
and Sally is the most frequent name. If we were to copy John over to multiple names here
then you'll see that John's the most frequent.
#4 Last Value. Another complex formula in my Advanced
Excel Functions video is how to find the last value in a list of names.
We're going to look up James in this list of names and return the value from the second column.
This complex formula right here does that so let's go ahead and copy it.
We'll go to the Name Manager, create a new entry, and we'll call it LastValue.
We'll give it our LAMBDA function
and this time we're going to pass in two variables. The first is going to be the list
of names and values and the second is going to be the name that we're looking up. Then we put
in our formula and we're going to substitute the entire range A1 through B9 as that list
and we'll enter name for the D1 location as our second parameter. Add our end parentheses
and hit OK and Close. Now we can substitute that LastValue formula
using this range and looking up that name and there's your result. #5 FilterList. Now let's
try something a little bit more complicated. I'm going to use the Filter function which is
one of the brand new dynamic array formulas available in the latest version of Excel
and I'm going to pull data from this list that matches these different criteria up here.
So I can take and put in a year 2015 and it's going to pull the data from the table
that matches 2015 for Bikes with this product of Mountain Bikes. I can also just remove the product
and it's going to show all Bike categories and I can remove the Year
and now it shows the Bikes from all the Years and all the Products.
The way this formula works is I'm using the Filter function to look at the entire table array and
then I'm looking to see if the year G2. If it's blank I use a 1 which ignores it essentially
and if it's not blank I'm going to take column 1. That's what comes from this OFFSET function if it
equals G2 the value 2015. So when it looks at the entire column and it finds the year match it's
going to bring that into the filter. I multiply that by the second group which is the Category and
if it's blank it ignores it otherwise it takes the second column and compares it to the value in H2
and then I multiply it by the third which is the Product and do the same thing. So let's take a
look and see how we can convert this into a LAMBDA function. I'm going to take this whole formula,
copy it, go to Formulas, Name Manager, create a new entry. We're going to call this FilterList
and we'll create our LAMBDA function. The first parameter we want to pass in is the entire range,
the second parameter is going to be the Year, the third is the Category, and the fourth is
the Product. We'll insert our formula here and we're going to change this to represent the range.
We're going to change G2 to represent the Year. This again is the entire range.
This represents the Year. H2 is the
category. The range. Category again.
Then the Product.
The range again and the Product for the last time.
We'll put an end parentheses for the LAMBDA function and hit OK.
Now we can replace this formula with our new function
FilterList. We're passing in the entire range
then we're passing in the Year, the Category, and the Product and now you have your FilterList
LAMBDA function and it continues to work just like we had before
but it's a nice concise LAMBDA function. That covers more examples of the new LAMBDA function
in Excel. Be sure to look in the description of this video to find links to my original
LAMBDA introduction video, my Advanced Excel Functions video, my Dynamic Array Formulas video,
and a listing of each formula used here in this video. Also, make note that I am using
the LAMBDA function in the beta version of Excel. It will be a few months before
it's available in general release versions of Microsoft Excel. Thanks for watching. Hey,
if you want to see more videos like this one please subscribe and if you've enjoyed this
video be sure to click the thumbs-up and leave a comment. I really do appreciate your support!