(upbeat music)
- Hello, I'm professor Rick Jerz,
and I welcome you to this session titled
Descriptive Statistics Using Excel.
In our last session,
we learned about describing data numerically.
Yep, a lot of equations.
I am much more excited about this session.
We are going to learn how to use Microsoft Excel
to do all these calculations.
What excites me the most is that I'm going to show you
that we can do all these calculations
with Excel's basic functions.
I call this the function approach to using Excel.
We are not going to use any special Add-ins.
I think you're going to see how Excel
can make doing these calculations pretty straightforward,
let's get started.
Our goal for this session
is to calculate descriptive statistics
using Microsoft Excel.
We want to be able to calculate the mean, median, mode,
range, mean deviation and the variance
and standard deviation for both population and sample data.
This is a good point for me to mention
that Excel version 2019
is now very similar for both PCs and Macs.
The top graphic shows Excel 2019 for the PC
and the bottom Excel 2019 for the Mac.
If you own an earlier version of Excel, consider upgrading.
I am running Excel 365,
which is Microsoft subscription service on both my Mac
and my Windows 10 PC emulator.
And little by little,
I am making all my videos with this version.
I most commonly use the Mac version
because it provides better quality videos.
If you ever notice any differences
between what I show you and what you see,
just search the internet
to learn where Microsoft has put a feature.
I'm going to show you three ways
to calculate these statistics in Microsoft Excel.
I will start with the easiest way and end with the hardest.
These three ways are not all equal,
they vary in the amount of statistical information
they provide how interactive they are,
and how easily they can be integrated
into bigger business models.
The first way is the visual status bar method,
the second is the Data Analysis ToolPak method.
And the third is the function method.
I will demonstrate these three techniques
with this descriptive statistics Excel model
that I'm giving to you.
Developing this model has taken some time,
and I couldn't expect you to do the same thing
within one semester,
but I can expect you to master the use of my models.
My models are completely open
so that you can explore exactly what I have done.
The easiest method is the status bar method,
let me show you this technique.
From the data that I have provided,
just click on the first data cell
and while holding your left mouse button down,
drag your cursor to the bottom data cell.
Now look at the lower status bar
and you'll see some summary statistics.
In my example, you might see more than what you see
because I have turned on some additional stats.
You can do this yourself by right clicking on the status bar
and then clicking on the stats of interest.
The status bar technique is sensitive
to the kind of data that you are highlighting,
qualitative versus quantitative.
For example, if we highlight our color data,
you will only see the status bar show count.
The second method I call the Data Analysis ToolPak method.
I show the result of this method for our data here
in my Excel model.
Since Excel 4 released in 1992,
Microsoft has provided this data Analysis ToolPak
as an Add-in to Excel.
And this Add-in and has remained in Excel
for all these years without modification.
I am showing you this Add-in
in my Windows 3.1 PC emulator Excel Version 4
running on my Mac.
Back in the 1980s,
statisticians criticized Excel as not being appropriate
to do statistics.
So Microsoft's response was this Add-in.
I believe that Microsoft chose to keep this utility
as an Add-in because back in the 1980s,
computers had limited memory
and these extra features consumed memory.
So Microsoft left it up to the user
to decide if they should invest in more memory
to run these utilities.
Around 1990, while I was working for John Deere,
we would buy one megabyte memory cards
for around $500 each.
Add-ins typically need to be installed and turned on,
this Data Analysis ToolPak just needs to be turned on.
On the PC, go to Files, Options, Add-ins,
manage Add-ins, Analysis ToolPak, and finally Okay.
On the Mac, go to the Tools menu pick Add-ins
then Analysis ToolPak.
This Add-in is now installed
and shows itself on the data ribbon.
To use the data Analysis ToolPak utility
click on Data Analysis,
scroll through the list of statistical utilities
and pick Descriptive Statistics.
Click in the Input Range Text Box first,
then click and drag through your data.
I find that using this pointing method
is the easiest way to enter the input range cell references.
If when you selected the data,
you included the data label,
check the label in first row box.
We are going to add the results on this same worksheets.
So I click in the text box, Output Range,
then click on My Data,
this will overwrite my data.
I also click on Summary Statistics, then Okay.
This data Analysis ToolPak produces this table, that's it.
You might need to size the column
so that you can see the words and the numbers.
To the right of our Data Analysis ToolPak results,
I show you the results
for a non-Microsoft statistical utility mega stat.
We won't use mega stat in this course,
I show you these results just to show you
that there are companies
that have produced more comprehensive,
statistical Add-ins for Excel.
Using mega stat is very similar
to using the Data Analysis ToolPak.
The last method which I call the function approach
involves using a variety of Excel's functions,
including statistical functions.
This technique works for all versions of Microsoft Excel,
and it is my preferred technique
and the one that we will use throughout this course.
In my Excel model, this function approach is on the left.
There are many statistical functions already built
into Microsoft Excel for doing statistical calculations.
You can find many good references
to Excel statistical functions by searching the internet.
I have used this function approach
to create a general purpose statistics calculator.
The cells that you see highlighted in green
is where you enter raw data.
The red cells is where I use Excel functions
to calculate results.
You should not enter anything into red cells,
otherwise you will be wiping out the calculations.
To the right of these red cells,
I am showing you the Excel function name
that I am using to calculate these statistics.
I am going to explain some of the easier functions first,
these include sum, count, average and median functions.
To use the sum function type equal the function name,
sum, left parentheses,
and then left click and drag through the data
that you want to add.
In general, you should end your function
with the right parentheses,
but in many cases, Excel will do this for you
if you happen to forget.
The count, average and median functions
all work the same way,
the next group of functions are slightly more difficult,
only because their function names
do not match the common statistical names,
but otherwise they are used the same way.
For example, to find the minimum value of sum data
you use the function M-I-N
and not the more common word minimum,
although I prefer the whole word.
I'm not exactly sure why Excel doesn't,
but my guess is that it goes back historically
when computers did not have as much memory
and using an abbreviation saved a few bytes of memory.
I think it is obvious that the function M-A-X finds
the maximum value of some data.
A-V-E, D-E-V or Average deviation
is statistically the mean deviation.
And then we have two groups of standard deviation
and variance functions.
The P at the end of these two functions
is for population data,
and if we leave off the P
these functions are for sample data,
the Excel function, S-K-E-W calculates skewness.
To use any of these functions,
you use the exact same technique
that I use for the minimum function.
The range and percentile functions
are a little bit more complex.
Excel does not provide a range function.
Yep, this gives statistician something to complain about.
They say, "See, I told you Excel
can't be used for statistics."
But when you know how to use Excel, no problem.
Excel is a very flexible and powerful tool.
When you don't have a specific function to do something,
simply create an equation.
This is what we will do for the range.
The definition of the range
is the maximum value minus the minimum value.
So we can easily set up this kind of equation in Excel.
I simply enter equal max swiped through the data,
add a minus symbol, type min, swipe the data and I am done.
Our Excel model now calculates the range statistic.
You might notice that the percentile function
looks a little bit strange.
It has this .exc at the end.
I don't know why, but in Excel 2010,
when Microsoft added more statistical functions
and improved some,
it adopted this stranger kind of function name.
As you explore Excel's functions,
you will come across more of these .exc functions.
The percentile function also takes two arguments,
one for the data range
and one for the percentile of interest.
For example, you might want the 25th percentile
or the 75th percentile.
In Excel, functions can take on one, two or more arguments,
the percentile is an example of a two argument function.
In my model, I have implemented this percentile function
by giving you the flexibility
of entering any percentile of interest in the green cell.
You might notice that the Data Analysis ToolPak
doesn't do anything for percentiles,
it is missing and you cannot do anything about it.
Also missing are the mean deviation
and population standard deviation and variance.
It may seem interesting that I put the mode function
into a group of most complex functions.
Recall that the mode is the most frequently occurring value.
A statistic that most frequently applies
to discreet, nominal and ordinal data.
The problem is that for a set of data,
like what I have provided, there can be more than one mode,
Excel's older mode function
and even the Data Analysis ToolPak
will only produce one mode.
This is a problem and error and an error
that can even occur on our exams.
In Excel 2010, Microsoft gave us
a new mode function called mode.multi.
This function will find multiple modes,
but you must know how to use it, which most people do not.
Mode.multi is an Excel array function.
Excel offers many array functions,
but seldom do people know how to use them.
We experienced one array function earlier,
the frequency function,
which was a bit tricky to use.
This mode multi-function is also tricky.
So carefully watch me demonstrate it.
In some blank area of our worksheet,
I'm going to pre-select about seven or eight cells,
assuming that it is unlikely for our data
to have more than eight modes.
Next enter mode.multi, left parentheses
into the top most cell,
then swipe your data, but don't hit your Enter key.
Exactly as we learned with the frequency function,
you end this array function
with the three keystroke sequence, Control, Shift and Enter.
This is a case where Mac user should not substitute
the Command key for the Control key.
This mode.multi function will then report all the modes.
This manual mode technique, however,
is a bit clumsy to build into my model
since I wanted my model to immediately provide
all the statistics without manual intervention.
If you look down below, I have what I call a scratch area,
where I use a special technique to calculate the modes.
In this course, you will see me use
these scratch areas quite often.
This is one way to get Excel,
to do a lot of work,
and then carry the results into an individual cell.
I'll leave it up to you to explore the scratch area,
but this is how my Excel model finds all the modes.
Since I am doing this intermediate work,
I decided to color these cells orange.
Of course, this is an advanced Excel technique
that many would not know how to do.
In this course,
I will be using other advanced Excel techniques
and hopefully you will learn how to use some of these.
Okay, our Excel Descriptive Statistics Model's taking shape.
However, we have a problem with the way
that I have shown you some of these Excel functions.
What happens if we add another value to our data,
let's say 80 in cell B31.
When I enter this value,
my demo calculations for sum and range do not change, why?
This is because when I stroked through my data,
well, I only stroked my data.
This put the cell references,
B5 to B30 into these functions.
So the 80 entered into cell B31 will not be included.
We could change the cell reference,
but with this technique,
we would have to change the cell references
for every function that we use, a bit cumbersome.
I'd like to illustrate an advanced topic
called named ranges that I use to solve this problem.
This is a bit of an advanced Excel technique,
so not many people know about it.
Excel allows you to name a cell or a group of cells
and then use that name in your models,
somewhat like a variable name
instead of the letter number form of a cell reference.
To do this, when you have selected a cell
or a group of cells.
In this example,
I will select cell B7,
in this upper left box type a name.
For example, I can name this Rick,
then go to another cell and enter equal Rick,
and you will see the value show up.
Rick is a variable name
that I can use anywhere instead of B7.
In my models design,
I have selected all the green cells
and name these cells, My Data.
Then I use the variable My Data in every function.
You can click on every red calculated cell
to see that my data is being used.
This is how I add some flexibility
to my descriptive stats, Excel model.
When a new value is entered,
the red cell calculations are immediately updated.
Pretty cool technique, isn't it?
Excel provides a tool located on the formulas ribbon
to view, manage and change your variables.
On the PC, it is name manager,
on the Mac, it is define name.
Who knows why this feature
is a little different between PC and Mac versions of Excel?
When I pick my data, you will see
that it is defined to B5 to be 55.
If I wanted to expand my model's green area to,
let's say B400.
All that I need to do is change B55 to B400,
and then every function where I use My Data is updated.
I hope that you see that this is a very useful
and powerful Excel technique.
We will see My Data being used throughout this course,
along with more defined name ranges.
This is a good point for me to review the three methods
that I have shown, the status bar,
the Data Analysis Toolack and the function methods.
The status bar method is quick, easy and convenient to use.
However, it's a manual operation,
It doesn't provide a complete set of statistics
and the results are only visual.
It does provide some descriptive statistics,
but no predictive or prescriptive analytics.
You cannot build the results into an Excel business model.
The Data Analysis ToolPak method is also pretty quick,
easy, and it's dialogue boxes and options
make it somewhat flexible.
It also provides a fairly comprehensive set
of statistical analysis for a course, such as ours.
It does take a little longer to learn.
We have no clue what equations Microsoft has used,
and it is what it is and cannot be modified.
Perhaps its greatest disadvantage
is that it must be rerun whenever data changes.
If you forget to rerun it,
your results will be wrong.
Give this a try,
change some data and see
that the Data Analysis ToolPak results don't change.
This lack of interactivity makes it a very weak tool
to build into bigger business models.
Yes, it is free from Microsoft, but if you like this method,
there are better utilities such as mega stat.
It does support descriptive statistics,
but offers little predictive analytics
and due to its lack of interactivity,
it is a very weak approach for doing prescriptive analytics.
Some instructors believe that,
using the Data Analysis ToolPak
is how one should teach statistics.
In fact, some of my colleagues
have accused me of not teaching Excel
because I don't use the Data Analysis ToolPak,
this is ridiculous.
Yes, there are some good Add-ins for Excel,
but everything that this Data Analysis ToolPak does
can be done with Excel functions and other Excel techniques.
This is why I support and teach the function method.
Excel grew up as a business tool
for modeling business scenarios
with this major feature of being able
to do what if or sensitivity analysis,
which is the major theme of prescriptive analytics.
This is the most powerful and flexible way of using Excel.
As you have seen the function approach is the only method
that can provide a complete set of statistics.
Yes, Excel does take time to learn.
My mode calculation, for example,
is a bit advanced and complex.
Some of the models that I will share with you in this course
were extremely difficult to create.
This function approach is the only method
that supports descriptive,
predictive and prescriptive analytics.
It is the method that you can use
to build your own business models
and it is the method that we will use in this course.
Let me show you one quick way to enter some
of the most commonly used statistics functions.
Let's say we want to quickly enter the sum function.
I'll demonstrate this technique first
by positioning my cursor right below my data.
On the home ribbon to the far right,
Excel provides us tool
to quickly enter the sum function.
When you click on it,
you will see that Excel enters the sum function
and guesses at the cells that you might want to sum.
If the guess is wrong,
just swipe through your data of interest.
If you look carefully at this sum tool,
there is a little dropdown arrow.
If you click on this,
you will see that this quick equation tool
can be used to insert other commonly used equations.
Give it a try.
Another way to find and explore functions
is by clicking on this fx symbol.
Doing so opens up a dialogue box
where you can search for functions
and see some instructions about how to use them.
I will end this session showing you my weighted data models.
Let's assume that you have a problem like this one,
a frequency distribution for the vehicle selling prices
is constructed, shown below.
Determine the arithmetic mean vehicle selling price.
The table represents grouped or weighted data.
Sometimes you will encounter data like this,
where someone has grouped the data.
I provided the equations for calculating statistics
for weighted data in my previous session.
One can do these calculations manually,
but I have built an Excel model to do these calculations,
which I call my weighted data model.
To use my model simply enter the data,
remember to not touch red cells,
make sure to delete any data leftover
from any previous problem.
Very quickly, my weighted data model
calculates a set of statistics, it's that simple.
One very interesting function
that I use in this model,
that once again,
very few people know exists,
is the sum product function.
You might recall from our previous session
that in order to do this weighted average calculation,
we take the group mid point times the count
for each category and then add them together,
the sum product function does this all in one step.
You might want to show this function to some of your friends
to see if anyone is aware of it.
Below this way to data model
is a model that can be used to create groups from data,
implementing some of the rules
that we learned in our graphing session.
It calculates the number of intervals
which you can override.
It then calculates the interval
with which you can again, override
and it lets you set the minimum value.
To the right each interval, lower and upper values
and the count for each interval are produced.
I should mention that I am always improving my Excel models
and that you may notice features in my latest model
that are not shown in my video.
For example, I recently added geometric mean calculations
shown in these two cells.
Recall that the geometric mean is useful
in finding the average change of percentages ratios,
indexes, or growth rates over time.
I can best illustrate this with this textbook example.
The return on investment earned
by Atkins Construction Company for four successive years
was 30%, 20%, minus 40% and 200%.
What is the average rate of return on investment?
When I put these values into my Excel model,
I ended up with 1.294,
which is exactly what the textbook author gets.
Had we used the arithmetic mean,
we would have calculated 52.5%,
which is much higher than the true rate of return.
Please notice that you must be careful
entering percentages correctly into the model.
Minus 40% is entered as 0.6 and 200% is entered as three.
Since a 200% increase triples your investment,
a 100% increase will double your investment.
I'm using excels geo mean function to do the calculation,
which is a lot easier than using the geomean equation.
Another application of the geometric mean
is to find the average percent change over a period of time.
For example, if you earn $45,000 in 2006
and $100,000 in 2018,
what is your annual rate of increase over these 12 years.
In my Excel model,
you enter the beginning value
into the first cell,
the ending value into the second
and the number of years into the third cell.
My model calculates 6.88%,
which is exactly what the textbook author gets.
As a side note, excels Data Analysis ToolPak utility
does not do geometric mean calculations.
In summary, we have learned various ways to use Excel
to calculate descriptive statistics.
I am giving you my Excel models
because expecting you to create these takes time
and are beyond the scope of this course.
I do want to emphasize that my models
are completely open for you to explore.
I do not password protect anything
nor do my models use the visual basic
or VBA programming language.
I just use Excel functions,
have fun using my models and learn from them.
(upbeat music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help,
please let me know.
(upbeat music)