Function Anatomy

Quick reference

• 00:04 In this video, we're gonna talk about Excel Functions.
• 00:07 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 pre-packaged algorithm.
• 00:20 That allows us to pass certain parameters into it and get a result back out.
• 00:25 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 that we need.
• 00:41 Every function has some key parts to it.
• 00:43 It has a function name.
• 00:45 It'll have required parameters and potentially optional parameters, as well.
• 00:49 And this is taking from the IntelliSense help that we see that pops up
• 00:54 inside 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:02 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:10 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:18 So in this case number two and anything after it, the comma dot, dot, dot.
• 01:23 Allows it to or
• 01:24 allows us to know that we don't have to provide a number two if we don't want to.
• 01:28 But we also know by default,
• 01:30 then that anything that doesn't have square brackets around it.
• 01:34 It's the case of number one here.
• 01:35 It's not optional, it's required.
• 01:39 Something that's also important to understand about functions is that we
• 01:42 can 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:49 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 Okay.
• 02:00 Let's see if we can use the SUM function to put the proper totals into this
• 02:04 particular schedule.
• 02:05 If we were going to avoid the SUM function, we'd basically just say,
• 02:09 equals B5 plus B6 to get our total, but let's try and
• 02:12 actually work this with the function instead.
• 02:15 So in order to use our function the first thing we have to do is we have to put in
• 02:19 the formula, so we must start with equals and the we will type in su.
• 02:23 And you will notice that as we start typing,
• 02:25 Excel starts narrowing down the list of all the functions that exist.
• 02:29 And we could continue typing or we could click on SUM and
• 02:32 hit Tab, that will actually allow us to complete SUM function.
• 02:36 And you'll notice that it's got its open bracket there.
• 02:38 You'll also see that the Intellisense has popped up and
• 02:42 you can also see 1 is in bold.
• 02:44 So we can say, well this is cool.
• 02:45 We could put in individual ones, but what we're gonna do is we gonna grab and
• 02:50 select a range.
• 02:51 So it says, sum B5 up to B6.
• 02:54 We can close the brackets and hit Enter and it will commit that to a cell for
• 03:00 us, which is quite nice.
• 03:01 Now, we could go and we could copy this and
• 03:04 we could paste it into the cell beside it.
• 03:07 And you'll notice that,
• 03:09 because we've used the cell references, it is indeed working as we'd expect.
• 03:15 We can also sum horizontally using the exact same method, methods equal SUM and
• 03:19 I'm gonna type it all this time.
• 03:21 Equal SUM open brackets.
• 03:23 And this time, we'll just drag the mouse across here so we're B5 to C5.
• 03:28 Close our bracket and hit Enter.
• 03:30 We now have a nice SUM function that works horizontally, as well.
• 03:33 We can copy this and then we can paste it in here, as well.
• 03:38 So that's nice.
• 03:40 Now we can also copy this,
• 03:41 because we know that it's pointing to the ranges beside us.
• 03:44 We can copy here and paste it down next to our shoes and
• 03:49 boot costume sale here and if we felt like it.
• 03:53 Again, we could go back and
• 03:54 we could actually write the function to sum the numbers up above for B10, B11.
• 04:00 Close our brackets and hit Enter.
• 04:03 And then again, copy and paste these across.
• 04:07 Now, we can also use the SUM function a slightly different way.
• 04:13 If we wanted to try and work out what our cost of or
• 04:15 the difference is between our revenue and cost of sales,
• 04:18 giving us a gross profit, we could try this again with the SUM function.
• 04:22 We'll say, equals SUM, open brackets.
• 04:24 We'll grab the 2,994, so we can actually separate these things in commas.
• 04:30 And you'll notice that's we're not on optional number two is bold.
• 04:33 So let's grab this guy here and say, close brackets and Enter.
• 04:37 And then we'll look at that and go, whoops.
• 04:39 That didn't work.
• 04:40 We've added those two together.
• 04:41 Not so good.
• 04:42 In order to get our gross profit,
• 04:43 we actually subtract our cost sales from our revenue.
• 04:46 So we'll go back in and enter here and we'll edit this formula and
• 04:50 instead of adding B12, we're just gonna subtract it.
• 04:54 So we can actually do basic mathematics operations within the SUM function as
• 04:58 well, which is kinda handy.
• 05:00 And if we hit Enter, we now have the sum of B7, minus B12.
• 05:06 So it's picking up those individual cells.
• 05:08 I don't have to sum the entire range.
• 05:09 And once again, if I were to copy this, I could move it across and say, paste.
• 05:15 And we now have some pretty nice formulas that are working very well to
• 05:19 fill out our gross profits statement using the SUM function two different ways.
• 05:24 One with individual cells, the other with ranges of cells.

Lesson notes are only available for subscribers.