Hello again and welcome back to our course on Excel 2013 Advanced.
In this section we’re going to look at some of the more advanced aspects of functions
in Excel 2013.
You should already be familiar with the Formulas tab so we’re going to start with that.
Now when you’re using the Formulas tab to insert a function, one of the problems you’ve
probably come across in the past is finding the function that you want.
Even if you know the name of the function, you won’t necessarily know which category
it’s in.
There are various things that can help you with this and one of the things we’re going
to look at in a moment is the very first button on the left here, the Insert function button.
But let’s look at the function library first and we have all these categories.
I’m sure you’ve used many of these in the past.
The last option, More functions, gives you access to additional categories: statistical,
engineering, cube, and so on.
Now whichever category you’re interested in and indeed whichever function you’re
interested in, if you hover over the function name you get a description of what that function
does as well as a syntactical definition including a list of the arguments of the function.
So that’s one thing that can help you to find the function that you want.
With each of these categories, with all of the buttons here right at the bottom, you
have Insert function which gives you access to the Insert Function dialog and, as I said
just now, that’s the one I want to look at in detail first.
Now one useful thing when you’re using the Insert Function dialog is that you effectively
have two lots of Help.
The question mark denoting the conventional Help in the Office 2013 applications, if you
click on that, the Help that you get relates to how to use this dialogue.
In fact, slightly more generally how to create a formula that uses or includes a function.
So that’s the general Help on working with formulas and functions.
Whereas at the bottom left of the dialog given a selected function, let’s suppose I select
this one Cube set, Help on this function gives me access to the Help specifically on the
selected function.
So if you’re not sure about a particular function, what it does and how to use it,
you’ve got direct access to the relevant Help there.
Now in terms of finding a specific function, if you know the name and you don’t really
want to go around the function library on the Formulas tab, you can select the category
here and look for that particular function within any one of the categories.
If you choose a particular category and you want to know what a particular function does,
click on the function, you get a brief description here, and as I mentioned just now you also
have access through to the Help on that function.
So that’s one way of quickly going through the lists of available functions and finding
the one that you want.
If you’re quite a bit unsure about the name of the function that you want or if you don’t
even know whether such a function exists, you can use the Search facility within the
Insert Function dialog.
This is a fairly intelligent search facility.
It uses a certain amount of what might loosely be called fuzzy logic.
So if I say wanted to find the median of some numbers, I could click in this box, type in
the word Median, and then hit the Go button and it will find the median function for me.
Now that is the only specific median function in Excel 2013 but a median is a statistical
average and there are various other statistical averages such as mean and mode.
And the Insert Function dialog and the search within it are intelligent enough to offer
me some of these alternatives.
Now these aren’t medians, they’re arithmetic means and modes but it may well be in some
situations that the term I’ve used to do the search is just something about what I’m
looking for and it may be that one of these other functions is actually what I need.
Now if you put in a term that it can’t exactly match but it’s got some idea of what it’s
about, so let me put in the term Capital, as in financial capital.
Let’s see if it can find any financial functions related to capital.
Click on Go and it’s not really quite happy.
It says, Please rephrase your question.
But it does find some functions that are related to capital in some way.
So there’s the PMT, the payment function.
There’s the CUMIPMT, the cumulative interest paid function.
So it knows that capital is potentially to do with finance but it doesn’t have a specific
function that calculates capital or indeed uses capital specifically as one of the arguments.
So if you’re a little bit uncertain, it’s always worth trying the search function because
I find it quite often comes up with the answer.
So you have there a few way of finding the function that you need.
Another way is to use the AutoComplete facility.
For this, first of all, go into File Options, go to the Formulas Page, and make sure that
this option is checked, Formula AutoComplete.
Show a list of relevant functions and defined names when building cell formulas.
This functionality can be enabled or disabled by using the keyboard shortcut Alt plus down
arrow.
So click on Formula AutoComplete to enable it, click on OK, and now what I’m going
to do with this selected empty cell is click in the entry bar, Equals, and start typing.
So A, and what it comes up with is a list of the functions that begin with the letter
A. Now notice that depending on which one is selected by default, in this case ABS,
a little tool tip appears on the right.
Returns the absolute value of a number; a number without its sign and you can actually
move that tool tip.
If you hover over it, it becomes a crosshair cursor.
You can put that tool tip somewhere else.
One of the reasons for being able to do that is that when you’re working in a particular
part of a worksheet, you may well find that that tool tip is in the way.
Well, it’s as easy as that to move it.
And, of course, if I type a second letter, third letter, and so on it’s going to narrow
down the choice of available functions.
So say I type an M next.
There are only two functions that begin with A-M and again I’ve got tool tips that I
can move.
So that can be a very helpful way.
If you’ve got a reasonable idea of what a function name is a combination of that,
and using the tool tip, you can normally find the one that you want quite quickly.
Now once you’ve chosen the function, let’s suppose in this case that we’re going to
choose the first function there, not only do we get the function with the open parentheses
but we get an improved tool tip which includes both the function name and the list of arguments.
And in fact if you hover over the function name in that new tool tip, you notice it becomes
underlined and that’s a hyperlink through to the relevant Help page.
Now as I mentioned earlier on, any arguments that are in square brackets such as Basis
here are optional arguments and unless you can remember exactly what the options are
in a particular case, it’s useful to be able to just flick through using this link
here to the Help.
So in the case of the function we’re looking at here, the AMORDEGRC function, if you look
at the optional argument there which was the last one, the basis argument, Basis optional,
the year basis to be used zero or omitted.
So that is that if we don’t put any value in there at all, then the basis is 360 days
using the NESD method.
I won’t trouble you with what that means now.
One means the actual year basis, three is 365 days in a year, and four is 360 days in
a year, the European method, and I’ll leave you to look up exactly what each of those
mean.
But particularly if you’re using a function you’re not familiar with, you may well need
to just flip into the Help there for any optional arguments to check what the options are or
indeed what happens if you omit the argument.
So that’s a pretty useful way of finding out about optional arguments or indeed any
arguments using the Help facility, but there’s another really great way of doing this in
Excel.
Let’s go for that same function again.
So I’m going to just select that function but instead of clicking on the Help, I’m
going to press Control and A and what I get is the Function Arguments dialog which lets
me enter a value for each of the arguments but then will also give me an explanation
of what each of the arguments means.
If like me you have trouble remembering those keyboard shortcuts, if you’ve identified
a function such as this one, selected it, and just got as far as the function and the
first parentheses, there’s an Effects button here on the left and if you click on that,
that also gives you the Function Arguments dialog.
Now the Function Arguments dialog is a non-modal dialog so you can keep it open while you work
on the function.
And not only can you type in values such as cost, date, purchase, but you can also refer
to cells or ranges within your workbook.
So for instance, if I wanted to pick up the cost from cell A4, I realize that A4 here
is empty.
But if I wanted to pick up it from A4, if I just click in A4, then the cell reference
there is copied straight into the cost field in the Function Arguments dialog.
We’re going to be using the Function Arguments dialog a little bit later on when we start
working through the case studies, so I’m not going to dwell on it here.
But hopefully you can get a good idea of how to use the Function Arguments dialog already;
as I say, more on that later.
So having looked at some of the more advanced facilities available, generally for functions
we’re nearly ready to start using them in some specific case studies but before we do
I’d like to look at a particular range of functions that have a special status within
Excel really, and that’s the AutoSum functions.
They’ve even got their own button here in the function library.
So in the next section we’re going to take a quick look at some special things to do
with AutoSum and then after that we’ll start working on our examples and case studies.
So please join me in the next section.