🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Function Anatomy

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Use to understand the anatomy of Excel functions and what their components mean.

Exercise files

Download this lesson’s related exercise files.

Function Anatomy - Begin.xlsx
23.8 KB
Function Anatomy - Complete.xlsx
24 KB

Quick reference

Function Anatomy

Understanding Excel Function Anatomy.

When to use

Use to understand the anatomy of Excel functions, and what their components mean.

Instructions

What is a Function?

  • A pre-packaged algorithm that accepts parameters to return a result.

Key points to remember

  • Functions must be used inside formulas.
  • The function name is always followed by ( )
  • The parameters for the current function are shown in Excel’s “Intellisense”.
  • The current parameter is always listed in bold.
  • Optional parameters are surrounded by [ ]
  • Parameters not enclosed in [ ] are required.
  • Ranges can be used as parameters.
  • Other functions can be used as parameters.
Login to download
  • 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.

Cell Referencing - Example
09m:45s
Math Functions
06m:42s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement