Function Anatomy

Quick reference

• 00:04 In this video we're gonna talk about Excel functions.
• 00:08 It's important to understand that Excel functions and formulas,
• 00:12 although often referred to as the same thing, are actually very different.
• 00:16 What a function is, is it's a prepackaged algorithm
• 00:20 that allows us to pass certain parameters into it and get a result back out.
• 00:26 Now, the interesting part is that they work very well with formulas.
• 00:29 As a matter of fact, in order to use a function, it has to be inside a formula.
• 00:33 And we can use multiple functions or only one function, or
• 00:37 none at all inside a formula to get the results we need.
• 00:41 Every function has some key parts to it.
• 00:44 It has a function name, it'll have required parameters and
• 00:47 potentially optional parameters as well.
• 00:50 And this is taking from the IntelliSense help that we see that
• 00:53 pops up inside of excel.
• 00:54 You"ll notice that the function name is always followed by an open brackets,
• 00:59 and then the list of any parameters, followed by a closing bracket.
• 01:03 The current parameter is always listed in bold.
• 01:06 So when you look at the sum function here, you can assume that we actually snapped
• 01:09 a picture of this as we were working on number one, which is the first parameter.
• 01:15 Optional parameters are surrounded by the square brackets.
• 01:19 So, in this case, number two and anything after it, the comma dot dot dot,
• 01:24 allows us to know that we don't have to provide a number two if we don't want to.
• 01:29 But we also know by default then, that anything that doesn't have square brackets
• 01:33 around it, it's the case of number1 here, is not optional, it's required.
• 01:39 Something that's also important to understand about functions is that we can
• 01:42 use ranges as these parameters.
• 01:44 We can use regular numbers, we can use text, or
• 01:47 we can use ranges depending on what the function needs.
• 01:50 So let's go take a look in Excel and see how we can actually
• 01:54 use a function in order to build a good formula to work with.
• 01:59 Okay, so here we are in Excel, let's see what we can do here.
• 02:03 If we wanted to leverage a function to sum the numbers above cell B7 here,
• 02:08 what we would do is we would start by pressing the equals key because every
• 02:13 function must exist inside a formula.
• 02:15 Then, what we're gonna do is we're gonna start typing the function name
• 02:18 that we want.
• 02:18 So, I'm gonna type in an S and you'll see that excel comes along and it actually
• 02:23 gives us a list of all of the functions that it believes start with an S and
• 02:27 are ready to go.
• 02:28 And if I type a U now, it'll start nailing down this list and making it smaller.
• 02:33 I could use my arrow keys to arrow down to the one I want.
• 02:37 Or I could keep typing it and I can press tab once I found the one that I want.
• 02:42 You'll notice that as soon as I press tab it puts the opening,
• 02:45 closing parenthesis around and now its got the IntelliSense showing up underneath.
• 02:49 It says this one's bold.
• 02:51 You are currently working with number one.
• 02:53 And it is required.
• 02:54 I can see that number two and so forth are not required.
• 02:58 So, it says what numbers would you like?
• 02:59 Well, what I'm gonna do is I'm gonna grab the mouse and
• 03:01 I'm gonna just select cells B5 and 6.
• 03:04 And you'll notice that it gives me B5 colon B6.
• 03:08 Now, the colon is Excel's shorthand to say from here to here.
• 03:13 So, I like to remember this as B5 up to, there's an up two dots, up to B6.
• 03:18 And it's gonna sum everything in between.
• 03:21 Now, at this point, it's got a closing parenthesis on it.
• 03:25 I can hit Enter, and you'll see that the formula works quite nicely.
• 03:29 Of course, I could type this all manually if I wanted.
• 03:33 I don't need to use the mouse.
• 03:34 I could type equals SUM, open parenthesis.
• 03:38 I could then arrow up to C5.
• 03:41 I could type, or hold down my shift key.
• 03:44 Actually, let's do that first and arrow down.
• 03:46 And that automatically puts in the colon and gives me the C6.
• 03:50 I'll close my parenthesis and
• 03:52 hit enter and you'll see that that formula works as well.
• 03:56 What if I wanted to sum across?
• 03:59 Well, I could say equals SUM, open parentheses.
• 04:01 Lets go over to this guy here and I'm gonna type in a colon now.
• 04:07 That takes me back to cell D5.
• 04:09 So, now I'm gonna arrow one to the left, close my parenthesis and hit Enter.
• 04:16 So you see that there's many, many ways to do this.
• 04:19 Of course, now that I have this,
• 04:21 you already know that we can fill the formula down as well.
• 04:24 So that's gonna work nicely.
• 04:26 I could also fill this formula across if I wanted to do that.
• 04:30 And you'll see that because it's a relative formula,
• 04:33 it's actually picking up quite nicely to sum D5 to D6.
• 04:37 What's interesting here is because these are relative formulas, I can now go and
• 04:41 take this information.
• 04:43 I can right click and copy it.
• 04:46 And I can paste it over the total cost of sales line.
• 04:50 The relative formulas using the sum function you'll notice that I've
• 04:53 selected here, and it's summing up what I have above, which is great.
• 04:58 I could also go and copy these two guys here.
• 05:01 Well, let's use Command, C to copy them this time.
• 05:04 I'll select here, Command, V will paste both of those cells in as well, and
• 05:08 that looks great.
• 05:09 Now, here's the next challenge, though.
• 05:12 What if I want to go in and
• 05:14 add up sums of numbers that are not in a contiguous block?
• 05:18 They're separated.
• 05:19 Because right now I'd like to sum this number positive but
• 05:22 I'd like to subtract this number from it.
• 05:24 But I'd like to use the sum function.
• 05:26 Can I do that?
• 05:26 Well, I absolutely can.
• 05:28 We can type in equals SUM, open parenthesis.
• 05:31 And it says what's number one?
• 05:32 And I say, well I want to use B7 as number one.
• 05:38 To move to the next parameter, what we're gonna do is we're gonna hit comma.
• 05:42 And you'll notice now that number two, even though it's in square brackets, is
• 05:45 now lit up in the IntelliSense in bold and it says all right this is now required.
• 05:49 Because you've put a comma in here, you have to have this.
• 05:52 And it's also providing me with an additional Number3 dot, dot, dot.
• 05:55 So if I were to hit the comma again,
• 05:57 this would now be, Sum Number1, Number2, Number3, Number 4 dot, dot, dot.
• 06:02 So the dot, dot, dot tells us we can do this ad infinitum.
• 06:06 So, the next number I wanna add though is negative so I put in my minus sign, B12.
• 06:14 So this will look at B7 and say, all right, no problem.
• 06:18 And give me negative of B12 and then it'll sum them together.
• 06:21 If I close the parentheses on there and
• 06:24 say enter, you'll notice that it indeed does exactly that.
• 06:27 And again, of course, we could go and copy this across to the right and
• 06:31 everything will work nicely.
• 06:35 You'll notice there's also another practice page in here where you can go and
• 06:38 practice building some different pieces here to use the sum function and whatnot.
• 06:41 And there's a solution on the solutions page.
• 06:44 I also wanna give you just a little bit of a heads up about the test that's
• 06:47 coming up.
• 06:48 You will be tested on different functions that you haven't seen yet.
• 06:51 And this is intentional because the big key piece that we wanna make sure we
• 06:54 understand here is what the IntelliSense means and what the parameters mean when
• 06:59 you're working with different functions that you may not have experienced before.
• 07:03 So just be ready for that as you go through.
• 07:05 All the concepts that we've looked at here are equally applicable but
• 07:08 they are gonna be different functions to really test that knowledge

Lesson notes are only available for subscribers.