Welcome to Excel 2013 statistical analysis
video number 15.
Hey, if you want to download this workbook and follow along,
click on the link below the video.
Hey, in this video we want to see how to create a dot plot.
Now we already had an earlier video
where we talked about how to interpret a dot plot.
And we weren't going to create a dot plot in Excel.
But it's too useful of a chart and relatively straightforward
to create.
So I'm going to see how to create these three dot plots.
Now what it does is we take quiz 1 and quiz 2, quiz 3.
Here's the possible scores.
We actually stack the dots on top.
So here, we could see man, that's
a pretty uniform distribution for quiz 1.
Quiz 2, people scored completely different.
It almost looks bell shape.
And then here in quiz 3, looks like there's
a bunch of people in the top and a bunch of people
in the middle, a bimodal distribution.
So dot plots are a great way to compare
different distributions.
Let's go over to the sheet dot plot.
Here's our quiz 1, quiz 2, quiz 3.
I'm actually going to insert a bunch of rows
above and put the dot plots above.
So I'm going to click on 1 and drag all the way down
to 10, and Right Click Insert.
All right.
So now I'm going to start with quiz 3, Tab.
That'll be our label right there.
And I'm going to put a 0 Tab, 1, Tab.
And then I'm going to highlight the 0 and 1.
I've established a pattern of adding one
and I'm going to with my [INAUDIBLE] rabbit, click
in drag.
And I see the screen tip.
It's at 7, 8, 9, 10.
Those are all the possible scores.
Now I'm going to change the width of the column,
clicking on E. And then click and drag all the way to 0.
And then I'm going to point between any one of them
and click and drag.
Scroll down just a little bit so see for quiz 3.
Actually, let's do some formatting.
I'll highlight, fill bucket, dark blue, font color, white
and add some borders.
Now for a few cells right here, I'm going to add a border.
We're actually going to need to stack dots on top.
So for quiz 3, it looks like we have 1, 2, 3 tens,
so I need dot, dot, dot.
But I want a formula here that I can enter into the cell
and copy over.
And it will automatically give me those dots.
Now what are we really doing?
We're counting zeros.
How many zeros are there?
Ones, how many ones are there?
Two, how many twos are there?
That's the perfect use for the count ifs function.
Some I'm going to use count ifs.
It wants the criteria range.
Highlight quiz all the way down to the bottom.
And F4 key to lock it.
There's criteria range 1.
I type a comma to get to the next argument.
You know, this screen tip is in the way,
so I'm going to point it when I see my four way white arrow
cross hair.
I'm going to click and drag it out of the way.
Now I just need relative cell reference.
Boom.
Control-Enter, and then copy it over.
Now that's not quite what we want.
A three, a two, a two, a zero, et cetera.
That's the right count, but I want dots.
And what do we want to do?
We want to repeat three dots, repeat two dots.
No way.
You're not going to believe this.
Let's click in this cell F2.
There's a function that will repeat a certain character.
We already have the count from count ifs, the repeat function.
So are r-e-p-t.
And you can see right here, repeats the text
given a number of times.
That's pretty amazing.
Now what it wants text and number of times.
Hey, the number of times is going to be that count ifs.
But for text, text in formulas always goes in double quotes.
Double quote, asterisk, Shift 8, double quote.
That's the text, comma, and I'm done.
Whoa.
Except for I have to come very carefully to the end.
And what you don't want to do is click right there,
because that'll click a cell reference
and put it right where the cursor is.
You want your I beam, or you can come up in the Formula bar
if that's less dangerous to you and click, and close
parentheses, Control-Enter.
Hey look, I got a zero, nothing.
I drag this over.
Oh!
That is so cool.
Now we just have to do some formatting to turn them red,
make them bold, and make them stand straight up.
Now we can Right Click Format Cells,
or use the keyboard Control-1.
Now alignment is what we use to align them vertical.
So [BROOP] right there.
Either way, up or down.
Now I go to font.
Here I can select bold 14, font color, red.
And when I click OK, boom.
Just like that.
Now that is beautiful!
Look at that!
And if I were to change this, I'm going to change this to 10.
Then instantly, we get a fourth one there.
I'm going to increase the row height.
I'm pointing between 8 and 9.
That is so cool!
Now watch this.
I'm going to copy and paste.
Control-C, 1, 2.
I'm going to Control-V.
Oh, that's not going to work.
Control-Z, 1, 2, I'm going to click on the third one,
Control-V. 1, 2, 3, Control-V. Now we need to change it.
I'm going to change this to 2.
Change this one to 1.
Come to the first cell in this second dot plot, F2.
Oh.
Look at that.
It's in red now.
Now I can either click here and highlight it,
and then redirect it, Control-Shift Down Arrow F4.
In the next example, I'll show you a different way.
But notice I've edited it by clicking and then just taking
my mouse and [BOOP] Control-Enter,
and now copy it over.
[BROOP] Now I'm going to have to come over here and change
the row height.
I'll actually change them so they're
all the same size in just a moment
but now let's go up to the third dot plot, F2.
And watch this.
See that blue?
See that blue right there?
I'm going to point.
And when I see my move cursor right there, that's the move--
move cursor, click and drag.
I love that.
Control-Enter, and now copy it over.
Uh!
I did the same thing.
I'm going to click on row header 2.
I'm holding Control, clicking on number 5.
When I hold the Control key and click
on different sections in the spreadsheet,
I'm allowed to select different parts.
Now I can change the height of one of them just a little bit.
And they all get exactly the same fit.
So that is how to do a dot plot.
We did repeat, a repeat and asterisks, count ifs to count.
We had to create dots stacked on top of each other using
alignment.
And we had some bold and red font color.
Copied and paste and boom, we have our dot plots.
All right.
We'll see you next video.