Three more of the new Lambda Helper functions in Excel: MAP, REDUCE, and SCAN. In today's episode, see three different ways to create a PRODUCTIF function in Excel using MAP, REDUCE, or SCAN.
Important points:
MAP can take one or more arrays.
MAP is looking at each individual cell, not just the values in the cells.
REDUCE does the same thing, but produces a single result.
SCAN is like REDUCE, but makes the intermediate results available.
Read more at: Re: Announcing LAMBDA Helper Functions: Lambdas as arguments and more
Important points:
MAP can take one or more arrays.
MAP is looking at each individual cell, not just the values in the cells.
REDUCE does the same thing, but produces a single result.
SCAN is like REDUCE, but makes the intermediate results available.
Read more at: Re: Announcing LAMBDA Helper Functions: Lambdas as arguments and more
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2416.
Creating a PRODUCTIF function using the three new Lambda helper functions: MAP, REDUCE, or SCAN.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Yesterday we talked about BYROW and BYCOL. Today we're going to take a look at these others: MAP, REDUCE, and SCAN - new Lambda helper functions.
These are currently only flighted to 50% of people who are on Insiders Fast, so they are brand new. MAP is a function used for value transformation.
We can pass it one or more arrays and have MAP go through each item in the array and do a calculation and then return an array of the equal size.
Chris Gross on the Excel team always demonstrates this with the ISEVEN function.
ISEVEN is one of those functions that in Excel 2007 came over from the Analysis Toolpak.
Those functions; originally written in VBA don't play well with arrays.
Try to do ISEVEN of an array. It gives you a #VALUE! Error.
I think in the past on the podcast – I am not even sure what episode it was – I showed how you could do minus minus to coerce that into working right.
But that's weird and you know, let's not do it.
So with the MAP function, I can pass it in array of values A1 to A20 and then have the logic. So the LAMBDA says we are taking an array.
You could call this anything. You can call it Bill. You can call it anything.
And then for each item in the array, we're going to pass it to the ISEVEN function.
And it will return. True or False, depending on whether these numbers are even.
The thing to understand here and is not being shown in a lot of the examples that are coming out is they are not just passing the values here, they're actually passing each individual cell right?
So here we have a data set - you know just one of those horrible datasets you see out in the wild, right?
Where someone has created something where the first item is quantity, second item is revenue.
And quantity, revenue.
So, like, you have to add up all the green things in essence.
And what I'm going to do is, I'm going to say we're going to add up all of the items that are on an odd row, right?
So in this case I'm passing it an array of these values.
And then the LAMBDA says we're taking this array and then.
Check it out, because they're taking each individual cell, I can actually use the ROW function and then check to see if the row is odd and that will return a series of trues and falses.
All of the odd rows will be True and then multiply that by the array and I get my answer here.
Now if I wanted the total that of course then we just send it into the SUM function like that.
Alright, so map will iterate through a series of values, do some logic for each cell in the array and return a single value.
The other cool trick that MAP can do is you can pass it multiple arrays.
Up to 250 arrays. We pass it the first array.
Pass the second array.
And when you do that then the Lambda needs three arguments.
If I'm passing 2 arrays, it needs the first array, the second array, and then the logic of what to do with it, right?
So here we have Andy and some values. I want to do a PRODUCTIF of just Andy’s values.
So if the A array is equal to Andy then give me the B array otherwise give me a one.
And what that does is that gives me a series of values that I can then send into the PROODUCT function, right?
So we have our PRODUCTIF Actually, here, let’s just do it out here. So =PRODUCT of that array.
Or you could also just take the whole function like this and wrap it in the PRODUCT function and have a single PRODUCTIF Alright, so that's showing off the MAP function.
And be aware that a couple of things are happening.
First it's passing the actual cells, not the values in the cells, which is cool.
And then this concept of being able to pass multiple arrays.
In this case it's just two.
But you know we have 255 arguments so in theory if the Lambda is argument number 255.
I guess you could pass 254 arrays, although I don't have an example like that.
Alright, so MAP is the first new function. Then the next function: REDUCE.
This operates on each item in the array, but it doesn't return an array of values.
It returns a single value, a single value.
And, what we're going to do: We're going to pass an initial value and then an array, and then the Lambda.
The first argument in the Lambda is a variable that's going to be what they call the Accumulator.
This is where we're going to store the result each time through and then here in incoming array.
Now I'm a little frustrated here because with MAP I can pass it multiple arrays.
But I can't seem to make that happen with REDUCE.
And it's not documented that it should do it, but it feels like it should do it.
And it might be because of this optional initial value here that they they just can't do it. Or, I'm just not smart enough.
Which that probably is the situation.
I'm sure down in the YouTube comments someone will come along and say “Oh no, you could do that! Here's how to do it”.
Because unfortunately then this forces me to use the OFFSET function and I hate using the offset function.
So what I'm doing is, I'm passing it the values over here in column B and then if from B, I go zero rows down. Negative one column over.
So look at the name to the left of me.
If that's equal to Andy, then I take my total times B, otherwise just Total. And see, I set total to an initial value of 1.
If you didn't do that, if Total came in as zero, then the product if would not work.
So there is a function using the REDUCE which avoids us having to wrap the map function inside the product function. So the new MAP function and the REDUCE function.
The third one we're going to talk about today is very similar to REDUCE.
It's called SCAN.
The difference is the SCAN: as scan is doing its thing, it's returning each intermediate value right?
So 3780 is is the answer, but if we wanted to see how this was accumulating over time, right?
So there Andy has two right?
So that becomes the PRODUCTIF for the 1st 4: One, two, three, four.
But then the next time we run into Andy, two times two.
Then the answer is going to be 4 for the next several until we get to Andy's three here. And then it becomes 12.
Right, same kind of thing.
We are passing an initial value, single array, and then the Lambda.
We pass it an accumulator and again you can call this anything, the incoming array. You can call this anything.
If A is equal to Andy. So I'm going the opposite way here.
Looking at the name, then taking Total which starts out as 1 times one column to the right.
Otherwise, Total. And it accumulates as we go.
I am still trying to figure out a great name for the MrExcel 2022 version of this book, which will include these new helper functions.
If you like these videos, please down below Like, Subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Creating a PRODUCTIF function using the three new Lambda helper functions: MAP, REDUCE, or SCAN.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Yesterday we talked about BYROW and BYCOL. Today we're going to take a look at these others: MAP, REDUCE, and SCAN - new Lambda helper functions.
These are currently only flighted to 50% of people who are on Insiders Fast, so they are brand new. MAP is a function used for value transformation.
We can pass it one or more arrays and have MAP go through each item in the array and do a calculation and then return an array of the equal size.
Chris Gross on the Excel team always demonstrates this with the ISEVEN function.
ISEVEN is one of those functions that in Excel 2007 came over from the Analysis Toolpak.
Those functions; originally written in VBA don't play well with arrays.
Try to do ISEVEN of an array. It gives you a #VALUE! Error.
I think in the past on the podcast – I am not even sure what episode it was – I showed how you could do minus minus to coerce that into working right.
But that's weird and you know, let's not do it.
So with the MAP function, I can pass it in array of values A1 to A20 and then have the logic. So the LAMBDA says we are taking an array.
You could call this anything. You can call it Bill. You can call it anything.
And then for each item in the array, we're going to pass it to the ISEVEN function.
And it will return. True or False, depending on whether these numbers are even.
The thing to understand here and is not being shown in a lot of the examples that are coming out is they are not just passing the values here, they're actually passing each individual cell right?
So here we have a data set - you know just one of those horrible datasets you see out in the wild, right?
Where someone has created something where the first item is quantity, second item is revenue.
And quantity, revenue.
So, like, you have to add up all the green things in essence.
And what I'm going to do is, I'm going to say we're going to add up all of the items that are on an odd row, right?
So in this case I'm passing it an array of these values.
And then the LAMBDA says we're taking this array and then.
Check it out, because they're taking each individual cell, I can actually use the ROW function and then check to see if the row is odd and that will return a series of trues and falses.
All of the odd rows will be True and then multiply that by the array and I get my answer here.
Now if I wanted the total that of course then we just send it into the SUM function like that.
Alright, so map will iterate through a series of values, do some logic for each cell in the array and return a single value.
The other cool trick that MAP can do is you can pass it multiple arrays.
Up to 250 arrays. We pass it the first array.
Pass the second array.
And when you do that then the Lambda needs three arguments.
If I'm passing 2 arrays, it needs the first array, the second array, and then the logic of what to do with it, right?
So here we have Andy and some values. I want to do a PRODUCTIF of just Andy’s values.
So if the A array is equal to Andy then give me the B array otherwise give me a one.
And what that does is that gives me a series of values that I can then send into the PROODUCT function, right?
So we have our PRODUCTIF Actually, here, let’s just do it out here. So =PRODUCT of that array.
Or you could also just take the whole function like this and wrap it in the PRODUCT function and have a single PRODUCTIF Alright, so that's showing off the MAP function.
And be aware that a couple of things are happening.
First it's passing the actual cells, not the values in the cells, which is cool.
And then this concept of being able to pass multiple arrays.
In this case it's just two.
But you know we have 255 arguments so in theory if the Lambda is argument number 255.
I guess you could pass 254 arrays, although I don't have an example like that.
Alright, so MAP is the first new function. Then the next function: REDUCE.
This operates on each item in the array, but it doesn't return an array of values.
It returns a single value, a single value.
And, what we're going to do: We're going to pass an initial value and then an array, and then the Lambda.
The first argument in the Lambda is a variable that's going to be what they call the Accumulator.
This is where we're going to store the result each time through and then here in incoming array.
Now I'm a little frustrated here because with MAP I can pass it multiple arrays.
But I can't seem to make that happen with REDUCE.
And it's not documented that it should do it, but it feels like it should do it.
And it might be because of this optional initial value here that they they just can't do it. Or, I'm just not smart enough.
Which that probably is the situation.
I'm sure down in the YouTube comments someone will come along and say “Oh no, you could do that! Here's how to do it”.
Because unfortunately then this forces me to use the OFFSET function and I hate using the offset function.
So what I'm doing is, I'm passing it the values over here in column B and then if from B, I go zero rows down. Negative one column over.
So look at the name to the left of me.
If that's equal to Andy, then I take my total times B, otherwise just Total. And see, I set total to an initial value of 1.
If you didn't do that, if Total came in as zero, then the product if would not work.
So there is a function using the REDUCE which avoids us having to wrap the map function inside the product function. So the new MAP function and the REDUCE function.
The third one we're going to talk about today is very similar to REDUCE.
It's called SCAN.
The difference is the SCAN: as scan is doing its thing, it's returning each intermediate value right?
So 3780 is is the answer, but if we wanted to see how this was accumulating over time, right?
So there Andy has two right?
So that becomes the PRODUCTIF for the 1st 4: One, two, three, four.
But then the next time we run into Andy, two times two.
Then the answer is going to be 4 for the next several until we get to Andy's three here. And then it becomes 12.
Right, same kind of thing.
We are passing an initial value, single array, and then the Lambda.
We pass it an accumulator and again you can call this anything, the incoming array. You can call this anything.
If A is equal to Andy. So I'm going the opposite way here.
Looking at the name, then taking Total which starts out as 1 times one column to the right.
Otherwise, Total. And it accumulates as we go.
I am still trying to figure out a great name for the MrExcel 2022 version of this book, which will include these new helper functions.
If you like these videos, please down below Like, Subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
