Welcome to Excel 2013 Statistical Analysis Video
Number 40.
Hey, if you want to download this workbook and follow along,
click on the link below the video.
And we're going to start on the sheet Create Area Chart.
But let's go check out our end result.
Here's our area chart for our normal bell
shaped distribution.
And I want to put an x in 64.
Right now it's showing me the area or probability
from the low end all the way to that 64.
But I want to be able to change that to 84
and instantly have the chart update, including
the probability on the chart.
So let's come over here.
Now we're going to have our inputs here.
And we're going to build some tables.
Because in Excel, you have to have your x values
and your probabilities.
You actually have to have all the data points to plot.
Now these are the steps for the chart
even though we're going to do a bunch of formula
stuff over here first.
Hey, there's our mean, 74 points with a standard deviation of 10
on this test.
And we want to calculate 64 and then be able to change that.
Now the first thing is the x value.
Now we noticed when we were talking
about the characteristics of the normal distribution
that both sides go to infinity.
But that's not really true.
That's just theoretical.
Because below four or five standard deviations,
the probability is so small that it is highly unlikely.
So I'm just going to take this and go down
four standard deviations below.
And then we'll make our x go all the way
to four standard deviations above.
And that'll be enough x's to plot.
So I'm going to say equals 74 minus the standard deviation
times 4.
That gives me 34.
Now I'd like to calculate my z for this and then increment
this down and stop when this gets to 4.
So I'm going to say, equals open parentheses
of particular x minus the mean.
F4, close parentheses, and divide
by the standard deviation F4.
Right now it gives me minus 4.
Now I'm going to increment this equals one cell above,
relative cell reference plus 1.
Now I can copy this down.
So minus 3.9.
Now I'm going to take both of these formulas
and the empty cell and just drag it down.
Now I already pre-formatted this.
But imagine you stopped here and you're like,
oh, 3.1 is not enough.
So I'm going to just drag it down till those four.
Now notice when we do this theoretical chart, 114.
Well, that point isn't even possible.
But theoretically it is.
I mean, maybe the teacher's giving extra credit.
Control-up arrow.
Now we want to calculate our probability.
And guess what?
We're going to use our norm.dist but whereas earlier when
we did cumulative, here we're going to give it our x, comma,
the mean F4, comma, the standard deviation F4
and, comma, we want probability mass.
Now be careful.
That's the same wording they used when we
did binom discrete functions.
But it does something different here.
It's not calculating length of binom dis,
height of the column, and probability.
It's just calculating the height of the curve.
So I'm not going to put false.
I'm going to put zero.
I'm following our convention of one for true, zero for false.
Close parentheses.
Control-Enter.
That calculates the height of the curve.
None of these are probabilities.
Now if you were curious, I do have off to the side,
there's the formula from the book that
actually calculates that curve.
Not necessary because we have norm.dist.
Now, we want to plot this.
And that just gives me the round curve.
But we actually want to also plot
all of the area when the x value is less than 64.
So I'm going to do a formula here, equals IF,
and the logical test, this is something
that comes out true or false.
Now our logical tests, I'm going to say,
hey, relative cell reference x value, are you less than
or equal to the 64?
F4.
Right now the answer is yes because 34 is less than 64.
When I copy it down, that one's less than 64.
So is this one.
So each time I get a true from this logical test,
I need to get the height into this column.
When it gets down past 64, then I need to not show the height,
comma, the value if true.
That's what the IF function is going to put into the cell
if the logical test comes out true.
The height, comma, otherwise we need
to show nothing, which is double quote, double quote.
That's a zero length texturing that'll show nothing.
And it's what you put into a column like this
when you don't want anything to show up in the chart.
Close parentheses.
Control-Enter.
Double click and send it down.
Control-down arrow, you can see all of those are empty.
But check that out.
Right at 64, it shows it.
And here it doesn't.
All of these values will show up on our chart
as that second color.
Now we do want to get a little bit tricky here.
I want a label up here that will show up
in the legend that tells me that this
is a probability, a cumulative probability,
and has the probability.
And I want to do a label up here,
a text formula, that says, hey, this is the probability
of less than or equal to 64.
So watch this.
We're going to do a text formula.
Equals, in double quotes, P open parentheses x, space, less than
or equal to, space.
And now I'm going to end double quote.
Because I need to join it to this 64.
So far I have a bunch of text, the actual 64.
Now I need to join a third thing.
In double quotes, close parentheses, end double quote.
Control-Enter.
Actually F2 to put it back in edit mode.
I'm actually going to put a space and an equal sign
and then end double quote, Control-Enter.
Now we can go ahead and calculate our probability
equals no norm.dist.
And just as we've been doing earlier,
this function will go from the low end.
So I put in 64.
That's everything from the very bottom to 64, comma,
and the mean, comma, and the standard deviation, comma,
and this one is 1 for cumulative.
Control-Enter.
That's the probability.
Now we come down here.
And this is the label that will show up in the legend.
Before I do this formula, I'm going to come over here and F2.
And to make it easier, I'm going to add a space here.
It won't hurt in this cell because it won't show up.
But I need it when I'm going to make my formula over here.
So now I'm going to go, equals that one right there.
And I'm going to join it to this.
Now this messy number is going to show up down here.
And I don't want it, just for a second.
I'm going to expand the column.
I do not want all those decimals in my legend or in my formula.
So I'm going to round this F2.
I'm going to use the round function, round.
Round, there's the number.
But I'm going to come to the end and say, comma, four
because I want to show four digits.
Close parentheses.
Control-Enter.
If I expand this just a little bit, that is beautiful.
And watch this.
This is all dynamic.
84, and instantly from the low end,
all of my formulas flowing down to this one, which will
be in our legend are working.
Now I'm going to change this back to 64.
And we can make our chart.
Now step one is we highlight all of these heights.
And notice, I put a P of x here because that's
going to show up in our legend represented by some area.
So even though these are not probabilities, the legend
and as it appears on the chart, it
will be probability, same as over here.
So I click on the top cell.
Control-Shift-down arrow.
And actually, I'm going to scroll up.
Because I want the chart to appear up here.
Insert.
And we're going to use an area chart, this first one right
here, and boom.
Now immediately, we have to go up to Design, Select Data,
and add a second series.
So I'm going to click Add.
Scroll up here and I can see I'm not
able to get to where I want.
So watch this.
I'm going to click here and right arrow.
That gives me D10.
And then I'm going to highlight this and delete.
And then, click right below z and right arrow.
Control-Shift-down arrow.
Click OK.
Now the horizontal labels are messed up.
But we can't fix them yet.
Click OK and scroll up.
You can see that with charting, it leaves us in funny places.
I'm immediately going to click on the second area
that we added.
Control-1 to open up the task pane and add to secondary axis.
We have to do that in order to change the x and z's
and associate them with two different series.
Now I'm going to delete both of these.
And that gets rid of that area hanging off the edge there.
I don't need these horizontal grid lines.
Delete.
Right click.
Select Data.
And now very carefully, I'm going
to select the second axis we added,
and click the horizontal category, and add the z values.
Control-Shift-down arrow.
Click OK.
Now I'm going to click on the first column we added
and edit these horizontal categories.
Scroll up and get the x's.
Control-Shift-down arrow.
Click OK.
And we can click back and forth and see we have
our z's and x's.
Click OK.
Scroll up.
Now I'm going to move this to the side.
Click on the plus.
And under Axes, now we want to add secondary horizontal axis
in.
No way.
It's up at the top.
Now I'm going to select this and Control-1 to open up
the task pane.
Click to turn that off.
And we immediately want to do a couple of things.
Come down here to Labels.
I want to try to specify an interval.
I'm going to try 10 tab.
That's looking good.
Label position, I want to say low.
And instantly, that's down at the bottom.
It still has that x-axis.
And I want to come up here to Tick Marks
because I want to turn these off up here.
And I'm going to say Major Type None, Minor Type None.
Close this.
We may have to open it later.
Now I need to add x and z here.
So I'm going to have to do something.
Click on the edge and with my horizontal arrow, drag in.
And now I want to add a text box here.
Insert, Shapes, and I'm going to use this rectangle here.
Click and drag.
Right click.
Edit Text.
x, enter, enter, z.
Control-A to select all of that text because it's white.
Go up to the Home ribbon and change it to black.
And I'm going to click on the edge here.
And this is a text box.
So Control-1 and it will add up.
I don't want any fill.
And I don't want any border.
I can see I'm not seeing my z.
So I'm going to click and drag up or click and drag down
if I can.
And I can click to Add a Chart Title.
Equal sign, and I'm going to scroll up.
I'll close this task pane.
I think it is in Q1.
Scroll over and Enter.
The font is too big.
Home, 10, Enter.
Still too big.
8, Enter.
So I have to put an 8 up there.
Click the plus.
Let's add our legend.
Axis Titles.
Click on the vertical one.
And I'm simply going to type Area equals Probability
and Enter.
Maybe I'll move this one in a little bit.
I see the axis title.
I'll fix that.
But I want to say equals and click on my x up here
and Enter.
Move this off to the side.
F2, space.
So x equals test score, Enter.
I'm clicking on the inside here.
And I see my little white box.
I'm going to click and drag to drag this up.
Click and drag this down.
And this axis title up here, now I can't really get to that.
So I'm going to use my arrow keys
and move around inside the chart until I see it selected.
I see it selected right there.
The other way to do it is to go up to Format.
And then the dropdown over here, you
can select any particular element in your chart.
Secondary horizontal category axis, I don't want that.
So I'm going to hit Delete, Delete.
And I'm going move this up here.
Look at that.
Well, so now I can move this up, get the z
and the x kind of lined up.
And that is one wild chart.
Now you know for this class, you can stop.
You don't have to do the second silly secondary axis stuff.
But at minimum, make a probability chart and delete
everything except for the colors.
That's how you do this.
So now if I change this to 84, instantly.
Now if you wanted to do the upper end,
you'd have to have just a different formula here.
Also for the between, you'd actually have to amend this.
For the upper end, you'd say greater than or equal to.
But you'd actually have to add an AND.
And if you want to see how to do that,
you can come over to normal between.
And down here is the formula for that.
All right.
That was a pretty wild charting exercise.
All right, next video, we're going to talk about a few
more calculations for the normal probability distribution.
All right.
See you next video.