Functions Overview

Quick reference

• 00:03 Finally, it's time to actually think about the Excel functions we're
• 00:08 going to use regularly when building financial models.
• 00:12 You maybe surprised for
• 00:14 what I'm actually gonna to cover there, I've got a list of 12.
• 00:18 Other this he doesn't try to speak, to get use commonly.
• 00:21 And they're not just complex or as remote as you might think they might be.
• 00:26 And the ones you might already know possibly because you should keep it
• 00:30 simple, don't go for the show of functions.
• 00:33 Use the ones when you can step them out to apply the rule of thumb.
• 00:37 But before I do that,
• 00:38 I'm just going to have look at functions overview to put everything into context.
• 00:46 So first of all, the difference between functions and formulae,
• 00:49 because people use the two terms interchangeably, it's not true.
• 00:53 The function is the building block of a formula.
• 00:55 So that's the thing that gets capitalized and has arguments and does something.
• 00:59 The formula is the thing, everything, that starts with the equals sign.
• 01:03 Sometimes, you put one function inside another, that's called embedding.
• 01:08 You can do this in Excel 2003 and earlier.
• 01:11 You had a limit of seven times you could do this.
• 01:14 But it's up to 63 times in Excel 2007 onwards.
• 01:19 If you do that, do you remember once you finished it,
• 01:23 to find a big wet fish, and slap your face with it as much as you can because really,
• 01:29 you shouldn't be building formulas like that.
• 01:32 Use the Rule of Thumb.
• 01:34 Step it out.
• 01:35 If you do have to have a longer formula, do remember you can use Alt+Enter,
• 01:39 holding the Alt button down to space out a formulae with multiple lines.
• 01:43 Remember, you can't put extra spaces in because that's the intersect
• 01:48 operator to which we showed when we were looking at range names.
• 01:53 So what is a function then?
• 01:55 Well, most contain an equal sign because they're in there for the calculation.
• 02:00 They'll have the actual function name.
• 02:02 They'll always have brackets, because that's what distinguishes
• 02:05 a function from a range name, but they won't necessarily have arguments.
• 02:10 So, we saw when we were looking before,
• 02:12 the rand function that generates a random number between zero and
• 02:16 one required no arguments whatsoever, just an open and close bracket.
• 02:22 Not all the functions are return values.
• 02:24 Some of them will give you true or
• 02:26 false or other things like text if you code them up correctly.
• 02:30 Another to everyday functions you can use in here as well.
• 02:34 Some of these will be going through in the top 12, some of them are won't.
• 02:38 The fact is some of you don't you recognize.
• 02:41 One of the things we find when we're trying to actually build them on list,
• 02:45 we don't know what we don't know.
• 02:48 There are hundreds of Excel functions out there, and
• 02:51 Microsoft actually claims anyone that knows 30 or
• 02:54 more functions well is probably justified in calling themselves an expert in Excel.
• 03:01 But how do you find the function you want?
• 03:02 You can't go to Excel and say, actually can you find me the one that does?
• 03:07 Cuz Excel help is sophisticated, but it's not quite that good, and
• 03:11 this is where this flow chart comes in.
• 03:13 I find this a very useful flow chart.
• 03:15 All you do is you start at the top and
• 03:17 you answer the question, what do you want to do with your Excel formula?
• 03:20 We then split that up into five categories.
• 03:23 Be it calculate, modify, use functions, user range of data, and data time.
• 03:29 I've highlighted calculating in black because they
• 03:32 tend to get used more regularly, but don't read anything in to it other than that.
• 03:37 And you follow the flow chart through.
• 03:39 Those at the top of the table,
• 03:41 they're used all the time, in the middle used regularly, and those at the bottom,
• 03:45 not so much, but you might the function you're looking for.
• 03:48 So once you see one and say, I wonder if they does it,
• 03:51 you can then use Excel Help, to go and hunt it out.
• 03:54 Talking of which, you've got your function tips.
• 03:58 Yes, when you're typing your formula,
• 04:00 press F2 to edit a cell instead of double-clicking on it helps.
• 04:03 It's safe if you stuff it up.
• 04:05 And if you're not sure what the function does, press the fx button right next to
• 04:09 the actual formula bar to learn how an Excel function works.
• 04:13 So as an example, I presume everyone here knows the BAHTTEXT function.
• 04:19 That's the member of the Simpsons family who's always on the mobile phone or is it?
• 04:23 You could actually click on the effects button and will show you the converts of
• 04:28 numbers into text in the language of box and good old tie.
• 04:32 That's really cool you'll be good if those are in english but that does not exists,
• 04:37 never quite understood what that function that is.
• 04:39 But it gives you idea what resources are available to you
• 04:44 when creating formulas using functions that you are unfamiliar with.
• 04:49 So, which of these functions are you or are you not familiar with?
• 04:53 Let's go through the top 12 functions in a subjective list,
• 04:57 I must confess, the things I would use when building a Financial Model.

Lesson notes are only available for subscribers.