Welcome to Statistics in Excel video number 31.
Hey, if you want to download this workbook and follow along,
click on my YouTube channel, then
click on my College website link,
and you can download the Workbook Business 210 Chapter
2.
Hey, we're on the sheet 32, even though this is video 31.
We want to see how to use the data analysis and histogram
feature in Excel to create a frequency.
Let's go over here.
We're going to create a data analysis add-in.
If you didn't add that in and you need to see how,
go back and watch video in this series number
eight, because you actually have to add in the data analysis
feature.
And then we will use what's called a histogram.
We'll click on the histogram button,
and it will create a frequency distribution, a histogram,
and a percent cumulative chart, all with just a few clicks.
Now, we have done these, this exact chart,
with formulas and pivot tables.
This feature works slightly different.
We're going to have to give it some upper limits, some bins,
and it will then create our frequency distribution
histogram, et cetera.
Now, here's our data set.
Actually, why don't we go back and, just real quickly,
back in video 20 and 21, we saw how to do--
video 21, we saw how to do this with a formula, right here.
And we got exactly what we wanted.
It was beautiful.
You have more control when you use formulas and pivot tables
than you do with this add-in feature.
Nevertheless, we do have to look at this feature.
It is quite helpful and can allow
you to do what we did with formulas and pivot tables
quite quickly compared to those other methods.
Now, our goal is to look at customer age, group
them together, and then create a histogram.
All right, here we go.
We're going to go up to Data.
And if you've added this in, then you'll
see the analysis, and then Data Analysis.
Then you click on this right here.
You click on Histogram and then click OK.
Now it's going to ask you, what's the input range?
Now, very important, you cannot include the field name when you
use this feature.
So I'm going to click right-- or we're not going to.
We could, but we're not going to.
So we're not going to check this.
And then we do our input range.
We're going to click on C21, and then Control-Shift-Down Arrow
to select C21 all the way to C220.
And then on the scroll back up.
And this is where this feature gets a little bit different.
We are going to have to give it some upper range,
some upper bins.
And the pivot tables we did when we used integers,
it didn't include this upper number.
But this feature, when you include--
when you give it the bins, that means for the first group,
this is the upper number, 20.
Everything below will be included in here.
And then the next group, 30, everything between and not
including 20 up to 30 will be included in this.
So this is different.
The bins, when we give it the upper end,
they are included in that particular category.
So I'm going to highlight the range right there.
Now, it will spit out these numbers in our chart,
and we're going to change it.
We're going to say 10 to 20, 20 to 30, et cetera.
All right, now we come down here.
We want it on a new worksheet.
We want cumulative percent to show
on our chart, a Ogive chart.
And then we want our chart, our histogram.
Now, the chart will spit out a histogram with gaps,
and we will change that.
All right, and now we click OK.
And just like that, that's a lot faster
than how we did it earlier.
Now, the problem is this 20, this is not the same chart
as we did back in video 20 and 21, or our pivot tables.
This 20 is included, and since there's six 20's
in our data set, we get a 19 here.
Nevertheless, again, all these charts are estimates.
We're visually portraying data from all the raw data.
So this is an acceptable method.
Now, we need to clean this up a little bit.
I'm going to say 10 to 20, Enter.
Now, what happens there?
I typed 10 to 20, and it put a date in?
Guess what?
No matter how hard you try, that's going to happen,
because it interprets it as a date.
So what do we do?
We type an apostrophe.
And I'm going to do 10 up to 20.
None of the other ones will cause a problem,
because 10/20 can be interpreted as a date,
whereas 20 up to 30 cannot be interpreted as a date.
All right, and then 30 up to 40, 40 up to 50.
These are somewhat ambiguous, because that 40 is not included
in this group right here.
It is included in this group up here.
Again, when I type 50 to 60, the 50 is not in this group.
The 60 is.
So, again, when we use the data analysis feature,
the upper end is included, but not the lower end.
Finally, we'll do 60 to 70.
Notice, that's totally cool.
It updates our chart perfectly.
Now, we definitely need to clean this up.
This is not a proper histogram.
Let's get rid of this, delete.
And then I'm going to click on this.
We could right-click and point to format legend.
Or just like we format cells with the keyboard shortcut
Control-1, all chart elements can be formatted by Control-1.
So I open that up and I say, hey,
show me the legend at the top.
Click Close.
There we have that there.
Notice there's an extra More category here, too.
The way this charting engine works is when you give it
our bin back over here of 20, it says 20 and everything below.
You give it an upper end of 70, right?
You think that it would just interpret that and stop there.
No.
It always adds an extra category,
in case there's some values greater than 70.
Now, I'm going to click here and change this, type maybe a data
analysis.
So I named that sheet.
Now I need to do some other things.
I'm going to click on the columns, Control-1.
I'm going to change the gap width to zero.
Then I'm going to come over here to border color.
I'm going to say a solid line.
I'm going to change it to black.
I'm going to click this Close right here.
And with those still selected, I'm
going to go up to Layout, Data Labels, and then Outside End.
You could see we got different results.
There's still exactly 200 records being counted.
They're just grouped in a slightly different way.
That one's fine.
Ah, this one.
We don't want that.
I'm going to click--
just click and then start typing.
You can watch up here.
It says, equals bin, but watch this.
I'll type-- these are the classes.
And then I'm going to hit--
and then how about, in parentheses, age.
So we've labeled-- and then hit Enter.
Notice, as I'm typing, it's coming up there.
I hit Enter, and there we go.
Did I spell it right?
I hope so.
I'm such a bad speller.
I'm going to click up here, too, and type Upper
included, Lower not included.
I hope I spelled it right.
I'm such a bad speller.
Double-click that.
I'm going to move this like this.
Highlight all of these right here and go up to Home.
How about Word Wrap.
And then maybe I change the--
OK, so there we have how to use the data analysis add-in
feature to do a frequency table, histogram, and then
cumulative frequency, also.
All right, we'll see you next trick.
Next trick, 32-- next video, 32, we'll
do a summarization of all the different formula and pivot
table methods we saw in this chapter 2.
All right, we'll see you next video.