 Excellent. I'm starting stuff up here, and then I'm passing presenter over to you. Okay. Well, welcome to class. Again, sorry for the technical difficulties that we experienced this morning, but we are covering today formulas and functions. Formulas are very important in Excel. That's what's going to allow you to add, multiply, divide, subtract, that sort of thing. And one of the things that people need to understand about formulas is the hierarchy of maps. Sometimes people forget that. So let me go over, this is a very basic concept, and that is how things are going to be calculated in Excel. For instance, if I want to say, if I want to put in a formula, and by the way, all formulas start with an equal sign in Excel. So let's say I have some numbers. I've got 100, and 50, and 75. And I would like to add these. To add these, we're using a formula and not using a function. I know a lot of you probably already know some basic functions like equal sum, and we will go into that in a minute. But I'd like to have you understand formulas first because it's very important to understand how to add, subtract, multiply, and divide in Excel, which a function will not do for you. So again, all formulas and functions start with an equal sign. So I'm going to type in an equal, and then let's say I'd like to add the 100. One way of adding it is to just type in 100, of course. And then if I want to add 50, I can type in 50. And then if I want to add 75, I can type in 75. And that gives me the correct answer, right? It's 100 plus 50 plus 75. But there's a problem with doing that, and let's look at that. I'm going to do this again. I'm going to type in an equals. And this time, instead of typing the actual numbers, I'm going to click on the cell that I would like to add. So I'm going to click on B2. Now I could have typed in B2 if I wanted to. But clicking on it will enter it for me. And then I can just put in a plus, okay? And then click on the next cell that I want to add, and type in a plus, and click on the final cell. Notice, I don't know if you can tell on your screen, but the colors of each one of these cells is slightly different. I've got blue, then red, then purple. And that corresponds to the blues and reds and purple that you see in the formula, making it really easy to try and diagnose problems with formulas, okay? Now what a lot of people do is they accidentally type in another plus, and you don't want to do that. You want to just leave it those numbers. So at this point, I'm going to hit Enter. Now they gave me the exact same answer. So why did I put in what are called cell addresses, a cell address of B2, B3, and B4, instead of just typing in 150 and 75? Well, the problem comes if I want to change any of these numbers. Let's say this should have been 55. If I now type in 55 and hit Enter, notice that this formula changed, but this one did not because I used the numbers. And that's why whenever you use formulas or functions, it's best never to use a number. It's best to always use a cell reference, okay? Because what the goal is, is that the data, we'll call this the data. The goal is that the data can change whatever you wanted to, but that the formula doesn't have to. The formula should always be able to stay the same, okay? So we've seen how to add. Now let's say I'm going to do this again. I'm going to delete both of these. I'm going to start out and remember all formulas start with an equal sign. So I'm going to type that in, hit my first one, hit a plus, and then I'm just going to arrow up instead of having to click on it. I'm just arrowing up to the second one, hit a plus, arrow up to the third one, and let's say I accidentally hit another plus. Now I press Enter and look what Excel did for you. It said we found a typo in your formula. That was that extra plus. And would you like me to correct it for you to being this? So if you read this error message that comes up, it'll help you to correct what you got wrong over here. So we'll just say yes, and you see how it automatically corrected it for you, which is a really neat function, okay? All right. So we learned how to type in a formula because sometimes you're going to want to do equal this times this minus that. And if you do that, then you need to be able to use those multipliers and dividers, and you can't use a function for something like that. So notice that I put on my quick access toolbar, the plus, the minus, the multiplication. Notice multiplication is not just an X but an asterisk that's very important to understand. If you look, if you have a numeric pad on your, excuse me, on your keypad, if you look at that numeric keypad, you'll see the division is this forward slash like that. Multiplication is the asterisk, then you have subtraction and addition, which you're used to, okay? So all those things are really good. Now, I'm going to bring up Word for a second, and I'm going to ask you what the answer is to this. If I say equals five plus two times three, what is the answer to that equation? Five plus two times three. Now, unfortunately, I don't get to hear your responses, but what many people would say, they would add five plus two making seven, and then multiply times three which equals 21. Does that make sense? Five plus two equals seven times three equals 21. And yet, the answer that Excel will give you is 11. Why is that? Well, because there's such a thing as the hierarchy of math, and this is explained on page one in the handout that hopefully you have, and if not, Brian will have it for you after the class. The hierarchy of math says multiplication and division always happen before addition and subtraction. So in Excel, what would always happen first would be two times three. Just to make sure people know, the handout is available through the control panel under the handout area. It's called Excel 2016 formulas. It is there. It's downloadable. If you have any trouble downloading it, also please feel free to email me, and I can send you out a copy, but it is there in the control panel. Thank you. So it would do two times three, which is six plus five is 11. And just in case you don't believe me, we'll try it. We'll go back to Excel, and I'm going to type in what was the, I said, well, actually, you know what? I'm just going to copy and paste this. So I'm going to come in here and paste. So five plus two times three is 11. Now let's say you don't want to have to memorize the hierarchy of math. That's fine, but then what you're going to have to do is you're going to have to type in your formula, and you should know whether this is the answer you want. If the answer you had wanted was really 21, then the way to get around the hierarchy of math is to put parentheses around whatever you want to have done first. So if the answer you needed was 21, then you have to tell Excel that you want the five and the two added first, and then you want it multiplied by three, and that's going to get you your 21. Another thing you can do is always put parentheses around things that you want to have happened first, but it's unnecessary. It will always follow the hierarchy of math, which states that multiplication and division will always come before addition and subtraction. Very important concept right on page one. All right, so let's go to a worksheet that has some numbers in it, and I'm going to actually get rid of these. I don't need quite as many numbers as all that. Okay, so, and I'm going to get rid of these totals that are already there, because we're going to create our own. So the way I showed you how to do this before is just type in equals and then click on the first number or type in a plus, that sort of thing. Now if we do that, and let's say we had 100 rows to add, that would take forever. So people would not like Excel very much if that were true. So of course anytime anything seems cumbersome, there's a better way. And the better way in this case is a function. Now on your ribbon, right on this home tab, notice we're on the home tab, we've got an insert function tool right here. This auto, this is a summation symbol, because that's one of the things people do a lot, is to add, summation means to add. So this is one way of seeing what kinds of functions are available. Let's go ahead and click on the down arrow to the right of it. Notice the first thing is to sum. So we can sum, we can average, we can count, we can find the maximum of a range, the minimum of a range, or we can go to more functions and get a ton of other functions. There's about 450 or so functions in Excel. Before I come to click on this, notice the first thing I did was I put my, I clicked on the cell where I want the total to appear. So I clicked in B9, then I came over here, and if I want, I can just click on sum. Now remember I told you that all functions and all formulas start with an equal. So why don't I have to type in an equal? Well, because by clicking on sum, Excel does it for me. Not only does Excel put in equal sum for me, but it also puts in the parentheses, and this is what's called the syntax. This is how all formulas have to be put in. There's always an equal sign first, then there's always the name of the function, which in this case is sum. It could have been average, it could have been max, it could have been min, but there's always an equal, then there's a function name, and then within parentheses, you're going to see the other things that are needed in order for the function to work. So in this case, what was needed for the function to work was the range of numbers that you want to add. So Excel even made a best guess at what that range is. In this case, it's right. It's saying what I think you want to add is b5 through b8. Not only does it put that in here, it also puts the marquee around it and colors it so if you have any doubt as to whether it's right, it gives you a visual right away. So at this point, you can either click on this tool again, or you can just press enter and look at that. This is exactly what you wanted, 3400 equal sum b5 through b8. Now, how do I get this function? We did our first function. How do I get it in all the rest of the cells? Well, you can copy it and paste it if you want, but a lot of you also know that there is a fill handle, this little box in the lower right-hand corner. So I point to that and then drag this over. It fills that function across. You might also know that if I drag this fill handle back on top of itself, it'll delete everything that I just put in. But for now, I'm going to fill this across. Now, the question is, why did this work? And what I'm asking is, notice that the function in this cell, b9, says equal sum b5 through b8. If this says equal sum b5 through b8 and I copied it to this cell, why isn't it still 3400? Isn't b5 through b8 3400? It is, right? So why isn't this 3400? Well, let's look. The only way to answer this question is to actually look to see what's in the cell. And the way to see what's in the cell is to click on it. So by clicking on it, I can look up here and I can say, oh, it doesn't say b5 through b8. It says c5 through c8. And this says d5 through d8. How did that happen? When I just copied and pasted it, how did Excel know to change the column letter as it moved to the right? It moved from b to c to d to e to f, and I didn't do anything to make that happen. All I did was copy and paste. So why did that work? Well, the reason it worked is because Excel uses a concept called relative cell addressing. Relative cell addressing says that as I copy a formula or a function, either one, as I copy to the right, Excel is going to automatically increase the column letter. Because if it didn't, if it left it b for all of these, you could never really copy a formula and have it be correct. Who would ever want to have b5 through b8 here? And b5 through b8 here, nobody would. So it would be pretty useless if it didn't relatively address the cell. Now, relative cell addressing doesn't just work. Let me move this down here. It doesn't just work going this way. It also works coming down. Let's check that out. I'm going to get rid of these. Once again, I'm going to click on my summation tool, or I'll click on the down arrow and click on some. If I'm going to do some, that's the default. So I could just click on, sorry about that. So I could just click where I want the total and click on the summation tool. Notice again Excel guessed correctly that what I want to do is add everything to the left. So now I can just press enter. Now, remember a second ago, I said you could just drag this fill handle. So I could just drag the fill handle down if I wanted to. So what if you needed that to go down 400 rows? That would be quite a pain. So what Excel allows you to do is instead of dragging the fill handle, if you double click the fill handle, Excel is smart enough to know to take whatever you have in this top cell and bring it down as far left as the column to the left of it. That's a pretty cool trick. Again, this was B5 through M5. What do you think Excel changed when it brought it down one row? It didn't change the B or the M because we still want B through M, right? The thing is we don't want row 5 anymore, we want row 6. So let's see if it changed the 5 to a 6. It did. And let's see if it changed the 6 to a 7. It did. So as you bring formulas down, it'll change the row number. As you bring the... Hang on, I'm getting another message. As you bring them across, it changes the column letter. And that's critical not just for when you're copying the formula or function, but what if I moved this whole bit of information? What if I moved it down and across? If this still said B5 or whatever it was, B2 through B5, it would be wrong. But luckily, it changed it from B to C and it changed it to 12 and 15. So in this case, by moving this whole set of data, it kept everything correct because it used relative cell addressing. Okay? So Excel automatically uses relative cell addressing. And it's also explained on page 1 in your handout. Really important concept. Why is it important? Well, we'll see in a second. Let's say we now want to add tax to this total. Okay? So we type in equals and we click on this. And then we're going to multiply. So that is an asterisk. And let's say our tax rate is 10%. So I'll just type in 0.1 and press Enter. And notice it did 10% beautifully and I can drag that across. And it did a great job all the way across, right? But what if our tax rate changes from 10% to 12%? Now all of a sudden, I have to go into every single one of these formulas and change them, right? Because this is wrong in every single one. Remember earlier I told you that we really didn't want to use numbers in formulas if we could help it. We wanted to reference a cell instead. So let's do this. Let's type in 0.1 down here. So notice our tax rate is going to be 0.1. So instead of using the number in our formula, we're going to do an equals and we're still going to multiply that times. Instead of typing the 0.1 in here where it's more difficult to change, we're just going to reference and see if it works. Wow, that looks perfect, doesn't it? 340, isn't that what we wanted? Okay, so now I'm going to drag my cell handle across. Well, once again, this works perfectly, right? So let's see what went wrong. Again, the only way to see what went wrong is to not guess, but to click on this cell and see what happened. If I click on this cell, it says C9, C9, that looks right, times B11. That's odd, there's nothing in B11. Why didn't it just say A11 like it's supposed to? Well, let's go over here. This time, instead of just clicking, I'm going to double click to show you another cool thing that's going to help you solve errors in your formula. I'm going to double click and look at that. It shows me C9 is correct, but now it's C11. I'm going to hit escape to be careful not to ruin this formula, not that it's not kind of already ruined. Come over here, double click, E9 is right, but B11 is not. Why? Well, Excel, remember Excel uses relative cell addressing, so it went from B to C to D, and you were happy about that. It's supposed to and you want it to. The thing is, you don't want that same thing to happen down here, but Excel doesn't know that. Excel is still going to use relative cell addressing so that it's going to move from here to here to here when it references that cell. So this is another really crucial concept in working with formulas, and that is absolute cell addressing. Absolute cell addressing says that the B9, I wanted to go to C9, B9, E9 because I wanted to use this first, then this, then this, right? But this A11, I wanted to stay put. I wanted to be absolute. So when that's the case, you need to go to the part of your formula that you want to have the absolute, which in this case is A11. You want to and you want to add a dollar sign in front of each part of the formula that you want to stay put. Now what some people do is they just put dollar signs around both, hey, I want the column to stay put and I want the row to stay put. But really, because we're dragging to the right, the row is never going to change, right? It's the column that you want to stay put. You want it to stay A. So you would really be just as correct, if not more so, to just put a dollar sign in front of the A because that's the only part that you need to stay put. So let's see what happens. So in our formula, we have B9, which is allowed to change, the A which is not allowed to change, and the 11 which doesn't make any difference because we're not going down any rows. Now we're going to hit Enter, come back up, drag the fill handle. Wow, that looks a lot better. Let's check it out. I'm going to go ahead and hit Enter to go from the first cell to the second and notice that it's still A11, still A11, still A11. And yet it went from B9 to C9 to E9, and on and on to F9, G9. So it did exactly what we wanted. Now because people use the absolute value quite a bit, Excel has given you a function key to put those dollar signs in for you. It's the F4 function key. That doesn't mean you hit the letter F and then hit the number 4. It means along the top of your keyboard, there's F1 through F12. You're going to press the F4. Well, first you're going to make sure you're anywhere in this A11 range. So it's the beginning of the A after the 11. In between, it doesn't matter where. You just have to be somewhere closer to it than to the B9, and then you're going to press F4. Notice the first thing Excel does is put dollar signs around both saying, hey, I'm going to cause both to stay put. If that's not what you want, you can press F4 again, and it cycles through the different options. So first it was both. Now it's just the rows it's going to stay put. Now it's just the column that's going to stay put. Now nothing's going to stay put. So you can either type dollar signs in yourself, or use the F4 function key, which it says in your handout, until you have the right absolute part of your equation. And then drag the bill handle across. Okay? So let's show you another time where you might need to use that absolute. Let's do a percent of total. To figure out what percent this is, the 78,000 is of the 304,000, the way you find a percent of total is you do an equals. You take the number, and you divide by the total. Press Enter, worked beautifully. So 78,000 is about 25% of the total. Now we're going to drag the bill handle down, or I don't even have to drag. I can just double click, remember. Now for some reason I must have had something in here. Look what it did, though. It gave me a divide by zero error. Why? Well, if I double click on this first one, notice it's taking this divided by that, exactly what I wanted. But now if I double click down here, look what happened. Relative cell addressing caused not only this part of the formula to move down, but this one. So now all of a sudden it's dividing by nothing. Again, if I do it down here, double click, I've got that same problem. So if I want to fix this problem, I need to, once again, I need to go to where the incorrect formula is. I need to go to the part of my formula that's got to stay put, which is that, the N5 or the N9. Well, does the N5 need to stay put? No, because it needs to go to N6, N7, and N8. It's the N9 that needs to stay put. So I'm going to click anywhere in there. Now, is it the N part or the N9 part that needs to stay put? Beginning Excel users often just hit F4 and say, hey, I'm just going to make everything stay put and call it good. And that's fine, but it would be better if you just made the part of your formula stay put that you need to. And in this case, because we're driving down, which means the row number is going to change, that's the only part we need to have stay put. So if we want to, we could hit F4 one more time. So now we're saying only the row needs to stay put. I'm going to press Enter by clicking this checkbox right here. So that way I stay in the cell. I'm going to drag this down and Perfection. And we can make sure just by double clicking, we see that now it's going down one for the N6 part, but stay put because I said it has to stay in row nine. Got the dollar sign. Any questions on these really important concepts with formulas? Any question on the hierarchy of math or relative versus absolute cell addressing? Okay, I'm assuming that's enough. So I'm going to go on. Now, I'm going to take this away again and I'm going to come up here. Remember when I put in this first function, I clicked here. I came up here and I clicked on the summation symbol and it automatically put it in correctly for me, right? Well, let's see what happens if I don't have amounts in each and every one of these cells. I'm going to click and you're going to see that if you have a blank cell, Excel could get this wrong. You cannot count on Excel getting it right unless all of them are filled in but you still want to always check. So what happens, so basically what happens is Excel will go backward or go up to the first blank cell it finds or non-numeric cell it finds and stop right there. So how would you correct for this? Lots of different ways of doing that. One is, do you see there's this little box in the upper right-hand corner and upper left-hand corner? I can come up here and I can just drag this box up a couple and notice now it's corrected the formula. That would be a way of doing it, okay? Another way of doing it would be to drag across all four cells yourself. Just drag it and that works beautifully as well. Or you can always come into the formula bar and change this was a 8 and 9 and change this, excuse me, it was a, it wasn't that, it was just the last two cells. It was a 7 and 8 and I could highlight the 7 and make it a 5. So three different ways of correcting it, okay? So what if you don't want to? What if you say, you know what, I just want Excel to do it right? Then instead of just clicking in this cell, if you select all of the cells that you'd like to add plus the cell where you'd like the formula to go and then click on the autosum, it will always do it correctly. Because notice it does B5 through B8 even though you have a hole in your data even though you have a blank. By selecting the cells first and an extra one, you have told Excel, I don't care what you think, I want you to add all of these, okay? All right, now we learned to add just one column and drag the fill handle over. But what you could do is you could, let's get rid of all this and all this, what you could do instead is you could highlight all of these cells and an extra row and click on the autosum tool and notice it can do them all at once. Pretty cool, huh? What if, I'm going to undo that, what if you not only want to add up all of these columns but also all of the rows? Well, I'm just going to shift click in that bottom right cell so that I've included not just an extra row but an extra column so my totals can go in both places at the same time and now hit autosum. And it just added them in both places at the same time. In Excel everything can be done so quickly that if ever anything seems cumbersome you're not doing it the right way. Let's show you, I'm going all the way back now to this. So let's say I've got three different ice cream parlors one's in Belby, one's in Sammamish, one's in Issaquah and now I need a grand total of all of them. Okay? What a lot of people do to get the grand total which is completely correct is they'll do equals click on the first one plus click on the second one plus click on the third one and press enter. Works beautifully. So all we did is equals and then we put the three cells in that we wanted. Okay? Another way of doing it is you could do an equals sum now I'm typing it in this time. Notice that when you start typing it shows you a list of different operators that start with sum and if I want I can just double click on it and notice it automatically puts in the less parentheses for me. Now when I have an equals sum instead of having to hit the plus sign I can just click and because this is not next to this one I'm going to hold my control key down. Your control key allows you to select things that aren't next to each other and this is not next to it so I want to hold my control key down and click on that. Notice Excel is nice enough to put the comma in for me. Whenever you're doing a sum of different ranges before we did a sum of a continuous range but when it's different ranges you have to separate them by commas as it's showing you here. Number one comma, number two comma, okay? But I'm not even having to put them in I'm just having to hit my control key even faster because I don't need to hit the plus and then press enter. Notice Excel is nice enough to also put in that ending parentheses for me. So once again all I did was equals sum and I can just type in probably SU before I double click on the sum and then clicking, control clicking, control clicking, and pressing enter. Still though, what happens is this is Starbucks and I only have about, I don't know how many Starbucks there are nationwide. Let's say there are 90,000. Can you imagine clicking on 90,000 rows? So there is a faster way. All you have to do is highlight all the data once again plus one extra row where you want the grand total to appear. So I clicked up here. I held my shift key down and clicked down here because shift highlights everything in between. If I clicked up here and clicked control remember control allows you to select things that aren't next to each other, right? Shift allows you to select everything in between. So I've highlighted all the data and an extra row. Now I'm just going to press my AutoSum tool and see if it works. Let's see. And so just clicking on this I'm going to double click to see what it did. Look at that. It added equals sum B23, B16, B9. It took me two seconds to do something that if I didn't know about this trick it could take me hours to click on 90,000 rows. And the chance that I got things wrong would be probably pretty good. So in Excel it's so worth learning everything you can learn about it because the more you learn about it the more hours you're going to have a free time. Just amazing the amount of free time you will have or maybe not free time but you'll be 100%, 200%, 3%, 400%, 500% more productive because Excel will do the work for you and it will be more accurate. All right. So any questions on basic formulas and functions? So far we've done some basic formulas and functions. Now I'm going to do, so far we've only learned the sum equals sum, right? What if we want to do an average? And we don't know how. Well once again we're going to click in the cell where we want that average to appear. And I think we saw it when I clicked on the down arrow. It was the second one. So I'm going to click on average. And notice it looks a lot like the equal sum does. In fact it looks identical except the function name is average. The problem is that it went one too far down, right? It saw numbers above it and it grabbed them all. So in this case there is, again, there's a little box in the lower right-hand corner although I'm finding it very hard to get to the point of being able to grab it. Yeah, I'm not really able to grab it. So what I'm going to do is I'm just going to drag across the four cells that I really want to average and press enter. So it took all of these numbers and averaged them and then I can drag across, okay? Let's say I'd like to find the maximum. Once again I can come over here and maximum is shortened to max. Once again I don't want to be quite so far down. Drag across just the cells I want to have and press enter. Let's see if it's right. Is 1000 the biggest number? Yes it is. Say I want a minimum. Once again, come up here, go to min, which is shortening for minimum. Drag across just the ones I want and hit enter. And now I can drag them all across by highlighting them all first and dragging them across is going to bring those formulas across to every single one of them. Excuse me, functions across to every single one of them. Any questions on basic functions? Okay. All right, let's say, let me see if I can. Okay, let's go down here. And let's say what I really want to do is count. Instead of adding, I would like to count. For instance, I would like to know how many names there are in this first column. Or a lot of times people do a charity like people want to contribute to, what's a well-known charity? Let's say we're contributing to Seattle Children's Hospital. Okay. And we have people's names here. Sandy, Ken, Sue. Okay. And Sandy contributed 500. Ken contributed 1,000. Sue contributed 2,000. And let's say Andy hasn't come back yet from vacation. He has contributed nothing. All right. So if I did a sum of these, it would give me the 1,000 plus the 500 plus the 2,000. It would give me 3,500, wouldn't it? But I don't really want to know. I know I'm going to put in one more person. Let's say Mary, and she's going to do 50. All right. So I could add these if I wanted to, right? I could just go like that and hit summation. And it gave me the total. But maybe I don't want to know the total. What I want to know is how many people contributed. So I want to know that 1, 2, 3, 4 people contributed because Andy did it. Andy is a zero. Okay? So instead of sum, which adds, there's another function called count. So by start typing in count, I'm going to then double click on that. And I'm just going to select the cells that I want to add. Not to add, but to count. Press Enter. And notice it says 4 people have contributed. If Andy now wants to contribute, it's going to say 5 people have contributed. If these people don't contribute, it's just 3 people. So count is another very useful function for counting. But it counts the cells that have values. Okay? Actual numeric values. So what if instead of counting values, what if you wanted to count alphabetic? Like how many people there are here? Okay? These aren't values. These are alpha, right? Now when I type in count, you may have seen a count A. Count A will count alphabetic things. So double click on count A, select just the name, hit Enter, and it tells me there are 5 people here because I used the count A function. Okay? So count and count A are very useful functions as well as sum, min, max, et cetera. Okay. So I'm looking at my handout now, and we've done page 1, we've done page 2 which talks about absolute cell reference, we've done page 3 which talks about autosum and summing rows and columns. Now you may wonder how if you didn't know about the count function. On page 7, excuse me, on page 6 in my handout, it talks about different ways of entering functions because you may not know the name of a function, right? One way is if you click in the cell where you want the function to appear, notice there's a little function tool right there, an insert function tool. I can click on that, and then you can do a few things. You can look down here to see if a function like count or count A or sum or whatever, you can see if it's already being displayed, and if so, you can select it. Okay. These are recently used functions, the ones that are displayed here. Excuse me. The way you know that is because it's telling you here. You can also click on the down arrow and say, hey, I want to see every single function Excel has to offer. So you click on all, and you just go A to Z, and notice as I scroll down, it gives you a little description of what each of these things do. Okay. So I could type in count. Notice as I type, it comes down to count, and if I scroll down, I see count A, count blanks if I want, count if, only counted if a certain criteria is true. Okay. So one way is to select all and see everything. But what if you know a little bit more about what you're looking for. What if you know that what you want to do is you want to make some sort of a logical question, like if somebody has this issue, then I want to do that. I want to count them, or I want whatever. So if you want to go to testing things, that would be a logical type of thing. And notice that there is an if statement. Okay. So if you have some clue of what you're looking for, you can select a category, and that just gives you a subset of all. But let's say you have no idea of all this looks Greek to you, and you just say, hey, I just want to know how to calculate a payment for a house because I'm about to buy a house. So if you type up here, calculate payment, it helps to spell right, calculate payment, and hit go. Notice that it starts giving you all sorts of different functions. Now you may not know that PMT is the payment function, but as you scroll through these different things, you're going to see calculate the payment for a loan based on constant payment. Now even at this point, maybe you don't know this is telling you what it needs to calculate. So it says, hey, for the payment, I need to know what is going to be the interest rate, how many periods are you going to pay over? So are you going to pay over 30 years or 36 months or what is the present value and the future value, et cetera, et cetera? And what if you look at this and you say, I haven't a clue what it's trying to tell me here, which I have to admit for a lot of functions, that's how I am. I haven't a clue what they're trying to tell me. Well, the cool thing is you don't have to have a clue. Notice in the bottom left-hand corner, it says help on this function. So you can click on help, and it is such great help. I cannot praise this help enough. So first of all, it gives you a definition. All right, let me try and make this a little bigger so you can see it better. Still sort of making it hard to understand here, potentially. So look at this. So again, it says rate. Notice that some of these have square brackets around it. Square brackets always mean that they're optional. So you have to have these first three, but you don't have to have the second two. Then, let's say you have no idea what NPR means. Well, it says the total number of payments for the loan. So that's helpful, right? It gives you a better idea of those different parts of the syntax that you need. Let's keep going. Okay? It gives you some more information here, gives you some remarks that are very important. You should read them. But, and gives you tips, which also all of this is very important stuff, but this is one of the things I really love. They say a picture is worth a thousand words, and I so agree that this, if you take this, you can actually copy and paste this into an Excel spreadsheet and then see exactly how everything works. Okay? So if you took this, starting in data, and coming down to the end of the example, oops, that's too far, right? Because I'm starting to get the next one. That's true, let's see. And you'll interest rate. Yes, starting to get the next one. Whoops, come on back. There we go. All I'm doing is shift clicking where I want to end, clicking on copy. Now it does tell you that you want to always start in cell A1 when you copy and paste. I'm going to go into a new spreadsheet and paste. Now the only negative is that, so notice I'm in A1, that sometimes you have to adjust the column width and row height, although look, it did a brilliant job of adjusting it for me. But let me go down here and see, it's showing what the formula is here. Now I'm not sure why they have the results here, but it doesn't have the actual formula in here, but it's saying here equals payment A2 divided by 12. What is A2? Well A2 is the interest rate. So it's saying that in order for you to get a monthly interest rate, since you're working with number of months, whatever your interest rate has to match the number of the, whatever unit you're using down here. So if you're using months down here, you need to use months up here. So this is really important and it was in the tip that we didn't show you, but A12 divided by 12 takes this 8% and makes it a monthly rate. So that's what you need first. That was the rate, remember? Then the next thing it asked for is how many, the end per, the number of periods. Ten was the number of periods, so that's coming from A3. And then last, it needed to know the amount of the loan, which is coming from A4. So even if none of the description was clear to you when just looking at the description, the neat thing is you can look at this and you can see every single detail, and that makes it perfectly clear. Interest first divided by 12, number of periods, and amount. So I highly recommend using this FX and then help on whatever function you might need help with. It'll be so beneficial. Now one of the new things that happened in 2016 is when you type in an equals and start typing in a letter, it will give you all these great different function names. So that would be another way of inserting a function, is to just scroll down and see what function you might want because it'll give you a description. Not only that, but once you get to the function you want and you decide on it, remember if you double click, not only does it put in that left friend, but it starts telling you what it needs. So let's do payments because payment was what we just looked at. Notice that it tells you, it gives you a little cheats here if you forgot. The first thing it needs is the rate, then the number of periods, and then how much money you're borrowing. The present value of how much money you're borrowing. Okay? So not only do you have all of that available, but if you go to the formula toolbar, it will also show you another way of getting financial kinds of functions, logical functions, et cetera. Okay? So page 6 and 7 show you how to get four different ways of inserting functions, and we've gone over those. Okay? And same with pages 8 and 9. Page 10 talks about the if function. The if function is one of many very useful functions. Okay? So for here. So what it does is instead of you having to figure out and look through thousands of rows to determine does this meet your criteria, Excel can do it for you. For instance, in this spreadsheet, I'm seeing whether or not a sales person or a division east, west, north, south, whether they made their goal, which apparently is over 15,000. If so, then I'm going to say great job, otherwise I'm going to say you're fired. Okay? So let's look at that. That's using an if function. Okay? So for a if function, again, it's going to start with an equals and then I'm going to type in an if. If I don't know how to do the if function, I can double click and it's going to say, hey, the next thing I need to know is what are you going to test on? Well, I'm going to see, I'm going to test on whether this is, let's say, greater than. So I'm going to put in a greater than symbol or maybe I can do greater than or equal to 16,000. Okay? Then notice it has a comma right here. Okay? So I'm going to do a comma and then it says what do you want me to do if it's true? Now, for anything alphabetic, I always need to enclose them in quotes. So I'm going to type in a quote and I'm going to say great job or just great. Then I need another comma and then within quotes, I need to say what I'm going to say if it's not a great job. I'm going to say not great. Okay? And 99% of the time if I just hit enter, Excel is going to be nice enough to put in that right print for me and it did. So now I just drag it down and let's see if it works. This was the only one that was not above 16,000. Now let me change that from 16,000 to 18,000. Okay? And drag down. And I've got three that are not great. So this if statement allows you to check for anything. It doesn't have to be greater than. It can check to see if two things are equal. Many times, clients have given me lists. Two different lists. Let's say this is Tom and this is Tam accidentally or whatever. And so they want to say is this equal to that? And if not, tell me it's different. So you can check different, you know, see if data is the same. Okay? So if statements are just incredibly handy, not only for texts like this, but notice this is an if statement. This is saying if F4, so if F4, this one here, is greater than 15,000, then I'm going to give this person a bonus. F4 times 1.2, so I'm giving a 0.2, a 20% bonus. And if not, I just want to put in F4, whatever the amount is. So this person, because they were over 15,000, they got a bonus of 20%. This person didn't do so great. They didn't get a bonus. Okay? This one did. This one did as well. Now you may wonder why did these three not greats? Why did they still get bonuses? Well, that's because here I'm checking to see if it's greater than 15,000, whereas as you recall, here I was checking to see if it's greater than 18,000. Okay? So you can use formulas in the if statement as well to actually make calculations. Okay? All right. Instead of putting this amount in, what if I just wanted it to be blank or instead of saying not great, I just wanted it to be blank? Well, we cannot leave out this last parameter. You have to put it in, but you can just put in double quotes. Double quotes mean don't put anything there. Okay? So now if I drag this down, it's got double quotes, nothing in between. So if I drag this down, and notice that now it has nothing there. Okay? So if statements are very helpful. Now sometimes you want to have, you want to test more than one thing. And in which case, you're going to need not just an if statement, but also an and and an or statement. Okay? So if you need to say, hey, if they made over 1200 here and they made over 4,000 there. Okay? That would be an and statement type of thing. So since we just have a few minutes left before questions, I was wondering, is there a particular function that you don't cover? And if not, I thought one other function I really like. So if so, please let Brian know. And otherwise I'm going to move on to something called a sum if. Sum if says, add only if something is true. So sum if something is true. So let's go back and it looks like trying to find some of this spreadsheet that has some things here. We'll just make up our own. I'm going to take away the total and I'm going to continue this down. All right. So what if I was like to add these numbers, but I only want to add them if there's ice cream? Okay? So that would be a sum if. Sum if if it's ice cream. So for that, I'll take away these two. They have negatives in there. So what I'm going to do is I'm going to type in my equals and start typing in sum. Notice that right below sum is sum if. And by the way, sum if is on page 17 in my handout. So I'm going to double click on sum if. And it's saying what is the range that you want to check? Well, I want to check to see if ice cream is in this range. So I'm going to highlight that range. Then notice after the range it wants a comma. So I'm going to type in a comma. Now the criteria in this case is we're going to put it within double quotes. So I'm going to hit a double quote because it's a text field. I'm going to type in ice cream exactly as it's shown. Another double quote. And then a comma. And then it's saying, okay, what would you like to add if it is ice cream? Well, I want to add this. So if it's ice cream, then it will add these numbers. And I'll press enter. 2100, let's see if that's correct. I'm going to click on the first ice cream. Control click on the second ice cream. Control, there we go. The third, the fourth. And notice that in, this is a really cool thing. In your status bar, notice it's showing me a sum and a count. It's got four items and it came to 2100. And look at that, 2100. Okay? So it's only counted ice cream. Now let's add like that to happen for the rest. Let me go ahead and drag that across. Oh my goodness, we have a problem again. Why do we have a problem? Well, on the first one it worked fine. Let's go ahead and double click in the second one. Notice that the range that it's checking to see if it's ice cream went from being A to being B. Why? Here's another case of relative cell addressing. Okay? We want A to stay put. So I need to come back to my original function. And what do I need to stay put? I need the A over here to stay put. And I need the A over here to stay put. So there's got to be dollar signs in front of those A's. Now do I want the B's to stay put? No, because the next column I want is C, D, E, and F. So it's just this A that I need to stay put. Put on this so that it stays in place. Drag the fill handle over. And now I've successfully added just the ice cream. But probably want to label this so it doesn't think that it's a total of everything. So I'm going to say ice cream total or whatever. And why isn't this column a bit? So Sandy, we have a question here which is can we look at the VLookUp function? Oh, one of my favorites. Well, if it's okay, I would love to do that. And if you don't have time because that's fine, but for those of you who can stay, we're going to look at VLookUp. Here's my VLookUp. VLookUp, for those of you who don't know what it is, because it's certainly not very clear, I don't think, right? I'm just looking at the name, is V stands for vertical. There's a vertical and a horizontal lookup. And what it's useful for is, if you have, let's say you have a sheet of paper that gives you the name of an item, or maybe the name of a legal description or whatever it is, but you've got something, and based on that, so like for a bar stool, you may have what the item number is and you may have what the amount is. And let's say this is an invoice, and you would like to just be able to type in bar stool, and instead of you looking up what the item number and the price is, you just have to type in bar stool and ExcelNow is smart enough to know to look up bar stool over here and bring in the item number and bring in the amount. So it is vertically looking up, it is vertically looking up through this list what needs to be filled out. So instead of you having to do it, it does it, it's so great. Now, because... So a quick note to people as we're running towards the end here. I've got links to the next three webinars in the chat and to where all the archived videos are including today's video. And as a reminder, the handout is there to be downloaded. If you need any help getting it, you can also email brianr at nwjustice.org, and I'll put that email address into the chat box. Thank you, Brian. And also for those of you who aren't able to stay online, I just want to let you know that ZLookup is on page 15 in my handout, so I do have that in there and you can look it up. But anyway, the important thing to note once again is that when it's looking up in this table, as I move these formulas down, I need to somehow tell Excel to please stay within this range. In other words, I need to have absolute cell addressing because otherwise, as I drag this formula down, it's going to start going down here. It's going to look outside the range. There's two ways of doing that. One is inside the VLOOKUP formula, which is equals VLOOKUP. Let's look at this formula. It says equals VLOOKUP, which is just the name, right? And then V6. What is V6? Well, V6 is... Let me double-click in here so maybe it'll tell us. V6 is the lookup value. What does that mean? What does the lookup value mean? Well, what it means is what is it going to get from here to be able to know what to look up in this table? So the lookup value is always going to be the column that it needs here to look up over here in order to get these other values. So that's why it says VLOOKUP, V6, V6 is where it's going to take it. Then it's saying what range do you want me to look at? Well, one way of specifying the range would be to say F... Let me click here. F9, right? Through H13. But in order for it to stay F9 through H13, I would need to make it an absolute value. Instead though, what I've used instead is this... It's called a range name, a range name of furniture. So what I did before I put in this formula is I highlighted this text, and do you see how over here it's already called furniture? But if it weren't, if it were just a range, I would come in here, I click in here, I type in furniture, I hit enter, and that would name my range. And the cool thing about named ranges is that they are absolute. So it makes it a lot easier for you when you're typing in the formula. So it's equal to the lookup. Then what is it that I'm going to look up, which is V6, right? And then I guess if there's a difficult part, this is the difficult part is knowing what to put in here. This last thing that it's going to ask for is, which of the columns do I want to put here? So this is my table here. The first one is always going to be what it's looking at, the bar stool, office chair, et cetera. That's what's going to match, so that's why it's V6. But then this is column one, column two, and column three. And a lot of people say, well, isn't it F9, isn't it G? No, it's column one, two, and three. So what it's saying here is that in the furniture or in this range, I want to bring in the second column, which is the item number. So look at that. So this is going to be identical to this formula, except instead of column two, it should be column three. Okay? So it's equal to the lookup, V6, which is what's going to match this, and then which column? Two over here, three over here. The only problem with that is, if I don't have anything in here, it's going to give me what's called an NA. An NA is saying it's not applicable. It's not finding a blank over here. So until I type in bar stool, until I type in coffee table, or whatever it is, desk, whatever I want to do, it's going to have this NA. And a lot of people don't really like to see that, because a lot of times, like if this is an invoice or whatever it is, it's not going to be filled out. So they want this to be blank until you fill it out, which is why the very next thing on page 16, it talks about if is NA. But I'm going to, this is what you have to do if you don't have 2016. What this is doing is it's saying if, and then it's saying if it's an NA, which means that that NA were to appear, okay, then put in a blank. Remember, two quotes, two double quotes in a row are a blank. And otherwise, do the V lookup. That's what it's saying. So this is saying if there's an NA that appears, put in blank, otherwise do the lookup. But if you have 2016, instead of using the isNA, which is kind of a long formula, right, there is a new, excuse me, kind of a long function. There's a new function called ifNA. Look how nice this is. So remember two seconds ago, we learned equals V lookup, V6, right, V6 has bar stools, furniture, right, and column two. All you have to do now to make sure that NA doesn't appear is put ifNA, and then within parentheses, the V lookup, and a comma, and what you want to have happen if it's false. Oh, excuse me, that's true. If there is an NA, you're saying put in a blank, but if not, this ifNA just says okay, then just do the V lookup. How cool is that that you now have this shortening of being able to do that? So look, the difference is, the only difference is when I hit delete, instead of seeing all those NA's, you're just not seeing anything. And now when I type in barstool, it's beautiful, okay? So ifNA is something you can use, and it's just basically nesting or putting one formula inside of another, okay? Any last tip you would like to end on here, Sandy? Yes, maybe one of the last tips if nobody's got a question. One of the things I really like to do if you're setting up a new sheet, a lot of times you're setting up something just to add. Equal sum is what people use, a ton of. And so let's say we've just got some data here and some, I'm going to do my ice cream, cherries. And what I really want to do is I want to put a total here and there. So I'm going to type in total, hit control enter, so I get it in both places. I'm ready to do that, but of course if I hit my summation symbol, it has no clue what to do. So with that, and then I would have to drag across what I'd like to add, right? And then I'd have to hit enter and I'd have to pull it across. Which takes a little bit of time, not terrible, but more than I want to spend. So instead what I do is I click in that first cell and I shift click in the last cell, so I'm not including the totals. And I put in just a dummy number like 50. And in order to get it in all those cells, I press control enter, so I highlight the area, I put in a dummy number and I press control enter. Then I press shift and click in, remember one additional row, one additional column, hit the autosum. Because I've got numbers in it now, it does all the work for me. So all I need to do now is shift and click one back and press delete. And I'm ready to go. So I like that little trick of filling in dummy numbers so I can get Excel to put in my formula for me and then just deleting the dummy numbers. So again, all I did was highlight as much as I wanted, typed in the number, and instead of pressing enter, I press control enter, which puts my information everywhere I want. Thank you so much for coming. It's been a lot of fun and look forward to our next training together. Excellent. Thank you so much, Sandy. I greatly appreciate it. We will be sending out links to the email list and putting together a blog post. The booklet is downloadable and will also be published online. Until next time, thank you all.