Welcome to Excel 2013
Statistical Analysis video number 23. Hey, if you want to download
this workbook and follow along, click on the link
below the video. Hey, we're on the sheet
five number summary. That's cause we want to talk
about the five number summary. Now, it's straightforward
to calculate. You've got to calculate the min,
quartile 1 to 3, and the max. Those are the five numbers. Hey, the five numbers
summary provides information like min, max, the median. You can figure out
the range quickly. It gives you the quartiles. You can figure out the
quartile range quickly. And it's the basis to
build a box plot, which we'll see in just a second. Now, our example
here is a sample of salaries for accountants
in Oakland, California. Here's our column here. And we want to calculate
our five number summary. Now, we're using .exc. Quartile exclusive,
it excludes 0 and 1. We saw back in the video on
quartile that you could use .inc and use 0, 1, 2, 3 4. And it would automatically
calculate the five number summary. We're going to use the min, the
max, and the quartile function. So in cell C13 equals min. Click in the top cell,
Control+Shift down arrow to highlight the whole range. And I'm going to Shift+Enter to
put that in the cell and push the cell up. Now, I'm going to use
quartile equals quartile.exc. The array, click on the top
cell, Control+Shift down arrow. F4, because we want to lock
it with our dollar signs. And jump the screen
back up, and then comma. And I'm going to use my arrow
keys to get to my 1, 2, 3. Control+Enter, and now
I can copy this down. And there's quartile 1, 2, 3. Click on the last
cell, and I'm just checking to see if it's locked
and if it's got the right cell reference. Yes, it does. Enter equals MAX tab. Click in the top cell,
Control+Shift down arrow, Shift+Enter to put that in
the cell and push the cell up. So there it is,
five number summary. So the min salary for
an accounting position in our sample for
a month was $2,510. The max was $6,987. Quartile 1 to 3, there they are. If we subtracted
$4,003 from $4,664-- that's quartile 1
from quartile 3-- that would be the
interquartile range. And we can see that
about $7,000 to $2,500, the difference between
those two, is the range. Now, let's go over to
the sheet five number summary and box plot. Now, in this class,
we're not going to build this chart in Excel. Because although it can be
done, it's quite tedious. I do have other videos on
how to build box plots. Now, here's the deal. Here's some quizzes. And this articulates
the five number summary. So the rectangle in the
middle represents quartile 1, which is 17.5 points, all
the way up to quartile 3, which is about 30. We can clearly see the
middle 50% of the values go from about 30 to about 17. The line down the middle,
that's quartile 2 or the median. It looks like 27. We also put the
mean up here, right? Remember, if the mean
is below the median, then there's probably some small
values pulling the mean down. We can also see the
little whiskers here that extends to the biggest
value inside the lower limit, which happens
to be our min of 10. This whisker up here
extends and shows the max, which also is
inside our upper limit. Now, these limits are created. We had 0 and 47.5. If we had values, they
would be marked as an x or a square or something. That would represent an outlier,
an unusually small or unusually large value. Now, in this class,
we're going to see how to calculate all of these
pieces from this dataset. You can sketch out a
box plot like this. I'm going to scoot
this off to the side. All right, so
here's our dataset. We will calculate equals min,
highlight the whole range, Enter, equals q. And the very first
function is quartile.exc. So Tab, highlight the range, and
I'm immediately going to hit F4 to lock that range, comma
and quartile 1, arrow, arrow, over to the right,
Control+Enter, and copy it just down
to the third quartile. The max equals max. And there's our range, Enter. Our interquartile range,
that is quartile 3 to quartile 1, and Enter. Now, to calculate the
lines up at the top, our textbook uses
the multiplier 1.5 to determine an unusually
small or large value. There are other
multipliers like 2.2. There's some notes down here
if you want to look at that. We're going to use 1.5. And here's the calculation
for the lower limit. Equals, we're going to
actually start with quartile 1. And we're going to
subtract our multiplier times the interquartile range. Boom, now, this is
negative, right? A quiz can't go below 0. So we would convert that to 0. Notice the rule right there. Our upper limit,
hey, we're going to extend past quartile
3 plus our multiplier times the interquartile
range, and Enter. Now, those are two
assumed marking positions. Any values that we
find past these two would be considered outliers. If you wanted to
change it to 2.2, boom, there would be different lower
and upper limits, Control+Z. Now, I'd like to actually count
to see if there's any values below or above our limits. Now, this one we don't
have to worry about. Because the quizzes can't
possibly get below 0. Not only that, but on a small
data set, we could just eye it. But of course, we have
to COUNTIFS functions. And that will
allow us to count-- bloop, there's all the values,
comma, and the criteria. Well, there's the hurdle. Anything above would be
considered an outlier. So I need to combine comparative
operator and the number. I put the comparative
operator in double quotes. The greater than symbol
is pointing over there. So if it finds any of these
that are bigger than 47.5, then it will be counted. We have to join it, Shift+7,
the ampersand to our criteria sitting in the
cell, Control+Enter. So there are 0 if I were to-- if I had one in here like 50,
instantly I would get a count, Control+Z. Now, we need to
calculate the mean average. Highlight the range and Enter. So there we have all
of the inputs that will allow us to
draw our box plot, a visual summary of our
five number summary. Now, we have one more topic. Here, we considered
outliers in this 1.5 rule. Let's scroll over here and
look at the sheet, Outliers. Now, we talked about
z and our normal curve with plus or minus 3
standard deviations as being really rare. So another way to calculate
an outlier is simply to check the z. So I'm going to say
the particular value minus the mean-- F4 key to lock that-- close parentheses divided
by standard deviation, F4, Control+Enter, double
click and send it down. Now certainly, we could
go through and eye this. I can already see it looks like
there's one there and there. So we could actually add
an extra column and use-- since we're checking for
positive 3 or negative 3, we could use the ABS,
the absolute value. So that will return whatever
the number is distance from 0. And I'm going to say anytime
you are greater than 3. Now, because we're using a
comparative operator directly in our formula, this
is a logical formula that will return
just a true or false. Control+Enter, double
click, and send it down. Now, notice even here,
we can see the truths now and pick those out. We could come over to this side. We could do a formula
like equals COUNTIFS, highlight the whole range,
Control+Shift down arrow, Control+Backspace to jump the
screen back in view, comma. And then because
it's a logical, we don't have to put
it in double quotes. It is its own data type,
neither text nor number, so Control+Enter. Actually, if you hit F2, you
see it pop back up to true. At the beginning
of class, we talked about how that's
a characteristic of true/false values in Excel. So that is another way we
could look through the data set and count. Right click, paintbrush to copy
just the formatting, and click. Still another way, watch this. Control+Shift down arrow,
because we want to highlight the whole column. Now, I'm going to scroll back
up so you can see that I've highlighted the whole column. We can go to Home, Conditional
Formatting, Highlight Cell Rules, equal to-- and I'm going to type true here. And then instead of
accepting the default, I'm going to click the dropdown
and go to a Custom Format. And now, I can do
whatever I want. This is our Format
Cells dialog box. I'm going to select Red
Fill, Font, I want White. Click OK, click OK. I kind of like that way. Because now, we can go
through and pick it out. Because an outlier could
be an actual value that should be in the data set,
a value that was mistakenly in the data set, or even
typing error, all right? So there you go. Outliers with z, logical
formula COUNTIFS, and even a little conditional formatting. All right, so we talked a
little bit about outliers with the z rule. We talked about box plot. And we talked about our
five numbers summary. All right, next
video, we're going to talk about our last
numerical measure, covariance and coefficient of
variation for two variables. All right, see you next video.
