Hello again and welcome back to our course on Excel 2013 Advanced.
In this section I’m going to start to look at the statistical functions in Excel 2013.
I’m not going to assume that you’re a statistician and have a deep and broad knowledge
of statistics, but most people have enough exposure to statistics in everyday life nowadays
to understand the kinds of terms and terminology that I’m going to use.
Microsoft Excel has been used in statistics and by statisticians for quite a few years
now and it’s actually a pretty strong tool.
As we’ll see during this section and later on in the course, not only can we do some
very useful numeric work but we can also use some of the charting and graphing features
of Excel 2013 to look at statistical questions and statistical issues.
There are some specialist pieces of software available to do a lot of these sort of statistical
number crunching that you might need but the advantage of Excel, of course, is that it
does many other things as well and many more people have copies of Microsoft Excel.
So if you’ve got a particular statistical problem to solve or a piece of work to do,
the chances are that Excel can help you to solve that problem or do that job for you;
if it can’t then you probably will need to look at one of the more specialized products.
Now for the purposes of this course I’ve divided the application of the statistical
functions in Excel 2013 into three groups, and those three groups are: description, prediction,
and inference.
The first one we’re going to look at is description and by that I mean we take some
statistics, in our case, a set of numbers and we look at ways of describing that set
of numbers.
So we’re really just looking at fact finding.
We’re saying what are the features of this set of numbers that will help us, for instance,
understand what the numbers tell us or maybe even compare them with other similar or related
sets of numbers?
I’m then going to look at prediction and in particular at regression and how we can
use some existing measurements to predict what might happen in the future.
The third category in relation to using Excel 2013 in statistics is inference.
By inference what we mean is the ability to infer something about a population by measuring
a sample from that population.
So typically we’re talking about things like doing opinion polls, maybe taking 10
or 100 or 1,000 people, getting their opinion, measuring some feature, and from that measurement
inferring something about the population.
Now this is an area that requires quite a bit more statistical knowledge so I’m not
going to go into it in great detail but I’ll look at one or two examples of how Excel can
help with inferential statistics.
Now I’m looking at the list of statistical functions in Excel Help here and one of the
things you’ll notice straightaway is that it is a very, very long list of function.
It’s one of the areas of Excel where the most functions are available.
And if you’re not a statistician, many of those won’t really mean a lot to you.
I’m not going to worry too much about those very detailed statistical functions, the ones
that involve quite a lot of technical knowledge.
I’m going to look at the more straightforward ones in this section.
But I would like to point something out.
If you’ve used an earlier version of Excel and if you’ve used statistical functions
before, many of those functions have been superseded in Excel 2010 and Excel 2013 with
functions of a similar name but with extensions.
For instance, the quartile functions that I’ll look at a little bit later; QUARTILE.EXC
and QUARTILE.INC, for example.
Many functions have extensions that may surprise you if you’ve used these before.
I’m going to explain some of these in this section and the next section but just be aware
that if you’ve used Excel functions before, don’t assume that things are exactly the
same now because quite a few things have changed for reasons that as I say I’ll explain shortly.
So let me introduce you to the data that we’re going to use in this section.
I have here a section of some details of sales of policies for an insurance company.
They have a number of branch offices around the United States and one of their offices
is in Orlando.
In columns B and C here, you can see over a period from the beginning of 2009 to the
end of 2012 the number of inquiries they had from clients and the number of policies that
they sold.
Now typically when you have this sort of set of numbers like the policy sales here, you
have a mass of numbers just a sequence and you might be able to see a pattern.
But what you often need are some descriptive statistics.
Things like what’s the average number of policies that were sold?
And the sort of way that you could use these would be maybe to look at another branch of
the insurance company and compare the average number of policies sold there as well.
But in the first instance, what we’re going to look at is typically some of the descriptive
statistics that we can produce from a set of numbers like these.
I’ve got inquiry statistics and policy statistics.
I’m just going to look at the policy statistics here.
Now, first of all, if I’m going to calculate a number of statistics for the policy sales
over that four year period, I’m going to be referring to this range of numbers from
C4 down to C51; quite a lot.
So what I’m going to do is define a name for that range of numbers.
So having selected, right click, define name.
I’m going to call it Policy Sales Orlando.
Click on OK and now whenever I want to refer to that range, I can use that name.
So let’s start with one or two really straightforward statistics.
One of the very straightforward statistics would be what is the minimum value in that
range?
What’s the lowest number of policies that were sold?
Now the function we use for that is the min function.
So let’s just put that in.
And all we need for the min function as an argument is the range of numbers to look at.
Well, of course, we’ve got a name and there it is.
As soon as I see that appear, Policy Sales Orlando, tick, and I see that in any one month,
the smallest number of sales is 57.
Similarly for the maximum, I’m sure you can work this out.
Now in a very simplistic way, you could compare the sales at two offices by looking at what
the minimum number and the maximum number of sales are.
Minimum and maximum values can be very useful as descriptive statistics but generally speaking
we want to look at two types of statistics which are of particular interest.
One of them is the average and one is the measure of spread.
Now there are various types of average.
Probably the best known one is the arithmetic mean which I’ll look at in just a moment.
But other types of average include the median which, if you put all of a set of values into
sequence, is the middle one or if there are an even number of values, the average of the
two middle values.
And sometimes we’re also interested in the mode.
The mode is the value that occurs most frequently.
Median and mode functions are available in Excel 2013.
I’ll leave you to check those out using the Help.
But let’s look at the arithmetic average, the mean.
So I’m going to put in here Mean and the average function is Equals average, and for
that all I need to do is to put in that range again.
So I’ve got my name setup and I find that for this particular branch the average number
of sales per month over that period is 114.
Now very often in statistical analysis the mean is the value that you’ll be using but
let’s look at one of two additional things about mean.
One of the things that you can do which is very useful statistically is to use one of
the more advanced average functions to be more specific and selective about the mean.
Supposing I wanted to know the mean number of sales in 2012, now I could of course define
a different range and look at the mean for that but I could also use one of the average
if functions.
Now the function we’re going to look at here is the average if function and the principle
of the average if function is we’re going to average a range of numbers but we’re
going to average that range subject to a criterion.
Now we define the criterion using a range which might be the actual range.
If you look at the syntax of the function here, average if, we can say subject to a
criterion apply to a range, get the average of this range.
Now the range that we apply the criterion to may be the same range that we’re finding
the average of or it may be a different one.
I’ll explain that more carefully in a moment.
There is also a function average if S, so it’s the same name but with an S on the
end where you can define multiple criteria.
In this case, we’re just going to have one criterion and the criterion we’re going
to use is that the year in question is 2012.
So let’s see how we put this together.
Note that the month identifiers are in column A. So A4 to A51 give us the months and, of
course, our data are in column C and we have the whole range defined by our name.
This is how I structure this average if; Equals average if.
Now then what range am I checking?
Now the range I’m checking in this case is A4 to A51.
I need to put that in as a range.
Next what am I checking?
Well, if I’m looking for months in the year 2012, what I’m actually going to put as
my criterion is Equals star 12 and basically what that means is the contents of a cell
in the range A4 to A51 is something with 12 at the end.
So that means it’s one of the months in 2012.
Now what range am I averaging?
Well, the range I’m actually averaging is the range in column C which as I know I’ve
got setup with a name, so that’s Policy Sales Orlando.
And that’s basically the structure, the syntax for this average if.
Now note if the criterion actually applied to the contents of C, so for instance supposing
instead of looking for months in 2012 I was looking for the average where the number of
sales was say less than 100, then the criterion would apply to column C and I will have column
C both here for the criterion range and for the range to average.
So I could use Policy Sales Orlando in both places.
If that were the case, I wouldn’t actually need to include the third argument because
by default it’s the same as the first argument.
But in this case, the criterion applies to column A so I need to specify the range in
A, A4 to A51.
I want months that end in 1-2 and let’s see what I get as my average if I check there,
and the value is 81.
So for months in 2012 the average number of sales is 81.
So that’s our first look at averages.
In the next section I’m going to take a look at percentiles and then we’re going
to look at measures of spread.
So please join me for that.