Popular Excel Functions and how to use them. | Thomas Fragale | Skillshare

Popular Excel Functions and how to use them.

Thomas Fragale, Microsoft Certified Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
2 Lessons (1h 25m)
    • 1. Functions and formulas

      71:41
    • 2. Excel Nested If Formula

      13:02

About This Class

This course covers some very popular functions in Excel and how to use them.

It covers:

  • AutoSum
  • relative formulas
  • absolute formulas
  • Vlookup
  • If
  • Sumif 
  • Countif
  • Averageif
  • Sumifs
  • And
  • OR
  • Today
  • Networkdays
  • Lesson 2 includes the powerful nested if

Transcripts

1. Functions and formulas: now Here we are in Microsoft Excel. So here we have. Ah, the start of a simple budget type of spreadsheet and the 1st 1 I want to show you something is called In order Some. Now, today we're going to cover a lot of different forms and functions. But 1st 1 sure the order some. Then I'll show you what we call relative and absolute functions. Then I'm gonna talk about how to do the very popular V look up and the h Look up. They wanted a cover function that it called some. If count if and average F, then I'll show you the popular if function along with different variations. How did the f with the or in the end? And then later on, we'll cover some date functions that today and network days. So notice how we have a simple budget spreadsheet going on a typical spreadsheet. Here's one that you're going to use all the time it's called, although some obviously I want to sum up those numbers in columns. See, by the way, I'm using Excel 2016. But what I'm showing you should working except 2013 Excel 2010 as well. Now, sometimes I'll make my mouth do that so that you can find it. Women mouses. So I just caught on Cell C 11. Obviously, I want to get a total for that column. So here's a classic one that's called Order Some. I pick in the home menu of Top Home and will come way over here and pick in the word Order some right there. Now when I pick on orders, some usually it will capture the proper range for you. In this case, it did. It says from See some see for three C 10. Whenever you see that colon, that means it's It's a range of cells that when you read, that s C four through C 10 and it is the right range. The older some doesn't always give you the top arrange, but many times it does. I'm gonna hit the enter key, and now I'll have the sum for that column. So watch again. I'm gonna delete that cell so I could do it again. You click on this cell where you want your total toe a PR usually at the bottom of the column or on the right side of a row and on the home and you'll come over here and I'll pick on orders, son. And that is how Yushin does gets the proper range for you. I'm gonna hit the NRK and now has the mathematical. Some of those call of that calm. But I click in the cell nose up here on the formula bar. Has the formula is is equal. Some open Prentice's C four through C 10 close parentheses. If I need to change the formal, I can change it up there in the form of the bar. Now we'll do some other older sums as we go throw. Now I'm gonna come over here into the February column. I want to start to show you something is called a relative. Ah, relative reference. Now let's say January was the real numbers. But let's say February, I want a 10% increase of the number of that's in January. So I'm gonna type in all form this, of course. So the equal sign You probably know that so I'll tape it equals and I'll click on this. So over here, see, for times 1.1 mathematically, if you multiply any number by 1.1 is going to give you a 10% increase. So where is this C four ties 1.1. That means used a number that's in cell C for multiplied by 1.1. When I hit the enter key. Is that $13,750? Now, if I change this number over here and sell, see for a month have been 14,000. When I hit the enter key to numbers are gonna change. This number over here is going to change and then sell d for it's also gonna change because both of those cells have a formula that points back to Cell C for. So when I hit the enter key, no. So this number changed and this number also changed. So think about that number 15,400 10% of the 14,000 would be 1400. Add that again onto the 14,000. That's why it's his 15 4 specially a 10% increase. Now, let me show you something that's called a relative reference. This is gonna be a big time saver for you. I want to have that same formula globally down that column So I'm gonna get the Black Cross right here, and we're gonna drag it down to the rest of the column That's called a speed Phil. It's also called on Auto Fell, but let's see what happened to the formal. It's more important. The 1st 1 says C four times 1.1. That's when I typed in the 2nd 1 says C five times 1.1. The next one says C six times 1.1 knows how it's changing the formula relative to the new cell that's called a relative formula that's going to see a lot of time. I only had the type in the form of one time. Then I was able to get the Black Cross and drag it down all the way to the rest of the calm . When you see most people's larger spreadsheets, it's hardly ever the case with the type of each form of one by one whenever they can that they use that that it type in the top one and copy it down with the speed fell and then each. Each cell has the formula relative to that cell now to C five. This one says he sex, so that's called a relative formula. Now we're gonna take that same concept and put that all the way across the rest of the columns. So this time I want a hot it their cells. I'll get the Black Cross in the corner. I'm gonna drag all the way over to the dry column. Now let's see what happened to the formulas. This one says. C four times 1.1 This one says D for ties 1.1 this and says E four times 1.1. That's how it's always the cell to the left of the current cell. It's called a relative formula. The changes that sell relative to that new cell. It will save you a lot of time. A. So you can say now, once you get that concept, you're gonna use it over and over again. For example, over here, when I first started a couple minutes ago, I did the order some. Now I could click on this cell and do another order some and then click on this out and do another older some. But here's the quicker way. We'll just do that. Speed fell. No, that's right. Now, the formula says some C 43 c 10. I'm gonna get the Black Cross. We'll drag it over. Now when we see the pound size and our Excel spreadsheet, that means we have to make that column beggar. Now I could do on one at a time. So, for example, if I go over here, no swimming now says in between Letter E and letter F, and I get that Black Cross that could double click there. And don't make that one column bigger. Here's how I condemn all. One time I'm gonna start a letter f. How about all the way over to let her I now all of those columns air selected now move him out between those two columns and get the Black Cross DoubleClick. And now, because it had a mall selected, they all changed. But let's see what happened to the formula. Remember how the first and said some C four through C 10? Then I get the Black Cross and drag that over the next one says some D four through d 10. The next one says some E four through E 10 and so on. So when you start to use that relative reference, it really save a lot of time because then you could just copy the former over and it changes each one accordingly. Now I'm going to insert a column. A column? A Because I'll show you a couple more examples of the auto, some type of the world average. Good. Now let's get the total for that row. So knows how I months LJ for and once again are picking the word orders. Some appear now knows how. This time it gets the proper range. It doesn't always gets the proper range, but many times it does. Now, this is some see for through I for which in this case is correct. I'm gonna hit the enter key. So now we have the sum for that row. Now, you can probably guess what I'm gonna dio now. Use a relative formula again by doing the order some. I mean, the owner felt so get the black cross and drag it down. Fact that dragon all the way then to row 11. Now here I have a row total for each row and I even have a grand total. Now this one is zero because if you think about it says some see 10 through J 10. There's nothing in those cells, so it came to be zero. Now, to me, that zero is extremely so I'm gonna delete it. I'm gonna hit my delete key on my keyboard. Good. Now, remember, if you're watching, we will have questions and answers at the end of the session. So, uh, you know, keep you can type in your questions now, and we'll get to those after the whole session. So I did the altars, some By picking on that cell. I'm picking the auto. Some appear in this case against a proper range. Then to get to the rest of them. I clicked in that first, although some I got the Black Cross tried it down. Now, at that point, I got rid of the zero by deleting that cell. But then basically, we have a road total for each row, and I even have a grand total now as well. Then we should another way to use the order. Some if I just put them. Hey, I want the average for sales now. I'm gonna come over here. Now you're gonna click in the pull down for the order. Some you say Now you can do some other calculations, some average count, minimum and maximum. So picking on average now, this time I want you to notice that it did not pick the proper range. It wants to average from C for two j four. But if I included J four in the average than the average will not be correct. So here's what you dio you're gonna highlight the reuse that you want to do. So I'm gonna highlight from a C for over toe I for you say, Then I'll hit the enter key. And now I have the average for that round watch again. I'm gonna do eat that self weak and redo it. Clicking the pull downwards is also some right there cooking the average. That s how it did not pick the proper range that time. So you're gonna highlight the range that you want to go. I'm gonna highlight from C for toe I for Then I'll hit the enter key. And that's the cell that I want to hit the average from. Now you know what I'm gonna do from here? I'm gonna copy that down, so I'll get the Black cross in that cell. Try get down. And now we have the average for the rest of the Rosa's. Well, excellent. Now, before I continue, I want to make sure I format all those numbers I wouldn't turned in like this, because if you notice some of the numbers have to does some places, this really even has three. I think I saw one with three somewhere, but we want to make that look a little bit better, so I'm gonna have it all of these numbers. It's how do all the numbers? Yeah, if you look at these numbers than here, they have three Dustin places, so I have it all the numbers. Now, you probably know about formatting. I'm gonna go ahead and click in this pulled downwards his number on the home, on the home toolbar. And I said, we're gonna go with currency format. See, now it looks a whole lot better than to have dollar signs, commas to Dustin places. So you always hot in a bunch of numbers, then here you see some number formatting options under the under the home menu. Now, if you be using yourself for a long time ago, I have Then you know, another way to format yourselves is called format cells, for example, right click in those cells and then up again word format cells. This is the way I learned a long time ago, and I still use that screen to Thursday, you know? So here you have your number formats. But in this, when you go, the further you treat the deaths in places and the dollar symbol on, I mean the currency symbol. And then you would even pick a format for your negative numbers, so I'll click. OK, but either way is fine. You can highlight your numbers and find some formatting over here under the home menu, or, if you right click on their cells, then you go back to format cells. Good. Now, when I was ableto click on this cell and get the Black Cross and drag it down, that's called it relative reference. Let me share. This is has averaged C for toe I, for this is his average C five, the I five. That's just his See 62 i six. That's called a relative reference, but sometimes you don't want that to happen. Sometimes we want to use what we call the absolute references for those of you that have used Excel before. This is where you have the dollar sign in your formula. Let's see, in the example I like to do the percent of total. The percent of total is going to be taken by take by calculating the total for that row and dividing it by the grand total. Now, first, I'm gonna do this form of the incorrect way I'll show you why is incorrect, and then we'll correct it. But this is the trap that many people for in tow. I'm gonna take the equal sign. I'm gonna click in the total for that role, which is J for taping the slash for division. And I'll cook on this out over here with his Jake, you love, which is the grand total. So the formula says equals J four divided by J 11. I'm gonna hit the enter key now. Previously, I had format that column as a percent, so I'm just gonna make sure that our percent so I'm just gonna have it. That column and we'll make it percentage format. Okay, good. So watch what's gonna happen now. You would think that you just be able to copy that down like I've been showing you going to get the Black Cross, copy it down and knows how. If they say division by zero. So let's see what happened. This is I'm trying to show you this one says J four divided by J 11. The next one down says J five, divided by J 12. You see, it's still a relative formula. Well, there's nothing in J 12. That's why it's his division by zero. But I don't want Divide by J 12 I went away by J 11 The next one downs getting even further away. Now this is J six divided by J 13. Except there's nothing in J 13. That's why is this division by zero? But I don't want the biology 13 I want to buy by J 11. So some town somehow we have to tell it that when we happened, that formula down not to change the J 11 part. So what I just showed you was the incorrect way to do this formula. But this is the way this is the trap that most people for into. So let's go in and fix that now. I can also double click on that cell to see the formula. I'm gonna double click on it. Now I'm gonna put a dollar sign before the J and a dollar sign before the 11. Let's take a closer look at that, so you could really see that little bit better. This is Jay four. Divided by dollar Sign J Dollar Sign 11. When we see the dollar signs in the formula, we're not talking about money. It makes it what we call an absolute reference. Which means what I copied down the J 11. Part of that fund will still say J 11 but you have to see it understanding. Talking about I'm gonna hit the enter key that now the dollar signs are not gonna make a difference until I re copy it down. Somebody click in that cell will get the Black Cross. Drag it down Now we have actual numbers. Let's see what happens. This is says J four, divided by J 11. The next one down says J five, but it's still dividing it by J 11. Because of the dollar signs, it's called an absolute reference connection down, says J six. But it's still dividing. About 11. The first part, that formula is relative. Mother is the next one will say J seven, Maxwell said Jay, but notice how they're all dividing it by j 11 because it is dollar signs. That's called an absolute reference. So those come up in your larger spreadsheets that you could see the difference that it made . Let's see another example of that. I had to see this a couple times before. Everybody got it over here. I'm gonna type in the word tax free. Now I'm recording this from Philadelphia, Pennsylvania, in United States, and here are tax Rate is actually in Philadelphia is actually 8%. That's pretty high. Okay, 778% there. Now here I'm gonna type in the word sales tax, and then you show another example of the absolute reference. I'll taping the equal sign all four men. There's always started the equal sign. I'll click in the total for that row with the multiplication, which is the asterisk, and I'll cook in the 8% which is M two now. I know I want to make that into an absolute reference. You can either type in the dollar signs or a quicker way is a keyboard shrinker. I'm gonna hit the F four function Key F four and those head up with the dollar signs in their foreman. So you can either type in the dollar signs or when you're on that part of the former that you want to make an absolutely reference. You can use the F four function kid. That is the dollar signs in there for me when I hit the Enter key. Now we're gonna copy that down, get the black Cross and drag it down. Let's see what happened. This one, says J four, as multiplying about em to this one's taken J five, but it's still multiplying by em. Two. Because of the dollar signs, Dixon says J sex multiplied by M two. So the first part that former is relative. It is changing for each cell. I wasn't sure, I'm sure, since J seven, the second part of former that is absolute. It doesn't change because of the dollar sense. So hopefully you saw have used a dollar signs in your formula, as it's called in absolute reference. Where is the norm? Ones are called relative. If I changed at 8% let's say I make it. 6% watch those numbers and column, and they're all going to change because they were all pointing to that same cell. Now let's put it back to you. Percent watch the role change again as physical relative references. Now let me Ah, that zoom out good. The next thing I want to show you something that's called a V. Look up. Very popular function. I'm gonna go to the next sheet down here. That's just called orders. Now, this might be a list of all the orders that your company has notice. Here I have a customer, I d but notice how it doesn't show the company need. If I come down here to the sheet that's called customers, then here we have the customer idea, the company name and all the information about the customer. So somehow I have to pull the company name for the specific customer I d into the sheet. That's called orders. Well, that's what we're going to use a V look up. The V look up has a few different purposes, but probably the main purpose of the V look up is to look up information from one sheet into another shoot Let's see if we can make it work. Now I'm gonna go ahead and uncertain. New column A column C. Right click on that Air Sea and I'll pick on insert. Good. So in this case, I'll type in company name that will just be the column heading now. I'm gonna start t form with the equal sign, of course. Now follow my mouth's. And when I'm picking this F X right there, the FX is called Insert Function, and it's a very powerful window. It's everybody share all of your built in functions that come with Excel. Now there's over 300 of them every time they add a new version, Excel there always had a new functions, so you could either search for them up here, or you can click on this, pull down, and then you can have a bunch of functions. As you can say, there's different categories. Now we're gonna see some of those categories today. Now I happen to know that the V look up is under the look up and reference category. If you didn't know that, you would type in the word V, look up up there and search for it. But I'm gonna pick in the word look up and reference. Now I'm gonna scroll down in this column and you see the bottom one is called the Look up. Now it's going to give you a brief description and after our session here, you can always go back and pick on help on this function. But I want to show how the user right now, of course, So picking the road, V Look up and I'll click on OK, this screen is called function arguments or it's also called the function parameters. So what do I need to make this function work? This needs four piece of information or four function arguments. The look of value is what you're trying to look up. I'm trying to look up that customer I d. So I'm gonna pick on Selby too, right? That's what I'm trying to look up. So look up value. Now the table array usually is on a different sheet. It doesn't have to be on a different sheet, but a lot of times it iss now the better name from the look up. Skimming about name for the table, Ray is the look up table. So I'm gonna click on that area. When they come over here, I'll go to the customer sheet semen else down here at the bottom of customers. Click there and we're gonna highlight now. Usually, I would recommend that you highlight the entire column and work out a whole lot better. I'm gonna have it from column A All the way over the column F and that'll be our table are right or the look up table. Now the look up table could be many, many calms across and many, many rivers down. And certainly you can be on a different sheet like it is now, where sometimes it's on the same, she does the original data. The most important part about the look up table is the first column. That's what has to match what you're actually trying to look up. We were trying to find a customer I D has had. The first column of the table is the customer idea, so that's very important. It doesn't have to be column A necessarily. It just worked that way. This time it just has to be the first column of the table that you select otherwise didn't go many, many columns across So if you're looking at for part numbers than the first column of a table has to be part numbers. If you're looking for P O numbers in the first column of the table has to be peer numbers, and then from there you can get many columns across. Now, let me show you how you calculate the next part of the V. Look up, which is called the column Index number if you count from the left side of the table. So in the table, this is column one, 2345 and six. I want the company need the company. Name is the second column one to. So for that reason, I'm gonna type of the number two for the column index number. That means when I find inappropriate row based in the customer, I d. I want the second column of information, which is maybe the company name. Now I'm gonna come down here and pick on the range. Look up the range, Look up it. Let's see what it says. The range look up is a logical value. That means this kind of said there were true or the word false to find the closest match in the first column. Then you put the word true there to find an exact match. You type in the word false, probably like eight out of 10 times, if not more. You use the word false in the V. Look up now in a little while, show you wonder has two were true. There may times you used the word false. If you're looking for a specific part number, you want that example. Number right, so usually will type in Foster. Let's see what we have. The look of value is Selby to which is right over here. The customer idea, the table array or the look up table is on the other sheet. This is customer sheet column A through column F column. Index number says to that means here I'm back on the table, right? That would be the second column information, which we the company name. If I want the contact name, then the kind of column index number would be three, because that would be the thorough column over. But here I wanted the company name. That's the second column on the table. So that's why I have the number two right now. And then the word false means I want to do an exact match. I'm gonna go ahead and click. OK, now I'm gonna make column, see Bigger. So it looks like if looked up, that customer I d on the other sheet and returned the proper company name. Take a look at the formula up here, it says equals V. Look up open parentheses, B two comma customers. Exclamation point A through F That's the look up table, Comma two. That's the second column, comma False. That means you want to do an exact match and then close the parentheses. Now, the way I built that, I built that with the F X over there, although after a while you could just kind of type of them. Once you've used it a few times, you could just type it in. But there's nothing wrong with using the FX I still use to this day. Now, let's make sure it found that right one. I wouldn't assume that it's the right one until you check it out. So the customer I d. Is the i N E T. And you can see the company name that's good to the customer sheet now these happen to be in alphabetical order. So if I scroll down, then we'll see. There it is on Road 80 sex V i N E t. And it looks like it found the proper company. Name eso. Let's go back to the other sheet. So that's like a textbook example of when they used to be. Look up. If you want to pull information from another sheet, the vehicle is perfect for that. Now this gun and copy that down. I'm gonna get the Black Cross again right there. Now let me show you a short cut for the Broad Cross. Sometimes you can double click on the Black Cross. If you double click on it, it'll go all the way down. Now The reason that that worked is because I had something to my immediate left ever here or something in the column to the immediate right. If column D was not filled in and column B was not filled in, then the double click of the Black Cross would not have worked. But in this case also day was I clicked in that cell, got the Black Cross and I double collect, and I went all the way down. Now I have the appropriate company name for each different customer idea. Because if you look up now, I cook in this You look up those hell says the word false at the end. That means it has to do in the exact match. Let me show you what that means. I'm gonna come over here and I know I don't have one for five. These So it had been vv vv and knows how it says end A and A means not available or not found. It says that because I was looking for an exact match, it didn't find an exact match. So this is in a well what we're talking about these functions. I know. Some people would say, Well, I don't want that to see n a. I want to say something different. So here's a bonus that will decide the phone for you right now. We could use something that's called if error watch my skin and what does it mean for you again? Good. I'm gonna come up to the formula and we go ready after the equal sign between the eagle side and the letter V and I would have been if ever. Now, the if error is a function within itself, so it needs its own parentheses. So we'll do an open print disease. We're gonna go to the end of the whole thing typing comma. Now, the word not found here has to be in quotes because it's a piece of text In many of these formulas, when you have a piece of text, you have to put the text in quotes. So they're open quotes, not found, close quotes, close parentheses. So let's take a look at the whole thing. This is equals. If our open parentheses, then we had the whole the look of that was there before. After the vehicle, I have comma open quotes, not found close quotes, close parentheses. I'm gonna hit the enter key notice. Now it says not found. All right, So the if our can work with any formula what it really means, as if the V look up gives me an error. Then put the word not found out. If the V look up does not give me an hour, then just give you the normal value of the V. Look up. Now. We're gonna copy that all the way down to get the black cross and double click there. So now if I came over here and I typed in, um, C c c Agassi's has not found in this case, I'm gonna indeed that. So that one will be back. Here's a deal. And then for this one type in the i N e t. And now it finds that one again. Now, that's how this view look up, says the word false at the end and then added, If our in there as well. But sometimes the vehicle said the word true. Let me give you one of those examples. I'm gonna go back to the sheet that's called V. Look up. The sheet itself is called V. Look up. Okay, now, this time, the look up table is within the same sheet muscles human again. All right, now, this time I'm trying to find the appropriate. I'm trying to find the appropriate tax rate for the income level. So in this case, I'll type in 40,000 for the income level. And that's how we get 31%. Let me go back there again. Okay, so now we have 31%. Now let's take a look at the V. Look up, it says B two. That's the, um, income level. Now the look up table is what I have in blue over here. D two through F seven d 23 F seven is the look up table. The number three means when I find the appropriate row, I wouldn't want to get the third column. 123 That's why it's his 31% now. Knows how the fourth parameter of the V look up is not fair. Usually you would expected to send it were true or false. But notice how this time the fourth parameter is not there. The fourth grammar is actually optional. Let me show you why it's optional. I'm gonna pick of the FX rate there. And now we could see the function arguments again. No, the ones that are in dark plaque like that those are required. And the ones there in light black like this one are optional. So that actually has a purpose. When you're looking at this screen, the ones that are in dark black are required, and the ones that in light black are optional. So let's see what really shook up cess range Look up is a logical value to find the closest match in the first column you would put true there. Or if you omit it, it assumes that it's true. All right, So when the range look up says that were true or if you leave the range like a blank, then it assumes that it's true. Which means is going to find the closest match we find an exact natural type and false. Okay, so let's see what happened. If that you look up said the word false at the end, then this would say n a right now. But we just discovered that when the fourth parameter is not dead, assumes it is true. Which means we're gonna find where 40,000 with this 40,000 for within these. It's between the 27 3 and 58 1 58 5 So everything between 30 27 3 and 55 is gonna give us this tax rate of 31%. So when the fourth parameter is not there, where if the fourth primer says that were true, that means you're gonna mash the range or when the veto cups is the word false don't have that. It will be an exact match. For example, this type in 60,000 there now says, 36%. If you think about it, the 60,000 falls between the 58 5 and the 1 31 8 So everything between 55 won 31 8 will give us the third column, which the 36% now, let's say the number that you type in here is bigger than the largest number in the table. Well, just like in the tax table, it doesn't matter how far they're in the highest tax bracket. You're still gonna get the highest tax rate, so I'm going to have been a big number at 40 or 50,000 and it's now that gives the highest tax rate of 45.25%. So I showed you. If you look up over here, let's go back to the other shoot. Should it be, look up. That has the word false at the end. That's when we want to do an exact match, a shooting if error, that could work with any kind of formal. When you have an hour, if you want, have a better message. Then I showed that if you look up that has, um, the true Asti fourth parameter over in this other workbook, as we can say Scooter. All right, now let's see a similar when it's called H. Look up. Let me go to the next shoot. It's called H. Look up. Let me make sure. Well, we'll do this one. We'll make sure we can trip with our slides. The H look up is similar to the V. Look up. Except this time those have the table is running across the screen. It's horizontal. Where is the other one? The table was running down the screen on this when it was vertical. So the V actually means vertical and the H actually means horizontal. So all the peasant had a table is running across the screen. But if I click here No. So the age look up is gonna be very similar to the view. Look, I've accepted the tables kind of on its side. The look of value is B two, which is the salary over here then, um, the the table references. Ah e 13 j three. But I have in ah, in light blue over here. But this time, instead of looking at the first column, it's gonna match the first row of the table. That's the big difference. Then the number three says, When I find the appropriate column, I want to go three rows down, which is gonna be the percent the tax rate. And then once again, even with this one, if I go back to the FX knows how the same thing with the fourth parameter. If it's not fair, it assumes that it's gonna be a true. So it's going to assume that it's gonna match the range when the fourth parameters not fill them just like the V look up. So in this case, the 2155 66 gave us 28% because let's hear that false. That falls between 26 51 27 301 Everything But between those two numbers will get 28%. As you can say so, the H look up is very similar to the the look up, except the table is now going horizontal incident with the video cup, the table be going vertical, I'd probably say maybe seven out of 10 lookups that you'll see will probably be a V look up , but sometimes you run across one that's called H. Look up. All right. Now, let's go on to a new topic. And that's how I'm gonna go to a new sheet down here that is called some if count if and average f Did you eat this column for it? And I wouldn't really need that. Okay, So now, clearly, I want to get the count of all the breakfast items. If I used the count function, the count function counts everything. I just want to count. The ones that are that are breakfast only. So watch what we'll do. Whatever the equal sign. Once again, I'm gonna pick in this FX every year, and we'll be back to the insert function window, an attempt to account this some searching for it and that I'm picking the word go, and it finds different ones that have to do with account. So I'm picking the word count if and look what it says cast the number of the cells within a range that need a given condition. Of course, you have to. This session you can always go to help window and see more information about that, but I'm going to show how to use it right now. So I picked on count. If I pick up, Okay, so the range here is going to be the full list of data which in this case is column F when I have a column F now the criteria is what you're looking for. And this is where I'll pick in their word breakfast. All right, so I want to go through column F and find all the breakfast items. And that's exactly what count if he's going to do for us, I'm gonna click on. OK, let's see what happened. Now we have the number 16. Take a look at the formula up here. Does it mean for you, it says equals count if open parentheses F colon F, which is where all the data as column F comma I too, which is the word breakfast. And then we got the number 16. So that means if we went through column F, we would see 16. I'd insisted the word breakfast. Now watch what I'll dio. I could quickly get the cancer. The other categories. I'm gonna go get the black cross right now. Ah, double click on the Black Cross and then it goes all the way down so that quickly, I have a count on all of the categories. So when you have a big list of A like this, the account, if can be especially helpful now, I'd like to do a sum of all the breakfast items. So let's try this one. Now. It'll use something that's called some If what happened, the equal sign and I'll go back to the FX again, which is called Insert Function Cut. Now, this time I'll type the word some. I'm gonna cook a go notice. Now there's one that's called some if, and I'll pick up okay. The Summit has three function arguments, but it's going to the 1st 2 of the other one anyway. The range is still column F. So how that column F the criteria is still the word breakfast, just I to now the some range means what number do I want to add up for all the breakfast items? I want out the numbers in columns e so hot A column e. The Ranges column F That's where all the data is. The criteria is I too. That's why I'm looking for and the some ranges column E That's the numbers I want to sum up when I find the breakfast items with a click. OK, now take a look at this formula. This is equals some F open parentheses. F Colin F comma I to comma e calling e close parentheses. Now, I'm gonna copy that Right on down. I'll get the Black Cross DoubleClick in the Black Cross. And now I have this sums for all the other categories. You can see how these would be extremely beneficial on a large list of data. And I have all the council, the categories and all the sums of the categories as well, 2. Excel Nested If Formula: in this video. I want to talk about the nested. If statement, a nested F statement is when there's more than one, if statement within one formula. So first we're going to start off with a simple If and then we'll expand it into a two level in a three level in a four level if using the missed it. If so, there we have some transactions. Let's say this is just a hypothetical situation. Okay, let's say if it says dinner and column F, they're going to get 6% sales tax. I'm using 6% because I'm recording this in the Pennsylvania here in United States and our sales taxes 6%. So I'm just using that as a number. Okay, so let's do a simple, if all forms, of course, start the equal sign. I'll do equals if open parentheses, so we'll say F two equals dinner. Now The word dinner is in quotes here because as it's in text, all right, so any time that we use texting these formulas, usually the test is within quips. Well, taping a comma now, if that's true, will get 0% sales tax, not having another comma, and if that's false will have 6% times e to. So the whole thing says equals if f two equals dinner comma zero comma, 6% times E to close parentheses. So that means if it's dinner, they're going to get zero. Everything else will get 6% now. When I hit the enter key, this one does say zero, because obviously it says dinner right there in column F. Let's go ahead and copied the format it down. So I'm gonna go ahead and get the Black Cross. This is called the Speed. Fill a double clicking the Black Cross and it goes all the way down and you can see obviously the numbers that if there if it's not a dinner item in column F, he gave us 6% of the number that's in column, and we can clearly see that the next thing I like to dio is be able to format column G as currency. So it'll look a whole lot better when we do that. For my thing is not an issue, all right, but if I scroll down, notice how there's another zero because it says dinner in column F. Now let's try something else. Let's say hotels are 10%. So we're gonna change the F to now include logic for hotels and will make that 10%. This would be our first nested if now I'm gonna come up here to the Formula Bar and I'll click right after that comma after the zero Good and I'll try even if open parentheses. F two equals hotel comma, 10 percent times e to comma, 6% times e to now. I need another close parentheses because I have another open over here so they always have to match out. So let's see what it says this time equals. If open parentheses, F two equals dinner comma zero comma If open parentheses. F two equals hotel Kama, 10% times E to comma, 6% times E to close parentheses. Close parentheses. Okay, so let's see what this says in English. It says dinner is still going to get zero. Hotel is going to get 10% and everything else will get 6%. So that's what it says in English. Just try to understand what's going on here. Where is his F two equals dinner? That's the condition part of the first. If zero is the true part of the first. If logically, it's not even going to get to the second if unless the first if it's false. So if it's not dinner, we're going to check to see if it's hotel. So where is his F two equals Hotel? That's the condition part of the second. If the 10% is the true part of the second if and the 6% is the false part of the second if but guess what? The second if is the false part of the first. If it's not even, we get to this second if unless the first of his false. So this is our first example of a nested. If in Excel 2003 you were ableto have eight ifs in one statement, which is starting to be, you know, that would be a lot, but some people needed more if stian that. So they wrote the Microsoft that this a deer Microsoft, Can you please add more ifs? Well, guess what? Since Excel 2007 I think now we can have 64 ifs going across so we can get really, really complicated A so you can see let's go ahead and ah, you know, you can take a look at that formula. I'm gonna copy it down. Now. The ones that should change are these ones to say hotel. So I'm gonna wash those numbers. It's a copy it down. I can imagine the number is going to go up. Gonna get the black Cross double click knows how those numbers did go up, and this number is 10% of that number because it's a hotel item and this number is 10% of that number over there. So as you go down the list here, this number clearly is not 10% of this number. It must be 6%. We have some that are zero clearly and then for the hotels those air 10% of that member because of the F statement. Now, like I said, you could have as many if said you wanted to. I'm just trying to show you how to build the if statement. Now you can have. And if anywhere within that formula, it could be maybe the first set of cried, the criteria there could be another. If for the true part, I mean, that could be different. ifs for all those different sections of the former. I'm just trying to show you how to formulate this. Let's go to 1/3 level of If here, let's say entertainment is going to be 20% eso just that is an example. Here, here's what will dio now that's how I changed the top one so I could I could just copy it down from there. What I do is I'm gonna go right after that common right before the 6% on tape in another. If Biff open parentheses, F two equals and her team mint comma 20 percent times e to comma 6% times e to then I need another close parentheses. Because I have another open print issues right there. They always have to match out. So everything was fine up until this e to then I have comma. If open parentheses, F two equals entertainment comma 20% times E to comma, 6% times e to then we have three closed parentheses. There's an open. There's an open and there's an open. That's why I need three closes now. This whole thing says the following. If it's dinner, they're going to still get zero. If it's hotel, they're good. 10%. If it's entertainment, they're gonna get 20% and everything else will get 6%. But let's see if we could figure out the logic. The F two equals dinner. That's the true part. I'm sorry. That's a condition. Part of the first F zero is the zero is the true part of the first F the second. If is the false part of this first F the F two Eagles hotel, That's the condition part of the second F. The 10% is the true part of the second. If the third if is the false part of the second If Okay, so where is his F two eagles entertainment? That's the condition part of the third F 20% times e to That's the true part of the third. If and the 6% times E to is the false part of the third F, So I'm gonna hit the enter key, and now let's copy it down. And then these are the two numbers right here for entertainment. That should change. I'm watching those those tumors right now. I'm gonna get the black cross a double clack. You can see how those numbers went up, and then this number isn't 10% of that number. It must be 20%. All right, this number is 20% of that number. All right, So when we look at that formula, um, now that the second if can be anywhere within this statement could be part of the first criteria that could be part of the troop statement in this case that have happened to be part of the false statement. All right, But you could start to see how they might work, and then the third if is the false part of the second if and just to do one more, let's see what we can Dio. Let's say lunch is going to be 5%. Okay, so you see some lunch items over here Once again, I'm gonna go all the way to the end. Now, again, this is just an example. You can put the EFS anywhere. Where would make logical sense? I'm just trying to show that construct the multiple f, so I'm gonna go right there. I'll say if open parentheses, F two equals lunch. Um, comma 5% times e to comma. 6% times e to. And then, in this case, I need another close parentheses at the end. All right, let's take it from the time. So in this case, where is his F two equals dinner? That's the true part. That's the condition part of the first. If zero is the true part of the first F, the second. If is the false part of the first stuff F two equals hotel. That's the condition part of the second. If the 10% is the true part of the second, if the third, if is the false part of the second. If where is his F two equals entertainment? That's the condition part of the third F 20%. Is the condition part of the true part of the third F, The fourth? If is the false part of the third If and we have F two equals lunch, all right, that's the condition. Part of the fourth F 5% is the true part of the fourth F, and 6% is the false part of the fourth. If so, what? All this says in English. It says dinner is zero, hotel gets 10% and the team against 20% lunch gets 5%. Everything else gets 6%. So if you think about it, I have one to three four five conditions. That's why I need four ifs. If I needed seven conditions, I would need six ifs. I'm gonna go ahead to hit the enter key. Now let's copy that down. The ones that we are going to change our these ones for lunch. Those numbers should go down just a little time, You bet, because it's going for 6% to 5%. I'm gonna get the black cross with double click wash the ones for lunch. You can see how the numbers went down, just a little tiny bet, and now you can imagine that's 5% of that number instead of being 10%. So hopefully you got some ideas of how to build a nested f here in Microsoft Excel