Google Sheets ARRAYFORMULA, Introductions to Arrays, ARRAY_CONSTRAIN, SORT Functions Tutorial

okay so in this video I'm going to talk about arrays and array formulas so if you're not familiar with a race this will be probably a pretty good introduction so first of all what is an array usually it's a little easier to explain a raise when you're in Excel versus Google sheets but that should not be a problem so first of all I'm gonna start with an equal sign and I'm going to place on a right here so the way I can say that what I'm doing is an array is I can use this curly brackets so what I have in between this curly brackets is my array an array is basically a set of values that could be rows and combined with columns so what does that mean so let's say I do 1 comma 2 comma 3 so when you're doing commas that's basically your separation of columns so right now this is an array with 3 values 1 2 & 3 and these are three different rows so if I simply just hit enter here you'll see that basically that entire array from this formula is being placed in three different columns here so again this is our separation of columns so I hope I didn't say rows at some point but if I did just make sure you understand that a comma is a column so now we have three columns now what if I wanted to move to different row in this array so to do that you do a semicolon and semicolon indicates now we're moving to the next row so now let's say I'm going to do 4 comma 5 comma 6 so basically this will be our first row with 3 columns and this will be our second row with 3 columns again so if I enter you're gonna see we have those values and obviously if I want another bro I just do another semicolon and basically just place your values up let's do kama and there we are so we have our set of values basically just placed on a screen that should give you a little understanding about how arrays are structured so again comma four columns and semicolon to basically indicate another row okay so now now that we know that let's try to do some calculations and some results in a race so you can do some pretty interesting things with a race so one thing we can do is for example build a new range and reposition columns so what do I mean by that I can start an equal sign I can do my curly bracket and what I can do I can highlight all of these values as my Coast as my first column as I said and remember how we separate columns is by using a comma right so the next column is gonna be these values over here so if I do my curly brackets to close this I'm gonna hit enter now you're gonna see that what I get is basically the same two columns only in the opposite order now basically this is my first column and this is the second column and obviously didn't have to be columns that are next to one another and you can just also just continue I can do another comma and decide ok so then I'm gonna do this right and now we should see those three columns repositions and rearrange and now this is the way we actually see those results hopefully that makes sense now why could this be interesting right why would you care about this or why would you ever want to do this so sometimes when you do a vlookup for example to do an example of a vlookup so again if you don't know how if you look up works I have an entire video about vlookups you can go ahead and watch that but for now let's say I'm going to do a pretty simple vlookup I'm going to say I'm going to be searching for this item which is so again I put it in quotes because it's text I'm looking for six nine nine - eight nine - four three zero that's the item I'm gonna be searching for , and I'm gonna be searching for in this range right and again with vlookup you have to make sure that whatever you're searching for needs to be located in the first column of the range you're highlighting now for good practice I'm gonna just lock this with f4 it doesn't really matter here but anyhow so I'm gonna go to comma and if I do let's say column index three and do an exact match or false option which is zero false that's gonna get us the third column because I'm saying let's get the third column match so if I hit enter see I'm getting 68 90 which is basically we looked for this we found it and it was 68 90 that was the coast for that so again if you don't know what I just did with vlookup go and watch vlookup video there is a lot to cover about vlookups and i'm not gonna cover phyllo cups here but why do I want to mention this right so with vlookups one thing that you're gonna notice that you only can pull information from the right of your column where you're searching right so we're searching in stock numbers because we're looking for a stock number now I can only get the price or the coast but I could not get this because this is located to the left so to be able to do that I can create this a rake type of range so I can say you know what I want this to be my first column which is my stock numbers , and I want this product to be my second column right here so now this is my first call and this is my second column right and if I wanted to do a vlookup I would say well let's search for this item and now we're going to look for it not here we're going to look for it in here so again for good practice lock that right and now because it's a different range we have only two columns I'm going to say I want to retrieve the information from column two so if I enter I'm gonna get whatever that product that I was searching for is so four three zero is this item which is what we get now obviously we don't want to do this range on the site we want to sum somehow incorporate that in our formula so we don't have this site stuff sitting here so to do that I simply go back and copy this array that gives me this result and I can think about it as a result range I can hit escape go back here and instead of providing this range for our lookup which is this one over here I'm simply going to paste that range here and if I had enter that's going to work just fine now I don't need this anymore here so I basically put that right in my formula and it works now again for good practice I'm gonna lock this I'm gonna like this and there we are we have our end result so that's something you can accomplish by rearranging columns now vlookup works to look inside of this column and retrieve data from that column right so what else can we do with a race right so again the thing with the race is that there are really unlimited things you can do and once you really understand the core or how things work then you're gonna figure when you could potentially use something like a race but for right now I'm going to use another function it's called array formula and in this case you can use a write formula if you want to get a formula that's also on a race so in the other case when I was using the simple curly brackets there was no formula I was just selecting the data I needed and I was saying let's reposition it this way or reposition it that way right that was kind of the idea now what I'm going to do now is try to create a formula and first of all I guess let me explain what the formula is going to do so what I'm going to try to do is simply just get a sum or or not the somebody the average net profit from an item sale so what I mean by that so what I want to do I want to do basically let's take the price of each item minus the cost for each item which is gonna give us the difference right I want to copy this down which gives me all of these values and I want to see what's the average basically profit or net not really profit we get from an item so that should be 43 92 now however I don't want to do all of this extra columns and all the stuff I just want to get it working in my single formula so to get to the same results I can use an array formula and in this array formula I'm going to say well I want all of this - all of this so I'm basically taking a range - another range I'm gonna close that parentheses and if I hit enter right now you see that what I get is basically this values right here as a result of my calculation so I said let's get all of this - all of these and at this point I want to do the average of all of those so I can wrap that whole thing in a function average hit enter and I'm getting exactly the same value and now I don't need this extra column to get to my result hopefully that makes sense so that gets us our average which is then we take our array formula results and average it out and we get 43 92 hopefully that makes sense now other thing we could do let's say we could get like what's the average profit margin right what does that mean so how do I get average profit margin right so I need to get something like profit margin here and to get our profit margin so if I take the cost of the product and divide it by the price of the product it's going to tell me what percentage of this product is the coast so seventy-eight percent of the price is the cost of that product right now so to get to the actual profit margin that's basically the remaining 22 percent which is the rest that remains so to get to that 22 we have to take 1 minus this point 78 to get that to that 20 to 0.22 so I just go here and do 1 minus that and that gets me to that 22 percent and if we copied down that our profit margins now obviously you could do this it's a percentage sign to make them pretty and stuff but I'm not gonna worry about it right now so if you're watching this hopefully you already know that so I'm gonna go ahead and do an average and use the average of all of those and basically that's our average profit margin so that's about like 33 percent now let's get to the same result using an array formula so I'm gonna start array formula so what I did if you remember I said let well we're gonna take coasts and we're gonna divide it by the price but that gave us like what percentage is the Coast out of the price now what I want to do is do the opposite which is 1 minus debt which is what I did in my other formula and then just close this parens right there and hit enter and that should give me again exactly the same thing so basically I did the same type of formula as I did before over here which is 1 minus this divided by this only I did it with the entire range of items I have and I have my array formula around it to treat it as an array so now to get to my average again I simply have to just take this thing and put it inside of my average function again elope the lowercase uppercase the average doesn't matter here but there it is I get to the same number that's our average profit margin so again I don't need any others again that's gonna get me my average profit margin another thing you may want to get I guess is for example get your bottom or top five highest margin products so let's say we wanted to get the top five products with a higher margin which what that means this probably shouldn't delete that but in this particular case if we have this profit margins to get the top five see this is the one - this is the second highest profit margin so then we have what do we have I guess this is a 30 0 38 and then 0 36 or know we have this 0 4 anyways we just want to get the top 5 so first of all how do we get the highest profit margin item so to do that again a lot of this is going to be the same to get the highest profit margin item so this gets us the average what I did if I simply just did max instead of the average that should give me the highest profit margin that I have out of my different items now we want to let's say get top 5 so how can we get something like that so the way we can get something like that we can basically sort all of this prices and then once we sort then we get the highest 5 on top then we can just just get the top 5 and that will get us those products so how can we do this so again so if I have let me just take off this max and this was our array formula that was giving us the set of values see where their margins now what I can do I can use a function called sort and see the sort the first thing is the range which is going to be provided by this array formula and then what I want to do I want to say comma which column I want to sort by I want to sort by that same column so that's the first column in here right so that's the first column and comma and finally do I want to sort this ascending or descending so ease ascending is the true option I don't want to have it as ascending so I can either do false or I can do 0 so I'm gonna do false here close my parentheses hit enter so now you can see we got all of these results sorted right so one two three four five and etc so we wanted to get this top five so how can we do that for that we can use another function around this so I'm just gonna cut this so I can paste it so it's not confusing and the function is called array constrain and what this does is basically going to only allow us to get set of results instead of getting all of the results so what does that mean so the first thing is the range so the range is gonna come from that formula we had and then I'm gonna do comma and then it says how many rows do you want to get out of that so what I want to do I want to get five rows comma because we want top five and how many columns we want well we have a single column of values so I'm gonna do one close my parentheses hit enter and there it is we have our top five highest margin product as a result of this now you could say I want to do the average of my top five you could just wrap this whole thing in an average function now what if we simply wanted to return the whole data set we wanted to just find out those top five items so we want like this we want like this we want like this and we want like this we want to return all of those instead of just getting the numbers we want the entire row okay so let's try to do something like that I'm gonna clean that up I'm gonna remove this - and maybe delete this as well now what I want to do first is I want to create a new range of items so what I mean by this so first of all let's just start with that array formula and my array formula remember what I did I said let's take one - our coast divided by our price and if I close this I hit enter I'm getting this new column profit margin that we need we're assuming that we don't have this extra column right and that's gonna be this extra column I'm actually gonna cut this and move this over here so I probably should have moved it a little down here okay that's fine anyhow so that's the same thing that I get here now what I want to get as end result is not just that I want also all of these columns that are located on the left of this right so what I need to do I need to make my array so I'm gonna cut that I'm gonna start with that curly bracket right so as a first result I'm going to say I want all of this and then new column is a comma right and I'm gonna paste that array formula that's giving me that extra column and I'm going to do a semi not semicolon but they curly bracket closing whatever we're calling this I'm gonna hit enter and what are you gonna see is basically I have those results which is all of them my table on the left in addition to that I also have that extra column that I was looking for and this is obviously not connected to this at all so if I delete this still gonna be the same right I'm still gonna keep it so we can kind of reference it with our eyes but just keep in mind that this is not connected to this column at all and that's the whole point of doing this right you don't want that extra column you just want your result okay so now out of this result what I want to do I want to now sort these by this column over here and that column is 1 2 3 4 5 number 5 column I'm gonna go back here I'm gonna grab this whole thing cut it because I'm gonna need to use it and I'm going to use my sword function and the first thing is the range which is that whole thing that I had I'm gonna paste it comma and then the sort column so if you remember 1 2 3 4 5 I want to sort by the 5th column and is this ascending no so it's false or 0 close that hit enter and now I'm gonna get that same result only now sorted and we have the highest value on top and now at this point what I want to do first of all I want to basically just constrain my results so I want to just get top 5 so I'm gonna go back here grab this whole thing and we're going to use our array constraint formula I'm going to paste my range comma now I wanted how many rows the number of rows needs to be 5 that's how many results I want comma now another interesting thing is that how many columns do I want to return because I don't really want to see this profit margin I just want to see these columns so I have 1 2 3 4 so I'm gonna do 4 and I'm not gonna do 5 so I could do 5 also to include that you know profit margin column but I don't want it so I'm gonna do 4 closed parens hit enter and there we have it now we basically are able to dynamically extract top 5 margin products from our results and again keep in mind this is not top five highest priced or the lowest price the top five with the highest margin what that means is that the first one should be seed this one which is Nike Train speed for right this zero four nine the second highest margin is 0.47 which is this Nike train ultra fast whatever that is and so on hopefully that makes some sense and that's the result of a rate constraint sort an array formula all of those functions combined right here ok I think that's enough for this video for introductory level I'll do another video which is probably gonna get a little more advanced but for this I think this is it I hope you've enjoyed this video thanks for watching please subscribe and I'll see you next time

Channel: Learn Google Spreadsheets

Views: 183,665

Keywords: Google Sheets, ARRAYFORMULA, Introductions, Arrays, ARRAY_CONSTRAIN, SORT, Function, Tutorial

Id: m6-6Le7gEpY

Length: 23min 17sec (1397 seconds)

Published: Wed Oct 25 2017