(bright upbeat music)
- Hello, I am Professor Rick Jerz
and I welcome you to this session titled
"Exploring Dispersion using Excel."
My Excel model that I provided to you
in our last Excel session
already includes some of these additional ways
to describe dispersion that we just learned about.
In this session, I will show you these
and I will show you how to use Excel
for the few more ways we learned in our previous session.
Let's get started.
Our goals are to create a dot plot using MegaStat
in Microsoft Excel,
to use Excel to compute quartiles, deciles, and percentiles,
to compute skewness, and to see how Excel
can create a box plot.
Remember from our previous session
that a dot plot looks like this.
Excel does not provide a dot plot
as one of its common graphs.
A dot plot is essentially a frequency diagram
with circles instead of a solid bar.
It's relatively easy to convert any frequency diagram
into a dot plot with this technique that I will show you.
I will demonstrate this technique
using my M&M count data.
Click one time on any bar to highlight all,
right click and pick format data series,
click on this fill tool,
and then, under border, pick no line.
Drop down the fill menu,
click picture or texture fill,
and the key is, then, to pick a picture source
either from your computer or the clipboard.
I happen to have a picture of a black dot on my computer
so this is what I'm going to insert.
Last, pick stack and scale width.
That's it; we can close this dialogue box and we are done.
We now have a dot plot.
The interesting thing about this technique is that
you're not limited to just using a black dot.
You can use any kind of graphic.
Give it a try and experiment.
I should mention that the data analysis tool pack
does not provide a dot plot.
However, the MegaStat add-in does.
You are welcome to install MegaStat and experiment.
By the way, the installation of MegaStat
is not a requirement for this course.
I'm showing this to you so that you can get
an appreciation of what some of these
additional add-ins can do.
Now, let's turn our attention to quartiles,
deciles, and percentiles.
Microsoft Excel provides a function called
percentile.exc.
It needs an array and a value for k.
The array is your data and k is the percentile of interest
expressed as a decimal between zero and one.
For example, if we want the value for the 25th percentile,
k should equal .25.
Here's a special note about this function.
It is only
a 2010
or later function.
Excel's previous percentile function did not work.
I demonstrated my percentile function
in my previous session on descriptive statistics with Excel.
But I will show it to you here again.
In my model, you must enter the percentile of interest.
In this case, I'm saying the 25th percentile.
Right below this cell, I show the calculated value
for this 25th percentile.
So calculating percentiles in Excel can be as easy as this.
Let's turn our attention to boxplots.
I really like boxplots and enjoy using them.
Here is a boxplot provided by the author
and one produced by MegaStat.
You might notice that they are very similar.
So, MegaStat can produce boxplots.
For many years, statisticians would argue that
Excel is not a capable statistical tool.
It can't produce boxplots.
However, many years ago, I showed that Excel
can produce boxplots if you know some techniques
about how to use Excel.
Here I show you a comparison of the boxplot graphic
provided by our author
and the one that I have produced in Excel
for quite some years.
If you compare these,
you will notice that they both show
the median, minimum, and maximum values
and the inner 50% quartile.
So they are equivalent.
I show you my boxplot in my Excel
descriptive statistics model.
Right above it, I show you the data that is needed
to create this boxplot.
We need the minimum value, the 25th quartile,
the median, the 75th quartile, and the maximum.
By the way, I am using this percentile.exc function
to calculate the 25th and 75th quartiles.
From a practical business perspective,
my boxplot gives you the same information
that you would expect to find in the pure form
of the boxplot.
A subtle but important feature of my boxplot
is that it is dynamic.
When the data changes, this boxplot will dynamically change.
The boxplot produced by MegaStat
must always be refreshed.
In one of Microsoft's versions of Excel 2016,
Excel finally provided a boxplot tool.
Here is a comparison of Excel's boxplot
produced with this new tool
compared with the author's boxplot graphic.
In my Excel descriptive statistics model,
I include Excel's chart next to my own.
Producing this boxplot chart is identical
to producing many of the other charts
in Microsoft Excel.
You pre-select your data,
then go to insert chart and pick box and whisker.
Excel allows you to format this chart to your liking.
Let's now talk about skewness.
Our textbook author mentions that there are
several ways to calculate skewness.
And we, of course, are going to use the software approach.
In Microsoft Excel, this calculation is simple
because Microsoft Excel provides a function called Skew,
S-K-E-W,
and this is the function that I use
in my descriptive statistics model.
Scatter diagrams are illustrated in this chapter
as another way of graphically showing dispersion.
Microsoft Excel can produce scatter diagrams
and it's pretty easy.
In fact, there is a tool called Scatter that produces
scatter diagrams.
We will look at scatter diagrams in a later session
on correlation and regression analysis.
In summary, we have learned about dot plots,
percentiles, box plots, skewness, and scatter diagrams.
(bright 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.
(bright upbeat music)