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:17 What a function is is it's a pre-packaged 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 the function, it has to be inside a formula.
• 00:34 And we can use multiple functions or only one function or
• 00:37 none at all inside a formula to get the results that we need.
• 00:41 Every function has some key parts to it.
• 00:44 It has a function name, it will 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 pops up
• 00:54 inside of Excel.
• 00:55 You'll notice that the function name is always followed by an open bracket and
• 00:59 then the list of any parameters followed by a closing bracket.
• 01:03 The current parameter is always listed in bold, so
• 01:06 when you look at the sum function here, you can assume that we actually snapped
• 01:11 a picture of this as we were working on number1, which is the first parameter.
• 01:16 Optional parameters are surrounded by these square brackets.
• 01:19 So in this case, number2 and anything after it, the comma dot, dot, dot,
• 01:24 allows us to know that we do not 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, as is the case of number one here: is not optional, it's required.
• 01:40 Something that's also important to understand about functions is that we can
• 01:43 use ranges as these parameters.
• 01:45 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 use
• 01:54 a function in order to build a good formula to work with.
• 01:59 So for this example, we have a little income statement for
• 02:02 a shoe store here, with has a couple of values in it that I want to summarize.
• 02:06 Now, I could use just hard coded numbers but
• 02:10 I don't wanna do that because if somebody changes the January, February numbers,
• 02:13 I want it to update and flow all the way through the statement.
• 02:16 I could also say =B7 + C7, but if someone inserted a new column between January and
• 02:23 February and then renames the column, it wouldn't pick that data up.
• 02:26 So the best way to do this is to actually leverage the sum function in a formula.
• 02:31 And to do that, I'm gonna start by typing =.
• 02:34 And, I'm gonna start typing the function name, su, and
• 02:37 you will notice that the IntelliSense list shortens down to all of
• 02:41 the functions that actually start with su.
• 02:43 So we've got substitute, we've got subtotal.
• 02:46 I'm now gonna go and I'm gonna type in the m.
• 02:48 And that will get me to sum.
• 02:50 And at this point that's the function that I want, and
• 02:53 I have a way I can actually accept this I could double-click on it.
• 02:56 But the way I always do this is by pressing the Tab key.
• 02:59 And what you'll watch is that as soon I do this two things will happen.
• 03:03 Number one, it will convert it to upper case and
• 03:05 number two it puts in the opening parenthesis that's needed.
• 03:09 I always go and lock in to every function by pressing the Tab key.
• 03:13 I'll type as little as I possibly can to make that happen.
• 03:17 And now to sum the date up, we'll just select January and
• 03:21 February so we get B7 up to C7.
• 03:23 Close the parenthesis, and we'll hit Enter, and I get $2500.
• 03:29 Now, I can obviously copy this guy here.
• 03:32 And I've got a little bit of formatting on this one.
• 03:34 I'm gonna select these two cells here and I'm gonna right-click.
• 03:37 And I'm gonna choose to paste formulas.
• 03:40 And that way, it doesn't change the borders on these particular cells.
• 03:44 Now let's do another one.
• 03:45 We'll go up and this time again, I'm gonna go =su.
• 03:49 And I'll show you that if I got a lot of functions in list,
• 03:52 I can use my drop down arrow key to arrow down a couple of times to sum.
• 03:56 And now I can Tab the sum as well.
• 03:59 And what's interesting here is I don't even need to reach for the mouse.
• 04:02 I can arrow up and then I can hold down my Shift key and arrow down one,
• 04:08 close the parentheses, and hit Enter and my sum is now working there as well.
• 04:12 So I can do everything keyboard driven.
• 04:15 I can then copy this guy across as well.
• 04:18 Even better, I can grab these cells.
• 04:21 Control + C, Control + V, and
• 04:24 I can grab these cells here if I want to and pace them as well.
• 04:29 And you'll notice that because everything is relative, it's working quite nicely.
• 04:35 Now there's another way that we can use the sum function as well.
• 04:38 So I'm gonna do this for gross profit.
• 04:39 We're gonna say =su, arrow down a couple times, m.
• 04:43 And you'll notice that right now I'm on number one.
• 04:46 So I could grab this one and then I could say, and I could grab number two.
• 04:52 And I could put these two things in individually so
• 04:54 I can use different ranges individual or multiple here and I can Enter, of course,
• 04:59 that's not the right calculation.
• 05:01 I actually need to go back and say let's go and subtract the cost in
• 05:08 other to get the proper gross profit, at which point I can now fill this across.
• 05:13 Working with the sum function this way is similar to saying this plus this,
• 05:18 it's just a little bit different style.
• 05:20 And remember, you've got lots of functions here that you can work with, sums, and
• 05:23 averages, and counts.
• 05:24 They're all built in exactly the same fashion but
• 05:28 return the results as named by the specific function.

Lesson notes are only available for subscribers.