so excel is releasing an amazing set of functions which are going to be map by row
by column reduce so if you're familiar with array methods in programming languages like javascript
python etc you should have a pretty good idea what these actually do otherwise they could be a little
complicated to understand at first so generally what this function are going to allow you to do
is to convert anything that was not possible to convert to an array function to an array function
so let's do an example of that for example if we have an end function in our spreadsheet if
i'm trying to check for multiple conditions so let's say i want to make sure that this
sales amount is greater than 7000 comma and at the same time i want to make sure that
cost of goods sold is greater than four thousand so if i did that close parentheses hit enter
i could drag this formula down and see we'll get our trues and falses so like we have true here
when both conditions are met we have another true here etc maybe one more now the problem with this
function is that you cannot really use and as an array function so i couldn't just go here and do
g2 colon g17 and then also f2 colon f17 and hit enter and get this to populate all of
these results we can use some trickery to make it happen with some math calculations and so
but not with and function so now using this array methods this will actually be possible
so let me just keep this formula like this so let me try to now convert this to an array formula
to be able to actually understand what i'm doing in this video it's helpful if you have
some experience with lambda function i'm gonna link to that video in video description so you
can check that out if you haven't yet otherwise we're going to continue here so what i'm going
to start doing i'm going to use map function and with map function we're going to give it
one or more arrays as our first argument so for example i could grab this first column right here
that's gonna be my first array then i'm gonna do a comma and what i'm gonna do i'm gonna select
the second column which is my cost comma again as a third argument we're going to use lambda
function now you could use a single array if you wanted to you could use multiple arrays if you
want to so there's really no limit how many right now because i'm gonna work with these two columns
i have those two columns selected so once you have your data now you can follow that by a
lambda function so lambda function here is going to accept parameters and the way it's going to
work you're going to pass arguments from these two arrays that you've defined since i have two
of them i'm gonna have two arguments you can call these arguments anything you want so for example
in this case i could call the first one sales comma and the second one cost like this these
are just names so you could have just done a comma b if you wanted to i'm gonna do another comma here
let me just move right a little bit and now i'm going to follow that by the actual formula i want
to use so if you remember that and function so let me just comment this for a second like this
this is that and function i'm going to copy that formula without that equal sign hit escape
go back to this formula let me remove this comment here and i'm going to follow this
comma by that formula i'm going to paste that formula here let's move this out of the way and
then i'm going to go ahead and close parentheses for that lambda function at this point and another
parenthesis for my map function so we're almost done here there's only one problem here this g2 is
no longer going to be g2 in this lambda function so if you remember this g2 used to refer to this
line item in our sales column so what i'm going to do i'm going to use this variable that i've
defined here in this lambda function instead so i'm going to do sales and then this one i'm going
to replace with this variable here so essentially what's happening here we give it two arrays
and after that what's going to happen in our lambda function each row in the first array this
one is going to become this variable and each row in our second array here is going to become
our second variable so at this point the sales is going to refer to each row from our sales column
and cost will refer to each row from our cost of goods sold column and then we can use those
variables here in our actual calculation so at this point if i'd enter now that should
just make it an array formula see i got my trues where they should be so that's our map function
basically it runs through an array and each time within your lambda function it's going to give you
one of the rows within that array now this is true so long as your array is a single column
as you can see i did refer to each column as a single column but you could also refer to
something like this so at this point if you do this i'm just going to use one array in this
case you need to follow that with your lambda function so now if i use some sort of variable
i'm just going to call it x this time this will now loop through every single
cell within this array so basically every single cell within these two columns
and apply that function to that so let's check if that x is greater than 2000 so at this point if i
close lambda function and map hit enter because we have two column results it's basically gonna
go through every single value here and check if they are actually above 2000 and if they are
it's going to give us trues see these are trues because these are above 2000 these two are not
so we're going to get falses for those and then the same thing is going to happen with this column
so we're going to check every single value like this one is not above 2000 so we get a false here
so another great thing about this map function is when you get this lambda parameter here
you get a reference instead of getting just a value which is awesome because we can now do
things that otherwise would be impossible to do so let's do an example with this running total
so let's say i'm trying to create an array function that's going to give me a running total
if you want to do a regular running total function you would have done equal sum
open parenthesis and then i would grab the cell and do another colon another b2 close parenthesis
like this and then to be able to keep this reference locked i would have done a dollar sign
for that row 2 and at this point if i drag this formula down let's make this a little bigger we're
going to get our running total essentially here we just get this one cell then when we go to the next
row because our first reference is locked we get that first row two and then we get up to this row
and we get the total of these two then we get the total of these three then four then five and so on
now let's try to convert this using that map function to an array function so what i'm gonna
do here i'm going to do map open parentheses so my array this time is going to be this entire range
so now i'm going to do a comma and follow that by a lambda function and here we're going to
get a parameter which is basically each row from this sales amounts let's just call it
r for now comma now i have to do that calculation now if you remember my formula was sum
b2 colon b2 and then i had this dollar sign for this then i'm going to close parentheses
close parenthesis again and close parentheses one more time for this map function now we need to
use this r in a creative way to make this work now right now it's not going to i'm going to
hit enter now if you remember what we used to do this b2 cell was stuck to this cell but if i go to
these other rows you'll see the second part of this range keeps changing to our current row so
this goes to b3 then this goes to b4 so it goes to our current row every time like this so this
part doesn't change this one does right here so for that reason i'm going to go to this formula
this is the part that used to change to our current row and if you remember this r is
our current row it's not the value of the current row it's a reference meaning i could just do this
r so with this if i'd enter we got our array formula just like
that so we have one array formula that gives us that rounding total
that's amazing let me give you an example of this by row function too map as you remember
it goes through each value individually one by one and it maps through all of the values
but what if we want to do something like this let's say we have these numbers right here these
scores and we're trying to get the maximum of each row for example if i do max function and select
these the maximum of that row is going to be 8 and then i want to be able to drag this down
9 4 see each row we get the max of that now in this case when we do this max calculation
we do it over entire row in this array not individual cells separately but the whole row
then it goes to the next row next row next row we apply the same max function to it
so this is where using that by row is going to help you so with this function
when you provide an array like this comma then you follow by that lambda function and in this lambda
function whatever parameter you provide is going to refer to this entire row so first time it's
going to be first row then second row then third row so for now i'm gonna call it r for row i guess
comma and then i'm gonna do the actual calculation so if you remember my formula was max
and it used to refer to this range i'm going to close parentheses close parentheses again
close parenthesis one more time to get to this parentheses so now this
can be replaced with this r which every time is going to be one of these rows so if i do r right
there hit enter we get our array formula just like that using this by row function
and i can delete this now and it just works now let's do an example of by column two so let's say
here i would like to get the average of each one of these columns so regularly i would do
average open parenthesis select this column like that
enter see that gives me this then i can drag this formula right to get the average for each column
so now if you want to do this with an array formula then you can use that by column function
so the difference between by row and by column is that by row will basically map through your array
row by row and by column will map through your array column by column so let's do an example
right here i'm going to do equals by col that's our function now i'm going to refer to this whole
range now within this range because we use by column it's going to go first column second column
third column fourth column so now i can do a comma here and i can follow this by my lambda function
for this i'm going to call it col for each column comma and then basically
that col is going to become every time one of these columns so what we're going to do
here we're going to use that average function again and i used to just select this column here
but now we can use this variable col which should refer to each column so we'll take that
close parenthesis again close parenthesis one more time to basically close this one and if i
hit enter see i get one formula that feels the whole array going right and we got our averages
so there you have it thanks for watching please subscribe and i'll see in the next video