25 Must-Know formulas and functions in Excel

1. Course Intro: In 25 minus no formulas and functions in Excel. By Tom for gallium, a Microsoft Certified Trainer. In this course, I'm going to show you that I do many formulas and functions in Excel, including the auto sum using relative references. Using absolute references, I'll show how to link your sheets together with formulas. Well, the, the if statement start, we'll start with a simple F. Then we'll incorporate the end and the ore into the f. And then we'll do a nested IF will do countif, some f average F sumifs, countifs and average Fs. Those are all great when you have a large amount of data. Will do a grid function is called an IF error that can give you a better result. We need to have an error in a formula. We'll do the VLOOKUP, HLookup x VLOOKUP match index. Those are graded for where they have to look up something in a table. And then we'll do some formulas with the digits including heavy subtract these from each other within the network days function. How to add these to it? I'll show you how to use the workday function and the weekday and the weak DM function as well. 2. 1 thru 5: Welcome to the course. 25 must know formulas and functions in Microsoft Excel. My name is Tom for gallery and I'm a Microsoft Certified Trainer. If you have the full course excel file that I'll be using should be part of the course. And this PowerPoint that I'm using should be part of the course as well. Let's get right into it. I want to minimize PowerPoint. We're going to go into Excel. What I'm going to open up the workbook that accounts with the course. My case, it's going to be in my desktop folder. I'll pick on open and then browse. Minus gonna be on my desktop. There's the student file. By the way, I tried to make the mass a little bit bigger Fourier so you can find my mouse. Also sometimes during the class I'll make my mouse do this. You can find my mouse this way. I'm going to go to a different sheet. This particular workbook has many, many sheets. So I'm gonna right-click on this arrow with a bottom right-click. And then you'll see a list of all the sheets. They're not enough for medical order. They're in the order they appear in the workbook. I'm going to scroll back up and we'll start with a sheet that's called AutoSum relative and absolute. The sheet is called AutoSum relative and absolute. And I'll click Okay. Let's start off with the AutoSum. Just in case you don't know about it is very, very important. It's going to be used all the time. You can see in column C, obviously we want to have a column total. So I'm going to click on cell C2 11. Now this could be a big long column all the way down. You click at the bottom of the club. You click on the bottom of the column. And there's a couple of places you can find the autosome, the classic places to be under the home menu, home. And then here's the AutoSum icon over here. But also it can be found under the formula's menu. I'll pick in the formula's menu up at the top. Then we have the AutoSum here. Then typically it'll put the right formula in there for you. It says equals some C14 through ten, which is correct. Usually the autosome will put the proper formula in there for you. Now later on, I'll do another example of the AutoSum, where it doesn't pick the proper range. And then I'll show you what to do about that. By the way, when you click on the pull-down for the autosome, then you can get other calculations like sum, average, count, minimum and maximum. Now, the next step I wanted to show you is something that's called a relative formula. A relative formula is going to help you build your spreadsheet a lot quicker. I'm going to go to cell D for, let's say the February column is going to be a 10% increase over January. Of course, all formulas start with the equal sign. I'm going to click on cell C4. And I'll use the asterisk for multiplication, and I'll type in 1.1. It says equals C4 times 1.1. Then we have a 10% increase. Now, I want to copy that formula all the way down the column. So I'm gonna click on that cell. Following my mouse. I'm gonna move a mouse to the corner of that cell and get the black cross. You have to be right on the corner of the cell to get the black cross. That's called the speed film or the autofill. I can drag it to the right or I can drag it down. I'm going to drag it down. Let's see what happened. The number is filled in, but let's see what happen to the formula. The first one says C4 times 1.1. That's the one I typed in. The next one down says C5 times 1.1. The next one dance is C6 times 1.1. So when I clicked on the cell and then got that black cross and drive the dam, copy the formula. But I changed each one relative to that formula is called a relative formula. When you see most people's larger spreadsheets, it's hardly ever the case where you type in each formula one-by-one, whenever you can, you do that. You're typing the top formula, you get the black cross. By the way, we can also drag it down, which we'll do in a couple of minutes. Or we could drag it across. And then I'm going to drag it down. And then each one change relative to that cell. It's called a relative formula. Once you learn that you're gonna use it over and over again. So what if March is going to have a 10% increase over February and April, we'll have a 10% increase over Match. Watch what I'll do. I'm gonna highlight the numbers in the February column. I'll get the black cross and drag it over to the July column. And others all filled in. There all relative formulas. The next one over it says D4 times 1.1. Here we have e 44. These are all relative formulas. Like I said, once you learn that you're going to use it over and over again. So let's click on this cell before we did it with the autosome. And it says some C4, three, C11, get the black cross and drag it over to the right. Each of those filled in. The next one over says some D4 through D11. The next went over it says some E4 through E11. Those are relative formulas. Now let's do another absolute, Let's do another autosome. So I'm gonna go to a cell J4. Again, I'll pick up the auto sum over here. Then typically it puts the right formula in there for you. I'm going to hit the Enter key. Now let's do another relative formula. I'm gonna get the black cross and drag it down to row 11, and each of them fill them. This one says sum C5 through I5 and C6, H12, O6, and so on. There's see the relative formulas. Now let's do another example of the AutoSum, but this time it's not going to have the correct range. So I'll tell you what the Dow I'm gonna come over here. And I'm actually good at, well, whenever we insert a column, a, column K, I'm going to right-click on column K. And I'll pick on Insert. Here. I'll have the average. Watch my mouse. This time I'm gonna click on the pull-down for the AutoSum and I'll pick on the word average. If you look closely, it did not pick the proper range that time it says C4 through J4. But if I included J4 in the average, that number would not be correct. So watch what I'll do. I'm going to highlight a different range. I'm gonna highlight from C4 to E4. Usually the autism picks the right range for you. But if you can your own range, I'm going to hit the Enter key. Watch that one again. I'm going to pick up the pull-down for the AutoSum, pick on the word average. It did not pick the proper range. So I'm going to highlight my own range. I'm going to go from C4 to E4. And then I'll hit the Enter key. Now, we'll copy it down using the relative formula. I'll get the black cross and drag it down. Each one of those changed. This one says average C5 to I5. Those are relative formulas. So let's go to the third, the third big tip with formulas and functions. We're going to talk about the absolute reference. So let's go to cell L4 here. And I like to get the percent of total. I'm going to take the total for that row and divide it by the grand total. Let's do it the normal way first. So I'll type in the equal sign. Click on this cell J4, divide it by the grand total, which is J 11. It says equals j4 divided by J E11. I'm gonna hit the n or k is 21.74%. Let's see what happens if we copy it down. We're gonna get the black cross and drag it down to row nine. Now this is a division by 0. Let's see what happened. You see there's still relative formulas. This is his J4 divided by J E11. The next one Dan says J5 divided by J182. There's nothing in J182. That's why it says division by 0. But I don't want to divide by j 12, I want to divide by j 11. The next one down is going even farther away. It says J6 divided by J three. Except I don't want to divide by J3. I still want to divide by j E11. The absolute reference is going to be an important one to know about. I'm gonna go ahead and click on cell L4, followed by mouse. I'm going to type in a dollar sign before the DJ and a dollar sign before the 11. When we see the dollar signs in the formula, we are not talking about money right now. That makes it. And what do we, what we call an absolute reference? Which means when we copy it down, that part of the formula is not going to change, it's going to remain constant or absolute, but you really have to sit. I'm going to hit the Enter key. Now the dollar signs don't make a difference until you copy it down. So I'm gonna click on that cell, get the black cross again. And drag it down. Now we have real numbers. Let's see what happens. The next one down says J5, but it's still dividing it by J 11 because of the dollar signs, that's called an absolute reference. The next one down says J6, J7. Notice how they're all dividing it by J E11. That's the results of the dollar signs. That's called an absolute reference. Let's try another example of the absolute reference. So I'm going to go to this cell. The sales tax will be the total for that row times the tax rate. So I'll say equals click on the total for that row J4 times. I'm gonna get this 7%, which is M2. Now that's the part of the formula I want to make an absolute reference. You can either type in the dollar signs or a keyboard shortcut is to use the F4 function key on your keyboard. I'm going to press F4. Now that put the dollar signs in there for me, you can either type in the dollar signs or you can use the F4 function key. And that'll put the dollar signs in there for you. I'm going to click on that cell and copy it down with a black cross. The next one down says J5, but it's still multiplying goodbye M2. That's called an absolute reference. The next one down says JSX, and it's still multiplying them by m2. The first part of that cell parts, the first part of that formula is relative. It doesn't, it says J7, J, J nine, but they're all multiplying that by m2 because the dollar signs, so the second part of that formula is an absolute reference. So hopefully you got the idea of the AutoSum, which is tip number one, the relative formulas, which is tip number two, and the absolute formulas, which is tip number three. The next thing I'd like to talk about is making the formulas that will link your sheets together. I'm gonna go to a different sheet here. By the way, let me get back to the slides. This slide that should come with the presentation if you bought the full course from my website. When I made this slide, the autosomal is under the home menu. It's also under the formula's menu. I picked on home and then AutoSum over here. Then here is a did the auto sum as you can say. Then here is where we did the relative formula. I said C4 times 1.1, and then we copy that one down with the black cross, as you can see in that cell. We copied it down as you can say, these are all relative formulas. And then a copy of the cross isn't this slide, and these are all relative formulas. Then here we did another autosome. Let's go to Slide nine. Then here is the absolute reference as how it has the dollar sign before the J and the dollar sign before the 11, which makes it an absolute reference. Now, let me show you the next step which is gonna be making formulas that will link the sheets together. I'm gonna go back into Excel now. I wanted to go to a different sheet. I'm going to right-click on this arrow. And I'll click on the sheet 2014. And I'll click Okay. So we have the company budget for 2014. If I go to the very next sheet, I see Sheet 2015. Notice how there's a space for the 2014 totals here. Let me show you two ways to link your sheets together. In fact, over here I'm going to type in 2014 grand TO now. I'm going to go back to sheet 2014. I'm going to highlight these numbers on the total row. We'll just do a normal copy. Now I'm going to go to sheet 2015. We're not just gonna do a paste. Well, there's something that's called a paste special. I'm going to right-click on this cell. I'll click on the word paste special. And then you pick on the word paste link. We do a paste special and then paste link. Notice the formula is pointing back to sheet 2014. This case sell 11. Whenever you see the exclamation point in the formula, it means you're referencing a different sheet. We're referencing the 2014 sheet, cell C 11. So if that other sheet changes, then this should change. Let's see if that's the case. I'm going to go back to sheet 2014. Now if you see it as blinking cells, just press the Escape key on your keyboard. I'm gonna change this number over here to say 20 thousand. Now that changed this total to 111 thousand. Let's see if it changed on Sheet 2015. And it did. We copied from one sheet. We went to another sheet and we said Paste Special and then paste link. And now they're sheets are linked together. So if I change y, it'll change the ADA. Now let me show you a second way to link your sheets together. This time we're gonna start on the 2015 sheet. And as we're building the formula, we'll go back to the other sheet. I'm going to type an equals sign here. As I'm building the formula, I'll click on Sheet 2014. Then I'll click in the grand total over here, which is J 11. Now I can keep on going and do more with that formula. But this made the point that I'm trying to make. When I press the Enter key, it will bring me back to sheet 2015. And we'll see this number. I'm gonna hit the anarchy. There's that number. Now I'm going to click on the cell. That is how it's pointing to the other sheet, cell j 11. That time we started the formula on one sheet with the equal sign. And as we were building a formula, we clicked on cells on the other sheet. That's how you can make your sheets together. You can see the numbers says $1,053,075.98. So I'm going to initiate this number. It says 111 thousand. So I'm gonna click on Sheet 2014. I'll change this total of, let's say 18 thousand. Now the total for that column is 114 thousand and the overall total is $1,081,537.49. Let's see if xi 2015 has this, and it does. So there's two ways to link your sheets together. You can copy from one sheet, then go to another sheet and you say Paste Special and then paste the link. Or you can start the formula on one sheet with the equal sign. And as you're building a formula, you can click on Cells on another sheet. By the way, that's how you can make your workbooks together as well. I can copy from a workbook, go to another file and then say Paste Special and then paste the link. That'll make the two files together. Or you can start the file on one workbook. And then as you're building the formula, you can go to the other workbook. So that'll make your workbooks together as well. Now we're going to go to 0 number 5. Number five is going to have a very popular function that's called an IF function. So we'll start with an F. Then in the next couple tips, I'll show you how to make it more complicated. Let me go to a different sheet here. I'm gonna right-click on this arrow. And I'm gonna scroll back up and find the sheet. That's called sum F, COUNTIF and average F. We're going to be on this sheet for awhile, some F COUNTIF and average F and I click OK. Here we have the sales tax. I'm gonna go to cell G2. Let's just say if it says dinner and column F, then they'll get 0% sales tax. Otherwise they'll get the look at the 7%. Now you can type in the formula, but in this case I'm gonna use the formulas menu up at the top, the formulas. And this one happens to be under the logical category, up a gun logical. And I'll pick on F. The logical test. There'll be some kind of comparison. I'm going to click on cell F2, type in equals the word dinner here has to be in quotes because it's text. When you're using these formulas, usually the text will be in quotes like that. I mean the double-quotes. This is F2 equals now it's either dinner or it's not. If it is thinner than the value of true will kick in. And if it's not in the value, if false will kick in. For the value of true, I'll type in 0. The value of false. I'll type in 7% times number for that row, which is the logical tests as f2 equals dinner. The value of true says 0. The value of false, it says 7% times e2. I'm going to click Okay. Now you can see the formula over here. You can always type it in once you learn it. Or we found that under the logical category of your functions. Now you can see how the formula is broken down. We have the logical part, the true part, and the false part. The different parts of the formulas are separated by commas. This one does say dinner, and that's why it says 0 there. I'm gonna go ahead and copy it down with the black cross. Let me show you a shortcut. By the way, of course you can drag it down. But the shortcut is to double-click on the black cross. If you double-click, it goes all the way down. So I think that can save a lot of time. Notice how the ones that say Dina going to say 0 over here. But the ones that don't say dinner have a real number, there's a B. Those would be 7% times the number for that row. By the way, those are all relative formulas. For example, this one down it says F3 and E3, it says F4 and E4. So those are all relative formulas. Now I'm gonna make that column look better. I'm gonna highlight column G will make a currency format. It looks a whole lot better. That's the basic f. Now let me show you ways to go further with the F. 3. 6 thru 10: Point number six or a tip number six here in this course is going to be the formula. So the function is one way to make the FBO bit more complicated. Actually, I'm going to insert a couple of columns here. I'm gonna right-click on column H and I'll pick on Insert. I'll do it two more times. Because I know what I have. At least a couple of more than well, that should be fine. I just titled this column. Let's say I'm looking for the ones that are dinner. And then greater than 30 at the same time. I wanted it to be dinner. Greater than 30 at the same time. Now the end could stand by, by itself, but usually the end is incorporated into another function like the F. Now this one, we're just going to type it in. You can see how a minute, so I'll type in the equal sign. I'll type in f, open parentheses. Open parentheses. I'm going to pick on F2 equals dinner. Dinner is in quotes because it's text. I'll type in a comma. And I'll say E2 greater than 30. I'm going to close the parentheses for the end, and then the normal F is going to continue. I'll type in comma. Let's say I want the word true or false there. Now the word true or false is actually built into Excel, so I don't need quotes for those. Most of the texts I will need clothes for, but the word true or false is built right in. So I don't need quotes for this. I'll type in comma true, false. And that'll be the response to the function. Close the parenthesis. The whole thing says equals if open parentheses. Open parentheses. F2 equals dinner comma E2 greater than 30. Close parenthesis, comma true, false, close parenthesis. Let me show you how the end wax. With the end. You could have multiple contingencies between the parentheses and they're separated by a comma. With the, and all of the conditions have to be true for the whole thing, they'd be true. All right. So it has to be dinner. And more than 30 at the same time. I'm gonna hit the Enter key. Then we're going to copy it down. So let me click there. And then I'll get the black cross and double-click. Here's one that says dinner, but the number is less than 30. So because both of the conditions are not true, it says false here. Here's one that's more than 30, but it's not a dinner item. Most of these are going to say false. Let's scroll down. Here's a true on row 54, are on row 53. The number is more than 30, and it's a dinner item. Both of the conditions are true and that's why this is true here. Here's this as data. The number is not more than 30. Here's one that's more than 30, but it's not a dinner item. You can see with the end of the conditions have to be true for the whole thing to be trow. You can incorporate that into your Fs, especially to make them more complicated. Let's go to our next point. Next tip here, it's gonna be the, or. The, or is another way we can enhance the F Now D or it could stand by itself, but usually it's part of an if statement or something like that. The order will be similar to the F. However, with the OR either of the existence can be true for the whole thing to be true. So let's come back to the sales tax column. Right now if it's dinner, then it's going to get 0. Otherwise everything else will be 7%. I like to include either breakfast, lunch, or dinner. Watch how it's gonna work. Cell G2, I'm gonna click after the parentheses and type in the word or. The or is a function within itself, so it needs its own parentheses. I'll do an open parentheses. I'll type in F2 equals wreck this. The word breakfast can be quoted in quotes because it's text. Comma F2 equals lunch. And the word lunch will be in quotes. I'm gonna close the parentheses before that next comma right there. And then the rest of it is fine. Let's see what the formula says. Equals f open parentheses. Open parentheses. F2 equals breakfast comma, f2 equals lunch. Comma f2 equals dinner. Close parenthesis, comma 0, comma 7% times e2. With the or. If either one of those is true, then the whole thing is true. If it's either breakfast, lunch, or dinner, then they're going to get 0. Otherwise it will be 7%. Now we're going to copy that one down. Watch these ones that say lunch here should go to 0 when they get to buy across and double-click again. Now you can see the ones that say launch went to 0. If I scroll down further, Here's one that says breakfast and that is also 0 and the deniers, everybody. The OR is another way to enhance your F. And that's one that gets used quite a bit. Now let me show you something that is called a nested F. And this would be our next tip within the class. The nested F is going to make a little bit more complicated. So what this really means, what if breakfast, lunch, and dinner is going to be 0, everything is going to be 7% except for NO team. A team is gonna be 10%. I have another condition, so I need another F. Watch it, I'll do, I'm gonna go to this cell G2. Then I'm going to click right before the 7%. I'm gonna click right before the 7%. And I'll type in if open parentheses. F2 equals entertainment. And entertainment will be in quotes because it's text comma 10% times e2, 7% times e2. I need a closed parenthesis over here. Everything was fine up until the 0. And then I have comma f open parentheses. F2 equals entertainment comma 10% times e2, comma 7% times e2, close parenthesis, close parenthesis. So what this means is breakfast, lunch, or dinner will give us 0, and our table will give us 10%. Everything else will give us 7%. But I first started with Excel a long time ago. You were able to have seven fifths in one statement, which you would think there would be enough right? Now, you could do 64 F's. In one statement. It gets really crazy, but I would do them one at a time. And you agree, Always start to make the math a little bit more complicated. This is a nested F. Now you can really have up to 64 F's and one statement. I've seen ten or 12. And realize that even that it started to become pretty complicated, I'm going to hit the Enter key. Again. We're going to copy it down. Now watch these ones that say entertainment. There should be a different number. I'm gonna get the black cross and double-click. I saw those ones that say n is Haim, it, they changed. Now this number is 10% of this number. And this number is 10% of this number because their entertainment. So we started off with a simple f. We add the clause over here. That was the next step. We added the or clause. That's another very popular function that was the next step. Then we took it even further. We did a nested F and you can start to see, those are ones you're going to use all the time. Let's go back into our slides now I have a couple of slides for these. Actually, we're going to skip a couple of slides. In this case. I'm going to come up to the account if right now. If I go to Slide 18, there is the if there is an f with an OR, then go to slide 19. There's an F with the end. This slide deck does have a few of these examples in that. Now, let's go back to slide 17, And we're gonna go through the next couple of tips here. This will actually take up six of the important functions. We're gonna do, countif, sum and average F. Then we'll do the more complicated variation of those sumifs, countifs and average fs. These are really great when you have a large amount of data. So I'm going to go back into Excel. I'm gonna come over here to the right. I want to count how many are breakfast items. If we just use the normal count function, it'll count everything. We're going to use COUNTIF. This one, we're just going to type it in. I'll say equals COUNTIF. Open parenthesis. The range. That's where we're looking. I'm looking in column F. I'm gonna highlight Column F, type in a comma. The criteria is what we're looking for. That's why I have the word breakfast right there. And so K2, and I'll close the parenthesis. It says equals countif, open parentheses, f colon, F comma K12, close parenthesis. And I'll hit the anarchy. The 16 means there are 16 occurrences of the word breakfast and column F. I'm going to click on that number, get the black cross and double-click. Just that quickly. I have the counts of all those categories. You can see how useful that would be when you have a larger metadata. And that was, that was our ninth important tip today. And that was called count. For the 10th important tip. We're gonna do some F and it'll be very similar to the account F. I'll just type this in. I'll say equals sum. Open parenthesis. The range is going to be column F. That's what we're looking at. Type in a comma. The criteria is the word breakfast right there in cell K2, type in another comma. And the sum range means the numbers I want to sum up for the breakfast items. So I'm gonna go back to column E. I'm going to highlight column E and then close the parenthesis. So we have some F, f colon, F comma K12, comma E coli and E plus parenthesis. The breakfast items came out to be 7385. I'm gonna go ahead and copy that one down with the black cross. You can see how these are really helpful when you have a large amount of data. 4. 11 thru 15: Then the next important tip here is going to be the average F. And it'll be very similar to some f, I'll say equals average. Open parenthesis. The range is still column F, that's where we're locking type in a comma. The criteria is still the word breakfast right there. And so K12, then another comma. The average range is the numbers I want to average for the breakfast items, which is column E. I'll close the parentheses. It says equals average f open parenthesis, f colon F comma K2 comma E Colon E plus parenthesis, and I get 462. I'm gonna copy that down with the black cross. Those are pretty useful, COUNTIF some F and average F. Now the next couple important formulas I want you to know are gonna be the more complicated variations of this. Now I have more than one criteria. I'm gonna come over here in column P. I want to get this some other ones that are France and breakfast at the same time. I'm just going to come over here and I'll type in average. There'll be an average F. All right. I'm going to go to cell p2 and we can type this on it as well. I'll type in equals average F, I'm sorry. Sum f with an S at the end, some Fs. When we put an S at the end of these, then you could do multiple criteria. So we'll do an open parentheses looking for the sum range first. This time I was looking for the numbers. I'm gonna highlight column, go back to column E. I'll type in a comma. The first criteria range is still column F. I'm going to have a column F and type in a comma. The first actual criteria is the word breakfast and Cato. The whole point of the sum Fs is to do multiple criteria. So I'll type in a comma. The second criteria range is way back in column B with the countries might know how to column B. Then I'll type in another comma. And the second actual criteria is the word France over here in cell O2. I'll close the parenthesis. So the whole thing says equals SUMIFS, open parenthesis, E coli and E comma f colon F comma comma b colon b comma o2 plus parenthesis, we get 2423. All of the breakfast items where 7385 out of those 24th, 23 where from France at the same time, when we use some Fs or account Fs or average F's, then we could do multiple criteria. I'm going to copy it down. Then the next important tip are important formula here is gonna be the count Fs, and we'll type that one in. So I'll say equals count the F's open parenthesis. The first criteria range is still column F over here. And I'll type in a comma. And this first criteria is the word breakfast right there. And so I'll type in a comma. Now the second criteria range is way back in column B with the countries. And I'll type in a comma. And the second actual criteria will be the word France in O2. And I'll close the parenthesis. It says equals countifs, open parenthesis, f colon F comma K12 comma B column B comma o2, close parenthesis. All of the breakfast items are 16. And out of those four are from France at the same time, and I just copied it down. Then let's do the average Fs, which would be the next important formula. The equals average Fs with an S at the end. Open parentheses. And you're going to see it's very similar to the sum f. The average range is going to be the numbers in column E. I'll type in a comma. The first criteria range is column F, and another comma. The first actual criteria is the word breakfast right there. And so K12 will type in another comma. The second criteria range is way back in column B with the countries. And I'll type in a comma. And the next criteria is the word France. And so I'm gonna close the parenthesis and you can see the formula. It says equals average open parentheses, E Colon E comma f colon F comma K12 comma B column B comma o2, close parenthesis. Then in this case I'm gonna copy it down. Now some of them say division by 0 because in this case is trying to divide by 0 in that row. There wasn't any that were from France that were also gets at the same time. In that case, I'm gonna go ahead and format them as currency format. Now let me show you another important formula. We'll make this the next important one. If I wanted to have a different message there, will use one that you're going to use all the time is called the error. With the IF error, I can replace an error message with maybe something bad up. Watch what I'll do. Now you could use the IF error with any kind of formula. I'm going to go to cell R2 and I'll type in IF error after the equal sign. That would find any kind of error in the formula that I have, the formula there. I'll type in a comma. If that formula gave me an error, then I'll just type in the 0 there. I can type in something different if it is an error. And I'll close the parenthesis. Equals if open parentheses, average open parenthesis, E Colon E comma f colon comma K12 comma B column B comma o2, close parenthesis, comma 0, close parenthesis. What this means is this formula gives me an error of any kind, then put the number 0 there, otherwise use the value of the formula. So I'm gonna hit the NRK. I'm going to copy it down. Watch these ones that say division by 0 though should just change to 0. Now, I'm gonna copy it down. And that's exactly what happened. That's another important formula that you're going to use is called the IF error. You can use that with any other formula or any other function to give you a better result when you do have an error. Let's go back into our slides now. If you look at this slide, the next slide, 17, at least has the COUNTIF, the sum f and the average F. If you bought the full course, then you also have a copy of the workbook with all of the finished form of us. So that's another place you can find them. Now, let me go back a couple of slides. We'll see some of the next must know formulas starting on page, starting on slide ten. 5. 16 thru 20: Now we're going to do VLOOKUP, HLOOKUP. There's a new one that's called the X lookup. And we'll do match and index for the next couple must know formulas. One that people really struggle with is called VLookup. I'll share a couple of ways to use the VLookup. I'm gonna go to Excel now. I want to go ahead and go to a different sheet. It should be the next sheet here I'll pick on customer list. Here you see a list of all the customers. It has the customer ID, the company name, and the contact name. Everything about the customer is on the customer list sheet. Then click on the ordered list over here. And this has a list of all the orders as the order ID in the customer ID and the employee ID. Everything about the order is on the order list sheet. Notice on the ordinary list she is showing the customer ID, but not the company name. If I went to share the company name here, I have to take the customer ID from this sheet, look it up on the other sheet, find the right one, and then return the proper company name back to here. What that is exactly what the VLookup does. Let's try it. I'm going to insert a column in column C. Over here. I'll type in company name. I'm going to say, I'll see Tale. Of course you can type in the formula, but in this case we'll do it the other way. I'll pick on the formula's menu. So these next few gonna be under the Lookup and Reference category. Does previous ones, a lot of them were under the logical categories, like there's the F and the end and the, or. Some of those might have been in other math and trig here we have average. If some F, It's probably going to be down. There are many different categories. But in this case, this is going to be next year will be under the lookup n reference category. And then the last couple of functions that we do today will be under the date and time category. But let's go and look up at reference. Then I'm going to scroll down and use when that's called V lookup. V actually means vertical. In a couple of minutes when we did the HLookup, H means horizontal. It depends on how the table is laid out. So I'll pick on V lookup. The lookup value is what we're trying to look up. I'm trying to look up the customer ID in cell B12. So that's just a bientot. The table array is where we're looking. Usually this is the one that's on a different sheet. It doesn't have to be on a different sheet. But many times it is. I'll pick on this sheet that's called customer list. Now for the VLOOKUP, you have to highlight the column that you want to match. And you'll also have to highlight the column that you want to read a time. So for that reason, I'm going to highlight Column a at the top and then drag it over to column B. That should say customer list exclamation point a, column B. Again, when we see the exclamation point in the formula, it means you're looking at differentiate. I want to match column a, that's the first column of the table. But I have to include column B because I want to return that. The column index number is calculated like this in the table. Column a is column one and column B is number two. I'm gonna come over here and type in the number two for the column index number, which means I'm going to get the second column of the table, which is gonna be the company name. That's why I had to include column a and column B here. Let's see what range lookup says. Range lookup is a logical value. That means there's going to be the word true of the word false. To find the closest match. The type in the word true there. To find an exact match and type in the word false. I'm going to type the word Falstaff in a few minutes. I'll show you when they don't have the word trip. For the lookup value, I have B2. That's what we're trying to look up. That's that customer ID over here. The table array says customer list columns a through column B. So I'm gonna try to match column a. And then I have to include column B because that's the cotton on when I return the column index number says to attend this second column from the table, which is really column B, which is gonna be the company name. And then the range lookup says the word false, which means I want to do an exact match. I'm gonna click Okay. There's the VLOOKUP formula. You can always type it in. Or we found that under the lookup and reference category. I'm going to expand that. Let's think about what it did. It took v INET, it looked it up on the other sheet, found the right one, and then brought the company name back to here. So it looks like the VLookup that is job. I'm gonna go ahead and copy that down. Now they all fell down and by the way, those would all be relative formulas. Let's see what the word false means. At the end, I'm gonna come over here in cell B12 and I'll just type in AAA. Notice how it says NA means not available or not found. We were trying to find an exact match. It didn't find an exact match. So it says n. Let me put that back to say VIN. It found it. That's the first variation of the VLOOKUP. And that's going to be over here in this slide deck on slide ten. Let's go to Slide 11. Now this would be a variation of the VLookup that'll have the word true at the end. Let's see what that one does. So I'm gonna go back into Excel now. Now I'm gonna go to the sheet down here that is called VLookup. In this case, it's trying to look up the salary and find the tax rate for that salary. This time the lookup table is on the same sheet. There's the VLOOKUP, VLOOKUP B2. I'm looking at the salary over here. This time I have the range specifically laid out as his D2 through F7. That's what we're looking. What I have in light blue over here. Number three means when I find the right one, I want to go to the third column 123. So it's going to return the tax rate. That's why I haven't include column F over here, so I can go three columns over. Now notice how the fourth parameter is not even there this time. The fourth parameter is optional. Let me show you why it's optional. I'm gonna click on that cell and I'll pick them, the classic fx over here. Then you'll see the parts of the formula. The ones that are in dark black required and the ones that are in light black like that one means it's optional, that has a real purpose. The ones that are in dark black are required and the ones that are in light black are optional. So I'm going to click where it says range lookup and see what that says. Range lookup is a logical value, means it's going to be the word true or the word false. To find the closest match. You'll put the word true there. Or when you omit it is the same thing as saying in the word true. When that fourth parameter it says the word true, or when it's snack, there is still means there were a true means. We're going to match the range. To find an exact match, we'll type in false. So I'll click Okay. Let me change this to a different salary. I'll type in 30 thousand and it gives us 31%. If that feedback up had the word false at the end, then it would say NA here because 30 thousand is not on this list. But the fourth parameter is not there, which means it's going to be true, which means it's going to match the range. So everything between 273585 will give us 31%. If I come over here and type in 60 thousand, then it'll give us thirty-six percent because everything between 5851318 will give us 36%. That's what we're use the VLookup when, when, when we have a true type of situation, or when the fourth parameter is not there, it's still remains true. Let's go to our next important function, which is going to be called HLookup. Because I'm going to get to the next sheet here that we have HLookup. H means horizontal. The table is laid out going across, but it's going to have the same syntax as the VLookup. If I come over here to cell B3, there's the edge lookup. B12 is the southern. We're trying to look up. This time. The range is y1 to J3. What I have in light blue over here. Now it's trying to match the first row, whereas with the VLookup you try to match the first column. So as long as it the table, it's on its side. The three means when I find the right one, we're going to go three rows down. Notice how the fourth parameter is not there. If I click on this fx, just like with the VLookup, the fourth parameter is optional. When it's not filled in. It means true, which means it's going to do a range match. So it's very similar to the VLookup. In this case, I'll type in 40 thousand for the salary. And it gave me 31% because everything between 273585 will give us that 31%. The H means horizontal is when the table is going across. But I've seen that just as many times as the VLookup. So that's another important function that you should know, H lookup. Now everybody, if you have a newer version of Excel like I do, then let me show you the next important one effect. Let's go back to our slides for a second. Here's the VLookup. Now this one has the word true at the end. Or remember you can leave the fourth parameter often is still means the word true, which means it's going to match the range. And then I'll slide 12. There's the HLookup, same kind of thing, except the table's going across. So it's horizontal. This slide 13. Now if you have a newer version of Excel, you can use x lookup. I think X lockup is very powerful and I think it's simpler to use than the VLOOKUP and HLOOKUP. It can actually replace both of those. Let's try an example of the x lookup. I'm going to go back into Excel. And I want to go back to the other sheet. I'm gonna go back to the order list sheet where we did the VLookup. I'm going to insert a new column here on the right click on column D, and I'll pick on Insert. Over here, I'll type in contact name. Alright. In cell D2 will do the x lookup. And this is also going to be under the Lookup and Reference category of your functions. Do look at my reference on the scroll down. And if you don't have, if you don't have a newer version of Excel, you're not going to see the word X look up. It's about a year old, so you'll have to update to a newer version of Excel. If you don't sit, I'll click on X lookup. The lookup value is still the customer ID over here in cell B2. I'm going to click where it says lookup array. I'm going to go back to the customer lists sheet. Here. I just have to highlight the column I would imagine is column a. I don't have to include the additional columns, just the column I want to match. Now I'll click on this space where it says return array. I'll click on the customer lists sheet. I'm just gonna highlight column C. I think they made it much simpler than the VLOOKUP. And you can actually use this to replace the cup as well. The lookup value's is B12. That's what we're looking for. The lookup array is just a customer list sheet column a, that's where we're looking. And then return array is the customer lists Sheet Columns C. That's where our returning. And I'll click Okay. And now at found the contact name based on that customer ID. Of course, I'm just going to copy it down. There's the x lookup. I think it's very, very useful. I think it really is a must know formula. If you have a newer version of Excel that can actually replace the VLOOKUP and HLOOKUP. I think it's a lot easier to use. We're going to go back into our slide now. And that was on slide 13. And that was the 18th. Must know formula. Let's go to slide 19, slide 14. Now we're going to talk about the match and index. Match is going to be the 20th important function and index would be the 21st. Match. An index is another way to look at this information on a table. Many of your legacy spreadsheets, we'll have the matching index and they're spinning around for a long, long time. Going forward, I would recommend that you use the lookup. The excellent cup can actually replace the match and index as well. The match and index, it's in so many different workbooks. So I recommend that you use this one as well, or at least learn it. Let me go back into Excel now. Now there should be a sheet over here that is called match and index. Again, in real life, you can use a much bigger table. I want to try to match the row that we're looking for. We're going to try to match the column that we're looking for, then the index will find the intersection of those two. That's why it's called match and index because they go together, although they are really separate formulas. In cell B4, these will also be under the Lookup and Reference category, which is Lookup and Reference. And I'll pick on the red match. The lookup value is what I'm looking for, which is the word July in cell B1. The lookup array is where I'm looking. I'm looking in column D. I'm going to go from D to D for team. That's where we're looking. Now if you go to your help screen, you can always go to this window and click on Help on this Function for any of these, then the match type is looking for 10 or negative one. It'll tell you that 0 is for an exact match. So they usually, with the match, I'll have a 0 that my lookup value is B1. Lookup arrays as D1 through D4. And the match type says 0, I'm going to click Okay. It says the number eight here because it starts here. This is number 112345678. Such utilize the eighth route. Remember you would use this on a much bigger table. Then I would use another match to match the column that I'm looking for. So again, I'm going to pick on the lookup array reference category, and I'll pick on the word match. The lookup value will be the word rockets in cell B12. The lookup array is, we're going to go across this time. I'm gonna go from D to H. For the match type, I'll type in 0. My local values is B12, the look of a race as D1, the H1, and the match types of 0. Now we know that July is the eighth row and that's bronchus is the third column going across. Then the next must know formula is going to be the index. It is a separate formula. The index will show us the intersection of this two. I'll click on lookup, a reference pic on the word index. Now there's two variations of the index function, so I usually use the first one. And I'll click okay. For the array, you're going to highlight the entire table. I'm going to highlight the entire table. And this time, then for the row number is gonna be the first match that, which is over here in cell B4. And the column is the second match that we did, which is over here in cell B5. Array is the entire table D13, age 14. The row number says before, which is where we did our first match. And the column number says B5, which is where we did our second match. And I'll click Okay. It says 3337. Is that correct? July 1st, brackets is 3337. Usually, I'll do two matches, one for the row that we're looking for and one for the column. And then the index will find the intersection of those two. That's called the match and index. Those are other must, must know formulas in Excel. Let's go back into our slides and we'll see the first match on slide 14, the second match on slide 15, and the index on slide 16. Now if I go to Slide 17, we already did the count F. Then 1819, have the f's. Now, let us show you a couple of date formulas. And I think these date formulas are going to be useful for you as well. And I think these are very important to know. That's why I included them in the class, of course. So I'm gonna go back into Excel. 6. 21 thru 25: I'm gonna go to a different sheet now. I'm gonna right-click on this arrow. And I'm going to scroll down and I'm looking for one that's called date formulas. The sheet is called the formulas, and I'll click Okay. Now here I have the holidays for 2021. So if you're looking at this course in a different year, you would make sure you type in the holidays, the dates for the holidays of the current year. The D is going to work for you. So let's see how we can. Let's say I'm gonna put today's date in there. I'm recording this. Here's a keyboard shortcut for today's date control semicolon. That is the current date. One important thing that you should know about the formulas is being able to subtract one formula from another. Let's see what happens. I'm going to type in the equal sign. I'm going to click on today's minus January 1st. It says equals A2 minus A1. When you subtract one date from another date, it'll give you the difference in days. But those are calendar days. But I think that's really important to know when you subtract one date from another date, it gives you the difference in days. Now what if I want the number of business days between those two dates? Then we're going to go to our next important function, which is called net work days. Everybody, I'm gonna come over here. And once again, we'll go to the formula's menu and we'll go to the date and time category. That's called network days as click that. The start date will be January 1st and the end date will be 125. I'm gonna leave the holidays blank for right now. I can do that because it's optional. I could tell it's optional because of its light black over that. I'll click. Okay. There was 338 calendar days and 241 business days. The network days will take away the Saturdays and Sundays. Then they really made this complete. You would want to include the holidays. That's why I have the dates of all the holidays over here. Like I said, if you're watching this course in a different year, you would get to the stage for the holidays for that year and then fill in your workbook with those. Watch how we're going to incorporate the holidays. I'm going to click on the FX again. I'm going to pick on this page for the holidays and then you highlight your list of holidays for that year. My case is I2, I3. I'm going to click Okay. Now, that gave us the the 230 is the number of business days between those two days, including it took up the style of this and this Sundays and the holidays. I really liked that formula alive. The network that is formula gives you the number of business days between two different dates. So that's why I included it in our important formulas today. The next one I'd like to show you, let's say the order date was today's day, but I wanted to go 30 days beyond today's date. So another important thing you should know about your dates is being able to add a number of days to a date, so I'll say equals today's day plus 30. Now when we do it, well, let me put the order data and do Control semicolon for the order, which is today's date. Here, I'll say equals this state plus 30. Now when we do it that way, that's going to be the number of calendar days, 30 calendar days. And it gives us 142,022. When I just add a number or subtract a number from a date, it'll be the number of calendar days. So that's another important formula that you should know about. However, what if I wanted to add 30 business days to that date? Then we're going to use another important formula that's called Workday. In this case, I'm gonna go back to the date and time category. I'm going to scroll down. Then let's see what Workday says. Really is going to add the number of business days to the day to get the result. The start date will be today's date. The number of days is 30. And then notice how this one can also include the holidays. So I'm gonna highlight the holidays and then highlight the list of holidays over here. I'll click Okay. Now we'll see how that gave us a number. The dates are always stored as a number in Excel. I would like to convert that to date format. I'll pick on the home menu. And we'll make it short date format. The order date is 125. This due date is 30 calendar days beyond that due date. And this one because I use the workday function, is 30 business days beyond that date. Let me show you two more important functions here. I like to see the day of the week that these happened on. Over here. I'll type in the word day and I had to see what week these fall on. These are a couple of more important functions that you should know about. We're going to use the, we're going to use the weekday function here. This will also be under the date and time category. I'll pick on date and time. I'm going to scroll down. Let's see what the word weekday says. It's going to return a number from one to seven. The serial number will be that day. Now, you look in the help screen, I'm going to actually go to the Help window here. It's gonna say if I leave it normal, then the one is gonna be Sunday. If I scroll down on this Help window, yes, He one is going to be Sunday. So we can just keep it like that. I'm going to close that window for the help. Now we know one is Sunday. I'm going to click Okay. Now I'm going to highlight column J, and I'm just going to format that as number format will take away the decimal places. I'm gonna copy that down. Then I'll lead to capture the week of the year that these fall on. Once again, we'll go to the formula's menu and I'll pick on date and time. And we have one that's called weak num, that was called weekday. This is called weak num. Again, the serial number will be the day, and I'll click Okay. Again, I'm going to format that column is numbers, 0 decimal places. And it was this copy it down. As far as the date the days are concerned. If you subtract one from another day, it'll give you the difference in calendar days. If we used a network that is function and we can get the difference in business days including the holidays. Over here when I added 30 to that date, that was 30 calendar days. But when I use the workday function, then I can add the number of business days to get to the end result. Then we got the weekday function and we got the week numb function. Actually, let's take it one step further and we'll wrap up the course here. Let's say I wanted to go change that day to the actual day. So over here I'll type in the word day name. Well, over here, I'll just make a quick little table. One this seven. Then over here, I'll have Sunday. Then we'll make a list from Sunday to Sarah. This is just another quick example of I can use the lookup, will go back to the lookup, a reference category. We'll do x lookup. I'm trying to look up this day number over here. The lookup array will be column, oh, that's what I'm gonna try to match. And the return array is column P. And I'll click Okay. Now I have the actual day name of when those dates happened. I actually showed you a bunch of different, very, very important I will call the must know formulas and functions today. I hope that it was helpful for you.