This is a continuation of my 20 Essential Excel Functions video. Follow
the link at the end of this video if you missed it. The 20 Essential Excel
Functions will do 95% of what you need in Excel, but for the other 5% it
requires advanced Excel skills. Here are 10 common tasks you often need in Excel
that are not represented by any one function. You'll have to combine
functions or create complex formulas to do them. Welcome to Sele Training. To get
you started on the 10 Advanced Excel Functions please download the reference
guide listed in the description below this video, which is available from the
seletraining.com website. You're welcome and encouraged to copy and
distribute this spreadsheet. The reference guide has a separate tab for
each Excel advanced function. It includes a name and description of the advanced
function, a link to the specific point in this video where the advanced function
is explained, a sample list of values and their results, additional notes, and links
to additional help on the Microsoft Office support site for each function
used. Let's get started. NESTED IF. The IF function lets you choose
between two binary choices, but what if you have three or more? That's when you
need to do a NESTEDIF. For example, let's do an IF function. When this is greater
than 10,000 we're going to have it pick "Good" and I'm going to hit f4 to lock
that in as an absolute reference, but when it's not we actually have two other
choices. So, I need to do a NESTED IF and check this condition again. When it's
greater than 5,000 we're going to make it "Fair", F4, and everything else we're
going to make "Poor", F4. Now make sure that you put parentheses in the right
locations. That's one of the challenges when you have NESTED IF's. And there you
have a NESTEDIF function. INDEX-MATCH. When you combine
INDEX and MATCH together you get a function very similar to VLOOKUP, but
with more flexibility. For example, let's start with MATCH. If we want to match the
name Jim in this list, and I'm going to hit F4, and I want to do is zero for an
exact match. You can see that it returns the fourth row from that list that I
selected. Now let's take the INDEX of this entire array. F4 to lock it in and
choose that row number, and I'm going to select the value from column 2, and you
can see that it returns the value from the second column matching Jim in row
4. The INDEX and MATCH functions are very powerful when you combine them together
and the flexibility comes because you can use wildcard characters for the
match and it ignores upper and lower case. That's a little bit different than
the way VLOOKUP works. INDEX-MATCH also lets you insert a new column in the
middle and it dynamically adjusts the column number where with VLOOKUP you'll
have to edit the formula to correct it. EXTRACT DOMAIN. Let's say we wanted to
take the domain name out of an email address. Well, I can do that with the
RIGHT, LEN, and FIND functions. I'm going to take the right portion of this
email address and the number of characters that I'm going to remove is
the location of where the @ is plus one. Well, that location is based on the
length of that where we find the @ symbol in that text and there you go. Now,
you could remove the name from the email address as well by using the LEFT
function. So we want the LEFT of this email address and how many characters do
we want? - where we find the @ symbol in that text. CHOOSE RANDOM. We can
combine the CHOOSE function and the RANDBETWEEN function in order to assign a
random choice from a list to these lists of values.
So first off, let's take a look at the RANDBETWEEN function. This lets me choose
between two sets of numbers and you can see that it randomly selects between 1
and 3. Well, I'm going to add to that the CHOOSE function to randomly select
between 1 and 3 values from here, here, or here. Now the key is I need to F4 to lock
these and you can see that it assigned a random One. Let me copy this down. It
randomly selects from the other choices. A key with RANDBETWEEN is that it will
change the result anytime you refresh the data. So, if I were to recopy this
down you can see that they change. So every time you refresh your spreadsheet
or recopy data it's gonna randomly select again. COUNT WORDS. Here's a trick
to counting the number of words in a text string. In this text string we have
five words, which is essentially four spaces plus one. We can calculate the
number of spaces by taking the length of that text string, minus the length of
that text string, by substituting all the spaces with nothing and we'll add one to
it and you can see that it calculated five words. Now we can copy this down and
we have a problem here because there's additional spaces. So what we should do
is add a TRIM to remove all the extra spaces before we do the calculation and
it calculates correctly. Now we can do a little bit more complex calculation by
trying to find the number of words in multiple sets of strings. In order to do
that we use the SUMPRODUCT, then we'll put it around essentially the same
formula. So we're going to take the LEN of the TRIM but this time we're
going to pick both text string values and we'll
subtract the LEN of the SUBSTITUTE of both text strings, replacing the spaces
with blanks and adding one, and end-parentheses for SUMPRODUCT, and that
correctly counts the number of words in a range of text rings. MOSTFREQUENT. You
can combine the INDEX, MODE, and MATCH functions to find the most frequent
occurring word or number in a list. Let's start by taking a look at the entire
formula. You can see that it uses INDEX, MODE, and MATCH for the range B7 through
B13. It's also got curly brackets, which is an indicator that I've used Ctrl-Shift-Enter
to enter it as an array. Now let's take a look at each component. I do
the MATCH of this range as the lookup value, and I'm looking them up in the
entire array, and an exact match. If I hit F9 this will show me the values in the
array that are calculated from that formula. So you can see that it's 1 2 3 3
2 1 3. That means that John is number 1, James is number 2, Sally's number 3, Sally
again at 3, James at 2, John at 1, and Sally again at 3. So it's assigning a unique
number to each value in the data set. I'm going to hit Ctrl-Z. That returns me
back to my MATCH. Now if we take the MODE of that same value and hit F9 you can
see that it chooses 3. That means that Sally, the third value in the list, is the
one that's the most frequent. Ctrl-Z. Now all we have to do is apply the INDEX
for this same range with option 3 that we've selected from our MODE and MATCH
in parentheses and then Ctrl-Shift-Enter to enter it as an array and
there's our result. OFFSET-SUM. You can combine the SUM function and the OFFSET
function to sum an array of numbers starting at a reference point. Now the
key is this is an array, so we have to treat it
a little bit differently. So, first off, let's take a look at the OFFSET function.
OFFSET asks for a reference point. I'm gonna choose the upper left corner and
then it wants to know how many rows down. We will go down one and how many
columns over - one. Then it asks for the height and width. I'm going to choose
7 rows high and one column wide. Now if you hit f9 you can see that the
results of that function show me every value in that first column.
Ctrl-Z takes us back. I'm gonna wrap this in the SUM function so that it sums
everything in that first column and you can see my total matches the total here
so it's calculating correctly. Now we can take this a step further by adding the
MATCH. So, I'm going to take this A6 and make that an absolute reference. I'm
going to copy this formula down to the next line and on this line let's add a MATCH.
So instead of column 1, right here I'm gonna have it select the MATCH of this
lookup value, in this range, and I want it to be an exact match. And let's make this
an absolute reference. So now when I hit ENTER it shows me the total of Value 2
and now you can come over here change this to Value 3 and it automatically
shows me the total for the Value 3 column. Now, to make this even more functional
let's go over to the data tab, click on this link here for data validation, and
we're going to choose a list, and the source of that list is going to be these
choices right here. Hit OK and now I can select value 1, 2, 3 from the list and you
see that it changes based on my selection and you can see the power of
the OFFSET-SUM combination. You can also use AVERAGE and other array formulas
instead of SUM. SUM n ROWS. Let's say we have a list of values for Monday through
Friday dates and we want to SUM the weekly totals. We can use
a combination of the OFFSET-SUM functions and the ROW function in order
to do this calculation. Let's start by putting in the SUM of the OFFSET and the
reference point we're going to start with is C7. We want to lock that so it's
the same regardless of the week. For the rows we're going to take the current ROW
minus the ROW of C7, and I'm going to lock that in, and we're going to multiply
that times 5. Now what that does is on ROW 7 minus ROW of C7, which is seven,
gives us zero, times 5 is zero. So our row offset is zero to start with but
when we move to week 2 it's going to be ROW 8 minus ROW 7, which gives
us 1 times 5. So that means that we're gonna offset 5 rows. For the
number of columns we're gonna stay with the current column so I'm going to put a
zero in there, and we're gonna SUM 5 rows, and one column wide and you can see
the first five Monday through Friday sums to 48. Now that we copy this formula
down to week2. You'll see that it now copies Monday through Friday for a total
of 65. And that's how you do a SUM n ROWS formula. LAST VALUE. By now you
already know that you can look up a value in a list with VLOOKUP or INDEX-MATCH
and return data from the second column, but how do you find the last
occurrence of a value in a list? This is an advanced excel challenge. Let's say we
have a list of names with a set of values associated with them. I want to
find the last occurrence of James in this list and you can see there's three
of them with the last one on row 14. in order to do this calculation we're gonna
need to do an array formula so I'm going to start with one of the core things
from the array. I want to get the ROW for this set of data and if I hit F9 you can
see that it gives me 7 through 15, which is the rows for all those values. I'm
going to hit Ctrl-Z to get my formula back and I'm going to
subtract from that the MIN rows from this list and the minimum of course
would be 7 but we'll add one to it. So when you F9 on this entire array you
get the values 1 through 9 and that's what we're going to use in the
rest of the array formula. Ctrl-Z. I'll leave that there but we're gonna copy
this and put it into the second part of the formula in a minute. Now for the rest
of the formula we're going to do the INDEX of this entire array because we
know that INDEX is what we can use to pick the row to show us the value
from the second column. And the row number that we want to select we want to
be the last occurrence of James in that list, so I'm going to use the MAX
function, where, if this value equals James then we want it to pull the row
from the formula we created earlier so I'm going to paste that in and if it
doesn't equal James then we'll do nothing. So I'm going to leave that
second parameter blank and then for our index array I want to return the value
from the second column so only when the array value equals James will the IF
function be true, in which case it's going to return the row number from the
calculation that we did earlier, and we're taking the MAX of those values
because it's going to find three occurrences of James, but we want the
maximum one, which would be that row 14. So when I hit Ctrl-Shift-Enter it
gives us our value 9, which is the third occurrence of James in the list. And
that's how you get the last occurrence of a value in a list and return data
from the second column. INDIRECT MATCH. You can combine the INDIRECT and MATCH
functions to dynamically gather data from a table. Let's first take a look at
the INDIRECT function. If I do the INDIRECT of this cell it gives me the
value from B7. You can do the same thing with a range, so let me SUM the INDIRECT
of that range and it gives me the sum of B7 through B13
which is 34. Now you can also use named ranges so I'm going to go over to the
May section here, highlight it, go to formulas and define the name as this
location, and we'll do the same thing for April. Now if I do the SUM of the
INDIRECT of May it correctly calculates May totals - 36. Now let's do a little more
complicated calculation here by combining some other options. Let's do
the SUM of OFFSET, and the reference is going to be B6 - the heading for April.
Number of rows we want to go down is one, which takes us to the value 9 and the
columns is going to be where we MATCH this name in this list, and we need to
subtract one so that we get it back to zero If it's the current column, and the
height is going to be 7 rows that we're going to sum up, and it's going to
be one column wide and that gives us the total for April by looking up the name
"April" for the heading. So if I were to change this to May it gives us the total
for May, and this is how you can use the power of INDIRECT and MATCH and combine
it with other functions. Hey, if you want to see more videos like this one please
subscribe and if you've enjoyed this video, be sure to click the Thumbs-up and
leave a comment. I really do appreciate your support!