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 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:36 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'll have required parameters, and
• 00:47 potentially, optional parameters, as well.
• 00:50 And this is taken from the IntelliSense help that we see that
• 00:53 pops up inside Excel.
• 00:55 You'll notice that the function name is always followed by an open brackets, and
• 00:59 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 1, which is the first parameter.
• 01:15 Optional parameters are surrounded by these square brackets.
• 01:18 So in this case, number 2 and anything after it.
• 01:21 The comma dot, dot, dot allows it to, or
• 01:24 allows us to know that we don't have to provide a number 2 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, which is the case of number 1 here, is not optional.
• 01:37 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:58 So for this example, we've got some revenue and
• 02:02 some cost of sales for a shoe store here.
• 02:06 And we'd like to go and add up the January revenue for shoes and boots right now.
• 02:11 So what we're gonna do is we're gonna start.
• 02:14 We could just say equals one plus the other, but then there's a challenge.
• 02:17 If we insert a new row, we might not pick that up.
• 02:20 So let's use a SUM function to do this properly.
• 02:23 So we'll start typing =SU, and you'll notice that the list of IntelliSense
• 02:28 here starts to give us an autocomplete for our formulas.
• 02:31 It says, hey, you've typed in SU.
• 02:33 These are all the formulas that actually have an SU to start with.
• 02:37 So we could just use our mouse and click on the SUM function to select it.
• 02:42 It will tell us what it does.
• 02:44 We can double-click it, and
• 02:45 it will actually convert it to uppercase and put in the opening parenthesis for us.
• 02:50 And now we say, all right, well, where's number 1?
• 02:53 We're gonna go and select the range from B5 up to B6, and we can
• 02:58 still see that we have IntelliSense here, which means the function is not complete.
• 03:02 We do need to close our bracket, and then hit enter.
• 03:05 And that will work quite nicely.
• 03:08 Of course, this is a formula that's written
• 03:11 against a relative cell referencing set, so we could right-click and say copy.
• 03:17 We could select these two cells here, right-click and say paste.
• 03:21 And the formula will be written into those cells, referring to the cells above them.
• 03:26 We could also go and write a formula to go horizontally.
• 03:31 So let's type in = SU.
• 03:35 This time, what I'm going to do is I'm gonna hit my down arrow and
• 03:38 select the SUM, and then I'll press tab, and that will also complete it.
• 03:43 And now I could go and say, you know what?
• 03:45 Let's select B5 up to C5.
• 03:49 We'll close our bracket and hit enter, and you'll see it brings me back $2500.
• 03:55 I could, again, right-click, copy, right-click, and paste this in, as well.
• 04:01 Again, these formulas are relative to what we have here, so we could reuse these.
• 04:08 Just right-click and copy, right-click and paste, and there we go.
• 04:14 And you'll notice I copied two but pasted into one cell, but
• 04:17 because I copied two it pastes both of those in there.
• 04:20 So those are now working quite nicely to sum across the range here, as well.
• 04:25 So let's go back, and one more time we'll say =SUM.
• 04:30 I just typed it incompletely this time and opened the bracket.
• 04:32 I'm gonna use my up arrow key, hold down shift, and move my up arrow
• 04:37 key again to select the contiguous range, close my brackets and say enter.
• 04:43 And this time, to copy it across,
• 04:45 I'll just mouse over the bottom right-hand corner, left-click, and
• 04:49 drag the fill handle to get those formulas into the right place.
• 04:52 And you can see that, indeed, they still work.
• 04:55 So different methods for copying, whatever feels more comfortable to you.
• 05:00 Now the last thing we wanna do is we wanna go and add the gross profit to this.
• 05:04 So we can use a SUM function for this, as well,
• 05:07 because we don't actually have to pick up an entire range of cells.
• 05:12 We can click on 1, and then you'll notice that right now number 1 is in bold.
• 05:18 If I click comma and then choose the next one,
• 05:21 number 2 is showing in bold at this point, as soon as I press that comma.
• 05:26 So we'll close the bracket and hit enter.
• 05:28 And this is we realize we've made a mistake, because we've gone and
• 05:32 we've actually added revenue and cost of sales.
• 05:35 These need to be subtracted.
• 05:37 So if I go back to this now and say -B12, you'll notice that we can
• 05:42 actually do math inside our SUM function, subtracting one number from another.
• 05:49 And then, of course, we can copy it across.
• 05:51 And we've got a nicely completed statement that's actually going to update
• 05:56 when somebody updates the values in the January and February columns.

Lesson notes are only available for subscribers.