2 NEW Excel Functions ELIMINATE Copying Formulas

how often do you write a formula and then copy it down a column or across a row all the time I'll bet well you can stop doing that now because there's a much faster way with the new Bol and brro functions available in Microsoft 365 you can write a formula once and it will automatically copy across or down as required I know it sounds too good to be true so let me show you if you've used Excel tables before you'll most likely have experienced writing a formula in the first cell of a column and then it automatically being copied down the column to the end awesome right but it doesn't do it for the totals plus what if your data isn't in an Excel table let's write one brro formula to return the average grade for each student in the table I'll write it so that if I add more students I can have brro automatically fill the formula down the technical explanation is brro takes an array or range of rows that you want to iterate over this array is then pass to Lambda one row at a time for Lambda to perform the calculation it sounds scarier than it is if you're not familiar with the Lambda function you can get the lowdown from this comprehensive Lambda video but you should get the gist for using it with the brro and byol functions from the examples in this video the easiest way to think about brro is simply any formula or function that you want to apply to one row and copy down can automatically be applied to multiple rows with by row so starting with BYO my array argument that is the rows and columns containing my student grades I then use Lambda to take one row at a time from bro and I need to give the array in bro a name now I can name it in the name manager beforehand but I don't need to I can just name it on the Fly here so we're going to call it row range and I should also point out that Lambda can take multiple name arguments but when you use it with BR and B call it can only take one so the next thing I need to do is tell it what formula I want applied to those row ranges and here we're going to find the average what are we finding the average for the row ranges so there it is in the ital sense all I need to do is tab to select it close average close Lambda close BYO press enter and there you go it spells the results one formula for every row now if I just edit that formula notice that the array that I've selected here in my first argument for brro is the same size goes down to row 14 as the spilled array result but what if I want to add more students and have the formula automatically filled down in that case I can use the take function to discard any empty rows before passing the array to brro if you're not familiar with the take function check out this video so let's edit this formula we'll wrap the range into take so the array is still the same set of cells except I want to allow for growth so let's go down to row 20 can make it bigger or smaller and then which rows well I can simply use count a to count how many students I have make sure it's the same size close count a close take so this is going to give me my array and that's going to pass it to Lambda so all I need to do is press enter now it doesn't look any different but when I add a new student let's say me now I was never good at Sciences so let's say 62% there I was better at arts and chemistry not great either but will give me 65 that's probably being generous and you can see brro has already filled the formula down for me like magic now Bol works in the same way except for columns so here I can use B to reference the grades and we'll again have to use Lambda to take The Columns one column at a time so let's call this Co range and what are we doing let's find the average just to be consistent so we're finding the average grade in the co Range close average close Lambda close by Co so Bol is passing these columns one at a time to Lambda which passes them into the average function press enter and we get the spilled result so these are the average grades for each subject let's do something different we'll use Bol again to find the maximum score so again we're referencing The Columns we need to pass it to Lambda and we need to give those columns a name and here we want to find the max of the co Range close Max close Lambda close byy call press enter and it spills pretty cool but let's say we want to find the name of the student who got the top score Well normally you might use index a match to look up a name in the column and return the result so let's start with that index what are we indexing we want to find the names and then we're going to use B call to reference the array and we need to use a Lambda and we'll give the by Co range a name Co range what are we doing with that column range one by one we're going to pass it to match which is going to look up the max of the co range let me just contrl shift U to enlarge the formula bar and then crl F2 to go up there where I've got more space so now I'm going to find the max of the co Range close Max where am I finding it in the co range and I want zero for exact match so that's going to give me the row number for the highest score for each subject it's going to pass that to index to return the name of the student who got the highest score so close Lambda close by call and we'll close index press enter and there's the result so Ian got the highest score for physics Hannah for art and Alice for chemistry pretty cool these are just a few examples of how you might use Bol and BYU next time you write a formula that you plan to copy down or across try using these functions instead also there is a lot more to the Lambda function that you can do outside of Bol and BYO so I recommend you check out my Lambda video next if you found this video helpful please give it a thumbs up and subscribe to our channel for more thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 77,882

Keywords: excel, bycol, byrow, bycol formula, bycol function, byrow formula, byrow function, productivity, productivityhacks

Id: 8-ANSpXcHr0

Length: 6min 44sec (404 seconds)

Published: Tue Sep 19 2023