A first look at the new Excel functions BYROL and BYCOL. These functions, released to Insiders Fast this week are designed to be used with LAMBDA functions.
Read more about the 7 functions here:
Read more about the 7 functions here:
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2415. First look at these new helper functions for Lambda.
BYROW and BYCOL. Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen.
The seven new functions came out this week to Insiders Fast. 50% of insiders fast.
I was lucky enough to be on the the lucky 50% this time out.
Today we're going to take a look at. ByRow and ByCol.
These are all designed to work inside of Lambda functions or with Lambda functions and make things a little bit easier.
Things where we had to use and iterater before - we won't have to do that .
And so BY COLumn says we're going to pass it an array and it's going to evaluate that array one column at a time.
So just a really simple example here. I want to get the MAX of each of these columns in the array.
I'm going to pass it as a single array.
So, BYCOL A16 to D16, and then here's my Lambda built right into the formula.
So we're passing in array and then for each column in the array, by column, it's taking them Max of the array.
So I press enter, one formula spills, gives me 777, 666,554, and 221.
If I would change this to be 3333 and it obviously updates That same formula expands. I inserted a bunch of columns in the middle.
Here, the same formula figures out how many columns are in the array and returns one answer for each of those. Once you get the hang of that.
Very similar to do BYROW.
So pass it this whole array here and then for each row in the array take the Max function or the average function and it will return those results. Two cool new functions.
We will take a look at the others over the next few days of videos.
All of these, of course, making my book MrExcel 2021 ready for an update.
I wonder what kind of a pun I can come up with in 2022 for the next edition of this book.
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 will see you next time for another netcast from MrExcel.
BYROW and BYCOL. Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen.
The seven new functions came out this week to Insiders Fast. 50% of insiders fast.
I was lucky enough to be on the the lucky 50% this time out.
Today we're going to take a look at. ByRow and ByCol.
These are all designed to work inside of Lambda functions or with Lambda functions and make things a little bit easier.
Things where we had to use and iterater before - we won't have to do that .
And so BY COLumn says we're going to pass it an array and it's going to evaluate that array one column at a time.
So just a really simple example here. I want to get the MAX of each of these columns in the array.
I'm going to pass it as a single array.
So, BYCOL A16 to D16, and then here's my Lambda built right into the formula.
So we're passing in array and then for each column in the array, by column, it's taking them Max of the array.
So I press enter, one formula spills, gives me 777, 666,554, and 221.
If I would change this to be 3333 and it obviously updates That same formula expands. I inserted a bunch of columns in the middle.
Here, the same formula figures out how many columns are in the array and returns one answer for each of those. Once you get the hang of that.
Very similar to do BYROW.
So pass it this whole array here and then for each row in the array take the Max function or the average function and it will return those results. Two cool new functions.
We will take a look at the others over the next few days of videos.
All of these, of course, making my book MrExcel 2021 ready for an update.
I wonder what kind of a pun I can come up with in 2022 for the next edition of this book.
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 will see you next time for another netcast from MrExcel.
