Hello again and welcome back to our course on Excel 2016 Advanced.
In this and the next few sections we’re going to look at the use of Statistical functions.
Now Statistical functions are amongst the most numerous in Excel 2016 and many of them
are used in relation to pretty complex statistics.
And because this isn’t a specialized course in statistics I’m really going to look mainly
at the most commonly used and in some senses most basic statistical functions.
But if you do have an advanced knowledge of statistics and if you need to use statistical
functions you’ll find that the ones that are provided in Excel 2016 are a pretty powerful
set nowadays.
I’m looking at the Excel 2016 Help page here and if I just scroll down the list of
Statistical functions you’ll see just how many of them there are.
As usual the ones that have been introduced in the current version are marked with 2016.
And there’s a batch there related to forecasting.
Now in my coverage of statistical functions I’m really going to divide the functions
and their use into three main groups.
First of all we’re going to look at the descriptive functions.
These are the functions that you can use to describe a set of results, normally one or
more samples of results, and where you’re looking at statistics that in some way typify
those results or represent those results or enable you to compare, for example one sample
with another sample.
The second set of functions are the ones that are used for prediction or forecasting, and
that includes these new functions that you can see here.
And then the third set are the set that are used for statistical inference.
And these are functions where based on a sample you want to infer something about, for example,
the population that the sample comes from.
These are often functions that are used in scientific research, opinion polls and so
on.
Now one point to bear in mind is that as I mentioned just now this is a very long list
of functions.
And a couple of functions here, Quartile.EXE and Quartile.INC.
We saw the use of Quartile.INC earlier in the course.
Don’t forget there is also a function Quartile.
And in fact there are quite a few statistical functions that are still available in Excel
2016 that have been superseded by the ones listed here.
These are kept in Excel 2016 in order to make sure that older workbooks work for people.
But as far as possible you should try to use the ones that are listed in the current Excel
2016 Help on functions.
Try to avoid using the older ones because not only is there a possibility that the older
ones will be taken away at some stage in the future but also the newer ones tend to be
more accurate, where there is any issue of accuracy.
And they tend to be better documented and often are easier to use.
So let’s get started on the descriptive statistics and I’m going to use some data
relating to a branch of an insurance company.
And the data covers a period from January 2012 through to the end of December 2015.
There’s two columns of numbers.
The first column, column B, represents the number of enquiries made at the branch over
that time.
And the second column, Policy, shows the number of policies issued in each month.
Now the data you see here are very typical of a situation where you have a whole mass
of numbers.
And just looking at that very long list of numbers it’s quite hard to deduce anything
from that list of numbers.
You can see that there are generally a lot more enquiries than policies.
You can see that number of enquiries is often in the range of 400, 500, 600 and the policies
issued is usually around about 100, 150, maybe a bit higher sometimes.
But when we’re doing statistical analysis one of the first things we normally do is
to calculate a few standard statistics that enable us to get a good, basic picture.
So let’s look at the typical kind of statistics we might calculate.
I’m going to be using the numbers in columns B and C quite a bit so I’m going to define
named ranges for Enquiries and Policies.
And having set up those names let’s calculate a few straightforward statistics.
We might, for instance, look at the minimum number.
Function we’ve used before.
Now when we use the MIN function note the list of arguments there.
I’ve set up a named range now and I know that it begins with the word Branch.
So if I just start typing what happens of course is the IntelliSense will eventually
show me my options.
Branch Enquiries, Branch Policies.
So it’s Branch Enquiries that I want, double click, close the brackets, tick to enter.
And the lowest value in column B is 418.
And of course it’s a very straightforward job to find the minimum number of policies
and the maximum for each as well.
So I’ll do that next.
Now of course we could do a comparison between the figures for say a couple of branches based
on these minimum and maximum numbers.
But more normally we do a comparison based on average measures and measures of spread.
Now the three main average measures are the arithmetic mean, the median, and the mode.
So I’m now going to calculate the mean number of enquiries and the mean number of policies.
So mean number of enquiries.
The function we use there is the Average function.
And average is 557.646.
Let’s format that as the nearest whole number.
Now the mean number of policies.
And again I’ve reduced that to zero decimal places.
Now the next two main average measures are the median and the mode.
And just in case you’re not familiar with what a median is and what a mode is.
Mode is the easiest one.
The mode is the value that occurs most frequently.
So if the number of enquiries per month varies quite a bit as it does here, if there’s
a particular number of enquiries that occurs most often, say 612 or something like that
that will be the modal value.
In the case of the median, if you imagine putting the numbers of enquiries into numerical
order, ignore the month, just stretch them all out in numerical order smallest to largest
the median is the middle one.
It’s actually the second quartile, as I mentioned earlier in the course.
And if you have an even number of values it’s the average of the middle two.
Now I’m not going to go through the process here of identifying those functions.
That’s a little extra exercise for you to do.
So you need to look up median and mode as functions and you need to work out what those
functions are and then you can check your answer by looking at the Policy_Sales_1 worksheet.
So there I’ve entered the relevant text and formulae for median and mode.
If you tried mode yourself you may be somewhat confused by the fact that there are three
mode functions.
There’s the old mode function and the you also have a pair of newer functions, Mode.SNGL,
that’s single mode, and Mode.MULT which is for multiple modes.
Because a sample of data can have more than one mode.
There can be more than one value which occurs at the highest frequency.
And in fact for both enquiries and policies here they are multi-modal.
Now let’s look at the number of policies.
What I’ve used here is the function that you may well have used, Mode.SNGL, Mode.Single.
But in fact for the number of policies there is more than one modal value.
And in order to identify these multiple modal values you need to set up the formula that’s
currently in F17 as an array formula.
Now I’m going to assume for the moment that you’ve used array formulas before but I
am going to run through this example fairly quickly just in case you’re a little bit
rusty on it.
So I’m going to clear the formula in F17 at the moment and I’m going to select say
half a dozen or so cells.
And having made that selection I’m going to enter a formula.
And because I’ve already made a selection which covers more than one cell it will be
an array formula.
So I’m going to put =MODE and it’s the MULT version that I’m going to use.
And the array that I’m going to look in is of course Branch Policies.
Close brackets.
Now what I do is to press Control, Shift and Enter and I’ll see that there are in fact
two values, two modal values.
There’s 99 and there’s 132.
And if you check through the data in column C you’ll see that both of those occur three
times.
Note that having made that initial selection any of the cells in the selection, F17 to
F24 that are not needed, that don’t contain one of the multiple modes just have an N/A
in it instead.
And of course we could apply the same principle to the modal number of enquiries.
Let me put that over here.
Let me just make that selection, put the relevant formula in.
And in this case we’ll see that there are actually four modal values, 618, 553, 597,
and 454.
They are all modal values.
They’re values that occur with the highest frequency.
So having looked at some of the most important functions for calculating averages in Excel
2016 the Average function in particular, the one that calculates the mean has a couple
of variants that can be very useful.
And one of them is Average If whereby you can apply one or more conditions and find
the average of a set of numbers subject to a set of conditions.
Now the enquiry and policy figures we have here cover the period January 2012 through
to December 2015.
What if I said what’s the average number of enquiries in the second half of 2015?
So what I do in this case is I say =AVGERAGEIF.
Now first of all I need to specify the range of values that I’m going to test for a condition.
Now in this case it’s the range of dates so it is A4 through to A51.
Now we specify the criteria.
In this case the single criterion we’re going to apply is that we’re talking about
enquiries during the second half of 2015.
So it’s going to be from July 1st onwards.
So that’s greater than or equal to July the 1st 2015.
Now of course depending on your locale you may need to switch the day and month number
there.
And then finally which are the values we’re going to average?
What will the values that we identify by the branch enquiries name?
And let’s see what we get.
We find that for the second half of 2015 the average number of enquiries per month is just
over 461.
And it’s worth noting that there is another arithmetic mean function, AVERAGEIFS which
enables you to specify multiple criteria.
Just the last thing I’d like to mention here before we move onto measures of spread
in the next section.
If you, for example, take this list of policy sales starting at C4 and select just down
to the bottom here, when you release the mouse don’t forget the Quick Analysis option here.
If you click on Quick Analysis you have of course a good range of options there.
Formatting, Charts, etcetera.
But if you click on Totals, Totals include Average, for example.
So some of the basic statistics that you might be interested in can very quickly calculated
by reference to the Quick Analysis facility there.
So that’s it on averages for the moment.
In the next section we’re going to start looking at measures of spread.
So please join me for that.