Function Anatomy

Quick reference

• 00:04 It's now time to learn about Excel functions and Excel functions are amazing.
• 00:08 They're what actually truly unlocked the real power of Excel.
• 00:12 So what are they?
• 00:14 Well, essentially, they're a prepackaged algorithm that's been written
• 00:19 by someone and they take zero or more parameters and return a result, okay?
• 00:24 Things that we pass into these functions are called parameters.
• 00:28 Functions are often confused with the term formula.
• 00:32 And they're actually two completely separate things.
• 00:36 The reason being is every function must live inside an Excel formula.
• 00:41 But not every Excel formula needs a function.
• 00:45 You can write a simple formula equals 1 plus 5 that uses no functions whatsoever.
• 00:49 But you cannot use a function unless it is inside a formula.
• 00:54 Now most people use these two terms interchangeably.
• 00:57 And honestly, I probably will as we go through this course.
• 01:00 But from a completely technical standpoint,
• 01:03 it is important to realize that these are actually two different things.
• 01:06 Let's look at an example of a very commonly used function, the sum function.
• 01:11 Because this will actually show us what pieces you might see inside your
• 01:15 IntelliSense when you're actually working with one.
• 01:18 Now the first thing you notice that every function starts with a function name.
• 01:22 And then immediately after the function name, there will be an opening and
• 01:27 a closing parenthesis.
• 01:28 There may or may not be a list of parameters inside those closing
• 01:32 parentheses, depending on what function you're using.
• 01:36 In this case, we have a few different parameters inside.
• 01:40 The first one, called number1 is required.
• 01:44 How do we know that?
• 01:45 It's because it doesn't have any braces surrounding it.
• 01:49 In addition, the number1 parameter here is actually listed in bold.
• 01:55 And what that means is if I was actually writing this inside a formula,
• 01:58 this would be the one that I'm actually working on.
• 02:01 Whatever the currently active parameter is that I'm feeding into Excel gets
• 02:06 highlighted in bold.
• 02:07 If there are optional parameters for the function,
• 02:10 they will be surrounded in square braces.
• 02:13 And if there are unlimited number of optional parameters,
• 02:16 you will also see the...at the end.
• 02:18 That says, hey, if you give me a parameter for number two,
• 02:22 I'll give you an optional parameter for number three and so on and so on.
• 02:26 The other thing that's important to realize about this is that you should not
• 02:30 be fooled by the wording number.
• 02:32 Because anywhere that you can put in a value,
• 02:35 Excel will pretty much accept a range in place of that as well.
• 02:38 So that's a really handy thing here that makes our functions extremely dynamic.
• 02:43 Let's hop over to Excel and see exactly how this particular function works and
• 02:47 how it manifests in the user interface when we're working with it.
• 02:51 Before I write any functions myself, what I'm going to do is I'm actually going to
• 02:56 use the AutoSum to generate a basic sum formula syntax.
• 02:59 So right here in cell B9, I've got a couple of values above.
• 03:02 I'm going to go over to the AutoSum and just click the button.
• 03:05 And what I want you to recognize here is that we've got the equals that indicates
• 03:09 there's a formula.
• 03:10 Sum, that is the name of the function.
• 03:12 We open the parenthesis and then for number1, what we have is we've got B7:B8.
• 03:19 That colon indicates that we want the contiguous range from the two
• 03:23 references that we have here.
• 03:26 So when I hit Enter, what that means is we now have
• 03:29 a formula that grabs the contiguous range from B7 to B8.
• 03:33 Knowing what that colon does, I can now go and write my own formula.
• 03:40 So I can say =, to start by formula, I'll say su and you'll notice that
• 03:45 we get some IntelliSense right away here with a different formula names.
• 03:50 Now I could arrow down to select the sum function here.
• 03:53 Once I've got it and I'm happy, I can press Tab.
• 03:56 And I always like to do that for a couple of reasons.
• 03:58 Number one, it converts the formula to uppercase,
• 04:01 which is the way that it should be here.
• 04:02 It also puts the opening parenthesis on it for it automatically.
• 04:06 And best part, it saves me typing.
• 04:09 Now you'll notice that we have a required parameter here for number1.
• 04:14 So what I'm going to do is I'm just going to grab my mouse right now, and
• 04:18 I'm going to highlight January and February and it gives me B7 to C7.
• 04:23 I then have a comma and we get number2,...
• 04:26 But these are optional, so I don't really need to use them so
• 04:30 I'm just going to close the parenthesis and hit Enter, and I get 2500.
• 04:34 And I think, if you go and add those two values together,
• 04:37 you'll find that it does indeed equal 2500.
• 04:40 Now I can copy this formula down here.
• 04:43 I can go and copy this formula across here and
• 04:46 we should have some nicely cross total data at this point.
• 04:50 I'm going to do the same thing here.
• 04:51 I'm just going to go and copy this one and we're going to paste it down here and
• 04:55 I'm going to write my own sum function for these guys.
• 04:58 So we're going to say equals sum.
• 05:00 Oops, I typed a little bit weird, but that's okay.
• 05:02 I'm going to press tab and that corrects the typing.
• 05:05 And then we'll go and grab this range B12 to B13,
• 05:09 close the parenthesis and hit enter.
• 05:11 There we are.
• 05:13 I'm now going to go and say let's go and put this across all these cells.
• 05:16 So I'll hit F2 and get back to this.
• 05:19 I've selected the entire block of cells.
• 05:21 I'm going to hit Ctrl+Enter to commit it in there as well.
• 05:24 So that's great.
• 05:26 But now I want to show you another way that we can actually use our sum function.
• 05:30 Because what we've done right now is we have some contiguous blocks of ranges.
• 05:33 And this is a less common way of using this function, but sometimes,
• 05:36 it actually works nicely.
• 05:38 So we're going to go with equals SUM Rab, all right.
• 05:43 And it asks for number one.
• 05:46 Well, I want to take my total revenue and my total cost of sales and
• 05:49 for whatever reason here, well, you know what, I want to add,
• 05:52 take the total revenue and subtract the cost of sales.
• 05:55 So what I'm going to do is I'm going to take number1 29094.
• 06:01 Now the sum function doesn't have to work with contiguous ranges.
• 06:04 We've got this comma that takes us into another parameter, so
• 06:07 I'm going to hit comma.
• 06:08 Notice that number2 is now in bold.
• 06:11 That's the one that I'm working on and
• 06:13 it's also added an additional number3 as an optional parameter.
• 06:18 I don't need number3 but for number2,
• 06:21 what I'm going to do is I'm going to type in minus and grab B14.
• 06:26 When I close my parenthesis and I hit Enter, you can now see that I'm summing
• 06:30 two non contiguous ranges where everything else was summing contiguous ranges.
• 06:35 So this actually works out quite nicely.
• 06:38 And I can now go and feed this across.
• 06:40 And my little revenue statement here is actually done nicely.
• 06:43 So I now know that between my year to date, I've earned $3,365 in gross profit.
• 06:49 Because this is doing the exact same thing, summing my 6,014,
• 06:53 subtracting the 2649, okay?
• 06:55 So that's how the IntelliSense works when working with these formulas and functions.
• 07:00 And if you can learn to read that,
• 07:03 then you can learn to use virtually any function in Excel.

Lesson notes are only available for subscribers.