Excel Functions

[MUSIC] All right, greetings everyone and welcome to another exciting episode of OFFICEPRO.TV. I'm your host Daniel Lowrie, and in today's episode we're actually starting our series on Excel functions. Kind of taking it to the next level with what you can do inside of Excel. Joining us in the studio as always, our resident expert on Excel, Miss Vonne Smith. Vonne, welcome back, how's it going today? It's going swimmingly. Swimmingly, mm. Yes, I know. Do you have to put your pinky finger up when you say that? Swimmingly. Swimmingly. Yes, lovely, lovely, so. [LAUGH] We do have the means. [LAUGH] Yes, we are starting a new series on Excel functions. It's gonna be a lot of fun. And we're gonna start today at super basics. We're like the basics of basics. Introduction to Excel functions and really, this is where we're actually going back a little bit. Really. Yeah, and I purpose didn't tell you what we're going to be talking about in this episode. Yes. I'm kind of like set back a little bit on that. [LAUGH] She's looking at me like, I have plans for you. I know. I mean Excel. [LAUGH] It's gonna be fun. Yeah, on several. We're gonna go back, back in time. Imagine if you will. Yes. Picture this. Back. Sicily, 1922. [LAUGH] Yes, Stewart Florida, Stewart Middle School. [LAUGH] Mrs. Levi's math class. And this is where I actually remember this from. No. [LAUGH] You said the horrible word. I did. Out comes the flop sweat. [LAUGH] Because, of course, when we're talking about Excel functions, functions are really formulas, and we're doing formulas, we're calculating. Are you gonna put the fun back in functions? We're gonna try. Okay. [LAUGH] So I have a PowerPoint presentation that we're gonna start to kinda help us along. So I didn't do any major themes and stuff like this, just- Keep it simple, right? Yeah. [LAUGH] [LAUGH] Wait, this is black on white. Yes, there we go. Black and white. So we have Calculation Basics, all right, ready? Go for it. Do you remember this? Please excuse my dear Aunt Sally. You know it that way too. I do. Yes, that's how I learned it. My gosh, okay. [LAUGH] Must be a Florida thing. It totally is. [LAUGH] Cuz I've had people in class, I've been teaching Excel for a long time. Yeah. We always start with this particular one. I write it on the board, and people are like. I have a dear Aunt Sally, and you have to excuse her a lot. [LAUGH] Yes, and then every once in awhile somebody, usually around my age, Yeah. And they would say that's Please Excuse My Dear Aunt Sally. And the other people would go, what? And then I said this is orders of operations. Right. And that's where they're like- It's a little pneumonic for remembering the order of operations. Yes. And that's where it's, okay Please Excuse My Dear Aunt Sally, and then it was parentheses, exponents. Multiplication, division. Division and subtraction, yes. And then they're like, yeah, that. So they never learned the little- [SOUND] That. -Dear Aunt Sally thing. And that's where I always like, well, where'd you go to school? I thought that was almost universal, man. It was like where did you go to school? When did you go to school? So I think it was more. Something that came out when we were in school? Correct. And it's a Florida thing. Cuz I've had people my same age, that didn't go to school in Florida, like we weren't taught that. I don't know what you're talking about. Yeah. So that's where I thought this was my experiment and you got it right away. And I knew. I got real excited. [LAUGH] So but yes, this is the whole order of operations. And this is where we said okay, this is how you have to perform calculations. And the whole idea was that multiplication and division and addition and subtraction, they're on the same level. And that was like the whole right to left. So, I have some examples, so this right here. We have. If we did not do the order operations we would say okay, 8 minus 3 is 5, times 2 is 10, minus 4 is 6 divided by- Divided by 2 is 3- Yeah I'm not good at math in my head. Neither am I don't feel bad But that's not right. No. Because then I have a slide here of this is where if we did it the wrong way, that's why I have the little numbers, 5, 10, 6, 3. I assume they're behind your head. [LAUGH] Yeah, the answer's 5 right there off to the right. I made these slides to where it's centered for me not centered for them. Cuz it's just like my brain wrong side. [LAUGH] I can't put it over to the left even though my box is gonna be, [CROSSTALK] You will learn. I know, right? [LAUGH] But the correct answer is 2 because the whole Please Excuse- Right. -My Dear, and that's just where when we had our formulas in math class you'd have to put the PE, do that whole thing and then you put like a little check box, okay there's no parenthesis. Okay, so Please Excuse, there's no exponents. There's, okay division and multiplication. In this sense you would underline it, right? Okay 3 times 2 and then 4 divided by 2. We would have to perform those, and it really didn't matter which one we did first because, but- Cuz both of ' are kind of broken apart. Yes. Yeah. Remember the whole show your work. Yep. Goodness. Yeah, all those steps. And so then we'd say now that we're on the addition and subtraction now okay, 8 minus 6 minus 2 plus 2 is 2. It's funny Justin at Death Row is a math major and he was talking to me about this last week, I think. And he said, in math when you do math as like your job the answer is not what's important it's the formula and how do you prove it? And that's why they put such emphasis on show your work. Show your work. Yes and that was the ones like, cuz you wanted to combine multiple steps. Yeah, no can't do that. And they're like no every single line. You've gotta prove what you've done. Yeah. So we have a fun one, ready? Yeah. So we're gonna come to our fun sky cam view. Sky cam? Yes. In an Excel show? I know. My goodness. It's almost like we're a tech show. I'm gonna throw up. [LAUGH] And I have my trusty little pen with all the different colors on it. Okay. A friend of mine got this from Ireland, from Belfast. That's cool. Yes, and this is also talking about middle school. I figured we'd make a whole middle school reference. Cuz these pens, and I don't know about anybody else. I do, we had them when I was a kid. My gosh, I had one that had- Very popular. -like 16 colors on it. Yeah. And they had all, the thing was like this big, it was massive. Every color under the rainbow. Because we also had back in the day before we had cellphones, so we would write notes to each other. Correct. And you always had to write you notes in at least 15 different colors. Yeah, yeah, it's how you roll. So, [LAUGH]. So many a girls note, so. [LAUGH] So we would have to come up here, wow, okay, Please Excuse My Dear Aunt Sally. So we'd put that across the top, right? Yep. And we'd say, all right, so we have to go through and parentheses first. All right, so let's do the parentheses, we'll make those blue. So everything in here, all right? There we go. Is gonna go first. Is gonna go first. But then there's a parentheses within the parentheses. It's inside of the parentheses. So, you've got to do the inside parentheses first. You've got to do that first. So, 5 minus 2 is what? It's 3. 3, and we're gonna combine some steps, just because. All right so, now we have 3. Positive three. Yes. And then we have times 3 plus 7. I'm going backwards. Okay, so now we don't do 7 plus 3. What do we do? We do 3 times 3, which is 9. Yes and that's 9. 7 + 9 is? 16. 16, all right, so we've got all of that out of the way, so now we're gonna go ahead and to the second, times 7 divided by 4 times 2. All right, so now and then we do this, remember, right? Yup. Parenthesis are done, so now we do exponents. What's 2 to 2? 2 to the 2 is 4. 4, all right, times 7, plus 16 divided by, woops that's 4. This is why I should write in pencil. Yeah. All right, so now we got exponents. Now we need multiplication and division and that's where we have to come in. All right, so here is 4 times 2, but then there's also 16 divided by 4. But PEMDAS says multiplication first. But remember, multiplication and division are on the same level. They are on the same level. Yeah, so then it goes left to right. That's right, so you do 7 x4 first, right? Yes, there's one here too, I forgot that one, yes, okay. See, this is why my handwriting is so horrible and why it's so good that we have computers now. [LAUGH] All right, so 7x4 is? 28. Yes. No, right? Yeah, 28. Yeah, [LAUGH] we're so reliant on calculators though. Yeah, I know, I told you, I heard math, and I was like, I'm gonna cry. [LAUGH] And so now I changed my colors in the middle, so now 16/4 is- Is 4. 4 x is? 8. 8+ 28, the answer is? 36, right? And remember, you have to circle it. Yeah, right, you did very well. Right? This was like being in middle school again. I know. I made an F. [LAUGH] Yeah, [LAUGH] I got the pen, I got the paper, yes. You were there. All you're missing is Madonna bracelets and- And a lot of crying. Yeah, that's true. [LAUGH] [LAUGH] I make it sound like middle school was the worst thing in the world. It was pretty bad, but I mean, [LAUGH] it's okay. It wasn't that bad. It wasn't that bad, I survived. So [LAUGH] you can see that this was our formula that we did on the piece of paper. Now we know what's wrong with you. [LAUGH] You got that right, so [LAUGH]. Badoom shh, thank you, thank you. [LAUGH] So I didn't wanna have to try to do that on the computer, so that's why this guy can't. It was fun though, right? Yeah, it was fun, it was a good time. Yeah, not bad. So now that we all remember our order of operations, how does that transfer into Excel land? Because if you're gonna be doing calculations in Excel, you need to follow these rules. If I want to add up all of these and then add up all of these, but then take the result of this and divide it by this, well then, you're gonna have to put parentheses around things, aren't you? Yeah, so do this first, do this first, and then do what's ever in between it, or so on and so forth. So we're using numbers on this particular example. And actually, let's go back over here to my PowerPoint presentation cuz I just had it on the slide now. If we were doing this, I mean, this is a formula. This is where I need to make this result. I have to do this, then this, da, da, da, da. All right, well, this is the end of the PEMDAS. Cuz now, we're getting into how is this correlated into Excel, and like I just said, it works on the same things. So, what we have here when we start getting into functions and formulas in Excel. We do have some vocabulary that's gonna pop up throughout these episodes here. And I just wanted to take this moment to just, well, identify it. I'm not gonna show you how to do calculations in Excel right now. I'm just showing you the end results, so we can identify, that's what that thing is. That's what that is. That's what that colon means. It's a true introduction into functions in Excel. Correct. Start from scratch. Nobody knows nothing, everybody here is a newborn babe when it comes to Excel functions. Let's start there. And the one thing that we did talk about before this episode was the whole idea of just getting that foundational terminology to then not use it right the whole time. [LAUGH] [LAUGH] Yeah, now that I've told you exactly what that is and the correct terminology, forget I even said it. [LAUGH] I don't say that. Because I'm gonna use another word that, to me, that's what it is, but it's not technically right, yeah. So that's why we're gonna be technical, and then we'll- We'll have the editors go back in and be like, function. [LAUGH] Vonne's mouth is doing something different. [LAUGH] So your formula's functions cell reference, now cell reference, we already kind of talked about in other episodes. It's like if we say cell A2, that means the column reference and the row reference, that's the cell reference, okay? But then there's something known as relative reference, absolute reference, and there's also name ranges. These are just basic stuff. There will be other terms that come out through here, because this Excel functions series, this show, this is gonna be a pretty big one. Cuz there's gonna be lots of different topics in here. You probably already see that over in the course library, where it's this episode, it shows our different topics. And we're gonna have a reference guide with everything. This is gonna be like the never ending series. We're just gonna keep adding to this forever. Two years from now, Excel 2018 just came out, we're gonna scrap all that. [LAUGH] Well, that's the other thing, is like we're not doing this platform specific or version specific. Because if you think about- Math doesn't change. Exactly, and there's very few changes that happen throughout them with functions, specifically. And just to start clarifying, a function is just a named calculation, okay, it has a name. Which it really is a formula, so to me, I'm like, okay, what's the difference between a formula and a function? Why don't we jump over here? Let's go ahead and cancel this cuz that was the end of my PowerPoint presentation, and I have my Excel file. And this is just my introduction examples, and we have our basics, all right. So I just have some numbers, they don't mean anything right now. They're just what we've got here. So if I have, so let me look at my notes, E3, okay, so E3, look at the formula bar. Now remember where the formula bar is, right? Yes. The formula bar is up at the top. It's up where the formulas go. Yes, exactly. So we need to make sure that in the formula bar, every time that you're going to perform calculations in Excel, it always starts off with an equal sign. So, see right up there that the cell, the information says 7, right? That's what we see, but it's a result of adding up C2, this cell right here, + C3. It really is 3 + 4 is 7, but we didn't- Last time I checked. Yeah, [LAUGH] but we didn't code it as 3 + 4, this is- Right, you just did the cells. Yes, and the =, when you put the = on the front, it's telling Excel, do this calculation. I would say this would be a formula. It is just taking this cell plus this cell. Right, cuz you haven't given it any name, you haven't said this is something that I want to always be, and I just referenced that named function. It's not a named function. Right. So that's my clarification. So I think of it in program, I do a little bit of programming, dabble in that. And sometimes you have a little piece of code that you're like, you know what? I can reuse this in a bunch of different programs, so you create a function, and it has a name. And all I have to do is call that function, and it does whatever's inside of it. Yeah, [LAUGH] I'm like- Yeah, they're doing it here. That's exactly it. [LAUGH] Yeah, so yeah, you can totally correlate it into other things in computers, perfect. So if I come here to this cell, now the answer that I see is 27, but if you look at it, it's saying, remember what we talked about, the Please Excuse My Dear Aunt Sally? Yes, ma'am. Here it is in the flesh. We're saying add up 3 + 4 + 6 + 5, which is C2. Here, let me zoom out just a little bit, so we can kind of move over. Here, let me zoom out. There we go, so you can see, C2 + that one, + that, + that are cell references. Do all of that first, and then divide it by B6, which is 2, and then times C8. So 7 + 6 is 11, + 5 is- You're getting it wrong now. [LAUGH] Wait, 3+4 is 7, + 6 is 13, + 5 is 18, x 2 is 36. No, divided, 18, 9 x3 is 27 [LAUGH]. Wow, you can see- Yeah, buddy. I'm pretty bad at this in my head, [LAUGH] calculator. That's what we made them for. Yes, exactly, but I mean, these are just numbers. But you can think about this and like this is what I want my information to say. So these are all formulas now, but look at this one. Look, it's E5, cuz I move my, there we go, perfect, I just move that mouse over. [LAUGH] That's how it works. [LAUGH] So this is saying that this is a function. Okay, it still starts off with the equals, so it's performing a calculation. But SUM, hm, what do you think SUM means? Last time I checked, it's the aggregation of two Numbers, the [CROSSTALK] Whoa, you just went too far for me. [LAUGH] Aggregation, what? I can take two numbers. I got a three, I got a five. I put them together I got eight, that's the sum. Add, yes, there you go. We add them together, exactly. So now what we have is that's the function name. So SUM is going to go ahead and add up our numbers. Then the four that you see what's in parentheses here. You could see that this is open parentheses C2 colon C5. Alright so you're basically saying in the parentheses do that function. Add up C2 through, that's what the colon means- Mm-hm. The colon means, seat this right there. Right, so it's some sort of range you give it. Exactly, yes and so if I actually come into here to start to edit it, you can see there it is, that's my sum, that's what I want you to add. I'm glad you really pointed that out, because a colon in my brain. And says this and this right? And not a range a range is usually a dash this through this. That is interesting. Yes. So, that is what it means in this syntax. Yes. It is funny you say that because it is almost like, I know I did not give you any notes for this episode. Zero notes. [LAUGH] For this show. Because well look at this one, this one is the result of eight that is adding up C2 and C5, the comma. Okay, the comma makes sense, I totally get that, cuz Excel loves those comma separated values. Yeah. So I get that, I've been there enough to go, okay, okay. But if I see a dash come up, and it's not a range, I'm gonna freak out. Yeah! [LAUGH] Well, now you know it's a colon. So there you go, so this is saying C2, which is three plus C5 which is five, three plus C5 is eight. Yes. Okay so there's our result. So you could see, formula, just cells doing some type of calculation, using the cell reference. Yeah. A function with a named function, and the parameters that go into the parenthesis Whatever is in those parenthesis, do that. Right. And then colon versus like a comma that you might see from time to time. So those are some basic identification of what you might see when you're looking at formulas within the formula bar. Let's come over here. Now, what I have here is the result is 18. Okay, 18 of, well, what does that mean? [LAUGH] Look at the function, out the formula bar. [LAUGH] Equal sum, functioning, basics exclamation point. Whenever you see that, that means the sheet name. Look at the name of the worksheet down here. Basics okay. So it's saying on that worksheet take the cell range of C2 to C5. It's just these cells again so right here. Here are my cells and that's what i'm naming so looking that different syntax exclamation always going to be referring to sheet names. When we get into where you can actually perform calculations across multiple work books, multiple files. Yeah. Then your file name is in, I wanna say they're in brackets. Angle brackets. I don't remember. It's one of those. But they're some types of syntax. We'll know by the time we get there. [LAUGH] When you get into where it's gonna use the name of the file. It's in brackets, okay or something along that. This is so similar to a basic programming language. It's so funny. No. So if you have any programming experience you're gonna be going man this is, I get it now, let's totally put it together. We're telling it to do something. We're making it do commands. So that's where it all comes, It all comes from one source, right? [CROSSTALK] Full circle [INAUDIBLE] Full circular. Yes. It goes up and down like a carousel [LAUGH] Yes [LAUGH], wow okay, no secret. [LAUGH] We're gonna have some name ranges, this is one you would also see in the [INAUDIBLE] cuz we have a whole Episode on this, to where look at the result. We have sum, we know what that is, number underscore name, what? Yeah, that's just, if I double-click it, it's just that. Because what you can do is intstead of giving cell references. You can actually give a cell range a name. Mm-hm. Okay and so if you think about this and look, if I select this, there it is number, underscore, name. I believe we did this in the excel advancer, there someone else in some of these excel shows, we've done this before, but you can give it a name and if that means something to you. You could say some of January sales plus February sales, it's not just like C2 through C5 doesn't mean as much to me as January sales. So I'm just giving it like a alias, like a name that I want to use. Well we would call a variable. [LAUGH] My gosh, wow you're totally pulling. I told you. And I do enough to not know anything, to where I'm like I know this too. Very cool. So named ranges. And then some other concepts that we have over here, relative reference and absolute reference. This is when you get into some fun stuff. Remember, we're just demonstrating. We have here some sales, right? And we have Fuego and Bishop and everybody here. And you can see this is Sales. This one we've already done, right? So take our range of doing this function on B2 through D2, so January, February and March, perfect. Well, when I do it once, do you think I went through and did every single one of these, and if I had fifty rows I'm not doing each one individually. That would be a lot of work. That would. And you're real work adverse! [LAUGH] Yeah. Yeah here's the thing I'm not down with that. No. Remember the fill handle? Yeah. Yes filling in series? Yes. Well the whole idea about doing this when you do it. Functions or formulas. If I take this and drag it down. One, two, three, it's already done. But what I'm essentially doing is I'm saying take this first one, copy it down, but I'm not copying B2 through D2, it will relatively change its reference based on the position. So if I move down one row, the reference has change down. You got it, right? Very cool. And then you go down to from where you started, that goes down two. You go down three, it goes down three. So as you're filling down, it's gonna copy and use the relative reference so then change the- I'm vaguely remembering this from one of our previous Excel shows in another series where we have to work with that cuz it was like doing something like, it's because it's a relative reference or an absolute- Yeah. That. Yeah, that. I remember that, yes. It's bringing it back, right? Yes. Okay, relative meaning we're relative, it's moving relative to where I'm going and it doesn't just You can go across here, because the file going to go ahead and perform clarifications here, do want drag it over the reference would change, it doesn't matter which way you go. But absolute reference they got wider versus absolute, absolute doesn't move. Okay, so if I was doing a commission right? This is my commission rate of 10%. Well, to find the commission of my sales that would mean take this times this. And that's what I get. So right over here I've got this. Well, now notice here. I'm already gonna just point it out. Look at that, you see there? That is an absolute reference. I am telling it, don't, when I the whole copy down thing, I don't want this reference to change when I copy it. This one's relative, cuz there's no dollar sign. You put a dollar sign in front of the row reference, a dollar sign in front of the column reference, or well flip those column then row. [LAUGH] And then you're saying don't ever move from this position. So when this gets copied down, let me escape that. So here's the first one I copied it to here, F3 relative, G8 absolute. Move down one more, F4 relative, G8 Absolutes, so don't move from here. Now, you can have absolute references on just the row or just the column, or both. So if you think about go ahead and relatively move the row reference but not the column. We'll show examples, but you can have it to where. It's locking down one or the other. And then anything else would change relatively to where it goes. So, that's what we're going to be doing a lot of throughout all of these episodes, but again starting at the basics get that foundation It's in our central topic so this is one of those. [LAUGH] And this is where it's funny, I just created another series in CreativePro with video editing. And we named this file or this episode, Read Me. [LAUGH] That was the title of it. That's a good idea. Right? Yeah. You wanna always read the read me files. So don't skip over this introduction. Well if you're watching it, good. Good on you. [LAUGH] Tell your friends don't skip over this introduction. [LAUGH] Yeah, it goes right Right on back to what we said in the beginning. If I would have just read the introduction files. But that takes reading! [LAUGH] I know. Who wants to read? [LAUGH] But again, that's why you have us. So that is our introduction to Excel functions. Well, Vonne, it as very cool, and like you said, very important, crucial for us to understand, have the same vocabulary. So then when we're speaking about something everyone is on the same page, no one is going, what are they talking about, I have no idea what that means. Defining those terms and definitions right out of the gate that is going to help you and help us help you. So, good on you Von for helping us do that. Good on you for watching this episode as we said before but it looks like we are at that point in time for us to sign off. We're Office Pro TV, I've been your host Daniel Lowrie. And I'm Vonne Smith. We'll see you next time. [MUSIC]