Okay, so there is a function in Excel called
the TAKE function. It's a function that's easy to remember, it's also easy to write. But you
might be wondering, "what am I going to use this for?" Well, I'm going to give you some ideas right
now. For example, with TAKE you can grab the last rows of your data set. This makes it easier to do
calculations like the average spend of the last 5 campaigns or calculate the average sales
of the last 12 months. As your data set grows, TAKE is able to go down to the bottom
of your data set and grab the number of rows that you need. If you're familiar with
the OFFSET function, this TAKE function is a lot easier to write and remember. Other there
cool things you can do is something like this: you have the ability to return the names that are
associated with the highest cost and the lowest cost. And here's the great bit, you actually just
need a single TAKE function formula to return 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, here I have my marketing data. Here's the campaign
name, conversions, and marketing spend, and I want to calculate the average of the last five. So,
that would actually be this number right here, and I want to use the TAKE function to make
this dynamic. So, as my data set grows, everything updates automatically. Let's start
from the inside first, and then we can build on this like Lego. The array is my marketing spend,
and I want to return always the last five. 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 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 so much for being here. Thank you for watching,
and I'm going to catch you in the next video.
