Welcome to Highline BI348 Class Video Number 15.
If you want to download this workbook, BI348Chapter02start
or the finished file, click on the link below the video.
In this video we have a data set.
And we're actually going to use this data set
for the next couple videos to create frequency distributions
and the appropriate charts.
Now in this video we want to look at a categorical variable,
Website, and count how many transactions
for each one of the websites.
Now this data set is for the company Boomerang Incorporated.
And this company sells boomerangs
online through four different websites.
Now this is categorical data.
So we will simply count using either the COUNTIFS function
or a pivot table.
And then we'll make a column chart.
Now let's go ahead and click here and Control down arrow.
So this is like 26,000 records here.
Control up arrow.
Now I want to click in a single cell.
And we'll do pivot tables first, put it on a new sheet,
and then we'll see how to do the COUNTIFS
functions on that sheet.
Now the keyboard is Alt, N, V as we've
seen in the earlier videos.
But notice, the default is New Worksheet.
Not only that, but any time you have a dialogue box,
if a button is highlighted, that means
the Enter key will enact it.
So it's really Alt, N, V, and Enter.
It puts it on a new sheet.
I'm immediately going to double click this and call
this Cat for categorical, FD for frequency and distribution,
and Column Chart, and Enter.
Now here's our field list.
Now I'm going to actually drag this.
I'm going to point to the top and click--
and it's kind of hard to drag-- and drag it over here.
Resize this.
So ready?
We're going to drag Website to rows.
And then Website down to Values.
And look at that.
Now I don't like Row label.
So Design, Report Layout, Tabular.
Click right here.
And we'll type Frequency and Enter.
Now we want to drag Websites a second time,
because what we'd also like to do is do percent frequency.
Here we can clearly see that Amazon
is where we sell most of our boomerangs,
then our own base website, ColoradoBoomerangs.com,
and then some at eBay, and some at GelBoomerangs.com.
So there's our frequency, our count.
The total, of course, tells us how many transactions
we have in that data set.
Hopefully we don't have any empty cells.
Actually when we Control down arrow,
we would have bumped in one of those empty cells,
if there was an empty cell.
But now we want to do percent frequency, which is just
a fancy way of saying, take each one of these as the numerator
and then divide it on top of the denominator total.
That'll give us the percentage of the column total.
So guess what?
Right click.
And oh, the ever so powerful Show Values As,
and we want Percent of Column Total.
And just like that we have Frequency and Percent Frequency
and Enter.
Now I want to do the same calculation with formulas.
And in general, the pivot table is much faster and easier.
The only time we want to switch over to formulas
is if the data is going to change it
and we want it to instantly update without having
to use right click, Refresh.
Now right click, Refresh, doesn't seem like much.
And it's not.
But sometimes we need everything to update automatically,
so that's where formulas come in.
Now one thing about formulas is that you do not
have a feature that will automatically
create a unique list.
Well, if you don't already have the pivot table,
here's a great trick.
You can go up to Data and Advanced Filter.
Now Advanced Filter we're going to copy to another location.
But notice, with all of our Data Analysis features,
we didn't have a single cell selected inside our data set.
But no problem, the Collapse button
will let us go to a different sheet.
I'm going to click on Sales Data.
Click in the actual field name, and then
do Control Shift down arrow.
And what we want is this awesome little checkbox,
Unique Records Notice it jumps us back to the sheet.
I also need to tell it where I want that to go.
And when I click OK.
Boom.
There it is.
A unique list using Advanced Filter.
Now I'm going to type Frequency, tab,
and Percent Frequency, tab.
Highlight the two columns.
Click and drag.
Right click.
And I'm going to grab that Format Painter
to copy just the formatting, and then select those two cells.
And boop, highlight these two cells,
add some formatting for formulas.
And now we want to click in the cell.
And we're going to use COUNTIFS.
Equals COUNTIFS.
And guess what?
The criteria range is on a different sheet.
But no problem.
We're going to do a sheet reference.
Well, if the range is on a different sheet,
how do we get there?
We simply click on the sheet.
Oh, look at this.
I'm way down at the bottom.
I'm going to use the keyboard Control Home just
to jump to the top, A1.
And then I can get in the top cell, Control Shift down arrow.
And F4 to lock it.
Now you could see the formula-- here's
the screen tip right here formula-- evolving up
in the formula bar.
Now there's a crazy thing that happens when I
comma and go back to our sheet.
It puts in the actual sheet name,
even though I'm on the sheet.
And watch this.
Even if you were to delete this, once you go over
to a new sheet, you're permanently
in sheet reference mode.
So we don't really need that. so I'm simply
going to highlight and delete.
And so now there's our formula.
Close parentheses, Control Enter.
Double click and send it down.
Now we're in a slightly different order than over here.
The pivot table unique list alphabetized it.
The Advanced Filter Unique Records
Only gives us the first ones it runs into, but no problem.
If I want it the same, right click.
Notice this is the field names at the top, records and rows.
So I can use the Sort feature.
Notice I'm using right click.
And I'm going to sort A to Z. And so there we have it.
Now we can simply come below, Alt Equals to add it up.
And Enter.
Total.
Control Enter, shift arrow, arrow.
And I'm going to use Control B. And then add some formatting
for our formulas.
And now we can calculate our percent frequency.
Equals relative cell reference, one to my left.
I use the left arrow.
Divided by left arrow, Control down arrow, F4.
Control Enter.
And click and drag it down.
Now I can click below and Alt equals Control Enter.
Now I can highlight and add a percentage number
formatting with two decimals showing.
Now we have our frequency percent frequency
with either a pivot table or formulas.
Now let's talk about making a chart.
This is categorical data.
So we have Insert.
And in the Charts group for categorical data
we have three choices, Pie, Column, or Bar.
Now in recent years, the trends in data analytics
is to use column charts over pie charts.
Not only that, but research shows
that humans can understand differences
between column heights better than they can pie pieces.
So I'm going to click on the column and use 2D.
Oh, wait a second.
Look at that.
It's thinking I want the whole table.
I'm going to have to highlight.
I only want the labels at the top and the actual categorical
data and count.
Now I can go up to column and use 2D, not 3D.
3D tends to be chart junk.
Now one thing about long labels when you have a column chart,
is they don't fit so well.
So that's where the bar chart comes in.
I'm going to go to Change Chart Type.
And then over here click on Bar.
And click OK.
Much better.
Now we can click on the columns.
Control 1.
And in 2013 and later we have this task pane.
You can adjust the gap width, but you do not want to do that.
Zero gap width is reserved for continuous quantitative data.
But you can change the gap width a little if you'd like.
I'm going to go up to the Paint Bucket and Fill.
You can change it to whatever you want.
I'm going to vary colors by point.
Now I can close the Task pane.
And I actually can come over here.
And in the green plus, I love this.
Data labels.
It used to be so much harder, in earlier versions,
to add data labels.
That's looking good there.
But now we have chart junk.
I want to click down on the horizontal axis and hit Delete.
Now I'd like to come up and click on the chart
title and just type my chart title,
Boomerang Inc 2013 Sales Frequency by Website.
Notice when I type, the chart label didn't update.
But as soon as I hit Enter, boom.
There we go.
Now here's a crazy thing.
I want to change the font.
And if I Control 1, it's going to open this over here.
But there's no font.
And there's no font over here.
You actually have to right click and point to Font
or use the keyboard, Control Shift F, to get to Font.
Or actually just go up to Home and then Font Size.
I'm going to bring it down to 12.
Now one last thing.
We want to go over to the tab called Discrete Quantitative
data.
And here we have a frequency distribution.
This is a discrete random variable.
There's gaps between these numbers.
This is counting.
Zero people came in in a one-minute period
to our fast food restaurant at a busy lunchtime.
one person, two people, three people, four people.
You could see the biggest frequency-- from our sample
data-- 266 times four people came in
during a one-minute period.
Well, how do we visually articulate this data?
Well, this discrete random variable
goes along the horizontal axis.
And, of course, the heights of the column
represent the frequency.
But notice, there's gap widths here
to visually indicate that no numbers can
fit between 3 and 4.
There's a gap.
Back here on our Cat FD, the length of these bars
articulates the frequency or the count.
And then the labels are off to the side.
So in this video we talked about building frequency
distributions and percent frequency distributions
for categorical data with formulas and with pivot tables.
And then we saw an appropriate chart.
Now in next video we'll talk about frequency distributions
and the appropriate charts for continuous quantitative data.
All right.
We'll see you next video.