Both results. You don't have to write two separate functions. I'm going to show you how in a second, but first, let's rewind, go back to the basics, and see how TAKE works before we build on it. Yeah, the TAKE function is super easy to get the hang of. All you need is an array. This can be a single column, it can be a single row, or it can be a matrix like the content of this table. Then, you have to define the number of rows. If I put 3, for example, and I close the bracket,.
Press Enter, I get the first three rows returned. So, notice, it's not just the third row, but it's everything before it as well. If I put -3, so if you work with minus numbers, you get the last rows returned. So, this now is the last three rows. If you wanted everything returned, you just leave it empty. So, you add that separator, then you press Enter, and you return all the rows. If you wanted specific columns, so let's say I wanted the first two columns, I can.
Move on to the column argument, put it two, and now I have all the rows of the first two columns. If you're wondering what the difference between TAKE and CHOOSECOLS or CHOOSEROWS function is, here it is. Let's take CHOOSECOLS. My array is going to be the same thing. Now, for column, it says column number one, column number two. If I put a 2, close the bracket, press Enter, I just get the second column returned. If I put a 1 after the 2, I get the second column and.
Then the first column returned. So, with CHOOSCOLS, I get to define specific columns I want returned. The same would CHOOSEROWS, I can define specific rows I want returned, whereas with TAKE, it's going to take everything up to and including the row and the column I define here. Now that we have the basics out of the way, let's see how we can use the TAKE function to calculate the average spend of the last five marketing campaigns. So,.
I'm going to go with -5. I can skip the columns argument, press Enter, and I get the last five numbers. Now, I'm just going to stick this inside the AVERAGE function, and I have the average of the last five campaigns. If I happen to have more campaigns, just copy and paste this, and my results are going to update automatically by taking the average of the last five rows, and we can see that number is identical to this number here..
Now, let's see how we can combine TAKE together with the FILTER function. Here, we want to return the last five campaigns where marketing spend was greater or equal to one thousand. So, we can use the FILTER function first to get that done. We just want the campaigns returned. So, I'm going to filter for campaign name. I want to include only the column where marketing spend is greater or equal to one thousand. Now, when I press Enter, I return everything that's greater.
Or equal to 1000, but I only want to take the last five campaigns. So, we're going to put this inside the TAKE function and go with -5 to get the last five campaigns. Now, again, this is dynamic. Of course, if I put this in and change this to LG Expo, the campaign is automatically included. Now, let's see how we can combine TAKE with the SORT functions to do a top end or bottom end type of analysis. So, let's say we wanted to grab the campaign names that had the highest cost.
Associated with them. Well, first, we need to get the campaign name sorted by cost. Here, we can use the SORTBY function. This is great whenever you want to sort a column by another column. What we want to return is the campaign name. We want to sort this by average cost. Now, if I leave this unchanged, close the bracket, press Enter, I get campaign names sorted by cost in ascending order. So, Gail's Genius has the lowest cost. If you wanted them sorted the other way around,.
So in descending order, we can just expand on this and define our sort order to be descending. So, -1, and now we get everything sorted by the highest cost first. So, Pinkman's Power is the campaign that has the highest cost. Now, what do I do if I wanted the top three returned? Well, we can put this inside the TAKE function and define three for the rows. If we wanted the lowest cost returned, we would put -3 to grab the last three records. What if I want the.
Posts Related:
- What is Make
- Power Automate Advanced Tutorial Office
- VBA to Upload single or multiple files to Google Drive Office
Highest cost and the lowest cost in the same formula? Well, here's what you can do. You
Need to use the curly brackets, which is always difficult for me to find on my keyboard. Then, you put a 1 for the highest cost because we're sorting everything in descending order, semicolon, -1 to grab the campaign from the bottom, and close the curly bracket, press Enter, and we get the highest cost and the lowest cost returned with the same formula. Now, one thing to.Point out is that I use semicolon here to spill these vertically. If I use a comma, I spill them horizontally. Now, whether you use a semicolon or something else like a backslash depends on your regional settings. I have US regional settings. That's why the semicolon spills these vertically. Now, it's time for TAKE and VSTACK together. We want to grab the top five campaigns of 2023 and 2022. So, 2022 data is in a separate table. Then, we want to grab the top five based on the average.
Cost, so the lowest average cost campaigns. So, we're going to need VSTACK to place one data set on top of the other. And then, we'll probably need to sprinkle in some other functions as we come across new challenges. But let's start from the inside first. VSTACK allows us to grab one array, which is this table in this case, and add another array to this. So, this is the content of our second table. When I press Enter, I get both added on top of each other. The great thing is it's.
Dynamic. If the first table expands, my data set is going to expand automatically. Right? Notice this new line was added, and then it continues with the content of the second table. Okay, so I'm just going to reverse this out. Now, what other function do we need here? We want to sort based on average cost per conversion, right? So, that's our fourth column here. Let's use the SORT function for that. We're going to sort this result that we can see here based on the fourth column..
Close the bracket, press Enter. Now, we have our average cost per conversion sorted in ascending order. We don't want all columns returned, right? We just want campaign and average cost. Campaign is in the first column, average cost is in the fourth column. So, here, because we want specific columns returned, we can use the CHOOSECOLS function. Our array is what we can see here, it"s the result of our formula, and we want to return the first column and the fourth column..
Okay, so we're getting closer to what we want. There is one last step, and you've probably guessed what that is by now. That is the TAKE function. We want to only grab the top five records. So, our array is this, and we just need the top five rows. And that's our results. Okay, so I hope you enjoyed these different ways you can use the TAKE function in Excel. If you can think of other use cases, share it with us below in the comments. I'd love to see those. Thank you.
