How You Can Actually Use the NEW Excel BYROW & BYCOL Functions

so excel recently released a few new functions called lambda helper functions and i'm curious what you think when you hear that lambda helper functions do you think that sounds great i'm immediately going to use those or do you think they sound too technical i don't need them i'm just going to use my standard functions share with me because i'd love to know now i'll probably cover all these functions at some point but today i want to concentrate on two of these functions by rows and by columns i want to show you a practical use for these and check what you think if you can actually see yourself using these in the future now just to mention these functions have just rolled out so check the description of this video to find out more details about their availability let's jump in [Music] we have the sales data by month for the different sales employees and what we want to do is first get the months that were above the sales target so this is our sales target it's dynamic we can easily change it and here we want to get the months that passed the sales target and we want to do this in a single formula this is where by column can come in handy now the second example is going to be for us to get the employee names that sold above the target so basically we're going to look at each of these months and see was there any month where that employee sold above the target and we want to see their names down here as well okay so let's start with the first objective now because we have months in columns the function that we can use here is by call and let's see what that does so here i'm just going to start off by typing in by call you can see right here it says that it applies a lambda function to each column and returns an array of the results now don't get scared about the lambda part because it's actually really simple to set that up all this needs is an array and this is going to be our chunk here because what by column does is that by default it's going to split this whole array into single columns and then it's going to pass each single column to whatever function you want now here comes the function and that's the lambda function so here you start off with lambda and then you define the function so when you're building your lambda which is your machine that's going to process each column of this range you no longer need to reference the range right we've already done it here we need to create variables or parameters for this and you can call this whatever you want i'll just call it my call then we can add the calculation this calculation has to be any type of aggregation or calculation that you can apply to a single column so for example you could sum the columns or you could get the maximum value of the columns now just to demonstrate what happens if i do sum now don't highlight this or reference this we go with this name here my call because that's going to split this range into each separate column and it's going to sum each single column in this case so now i'm going to close bracket close bracket again press enter and i get the sum of each column spilled right so that's easily visible here that's the sum this is the sum of the second column and so on so now instead of sum we want to do a check we want to basically count how many times we went over this target here instead of using the sum function here i can use a combination of count with an if or i can use the countif function my range is my call and my criteria is whether it's greater than this so i have to put the greater than sign in quotation marks and then because i'm combining text with a cell reference i need the ampersand and i'm going to reference this now ideally you probably want to use the let function as well to define a name for this but in this case i'm just going to keep it simple and reference the cell directly this closes the count if function then we have the lambda function so we need another bracket press enter and check this out we get the number of occurrences where we were above this target so here there must be two numbers that are above this one is this one and the other is this one then here there was nothing that was above the target and so on i'm getting there but i want to get the months back and i don't want to get these numbers back what i can do is to combine this with the filter function so i want to filter whatever i want to get back which is the months here my criteria here what i want to include is basically based on the result of by column but i want to check whether by column is greater than zero then as the last argument if it's empty so if there are no matches found i want to return nothing close bracket press enter and i have the months where i was above target in this case i get it horizontally but you can easily use the transpose function and get this vertically as well if you need to let's just double check whether this is dynamic instead of 1500 i'll change this to 1900 and i just get april may and july let's press ctrl z to go back so this is how you can use the by column function and how you can combine it with other functions as well now that we know how this one works it's really easy to find the employee names that sold above the target so let's do that right here i'm going to do this from scratch but you probably know how that works now we need by row instead of columns because the employees are by row here the array is the same thing and the lambda function we're going to give it a parameter so i'll just call it my row you can call it whatever you want the calculation is the same i'm going to use the count if function check whether my row is greater than so i need the greater than sign and my target here close bracket and close bracket again and again now here i get to see how often an employee went above target so first case i have dog here and he went above target two times so that's one time and that's the second time right so it all looks great now i'm gonna put this again inside the filter function the array are my names here because that's what i want to get back the include argument is going to check whether this array is greater than 0 and if no matches are found i'm going to return an empty string let's close bracket press enter and i get to see the names and if this changes again let's go back to 1900 i just get dog and cat so that's an example how you can use by rows and by columns in excel so let me know in the comments below what you think and if you can see yourself using these in the future don't forget to subscribe to this channel by the way if you aren't subscribed already so you can stay up to date with new developments in office thank you for watching and i'm gonna see you in the next video [Music] you

Channel: Leila Gharani

Views: 154,559

Rating: 4.9782095 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, lambda, lambda helper functions, excel lambda, byrow, bycol, bycolumn function, byrows function, new excel functions 2021

Id: YqaEJiwJj2I

Length: 8min 2sec (482 seconds)

Published: Thu Sep 23 2021