 Welcome, my name is Sandy Rylander and I just want to tell you a little bit about my background. I graduated from UC Berkeley and then started with IBM for about eight years and then started my own training company teaching Lotus Word Perfect and DOS. Shortly after that I started working with the Microsoft Office suite and the main reason I did was because I fell in love with Excel and what an amazing program it is. And formulas and functions are part of what makes it so amazing. It does all the work for you and instead of you having to work for it. Formulas and functions are not difficult to learn. We are going to learn formulas first and then functions. And you may ask yourself, what is the difference between a formula and a function? Well, a formula is sort of the same as what you learned when you went through school. You would say 1 plus 1 equals 2 and that would be a formula. Only in Excel instead of saying 1 plus 1 equals, what you are going to do is you are going to start out with an equal sign. Whether you are working with a formula or a function, you will always start with an equal sign. And your formulas though are to do things like calculations like adding, subtracting, multiplying, dividing. Then when we go into functions, you will see that it will make it easier to add, subtract, multiply and divide. But we will also add a host of other things like being able to test for things, doing logical tests. For instance, maybe I only want to add these numbers if a number is ice cream. So maybe I only want to say $1,000 if ice cream is in the first column. So you can do logical tests, you can do all sorts of different testing with functions. Like I said, we are going to start out with formulas. And so if you happen to have printed out my book before starting class, we are on page 1 and it talks about formulas. It talks about the fact that they always start with an equal sign. So what I'm going to do is I'm going to place, I'm going to click in the cell that I would like to see the formula. And after I click in that cell, the first thing remember I told you, a formula always starts with an equal sign. So I'm going to type in an equal sign. And the reason I clicked in this cell is because that's where I would like the answer to appear. So you always click where you want the answer to appear and then you type in your equals. Now at this point, you may want, if you want to add this column, you could say I want to add 1000 and then plus 900 plus 800 plus 700. That is a perfect formula. It's 1000 plus 900 plus 800 plus 700. So all we have to do now is press enter. Before I press enter, I want to show you that not only, even though I typed it in the cell, you're seeing the formula not only in the cell itself, but notice you're seeing it appear in what Excel calls the formula bar. So whether you type it up here or down here makes no difference. It's exactly the same thing. And so now I can either press enter on my keyboard if I'd like, or do you see this little checkbox here? When I hover over the checkbox, notice it says enter. So if I'd like to enter it that way, I can do that as well just by clicking and it gives me 3400. Now that worked great, but the problem is if I change this 1000 to 2000, that would, you would assume that this would change to 4400, right? And the problem is it didn't. It stayed 3400. Why? Because we would also have to come into this formula and change this to 2000 in order to keep everything up to date. And that's why when I teach formulas, I always try and tell people don't use numbers if you can in formulas, but rather use cell addresses. A cell address. There's some questions here over finding the book. There is a handout section in the on go to webinar. It can be downloaded there. So that whole PDF is available for anybody to download. Thank you. OK, so we just noticed that when we changed our data, we had to change our formula and the whole idea behind a formula is that it will work regardless of how many times you want to change your data. So we don't want to use numbers if possible in our formula at all. What we want to use are cell addresses. So a cell address is the combination of a row letter and a column letter and a row number. So this would be cell address B5. And if you don't know what a cell address is or what this one is, all you have to do is look up here in this name box and it will tell you exactly what cell you're looking at. It's always the column first, which is B, followed by the row, which is 5. And notice when I click in this, do you notice how B and 5 are both highlighted? So that's another way for you to know. So instead of using the numbers, I'm going to delete this and start all over. Still using an equal sign. And what I can do is I can type in B5 if I want, but I'd like to show you another method of entering a formula. And that is what I call the point and click method. So I've just typed in an equals. And now I'm just going to click on the number that I would like to add. And notice that it automatically puts the cell address in for me. I don't have to do it at all. So then all I have to do is type in a plus and click on the next cell address. Type in a plus and click on the next one, plus and the next one. So notice that Excel is doing something else really nice for us. It is highlighting each of the different cells that I've got in my formula. And it's color coding them. B5 is blue and B5 is blue. So you can see exactly what it's adding. Now what a lot of people do when they're first learning about formulas is they make the mistake of adding an extra plus. You don't want that there because it then expects you to add yet another cell. But a lot of people make that mistake. And so they press enter at this point. And I just want you to see what happens. You get an error message. And a lot of people get afraid when they see an error message and they feel like no sounds less harmful than yes. So they click on no before they've even read it. I encourage you to read this error message because it says, we found a typo in your formula and we tried to correct it to not having the plus at the end. Do you want to accept this correction? So yeah, that sounds like a really good idea. I'll accept that correction. And now it's right. Now the cool thing is notice it says 4,400. And now if I change this back to being 1,000, do you see how now it becomes 3,400? So by using cell addresses, automatically it will change the answer every time I change my data, which is always, always our goal. You want to be able to change your data without changing your formula. OK? All right, so we've got equals B5 plus B6 plus B7 plus B8. Now a lot of people try and just do equals B5 plus B8 and think that somehow that's going to work. If you do that, you're going to end up with 1,700. It's going to do B5 plus B8. OK? Now a lot of you are probably saying, wow, that's really tedious. If I need to add a column of 1,000 numbers, I think I'm going to go crazy. Well, that's why Excel has functions. We're going to show you a function in a minute that will allow you to add what's called a range of cells where you will be able to say, just add A5 through A8. OK? Before we do that, however, excuse me, I would like this formula to be in all of these other cells, because I've got a lot of other columns that need to be added. OK? So one way to do that that you probably already know is you can copy and paste it. I can click on Copy. Notice I get a little marquee around. The marquee is this little border around the outside. And then I can highlight where I'd like to copy it to and press Enter. Now some of you may say, well, why didn't you do Paste? Well, let me hit Undo. And notice that as soon as you hit Copy, if you look down here at the bottom of your screen, do you see where it says Select Destination and press Enter or choose Paste? You can choose Paste. I could click Paste right now. But if I do what happens when I click Paste, is this marquee stays put? So then I have to get rid of the marquee. And I can do that by pressing Enter or pressing Escape or something. But the reason that you would press Paste instead of pressing Enter is because you want to copy it again. If I want to paste it again, excuse me, if you want to paste it again, I can keep pasting and pasting and pasting. If I want to, as long as I hit Paste instead of pressing Enter. But if my goal is just to paste one time, the easiest way to do that is to highlight and just press Enter. And your marquee goes away. Now, another way of copying this to the other cells is in the bottom right-hand corner, do you see this in the bottom right-hand corner of this cell? Do you see how my mouse pointer changes from what I call a Fat Plus to what I call a Thin Plus? That is a very special corner of your cell. It's called the Fill Handle. And when you have a Fill Handle, which is the bottom right corner, I can just drag this across and look at that. It automatically fills. So it doesn't copy. So I can't just copy from here to here, but it fills. Meaning that every single cell along the way will get that same formula. Any questions so far? OK, so then I have a question for you. Do you remember that what we did was we said B5 plus B6 plus B7 plus B8 equals 3400? If what I have in this cell is B5 plus B6 plus B7 plus B8, then why is it not 3400? If it truly copied from this cell to all these other cells, then they should really all say 3400, but they don't. Why? Well, if you were here in class, I could see your answers. But since I can't, I will tell you that a lot of people at this point start guessing. And I would prefer that in Excel, you never guess as to why something happens, but rather look. Now, how can you look? Well, all you have to do is click in the cell that you're wondering about. So if we don't know what formula is actually in this cell, click on it. So I click on it and look at that. Instead of B5 plus B6 plus B7, et cetera, it says C5. Look at that. It's in column C. When I come over here, it says D. It's in column D. E is in column E. So instead of copying the way I asked it to do, it actually increased the letter of the column. How is that possible? Well, because Excel knows that if it didn't, then copy and paste would be worthless. And you also couldn't move easily. If I can't copy this formula over here and have it change relative to where I'm going to paste it, if instead it stays static, if it doesn't change relative to where I'm going to paste it, then I can't really use the copy, can I? I would have to go in and type that formula into each cell. So what Excel does is it has, as it's default, it's going to always make your cell addresses, your B's, your C's. It's always going to make it relative to where you copy it. So as I copy to the right, it's going to make it relatively larger. It's going to go ABC. If I go the reverse direction, watch, I'm going to delete these and notice this is going to say M. See that, M across? If I now bring this fill handle backwards, it's going to go from M to L to K. So relatively speaking, it's always going to be correct. So this is needed not just for copying but moving. What if I take this whole thing and I move it down here? If this were still to say B5 plus B6 plus B7, then it would be completely worthless to me, wouldn't it? So it better say what? It better say B14 plus B15 plus B16. Let's see if it does. Or excuse me, and the E, because I moved it over to E. It not only moved down but it also moved across. So it changed these to being across to E and down to 14. All right, I'm going to undo that. Does anybody have any questions about relative cell addressing? We'll see how that goes in the rest of the training. Now, remember before, I'm going to go ahead and delete these. Remember before I said, if this were, let's just make this a little longer. If this were a little longer, I had more cells to add. If you had to say equals B5 plus B6 plus B7 plus B8, you soon would no longer be using Excel. So let me take away some of these dollar signs real quick. We'll leave it, it doesn't matter. But anyway, so what we're going to do is learn another way of adding, which is using a function. Now functions are the same as formulas in that they start with an equal sign. But instead of then just going to a cell address, you put a function name. So for instance, to add the function name is sum. So we're going to type in sum and look at that. As soon as I start typing even the S, when I do an equals S, it starts to show me every single function that starts with S. If I type in SU, it starts with SU. So if I do SUM, it's to equal sum. And if you say, gosh, I don't really know what sum does, notice that when you land on it, it says it adds all the numbers in a range of cells. That sounds pretty good. Now all functions start with an equals, then it has the function name, and then you need it left parentheses. Once you type in the left parentheses, it'll tell you what it needs next. And so equal sum next needs a range of numbers to add. So how do we put in a range? One way of putting in a range really easily is to just drag across the range that you would like to put in. Remember that point and click method. This time we're going to point and drag. So I'm going to hold my left mouse button down starting at B5, going all the way down to B27. But being careful not to hit B28, you don't want to add the sum to itself. You want to stay one short of where your function is. Now this is supposed to end with ending parentheses, but if you leave it out, Excel will be smart enough to add it for you. So how does Excel express a range? Well, it expresses a range by putting in a semicolon. Do you see that? You didn't even have to do that. Simply by dragging, it says B5 through, which is a colon, B27. So I'm saying equals, then I'm saying add within parentheses, B5 through B27. All I have to do now is press Enter or click on the Enter up here, and boom. It has added all of these. Now these are all negatives down here. You see these negative 100. So that's why we're getting such a small number. So let me go ahead and delete this part so that you can see it with just positive numbers. So it's easier for you to add. So once again, I'm going to do equals sum. Then what? Well, if you were here, I'm sure you would say left parentheses. Then drag across the range. And remember, I said then you could just press Enter because it'll put the right parentheses in for you. 5,500. If you want to cross check, if I just drag across these numbers, let's look down here at the status bar. Do you see on the status bar, when I drag across that area, do you see how it says sum is 5,500? So that would be a way of cross checking to make sure that you did it right. So once again, at this point, well, let's do it one more time. Let's do equals. And then the function name, sum, left parentheses, drag. Being really careful not to go on top of the 15, just staying back here and pressing Enter. Let's double check again. I'm going to highlight that. Come down here. 15,500 looks perfect. So now if I want, I can drag this across. And I'm getting all of my columns added. So you may say, Sandy, why did you even teach us a function, excuse me, a formula when a function is so much faster? And the reason is because you can't always use a function. For instance, if I wanted to say equals this plus this plus this, there's really no function for that. Or it might be this plus this minus this, or it might be multiplication. By the way, multiplication looks like this, this asterisk. So that's when you need to use a formula, is when you're doing addition, subtraction, multiplication, all of those sorts of things. So you need to learn both formulas and functions. So let's go back and review some of the concepts that we've learned by I'm going to type in tax here. So we want to know what the tax is going to be on $5,500. So what I want to do is I want to type in the tax rate, which where I live is 10%. So I'm going to just type in 0.1, because that's 10%, OK? So this is a perfect example of where a function is not going to help you. We need to use a formula. So I'd love to ask if anybody knows what the formula should be, but I'm told I can ask. So does anybody know what the formula should be here? First of all, what do I need to start with? Can anybody answer? Because we can see what your answers are here. Yeah, if you type it into the questions, we can see it. We've got equals. Most people are answering. Equals is great. You guys are great. What next? B15 star A17. So the cell addresses. That's brilliant. So what I did was after the equals, I clicked on B15. Then I did multiplication, which is an asterisk. And then I clicked in A17. That looks good. That will give me just the tax, right? So I'm going to press Enter. And then that looks perfect too, right? $550. So then what I'm going to do is just drag that fill handle across. Oh, that doesn't look very good. What went wrong? Can anybody tell me what went wrong? OK, somebody has taken this class before. So again, if you don't know what went wrong, don't guess. Look. Now remember up here, I told you that it changed. Relative cell addressing changed the B to the C to the D, right? So when I come down here, it's going to change the B15 to the C15 to the D15, which is perfectly OK. That's what we want. But the reason we're getting nothing in all of these is what happens when it changes the A17 to B17 to C17? You're multiplying this number times 0 and this number times 0. Let's see if that's exactly what happened. So for the first one, it's great. And if I double click on it, look at this. It's even telling me it's multiplying this times this and I'm happy. So I go ahead and press Enter or press Escape either way. And I'm going to come over here and double click and do the same thing. And look at that. If you don't know, just double click on the cell and it's going to highlight. It's highlighting the fact that it moved over here, which we love, but it didn't hear which we don't like. I'm pressing Escape, double clicking over here. So again, it's doing exactly what it's supposed to do, which is making the cell addresses relative to where you move them. The problem is, in this case, we don't want this one to move. We want it to stay put. So what we have to do is we have to click on the cell with the formula and we can either correct it here or in the cell, it doesn't matter. But we need to make it stay put. And what the person who answered absolute cell addressing or making this absolute is exactly what we need to do. Now, to make something absolute, there's two parts to this address. There's the A and there's the 17. If we want to make them both absolute, we put a dollar sign in front of the A and we put a dollar sign in front of the 17. Notice how long that took me, however. So Excel is nice enough to say, if you position anywhere, your insertion point, anywhere within that address and you press the F4 function key. F is in Frank 4. You press the F4 function key and notice it automatically puts the dollar signs around the A and the 17. That means it's going to make sure that the column doesn't change and the row doesn't change. In this case, do we care about the column changing? It won't change, will it? Because we're going across. The only thing that we don't want to change is the column. It's not going to change the row. So yes, you could leave it like this if you want. But look what happens when I press F4 again. Now it's saying the row can't change, but the column can. Well, that's exactly the reverse of what we need. What we really need is A to always stay A. So if I press my F4 again, look at that. Now it's only going to keep the A stable. And that's really all we need. But let's hit it one more time and you'll see all of the absolute go away. So if I keep hitting this until just the A is stable and I press Enter, now if I drag this across, do you see it works beautifully? Why? Because the first one says A17. The second one better say A17. The third one says A17. So they're all A17. And the only thing that's relative is the B15, C15, et cetera. So that's the difference between relative and absolute cell addressing. So crucial. And so many people don't understand this concept and completely mess up their worksheet. So it's incredibly important to understand. I have a question. So the question was what happens if you use brackets around A17? Then you do and it changes nothing. Brackets is, but the question is a great question for a different reason. Brackets help you with the order of operations. And let's look at that for a second because that's also in my book. So let's say that what we want to do here is we want to not just have the tax, which is what we have here, right? We have just the tax. But we want the tax. We want it to add the tax to this 5,500. We want to see the total including the tax. So then our formula is equals this. Well, actually, before we do that, let's do something a little bit easier. Let's do a really easy formula. What I'm going to do is I'm going to do equals 5 plus 2 times 3. Who can tell me what the answer to 5 plus 2 times 3 is? Please type it in your questions area. The answer to 5 plus 2 times 3. Usually I get three answers. Let's see how many we get in this class. We've got 11, 9, and 21. Oh, look at that, three answers. 11, 9, and 21 is what you were telling me. So let's see how each of you got those answers. 21 is a really popular answer, generally speaking, because what most people do is they go from left to right. So they'll do 5 plus 2 is 7 times 3 is 21. Looks logical. So we'll see about that one. The 11 was somebody did it backwards. Somebody said, let's do 2 times 3, which is 6 plus 5. So that is the 11. So that's how we got the 11. That's how we got the 21. And then the 9 is quite interesting. Not quite sure how somebody got the 9. We also got a 30. And we got a 30. So the 30 was probably somebody thought it was 5 times 2 times 3, not seeing that one of them was a plus sign and one of them a multiplication symbol. So what is the right answer? Well, there is something in math called the order of operations. The order of operations says multiplication and division always come before addition and subtraction. So the person who said 11 who did 2 times 3, which is 6, plus 5, getting 11 is correct because multiplication and division always come before addition and subtraction. So what if you don't want to memorize that? What if you don't know that? Well, then look. What I would say is type it in, press Enter, and you see what the answer is. It's 11. You should know the answer you're looking for. If the answer you're looking for isn't 11, if the answer you're looking for is 21, then what you need to do is that's when the parentheses come into play. That's when you're saying, Excel, I don't care what math wants to do. I want it to do what I want it to do. So at that point, if you put in parentheses and say, hey, I want it to be 5 plus 2, 7, times 3, I put parentheses around what I want Excel to do first, then I press Enter, then I get my 21. And that is a super important concept, which is, again, why it's in my book. There's a comment here, which is PEMDASH. So P-M-D-A-S, parentheses, multiplication, subtraction as a way to remember that order. Yes. So what they're saying is something they learned back in, probably elementary school or whatever, to help. It's a word that helps you remember what exponent, exponentiation, exponential, anyway, exponents come first, and then multiplication, division, addition, subtraction, et cetera. So that's what they're saying to help you remember. But usually, I think for most of you, you're really only going to be dealing with multiplication, division, addition, and subtraction. So just remember, multiplication, division come first. So let's test that concept. Let's see what a second ago I was saying. I would like the formula for calculating the tax and the total, so these two combined. So what would be my formula for that? I want to do equals, and then what? So I need to take this, right? Actually, I want to take this, and then what? Does anybody know how to do this? I would multiply 1 plus 0.1, which is the tax, because the 1 is going to give me the number itself. You multiply anything times 1, and you're going to get the number. And then you want the extra 10%, so I'm going to add the 10%. But is this going to work? What's going to happen? Well, let's press Enter and see. That's not quite right, is it? Why? Because what it did is it took B15 and multiplied it times 1, which gave us the 5,500, and then it added 10 cents. That isn't what we want. What we want is we want it to take this and add it first, so that it's 1.1, right? So if we do that, it should look a lot better, and it does. So this is an example of when you wanted to add first and then multiply, not multiply first and then add. So it's really important that you understand this order of operations, or if you don't, you can always put parentheses around whatever you want it to do first. It's just if you put it around something that's multiplying, it's going to happen on its own anyway, so you don't have to do it. So it'll save you time if you understand that the only time you need to use parentheses is when you need it to change the order of operations. Any questions on that? Any questions on absolute versus relative cell addressing? Is there a difference between the use of parentheses and brackets? Yes. Brackets are for arrays, and parentheses are for order of operations. So we definitely want to use parentheses when we're talking about these formulas. All right, so I'm going to delete all this now. Actually, I'm going to delete all this now. And now I'm going to add. So so far, we've typed in everything ourselves. And once again, anytime anything seems cumbersome in Excel, there's a faster, better way to do it. And so one of the things that you do all the time is adding rows and columns. So Microsoft put this autosum tool right on the toolbar. So once again, I'm just clicking where I want the total to go. And I'm going to click on autosum means add. So I'm going to click on that and boom, it just did everything for me. Typed in the equals, the sum, the parentheses, even was smart enough to put in the range. So why didn't I show you that first? Well, because it's really important for you to understand how to come up with this on your own, how to use these little helps and understand how these ranges work. Why? Well, look at something that might not work as well. I'm going to delete this, come down here, click on autosum. And a lot of people might not notice that Excel, because there was a blank cell there, thought that it was doing the right thing, but in fact, left out all these numbers. So it's really important when you click on the autosum that you always check to make sure it did the right thing. In this case, you have a couple ways of correcting it. You certainly can come in here and just type in a five instead of having it start at a nine if you want. The other thing that you can do is notice there's a little box in this upper right hand corner. I can just drag it up. That'd be another way. Another way to make sure it does the right thing is don't give it a choice. What I mean by that is if I highlight this area plus this extra cell, and then I click on autosum, I am forcing Excel to start at B5 through B14 because I highlighted it. I said, I don't care what you want to do, I'm telling you what to do. OK? So let's go back to here. Now, again, a faster way yet is you can highlight all the cells that you'd like to be added and then click on the autosum. OK? But sometimes people like to add not just the columns but also the rows. If you want to do that, you can drag across the entire area, just add a row and add a column so that it knows where to put the totals. Then click on autosum, and it just did it all for you in both directions. OK, notice that? Now, what if you had multiple stores? OK, I'm going to copy this and paste it here and paste it here. Let's say this is Seattle, San Francisco, and New York. And now what I want to do is I want to add all three stores. I want to grand total because each of these are sort of like subtotals, right? Let's do this a little faster here. Let's do them all at once, total and total. I don't know if you know, but if you highlight a bunch of cells like that, and instead of pressing Enter after I type in total, if I press Control Enter, boom. I just put it in two different places at the same time. How cool is that? OK, so I think most people, when they want to add all of these subtotals, what they would do is they would do equals, and then click on this, plus, click on this, plus, click on that. Is that correct? Absolutely. Is that tedious if you had 100 stores? Absolutely. So instead of doing that, if I want to grand total, if I select all these columns and an extra row for my grand total, and then I hit Auto Sum, look at this. Excel is smart enough to do what you just did manually. It says equals sum, and then it says I'm going to add. B39, let me just double click on it so you can even see it more clearly. It's going to add this cell, plus this cell, plus this cell. So instead of taking minutes to do it, it took you all of a second to do. And that's why it's so cool to really learn what Excel can do for you. Now, the only function we've learned so far is equals sum. What if you want to get an average? Well, that's what the dropdown arrow here is for. If I click on this down arrow, look at that. It's not just summing, which is adding. There's averaging. There's counting, finding the maximum and the minimum, and tons more different functions. So let's say I want to find the average. I just click on the down arrow, click on Average. And notice it's giving me an average, but notice what it's doing here. It's adding these extra two columns that I don't want. I don't want the total in the average, and I don't want the tax total in the average, do I? So I need to either change that B40 to B38 or somehow move this little, if I can get to the bottom right-hand corner, which as you can see is a little tough to do, I need to move that up a little. Since it is a little difficult, I'm just gonna re-highlight what I actually want to average and press Enter. And then I can drag it across and get my average. If I want to find the minimum, I can come back up here, go to Min. Same thing, drag across what I really want and press Enter. That's how easy it is to use some of these functions, okay? What if you don't know what function you want though? One of the things you can do is you can click on this FX, which is Insert Function, or you can go to here and go to More Functions. Those are exactly the same thing, either way. So I click in the cell where I'd like to function, click on FX, and it will tell me, first of all, it tells me the most recently used functions, the ones I used most recently. But I can also ask to see all functions if I want, or I can just look at a subset, like just financial functions or just logical functions or whatever it is that I'd like to do. I can also come up here, if let's say I'd like to know how to find an interest rate, I can just start typing in int and go, and see if I see anything for an interest rate, or, well, this actually says int as in integer for number. So what I meant to do is do something like rate or payment or whatever it is that you're trying to do, just type it in and it will search for it, okay? All right, so we've finished doing page one and page two with autosum. Summing rows simultaneously, we're doing insert functions on page five and the insert tool. The insert tool, which is what I was just on a second ago, is really helpful not only to find something, like for instance, what if instead of trying to add these, notice that this was a total that told me the total of what was in here, but instead, let's say this was a united way campaign that you were doing and what you really wanted to know was out of all these cells, who contributed? This person called, cherries didn't contribute, this person called cones didn't contribute. So instead of doing an equal sum, I want to do a count, okay? So if I don't know how to do a count, I can click on here, I can type in count or I can click on count here. It sort of tells me a little bit about the count function here, but not a lot, right? It says it counts the number of cells that contains numbers, but maybe that's not enough information. So the neat thing is I can click on help on this function and help in Excel is amazing for these things, okay? So it gives you a definition of what the count function does, okay? And then it gives you an example of how to do a count. It even gives you a video of how to do a count and then down here it explains what value one means, okay? It says the first item or cell reference or range where you want to count the numbers, okay? And if you continue down, you'll actually see examples, examples that you can copy into your spreadsheet and see exactly how they work. Like here it says equals count counts the number of cells that contains numbers in cell A2 through A7. So it tells you absolutely everything and it also gives you related functions. So it's such a helpful thing to use the help function and to remind you how we got there, we just clicked on the function we wanted to know more about and click on help, okay? So what we can also do, if we just want help with entering the function is we can now double click on count and it says okay, I'm gonna put in the word count and I'm assuming that you wanna count B36, let's see if that's right and it's not. We want B29, do you see where it says B36? So again, it's only guessing to where it hits the first blank cell. So we wanna start up here. So remember all we have to do is drag and notice as soon as I start to drag, do you see how the box shrinks up so that you can see your spreadsheet and as soon as you let go, it comes back down. So you're asking for a count of all cells that have numbers between B29 and B38 and look at that, it even tells you the answer. So you can see, is it correct before you even hit okay? Let's see, one, two, three, four, five, six, seven, eight. Whoops, shouldn't have clicked but it's exactly right, isn't it? So eight is the answer here, I'm gonna click on okay and boom, there's the eight. So that's what the count function does, okay? So the insert function and the help function on the insert box is amazingly helpful. Okay, any questions so far? All right, so then we're gonna go on to some a little bit more difficult functions because all you've learned so far is you've learned how to do functions that basically just take a range. Although let me show you that it doesn't have to just be a range. Remember we did the sum function and all of our sums have just been one range, like I'm just adding this and I'm happy with that but notice that it says number two, it can be another range. I can put in a comma, do you notice there's a comma right there and then do another range and it'll add that and I can type in a comma and type in a hundred and it'll add that and I can do a comma and click over here and it'll add that. So I can have as many different ranges as I would like and press enter and it will add all of those different things, okay? So that's the sum. Now let's look at something, let's look at a little bit more advanced topic of if statements because a lot of times people want to do something if, okay? For instance here, if somebody earns over a certain amount you might wanna say great job or you might wanna give them a bonus, who knows, okay? So let's look at an if function. So an if function is a test. What it's doing is it's saying if, notice it says equals if, if and then parentheses, we always have that and then it's saying F4, well this is F4. So it's saying if whatever's in this cell and then this little symbol here is a greater than symbol. If F4 is greater than 15,000, then say great job. So this is what happens if it's true and this is what happens if it's false. We're gonna say you're fired, okay? Not very nice, but that's the way it is. So let's see if we can recreate that and maybe it'd be a little nicer. So let's start out with equals. Maybe we don't wanna type in equal if. So I'm gonna click on the FX and right here's my if. Maybe I don't really know how to do this. Even though it's telling me how to do it down here, maybe I still don't quite understand. So I'm gonna double click on the if. So it says after the if, I need, notice it's already typed in the equals the if and the prints. It says all the work for me. So the first thing I need to do is do the logical test and it says the logical test is any value or expression. That was equals, remember that we said if this, which is F4, is greater than. So I'm gonna use my little greater than symbol. If it was greater than, let's do 16,000. So I can just type in 16,000, okay? Then it wants to know what happens if it's true and if it's true, we're gonna say great job or just great. Now look what happens as soon as I press tab, did you notice how it added quotes for me? Now, if you were typing this in, you would need to add those quotes yourself. Anytime you type text like great in function, you need to add quotes. But if you do it in this box, it'll do that for you, okay? Now it's asking what you wanna do if it's false. And we'll put not great, okay? Once again, if we press tab, then we're gonna go ahead and see, notice it says on this first one, it already evaluates it. So on this first one, it's saying great. So we know that's exactly right. This is more than 16,000, isn't it? So even before we press okay, we know we're doing well. Boom. Now those of you who know the fill handle know you could drag that fill handle down, right? But if this were 400 rows, that wouldn't be fun. So instead of dragging it down, if you just double click on the fill handle, boom, it brings it down for you. It'll bring it all the way to the bottom of the column that's to its left, okay? Pretty smart. Okay, so, but what if we don't wanna guess? Question? There is a question here over, also matching non-numerical spreadsheets by names. Like how do you do that? So just as a next thing to think about, how can I match two non-numerical spreadsheets? Non-numerical spreadsheets are non-numerical stuff in the cells, like- My guess is they're meaning stuff in the cells. Okay, so if I have what you're saying is, so I've got Sandy here and Sandy here. And then I've got Roger here and I've got Sandy here. I'm assuming what you're asking is, how can I test to see if what's in this cell is the same as what's in this cell? And then this is not the same as that, right? So, and then we'll do Tim and Joe and Tim and Tim. So, not Tim and time, Tim and Tim. Okay, so if the question is, can anybody tell me how I do that with an if statement? So my logical test is, if, right? Beginning per end. And then I'm gonna do if what's in the cell, if what's in this cell is equal to what's in that cell, and remember what comes after the comma, well, you don't have to remember, because it tells you right here, what are you gonna do if it's true? Now, some people may say, well, if it's true, I'm happy. So I don't wanna see anything. Now, in Excel though, in this, you cannot just leave this blank. So if what you wanna see in your cell is nothing at all, then just put in quote, quote. So what you're saying is, I wanna see nothing, but you can't just leave that blank. You need to put something. So we're just putting double quotes, which will mean it'll be blank. And then I'm gonna do a comma, and then I'm gonna say quote, because remember, if you're not doing it in that box, you have to type the quotes in yourself. I can say, do not match. Is that what you were asking? I'm hoping, okay? And then end parentheses or Excel will do it for you. So what you're saying is, if this equals this, don't show me anything, but if it's not matching, then say, do not match and press enter. And it didn't do anything. Now you may say, oh, it's not working. No, these match. So we're good. We won't know anything until we copy this down. So I'm gonna double click and look at that. This is a great way to see if your database, if you're comparing one to another, where they're not equal. I see there's another question. So it is, or if I'm trying to organize data from different years, can I match all years about Sandy? I'm trying to understand the question. If I'm trying to, can you match all years about Sandy? I'd have to know how your data is set up. So I guess, could you tell me a little more or hello? Can the person tell me a little more? Well, let me, they're typing it in. Okay, so while we're waiting, we're gonna go on because it also might be something that'll be better handled offline because I believe, how much time do we have to continue? 15 minutes? 30 minutes, 30 minutes. Up to 30 minutes. So we got plenty of time. But anyway, oh, up to 30 minutes. Great, okay. So we're gonna continue. So one thing you can do, like I said, is you can put in text, but as you see here, another thing you can do when you're comparing two things is you can use formulas. So within your function. So here it says, if F4 is greater than 15,000 this time, if it's true, we're gonna give this person a bonus. So we're gonna take F4 and we're gonna multiply it times 1.2. Remember how we did that? So multiplying times one means we're multiplying times itself. So that's giving us the 19,110. And then we're adding a 20% bonus because we're doing 1.2, right? If not, then we're just going to show whatever's in F4, which means they're getting no bonus, but they are gonna get their regular pay. So that's what you're seeing here, okay? So you can either use text or you can use actual formulas in side and if statement. Okay, okay. Now, sometimes though, you want to test more than one thing. Maybe you wanna say, not only does it have to be over 16,000, but also they had to do more than 5,000 in April, okay? If you're gonna do something like that, then you're having more than one of these tests, okay? So if you want to have more than one test, you're going to have to, inside your if statement, use what's called an and statement. And is going to allow you to have as many different tests as you want. So I'm going to come right inside the if statement and I'm gonna type in and. And remember every single thing you do is going to require, every single function is going to require parentheses. So just to make life a little easier on me, I'm gonna put the beginning and ending parentheses around my argument right away, around my evaluation statement right away. So I don't forget. And notice how nice Excel is. This is also a relatively new feature that it's color coding my parentheses. It's saying this black one belongs with that black one. This one belongs with this red one. So you don't get confused. So now instead of just testing CFF4 is greater than 16,000, I'm going to type in a comma because see my and statement is asking me for how many logical things I want, but I just need to type a comma between them. And I can say also April, which is E4 has to be greater than, oh, let's say 5,000, okay. So I've now got two different, not just one, but two different evaluation criteria. Okay, so it's going to be harder now. Okay, we'll see how that works. So I've got great, not great, and two greats, right? So let's drag this down. All of a sudden it went from having a few greats to only one great because only one satisfies both criteria. That's what the and criteria does. Now, if you only care that it satisfies one criteria or the other, which means you should have a greater number of greats, then instead of having this be an and, we can change it to an or. Or means only one of the criteria needs to be satisfied. So you should have more, oops, except it went away. Oh, it didn't go away. Yeah, I just moved down one because I hadn't filled it in yet. So there's an or here. So now I have to copy it down by double clicking and look at that. It went back to all being great because only this had to be more than 16,000 or this had to be more than 5,000. So I got all my greats back. So or is always more inclusive and is always more restrictive. Any questions on that? So that's on pages 10 and 11 is the if and and or statements. Now, the next thing that we're going to go into is something that when I first learned it, I was just like, I don't even know what this means. It's called VLOOKUP. And this actually may be the answer to the question that was asked a few minutes ago about how do I match this person's name with different years worth of data? So I'm gonna go to a different spreadsheet here and I'm gonna go to VLOOKUP. So what VLOOKUP does is, let's say you know your inventory, you know one's called a bar stool, one's called an office chair, et cetera, but right off the top of your head, there's no way you're gonna remember that its item number is 1,000 and its price is 175. So wouldn't it be nice if there were a table like this where all I would have to do is type in bar stool and it would automatically populate the item number and the price from this table over here. So this may be the answer to what this other person was asking is, hey, if the name is Sandy or bar stool, then bring in this data, okay? So VLOOKUP is basically, it's V stands for vertical, meaning that your data is running up and down. HLOOKUP means your data is running, your table is running left to right, okay? All right, so we're gonna start with VLOOKUP and what we'd like to do, look what happens. If I delete these, do you see I've got nothing there, but as soon as I type in bar stool, so I'm gonna delete this, it gives me nothing, but if I type in bar stool, because of the formulas that I've got in here and here, it's going to look up in this table, it's going to look up what these values are. So how's that happening? Well, first of all, what I'm going to do is give this range a name. I'm gonna type, call it furniture. Now why? Well, because then in my formula over here, I can just use furniture as the range to look up all my answers, okay? Now you may notice as soon as I highlight this, do you see that over here in the name box, it says furniture is already there because I already created it. That had you not, if you need to name a range, and this is on page 12 in your handout, all I have to do is highlight the range that I want, click in the name box, type in furniture, and press enter. That's how easy it is to create a range. And what's nice about a range is it's automatically, remember those absolute cell addresses we talked about earlier? It makes these cells absolute so that it knows that it can only be in this area here, it doesn't change, okay? All right, so let's look at the VLOOKUP. Before we try and do it on our own, let's actually look at an example. So the function is called VLOOKUP. So I type in equals VLOOKUP, or I can use the insert function. The next thing it says is it wants B6. What is that? Well, B6 is this guy here, Barstool. B6, what it's looking for, it says lookup value. What it means is what do I have to type in here that will match with the first column here? Okay, it's always gonna be the first column. What do I have to type in here to match with the first column in our table? That's what lookup value means. Then it asks for a table array. Well, the table array is nothing more than this table that we called furniture. So the lookup value is B6, the table array is furniture. Is there a question? There, one or two people are looking for the handout. Just as a reminder, it is under the handout section of the go-to webinar. There's a PDF there that you can download that had, which is where we're referring to different pages here. So it is downloadable through the go-to webinar application on handout. We will also post it along with the video and with the blog post where we summarize what's going on here. Thanks. Okay, so we have the table array. The next is the column index. Now that's a little harder to understand. Well, let's look at it. So notice that it says B6. So remember B6 is what it's gonna look up. Then the next thing it wants is the table array, which is furniture. And then the next thing is the column index. What does it mean? All it's asking is what column, starting with the bar stool being the first column, what column would you like to return in this table? So since I'm looking for item number, the column not number one, but number two. So this would be column number three. Okay, so one, two, three. So it's saying look up B6 bar stool in the furniture table and bring me back whatever the answer is in column one. Okay, let's see if we can recreate this ourselves. All right, since VLOOKUP is a new thing for us, maybe we wanna hit the FX. And if we don't see VLOOKUP in this very first thing, we can type it in if we know the name of it. Here's VLOOKUP. We can double click on it and then we get our little box to help us out. First thing it asks us for is the lookup value. Anybody remember what the lookup value is? What cell address? It's asking for what here is going to look up the value we want. Well, it's B6. That's what's gonna look up and it sounds like somebody got the right answer. Then it's asking the table array. And so we could just highlight this and because we've already named it furniture up here, it actually brought in the name furniture here. And then it's asking for the column number. If we don't know it, well, we don't wanna have G9. We wanna have a two because it's an index number, okay? And then click on, okay, and boom, there's a thousand. Let's see if we can do the same. Let's see if you guys can tell me how to do that for this. I start out with what? N equals and then VLOOKUP, okay? I can now click on VLOOKUP and it'll help me. It'll give me these little hints. It says the first thing I need is the lookup value. Anybody remember where that lookup value is? It's barstool. Remember, it's whatever I have to type in to get what I'm looking for in column one. Then I'm gonna type in a comma and then I can type in furniture or I can just highlight my table, do a comma, and the index this time, what number is it this time? Two would give us the item number, right? So it's three that's going to, it's the third column, one, two, three, that's going to give us the price. So the cool thing is now, I don't have to remember item number or price ever. All I have to remember is that my item is barstool and boom, I get both the item number and the price looked up for me. It is so cool for when you want to be able to just remember, it's really used for whenever you're having to look up on a sheet of paper, what the information is that you're looking for, instead of you having to do that, you can just type in what you do know. I've seen people use it if you're an accountant for like general ledger numbers or maybe an office number or maybe you type in my name and you want it to automatically bring in my phone number or my address or whatever it is. It's just so handy any time you find yourself looking outside of Excel on a sheet of paper for something or a table, you just put that table in here and it can bring the information in for you. So the bummer though is generally you want to leave this blank and you know until you want to type something in, right? And the last thing you want to do is to have to type in every single one of these. You may just want to maybe only have a couple of things like you may only want to have coffee table. So how do we just see these cells being blank instead of NA? NA means not applicable and it's not applicable because it can't find blank in our table, okay? So how do we get rid of the NA? Well, we're going to look at that in just a second but I also want to, before we do that I want to go back for one second and look at our formula. So bar stool matches bar stool perfectly. What if I type in bar stools? It's still matching. That doesn't make any sense because it's not an exact match. Well, that in fact is the default of this function. If I type in one more comma, you'll see what I mean. Look at this, true, which is the default is an approximate match because I typed in bar stools. It says, oh, that's good enough. I'll take it, okay? So even though it's not exactly the same, it's still finding it. If I said false, so instead of just leaving this blank if I chose false here, okay? And press enter. Notice now it says, uh-uh, I'm not giving you that unless you type in bar stool exactly, okay? So if you let it default to true, then it can be approximate if you let it default or if you choose false, it has to be identical. Now, why is that important? Well, what's important then is that if you say that it does not have to be an exact match, you need to make sure that these are sorted in order. Notice that we have B, C, D, L, O, those are sorted because what happens when you say it doesn't have to be an exact match, it's going to look up the line, tell whatever value it sees is higher and then it's gonna fall back. So what I mean by that is that when I typed in bar stools, it's gonna say, hey, bar stool doesn't work. I'm gonna go up to coffee. Well, coffee is a higher value, so I'm gonna drop back and give you the bar stool value. Does that make sense? So you may say, well, I don't really understand why that's important. Well, let's look at a tax table. Let's say somebody that makes 10,000 to 20,000 gets taxed at a certain rate and somebody who makes 20,000 to 30,000 gets taxed at another rate. Okay, so whoops. So let's say this first person gets taxed at 10% and the second person gets taxed at 20%. So what happens now? If these are sorted in the correct order, if I make $10,001 and I say it doesn't have to be an exact match, it's going to drop back and it's going to make my interest rate be 10%, excuse me, not interest rate, my tax rate be 10%. If I didn't have that option of saying that it doesn't have to be an exact match, I would have to have a table that said if I made $10,001, if I made $10,002, if I made $10,003, if I do, do you see what I'm saying? If you had exact match, this would be useless because you'd have to account for every single possible option. But with this in sorted order, with this going up in value like it is, then all you have to do is say whatever it is, if it falls between these values, it'll drop back and take this. Okay, so anyway, that's why it's important if you say that it doesn't have to be an exact match, make sure you sort them. If you do say it has to be an exact match, it doesn't have to be sorted because it could find Barstool even down here to look through each and every one, okay? All right, you have a question. So there's a quick question. Can you show the formula for that? I think they just wanna see the formula on the screen. For what now? Oh, for anything? Yeah, here. So here's the lookup. For the 10,000 minus 20,000. Oh, well, actually I sort of misled you a little on that. So all I would need is 10,000 to be honest, and 20,000, right? Cause it's only and 30,000. And then this would be 10%, 20% and 30%, whoops, 30%. And so then if this were my tax table, so I could, so I just highlighted it, I'm calling it tax. And then if I say that Sandy made $10,001, okay? And Roger made $20,002, okay? So then what I wanna do is I wanna do my VLOOKUP. Can anybody help me with that? Equals VLOOKUP? Let me know if anybody is volunteering to help me with that. Nobody yet? Okay, can anybody tell me what the lookup value would be? Remember, your lookup value is always, what here are you trying to look up in the first column over here? So in this case, it's this, right? Okay, that's my lookup value, comma. Whoever said Q6, you're brilliant. You got the concept. Then this is my table array, which should put tax in for me and did, comma. Column index is, well, there's, it'd be two. One would be the 10,000, 20,000, 30,000. We wanna bring in the tax rate, which is two. And we're done. So Sandy gets to pay 10% and oh, poor Roger, he has to pay 200% because I forgot to put in the point. So Roger has to pay 20%. Notice it's not an exact match, but because I have these sorted in order, it says it's not 10, but I'm gonna go up to the next one. Ooh, the next one is more, so I'm gonna drop back down. Okay, so if I had these, if I had 20,000 first, then 10,000, then 30,000, you could see that that would be totally mixed up in how it would, because it starts at the beginning and goes up the line to see where it should drop to. Who, whoever asked me to do that, did that answer your question? I'll assume yes. Oh, yes, I don't even have to assume, cool. So VLOOKUP is on page 15 in my handout. And page 16, I have two different statements there. If-is-na is what you used to have to do if you wanted to test to see if there was an na. Remember, if I delete this, I get to see that na, which is not very nice. If I'm trying to create, let's say an invoice, and I would just want these to be blank if I don't type something in, seeing all these pound na's would just be annoying, right? So it used to be that you needed to do, let's go to my if-is-na, okay. I'll just show it to you because it's kind of a bear. It used to be that you needed an if statement, and then you would test to see if it is an na, which is a non-applicable, right? And then you'd say, well, if it is not an na, then leave it blank. Remember the quote, quote, leaves it blank, and otherwise go ahead and do the VLOOKUP. And so then when I delete the barstool, do you see how it just nicely leaves everything blank? So that's nice, but you saw that is one heck of a large formula, right? And that's the way it was for years and years and years. And then about two releases ago, they came up with this wonderful if-na. And if-na is a, it takes the if statement and the is statement and bundles it into one so that now all you're doing is instead of an if statement, you're saying if-na altogether, and then you're putting in the VLOOKUP. So what it's gonna do is if it isn't returning an na, it's going to do the VLOOKUP, but if it isn't, if it's gonna return an na, then it's gonna leave it blank. So you have shortened this statement by half. Super cool statement. So if I come in here and I delete this, notice it just all goes away, but as soon as I type something in, it comes back. So that's if-na, and then this is just your regular VLOOKUP statement followed by a comma and remember the two quotes just say leave it blank. If it's not gonna show anything, then leave it blank. Don't give me an na, because I know it's there. I'm capturing the na and telling it don't show. I just want it to be blank. Does that make sense? Okay, that is a lot of times in learning functions, a picture's worth a thousand words. And that's why in my handout, in case you forget how to do this, it shows you exactly what it looks like to do a VLOOKUP. It shows you exactly what it looks like to do an if-na. So you start getting the concept by seeing by example. I always think that's the easiest way than just to try and remember a bunch of different values. Does anybody have any questions on anything at all so far? Okay, all right. So I just want to show you, let me see if it was in this one. No. All right. I wanted to show you one other, oh, here it is. One other thing that we probably really, well, actually we probably don't have time for it, but I wanted to bring your attention to a function called the SUMIF function. What SUMIF does is says I only want to add certain numbers if certain criteria is met. So I'm going to double click on this just so you can see. So what it's saying is SUMIF, A16 through A12. So SUMIF this range is greater than or equal to 4,000. So basically it's only going to add this if it is 4,000 and what is it going to add? It's going to add these. So what I'm doing is saying basically if this is a lounge chair, if this is 4,000, then add it. And look at that. I have one lounge chair and it's 800 and so it's giving me 800. If I had another lounge chair, maybe it all of a sudden added another number to it because I've got two 4,000s now, right? And so it's adding that to it. So anyway, we don't have enough time, but it's on page 17. It was the last page we have in here. There's a SUMIF, there's a COUNTIF, both of which are super cool features that if you have time to go look at them in case you ever need them, at least you know it's there. Does anybody have any questions before we leave? One last question here. Is there a function that can be used in order to determine a total match amount of 20% of a total projected cost? Which is the sum of the project request and the proposed project match? For example, a project request of 400,000 must provide a minimum match of 100,000, which is 20% of the total project cost 500,000. So it sounds like you're comparing based on two different criteria. Is that what he's asking us to do on two different criteria? If that's what you're trying to do, then that would be the SUMIFS formula. So the SUMIFS allows you, so let's say I wanted to say only if the item number is 1,000 and the description is bar stool or in your case, only if the project is 400,000 and what was the other thing? Whatever the 20% was, only if these two are this, that would be equal SUMIFS and you would highlight both of those columns and then get your answer. If I'm understanding you correctly. And if not, I may need to just have you send me the question and the spreadsheet offline so I can take a look at it and see what you're actually asking. Well, we are right at the 90-minute mark here. I wanted to say a huge thank you to Sandy for putting this training together. Thank you so much.